通过show status 来优化MySQL数据库

通过show status 来优化MySQL数据库

发布日期：11-05-03 05:55    文章来源：互联网

1, 查看MySQL服务器配置信息

Java代码 
mysql> show variables; 
mysql> show variables;

2, 查看MySQL服务器运行的各种状态值

Java代码 
mysql> show global status; 
mysql> show global status;

3, 慢查询

Java代码 
mysql> show variables like '%slow%'; | Variable_name | Value | | log_slow_queries | OFF | 
| slow_launch_time | 2 | mysql> show global status like '%slow%'; | Variable_name | Value | | Slow_launch_threads | 0 | 
| Slow_queries | 279 | mysql> show variables like '%slow%';| Variable_name | Value || log_slow_queries | OFF |
| slow_launch_time | 2 |mysql> show global status like '%slow%';| Variable_name | Value || Slow_launch_threads | 0 |
| Slow_queries | 279 |
+---------------------+-------+

配置中关闭了记录慢查询（最好是打开，方便优化），超过2秒即为慢查询，一共有279条慢查询

4, 连接数

Java代码 
mysql> show variables like 'max_connections'; | Variable_name | Value | | max_connections | 500 | 
mysql> show global status like 'max_used_connections'; | Variable_name | Value | | Max_used_connections | 498 | mysql> show variables like 'max_connections';| Variable_name | Value || max_connections | 500 |
+-----------------+-------+

mysql> show global status like 'max_used_connections';| Variable_name | Value || Max_used_connections | 498 |
+----------------------+-------+

设置的最大连接数是500，而响应的连接数是498

max_used_connections / max_connections * 100% = 99.6% （理想值 ≈ 85%）

5, key_buffer_size 
key_buffer_size是对MyISAM表性能影响最大的一个参数, 不过数据库中多为Innodb

Java代码 
mysql> show variables like 'key_buffer_size'; | Variable_name | Value | | key_buffer_size | 67108864 | 
mysql> show global status like 'key_read%'; | Variable_name | Value | | Key_read_requests | 25629497 | 
| Key_reads | 66071 | mysql> show variables like 'key_buffer_size';| Variable_name | Value || key_buffer_size | 67108864 |
+-----------------+----------+

mysql> show global status like 'key_read%';| Variable_name | Value || Key_read_requests | 25629497 |
| Key_reads | 66071 |
+-------------------+----------+

一共有25629497个索引读取请求，有66071个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率： 
key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100% =0.27% 
需要适当加大key_buffer_size

Java代码 
mysql> show global status like 'key_blocks_u%'; | Variable_name | Value | | Key_blocks_unused | 10285 | 
| Key_blocks_used | 47705 | mysql> show global status like 'key_blocks_u%';| Variable_name | Value || Key_blocks_unused | 10285 |
| Key_blocks_used | 47705 |
+-------------------+-------+

Key_blocks_unused表示未使用的缓存簇(blocks)数，Key_blocks_used表示曾经用到的最大的blocks数 
Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 18% （理想值 ≈ 80%）

6， 临时表

Java代码 
mysql> show global status like 'created_tmp%'; | Variable_name | Value | | Created_tmp_disk_tables | 4184337 | 
| Created_tmp_files | 4124 | 
| Created_tmp_tables | 4215028 | mysql> show global status like 'created_tmp%';| Variable_name | Value || Created_tmp_disk_tables | 4184337 |
| Created_tmp_files | 4124 |
| Created_tmp_tables | 4215028 |
+-------------------------+---------+

每次创建临时表，Created_tmp_tables增加，如果是在磁盘上创建临时表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数： 
Created_tmp_disk_tables / Created_tmp_tables * 100% ＝ 99% （理想值<= 25%）

Java代码 
mysql> show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size'); | Variable_name | Value | | max_heap_table_size | 134217728 | 
| tmp_table_size | 134217728 | mysql> show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');| Variable_name | Value || max_heap_table_size | 134217728 |
| tmp_table_size | 134217728 |
+---------------------+-----------+

需要增加tmp_table_size

7,open table 的情况

Java代码 
mysql> show global status like 'open%tables%'; | Variable_name | Value | | Open_tables | 1024 | 
| Opened_tables | 1465 | mysql> show global status like 'open%tables%';| Variable_name | Value || Open_tables | 1024 |
| Opened_tables | 1465 |
+---------------+-------+

Open_tables 表示打开表的数量，Opened_tables表示打开过的表数量，如果Opened_tables数量过大，说明配置中 table_cache(5.1.3之后这个值叫做table_open_cache)值可能太小，我们查询一下服务器table_cache值

Java代码 
mysql> show variables like 'table_cache'; | Variable_name | Value | | table_cache | 1024 | mysql> show variables like 'table_cache';| Variable_name | Value || table_cache | 1024 |
+---------------+-------+

Open_tables / Opened_tables * 100% =69% 理想值 （>= 85%） 
Open_tables / table_cache * 100% = 100% 理想值 (<= 95%)

8, 进程使用情况

Java代码 
mysql> show global status like 'Thread%'; | Variable_name | Value | | Threads_cached | 31 | 
| Threads_connected | 239 | 
| Threads_created | 2914 | 
| Threads_running | 4 | mysql> show global status like 'Thread%';| Variable_name | Value || Threads_cached | 31 |
| Threads_connected | 239 |
| Threads_created | 2914 |
| Threads_running | 4 |
+-------------------+-------+

如果我们在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）。Threads_created表示创建过的线程数，如果发现Threads_created值过大的话，表明 MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值，查询服务器 thread_cache_size配置：

Java代码 
mysql> show variables like 'thread_cache_size'; | Variable_name | Value | | thread_cache_size | 32 | mysql> show variables like 'thread_cache_size';| Variable_name | Value || thread_cache_size | 32 |
+-------------------+-------+

9, 查询缓存(query cache)

Java代码 
mysql> show global status like 'qcache%'; | Variable_name | Value | | Qcache_free_blocks | 2226 | 
| Qcache_free_memory | 10794944 | 
| Qcache_hits | 5385458 | 
| Qcache_inserts | 1806301 | 
| Qcache_lowmem_prunes | 433101 | 
| Qcache_not_cached | 4429464 | 
| Qcache_queries_in_cache | 7168 | 
| Qcache_total_blocks | 16820 | mysql> show global status like 'qcache%';| Variable_name | Value || Qcache_free_blocks | 2226 |
| Qcache_free_memory | 10794944 |
| Qcache_hits | 5385458 |
| Qcache_inserts | 1806301 |
| Qcache_lowmem_prunes | 433101 |
| Qcache_not_cached | 4429464 |
| Qcache_queries_in_cache | 7168 |
| Qcache_total_blocks | 16820 |
+-------------------------+----------+

Qcache_free_blocks：缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。 
Qcache_free_memory：缓存中的空闲内存。 
Qcache_hits：每次查询在缓存中命中时就增大 
Qcache_inserts：每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。 
Qcache_lowmem_prunes：缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的 free_blocks和free_memory可以告诉您属于哪种情况） 
Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。 
Qcache_queries_in_cache：当前缓存的查询（和响应）的数量。 
Qcache_total_blocks：缓存中块的数量。

我们再查询一下服务器关于query_cache的配置：