MySQL server's several tuning skills, so that the MySQL server running fast

About MySQL Tuning
There are 3 ways to speed up the running of the MySQL server, from low to high efficiency:
Replace the problematic hardware.
Tuning the MySQL process settings.
Optimize the query.

Replacing the problematic hardware is usually our first consideration, mainly because the database consumes a lot of resources. But this is only the solution. In fact, you can usually double the central processing unit (CPU) or disk speed, or you can increase 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 kind of load it will withstand. Speed up disk operation less than you need to reduce the number of disk accesses. Similarly, ensuring that the MySQL process is working correctly means that it spends more time on the service query than it spends on processing background tasks, such as processing temporary disk tables or opening and closing files. The emphasis of this paper is to adjust the mysqld.
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 makes full use of the MySQL functionality. Although this article does not contain content for query tuning (which is discussed in many books), it configures MYSQLD to report queries that might require tuning.
Although orders have been assigned to these tasks, you still need to be aware of the hardware and mysqld settings to facilitate appropriate tuning of the query. The speed of the machine is just too slow, I've seen a machine that's fast. A well-designed query failed because it was overloaded because the mysqld was occupied by a lot of busy work and could not service the query.

Record slow Queries
on a SQL server, the data tables are stored on disk. The index provides a way for the server to find a specific row 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 want to get only a subset of the data in a table, so a full table scan wastes a lot of disk I/O and therefore wastes a lot of time. This problem is even more complicated when data must be connected, because it is necessary to compare the multiple rows of data at both ends of the connection.
Of course, table scans do not always cause problems; sometimes reading the entire table is more efficient than picking up a subset of the data from it (the Query planner in the server process is used to make these decisions). If the index is inefficient or cannot be used at all, the query slows down, and the problem becomes more pronounced as the load and table size increase on the server. Queries that run longer than a given time range are called slow queries.
You can configure MYSQLD to record these slow queries in the appropriately named slow query log. The administrator then looks at the log to help them determine which parts of the application need to be investigated further. Listing 1 shows the configuration that needs to be done in my.cnf to enable the slow query log.
Listing 1. Enable MySQL slow query log

code is as follows	copy code
[mysqld]; Enable the slow query log, default seconds log-slow-queries; Log queries taking longer than 5 seconds = 5; Log queries which don ' t use indexes even if they take less than; MySQL 4.1 and newer only log-queries-not-using-indexes

These three settings are used together to record the execution time of more than 5 seconds and queries that do not use the index. Please note the warning about log-queries-not-using-indexes: You must use MySQL 4.1 or later. The slow query log is stored 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 accomplish this.
Reading a slow query log is best done through the Mysqldumpslow command. Specify the path to the log file to see a 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 once; this helps to identify the queries that require the most work.

To cache a query
Many LAMP applications rely heavily on databases, but they do the same query over and over again. Each time the query is executed, the database must perform the same work?? Analyze the query, determine how to execute the query, load the information from the disk, and return the results to the client. MySQL has an attribute called query caching, which saves the results of queries (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.
Add query_cache_size = 32M to the/etc/my.conf to enable the 32MB query cache.
Monitoring Query Caching
After you enable query caching, it is important to understand whether it is being used effectively. MySQL has several variables that you can view to understand the situation in the cache. Listing 2 shows the state of the cache.
Listing 2. Show statistics for query caching

The code is as follows

Copy Code

mysql> 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 (0.00 sec)

These items are interpreted as shown in table 1.
Table 1. The MySQL query cache variable name describes the number of contiguous blocks of memory in the
qcache_free_blocks   cache. A large number indicates that there may be fragments. FLUSH QUERY Cache will defragment the cache to get a free block.
The free memory in the qcache_free_memory   cache. The
qcache_hits   increases each time a query hits in the cache. The
qcache_inserts   increases each time a query is inserted. The hit count divided by the number of inserts is not in the ratio; 1 minus this value is the hit rate. In the example above, about 87% of the queries were hit in the cache. The
qcache_lowmem_prunes   Cache has a number of times when there is not enough memory and must be cleaned up to provide space for more queries. This number is best for long periods of time; If the number is growing, it means that it may be very fragmented or that there is little memory. (The Free_blocks and free_memory above can tell you what the situation is). The
qcache_not_cached   the number of queries that are not suitable for caching, usually because these queries are not SELECT statements. The
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, you can see the difference by displaying these variables in a few seconds, which can help determine whether the cache is being used effectively. Running FLUSH STATUS resets some counters, which can be very helpful if the server has been running for some time. The idea of
using a very large query cache and expecting everything to be cached is tempting. Because mysqld must maintain the cache, such as pruning when memory becomes low, the server may be in trouble trying to manage the cache. As a rule, if the FLUSH QUERY cache takes a long time, it means the cache is too large.

Enforce restrictions
You can enforce restrictions in mysqld to ensure that the system load does not result in resource exhaustion. Listing 3 shows some important resource-related settings in MY.CNF.
Listing 3. MySQL Resource Settings

The code is as follows	Copy Code
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 the maxclients in Apache, the idea is to make sure that only the number of connections that the service allows is established. To determine the maximum number of connections currently established on the server, perform show STATUS like ' max_used_connections '.
Line 2nd tells Mysqld to terminate any connections that have more than 10 seconds of idle time. 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 too heavy, the connection is suspended and the connection table space is occupied. If you have multiple interactive users or use a persistent connection to a database, it is not advisable to set this value down a bit!
The last line is a secure method. If a host is having problems connecting to the server and then trying many times to give up, the host will be locked down until FLUSH hosts. By default, 10 failures are enough to cause a lock. Modifying this value to 100 will give the server enough time to recover from the problem. If the connection cannot be established 100 times, then using a higher value will not have much help, and it may not be able to connect at all.

Buffers and caching
MySQL supports more than 100 adjustable settings; Fortunately, mastering a few can satisfy most of the needs. Finding the correct values for these settings allows you to view the status variables by using the show status command to determine whether the mysqld is working as expected. The memory allocated to buffers and caches cannot exceed the existing memory in the system, so tuning usually requires some compromise.
The MySQL adjustable settings can be applied to the entire mysqld process, or to a single customer opportunity session.
Server-side settings
Each table can be represented as a file on disk that must be opened before read. To expedite the process of reading data from a file, mysqld caches these open files, with the maximum number specified by the Table_cache in/etc/mysqld.conf. Listing 4 shows how to display activities related to opening a table.
Listing 4. Show activities that open a table

code is as follows	copy code
mysql> STATUS like ' open%tables '; + ————— + ——-+ | variable_name | Value | + ————— + ——-+ | Open_tables | 5000   | | Opened_tables | 195 | + ————— + ——-+ 2 rows in Set (0.00 sec)

Listing 4 shows that 5,000 tables are currently open and 195 tables need to be opened because there are no more file descriptors available in the cache (because the statistics are cleared earlier, there may be 5,000 open tables in which there are only 195 opened records). If opened_tables quickly increases as the show STATUS command is rerun, the cache hit rate is not sufficient. If the open_tables is much smaller than the Table_cache setting, it means that the value is too large (although there is room to grow it is always not a bad thing). For example, you can adjust the cache of a table by using Table_cache = 5000. The
is similar to a table cache and has a cache for threads. MYSQLD generates threads as needed when receiving connections. On a busy server with a fast-changing connection, caching a thread for later use can speed up the initial connection. The
Listing 5 shows how to determine whether enough threads are cached.
Listing 5. Display Thread usage statistics

code is as follows	copy code
mysql> 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 every time mysqld needs to create a new thread. If this number increases rapidly as 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 accomplish 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. Determining keyword Efficiency

code is as follows	copy code
mysql> Status like '%key_read% '; + ——————-+ ——— –+ | variable_name     | value     | + ——————-+ ——— –+ | key_read_requests | 163554268 | | key_reads       | 98247     | + ——————-+ ——— –+ 2 rows in Set (0.00 sec)

The

Key_reads represents the number of requests to hit the disk, Key_read_requests is the total. The number of read requests to hit the disk divided by the total number of read requests is not in the ratio?? In this case, about 0.6 of every 1,000 requests have no hit memory. If you have more than 1 hit disks per 1,000 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 must be saved to a temporary table before further processing, such as the GROUP by, in which case the temporary table is created in memory. However, if the temporary table becomes too large, it needs to be written to disk. Listing 7 shows the statistics related to the creation of a temporary table.
Listing 7. Determining the use of temporary tables

code is as follows	copy code
mysql> 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)

The

Increases created_tmp_tables each time a temporary table is used, and the disk-based table increases created_tmp_disk_tables. There are no strict rules for this ratio, because it depends on the query involved. Long observation Created_tmp_disk_tables Displays the ratio of disk tables created, and you can determine the efficiency of the settings. Both Tmp_table_size and max_heap_table_size can control the maximum size of a temporary table, so make sure that both values are set in MY.CNF.
Settings per session
The following settings are for each session. Be careful when setting these numbers because they represent a lot of memory when multiplied by the number of possible connections! You can modify these numbers in a session by code, or modify these settings for all sessions in MY.CNF.
When MySQL must be sorted, a sort buffer is allocated to hold the data rows when the data 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 the activity of the disk. Listing 8 shows some status counter information related to the sort.
Listing 8. Displaying sorting statistics

code is as follows	copy code
mysql> 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 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 that are needed, but sometimes the query (poor design or the nature of the data) requires reading a large amount of data from the table. To understand this behavior, you need to know how many SELECT statements have been run and the number of times you need to read the next row of data in the table (not directly through the index). The commands to implement this feature are shown in Listing 9.
Listing 9. Determining table Scan ratio

code is as follows	copy code
mysql> 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. If the value is more than 4000, you should view read_buffer_size, such as Read_buffer_size = 4M. If this number exceeds 8M, you should talk to the developers about tuning these queries!

3 Essential Tools
Although the show STATUS command can be useful when you understand specific settings, you need tools to explain the large amount of data that MYSQLD provides. I've found that 3 tools are essential, and in the Resources section you can find the appropriate links.
Most system administrators are very familiar with the top command, which provides a constantly updated view of the CPU and memory consumed by the task. The top is emulated by Mytop, which provides a view for all the clients on the connection and the queries they are running. Mytop also provides a real-time data and historical data about the efficiency of the keyword buffer and query cache, as well as statistics about the queries that are running. This is a useful tool to view the situation in your system (for example, within 10 seconds), and you can get a view of server health information and show 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. A Web page displays this data, such as the use of the table cache, keyword efficiency, the client on the connection, and the use 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 reports are far more complex than mysqlard because each 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 fixed.