检查mysql当前状态：show engine innodb status;
检查mysql状态
几个命令：
show status 显示系统状态
show variables 显示系统变量
show processlist 显示进程状态
show profiles; 收集执行查询资源信息 默认是关闭的 开启 set profiling=1;
------------------------------------
常用的衍生命令：
连接失败情况
show status like'%aborted%'
Aborted_clients 客户端非法中断连接次数 如果随时间而增大 看看mysql的链接是否正常 或者检查一下网络 或者检查一下max_allowed_packet 超过此设置的查询会被中断（ show variables like'%max%'）
Aborted_connects 连接mysql失败次数 如果指过高 那就该检查一下网络 错误链接失败会在此记录
mysql> show status like'%aborted%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Aborted_clients | 46 |
| Aborted_connects | 1 |
+------------------+-------+
2 rows in set (0.00 sec)
慢查询：
show variables like '%slow%'
show status like '%slow%';
默认是关闭的 开启 set global log_slow_queries=ON ;
Slow_launch_threads 值较大 说明有些东西正在延迟链接的新线程
mysql> show variables like '%slow%' ;
+---------------------+--------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------+
| log_slow_queries | ON |
| slow_launch_time | 1 |
| slow_query_log | ON |
| slow_query_log_file | /data0/mysql/3306/data/ks01-slow.log |
+---------------------+--------------------------------------+
4 rows in set (0.00 sec)
mysql> show status like '%slow%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
+---------------------+-------+
2 rows in set (0.00 sec)连接数
show variables like 'max_connections' 最大连接数
show status like 'max_used_connections'响应的连接数
max_used_connections / max_connections * 100% （理想值 ≈ 85%）
如果max_used_connections跟max_connections相同 那么就是max_connections设置过低或者超过服务器负载上限了
mysql> show variables like 'max_connections' ;
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 5000 |
+-----------------+-------+
1 row in set (0.00 sec)
缓存簇
show status like 'key_blocks_u%';使用和未使用缓存簇(blocks)数
show variables like '%Key_cache%';
show variables like '%Key_buffer_size%';
如果Key_blocks_used * key_cache_block_size 远小于key_buffer_size 那么就意味着内存呗浪费了 应该调大key_buffer_size值
mysql> show status like 'key_blocks_u%';
+-------------------+--------+
| Variable_name | Value |
+-------------------+--------+
| Key_blocks_unused | 213839 |
| Key_blocks_used | 503 |
+-------------------+--------+
2 rows in set (0.00 sec)
mysql> show variables like '%Key_cache%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
+--------------------------+-------+
3 rows in set (0.00 sec)
mysql> show variables like '%Key_buffer_size%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| key_buffer_size | 268435456 |
+-----------------+-----------+
1 row in set (0.00 sec)
线程使用情况
show status like 'Thread%';如果发现Threads_created值过大的话，可以适当增加配置文件中thread_cache_size值
Threads_cached用来缓存线程
mysql> show status like 'Thread%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 4 |
| Threads_connected | 1 |
| Threads_created | 5 |
| Threads_running | 1 |
+-------------------+-------+
4 rows in set (0.00 sec)
打开的文件数
show status like '%open_file%';
show variables like '%open_file%';
如果Open_files和open_files_limit接近 就应该增加open_files_limit的大小
不过mysql打开的文件描述符限制都是OS的文件描述符限制，和配置文件中open_files_limit的设置没有关系
mysql> show status like '%open_file%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files | 178 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like '%open_file%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| innodb_open_files | 300 |
| open_files_limit | 25000 |
+-------------------+-------+
2 rows in set (0.00 sec) 全联接
show status like '%select_full__%';
全链接是无索引链接 最好避免
如果Select_full_range_join过高 说明系统运行了很多范围查询联接表
mysql> show status like '%select_full__%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Select_full_join | 0 |
| Select_full_range_join | 0 |
+------------------------+-------+
2 rows in set (0.00 sec)
打开表情况
show tatus like 'open%tables%';
如果 open_tables接近table_cache的时候，并且Opened_tables这个值在逐步增加，说明table_cache不够用 表缓存没有完全用上 那就要考虑增加table_cache的大小了。还有就是Table_locks_waited比较高的时候，也需要增加table_cache
mysql> show status like 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 95 |
| Opened_tables | 0 |
+---------------+-------+
2 rows in set (0.00 sec)
查询缓存show status like 'qcache%';
show variables like 'query_cache%';察看query_cache的配置
query_cache_limit：超过此大小的查询将不缓存
query_cache_min_res_unit：缓存块的最小大小
query_cache_size：查询缓存大小
query_cache_type：缓存类型，决定缓存什么样的查询，示例中表示不缓存 select sql_no_cache 查询
query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果
mysql> show status like 'qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 3 |
| Qcache_free_memory | 536800368 |
| Qcache_hits | 224134 |
| Qcache_inserts | 382 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 404 |
| Qcache_queries_in_cache | 40 |
| Qcache_total_blocks | 95 |
+-------------------------+-----------+
8 rows in set (0.00 sec)
mysql> show variables like 'query_cache%';+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 2048 |
| query_cache_size | 536870912 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+-----------+
5 rows in set (0.00 sec)
排序情况
show status like 'sort%';
Sort_merge_passes过大 就要增加Sort_buffer_size 但是盲目的增加 Sort_buffer_size 并不一定能提高速度
mysql> show status like 'sort%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
+-------------------+-------+
4 rows in set (0.00 sec)
高速缓存
show variables like 'key_buffer_size';MyISAM 存储引擎键高速缓存 对MyISAM表性能影响很大大
show status like 'key_read%';磁盘读取索引的请求次数
索引未命中缓存的概率=Key_reads / Key_read_requests * 100%
不能以Key_read_requests / Key_reads原则来设置key_buffer_size
Key_reads 将这个值和系统的i/o做对比
mysql> show variables like 'key_buffer_size';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| key_buffer_size | 268435456 |
+-----------------+-----------+
1 row in set (0.00 sec)
mysql> show status like 'key_read%';
+-------------------+--------+
| Variable_name | Value |
+-------------------+--------+
| Key_read_requests | 115144 |
| Key_reads | 1311 |
+-------------------+--------+
2 rows in set (0.00 sec)mysql>
表锁情况
show status like 'table_locks%';
Table_locks_waited显示了多少表呗锁住并导致了mysql的锁等待 可以开启慢查询看一下
mysql> show status like 'table_locks%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 20370 |
| Table_locks_waited | 0 |
+-----------------------+-------+
2 rows in set (0.00 sec)
表扫描情况
show status like 'handler_read%';
show status like 'com_select';
如果Handler_read_rnd_next /Handler_read_rnd 的值过大 那么就应该优化索引、查询
mysql> show status like 'handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 20 |
+-----------------------+-------+
6 rows in set (0.00 sec)mysql> show status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select | 0 |
+---------------+-------+
1 row in set (0.00 sec)
临时表情况
show status like 'created_tmp%';
show variables like 'tmp_table%';
show variables like 'max_heap%';
如果Created_tmp_disk_tables值较高 则有可能是因为：tmp_table_size或者max_heap_table_size太小
或者是选择blob、text属性的时候创建了临时表
Created_tmp_tables 过高的话 那么就有话查询吧mysql> show status like 'created_tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 5 |
| Created_tmp_tables | 0 |
+-------------------------+-------+
3 rows in set (0.00 sec)
mysql> show variables like 'tmp_table%';
+----------------+-----------+
| Variable_name | Value |
+----------------+-----------+
| tmp_table_size | 257949696 |
+----------------+-----------+
1 row in set (0.00 sec)
mysql> show variables like 'max_heap%';
+---------------------+-----------+
| Variable_name | Value |
+---------------------+-----------+
| max_heap_table_size | 257949696 |
+---------------------+-----------+
1 row in set (0.00 sec)
二进制日志缓存
show status like'%binlog%';
show variables like'%binlog%';
如果Binlog_cache_disk_use 和 Binlog_cache_use 比例很大 那么就应该增加binlog_cache_size的值
mysql> show status like'%binlog%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 69166 |
| Com_binlog | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
+------------------------+-------+
5 rows in set (0.00 sec)
mysql> show variables like'%binlog%';
+-----------------------------------------+------------+
| Variable_name | Value |
+-----------------------------------------+------------+
| binlog_cache_size | 4194304 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | MIXED |
| innodb_locks_unsafe_for_binlog | OFF |
| max_binlog_cache_size | 8388608 |
| max_binlog_size | 1073741824 |
| sync_binlog | 0 |
+-----------------------------------------+------------+
7 rows in set (0.00 sec)
