LAMP system can optimize MySQL database server Optimization

Nowadays, developers are constantly developing and deploying LAMP (Linux®, Apache, MySQL, PHP/Perl) architecture applications. However, the server administrator often has no control over the application itself, because the application is compiled by others. This three-part series will discuss many server configurations that affect application performance. This article is the third and last part of this series. It will focus on optimizing the database layer for maximum efficiency. There are three methods for MySQL tuning to speed up the running of MySQL servers. The efficiency is as follows: 1. Replace faulty hardware. 2. Tune the MySQL process settings. 3. Optimize the query. Replacing faulty hardware is usually our first consideration, mainly because the database occupies a large amount of resources. However, this solution is limited to this. In fact, you can usually double the speed of the central processor (CPU) or disk, or increase the memory by 4 to 8 times. The second method is to optimize the MySQL server (also called mysqld. Tuning this process means allocating memory appropriately and letting mysqld know what type of load it will bear. Speed up disk operation is not as fast as reducing the number of disk accesses required. Similarly, ensuring that the MySQL process operates correctly means that it spends more time on service queries than processing 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 make sure 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 fully utilizes the MySQL function. Although this article does not include the content of the query optimization (many books have already discussed this topic), it will configure mysqld to report the query that may require optimization. Although the order has been assigned for these tasks, you should still pay attention to the hardware and mysqld settings to facilitate proper query optimization. The machine speed is too slow. I have seen very fast Machines fail due to heavy load when running well-designed queries, because mysqld is occupied by a lot of busy work and cannot be used for service queries. Slow query of records is stored in an SQL Server, and data tables are stored on disks. Indexes provide a method for the server to search for specific data rows in a table instead of the entire table. To search for the entire table, it is called a table scan. Generally, you may only want to obtain a subset of the data in the table. Therefore, full table scan will waste a lot of disk I/O, and thus a lot of time. When data must be connected, this problem is more complicated, because multi-row data at both ends of the connection must be compared. Of course, table scanning does not always cause problems. Sometimes reading the entire table is more effective than selecting a part of the data from it (the query planner in the server process is used to make these decisions ). If the index is very inefficient or cannot be used at all, the query speed will be slowed down and the problem will become more significant as the server load and table size increase. A query whose execution time exceeds the specified time range is called a slow query. You can configure mysqld to record these slow queries to the named slow query log. The Administrator then checks the log to help them determine which parts of the application need further investigation. Listing 1 shows the configuration that needs to be done in my. cnf to enable slow query logs. Listing 1. enable MySQL slow query log 1 [mysqld] 2; enable the slow query log, default 10 seconds3 log-slow-queries4; log queries taking longer than 5 seconds5 long_query_time = 56; log queries that don't use indexes even if they take less than long_query_time7; MySQL 4.1 and newer only8 log-queries-not-using-indexes are used together, records queries that have been executed for more than 5 seconds and are not indexed. Note the warning about log-queries-not-using-indexes: You must use MySQL 4.1 or later. 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 to achieve this goal. Read slow query logs by using the mysqldumpslow command. Specify the log file path to view the sorted list of slow queries, and display the number of times they appear in the log file. A very useful feature is that mysqldumpslow deletes data specified by any user before comparing results. Therefore, different calls to the same query are counted as one; this can help you find the query that requires the most workload. Caching queries many LAMP applications rely heavily on databases, but they repeatedly execute the same query. Each time a query is executed, the database must perform the same job-analyze the query, determine how to execute the query, load the information from the disk, and then return the result to the client. MySQL has a feature called query cache, which stores the query results (which will be used later) in memory. In many cases, this will greatly improve the performance. However, the problem is that the query cache is disabled by default. You can add query_cache_size = 32 M to/etc/my. conf to enable the 32 MB query cache. After the query cache is enabled, it is important to understand whether it is effectively used. MySQL has several variables that can be viewed and used to understand the cache conditions. Listing 2 shows the cache status. List 2. display the query cache statistics 1 mysql> show status like 'qcache % '; 2 + metrics + ------------ + 3 | Variable_name | Value | 4 + metrics + ------------ + 5 | metrics | 5216 | 6 | Qcache_free_memory | 14640664 | 7 | Qcache_hits | 2581646882 | 8 | Qcache_inserts | 360210964 | 9 | Qcache_lowmem_prunes | 281680433 | 10 | Qcache_not_cached | 79740667 | 11 | Qcache_queries_in_cache | 16927 | 12 | Qcache_total_blocks | 47042 | 13 + ------------------------- + ------------ + 14 8 rows in set (0.00 sec), as shown in table 1. The variable name indicates the number of adjacent memory blocks in the Qcache_free_blocks cache. A large number of fragments may exist. Flush query cache sorts the fragments in the CACHE to obtain an idle block. Qcache_free_memory idle memory in the cache. Qcache_hits increases when the query hits the cache. Qcache_inserts increases every time a query is inserted. The number of hits divided by the number of inserts is the ratio of not to the middle, and the value minus 1 is the hit rate. In the above example, about 87% of queries are hit in the cache. Qcache_lowmem_prunes the cache is out of memory and must be cleaned up to provide more space for queries. It would be better to look at this number for a long time; if this number continues to grow, it may indicate that the fragmentation is very serious, or the memory is very small. (The free_blocks and free_memory above can tell you what the situation is ). Qcache_not_cached is not suitable for the number of cache queries, usually because these queries are not SELECT statements. Qcache_queries_in_cache the number of queries (and responses) cached currently. Qcache_total_blocks. Normally, you can see the difference when the variables are displayed several seconds later, which helps determine whether the cache is being effectively used. Running flush status can reset some counters. If the server has been running for a while, this will be very helpful. The idea of using a very large query cache to cache everything is very attractive. Because mysqld must maintain the cache, for example, when the memory becomes low, the server may be in trouble when trying to manage the cache. As a rule, if the flush query cache takes a long time, it indicates that the CACHE is too large. You can force some restrictions in mysqld to ensure that the system load does not cause resource depletion. Listing 3 shows some important resource-related settings in my. cnf. Listing 3. MySQL resource setting 1 max_connections = 5002 wait_timeout = 103 max_connect_errors = 100 the maximum number of connections is managed in the first row. Similar to MaxClients in Apache, the idea is to ensure that only connections allowed by the service are established. To determine the maximum number of connections currently established on the server, run show status like 'max _ used_connections '. Row 3 tells mysqld to terminate all connections that have been idle for more than 10 seconds. In LAMP applications, the time spent connecting to the database is usually the time that the Web server takes to process requests. Sometimes, if the load is too heavy, the connection will be suspended and will occupy the connection tablespace. If multiple interactive users or persistent connections to the database are used, it is not advisable to set this value to a lower value! The last line is a secure method. If a host has problems connecting to the server and does not give up after many retries, the host will be locked until the host is flushed. By default, 10 failures are enough to cause the lock. Changing this value to 100 will give the server enough time to recover from the problem. If a connection cannot be established after 100 retries, using a higher value will not help much, and it may not be able to connect at all. The buffer and cache MySQL support more than 100 Adjustable settings, but fortunately, a few can meet most of the needs. To find the correct values of these settings, you can run the show status command to view the STATUS variables and determine whether the operation of mysqld meets our expectations. The memory allocated to the buffer and cache cannot exceed the existing memory in the system. Therefore, tuning usually requires some compromise. MySQL Adjustable settings can be applied to the entire mysqld process, or to a single client session. Each table set on the server can be represented as a file on the disk. It must be opened first and then read. To speed up the process of reading data from files, mysqld caches these open files. The maximum number of these files is specified by table_cache in/etc/mysqld. conf. Listing 4 shows how to display activities related to opening a table. Listing 4. SHOW the activity of opening a table 1 mysql> show status like 'open % tables '; 2 + --------------- + ------- + 3 | Variable_name | Value | 4 + --------------- + ------- + 5 | Open_tables | 5000 | 6 | Opened_tables | 195 | 7 + ------------- + ------- + 8 2 rows in set (0.00 sec) listing 4 shows that 5,000 tables are currently opened, and 195 tables need to be opened, because no available file descriptor is available in the cache (because the statistics have been cleared before, therefore, there may be 5,000 open records in 195 open tables ). If Opened_tables rapidly increases with the re-running of the show status Command, the cache hit rate is insufficient. If Open_tables is much smaller than table_cache, it indicates that the value is too large (but it is not a bad thing to increase the space ). For example, you can use table_cache = 5000 to adjust the table cache. Similar to the table cache, a thread also has a cache. Mysqld will generate a thread as needed when receiving connections. On a busy server with fast connection changes, caching threads for later use can speed up the initial connection. Listing 5 shows how to determine whether enough threads are cached. Listing 5. display thread usage statistics 1 mysql> show status like 'threads % '; 2 + threads + -------- + 3 | Variable_name | Value | 4 + ------------------- + -------- + 5 | Threads_cached | 27 | 6 | Threads_connected | 15 | 7 | Threads_created | 838610 | 8 | Threads_running | 3 | 9 + ------------------- + -------- + 10 4 rows in set (0.00 sec) the important value here is Threads_created. This value increases 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, thread_cache = 40 can be used in my. cnf to achieve this goal. The keyword buffer stores the index block of the MyISAM table. Ideally, requests for these blocks should come from memory, not from disks. Listing 6 shows how to determine how many blocks are read from the disk and how many are read from the memory. Listing 6. determine keyword efficiency 1 mysql> show status like '% key_read % '; 2 + rows + ----------- + 3 | Variable_name | Value | 4 + ------------------- + ----------- + 5 | Key_read_requests | 163554268 | 6 | Key_reads | 98247 | 7 + rows + ----------- + 8 2 rows in set (0.00 sec) key_reads indicates the number of requests that hit the disk, and Key_read_requests indicates the total number. The number of read requests that hit the disk divided by the total number of read requests is the ratio of not hit-in this example, each 1,000 requests, about 0.6 do not hit the memory. If the number of disks hit by each 1,000 requests exceeds 1, you should consider increasing the keyword buffer. For example, if key_buffer = 384 M, the buffer is set to 384 MB. Temporary tables can be used in more advanced queries. data must be saved to temporary tables before further processing (such as group by statements). Ideally, create a temporary table in the memory. However, if the temporary table is too large, it needs to be written to the disk. Listing 7 shows the statistics related to creating a temporary table. Listing 7. make sure that the temporary table uses 1 mysql> show status like 'created _ tmp % '; 2 + tables + ------- + 3 | Variable_name | Value | 4 + tables + ------- + 5 | Created_tmp_disk_tables | 30660 | 6 | Created_tmp_files | 2 | 7 | Created_tmp_tables | 32912 | 8 + tables + ------- + 9 3 rows in set (0.00 sec) created_tmp_tables is increased each time a temporary table is used, and Created_tmp_disk_tables is increased for a disk-based table. There are no strict rules for this ratio, because it depends on the involved query. After a long period of observation, Created_tmp_disk_tables will display the ratio of the created disk table. You can determine the efficiency of the settings. Both tmp_table_size and max_heap_table_size can control the maximum size of the temporary table. Therefore, make sure that both values are set in my. cnf. The following settings apply to each session. Exercise caution when setting these numbers because these options indicate a large amount of memory when multiplied by the number of possible connections! You can use the code to modify the numbers in a session, or modify these settings for all sessions in my. cnf. When MySQL needs to sort data, it will allocate a sort buffer when reading data from the disk to store the data rows. If the data to be sorted is too large, the data must be saved to the temporary files on the disk and sorted again. If the sort_merge_passes status variable is large, it indicates disk activity. Listing 8 lists some status counters related to sorting. Listing 8. SHOW sorting statistics 1 mysql> show status like "sort % "; 2 + rows + --------- + 3 | Variable_name | Value | 4 + ------------------- + --------- + 5 | Sort_merge_passes | 1 | 6 | Sort_range | 79192 | 7 | Sort_rows | 2066532 | 8 | Sort_scan | 44006 | 9 + ------------------- + --------- + 10 4 rows in set (0.00 sec) if sort_merge_passes is large, you need to pay attention to sort_buffer_size. For example, sort_buffer_size = 4 M sets the sorting buffer to 4 MB. MySQL also allocates some memory to read the table. Ideally, the Index provides enough information to read only the required rows, but sometimes the query (poor design or data nature) needs to read a large amount of data in the table. To understand this behavior, you need to know how many SELECT statements are run, and the number of times the data in the next row in the table needs to be read (instead of directly accessed through the index ). The command to implement this function is shown in listing 9. Listing 9. determine the table scan rate 1 mysql> show status like "com_select "; 2 + --------------- + -------- + 3 | Variable_name | Value | 4 + --------------- + -------- + 5 | Com_select | 318243 | 6 + --------------- + -------- + 7 1 row in set (0.00 sec) 8 9 mysql> show status like "handler_read_rnd_next"; 10 + ----------------------- + ----------- + 11 | Variable_name | Value | 12 + bytes + ----------- + 13 | Handler_read_rnd_next | 165959471 | 14 + ----------------------- + ----------- + 15 1 row in set (0.00 sec) Handler_read_rnd_next/Com_select get the table scan rate-in this example, It is 521: 1. If the value exceeds 4000, check read_buffer_size, for example, read_buffer_size = 4 M. If this number exceeds 8 Mb, you should discuss with the developer about tuning these queries! Although the show status command is very useful when you understand specific settings, you also need some tools to explain the large amount of data provided by mysqld. I found that three tools are essential. You can find the corresponding links in the references section. Most system administrators are very familiar with the top command, which provides a continuously updated view of the CPU and memory consumed by the task. Mytop simulates top. It provides a view for all connected clients and their running queries. Mytop also provides real-time data and historical data about the keyword buffer and query cache efficiency, as well as statistics about running queries. This is a very useful tool that allows you to view the status of the system (for example, within 10 seconds), you can get a view of server health information, and display any connection that causes the problem. Mysqlard is a daemon connecting to the MySQL server. It collects data every five minutes and stores the data in a Round Robin Database in the background. A Web page displays the data, such as table Cache Usage, keyword efficiency, connected clients, and usage of temporary tables. Although mytop provides snapshots of server health information, mysqlard provides long-term health information. As a reward, mysqlard uses some information it collects to give some suggestions on how to optimize the server. Another tool used to collect show status information is mysqlreport. Its report is far more complex than mysqlard, because it needs to analyze every aspect of the server. This is a very good tool for server tuning, because it performs proper calculation of state variables to help determine which problems need to be fixed