Shulou(Shulou.com)05/31 Report--

This article is about how to tune the MySQL server. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

On MySQL tuning

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

Replace the hardware in question.

Tune the settings for the MySQL process.

Optimize the query.

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 type of load it will bear. It is better to speed up the disk operation than to reduce the number of disk accesses required. Similarly, make sure

The correct operation of the MySQL process 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. Replacing problematic hardware is usually our first consideration, mainly because the database takes up a lot of resources. But that's the end of the solution. In fact, you can usually double the speed of the central processing unit (CPU) or disk, or increase the memory

Four to eight times.

The best way is to make sure 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 takes full advantage of MySQL capabilities. Although this article does not cover query tuning (which has been discussed in many books), it configures mysqld to report queries that may need to be tuned.

Although the order has been assigned to these tasks, it is important to pay attention to the hardware and mysqld settings to facilitate proper tuning of the query. It's just that the machine is slow. I've seen fast machines fail to run well-designed queries because they are overloaded because mysqld is too busy to serve queries.

Go back to the top of the page

Record slow query

In a SQL server, data tables are stored on disk. The index provides the server with a way 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 wastes a lot of disk I and O, and therefore a lot of time. This problem is even more complicated when the data must be connected, because multiple rows of data at both ends of the connection must be compared.

Of course, table scans don't always cause problems; sometimes it's more efficient to read the entire table than to pick out some data from it (the query planner in the server process is used to make these decisions). If the use of the index is inefficient, or if the index cannot be used at all, the query speed will be slowed down, and the problem will become more significant as the load and table size on the server increases. A query whose execution time exceeds a given time range is called a slow query.

You can configure mysqld to log these slow queries to appropriately named slow query logs. The administrator then looks at the log to help them determine which parts of the application need further investigation. List

1 gives the configuration that needs to be done in my.cnf to enable slow query logging.

Listing 1. Enable MySQL slow query log [mysqld]; enable the slow query log, default 10 secondslog-slow-queries; log queries taking longer than 5 secondslong_query_time = 5; log queries that don't use indexes even if they take less than long_query_time; MySQL 4.1 and newer onlylog-queries-not-using-indexes

Using these three settings together, you can record queries that take more than 5 seconds to execute and that do not use indexes. Please note the warning about log-queries-not-using-indexes: you must use the

MySQL version 4.1 or later. The slow query logs are saved in the MySQL data directory, called hostname-slow.log. If you want to use a different name or path, you can use log-slow-queries in my.cnf

= / new/path/to/file to achieve this.

Reading the slow query log is best done through the mysqldumpslow command. By specifying the path to the log file, you can see a sorted list of slow queries and 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 the results, so different calls to the same query are counted once; this helps to find the query that requires the most effort.

Go back to the top of the page

Cache queries

Many LAMP applications rely heavily on databases, but execute the same queries repeatedly. Each time a query is executed, the database must do the same thing-- analyze the query, determine how to execute the query, load information from disk, and then return the results to the client. MySQL has a feature called query caching, which stores query results in memory (which will be used later). In many cases, this can greatly improve performance. The problem, however, is that query caching is disabled by default.

Query_cache_size

Add 32m to / etc/my.conf to enable query caching for 32MB.

Monitor query cache

After enabling query caching, it is important to understand whether it is used effectively. MySQL has several variables that can be viewed to understand what's going on in the cache. Listing 2 shows the cache status.

Listing 2. Display query cache statistics mysql > SHOW STATUS LIKE 'qcache%' +-+-+ | 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 (sec) |

The explanations of these items are shown in Table 1.

Table 1. The MySQL query cache variable name indicates the number of adjacent memory blocks in the Qcache_free_blocks cache. A large number means there may be fragments. FLUSH

QUERY CACHE will defragment the cache to get a free block. Free memory in the Qcache_free_memory cache. Qcache_hits grows every time a query hits in the cache. Qcache_inserts grows every time you insert a query. The miss ratio is the number of hits divided by the number of insertions; subtracting this value from 1 is the hit ratio. In the above example, about 87% of the queries are hit in the cache. The number of times that the Qcache_lowmem_prunes cache is out of memory and must be cleaned to provide space for more queries. This number is best seen over a long period of time; if it is growing, it means that the fragmentation may be very serious, or there may be very little memory. (the free_blocks and free_memory above can tell you which case it is.) The number of queries that Qcache_not_cached is not suitable for caching, usually because they are not SELECT statements. The number of queries (and responses) currently cached by Qcache_queries_in_cache. The number of blocks in the Qcache_total_blocks cache.

In general, the difference can be seen by displaying these variables at intervals of a few seconds, which can help determine whether the cache is being used effectively. Run FLUSH

STATUS can reset some counters, which can be very helpful if the server has been running for some time.

It is tempting to use a very large query cache and expect to cache everything. Because mysqld has to maintain the cache, such as performing pruning when memory becomes low, the server may get into trouble trying to manage the cache. As a rule, if FLUSH

QUERY CACHE takes a long time, which means the cache is too large.

Go back to the top of the page

Compulsory restriction

You can impose some restrictions in mysqld to ensure that the system load does not cause resource exhaustion. List

3 some important settings related to resources in my.cnf are given.

Listing 3. MySQL resource setting set-variable=max_connections=500set-variable=wait_timeout=10max_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 stop all idle time exceeding

A 10-second connection. In LAMP applications, the time it takes to connect to the database is usually the time it takes the Web server to process the request. Sometimes, if the load is too heavy, the connection hangs and takes up the connection table space. If you have multiple interactive users or use a persistent connection to the database, it is not advisable to set this value low!

The last line is a safe way. If a host has a problem connecting to the server and gives up after retrying many times, the host will be locked until FLUSH

It will not run until after HOSTS. By default, 10 failures is enough to cause a lock. Changing this value to 100 will give the server enough time to recover from the problem. If you can't establish a connection after 100 retries, using a high value won't help much, maybe it won't be able to connect at all.

Go back to the top of the page

Buffers and caches

MySQL supports more than 100 adjustable settings; but fortunately, mastering a few can meet most of your needs. The correct values for these settings can be found through SHOW

The STATUS command looks at the status variables to determine whether mysqld is working as we expected. The memory allocated to buffers and caches cannot exceed the existing memory in the system, so tuning usually requires some compromise.

MySQL tunable 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 and must be opened before it is read. To speed up the process of reading data from files, mysqld caches these open files, the maximum number of which is determined by the

Table_cache specified in / etc/mysqld.conf. List

4 shows how to display the activities related to opening the table.

Listing 4. Displays the activity 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 5000 tables open and 195 tables that need to be opened, because there are no file descriptors available in the cache (because the statistics have been cleared previously, so there may be only 195 open records out of 5000 open tables). If Opened_tables reruns SHOW

The rapid increase in STATUS commands indicates that the cache hit ratio is not enough. If Open_tables is much smaller than the table_cache setting, the value is too large (but it's always not a bad thing to have room to grow). For example, using table_cache

= 5000 you can adjust the cache of the table.

Similar to table caching, there is a cache for threads. When mysqld receives a connection, it generates threads as needed. On a busy server where connections change rapidly, caching threads for later use can speed up the initial connection.

Listing 5 shows how to determine if enough threads are cached.

Listing 5. Show thread usage statistics 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 is incremented every time mysqld needs to create a new thread. If this number is performing SHOW continuously

If the STATUS command increases rapidly, you should try to increase the thread cache. For example, you can use thread_cache in my.cnf

= 40 to achieve this.

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. Determine 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 that hit the disk, and Key_read_requests is the total. 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 missed memory. If more than 1 disk is hit for every 1000 requests, you should consider increasing the keyword buffer. For example, key_buffer

= 384m sets the buffer to 384MB.

Temporary tables can be used in more advanced queries where the data is further processed (such as GROUP

Before BY, it must be saved to a temporary table; ideally, a temporary table is created in memory. But 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. Determine 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, the Created_tmp_tables; disk-based table also increases the Created_tmp_disk_tables. There are no strict rules for this ratio because it depends on the query involved. Watching Created_tmp_disk_tables for a long time shows the ratio of the disk table created, and you can determine the efficiency of the setting. Both tmp_table_size and max_heap_table_size can control the maximum size of temporary tables, so make sure that in the

Both values are set in my.cnf.

Settings for each session

The following settings are for each session. Be careful when setting these numbers because when multiplying them by the number of connections that may exist, these options represent a lot of memory! You can modify these numbers in the session through code, or you can modify these settings for all sessions in my.cnf.

When MySQL has to sort, a sort buffer is allocated to hold the rows of data when it is read from disk. If the data to be sorted is too large, 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 disk activity. List

8 some status counter information related to sorting is given.

Listing 8. Display sort 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 that you need to pay attention to sort_buffer_size. For example, sort_buffer_size

= 4m sets the sort buffer to 4MB.

MySQL also allocates some memory to read the table. Ideally, the index provides enough information to read only the rows you need, but sometimes a query (poorly designed or by the nature of the data) needs 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 (rather than accessing it directly through the index). The commands that implement this function are shown in the list

9 is shown.

Listing 9. Determine 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 got the table scan ratio.

-- in this case, 521RV 1. If the value is more than 4000, you should check the read_buffer_size, such as read_buffer_size

= 4m. If this number exceeds 8m, it's time to discuss tuning these queries with the developer!

Go back to the top of the page

Three essential tools

Although when learning the specific settings, SHOW

The STATUS command can be very useful, but you also need some tools to interpret the large amount of data provided by mysqld. I found it.

Three tools are essential; you can find the appropriate links in the Resources section.

Most system administrators are familiar with the top command, which is consumed by tasks

CPU and memory provide a constantly updated view. Mytop simulates top; it provides a view of all connected clients and the queries they are running. Mytop also provides real-time and historical data about keyword buffers and query caching efficiency, as well as statistics about running queries. This is a useful tool to view in the system (such as

Within 10 seconds), you can get a view of the server's health information and display any connections that are causing the problem.

Mysqlard is a connection to the

The daemon on the MySQL server is responsible for collecting data every 5 minutes and storing it in a Round Robin Database in the background. A Web page displays this data, such as table cache usage, keyword efficiency, clients on connections, and temporary table usage. 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 has collected to give advice on how to tune the server.

Another tool for collecting SHOW STATUS information is mysqlreport. Its report is much more complex than mysqlard because every aspect of the server needs to be analyzed. 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 fixed.

Thank you for reading! This is the end of the article on "how to tune the MySQL server". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.