Shulou(Shulou.com)06/03 Report--

How to optimize MySQL performance by adjusting MySQL's own configuration? This problem may be our daily study or work often see. Through this question, I hope you can harvest more. Let's look at the optimization plan together with Xiaobian today.

Preparation:

1, Pagoda Linux panel official version 5.2.0+ (2017/09/20 release) beta version 5.2.4+

2、MySQL 5.x

MySQL tuning is usually divided into the following parts:

1. MySQL configuration parameter tuning (needs to be adjusted according to the operation of the website)

2. Data table index tuning (obvious effect, but usually excellent open source programs do not need to be adjusted)

SQL statement tuning (this is what programmers or DBAs do)

Today we mainly talk about how to optimize MySQL configuration parameters with the new features of Pagoda Panel. Let's first look at two pictures.

! []

1566986430174536.png

Obviously,(Figure 1) shows MySQL's current running state, and (Figure 2) shows MySQL's main configuration parameters

Let's take a look at these two pictures:

1. Active/peak number of connections (Figure 1) The current active connection is 1, and since MySQL service was started, the maximum number of connections is 54; when the maximum number of connections is close to or equal to max_connections in Figure 2, max_connections should be increased appropriately. It is recommended not to increase too much at once. It is recommended to increase 50 at each time and observe for a period of time. If it is not enough, continue to increase.

Thread cache hit rate (Figure 1) Thread cache hit rate is 99.78%, if this value is less than 90%, it is recommended to appropriately increase the thread_cache_size (Figure 2), it is recommended to increase 8 at a time.

3. Index hit rate (Figure 1) The index hit rate is 99.50%. If this value is less than 95%, it is recommended to increase the key_buffer_size in Figure 2 appropriately. It is recommended to increase it by 64 each time. It should be noted that if your database uses Innodb engine, you can ignore this option.

4. Innodb index hit rate (Figure 1) Innodb index hit rate is 100%. If this value is less than 95%, it is recommended to increase innodb_buffer_pool_size appropriately (Figure 2). It is recommended to increase it by 64 each time. It should be noted that if your database does not use Innodb engine, you can ignore this option.

5. Query cache hit rate MySQL query cache is a controversial feature. Personally, it is recommended that when you have cache software such as redis and memcached, you can turn it off by setting query_cache_size to 0 in (Figure 2). When you do not use cache software, there is excess memory usage, and database bottlenecks obviously exist, you can try to turn on query cache. This is a feature that relies heavily on data table structure and SQL statement optimization. If the table structure and SQL statements are optimized for query caching, it works well.

6. Create temporary tables to disk (Figure 1) The proportion of temporary tables created to disk is 0.42%, which means that most temporary tables have been created to memory, and will not increase the disk IO overhead too much. It is recommended that tmp_cache_size in (Figure 1) be increased appropriately when the proportion is greater than 2%. It is recommended to increase it by 32 each time. When the proportion is greater than 60%, give up. Some open source programs have not specially optimized SQL statements, so a large number of temporary tables will be opened during operation. No amount of cache is enough.

7. Open tables When the open table in (Figure 1) is close to or equal to table_open_cache in (Figure 2), you can appropriately increase table_open_cache, but if the setting is too large, it may cause your program to frequently interrupt MySQL connections. It is recommended that it be within 1024 and not exceed 2048.

8, did not use the index of the amount, did not use the index JOIN amount if not 0, check the next table index bar, in fact, as long as there is no crazy rise, such as a few thousand a day, generally can be ignored, must optimize the index or programmers or DBAs to do more appropriate.

9. Number of mergers after sorting If this value is slowly increasing, it is recommended to increase the sort_buffer_size in Figure 2 appropriately. It is recommended to increase the sort_buffer_size by 512 each time, but the maximum should not exceed 8192. If this value has been rising wildly, it is useless to increase the sort_buffer_size. Give up this option. This pot is still for the program developer.

10, lock table times If the server CPU overhead is not large, crazy lock table, it is recommended that you convert all data tables into innodb, remember to backup before conversion.

11. Optimization scheme This is a recommended optimization scheme given by us according to the memory size. Is it only recommended for basic reference values or to adjust each configuration item according to the actual situation?

Note: Saving parameter configuration will not take effect immediately, remember to restart MySQL service.

The above is the way to optimize MySQL performance through MySQL's own configuration adjustment. Is there any gain after reading it? If you want to know more about it, welcome to pay attention to industry information!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.