Database Mysql Performance Optimization detailed _mysql

In the MySQL database, the MySQL key_buffer_size is one of the most influential parameters for MyISAM table performance (note that this parameter is not valid for other types of table settings), and the following will be on the MySQL Key_buffer_ The setting of the size parameter is described in detail below is a configuration with MyISAM as the primary storage Engine server:

Mysql> Show variables like ' key_buffer_size ';
+-----------------+------------+
| variable_name | Value |
+-----------------+------------+
| key_buffer_size | |

Allocated 512MB memory to the MySQL key_buffer_size, let's take a look at the use of key_buffer_size:

Mysql> show global status like ' key_read% ';
+------------------------+-------------+
| variable_name | Value |
+------------------------+-------------+
| key_read_requests | | The number of requests to read the index from the cache.
| Key_reads | | The number of requests to read the index from the disk.

There are 27,813,678,764 index read requests, 6,798,830 requests are not found in memory read the index directly from the hard disk, the probability of the index misses the cache is computed:

For example, the above data, Key_cache_miss_rate 0.0244%, 4,000 index read request only a direct read hard drive, has been very bt, Key_cache_miss_rate is very good under 0.1% (every 1000 requests have a direct read hard drive So theoretically, the smaller the ratio the better, but too small, it will inevitably cause memory waste.

The ratio of the above two values is a part of the explanation key_buffer_size is reasonable, but only in this way to explain the value set reasonable words, is too extreme and one-sided. Because there are two missing questions:

1, the proportion does not show the number of absolute size

2, the counter does not consider the time factor

Although Key_read_requests is better than small, but for system tuning, more meaningful should be the unit time key_reads, namely:

Key_reads/uptime

See the following methods:

[Root@web mysql]# mysqladmin ext-uroot-p-ri | grep key_reads
Enter Password:
| key_reads | | |
key_reads | | |
key_reads | | |
key_reads | | |
key_reads | | |
key_reads | | |
key_reads | | |
key_reads | | |
Key_reads | |

Note: the mysqladmin ext in the command is actually mysqladmin Extended-status, you can even abbreviate mysqladmin E.

Where the first line represents a summary value, so there is no need to consider, the following values for each row in 10 seconds of data changes, from this data can be seen every 10 seconds system will appear 500 times key_reads visit, reduced to every 1 seconds is about 50 times, as to whether this value is reasonable or not, is determined by the disk capabilities of the server. (Note: I am here because the data change is large, because there are updates and other statements caused the table lock, resulting in the next time the query number soared.) )

Why is the data sampled in 10 seconds instead of just 1 seconds? Because the time period is too small, the data changes more intense, it is not easy to visually estimate the size, so usually the data in 10 seconds or 60 seconds, such as time period to sample is better.

In addition to some, we can also refer to the following key_blocks_* parameters:

Mysql> show global status like ' key_blocks_u% ';
+------------------------+-------------+
| variable_name | Value |
+------------------------+-------------+
| key_blocks_unused | | |
key_blocks_used | |

Key_blocks_unused represents the number of unused cache clusters (blocks), key_blocks_used indicates the maximum number of blocks used, such as this server, all caches are used, or increase key_buffer_size, Either the transition index or the cache is full. More Ideal settings:

I note:

View the size (number of bytes) of a cluster (file system blocks, block)

There are several ways to do this in CentOS:

#tune2fs/dev/sda1 | grep "Block Size"
#dumpe2fs/dev/sda1 | grep "Block Size"

Theoretically, the file system block is a multiple of the sector.

Mysqladmin is an important client of MySQL, the most common is to use it to close the database, in addition to this command can also understand the running state of MySQL, process information, process killing and so on. This article describes how to use the Mysqladmin extended-status (because there is no "ambiguity", so you can use Ext instead) to understand the running state of MySQL.

1. Use-r/-i parameter

Using the mysqladmin extended-status command, you get all the MySQL performance metrics, the output of show global status, but because most of these metrics are cumulative, you need to perform a margin calculation if you want to know the current state. This is an additional feature of Mysqladmin extended-status, very practical. The default, using Extended-status, see also cumulative value, but, plus the parameter-R (--relative), you can see the difference between the indicators, with the parameter-I (--sleep) can specify the frequency of the refresh, then like the following command:

Mysqladmin-uroot-r-i-pxxx extended-status
+------------------------------------------+--------------------- -+
| variable_name | Value |
+------------------------------------------+----------------------+
| aborted_clients | | |
Com_select | | |
Com_insert | |
......
| threads_created | |

2. Use with grep

Together with grep, we have:

Mysqladmin-uroot-r-i-pxxx extended-status \
grep "questions\| queries\| innodb_rows\| Com_select \| Com_insert \| Com_update \| Com_delete "
| Com_delete | | |
Com_delete_multi | | |
Com_insert | | |
Com_select | | |
com_update | | |
innodb_rows_deleted | | |
innodb_rows_inserted | | |
Innodb_rows_read | | |
innodb_rows_updated | | |
Queries | |

Of course, can also cooperate with awk, the author is not introduced here, have fun friends can refer to other documents.