Mysql状态查询及调优

目录

一、MySQL状态查询

1、查看MySQL连接数 show full processlist

2、查看MySQL数据库状态 show status

二、MySQL调优 

1、开启慢查询日志

2、并发及连接控制

3、缓存参数控制 

3.1 key_buffer_size 

3.2 Key_reads 

3.3  Key_blocks_used

3.4  sort_buffer_size

3.5 read_buffer_size

3.6 thread_cache_size

3.7 table_open_cache

3.8 query_cache_size

3.9 innodb_buffer_pool_size

3.10 innodb_buffer_pool_instances

3.11 innodb_lock_wait_timeout

3.12 innodb_flush_log_at_trx_commit

3.13  sync_binlog

4、临时表

5、表锁、表扫描

6、打开文件数

---

一、MySQL状态查询

1、查看MySQL连接数 show full processlist

1. 如果连接数很多，可将信息打印出来
2. [root@mysql ~]# mysql -uroot -p123456 -e 'show full processlist' > /processlist.txt
3. 若不加上full选项，则最多显示100条记录
4. 若以root帐号登录，你能看到所有用户的当前连接。如果是其它普通帐号，只能看到自己占用的连接。
5. 在调试程序时，如怀疑应用程序中存在申请DB连接未释放的情况，可以通过该命令查询连接数(以应用程序中的user登录)。如程序运行过程中连接数越来越多，则可以判断程序中有DB资源未释放。
6. 修改允许建立的最大连接数：
7. set-variable=max_user_connections=30                    //单用户的连接数
8. set-variable=max_connections=800                           //全局的限制连接数

2、查看MySQL数据库状态 show status

• show status like '%变量名称%'；  
• 常用变量有：

1. Aborted_clients    由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。
2. Aborted_connects    尝试已经失败的MySQL服务器的连接的次数。
3. Connections    试图连接MySQL服务器的次数。
4. Created_tmp_tables    当执行语句时，已经被创造了的隐含临时表的数量。
5. Delayed_insert_threads    正在使用的延迟插入处理器线程的数量。
6. Delayed_writes    用INSERT DELAYED写入的行数。
7. Delayed_errors    用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。
8. Flush_commands    执行FLUSH命令的次数。
9. Handler_delete    请求从一张表中删除行的次数。
10. Handler_read_first    请求读入表中第一行的次数。
11. Handler_read_key    请求数字基于键读行。
12. Handler_read_next    请求读入基于一个键的一行的次数。
13. Handler_read_rnd    请求读入基于一个固定位置的一行的次数。
14. Handler_update    请求更新表中一行的次数。
15. Handler_write    请求向表中插入一行的次数。
16. Key_blocks_used    用于关键字缓存的块的数量。
17. Key_read_requests    请求从缓存读入一个键值的次数。
18. Key_reads    从磁盘物理读入一个键值的次数。
19. Key_write_requests    请求将一个关键字块写入缓存次数。
20. Key_writes    将一个键值块物理写入磁盘的次数。
21. Max_used_connections    同时使用的连接的最大数目。
22. Max_used_connections    响应的连接数，（max_used_connections / max_connections） * 100% = （理想值 ≈ 85%）
23. Not_flushed_key_blocks    在键缓存中已经改变但是还没被清空到磁盘上的键块。
24. Not_flushed_delayed_rows    在INSERT DELAY队列中等待写入的行的数量。
25. Open_tables    打开表的数量。
26. Open_files    打开文件的数量。
27. Open_streams    打开流的数量(主要用于日志记载）
28. Opened_tables    已经打开的表的数量。
29. Questions    发往服务器的查询的数量。
30. Slow_queries    要花超过long_query_time时间的查询数量。
31. Threads_connected    当前打开的连接的数量。
32. Threads_running     代表当前并发数，这个数值一般远低于connected数值。
33. Uptime    服务器工作了多少秒。

二、MySQL调优 

1、开启慢查询日志

• 查看MYSQL服务器的慢查询状态是否开启：

1. mymsql [(none)]> show variables like '%quer%'; 
2. log_slow_queries              | OFF     //当前log_slow_queries状态为OFF， 说明当前并没有开启慢查询       
3. long_query_time               | 10.000000        //查询最长时间默认10秒

• 在mysql的配置文件my.cf,，在mysqld下方加入慢查询的配置语句：

1. vim /etc/my.cnf
2. [mysqld]
3. .. ..
4. slow_query_log=1    //启用慢查询
5. slow_query_log_file=/var/lib/mysql/mysql-slow.log    //指定慢查询日志
6. long_query_time=5    //指定查询的最长时间，超过此时间会被记录
7. log_queries_not_using_indexes=1    //记录未使用索引的查询

• 查看慢查询日志：mysqldumpslow /var/lib/mysql/mysql-slow.log

2、并发及连接控制

1. show global status like 'max%connections';    查看当前已使用的连接数
2. show variables like 'max_connections';      查看默认的最大连接数
3. （max_used_connections / max_connections） * 100% = （理想值 <= 85%）

• 修改默认MYSQL连接数的方法有两个：

1. mysql> set global max_connections=200;
2. 配置文件my.cnf 中添加max_connections=200 重起MYSQL即可

mysql > show global status like 'max%connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 3     |
+----------------------+-------+
1 row in set (0.00 sec)

mysql > show variables like 'max_connections';    
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

3、缓存参数控制 

3.1 key_buffer_size 

1. key_buffer_size 是对MyISAM表性能影响最大的一个参数，默认为8M，指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。一般我们设为16M。不过数据库中多为Innodb表。
2. 如图一共有25629497个索引读取请求，有66071个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率： key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100% = 0.27%，适当加大此key_buffer_size缓存值。

• 修改默认key_buffer_size的方法有两个：

1. mysql> set global key_buffer_size=268435456;
2. 配置文件my.cnf 中添加 key_buffer_size=67108864 重起MYSQL即可

 

mysql> show variables like 'key_buffer_size';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| key_buffer_size | 67108864 |
+-----------------+----------+
 
mysql> show global status like 'key_read%';
+-------------------+----------+
| Variable_name