唠唠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连接数太多解决方案
http://www.cnblogs.com/lazyball/p/7424243.html
说说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.00 sec) mysql> show full processlist; +----+------+-----------+------+---------+------+-------+-----------------------+ | 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/bash for 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/bash for 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.28 sec)
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.log Reading mysql slow query log 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.c Count: 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 from t1 )
#显示次数最多的前两个 [root@rac3 python]# mysqldumpslow -s c -t 2 /opt/mysql/data/slowquery.log Reading mysql slow query log from /opt/mysql/data/slowquery.log Count: 12 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (12), select count(N) from tab_1 Count: 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 default al: 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 top n queries -g PATTERN grep: only consider 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
参数解释 看
https://blog.csdn.net/zhangjikuan/article/details/52263301#t120