15 useful MySQL/MariaDB performance tuning and Optimization Techniques

This article will show you some basic but useful tips on how to optimize the performance of MySQLMariaDB. Note: This document assumes that you have installed MySQL or Maria

This article will show you some basic but useful tips on how to optimize MySQL/MariaDB performance. Note: This document assumes that you have installed MySQL or Maria

MySQL is a powerful open-source Relational Database Management System (RDBMS ). It was released in 1995 (20 years ago ). It uses Structured Query Language (SQL), which may be the most popular choice in database content management. The latest MySQL version is 5.6.25, which was released on July 25, May 29, 2015.

An interesting fact about MySQL is that its name comes from Michael Widenius (MySQL founder)'s daughter "My ". Although there are many interesting rumors about MySQL, this article mainly shows you some useful practices to help you manage your MySQL server.

MySQL Performance Optimization

In April 2009, MySQL was acquired by Oracle. The result is that the MySQL Community is split and a branch named MariaDB is created. The main reason for creating this branch is to keep this project free under GPL.

Today, MySQL and MariaDB are one of the most popular RDMS for WordPress, Joomla, Magento, and other web applications (if not the most ).

This article will show you some basic but useful tips on how to optimize MySQL/MariaDB performance. Note: This document assumes that you have installed MySQL or MariaDB. If you still don't know how to install them on the system, follow the instructions below:

Important: Do not blindly accept these suggestions before you start. Each MySQL setting is different. You must consider it carefully before making any changes.

You need to understand this:

The MySQL/MariaDB configuration file is located at/etc/my. cnf. Every time you change this file, you need to restart the MySQL service for the change to take effect.

This article uses MySQL 5.6.

1. enable one data file setting for each table in InnoDB

First, there is an important explanation that InnoDB is a storage engine. MySQL and MariaDB use InnoDB as the default storage engine. Previously, MySQL used system tablespace to store tables and indexes in the database. This means that the only purpose of the server is database processing, and their storage disks are not used for other purposes.

InnoDB provides a more flexible way to store the information of each database in A. ibd data file. Each. idb file represents its own tablespace. In this way, you can perform database operations similar to "TRUNCATE" faster. When you delete or TRUNCATE a database table, you can recycle unused space.

Another advantage of this configuration is that you can place some database tables on a separate storage device. This can greatly increase the I/O load on your disk.

Innodb_file_per_table is enabled by default in MySQL 5.6 and later versions. You can see it in the/etc/my. cnf file. This command looks like this:

2. Store MySQL database data to independent partitions

Note: This setting is only valid on MySQL and invalid on MariaDB.

Sometimes the read/write operations of the operating system will reduce the performance of your MySQL server, especially if the operating system and the database data are on the same disk. Therefore, we recommend that you use a separate disk (preferably an SSD) for the MySQL service.

To complete this step, you need to connect the new disk to your computer/server. For this article, I assume that the disk is mounted to/dev/sdb.

The next step is to prepare a new partition:

# Fdisk/dev/sdb

Click "N" to create a new partition. Then press "P" to create the primary partition. After that, set the Partition Number from 1-4. Then, you can select the partition size. Press enter here. In the next step, you need to configure the partition size.

If you want to use all disks, press enter again. Otherwise, you can manually set the size of the new partition. After you are ready, press "w" to save the changes. Now, we need to create a file system for our new partition. This can be done with the following command easily:

# Mkfs. ext4/dev/sdb1

Now we will mount the new partition to a directory. I created a directory named "ssd" under the root directory:

# Mkdir/ssd/

Mount the new partition to the directory you just created:

# Mount/dev/sdb1/ssd/

You can add the following lines to the/etc/fstab file to set automatic mounting upon startup:

Now we will move MySQL to the new disk

First stop the MySQL service:

# Service mysqld stop

We recommend that you stop Apache/nginx at the same time to prevent any attempt to write data to the database:

# Service httpd stop

# Service nginx stop

Copy the entire MySQL directory to the new partition:

# Cp/var/lib/mysql/ssd/-Rp

This may take some time, depending on the size of your MySQL database. Once this process is complete, rename the MySQL directory:

# Mv/var/lib/mysql-backup

Then create a symbolic link:

# Ln-s/ssd/mysql/var/lib/mysql

Start your MySQL and web Services now:

In the future, your database will be accessed using a new disk.

3. optimized the buffer pool using InnoDB

The InnoDB engine has a buffer pool in memory for caching data and indexes. This certainly helps you to execute MySQL/MariaDB query statements faster. Selecting the appropriate memory size requires some important decisions and a greater understanding of the system's memory consumption.

You need to consider the following:

The amount of memory consumed by other processes. This includes your system process, page table, and Socket buffer.

Whether your server is dedicated to MySQL or whether you are running other memory-consuming services.

On a dedicated machine, you may allocate 60-70% of the memory to innodb_buffer_pool_size. If you want to run more services on a machine, you should reconsider the memory size dedicated to innodb_buffer_pool_size.

You need to set this item in my. cnf:

Innodb_buffer_pool_size

4. Avoid using Swappiness in MySQL

"Swap" is a process when the system moves part of the memory to a special disk space called "swap space. This usually happens when your system runs out of physical memory. The system writes information to the disk instead of releasing some memory. As you guess, the disk is much slower than your memory.

This option is enabled by default:

# Sysctl vm. swappiness

Run the following command to disable swappiness:

# Sysctl-w vm. swappiness = 0

5. Set the maximum number of connections for MySQL

The max_connections Command tells you how many concurrent connections are allowed on your server. The MySQL/MariaDB server allows users with SUPER permissions to establish a connection beyond the maximum connection. A connection is established only when the MySQL request is executed. After the execution is complete, the connection is closed and replaced by a new connection.

Remember, too many connections will cause high memory usage and lock your MySQL server. Generally, a small website requires 100-200 connections, while a large website may require 500-800 or more connections. The value here depends largely on your usage of MySQL/MariaDB.

You can dynamically change the value of max_connections without restarting the MySQL server:

# Mysql-u root-p

6. Configure the number of MySQL thread caches

The thread_cache_size command is used to set the number of threads cached on your server. When the client is disconnected, if the current number of threads is smaller than thread_cache_size, its threads will be put into the cache. The next request is completed by using the thread in the cache pool.

To improve the server performance, you can set a relatively high value of thread_cache_size. You can view the thread cache hit rate in the following ways:

You can use the following formula to calculate the thread pool hit rate:

If you get a lower number, this means that most mysql connections use new threads instead of loading from the cache. In this case, you need to increase thread_cache_size.

One advantage is that you can dynamically change thread_cache_size without restarting the MySQL service. You can achieve this by using the following methods:

7. Disable reverse DNS query for MySQL

By default, when a new connection occurs, MySQL/MariaDB performs DNS queries to resolve the user's IP address/Host Name. For each client connection, its IP address is resolved as the host name. Then, the host name is reversed to IP to verify whether the two are consistent.

When the DNS configuration is incorrect or the server is faulty, this may cause a delay. This is why you want to disable reverse DNS query. You can add the following options in your configuration file to set the settings:

After the change, you need to restart the MySQL service.

8. Configure the MySQL query cache capacity

If you have many duplicate queries and the data does not change frequently-use cache queries. People often do not understand the actual meaning of query_cache_size and set this value to the GB level, which actually reduces the server performance.

The reason is that the thread needs to lock the Cache during the update process. It is usually set to 200-300 MB. If your website is relatively small, you can try to add 64 M and later.

Add the following settings to your MySQL configuration file:

9. Configure the temporary table capacity and the maximum memory table capacity

The values of tmp_table_size and max_heap_table_size must be the same, so that you can avoid writing data to the disk. Tmp_table_size is the maximum space of the built-in memory table. If the table size exceeds the limit, it is converted to the MyISAM table on the disk.

This will affect the database performance. The Administrator usually recommends setting these two values on the server to 64 mb per GB of memory.

10. Enable MySQL slow query log

Recording slow queries can help you locate database problems and help you debug them. This can be enabled by adding the following values to your MySQL configuration file:

The first variable enables slow query logs, and the second variable tells MySQL the actual log file storage location. Use long_query_time to define the duration of MySQL query.

11. Check the idle connection of MySQL.