Lamp system can optimize MySQL database server Optimization

Nowadays, developers are constantly developing and deploying lamp (Linux
, Apache, MySQL
And PHP
/Perl) architecture applications. However, Server Management
Members often have no control over the application itself, because the application is written by others. This three-part series will discuss many server configuration issues
These configurations will affect the performance of the application. This article is the third and last part of this series. It will focus on Database
Layer optimization.

About MySQL
Optimization

There are three ways to speed up the running of the MySQL server:

1. Replace the 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 that the memory is allocated appropriately and the mysqld
Understand what type of load will be supported. Speed up disk operation is not as fast as reducing the number of disk accesses required. Similarly, ensure that MySQL
A proper process operation means that it spends more time on service queries than processing background tasks (such as processing temporary disk tables or opening and closing files. For mysqld
Tuning 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.
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 queries 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

In an SQL
On the server, 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. Usually
For example, you may only want to obtain a subset of table data, so full table scan will waste a lot of disk space.
I/O, so it will waste 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 usage efficiency
Very low, or you cannot use an index at all, it slows down the query speed and becomes more prominent as the server load and table size increase. A query whose execution time exceeds the specified time range is called
Is 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. Enabling MySQL slow query logs

1. [Mysqld]
2. ; Enable the slow query log, default 10 seconds
3. Log-Slow-queries
4. ; Log queries taking longer than 5 seconds
5. Long_query_time = 5
6. ; Log queries that don't use indexes even if they take less than long_query_time
7. ; MySQL 4.1 and newer only
8. Log-queries-not-using-Indexes

Copy code

This
These three settings are used together to record queries that have been executed for more than 5 seconds and that have not used an index. Note that log-queries-not-using-Indexes
Warning: 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 = In My. CNF.
/New/path/to/file to achieve this goal.

It is best to read slow query logs through 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
Mysqldumpslow deletes any user-specified data before comparing the results. Therefore, different calls to the same query are counted as one; this can help you find the query that requires the most workload.

Cache queries

Many lamp applications rely heavily on databases, but repeatedly execute the same query. Each time a query is executed, the database must perform the same job --
Analyze the query, determine how to perform the query, load information from the disk, and then return the result to the client. MySQL
A feature called query cache stores the query results (which will be used later) in the 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.

Monitor query Cache

After enabling the query cache, 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 statistics of the query Cache

1. Mysql> show status like 'qcache % ';
2. + ------------------------- + ------------ +
3. | Variable_name | value |
4. + ------------------------- + ------------ +
5. | Qcache_free_blocks | 1, 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 | 1, 47042 |
13. + ------------------------- + ------------ +
14. 8 rows in SET (0.00 Sec)

Copy code

The explanations of these items are shown in table 1.

Variable name	Description
Qcache_free_blocks	The number of adjacent memory blocks in the 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	Each time the query hits the cache, it increases.
Qcache_inserts	Each time a query is inserted, it increases. 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 is best to look at this number for a long time. If this number is growing, it means that the fragmentation may be very serious, or the memory is very high. Less. (The free_blocks and free_memory above can tell you what the situation is ).
Qcache_not_cached	The number of queries that are not suitable for caching, usually because these queries are not select statements.
Qcache_queries_in_cache	The number of queries (and responses) cached currently.
Qcache_total_blocks	The number of cached 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
The cache must be maintained. For example, when the memory becomes low, the server may be in trouble when trying to manage the cache. As a rule, if flush Query
The cache takes a long time, which indicates that the cache is too large.

Force limit

You can enforce 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 settings

1. Max_connections = 500
2. Wait_timeout = 10
3. Max_connect_errorrs = 100

Copy code

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 database connection time is usually Web
The time taken by the server to process the request. 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, set this value
It is not advisable to be lower!

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 flush hosts
Before running. 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 you retry 100
The connection cannot be established once, so using a higher value will not be of much help, and it may not be able to connect at all.

Buffer and Cache

MySQL supports more than 100 Adjustable settings, but fortunately, a few can meet most of the needs. You can use show
STATUS Command to view the status variable, from which you can determine mysqld
Whether the operation 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.

Server Settings

Each table 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 opened files.
Specify 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_table | 195 |
7. + --------------- + ------- +
8. 2 rows in SET (0.00 Sec)

Copy code

Clear
Table 4 indicates that 5,000 tables are currently open, with 195
Tables need to be opened, because no file descriptor is available in the cache now (because the statistics have been cleared before, there may be 5,000 open tables with only 195
Records ). 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, which means the value is too large (but it is not a bad thing to have space to increase ). For example
Table_cache = 5000 you can 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. + ------------------- + -------- +
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)

Copy code

This
The important value is threads_created. This value increases every time mysqld needs to create a new thread. If this number is in the continuous run show
When the status command increases rapidly, 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. Determining keyword Efficiency

1. Mysql> show status like '% key_read % ';
2. + ------------------- + ----------- +
3. | Variable_name | value |
4. + ------------------- + ----------- +
5. | Key_read_requests | 163554268 |
6. | Key_reads | 98247 |
7. + ------------------- + ----------- +
8. 2 rows in SET (0.00 Sec)

Copy code

Key_reads
The number of requests that hit the disk. 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 to the middle-in this example
1,000 requests, about 0.6 do not hit the memory. If the number of hit disks in every 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. determine the use of temporary tables

1. Mysql> show status like 'created _ TMP % ';
2. + ------------------------- + ------- +
3. | Variable_name | value |
4. + ------------------------- + ------- +
5. | Created_tmp_disk_tables | 30660 |
6. | Created_tmp_files | 2 |
7. | Created_tmp_tables | 1, 32912 |
8. + ------------------------- + ------- +
9. 3 rows in SET (0.00 Sec)

Copy code

Each
When temporary tables are used, created_tmp_tables is increased. disk-based tables are also increased.
Created_tmp_disk_tables. There are no strict rules for this ratio, because it depends on the involved query. Long observation
Created_tmp_disk_tables will display the ratio of the created disk table. You can determine the efficiency of the settings. 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.

Settings for each session

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
When sorting is required, a sort buffer is allocated to read data from the disk to store the data rows. If the data to be sorted is too large, the data must be saved to a temporary file on the disk, and then
. If the sort_merge_passes status variable is large, it indicates disk activity. Listing 8 lists some status counters related to sorting.

Listing 8. Displaying sorting statistics

1. Mysql> show status like "Sort % ";
2. + ------------------- + --------- +
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)

Copy code

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
Some memory will be allocated 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 number of tables.
Data. To understand this behavior, you need to know how many select statements are running.
Statement, and the number of times the data in the next row in the table needs to be read (instead of directly accessing through the index ). The command to implement this function is shown in listing 9.

Listing 9. Determining the table scan rate

1. Mysql> show status like "com_select ";
2. + --------------- + -------- +
3. | Variable_name | value |
4. + --------------- + -------- +
5. | Com_selected | 318243 |
6. + --------------- + -------- +
7. 1 row in SET (0.00 Sec)
9. Mysql> show status like "handler_read_rnd_next ";
10. + ----------------------- + ----------- +
11. | Variable_name | value |
12. + ----------------------- + ----------- +
13. | Handler_read_rnd_next | 1, 165959471 |
14. + ----------------------- + ----------- +
15. 1 row in SET (0.00 Sec)

Copy code

Handler_read_rnd_next
/Com_select returns the table scan rate-in this example, It is 521: 1. If the value exceeds 4000, View
Read_buffer_size, for example, read_buffer_size = 4 m. If the number exceeds
8 m, you should discuss with the developers about tuning these queries!

Three essential tools

Although the show status command is 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 to top
Simulation is performed. It provides a view for all connected clients and their running queries. Mytop
It 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, you can view the system (such
Within 10 seconds), you can get a view of the 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 them to a round robin in the background.
Database. A web page displays the data, such as table Cache Usage, keyword efficiency, connected clients, and usage of temporary tables. Although mytop
Snapshots of server health information are provided, but mysqlard provides long-term health information. As a reward, mysqlard
Use the information you have collected to provide 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 corrected.