mysql基础：连接数
连接数设置
Uptime: 1844934 Threads: 1 Questions: 674 Slow queries: 2 Opens: 143 Flush tables: 2 Open tables: 6 Queries per second avg: 0.000
如果想长时间监控mysql的连接数呢？
一种实现是（或是直接写一个shell脚本，将几个值做参数化，后续可以任意通用）：
for ((i=0;i<100;i++));do mysqladmin -uroot -p123456 status >>list.txt; sleep 5; done 表示每隔5秒，将连接数信息打印到指定文件中，总共打印100次。
在输出文件中指threads数取出来。用awk命令：cat list.txt | awk '{print $4}' > test 或 正则表达式 Threads: \d{1,} 或UE的列模式选取数据。
查看当前所有连接数的详情（也是查看线程详情）
进入MySQL/bin目录
>./mysqladmin -uroot -p123456 processlist
Warning: Using a password on the command line interface can be insecure.
+----+------+-----------------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+----+---------+------+-------+------------------+
| 13 | root | localhost:11551 | | Query | 0 | init | show processlist |
+----+------+-----------------+----+---------+------+-------+------------------+
如果有 SUPER 权限，则可以看到全部的线程，否则，只能看到自己发起的线程（这是指，当前对应的MySQL帐户运行的线程）。
mysql> show processlist; 可以显示前100条连接信息
mysql> show full processlist; 可以显示全部。注意查看进程等待时间以及所处状态 是否locked
说明各列的含义和用途，
id列:一个标识，你要kill 一个语句的时候很有用。
user列: 显示当前用户，如果不是root，这个命令就只显示你权限范围内的sql语句。
host列:显示这个语句是从哪个ip 的哪个端口上发出的。可用来追踪出问题语句的用户。
db列:显示这个进程目前连接的是哪个数据库。
command列:显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。
通常代表资源未释放，如果是通过连接池，sleep状态应该恒定在一定数量范围内
实战范例：因前端数据输出时（特别是输出到用户终端）未及时关闭数据库连接，导致因网络连接速度产生大量sleep连接，在网速出现异常时，数据库too many connections挂死。
简单解读，数据查询和执行通常只需要不到0.01秒，而网络输出通常需要1秒左右甚至更长，原本数据连接在0.01秒即可释放，但是因为前端程序未执行close操作，直接输出结果，那么在结果未展现在用户桌面前，该数据库连接一直维持在sleep状态！
time列:此这个状态持续的时间，单位是秒。
state列:显示使用当前连接的sql语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state只是语句执行中的某一个状态，一个sql语句，已查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成。
info列:显示这个sql语句，是一个判断问题语句的重要依据。
这个命令中最关键的就是state列，mysql列出的状态主要有以下几种：
Checking table 正在检查数据表（这是自动的）。
Closing tables 正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
Connect Out 复制从服务器正在连接主服务器。
Copying to tmp table on disk 由于临时结果集大于 tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。
索引及现有结构无法涵盖查询条件，才会建立一个临时表来满足查询要求，产生巨大的恐怖的i/o压力。很可怕的搜索语句会导致这样的情况，如果是数据分析，或者半夜的周期数据清理任务，偶尔出现，可以允许。频繁出现务必优化之。Copy to tmp table通常与连表查询有关，建议逐渐习惯不使用连表查询。实战范例：某社区数据库阻塞，求救，经查，其服务器存在多个数据库应用和网站，其中一个不常用的小网站数据库产生了一个恐怖的copy to tmp table操作，导致整个硬盘i/o和cpu压力超载。Kill掉该操作一切恢复。
Creating tmp table 正在创建临时表以存放部分查询结果。
deleting from main table 服务器正在执行多表删除中的第一部分，刚删除第一个表。
deleting from reference tables 服务器正在执行多表删除中的第二部分，正在删除其他表的记录。
Flushing tables 正在执行 FLUSH TABLES，等待其他线程关闭数据表。
Killed 发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。
有更新操作锁定
通常使用innodb(支持行锁定)可以很好的减少locked状态的产生，但是切记，更新操作要正确使用索引，即便是低频次更新操作也不能疏忽。如上影响结果集范例所示。
在myisam的时代，locked是很多高并发应用的噩梦。所以mysql官方也开始倾向于推荐innodb。
Sending data
Sending data并不是发送数据，别被这个名字所欺骗，这是从物理磁盘获取数据的进程，如果你的影响结果集较多，那么就需要从不同的磁盘碎片去抽取数据，
偶尔出现该状态连接无碍。
回到上面影响结果集的问题，一般而言，如果sending data连接过多，通常是某查询的影响结果集过大，也就是查询的索引项不够优化。
如果出现大量相似的SQL语句出现在show proesslist列表中，并且都处于sending data状态，优化查询索引，记住用影响结果集的思路去思考。
Sorting for group 正在为 GROUP BY 做排序。
Sorting for order 正在为 ORDER BY 做排序。和Sending data类似，结果集过大，排序条件没有索引化，需要在内存里排序，甚至需要创建临时结构排序
Opening tables 这个过程应该会很快，除非受到其他因素的干扰。例如，在执 ALTER TABLE 或 LOCK TABLE 语句行完以前，数据表无法被其他线程打开。 正尝试打开一个表。
Removing duplicates
正在执行一个 SELECT DISTINCT 方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。
Reopen table 获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。
Repair by sorting 修复指令正在排序以创建索引。
Repair with keycache 修复指令正在利用索引缓存一个一个地创建新索引。它会比 Repair by sorting 慢些。
Searching rows for update 正在讲符合条件的记录找出来以备更新。它必须在 UPDATE 要修改相关的记录之前就完成了。
Sleeping 正在等待客户端发送新请求.
System lock 正在等待取得一个外部的系统锁。如果当前没有运行多个 mysqld 服务器同时请求同一个表，那么可以通过增加 –skip-external-locking参数来禁止外部系统锁。
Upgrading lock INSERT DELAYED正在尝试取得一个锁表以插入新记录。
Updating 正在搜索匹配的记录，并且修改它们。
User Lock 正在等待 GET_LOCK()。
Waiting for tables
该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, 或 OPTIMIZE TABLE。
waiting for handler insert
Waiting for net, reading from net, writing to net
偶尔出现无妨如大量出现，迅速检查数据库到前端的网络连接状态和流量
案例:因外挂程序，内网数据库大量读取，内网使用的百兆交换迅速爆满，导致大量连接阻塞在waiting for net，数据库连接过多崩溃
state列很重要，大部分状态对应很快的操作，只要有一个线程保持同一个状态（比如locked）好几秒钟，那么可能是有问题发生了，需要检查一下。
什么情况下会发生too many connection
- slow query 引起
1. 真正的slow: 该query的确非常慢
2. 伪装的slow: 该query本身并不慢，是受其它因素的影响导致
- sleep 空连接 引起
1. 没有任何query，只是sleep, 这种情况一般是代码里面没有主动及时释放链接导致。
2. 因网络等原因引起的sleep空连接过多
sleep 空链接引起的TMC
- 原因
由于代码没有主动及时的释放链接，那么在db server中存在大量的sleep链接，一旦超过max_connections则报错。
- 解决方案
1. 遇到这样的报错，如果没有及时解决，则会导致后面的业务都一直连不上数据库，影响面很大。
2. 所以，我们第一件事情必须是保护数据库，kill掉这些sleep链接。关于kill这件事，又有很多技巧可以谈
2.1 如果是人工kill，这简直无法完成这样艰巨的任务，因为业务会时刻产生这样的sleep链接，有无尽头
2.2 如果自己写脚本，没秒去kill，当然可行。但是我们却碰到过非常极端的情况，那就是MySQL无法响应你的kill请求。
2.3 所以，这里还有一个更加靠谱的方案就是：设置wait_timeout, 它会自动帮你完成这项庞大且艰巨的任务，且一定可以kill掉
3. 完成上面几个步骤之后，只能保证你的数据库不会被压到，且你有机会登陆进去做一些管理事情，但是要彻底解决还必须让业务方处理这些sleep链接。
3.1 业务团队排查没有释放链接的原因。
3.2 通常，如果可以，DBA协助业务方提供TMC期间top ip，让业务方排查服务哪里异常。
真正的slow query 引起的TMC
一般这种情况，也非常清晰明了，找到它，优化它，当然前提是你的数据库还活着。我们通常有SQL防火墙保护，大大降低了这样的风险。
伪装的slow query 引起的TMC（最难的故障场景）
难点就是：因为它不是真正的slow，优化点难以寻找，所谓对症下药，就是要找到对应的症状是难点。
废话不多说，这里介绍下前一段时间遇到的一次真实的案例，一直想写没时间来着。
1）故障症状
1. too many connection error
2. threads_runnig 非常多
3. 几乎找不到有问题的query，没有明显慢的query
4. 几乎任何语句都变得非常慢
5. 服务器io压力并不大
2）故障分析
* show engine innodb status\G 统计的结果
427 not started sleeping before entering InnoDB
63 not started starting index read
27 not started committing
21 ACTIVE (PREPARED) 1 sec
14 ACTIVE 1 sec preparing
10 not started inserting
9 ACTIVE 1 sec inserting
5 not started estimating records in index range
4 ACTIVE 0 sec inserting
3 COMMITTED IN MEMORY committing
3 ACTIVE 0 sec committing
2 ACTIVE (PREPARED) 1 sec committing
2 ACTIVE 1 sec fetching rows
2 ACTIVE 1 sec committing
1 ACTIVE 1 sec updating or deleting
1 ACTIVE 1 sec starting index read
1 ACTIVE 0 sec starting index read
1 0 sec committing
3）重要参数详解
官方文档的解释我不多说，这里简单介绍下自己的理解
innodb_thread_concurrency : 进入innodb存储引擎的线程数量，如果数量满了，就要排队
innodb_thread_sleep_delay : 排队等候进入innoDB的时候需要睡眠多长时间
innodb_adaptive_max_sleep_delay : 设置一个自适应的最大睡眠时间
innodb_concurrency_tickets: 一旦进入innoDB，就会获取一个票据tickets，在票据期间可以随意进入innoDB不需要排队，如果用完了，理论上则要排队（实测后发现并不是严格这套机制）
当query超过innodb_thread_concurrency时，其余query会等待，即使这样的query非常快，也还是会等待，这就是所谓的伪装的slow query最终解决方式：那就是让并发线程少一点
发生ERROR 1040 (HY000): Too many connections
解决办法：
1，检查mysql server的CPU,IO,内存等状态的变化，是否有明显的升高现象，如果有明显的升高，实时的通过show processlist获取session信息，通过获取到的session信息分析cpu，io以及内存跑高的原因，综合分析（是否是遇到了阻塞或者慢查询）kill掉相关会话来解决
2，检查mysql server的CPU,IO,内存等状态的变化，是否有明显的升高现象，如果没有明显的升高，实时的通过show processlist获取session信息，通过获取到的session信息找到会话来源(看下是否是sleep连接较多)，尝试调整来源主机的应用行为
3，如果参数值比较小，尝试调整mysql的max_connections的值
