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

vi /etc/mysql/mariadb.conf.d/50-server.cnf 

Find

[mysqld]

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

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.

systemctl restart mariadb

MySQL Tuner

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

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

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.

innodb_flush_log_at_trx_commit=2

Slow Query Summary over 12 hours.

pt-query-digest --since=24h /var/log/mysql/mysql-slow.log --limit=100% --filter '($event->{user} || \"\") =~ \"USER_HERE\"'

See MySQL