About MySQL tuning
There are three ways to speed up the running speed of MySQL server, from low efficiency to high efficiency:
Replace the faulty hardware. Tune the settings of MySQL process. Optimize the query.
Replacing faulty hardware is usually our first consideration. The main reason is that the database will occupy a lot of resources. However, this solution is limited to this. In fact, you can usually double the speed of the central processing unit (CPU) or disk, or increase the memory by 4 to 8 times.
The second method is to tune the MySQL server (also known as mysqld). Tuning this process means allocating memory appropriately and letting mysqld know what kind of load it will bear. It is better to speed up the disk operation than to reduce the number of disk accesses required. Similarly, ensuring that the MySQL process operates correctly means that it spends more time on service queries than on background tasks (such as processing temporary disk tables or opening and closing files). Tuning mysqld is the focus of this article.
The best way is to ensure that the query has been optimized. This means that an appropriate index is applied to the table, and the query is written in a way that can make full use of MySQL functions. Although this article does not cover query tuning (many works have discussed this topic), it will configure mysqld to report queries that may need tuning.
Although the order of these tasks has been assigned, attention should still be paid to the hardware and mysqld settings to facilitate proper query tuning. A slow machine is enough. I have seen fast machines fail when running well-designed queries due to heavy load, because mysqld is occupied by a lot of busy work and cannot service queries.
Record slow query
In a SQL server, data tables are saved on disk. Indexes provide the server with a way to find specific data rows in a table without searching the entire table. When the entire table must be searched, it is called a table scan. Generally, you may only want to obtain a subset of the data in the table, so a full table scan will waste a lot of disk I/O, and therefore a lot of time. When data must be connected, the problem becomes more complicated because it is necessary to compare multiple rows of data at both ends of the connection.
Of course, table scanning does not always cause problems; Sometimes it is more effective to read the entire table than to select a part of the data (the query planner is used to make these decisions in the server process). If the index is used inefficiently or cannot be used at all, the query speed will be slowed down, and this problem will become more significant as the load on the server and the table size increase. A query whose execution time exceeds a given time range is called a slow query.
You can configure mysqld to record these slow queries in the appropriately named slow query log. Administrators then look at the log to help them determine which parts of the application need further investigation. Listing 1 shows the configuration required in my.cnf to enable slow query logging.
Listing 1. Enable MySQL slow query log
[mysqld]
; enable the slow query log, default 10 seconds
log-slow-queries
; log queries taking longer than 5 seconds
long_query_time = 5
; log queries that don't use indexes even if they take less than long_query_time
; MySQL 4.1 and newer only
log-queries-not-using-indexes
These three settings can be used together to record queries that take more than 5 seconds to execute and do not use indexes. Please note the warning about log queries not using indexes: You must use MySQL 4.1 or higher. The slow query logs are saved in the MySQL data directory, named hostname-slow.log. If you want to use a different name or path, you can use log slow queries=/new/path/to/file in my.cnf.
It is better to read the slow query log through the mysqldumpslow command. Specify the path of the log file, you can see a sorted list of slow queries, and also show the number of times they appear in the log file. A very useful feature is that mysqldumpslow deletes any user specified data before comparing results, so different calls to the same query are counted as one time; This can help identify the queries that require the most work.
Cache queries
Many LAMP applications rely heavily on databases, but execute the same query repeatedly. Every time a query is executed, the database must perform the same work - analyze the query, determine how to execute the query, load information from the disk, and then return the results to the client. MySQL has a feature called query cache, which stores the query results (to be used later) in memory. In many cases, this can greatly improve performance. However, the problem is that query caching is disabled by default.
Add query_cache_size=32M to/etc/my.conf to enable 32MB query cache.
Monitor Query Cache
After enabling query caching, it is important to understand whether it has been used effectively. MySQL has several variables that can be viewed to understand the situation in the cache. Listing 2 shows the status of the cache.
Listing 2: Display the statistics of query cache
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| Qcache_free_blocks | 5216 |
| Qcache_free_memory | 14640664 |
| Qcache_hits | 2581646882 |
| Qcache_inserts | 360210964 |
| Qcache_lowmem_prunes | 281680433 |
| Qcache_not_cached | 79740667 |
| Qcache_queries_in_cache | 16927 |
| Qcache_total_blocks | 47042 |
+-------------------------+------------+
8 rows in set (0.00 sec) The explanation of these items is shown in Table 1.
Table 1. MySQL Query Cache Variables
Variable Name Description
The number of adjacent memory blocks in the QCache_free_blocks cache. A large number indicates that there may be fragments. FLUSH QUERY CACHE will collate the fragments in the cache to get a free block.
QCache_free_memory Free memory in the cache.
QCache_hits increases every time a query hits in the cache.
QCache_inserts increases each time a query is inserted. The hit times divided by the insert times is the miss ratio; Subtract this value from 1 to get the hit rate. In the above example, about 87% of queries are hit in the cache.
The number of times that the QCache_lowmem_prunes cache ran out of memory and had to be cleaned up to provide space for more queries. It is better to look at this number for a long time; If the number keeps growing, it means that the fragmentation may be very serious or the memory may be very small. (The free_blocks and free_memory above can tell you which situation you belong to.).
Qcache_not_cached The number of queries that are not suitable for caching, usually because these queries are not SELECT statements.
The number of queries (and responses) currently cached by QCache_queries_in_cache.
Qcache_total_blocks The number of blocks in the cache.
Usually, the difference can be seen by displaying these variables every few seconds, which can help determine whether the cache is being used effectively. Running FLUSH STATUS can reset some counters, which is very helpful if the server has been running for some time.
The idea of using a very large query cache to cache everything is very tempting. Because mysqld must maintain the cache, such as pruning when the memory becomes low, the server may be in trouble when trying to manage the cache. As a rule, if FLUSH QUERY CACHE takes a long time, it means that the cache is too large.
Mandatory restrictions
You can enforce some restrictions in mysqld to ensure that the system load will not lead to resource exhaustion. Listing 3 shows some important resource related settings in my. cnf.
Listing 3: MySQL resource settings
set-variable=max_connections=500
set-variable=wait_timeout=10
max_connect_errors = 100
The maximum number of connections is managed in the first line. Similar to MaxClients in Apache, the idea is to ensure that only the number of connections allowed by the service are established. To determine the maximum number of connections currently established on the server, execute SHOW STATUS LIKE 'max_used_connections'.
Line 2 tells mysqld to terminate all connections that have been idle for more than 10 seconds. In LAMP applications, the time taken to connect to the database is usually the time taken by the Web server to process the request. Sometimes, if the load is too heavy, the connection will hang and occupy the connection table space. If there are multiple interactive users or the persistent connection to the database is used, it is not advisable to set this value lower!
The last line is a safe method. If a host has problems connecting to the server and gives up after many retries, the host will be locked until FLUSH HOSTS. By default, 10 failures are enough to cause a lock. Changing this value to 100 will give the server enough time to recover from the problem. If the connection cannot be established after 100 retries, no matter how high the value is, it will not help much. It may not be able to connect at all.
Buffer and cache
MySQL supports more than 100 adjustable settings; But fortunately, mastering a few can meet most needs. To find the correct values of these settings, you can view the status variables through the SHOW STATUS command, from which you can determine whether the operation of mysqld meets our expectations. The memory allocated for buffers and caches cannot exceed the existing memory in the system, so tuning usually requires some compromise.
MySQL adjustable settings can be applied to the entire mysqld process, or to a single client session.
Server side settings
Each table can be represented as a file on the disk, which must be opened before being read. To speed up the process of reading data from files, mysqld caches these open files. The maximum number is specified by table_cache in/etc/mysqld.conf. Listing 4 shows how to display activities related to opening a table.
Listing 4: Display the activity of opening the table
mysql> SHOW STATUS LIKE 'open%tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 5000 |
| Opened_tables | 195 |
+---------------+-------+
2 rows in set (0.00 sec)
Listing 4 shows that 5000 tables are currently open, and 195 tables need to be opened, because there are no available file descriptors in the cache (because the statistics have been cleared previously, there may be only 195 open records in 5000 open tables). If Opened_tables increases rapidly as you rerun the SHOW STATUS command, it indicates that the cache hit ratio is insufficient. If Open_tables is much smaller than the table_cache setting, it means that the value is too large (but it is not a bad thing to have room to grow). For example, use table_cache=5000 to adjust the table cache.
Similar to the table cache, there is also a cache for threads. When mysqld receives a connection, it generates threads as needed. On a busy server with rapidly changing connections, caching threads for later use can speed up the initial connection.
Listing 5 shows how to determine whether enough threads are cached.
mysql> SHOW STATUS LIKE 'threads%';
+-------------------+--------+
| Variable_name | Value |
+-------------------+--------+
| Threads_cached | 27 |
| Threads_connected | 15 |
| Threads_created | 838610 |
| Threads_running | 3 |
+-------------------+--------+
4 rows in set (0.00 sec)
The important value here is Threads_created. This value will increase every time mysqld needs to create a new thread. If this number increases rapidly when the SHOW STATUS command is executed continuously, you should try to increase the thread cache. For example, you can use thread_cache=40 in my.cnf to achieve this purpose.
The keyword buffer holds the index block of the MyISAM table. Ideally, requests for these blocks should come from memory, not from disk. Listing 6 shows how to determine how many blocks are read from disk and how many blocks are read from memory.
Listing 6. Determining keyword efficiency
mysql> show status like '%key_read%';
+-------------------+-----------+
| Variable_name | Value |
+-------------------+-----------+
| Key_read_requests | 163554268 |
| Key_reads | 98247 |
+-------------------+-----------+
2 rows in set (0.00 sec)
Key_reads represents the number of requests hitting the disk, and Key_read_requests is the total number. The number of read requests that hit the disk divided by the total number of read requests is the miss ratio - in this example, for every 1000 requests, about 0.6 miss memory. If the number of disk hits exceeds 1 in every 1000 requests, you should consider increasing the keyword buffer. For example, key_buffer=384M will set the buffer to 384MB.
Temporary tables can be used in more advanced queries, where data must be saved to temporary tables before further processing (such as GROUP BY); Ideally, create temporary tables in memory. However, if the temporary table becomes too large, it needs to be written to disk. Listing 7 shows the statistics related to temporary table creation.
Listing 7. Determining the use of temporary tables
mysql> SHOW STATUS LIKE 'created_tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 30660 |
| Created_tmp_files | 2 |
| Created_tmp_tables | 32912 |
+-------------------------+-------+
3 rows in set (0.00 sec)
Each time a temporary table is used, Created_tmp_tables will be increased; Disk based tables also increase Created_tmp_disk_tables. There are no strict rules for this ratio, because it depends on the queries involved. If you observe Created_tmp_disk_tables for a long time, the ratio of the created disk table will be displayed. You can determine the efficiency of the settings. Both tmp_table_size and max_heap_table_size can control the maximum size of temporary tables, so please ensure that both values are set in my.cnf.
Settings per session
The following settings are specific to each session. Be careful when setting these numbers, because when they are multiplied by the number of possible connections, these options represent a large amount of memory! You can modify these numbers in the session through code, or modify these settings for all sessions in my.cnf.
When MySQL must sort, it will allocate a sort buffer to store these data rows when reading data from the disk. If the data to be sorted is too large, the data must be saved to a temporary file on the disk and sorted again. If the sort_merge_passes status variable is large, this indicates the disk activity. Listing 8 shows some status counter information related to sorting.
Listing 8: Display sorting statistics
mysql> SHOW STATUS LIKE "sort%";
+-------------------+---------+
| Variable_name | Value |
+-------------------+---------+
| Sort_merge_passes | 1 |
| Sort_range | 79192 |
| Sort_rows | 2066532 |
| Sort_scan | 44006 |
+-------------------+---------+
4 rows in set (0.00 sec)
If sort_merge_passes is large, it means that attention should be paid to sort_buffer_size. For example, sort_buffer_size=4M sets the sort buffer to 4MB.
MySQL will also allocate some memory to read tables. Ideally, the index provides enough information to read only the required rows, but sometimes queries (poorly designed or due to data nature) need to read a large amount of data in the table. To understand this behavior, you need to know how many SELECT statements have been run, and how many times you need to read the next row of data in the table (instead of directly accessing it through an index). The command to implement this function is shown in Listing 9.
Listing 9. Determining the table scan ratio
mysql> SHOW STATUS LIKE "com_select";
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Com_select | 318243 |
+---------------+--------+
1 row in set (0.00 sec)
mysql> SHOW STATUS LIKE "handler_read_rnd_next";
+-----------------------+-----------+
| Variable_name | Value |
+-----------------------+-----------+
| Handler_read_rnd_next | 165959471 |
+-----------------------+-----------+
1 row in set (0.00 sec)
Handler_read_rnd_next/Com_select gives the table scan ratio -- 521:1 in this case. If the value exceeds 4000, you should check read_buffer_size, for example, read_buffer_size=4M. If this number exceeds 8M, it is time to discuss tuning these queries with developers!
3 indispensable tools
Although the SHOW STATUS command is very useful for understanding specific settings, you also need some tools to interpret the large amount of data provided by mysqld. I found that three tools are indispensable; You can find the corresponding link in the Resources section.
Most system administrators are very familiar with the top command, which provides an updated view of the CPU and memory consumed by the task. Mytop simulates top; It provides a view of all connected clients and the queries they are running. Mytop also provides a real-time data and historical data about keyword buffer and query cache efficiency, as well as statistical information about running queries. This is a very useful tool to view the status of the system (for example, within 10 seconds). You can get a view of server health information and display any connections that cause problems.
Mysqlard is a daemon connected to the MySQL server. It is responsible for collecting data every 5 minutes and storing them in a Round Robin Database in the background. A Web page will display these data, such as the usage of table cache, keyword efficiency, connected clients, and temporary table usage. Although Mytop provides snapshots of server health information, mysqlard provides long-term health information. As a reward, mysqlard uses some information collected by itself to give some suggestions on how to tune the server.
Another tool for collecting SHOW STATUS information is mysqlreport. Its report is much more complex than mysqlard, because it needs to analyze every aspect of the server. This is a very good tool for tuning the server, because it calculates the state variables appropriately to help determine which problems need to be corrected.
Conclusion
This article introduces some basic knowledge about tuning MySQL, and summarizes this three part series on tuning LAMP components. To a large extent, tuning requires understanding the working principle of components, determining whether they work properly, making some adjustments, and re evaluating them. Each component -- Linux, Apache, PHP, or MySQL -- has various requirements. Understanding each component separately can help reduce bottlenecks that may slow down the application.
Information source Shangpin China: Daxing District website production, Daxing District website design, Daxing District website construction company
.jpg)
