Detailed description of Mysql performance optimization and mysql Performance Optimization

Detailed description of Mysql performance optimization and mysql Performance Optimization

In a mysql database, mysql key_buffer_size is the most influential parameter on the performance of the MyISAM table (note that this parameter is invalid for other types of tables ), the following describes how to set the mysql Key_buffer_size parameter. The configuration of a storage engine server with MyISAM as the main storage engine is as follows:

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

Allocated MB of memory to mysql key_buffer_size. Let's look at the usage of key_buffer_size:

Mysql> show global status like 'key _ read % '; + ------------------------ + ------------- + | Variable_name | Value | + ------------------------ + ------------- + | Key_read_requests | // The number of requests that read the index from the cache. | Key_reads | // number of requests that read the index from the disk. + ------------------------ + ------------- +

There are a total of 27813678764 index read requests, 6798830 of which are not found in the memory to directly read the index from the hard disk, calculate the probability that the index does not hit the cache:

key_cache_miss_rate = Key_reads / Key_read_requests * 100% 

For example, the key_cache_miss_rate of the above data is 0.0244%, and only one direct read hard disk is available for 4000 index read requests, which is already very BT, key_cache_miss_rate is very good under 0.1% (each 1000 requests have a direct read hard disk), So theoretically speaking, the smaller the ratio, the better, but if it is too small, it will inevitably lead to a waste of memory.

The ratio of the above two values can partially explain whether the key_buffer_size is reasonable. However, if the value is set properly, it is too radical and one-sided. Because two problems are ignored here:

1. The proportion does not show the absolute value of the quantity.

2. The counter does not consider the time factor.

Although the Key_read_requests size is better than small, but for system optimization, it is more meaningful to be the Key_reads in a unit of time, that is:

Key_reads/Uptime

The specific method is as follows:

[root@web mysql]# mysqladmin ext -uroot -p -ri | grep Key_readsEnter password:| Key_reads | || Key_reads | || Key_reads | || Key_reads | || Key_reads | || Key_reads | || Key_reads | || Key_reads | || Key_reads | || Key_reads | | 

Note:In the command, mysqladmin ext is actually mysqladmin extended-status. You can even abbreviated it as mysqladmin e.

The first line indicates the sum value, so you don't need to consider it here. The values in each row below indicate the data changes within 10 seconds, from this data, we can see that the system will see about 500 Key_reads accesses every 10 seconds, equivalent to about 50 accesses every 1 second. As to whether this value is reasonable or not, it depends on the disk capacity of the server. (Note: The reason why data changes greatly here is that the table lock is caused by the update statement, which leads to a sharp increase in the number of queries in the next period .)

Why does the data be sampled in 10 seconds instead of 1 second? Because the time period is too small and the data changes dramatically, it is difficult to estimate the size intuitively. Therefore, it is better to sample data in a period of 10 or 60 seconds.

In addition, we can also refer to the key_blocks _ * parameter below:

mysql> show global status like 'key_blocks_u%';+------------------------+-------------+| Variable_name | Value |+------------------------+-------------+| Key_blocks_unused | || Key_blocks_used | |+------------------------+-------------+ 

Key_blocks_unused indicates the number of unused cache clusters (blocks), and Key_blocks_used indicates the maximum number of blocks used. For example, on this server, all the caches are used, or key_buffer_size is increased, either it is a transitional index, and the cache is full. Ideal settings:

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

Note:

View the size (number of bytes) of a cluster (File System block)

Centos provides the following methods:

# Tune2fs/dev/sda1 | grep "block size"
# Dumpe2fs/dev/sda1 | grep "block size"

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

Mysqladmin is an important MySQL client. It is most commonly used to shut down the database. In addition, this command can also understand the MySQL running status, process information, and process killing. This article describes how to use mysqladmin extended-status (because there is no "ambiguity", you can use ext instead) to understand the running status of MySQL.

1. Use the-r/-I Parameter

You can use the mysqladmin extended-status Command to obtain all MySQL performance indicators, that is, the output of show global status. However, most of these indicators are accumulative values. If you want to know the current status, A difference calculation is required. This is an additional function of mysqladmin extended-status, which is very practical. By default, extended-status is used, and the result is a cumulative value. However, when the parameter-r (-- relative) is added, the difference between each indicator can be seen, in combination with the parameter-I (-- sleep) you can specify the refresh frequency by using 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

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, we can also work with awk and so on. I will not introduce them here. If you are interested, please refer to other documents.