MySQL/MariaDB is a robust, powerful, and popular database choice. However, without fine-tuning, it can be inefficient in its use of server resources. Learn how to optimize MySQL/MariaDB performance in order to improve database response time and minimize server overhead.

Requirements

  • A Cloud Server with Linux.
  • MySQL 5.7 or MariaDB 5.5 installed and running.
Free Cloud Server Trial
Try our cloud hosting free for 30 days

Thanks to free starting credit, you can test the IONOS cloud server for 1 month free of charge (or until the credit is used up) and experience the perfect combination of performance and security!

Note

MySQL/MariaDB is installed and running on a standard Linux installation by default. If your server was created with a minimal installation, you will need to install and configure MySQL/MariaDB before you proceed.

Optimize MySQL/MariaDB Queries

Part of the process of tuning MySQL/MariaDB for better performance is checking for slow or inefficient queries. One poorly-structured MySQL/MariaDB query can slow down the entire database.

MySQL/MariaDB can be configured to log any query which takes longer than the specified number of seconds. This will let you track any slow queries, and correct them as needed.

To enable slow query logging, log in to MySQL/MariaDB:

mysql -u root -p

Enter the following command to enable logging:

SET GLOBAL slow_query_log = 'ON';

The default threshold is 10 seconds. Use the following command to enable logging for any query that takes longer than 1 second:

SET GLOBAL long_query_time = 1;

Queries which take longer than 1 second to complete will be logged at /var/lib/mysql/hostname-slow.log.

Tune InnoDB Values

There are three InnoDB settings which can be tuned to improve the performance of MySQL 5.7 and MariaDB 5.5. More information about each of these settings can be found in the official MySQL documentation.

innodb_buffer_pool_size

This should be a value between 50% and 75% of the server's total RAM. It should be equal to or less than the size of the database.

innodb_flush_log_at_trx_commit

This setting offers a significant trade-off between performance and reliability. When set to 0, database performance is greatly increased. However, up to 1 second of transactions can be lost in a crash.

The default value for this setting is 1. This value is required for full ACID compliance. If ACID compliance is a requirement for your project, do not change the value for innodb_flush_log_at_trx_commit.

innodb_flush_method

Set this to O_DIRECT in order to avoid double buffering data.

Add the New InnoDB Settings

To change the InnoDB settings, open the my.cnf file for editing:

  • CentOS 7:sudo nano /etc/my.cnf
  • Ubuntu 16.04:sudo nano /etc/mysql/my.cnf

CentOS 7 Only: Leave the first line of the file ([mysqld]) intact. Place configurations below this line.

Ubuntu 16.04 only: Add the following content to the top of the file. Be sure to put [mysqld] as the first line.

Add the new configurations, one per line. For example:

[mysqld]
innodb_buffer_pool_size = 500M
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT

Save and exit the file. Restart MySQL/MariaDB for the changes to take effect:

  • CentOS 7:sudo systemctl restart mariadb
  • Ubuntu 16.04:sudo systemctl restart mysql
Was this article helpful?
Go to Main Menu