MySQL and MariaDB are the most widely used relational database management systems (RDMS) when it comes to website hosting and CMS systems such as Joomla, WordPress, Drupal, and Typo 3. In this article, I will explain how to speed up and optimize your MySQL and MariaDB database server.

Store MySQL data in the separate partitions

When it comes to the point of optimization and assurance, it is always the best idea to store the database data in a separate volume. The volumes are specifically for fast storage volumes such as SSD, NVMe. Even if your system will fail, you will have your database safe. As the partition volume is made up of fast storage volumes, the performance will be faster.

Set the maximum number of MySQL connections

MySQL/MariaDB uses an instruction max_connections which specifies how many concurrent connections are currently allowed in the server. Too many connections result in high memory consumption as well as high CPU load. For small websites, the connections can be specified to 100-200 and larger ones may need 500-800 and more. The max_connections can be dynamically changed using the SQL query. In this example, I have set the value to 200.

$ mysql -u root -p
mysql> set global max_connections=200;

Output :

Set max connections in MySQL

Enable MySQL slow query Log

Logging queries that take a very long time to execute makes it easier to troubleshoot the database problems. The slow query log can be enabled by adding the following lines in the MySQL/MariaDB configuration file.

slow-query-log=1
slow-query-log-file= /var/lib/mysql/mysql-slow-query.log
long-query-time=1

Where first variable enables slow query log

Second variable defines the log file directory

Third variable defines the the time to complete a MySQL query

Restart the mysql/mariadb service and monitor the log

$ systemctl restart mysql
$ systemctl restart mariadb
$ tail -f /var/lib/mysql/mysql-slow-query.log

Set the maximum packet allowed by MySQL

Data is split into the packets in MySQL. Max_allowed_packet defines the maximum size of the packets that can be sent. Setting up the max_allowed_packet too low can cause the query to be too slow. It is recommended to set the packet value to the size of the largest packet.

Setting up the Temporary table capacity

Tmp_table_size is the maximum space used for the built-in-memory table. If the size of the table exceeds the limit specified, it will be converted to a MyISAM table on disk. In MySQL/MariaDB, you can add the following variables in the configuration file to set up Temporary table size. It is recommended to set this value on the server 64M per GB memory.

[mysqld]

tmp_table_size=64M

Restart the mysql service

$ systemctl restart mysql
$ systemctl restart mariadb

Set up the maximum memory table capacity.

Max_heap_table_size is the variable used in MySQL to configure maximum memory table capacity. The size of the maximum memory table capacity should be the same as the temporary table capacity to avoid disk writes. It is recommended to set this value on the server to 64M per GB memory. Add the following line in the MySQL configuration file and restart the service.

[mysqld]

max_heap_table_size=64M

To apply the changes, restart the database server.

$ systemctl restart mysql
$ systemctl restart mariadb

Disable DNS reverse lookup for MySQL

When a new connection is received, MySQL/MariaDB will perform DNS lookup to resolve the user’s IP address. This may cause a delay when the DNS configuration is invalid or there is a problem with the DNS server. To disable the DNS lookup, add the following line in the MySQL configuration file and restart the MySQL service.

[mysqld]

skip-name-resolve

Restart the service:

$ systemctl restart mysql
$ systemctl restart mariadb

Avoid Using Swappiness in MySQL

The Linux Kernel moves part of the memory to a special partition of the disk called “swap” space when the system runs out of the physical memory. In this condition, the system writes information to the disk rather than freeing some memory. As the system memory is faster than the disk storage it is recommended to disable the swappiness. Swappiness can be disabled by using the following command.

$ sysctl -w vm.swappiness=0

Output :

Set swappiness

Increase InnoDB buffer pool size

MySQL/MariaDB has an InnoDB engine that has a buffer pool to cache and index data in the memory. Buffer pool helps MySQL/MariaDB queries be executed comparatively faster. Choosing the proper size of the InnoDB buffer pool requires some knowledge of system memory. The best idea is to set the value of the InnoDB buffer pool size to 80% of the RAM.

Example.

  • System Memory = 4GB
  • Buffer Pool size = 3.2GB

Add the following line in the MySQL configuration file and restart the service

[mysqld]

Innodb_buffer_pool_size 3.2G

Restart the database:

$ systemctl restart mysql
$ systemctl restart mariadb

Dealing with Query cache size

Query cache directive in MySQL/MariaDB is used to cache all the queries which keep on repeating with the same data. It is recommended to set the value to 64MB and increase in time for small websites. Increasing the value of query cache size to GB’s is not recommended as it may degrade the database performance. Add the following line in the my.cnf file.

[mysqld]

query_cache_size=64M

Check idle connections

Resources are consumed by idle connections so that it needs to be terminated or refreshed if possible. These connections remain in the “sleep” state and may stay for a long period of time. Check the idled connections using the following command.

$ mysqladmin processlist -u root -p | grep “Sleep”

The query will list out the processes that are in sleep state. Generally in PHP, the event can occur when using mysql_pconnect. This opens the MySQL connection, executes the queries, removes authentications, and leaves the connection open. Using wait_timeout directive, the idle connections can be interrupted. The default value for wait_timeout is 28800 seconds which can be decreased to a minimum time range like 60 seconds. Add the following line in the my.cnf file

[mysqld]

wait_timeout=60

Optimize and repair MySQL database

If the server is shut down unexpectedly then there is a chance that tables in MySQL/MariaDB can crash. There are other possible reasons to get the database table crash such as accessing the database while the copy process is running, the file system is suddenly crashed. In this situation, we have a special tool called “mysqlcheck” which checks, repairs, and optimizes all tables in the databases.

Use the following command to perform the repair and optimization activities.

For all databases:

$ mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

For specific database:

$ mysqlcheck -u root -p --auto-repair --check --optimize dbname

Replace dbname with your database name

  1. Check MySQL/MariaDB performance using testing tools

It is best practice to check the MySQL/MariaDB database performance regularly. This will make it easy to get the performance report and point of improvement. There are many tools available among which mysqltuner is the best one.

Run the following command to download the tool

$ wget https://github.com/major/MySQLTuner-perl/tarball/master

Untar the file

$ tar xvzf master

Go to the project directory and execute the following script.

$ cd major-MySQLTuner-perl-7aa57fa
$ ./mysqltuner.pl

Output:

Optimize MySQL and MariaDB using mysqltuner

Conclusion

In this article, we have learned how to optimize MySQL/MariaDB using different techniques. Thank you for reading.

13 Tips for Tuning and Optimizing Mysql and Mariadb Databases

Karim Buzdar

About the Author: Karim Buzdar holds a degree in telecommunication engineering and holds several sysadmin certifications. As an IT engineer and technical author, he writes for various web sites. You can reach Karim on LinkedIn