What are the ways to speed up the MySQL server?

About MySQL Tuning

There are 3 ways to speed up the MySQL server, from low to high efficiency:

Replace the problematic hardware. Tuning the settings for the MySQL process. Optimize the query.

Replacing the problematic hardware is often our first consideration, mainly because the database is resource intensive. However, this solution is limited to this. In fact, you can usually double the CPU or disk speed, or you can increase the memory by 4 to 8 times times.

The second approach is to tune the MySQL server (also known as MYSQLD). Tuning this process means allocating memory appropriately and letting mysqld know what type of load it will withstand. Speeding up disk runs less than the required number of disk accesses. Similarly, ensuring that the MySQL process is working correctly means that it spends more time on service queries than it spends on processing background tasks, such as working with temporary disk tables or opening and closing files. Tuning the mysqld is the focus of this paper.

The best way to do this is to ensure that the query has been optimized. This means that the appropriate index is applied to the table, and the query is written in a way that makes full use of MySQL functionality. Although this article does not contain content for query tuning (this topic has been explored in many books), it configures MYSQLD to report queries that might require tuning.

Although these tasks have been assigned a sequence, it is important to note the hardware and mysqld settings to facilitate proper tuning of queries. The speed of the machine is just, I have seen the speed of the machine in the run well-designed queries due to heavy load and failure, because MYSQLD is occupied by a lot of busy work and can not service query.

Record Slow queries

In a SQL server, the data tables are saved on disk. Indexes provide a way for the server to find specific rows of data in a table without searching the entire table. When you have to search the entire table, it is called a table scan. In general, you may only want to get a subset of the data in the table, so a full table scan can waste a lot of disk I/O and therefore waste a lot of time. This problem is even more complicated when data must be connected, because multiple rows of data must be compared on both ends of the connection.

Of course, table scans do not always cause problems; it is sometimes more efficient to read the entire table than to pick out some of the data from it (the Query planner in the server process is used to make these decisions). If the index is inefficient to use, or if the index is not used at all, it slows down the query and becomes more noticeable as the load on the server and the size of the table increase. Queries that execute longer than a given time range are called slow queries.

You can configure MYSQLD to log these slow queries to the appropriately named slow query log. The administrator then looks at the log to help them determine what parts of the application need further investigation. Listing 1 shows the configuration that needs to be made in my.cnf to enable the slow query log.

Listing 1, enabling the MySQL slow query log

[Mysqld]
; Enable the slow query log, default ten 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

With these three settings, you can record queries that have a execution time of more than 5 seconds and no indexes. Note the warning about log-queries-not-using-indexes: You must use MySQL version 4.1 or later. The slow query log is 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 for this purpose.

It is best to read the slow query log through the Mysqldumpslow command. Specify the path to the log file, and you can see the sorted list of a slow query, and also show how many times they appear in the log file. A very useful feature is that mysqldumpslow deletes any user-specified data before comparing the results, so different calls to the same query are counted as one, which helps to identify the queries that require the most effort.

To cache a query

Many LAMP applications rely heavily on the database, but they repeatedly execute the same query. Every time you execute a query, the database must do the same thing-parse the query, determine how to execute the query, load the information from the disk, and then return the results to the client. MySQL has a feature called Query caching, which stores query results (which are used later) in memory. In many cases, this can greatly improve performance. However, the problem is that the query cache is disabled by default.

Adding query_cache_size = 32M to/etc/my.conf enables the 32MB query cache to be enabled.

Monitoring the query cache

After you enable the query cache, it is important to understand whether it is being used effectively. MySQL has several variables that you can view to see what's happening in the cache. Listing 2 shows the state of the cache.

Listing 2, displaying statistics for query caching

+-------------------------+------------+
| 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 explanations of these entries are shown in table 1.

Table 1, MySQL query cache variables

Variable name Description

The number of contiguous memory blocks in the Qcache_free_blocks cache. A large number indicates that there may be fragmentation. FLUSH QUERY Cache organizes the fragments in the cache to get a free block.

Qcache_free_memory free memory in the cache.

Qcache_hits is incremented each time a query is hit in the cache.

Qcache_inserts is incremented each time a query is inserted. The number of hits divided by the number of insertions is not the middle ratio; subtracting this value by 1 is the hit rate. In the above example, about 87% of the queries are hit in the cache.

The Qcache_lowmem_prunes cache is running out of memory and must be cleaned up to provide space for more queries. This number is best seen over a long period of time, and if the number is growing, it can mean that fragmentation is very serious, or that there is little memory. (The Free_blocks and free_memory above can tell you which is the case).

Qcache_not_cached the number of queries that are not appropriate for caching, usually because these queries are not SELECT statements.

Qcache_queries_in_cache the number of queries (and responses) that are currently cached.

The number of blocks in the qcache_total_blocks cache.

Typically, these variables are displayed at intervals of several seconds to see the difference, which can help determine whether the cache is being used effectively. Running FLUSH STATUS resets some counters, which can be helpful if the server has been running for some time.

With a very large query cache, it's tempting to expect everything to be cached. Because mysqld must maintain the cache, such as pruning when memory becomes very low, the server may get bogged down 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.

Enforce restrictions

You can enforce some restrictions in mysqld to ensure that the system load does not result in resource exhaustion. Listing 3 shows some important resources-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 row. Similar to maxclients in Apache, the idea is to make sure that only the number of connections allowed for the service is established. To determine the maximum number of connections currently established on the server, perform the SHOW STATUS like ' max_used_connections '.

Line 2nd tells Mysqld to terminate all connections that have idle time longer than 10 seconds. In a LAMP application, the time to connect to a database is usually the time it takes for the WEB server to process the request. Sometimes, if the load is heavy, the connection hangs, and the connection table space is consumed. If you have more than one interactive user or use a persistent connection to a database, setting this value down is not advisable!

The last line is a safe method. If a host has a problem connecting to the server, and then tries to discard it many times, the host will be locked until the FLUSH hosts can run. By default, 10 failures are sufficient to cause a lock. Modifying this value to 100 will give the server enough time to recover from the problem. If you cannot make a connection after retrying 100 times, then using a higher value will not help much, and it may not be able to connect at all.

Buffers and caches

MySQL supports more than 100 adjustable settings, but fortunately, mastering a handful of them will satisfy most of the needs. Finding the correct values for these settings allows you to see the status variables through the show Status command, from which you can determine if the mysqld is working as expected. The memory allocated to the buffer and the cache 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 disk, which must be opened first and read later. To speed up the process of reading data from a file, mysqld caches the open files with a maximum number specified by Table_cache in/etc/mysqld.conf. Listing 4 shows how to display activities related to opening a table.

Listing 4, displaying an activity to open a 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 there are currently 5,000 tables open and 195 tables need to be opened because there are no available file descriptors in the cache (since the statistics are cleared earlier, there can be only 195 open records in 5,000 open tables). If opened_tables quickly increases with the SHOW STATUS command rerun, the cache hit ratio is insufficient. If the open_tables is much smaller than the Table_cache setting, it means that the value is too large (but it's not a bad thing to have room to grow). For example, you can use Table_cache = 5000 to adjust the table's cache.

Similar to the cache for a table, there is also a cache for threads. MYSQLD generates threads as needed when it receives a connection. On a busy server with rapidly changing connections, caching the threads for later use can speed up the initial connection.

Listing 5 shows how to determine if enough threads are being 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, which increases each time mysqld needs to create a new thread. If this number increases rapidly as you execute the SHOW STATUS command consecutively, you should try increasing the thread cache. For example, you can use Thread_cache = 40来 in my.cnf for 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 are read from memory.

Listing 6, determining the efficiency of keywords

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 to hit a disk, key_read_requests is the total number. The number of read requests for the hit disk divided by the total number of read requests is not the middle ratio--in this case, about 0.6 of each 1,000 requests do not hit memory. If you hit more than 1 disks per 1,000 requests, you should consider increasing the keyword buffers. 2881064151 for example, Key_buffer = 384M Sets the buffer to 384MB.

Temporary tables can be used in more advanced queries, where data must be saved to a temporary table before further processing (for example, GROUP by); Ideally, temporary tables are created in memory. However, if the temp table becomes too large, it needs to be written to disk. Listing 7 shows the statistics related to the creation of the temporary table.

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)

Created_tmp_tables is incremented each time a temporary table is used, and disk-based tables increase created_tmp_disk_tables. There is no strict rule for this ratio, because it relies on the queries involved. Long observation created_tmp_disk_tables will show the ratio of the disk tables created, 2881064151 you can determine the efficiency of the setup. Both Tmp_table_size and max_heap_table_size can control the maximum size of the staging table, so make sure that both values are set in the MY.CNF.

Settings for each session

The following settings are for each session. Be very cautious when setting these numbers because they represent a lot of memory when multiplied by the number of connections that may exist! You can modify these numbers in the session through code, or modify these settings for all sessions in MY.CNF.

When MySQL has to be sorted, it allocates a sort buffer to hold the data rows when the data is read from the disk. If the data to be sorted is too large, then the data must be saved to a temporary file on disk and sorted again. If the sort_merge_passes state variable is large, this indicates the activity of the disk. Listing 8 shows some status counter information related to sorting.

Listing 8, displaying 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 the sort_merge_passes is large, it means to pay attention to sort_buffer_size. For example, sort_buffer_size = 4M Sets the sort buffer to 4MB.

MySQL will also allocate some memory to read the table. Ideally, the index provides enough information to read only the required rows, but sometimes the query (poorly designed or by the nature of the data) needs to read large amounts of data in the table. To understand this behavior, you need to know how many SELECT statements are running and the number of times the next row of data in the table needs to be read (rather than directly accessed through the index). The command to implement this functionality 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 the table scan ratio-in this case, 521:1. 2881064151 if the value exceeds 4000, you should see read_buffer_size, for example read_buffer_size = 4M. If this number exceeds 8M, it should be discussed with developers to tune these queries!

3 Essential Tools

Although the SHOW STATUS command is useful when you are learning about specific settings, you also need tools to explain the large amount of data that MYSQLD provides. I found that there are 3 tools that are essential, and you can find the appropriate links in the Resources section.

Most system administrators are familiar with the top command, which provides a constantly updated view of the CPU and memory consumed by the task. Mytop the top simulation; it provides a view of the clients on all connections and the queries they are running. Mytop also provides a real-time and historical data about the efficiency of keyword buffers and query caching, as well as statistics about running queries. This is a useful tool to view the status of your system (for example, within 10 seconds), you can get a view of your server health information, and display any connections that are causing the problem.

Mysqlard is a daemon connected to a MySQL server that collects data every 5 minutes and stores them in a Round Robin Database in the background. There is a Web page that displays this data, such as the usage of table caches, keyword efficiency, the client on the connection, and the usage of temporary tables. Although Mytop provides a snapshot of server health information, Mysqlard provides long-term health information. As a reward, Mysqlard uses some of the information he collects to give some advice on how to tune the server.

Another tool for collecting SHOW STATUS information is mysqlreport. Its report is far more complex than mysqlard, because every aspect of the server needs to be analyzed. This is a great tool for tuning the server because it calculates the state variables appropriately to help determine which issues need to be corrected.

What are the ways to speed up the MySQL server?