Optimize MySQL/MariaDB Performance on a Cloud Server with Linux
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 from IONOS
Try out a Cloud Server for free now - test your IONOS Cloud Server for 30 days!
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
Related articles
How to use MySQL With Your Ruby on Rails Application
Ruby on Rails uses SQLite as its database by default, but it also supports the use of MySQL. SQLite is an excellent alternative to a traditional database like MySQL, but it has some limitations, particularly with regards to concurrency and scaling to a high load, which may make MySQL a better choice for your project.
How to Reset the MySQL or MariaDB Root Password
Learn basic MySQL/MariaDB disaster recovery and preparedness: How to reset the root MySQL/MariaDB password.
Connect to a MySQL/MariaDB Database with PHP on a Cloud Server Running Linux
Learn how to connect to a MySQL/MariaDB database using PHP on a Cloud Server running Linux. This tutorial will cover connecting to a database on the same server as the PHP application, as well as how to connect to a database on a different Cloud Server.
RDVectorShutterstock How to import a MySQL/MariaDB Database
Learn basic MySQL/MariaDB disaster recovery and preparedness: How to import databases from an exported MySQL dump file.
How to install and use MySQL/MariaDB
MySQL/MariaDB is one of the most popular relational databases. Under Linux, it is easy to install and manage via the terminal. Find out how to install Mysql with this article’s step by step instructions. We will also show you the basic commands so that you can create your first custom database. What are you waiting for?
