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
|
1 |
vi /etc/mysql/mariadb.conf.d/50-server.cnf |
Find
|
1 |
[mysqld] |
On this section, you will see “#skip-external-locking”, add below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
max_connections = 500 wait_timeout = 8800 interactive_timeout = 8800 query_cache_size=0 query_cache_type=0 join_buffer_size=2048K tmp_table_size=256M max_heap_table_size=256M table_open_cache=4000 table_definition_cache=800 innodb_buffer_pool_size=10G innodb_buffer_pool_instances=10 innodb_log_file_size=1G performance_schema = ON skip-name-resolve=1 |
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.
|
1 |
systemctl restart mariadb |
MySQL Tuner
|
1 2 |
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl perl mysqltuner.pl |
innodb_buffer_pool_size
On a server with 32 GB RAM, set
|
1 2 |
innodb_buffer_pool_size=20G innodb_buffer_pool_instances=20 |
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
|
1 |
show variables like '%innodb_buffer_pool%'; |
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.
|
1 |
innodb_flush_log_at_trx_commit=2 |
See MySQL