Mysql上线后优化项

查询Mysql服务器运行的各种状态值，命令如下：

mysql> show global status;

1、慢查询

查询慢查询日志相关命令如下：

mysql> show variables like '%slow%';

+---------------------------+----------+

| Variable_name | Value |

+---------------------------+----------+

| log_slow_admin_statements | OFF |

| log_slow_slave_statements | OFF |

| slow_launch_time | 2 |

| slow_query_log | ON |

| slow_query_log_file | slow.log |

+---------------------------+----------+

mysql> show global status like '%slow%';

+---------------------+-------+

| Variable_name | Value |

+---------------------+-------+

| Slow_launch_threads | 0 |

| Slow_queries | 0 |

+---------------------+-------+

mysql> show variables like 'max_connections';

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| max_connections | 4096 |

+-----------------+-------+

服务器响应的最大连接数

mysql> show global status like "Max_used_connections";

+----------------------+-------+

| Variable_name | Value |

+----------------------+-------+

| Max_used_connections | 20 |

+----------------------+-------+

key_buffer_size是设置MyISAM表索引擎缓存空间的大小，此参数对MyISAM表性能影响很大。

mysql> show variables like 'key_buffer_size';

+-----------------+---------+

| Variable_name | Value |

+-----------------+---------+

| key_buffer_size | 8388608 |

+-----------------+---------+

查询key_buffer_size使用情况

mysql> show global status like 'key_read%';

+-------------------+-------+

| Variable_name | Value |

+-------------------+-------+

| Key_read_requests | 102 |

| Key_reads | 5 |

+-------------------+-------+

一共有102个索引读请求，有5个请求在内存中没有找到，直接从硬盘读取索引，计算索引未命中缓存的概率：key_cache_miss_rate=Key_reads/Key_read_requests * 100

4、临时表

临时表数量查询命令如下：

mysql> show global status like 'created_tmp%';

+-------------------------+--------+

| Variable_name | Value |

+-------------------------+--------+

| Created_tmp_disk_tables | 4387 |

| Created_tmp_files | 5 |

| Created_tmp_tables | 188846 |

+-------------------------+--------+

每次创建临时表时，Created_tmp_tables都会增加，如果是在磁盘上创建临时表，Created_tmp_disk_tables也会增加，Created_tmp_files表示MYSQL服务创建的临时文件数，比较理想的配置是：Created_tmp_disk_tables/Created_tmp_tables * 100 <= 25%

MYSQL服务器对临时表的配置：

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 |

+---------------------+-----------+

5、Open Table的情况

Open_tables表示打开表的数量

Opened_tables表示打开过的表数量

查询命令如下：

mysql> show global status like 'open%tables%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Open_tables | 3118 |

| Opened_tables | 3183 |

+---------------+-------+

Open_tables / Opened_tables * 100% >= 85%

Open_tables / table_cache * 100% <= 95%

6、进程使用情况

Threads_created表示创建过的线程数，可以用如下命令查看：

mysql> show global status like 'Thread%';

+-------------------+-------+

| Variable_name | Value |

+-------------------+-------+

| Threads_cached | 1 |

| Threads_connected | 56 |

| Threads_created | 120 |

| Threads_running | 1 |

+-------------------+-------+

如果发现Threads_created的值过大，标明MYSQL服务器一直在创建线程，这也是比较耗资源的，可以适当增大thread_cache_size的值，查询服务器thread_cache_size配置，如下：

mysql> show variables like 'thread_cache_size';

+-------------------+-------+

| Variable_name | Value |

+-------------------+-------+

| thread_cache_size | 16 |

+-------------------+-------+

它涉及到两个参数，qurey_cache_size和qurey_cache_type，其中qurey_cache_size设置MYSQL的Query Cache大小，qurey_cache_type设置使用查询缓存的类型，查询命令如下：

mysql> show global status like 'qcache%';

+-------------------------+-----------+

| Variable_name | Value |

+-------------------------+-----------+

| Qcache_free_blocks | 1 |

| Qcache_free_memory | 134200384 |

| Qcache_hits | 0 |

| Qcache_inserts | 0 |

| Qcache_lowmem_prunes | 0 |

| Qcache_not_cached | 3577291 |

| Qcache_queries_in_cache | 0 |

| Qcache_total_blocks | 1 |

+-------------------------+-----------+

MYSQL查询缓存变量的相关解释如下所示：

Qcache_free_blocks：缓存中相邻内存块的个数，数目大说明可能有碎片，flush query cache会对缓存中的碎片进行整理，从而得到一个空闲快。

Qcache_free_memory：缓存中的空闲内存。

Qcache_hits：表示有多少次命中。

Qcache_inserts：每插入一个查询时就会增大。

Qcache_lowmem_prunes：表示有多少条Query因为内存不足而被清除出Query Cache。

Qcache_not_cached：不适合进行缓存的查询数量。

Qcache_queries_in_cache：当前缓存的查询（和响应）数量。

Qcache_total_blocks：缓存中块的数量。

查询服务器query_cache的配置：

mysql> show variables like 'query_cache%';

+------------------------------+-----------+

| Variable_name | Value |

+------------------------------+-----------+

| query_cache_limit | 4194304 |

| query_cache_min_res_unit | 4096 |

| query_cache_size | 134217728 |

| query_cache_type | OFF |

| query_cache_wlock_invalidate | OFF |

+------------------------------+-----------+

各字段解释如下：

query_cache_limit：超过此大小的查询不缓存。

query_cache_min_res_unit：缓存块的最小值。

query_cache_size：查询缓存大小。

query_cache_type：缓存类型。

query_cache_wlock_invalidate：表示当有其他客户端正在进行MyISAN表进行写操作时，读请求是要等WRITE LOCK释放资源后在查询还是允许直接从Query Cache中读取结果，默认为FALSE。

8、排序使用情况

表示系统中对数据进行排序时使用的Buffer，我们可以用如下命令查看：

mysql> show global status like 'sort%';

+-------------------+---------+

| Variable_name | Value |

+-------------------+---------+

| Sort_merge_passes | 0 |

| Sort_range | 239040 |

| Sort_rows | 6264803 |

| Sort_scan | 641147 |

+-------------------+---------+

我们在处理MYSQL故障时，当open_files大于open_files_limit时，MYSQL数据库就会出现卡住的现象，导致WEB服务器打不开相应页面，查询命令如下：

mysql> show global status like 'open_files';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Open_files | 23 |

+---------------+-------+

10、innodb_buffer_pool_size的合理设置

InnoDB存储引擎的缓存机制和MyISAM的最大区别在于，InnoDB不仅仅缓存索引，同时还会缓存实际的数据，此参数用于设置InnoDB最主要的buffer大小，也就是用户表及索引数据的最主要的缓存空间，对InnoDB的整体性能影响很大。

mysql> show status like 'Innodb_buffer_pool_%';

+---------------------------------------+--------------------------------------------------+

| Variable_name | Value |

+---------------------------------------+--------------------------------------------------+

| Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |

| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 181130 14:25:04 |

| Innodb_buffer_pool_resize_status | |

| Innodb_buffer_pool_pages_data | 6897 |

| Innodb_buffer_pool_bytes_data | 113000448 |

| Innodb_buffer_pool_pages_dirty | 2507 |

| Innodb_buffer_pool_bytes_dirty | 41074688 |

| Innodb_buffer_pool_pages_flushed | 1399155 |

| Innodb_buffer_pool_pages_free | 1046 |

| Innodb_buffer_pool_pages_misc | 249 |

| Innodb_buffer_pool_pages_total | 8192 |

| Innodb_buffer_pool_read_ahead_rnd | 0 |

| Innodb_buffer_pool_read_ahead | 5888 |

| Innodb_buffer_pool_read_ahead_evicted | 131 |

| Innodb_buffer_pool_read_requests | 458760679 |

| Innodb_buffer_pool_reads | 340879 |

| Innodb_buffer_pool_wait_free | 147 |

| Innodb_buffer_pool_write_requests | 57994757 |

+---------------------------------------+--------------------------------------------------+

通过此命令得出的结果可以计算出InnoDB buffer pool的read命中率和write命中率

read命中率大约为：(458760679-340879) / 458760679 = 99.92%

write命中率大约为：6897 / 8192 = 84.2%