Database Mysql performance optimization details

In mysql database, mysql key_buffer_size is the parameter that has the most impact on MyISAM table performance (note that this parameter is not valid for other types of table Settings). The following is a detailed description of mysql Key_buffer_size parameter configuration.

mysql> show variables like 'key_buffer_size';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| key_buffer_size | |
+-----------------+------------+ 

Distribution of 512 MB memory to mysql key_buffer_size, we look at 1 key_buffer_size usage:

mysql> show global status like 'key_read%';
+------------------------+-------------+
| Variable_name | Value |
+------------------------+-------------+
| Key_read_requests | | // Number of requests to read the index from the cache. 
| Key_reads | | // Number of requests to read an index from disk. 
+------------------------+-------------+ 

1 A total of 27813678764 index read requests were obtained, among which 6798830 requests were not found in memory to read the index directly from the hard disk. Calculate the probability of index missed cache:

key_cache_miss_rate = Key_reads / Key_read_requests * 100% 

For example, the above data, key_cache_miss_rate is 0.0244%, 4000 index read requests only have 1 directly read from the hard disk, already very BT, key_cache_miss_rate below 0.1% is very good (every 1000 requests have 1 directly read from the hard disk), so theoretically speaking, the lower the ratio, the better, but if too small, it will inevitably cause a waste of memory.

Of course, the ratio of the above two values can partly explain whether key_buffer_size is reasonable, but it is too extreme and one-sided to explain the reasonable setting of the value only by this. Because there are two things that are missing here:

1. The scale does not show the absolute magnitude of the quantity

2. The counter does not take time into account

Although Key_read_requests is better than requests, for system tuning, it is Key_reads per unit time that is more meaningful, i.e. :

Key_reads / Uptime

Specific viewing methods are as follows:

[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 | |
| Key_reads | | 

Note: The mysqladmin ext in the command is actually mysqladmin extended-status, you can even abbreviate it to mysqladmin e.

Of line 1 represents the summary values, so here don't have to consider, the following every numerical data changes for 10 seconds, can be seen from this data every 10 seconds the system will be about 500 times Key_reads access, or to every 1 second is 50 times or so, as for this value is reasonable or not, by the disk capacity of the server. (Note: The number of queries in the next time period increases dramatically because of statements such as update that cause table locks.)

Why is the data sampled in 10 seconds instead of 1 second? Because the time period is too small and the data changes dramatically, it is not easy to intuitively estimate the size, so it is usually better to sample the data according to the time period of 10 seconds or 60 seconds.

key_blocks_* : key_blocks_*

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 represents the maximum number of blocks that has ever been used. For example, in this server, all the caches have been used, and either key_buffer_size has been increased, or the cache has been overloaded. Ideal Settings:

Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100%  material  80% 

The author's note:

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

Centos has the following methods:

#tune2fs /dev/sda1 | grep "block size"
#dumpe2fs /dev/sda1 | grep "block size"

Theoretically, file system blocks are multiples of sectors

mysqladmin is an important client of MySQL1. It is most commonly used to shut down a database. In addition, this command can also be used to know MySQL health, process information, process kills, etc. This article describes how to use mysqladmin -status in order to understand the status of MySQL.

1. Use the -r/-i parameter

Use the mysqladmin extended-status command to get all the MySQL performance metrics, that is, show global status output. However, since most of these metrics are cumulative, if you want to know the current status, you need to perform a difference calculation. This is an additional feature of mysqladmin extended-status, which is very useful. By default, use extended-status to see the cumulative value. However, add parameter -r(--relative) to see the difference value of each indicator, and use parameter -i(--sleep) to specify the refresh frequency, so as to get the following command:

mysqladmin -uroot -r -i -pxxx extended-status
+------------------------------------------+----------------------+
| Variable_name | Value |
+------------------------------------------+----------------------+
| Aborted_clients | |
| Com_select | |
| Com_insert | |
......
| Threads_created | |
+------------------------------------------+----------------------+ 

2. Used in conjunction with grep

When used 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 | |
| Questions | 2721 | 

Of course, you can also cooperate with awk, etc., the author here is not introduced 11, interesting friends can refer to 1 other documents.