目录
1、查看MySQL连接数 show full processlist
3.10 innodb_buffer_pool_instances
3.12 innodb_flush_log_at_trx_commit
一、MySQL状态查询
1、查看MySQL连接数 show full processlist
- 如果连接数很多,可将信息打印出来
- [root@mysql ~]# mysql -uroot -p123456 -e 'show full processlist' > /processlist.txt
- 若不加上full选项,则最多显示100条记录
- 若以root帐号登录,你能看到所有用户的当前连接。如果是其它普通帐号,只能看到自己占用的连接。
- 在调试程序时,如怀疑应用程序中存在申请DB连接未释放的情况,可以通过该命令查询连接数(以应用程序中的user登录)。如程序运行过程中连接数越来越多,则可以判断程序中有DB资源未释放。
- 修改允许建立的最大连接数:
- set-variable=max_user_connections=30 //单用户的连接数
- set-variable=max_connections=800 //全局的限制连接数
2、查看MySQL数据库状态 show status
- show status like '%变量名称%';
- 常用变量有:
- Aborted_clients 由于客户没有正确关闭连接已经死掉,已经放弃的连接数量。
- Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。
- Connections 试图连接MySQL服务器的次数。
- Created_tmp_tables 当执行语句时,已经被创造了的隐含临时表的数量。
- Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。
- Delayed_writes 用INSERT DELAYED写入的行数。
- Delayed_errors 用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。
- Flush_commands 执行FLUSH命令的次数。
- Handler_delete 请求从一张表中删除行的次数。
- Handler_read_first 请求读入表中第一行的次数。
- Handler_read_key 请求数字基于键读行。
- Handler_read_next 请求读入基于一个键的一行的次数。
- Handler_read_rnd 请求读入基于一个固定位置的一行的次数。
- Handler_update 请求更新表中一行的次数。
- Handler_write 请求向表中插入一行的次数。
- Key_blocks_used 用于关键字缓存的块的数量。
- Key_read_requests 请求从缓存读入一个键值的次数。
- Key_reads 从磁盘物理读入一个键值的次数。
- Key_write_requests 请求将一个关键字块写入缓存次数。
- Key_writes 将一个键值块物理写入磁盘的次数。
- Max_used_connections 同时使用的连接的最大数目。
- Max_used_connections 响应的连接数,(max_used_connections / max_connections) * 100% = (理想值 ≈ 85%)
- Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。
- Not_flushed_delayed_rows 在INSERT DELAY队列中等待写入的行的数量。
- Open_tables 打开表的数量。
- Open_files 打开文件的数量。
- Open_streams 打开流的数量(主要用于日志记载)
- Opened_tables 已经打开的表的数量。
- Questions 发往服务器的查询的数量。
- Slow_queries 要花超过long_query_time时间的查询数量。
- Threads_connected 当前打开的连接的数量。
- Threads_running 代表当前并发数,这个数值一般远低于connected数值。
- Uptime 服务器工作了多少秒。
二、MySQL调优
1、开启慢查询日志
- 查看MYSQL服务器的慢查询状态是否开启:
- mymsql [(none)]> show variables like '%quer%';
- log_slow_queries | OFF //当前log_slow_queries状态为OFF, 说明当前并没有开启慢查询
- long_query_time | 10.000000 //查询最长时间默认10秒
- 在mysql的配置文件my.cf,,在mysqld下方加入慢查询的配置语句:
- vim /etc/my.cnf
- [mysqld]
- .. ..
- slow_query_log=1 //启用慢查询
- slow_query_log_file=/var/lib/mysql/mysql-slow.log //指定慢查询日志
- long_query_time=5 //指定查询的最长时间,超过此时间会被记录
- log_queries_not_using_indexes=1 //记录未使用索引的查询
- 查看慢查询日志:mysqldumpslow /var/lib/mysql/mysql-slow.log
2、并发及连接控制
- show global status like 'max%connections'; 查看当前已使用的连接数
- show variables like 'max_connections'; 查看默认的最大连接数
- (max_used_connections / max_connections) * 100% = (理想值 <= 85%)
- 修改默认MYSQL连接数的方法有两个:
- mysql> set global max_connections=200;
- 配置文件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
- key_buffer_size 是对MyISAM表性能影响最大的一个参数,默认为8M,指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。一般我们设为16M。不过数据库中多为Innodb表。
- 如图一共有25629497个索引读取请求,有66071个请求在内存中没有找到直接从硬盘读取索引,计算索引未命中缓存的概率: key_cache_miss_rate = Key_reads / Key_read_requests * 100% = 0.27%,适当加大此key_buffer_size缓存值。
- 修改默认key_buffer_size的方法有两个:
- mysql> set global key_buffer_size=268435456;
- 配置文件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