唠唠mysql的连接数
1.查看mysql的当前连接数
[root@localhost ~]# mysqladmin -uroot -p123456 status
Uptime:34070 Threads: 1 Questions: 8678 Slow queries: 0 Opens: 910 Flush tables: 43 Open tables: 4 Queries per second avg: 0.254
Therads 打开的连接数
mysql> show status like '%Thread%';
+------------------------------------------+-------+
| Variable_name | Value |
+------------------------------------------+-------+
| Delayed_insert_threads | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Slow_launch_threads | 0 |
| Threads_cached | 0 |
| Threads_connected | 1 |
| Threads_created | 4 |#创建的连接数
| Threads_running | 1 |#打开的连接数
+------------------------------------------+-------+
8 rows in set (0.00 sec
2.查看mysql允许的最大连接数
mysql> show variables like '%max_connections%';+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.00 sec)
3.mysql连接数太多解决方案
说说mysql的慢查询
慢查询产生的原因一般是因为select设计的条件比较多,比如子查询之类的,数据库一直在插卡在哪里,别的语句也无法运行造成阻塞。开发也不是万能的,也会写一些 吊炸天的sql语句,一眼望去看不到结尾。
1. 查看mysql里有哪些进程在运行
mysql>show processlist; #默认一般显示100行 加上full显示的多+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 4 | root | localhost | NULL | Sleep | 882 | | NULL |
| 6 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
2 rows in set (0.00sec)
mysql> show fullprocesslist;+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 4 | root | localhost | NULL | Sleep | 894 | | NULL |
| 6 | root | localhost | NULL | Query | 0 | NULL | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)
[root@localhost ~]# mysqladmin -uroot -p processlist
Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 4 | root | localhost | | Sleep | 1238 | | |
| 7 | root | localhost | | Query | 0 | | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
参数解释:
id #ID标识,要kill一个语句的时候很有用
use #当前连接用户
host #显示这个连接从哪个ip的哪个端口上发出
db #数据库名
command #连接状态,一般是休眠(sleep),查询(query),连接(connect)
time #连接持续时间,单位是秒
state #显示当前sql语句的状态
info #显示这个sql语句
##慢查询发生的时候,state状态一般是 watting for table level lock 观察time 看是否运行的时间很久,紧急就杀死,不紧急找开发去,写的什么玩意(自己知道咋优化
的话,找开发炫耀去)
2. 紧急批量杀死mysql慢查询的脚本
先说自己写的吧
#!/bin/bashfor id in `mysqladmin -uxxx -h 192.168.1.1 -p'xxxx' processlist | grep Query | grep 'paigoushop' | grep 'Sending data' | awk '{print $2}'`
do
mysqladmin-uxxx -h 192.168.1.1 -p'xxx' kill${id}
done
#grep有点多 为了准确定位到自己想杀死的sql
下边这个是网上找的,我也没试过 放这里 万一以后自己用呢
#!/bin/bashfor i in `mysql -Bse 'show full processlist;' | grep -i select |grep -i "Waiting for table level lock" | awk '{print $1}'`
do
mysql-Bse "kill$i"
done
3. 开启慢查询日志记录
总觉得上边的方法不太好,比较被动,对于一些不懂的人,又要骂运维了,我来学一下:“不,什么情况呀,小杨,你这数据库也太慢了吧,怎么搞得,赶紧给我查。”
说起来都是泪呀,那就不说了。用事实说话,把sql语句拿出来,让开发好好看看写的什么玩意。
3.11 查看是否开启慢查询记录
mysql> show variables like '%slow%';+---------------------+--------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------+
| log_slow_queries | ON # 均为on |
| slow_launch_time | 2 #超过两秒就是慢查询了 |
| slow_query_log | ON # |
| slow_query_log_file | /data/mysql/localhost-slow.log |#慢查询文件路径
+---------------------+--------------------------------+
mysql> show variables like '%long%';
+---------------------------------------------------+-----------+
| Variable_name | Value |
+---------------------------------------------------+-----------+
| long_query_time | 2.000000 |#超过两秒就是慢查询了
| max_long_data_size | 314572800 |
| performance_schema_events_waits_history_long_size | 10000 |
+---------------------------------------------------+-----------+
3.12 命令行设置开启慢查询
mysql> set global slow_query_log='ON'; //启用慢查询
Query OK,0 rows affected (0.28sec)
mysql> set global long_query_time=2; //设置成2秒,加上global,下次进mysql已然生效
Query OK,0 rows affected (0.00 sec)
3.13 在my.cnf里设置 需要重启mysql
修改mysql的配置文件my.cnf
在[mysqld]里面加上以下内容
long_query_time = 2#设置超过2s的即为慢查询sql了log-slow-queries = /usr/local/mysql/mysql-slow.log
4.mysqldumpslow分析慢查询日志的工具
先大体看一下 效果
显示执行时间最长的前两个[root@rac3 python]# mysqldumpslow -s t -t 2 /opt/mysql/data/slowquery.logReading mysql slow querylog from /opt/mysql/data/slowquery.log
Count: 2 Time=412.54s (825s) Lock=0.00s (0s) Rows=1.0 (2),
select count(N) from sbtest ,t1 where t1.c=sbtest.cCount: 1 Time=778.20s (778s) Lock=0.00s (0s) Rows=1.0 (1),
select count(N) from sbtest where sbtest.id not in ( select id fromt1 )
#显示次数最多的前两个[root@rac3 python]# mysqldumpslow -s c -t 2 /opt/mysql/data/slowquery.logReading mysql slow querylog from /opt/mysql/data/slowquery.log
Count: 12 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (12),
select count(N) fromtab_1Count: 2 Time=412.54s (825s) Lock=0.00s (0s) Rows=1.0 (2),
select count(N) from sbtest ,t1 where t1.c=sbtest.c
上述结果的解释:
主要功能是, 统计不同慢 sql 的
出现次数(Count),
执行最长时间(Time),
累计总耗费时间(Time()),
等待锁的时间(Lock),
发送给客户端的行总数(Rows),
扫描的行总数(Rows())
mysqldumpslow常用参数
-s 排序ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is defaultal: average lock time
ar: average rows sent
at: average query time
c:count执行次数
l: lock time 锁定时间
r: rows sent 返回记录
t: query time 查询时间-t NUM just show the topn queries-g PATTERN grep: onlyconsider stmts that include this string 就想grep一样 指定过滤的关键字
可根据具体需求具体分析,例如:
分析出使用频率最高的前50条慢sql:
mysqldumpslow-s c -t 50 slow.log如只需分析处理速度最慢的10条sql:
mysqldumpslow –s t-t 10 slow.log
[root@BlackGhost bin]# ./mysqldumpslow -s r -t 20 /usr/local/mysql/mysql-slow.log
[root@BlackGhost bin]# ./mysqldumpslow -s r -t 20 -g 'count' /usr/local/mysql/mysql-slow.log
查看mysql的状态
show global status;
或者
mysqladmin-uroot -p extended-status
参数解释 看