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.
Free Cloud Server Trial from IONOS
Try out a Cloud Server for free now - test your IONOS Cloud Server for 30 days!
REST APIUnlimited trafficVMware virtualizationMySQL/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.
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 -pEnter 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.
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.
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.
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.
Set this to O_DIRECT in order to avoid double buffering data.
To change the InnoDB settings, open the my.cnf file for editing:
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_DIRECTSave and exit the file. Restart MySQL/MariaDB for the changes to take effect:
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.
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.
Learn how to get started with the popular relational database MySQL/MariaDB. This quick start guide will cover the installation of both, and an introduction to basic MySQL/MariaDB commands.
Learn basic MySQL/MariaDB disaster recovery and preparedness: How to reset the root MySQL/MariaDB password.
Learn basic MySQL/MariaDB disaster recovery and preparedness: How to import databases from an exported MySQL dump file.
Provide powerful and reliable service to your clients with a web hosting package from IONOS.
