15 Useful MYSQL/MARIADB performance Tuning and optimization techniques (reproduced in a good article)

MySQL is a powerful open source relational database management system (RDBMS). It was posted on 1995 (20 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, released on May 29, 2015.

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

MySQL was acquired by Oracle in April 2009. The result is a division of the MySQL community that created a branch called MariaDB. The main reason for creating the branch is to keep the project free under the GPL.

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

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

Installing LAMP on Rhel/centos 7

Installing LAMP on Fedora 22

Installing LAMP in Ubuntu 15.04

Installing MariaDB on Debian 8

Installing MariaDB on Gentoo Linux

Installing MariaDB on Arch Linux

Important: Don't blindly accept these suggestions before you start. Each MySQL setting is different and needs to be considered carefully before making any changes.

You need to understand this:

The MYSQL/MARIADB configuration file is located in/etc/my.cnf. You will need to restart the MySQL service each time you change this file for the changes to take effect.

This article uses the MySQL 5.6 version.

1. Enable InnoDB per table one data file setting

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 the system tablespace to hold 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 information for each database in a. IBD data file. Each. idb file represents its own tablespace. In this way, a database operation similar to "TRUNCATE" can be done faster, and you can reclaim unused space when you delete or truncate a database table.

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

MySQL version 5.6 and above is enabled by default innodb_file_per_table. You can see it in the/etc/my.cnf file. The directive looks like this:

Innodb_file_per_table=1

2. Store MySQL database data on a separate partition

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

Sometimes operating system reads/writes can degrade the performance of your MySQL server, especially if the operating system and database data reside on the same disk. Therefore, I 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 hangs in the/dev/sdb.

The next step is to prepare the new partition: # FDISK/DEV/SDB

Now press "N" to create a new partition. Then press "P" so that it is created as the primary partition. After this, set the partition number from 1-4. After that, you can select the partition size. Press ENTER here. In the next step, you need to configure the size of the partition.

If you want to use all of the disks, press ENTER one more time. Otherwise, you can manually set the size of the new partition. When you're ready, press "W" to save your changes. Now, we need to create a file system for our new partition. This can be done easily with the following command:

# 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 under the directory you just created:

# mount/dev/sdb1/ssd/

You can add the following line to the boot auto mount in the/etc/fstab file:

/DEV/SDB1/SSD ext3 defaults 0 0

Now we move MySQL to the new disk

First stop the MySQL service:

# Service Mysqld Stop

I recommend that you stop Apache/nginx at the same time to prevent any attempts to write to the database:

# service httpd stop# service Nginx Stop

Now copy the entire MySQL directory into 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/var/lib/mysql-backup

Then create a symbolic Link:

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

Now start your MySQL and Web services:

# service Mysqld start# service httpd start# service Nginx start

Your database will be accessed using the new disk in the future.

3. Optimize the buffer pool using InnoDB

The InnoDB engine has a buffer pool in memory to cache data and indexes. This certainly helps you execute MYSQL/MARIADB query statements faster. Choosing the right memory size requires some important decisions and a greater understanding of the memory consumption of the system.

Here's what you need to consider:

How much memory will be consumed by other processes. This includes your system processes, page tables, socket buffers.

Is your server dedicated to MySQL or are you running other very memory-intensive services?

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

You need to set this in MY.CNF:

Innodb_buffer_pool_size

4. Avoid using swappiness in MySQL

"Swap" is a process that occurs when the system moves some memory into a special disk space called Swap space. Typically, this happens when your system runs out of physical memory, and the system writes the information to disk instead of freeing up some memory. As you guessed, the disk is much slower than your memory.

This option is enabled by default:

# sysctl Vm.swappiness vm.swappiness = 60

Run the following command to turn off 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 your server currently allows. The MYSQL/MARIADB server allows users with SUPER privileges to establish a connection beyond the maximum connection. The connection is established only when the MySQL request is executed, and the connection is closed and replaced by the new connection when the execution is completed.

Keep in mind that too many connections can lead to high memory usage and lock your MySQL server. Small sites typically require 100-200 of connections, while larger may require 500-800 or more. The value here depends to a large extent on your mysql/mariadb usage.

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

# mysql-u root-pmysql> Set Global max_connections = 300;

6. Configure the number of thread caches for MySQL

The thread_cache_size directive is used to set the number of threads your server caches. When the client disconnects, its thread is placed in the cache if the current number of threads is less than thread_cache_size. The next request is done by using a thread in the cache pool.

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

Mysql> Show status like ' threads_created ';mysql> show status like ' Connections ';

You can use the following formula to calculate the hit ratio of a thread pool:

-((threads_created/connections) * 100)

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 add thread_cache_size.

One advantage here is that you can dynamically change the thread_cache_size without restarting the MySQL service. You can do this in the following ways:

mysql> Set Global thread_cache_size = 16;

7. Disable the DNS reverse query for MySQL

By default, when a new connection occurs, MYSQL/MARIADB DNS queries to resolve the user's IP address/host name. For each client connection, its IP will be resolved to the hostname. The host name is then parsed back to IP to verify that the two are consistent.

This is likely to cause a delay when the DNS configuration is wrong or the server is having problems. This is why you have to turn off DNS for reverse queries, and you can add the following options to your profile to set it up:

[mysqld]# Skip Reverse DNS lookup of Clientsskip-name-resolve

After the change you need to restart the MySQL service.

8. Configure the query cache capacity for MySQL

If you have a lot of duplicate queries and the data doesn't change very often – use cache queries. People often do not understand the actual meaning of query_cache_size and set this value to GB, which in effect degrades the performance of the server.

The reason behind this is that the threads in the update process need to lock the cache. Usually set to 200-300 MB should suffice. If your site is relatively small, you can try to give 64M and in a timely manner to increase.

Add the following settings to your MySQL configuration file:

Query_cache_type = 1query_cache_limit = 256kquery_cache_min_res_unit = 2kquery_cache_size = 80M

9. Configure temporary table capacity and memory table maximum capacity

The two variables, tmp_table_size and max_heap_table_size, should be the same size, which allows you to avoid disk writes. The tmp_table_size is the maximum space for the built-in memory table. If the size of the table exceeds the limit, it will be converted to the MyISAM table on disk.

This can affect the performance of the database. Administrators generally recommend setting these two values on the server to 64M per GB of memory.

[Mysqld]tmp_table_size= 64mmax_heap_table_size= 64M

10. Enable MySQL Slow query log

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

Slow-query-log = 1slow-query-log-file =/var/lib/mysql/mysql-slow.loglong_query_time = 1

The first variable enables a slow query log, and the second tells MySQL where the actual log file is stored. Use Long_query_time to define how long it takes to complete a MySQL query.

11. Check MySQL for idle connections

Idle connections consume resources and should be terminated or refreshed if they are available. An idle connection is a connection that is in a "sleep" state and is maintained for a long time. You can view the idle connection by running the following command:

# mysqladmin Processlist-u root-p | grep "Sleep"

This displays a list of processes that are in sleep. This occurs when the code uses a persistent connection to the database. Using PHP to call Mysql_pconnect can open this connection, after executing the query, delete the authentication information and leave the connection open state. This causes the buffering of each thread to be kept in memory until the thread ends.

The first thing you need to do is check the code and fix it. If you cannot access the running code, you can modify the Wait_timeout variable. The default value is 28,800 seconds, and you can safely reduce it to 60:

Wait_timeout=60

12. Select the correct file system for MySQL

Choosing the right file system is critical to the database. The most important thing you need to consider here is the integrity of the data, the performance and ease of management.

According to MariaDB's recommendations, the best file systems are XFS, EXT4, and Btrfs. They are enterprise-grade journaled file systems that can use very large files and bulk storage volumes.

Below you can find some useful information about these three file systems:

File system Xfsext4btrfs file system maximum capacity 8eb1eb16eb Maximum file size 8eb16tb16eb

Our article details the pros and cons of Linux file systems: Linux file System parsing.

13. Set the maximum allowed data packets for MySQL

MySQL splits the data into packages. Typically a package is a row of data that is sent to the client. The Max_allowed_packet variable defines the largest package that can be sent.

Setting this value too low may cause the query to become very slow, and then you will see an error in the MySQL error log. It is recommended that the value be set to the maximum package size.

14. Test MySQL for performance optimization

You should check the performance of mysql/mariadb regularly. This will help you understand whether the resource usage has changed or needs to be improved.

There are a lot of test tools available, but I recommend you a simple and easy to use. The tool is called Mysqltuner.

Download and run it using the following command:

# wget https://github.com/major/MySQLTuner-perl/tarball/master# Tar xf master# cd major-mysqltuner-perl-993bc18/#./ mysqltuner.pl

You will receive detailed reports and recommended tips for MySQL usage. The following is an example of the output from the default MariaDB installation:

MySQL Performance Optimization

15. Optimize and Repair MySQL database

Sometimes tables in the MYSQL/MARIADB database can easily crash, especially if the server shuts down unexpectedly, the file system suddenly crashes, or the database is still accessed during replication. Fortunately, there is a free open source tool called ' Mysqlcheck ', which automatically checks, repairs, and optimizes all tables in a database in Linux.

# mysqlcheck-u root-p--auto-repair--check--optimize--all-databases# mysqlcheck-u root-p--auto-repair--check--op Timize DatabaseName

That's all! I hope the above article will be useful to you and help you to optimize your MySQL server. As always, if you have any questions or comments, please submit them in the comments section below.

15 Useful MYSQL/MARIADB performance Tuning and optimization techniques (reproduced in a good article)