MySQL 优化相关的一些说明
上一篇 / 下一篇 2007-09-25 13:49:01 / 精华(5)
以下是 MySQL 优化相关的一些说明：
1、检验 key_buffer_size 参数大小是否合适（适用 MyISAM 表）
key_buffer_size 指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads，可以知道 key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得）（检查状态值，在查询工具里输入 SHOW STATUS ，执行）。
key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。
对于1G内存的机器，如果不使用MyISAM表，推荐值是16M（8-64M）。
案例1：健康状况
key_buffer_size – 402649088 (384M)
key_read_requests – 597579931
key_reads - 56188
案例2：警报状态
key_buffer_size – 16777216 (16M)
key_read_requests – 597579931
key_reads - 53832731
案例1中比例低于1:10000，是健康的情况；案例2中比例达到1:11，警报已经拉响。
2、查询缓存 query_cache_size 设置
从 4.0.1 开始，MySQL 提供了查询缓冲机制。在启用查询缓冲的情况下，MySQL 将 SELECT 语句和查询结果存放在缓冲区中（内存），之后对于同样的 SELECT 查询语句（区分大小写），将直接从缓冲区中读取结果，避免了重复查询的无谓开销。和查询缓存相关的参数包括：Qcache_free_blocks、 Qcache_lowmem_prunes、Qcache_free_memory、Qcache_not_cached、 Qcache_total_blocks、Qcache_queries_in_cache、Qcache_hits、Qcache_inserts。其 中，如果 Qcache_lowmem_prunes 的值很大，说明经常出现缓冲不够的情况（最好保持在零），同时 Qcache_hits 的值非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小 Qcache_hits 的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。另外，如果 Qcache_free_blocks 的值非常大，则表明缓冲区中碎片很多。
3、table_cache
吴威 (16:59:27):
table_cache
The number of open tables for all threads ？？？？
For more information about the table cache, see section 7.4.8 How MySQL Opens and Closes Tables
table_cache 用于指定表高速缓存的大小。每当 MySQL 访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查运行峰值时间的 Open_tables 和 Opened_tables 状态值，可以决定是否需要调整 table_cache 的值。如果你发现 open_tables 的值等于 table_cache，并且发现 opened_tables 状态值在不断增长，那么你就需要增加 table_cache 参数值了（上述状态值可以使用 SHOW STATUS LIKE ‘Open%tables’ 命令获得）。注意，不能盲目地把 table_cache 参数设置成很大的值，如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。
对于有1G内存的机器，推荐值是128－256。
案例1：该案例来自一个不是特别繁忙的服务器
table_cache – 512
open_tables – 103
opened_tables – 1273
uptime – 4021421 (measured in seconds)
该案例中table_cache似乎设置得太高了。在峰值时间，打开表的数目比table_cache要少得多。
案例2：该案例来自一台开发服务器。
table_cache – 64
open_tables – 64
opened-tables – 431
uptime – 1662790 (measured in seconds)
虽然open_tables已经等于table_cache，但是相对于服务器运行时间来说，opened_tables的值也非常低。因此，增加table_cache的值应该用处不大。
案例3：该案例来自一个upderperforming的服务器
table_cache – 64
open_tables – 64
opened_tables – 22423
uptime – 19538
该案例中table_cache设置得太低了。虽然运行时间不到6小时，open_tables达到了最大值，opened_tables的值也非常高。这样就需要增加table_cache的值。
4、Measuring Key Buffer Usage
When you add indexes to your data, it enables MySQL to find data faster. However, ideally you want to have these indexes stored in RAM for maximum speed, and the variable key_buffer_size defines how much RAM MySQL can allocate for index key caching. If MySQL cannot store its indexes in RAM, you will experience serious performance problems. Fortunately, most databases have relatively small key buffer requirements, but you should measure your usage to see what work needs to be done.
To do this, log in to MySQL and type SHOW STATUS LIKE ‘%key_read%’;. That returns all the status fields that describe the hit rate of your key buffer—you should get two rows back: Key_reads and Key_read_requests, which are the number of keys being read from disk and the number of keys being read from the key buffer. From these two numbers you can calculate the percentage of requests being filled from RAM and from disk, using this simple equation:
100 – ((Key_reads / Key_read_requests) x 100)
That is, you divide Key_reads by Key_read_requests, multiply the result by 100 and then subtract the result from 100. For example, if you have Key_reads of 1000 and Key_read_requests of 100000, you divide 1000 by 100000 to get 0.01; then you multiply that by 100 to get 1.0, and subtract that from 100 to get 99. That number is the percentage of key reads being served from RAM, which means 99% of your keys are served from RAM.
Most people should be looking to get more than 95% served from RAM, although the primary exception is if you update or delete rows very often—MySQL can’t cache what keeps changing. If your site is largely read only, this should be around 98%. Lower figures mean you might need to bump up the size of your key buffer.
If you are seeing problems, the next step is to check how much of your current key buffer is being used. Use the SHOW VARIABLES command and look up the value of the key_buffer_size variable. It is probably something like 8388600, which is eight million bytes, or 8MB. Now, use the SHOW STATUS command and look up the value of Key_blocks_used.
You can now determine how much of your key buffer is being used by multiplying Key_blocks_used by 1024, dividing by key_buffer_size, and multiplying by 100. For example, if Key_blocks_used is 8000, you multiply that by 1024 to get 8192000; then you divide that by your key_buffer_size (8388600) to get 0.97656, and finally multiplying that by 100 to get 97.656. Thus, almost 98% of your key buffer is being used.
Now, onto the important part: You have ascertained that you are reading lots of keys from disk, and you also now know that the reason for reading from disk is almost certainly because you do not have enough RAM allocated to the key buffer. A general rule of thumb is to allocate as much RAM to the key buffer as you can, up to a maximum of 25% of system RAM—128MB on a 512MB system is about the ideal for systems that read heavily from keys. Beyond that, you will actually see drastic performance decreases because the system has to use virtual memory for the key buffer.
1、检验 key_buffer_size 参数大小是否合适（适用 MyISAM 表）
key_buffer_size 指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads，可以知道 key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得）（检查状态值，在查询工具里输入 SHOW STATUS ，执行）。
key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。
对于1G内存的机器，如果不使用MyISAM表，推荐值是16M（8-64M）。
案例1：健康状况
key_buffer_size – 402649088 (384M)
key_read_requests – 597579931
key_reads - 56188
案例2：警报状态
key_buffer_size – 16777216 (16M)
key_read_requests – 597579931
key_reads - 53832731
案例1中比例低于1:10000，是健康的情况；案例2中比例达到1:11，警报已经拉响。
2、查询缓存 query_cache_size 设置
从 4.0.1 开始，MySQL 提供了查询缓冲机制。在启用查询缓冲的情况下，MySQL 将 SELECT 语句和查询结果存放在缓冲区中（内存），之后对于同样的 SELECT 查询语句（区分大小写），将直接从缓冲区中读取结果，避免了重复查询的无谓开销。和查询缓存相关的参数包括：Qcache_free_blocks、 Qcache_lowmem_prunes、Qcache_free_memory、Qcache_not_cached、 Qcache_total_blocks、Qcache_queries_in_cache、Qcache_hits、Qcache_inserts。其 中，如果 Qcache_lowmem_prunes 的值很大，说明经常出现缓冲不够的情况（最好保持在零），同时 Qcache_hits 的值非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小 Qcache_hits 的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。另外，如果 Qcache_free_blocks 的值非常大，则表明缓冲区中碎片很多。
3、table_cache
吴威 (16:59:27):
table_cache
The number of open tables for all threads ？？？？
For more information about the table cache, see section 7.4.8 How MySQL Opens and Closes Tables
table_cache 用于指定表高速缓存的大小。每当 MySQL 访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查运行峰值时间的 Open_tables 和 Opened_tables 状态值，可以决定是否需要调整 table_cache 的值。如果你发现 open_tables 的值等于 table_cache，并且发现 opened_tables 状态值在不断增长，那么你就需要增加 table_cache 参数值了（上述状态值可以使用 SHOW STATUS LIKE ‘Open%tables’ 命令获得）。注意，不能盲目地把 table_cache 参数设置成很大的值，如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。
对于有1G内存的机器，推荐值是128－256。
案例1：该案例来自一个不是特别繁忙的服务器
table_cache – 512
open_tables – 103
opened_tables – 1273
uptime – 4021421 (measured in seconds)
该案例中table_cache似乎设置得太高了。在峰值时间，打开表的数目比table_cache要少得多。
案例2：该案例来自一台开发服务器。
table_cache – 64
open_tables – 64
opened-tables – 431
uptime – 1662790 (measured in seconds)
虽然open_tables已经等于table_cache，但是相对于服务器运行时间来说，opened_tables的值也非常低。因此，增加table_cache的值应该用处不大。
案例3：该案例来自一个upderperforming的服务器
table_cache – 64
open_tables – 64
opened_tables – 22423
uptime – 19538
该案例中table_cache设置得太低了。虽然运行时间不到6小时，open_tables达到了最大值，opened_tables的值也非常高。这样就需要增加table_cache的值。
4、Measuring Key Buffer Usage
When you add indexes to your data, it enables MySQL to find data faster. However, ideally you want to have these indexes stored in RAM for maximum speed, and the variable key_buffer_size defines how much RAM MySQL can allocate for index key caching. If MySQL cannot store its indexes in RAM, you will experience serious performance problems. Fortunately, most databases have relatively small key buffer requirements, but you should measure your usage to see what work needs to be done.
To do this, log in to MySQL and type SHOW STATUS LIKE ‘%key_read%’;. That returns all the status fields that describe the hit rate of your key buffer—you should get two rows back: Key_reads and Key_read_requests, which are the number of keys being read from disk and the number of keys being read from the key buffer. From these two numbers you can calculate the percentage of requests being filled from RAM and from disk, using this simple equation:
100 – ((Key_reads / Key_read_requests) x 100)
That is, you divide Key_reads by Key_read_requests, multiply the result by 100 and then subtract the result from 100. For example, if you have Key_reads of 1000 and Key_read_requests of 100000, you divide 1000 by 100000 to get 0.01; then you multiply that by 100 to get 1.0, and subtract that from 100 to get 99. That number is the percentage of key reads being served from RAM, which means 99% of your keys are served from RAM.
Most people should be looking to get more than 95% served from RAM, although the primary exception is if you update or delete rows very often—MySQL can’t cache what keeps changing. If your site is largely read only, this should be around 98%. Lower figures mean you might need to bump up the size of your key buffer.
If you are seeing problems, the next step is to check how much of your current key buffer is being used. Use the SHOW VARIABLES command and look up the value of the key_buffer_size variable. It is probably something like 8388600, which is eight million bytes, or 8MB. Now, use the SHOW STATUS command and look up the value of Key_blocks_used.
You can now determine how much of your key buffer is being used by multiplying Key_blocks_used by 1024, dividing by key_buffer_size, and multiplying by 100. For example, if Key_blocks_used is 8000, you multiply that by 1024 to get 8192000; then you divide that by your key_buffer_size (8388600) to get 0.97656, and finally multiplying that by 100 to get 97.656. Thus, almost 98% of your key buffer is being used.
Now, onto the important part: You have ascertained that you are reading lots of keys from disk, and you also now know that the reason for reading from disk is almost certainly because you do not have enough RAM allocated to the key buffer. A general rule of thumb is to allocate as much RAM to the key buffer as you can, up to a maximum of 25% of system RAM—128MB on a 512MB system is about the ideal for systems that read heavily from keys. Beyond that, you will actually see drastic performance decreases because the system has to use virtual memory for the key buffer.
相关阅读:
- Mysql 数据库的导入与导出 (bc8, 2007-6-08)
- Mysql字段长度 (bc8, 2007-6-08)
- MySQL用户管理 (bc8, 2007-6-08)
- 如何把ACCESS的数据导入到Mysql中 (bc8, 2007-6-08)
- 如何为用户设置密码(MYSQL) (bc8, 2007-6-08)
- MySQL数据库备份 (bc8, 2007-6-08)
- 谈谈数据从sql server数据库导入mysql数据库的体验 (bc8, 2007-6-08)
- MYSQL如何记录慢查询 (admin, 2007-9-24)
- LAMP 系统性能调优 (admin, 2007-9-24)
