MySQL Optimize

Optimize MariaDB

Edit file

Find

On this section, you will see “#skip-external-locking”, add below.

This is some basic settings. Now run MySQL tuner, make suggestions as required.

To make the settings live, you need to restart MariaDB server.

MySQL Tuner

innodb_buffer_pool_size

On a server with 32 GB RAM, set

Having more innodb_buffer_pool_size, make MySQL act like an in memory database.

innodb_buffer_pool_instances divide buffer pool into smaller instances. It is better use 1 GB per instance.

To see current innodb_buffer_pool values, run

MySQL InnoDB Optimise

innodb_flush_log_at_trx_commit

Controls the balance between strict ACID compliance for commit operations and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. You can achieve better performance by changing the default value but then you can lose transactions in a crash.

The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.

With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.

With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.

If you can afford to lose 1 second of data, setting value of innodb_flush_log_at_trx_commit to 2 will improve performance. For most web applications, this works fine.

See MySQL

Need help with Linux Server or WordPress? We can help!

Leave a Reply

Your email address will not be published. Required fields are marked *