MySQL Optimize

MySQL is one of the most popular relational database management systems in use today, powering many popular websites and applications. To get the best performance, you need to optimize the MySQL database.

innodb_buffer_pool_size – Increase the buffer pool size. The buffer pool is where MySQL stores data and indexes in memory, so increasing its size can improve performance. A good rule of thumb is to set the buffer pool size to 80-90% of available memory.

nnodb_buffer_pool_instances – Increase the innodb_buffer_pool_instances. This will help to reduce contention and increase scalability.

innodb_log_file_size – Increase the innodb_log_file_size. This controls the size of the InnoDB redo logs, which are used to recover the database in case of a crash.

query_cache_size – Increase the query_cache_size. This cache stores the results of SELECT statements, so increasing its size can improve performance for frequently-run queries.

Tune the sort_buffer_size, read_buffer_size and read_rnd_buffer_size based on your workload.

Optimize your database schema by creating indexes on columns that are frequently searched.

Optimize MariaDB

Edit file


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

These are some basic settings. Now run the MySQL tuner, and make suggestions as required.

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

MySQL Tuner


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


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.


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

Leave a Reply

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