滴滴MySQL架构及自动化运维工作

在上周4.14 北京3306π活动中,看到朱进桌分享了滴滴的MySQL架构及一些自动化工作,一方面感吧,滴滴同学面对的DB压力也比较大,另一方面也赞叹滴滴的DBA同学牛。

在这里小记一下:

滴滴的MySQL架构

 

这个结构算是一个数据库四层结构和美团,小米的数据库架构都有点类似,整体结合如下:

  • 第一层是基于LVS的一个FNAT接入, 对后面的dbproxy做负载均衡
  • 第二层是DBproxy ,在滴滴的dbproxy中好象没有分库分表功能,可以理解为是ProxySQL的基于port的分组或是读写分离(细节不多)
  • 第三层 基于MHA做的主从高可用切换。 看样子滴滴的数据库还没升级到GTID :),估计也是数据量太大,不好升级。 不过,还是值得考虑升级到MySQL 5.7.
  • 第四层 MySQL主从这块采用,双主结构,后面接两个从库。这个结构GTID环境中非常好运维。有点失误忘了问作者,他们是不是使用的GTID复制。

 

滴滴数据库平台中主要模块

从上面的主要模块来看,大多是基于开源来构建,来的快。 也正所谓作者讲的,平台小的时僮做自动化不划算,平台做大,再来做自动化,推动实在太难。 采用开源的东西,小做改造就可以快速的实用起来。

滴滴DB自动化整体架构介绍

从整体结构上看, 在自动化平台中实现了: 自动化备份恢复, 自动化表结构变更, SQL审计,SQL统计等在线辅助系统。在自动化这块大量依赖于saltstack实现。

在大规模自动化平台saltstack目前在国内许多公司得到验证都是可行的。 而且效果不错,当年飞信的自动化运维也是大量的使用saltstack封装。

滴滴自动化平台技术栈

看了这个图也深深的感受到现在做个MySQL DBA不容易啊。 除了MySQL需要搞定外, 还需要一堆的周边工具待你发现,同时还要会一门语言能把这些串起来,现在运维及云平台语言,基于是Python和Golang的天下。 作者讲解的东西比较多,整体上觉的滴滴还属于中规中矩,踏实做事。

如果想了解更多3306π是滴滴自动化 PPT信息可以关注 3306pai公众号,回复“bj”获取 3306π北京的PPT打包下载。

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

新一代MySQL高可用:MySQL Plus

在基于MySQL传统复制的时代(MySQL版本低于5.5)MHA在MySQL高可用中可以说是独领风骚。在MySQL 5.6及GTID的出现后,MHA在这方面就显的不给力,和MHA作者交流,作者基本放弃该软件的维护,MHA作者现就职在Facebook,也没在使用MHA,他也认为在GTID环境下MHA存在的价值不大,不过你如果你还在使用传统复制,还是可以考虑使用MHA做主从的高可用(太老了,建议升级)。

下面我们围绕以下几点来讨论一下:

  1. 在MySQL 5.7 后为什么不需要MHA
  2. MySQL Plus 是什么,能解决什么问题
  3. MySQL Plus看他们如何搞定金融支持

MySQL 5.7后为什么不需要MHA

基于MySQL 5.7 GTID复制已经成熟,另外基于MySQL5.7的增强半同步性进一步提升: 所以在使用MySQL 5.7的复制可以使用: MySQL 5.7+GTID+增强半步, 在该结构中, 不存在会丢数据的问题。 所以MHA在这个结构基本失去了存在的意义。

但使用: MySQL 5.7+GTID+增强半步,也意为着新的知识,可能需要DBA同学们也要更新一下知识。而且在MySQL 5.7中引入binlog group commit, 又是对复制的一个加速。 所以说MySQL5.7 在复制完整性及性能上都有较大的提升,建议没升级的同学尽快升级了。

官方对MySQL 5.7的测试传送门: https://www.mysql.com/why-mysql/benchmarks/

MySQL Plus是什么,能解决什么问题

在3306π北京活动中 青云的蒙哲分享了青云RDS中高可用组件: MySQL Plus。 MySQL Plus是基于一套Raft构建的MySQL中自动选主及维护主从的套件,整体结构如下:

在该结构中Xenon之间会进行通信,在该结构中推荐三个节点的MySQL构建复制,听作者讲也支持两个节点的MySQL构建集群。

在MySQL Plus主要解决:

  1.  集群切换的强一致性(从上面架构看,更多的依赖于MySQL增强半同步,MySQL Plus在控制切换时,会做复制完成校验,从而且保证数据一致)
  2. 主从秒级别切换
  3. 无中心化自动选主

MySQL Plus看他们如何搞定金融环境

MySQL Plus 可以简单的理解是一个MySQL 5.7 GTID增强半同步复制的高可用管理组件。 在MySQL半同步配置方面,为了支持金融业务,青云给的配置如下:

  • rpl_semi_sync_master_wait_no_slave=ON
  • rpl_semi_sync_master_timeout=1000000000000000000
  • rpl_semi_sync_master_wait_point=AFTER_SYNC

看到这个配置我才想明白为什么他们建议是三个节点,在rpl_semi_sync_master_timeout配置上可以说不允许退化到异步复制, 另外RadonDB负责人交流,在MySQL Plus架构中主节点上至少要求一个Slave给半同步应答。 所以2个节点对架构的稳定性也是一个保证。 另外在金融环境中,作者推荐所有请求都在主库上完成, 免的存在复制延迟造成交易数据异常。

在金融架构中,青云也提供一套基于MySQL Plus之上构建建的分库分表机制, 基于MySQL的事务强一致性约,在该平台支持OLTP和OLAP更感觉有点NewSQL的感觉。

下面是官方给的一个总结:

重大消息

如果想了解更多3306π是青云MySQL Plus PPT信息可以关注 3306pai公众号,回复“bj”获取 3306π北京的PPT打包下载。

MySQL Plus 官方要开源了,希望通过MySQL Plus给MySQL 5.7 GTID复制提供一个新的高可用方案。

更重大消息: QingCloud RDS : RadonDB也要开源了,大家期待一下吧。 如果感兴趣提前体验或是更多的关注 RadonDB发展,也可以加到QQ群:748415432  和群主及 RadonDB负责人直接对喊话。   技术在于相互沟通,你的建议也需是将来一个非常不错的功能。

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

招商银行为什么使用MySQL

      对于金融行业使用MySQL可以说也比较早,例如腾讯的财富通从开始到现都是基于MySQL构建,但对于传统银行企业使用MySQL我也是一直存在好奇的想法。 这次在3306π北京站有幸听了招商银行王龙的分享也解决了我几个疑问,特Mark一下。

  1. 招商银行在走向开源的道路为什么选择了MySQL,而不是其它数据库?
  2. 招商银行在使用MySQL大概的规模及情况是什么?
  3. 招商银行是如何管理他们的MySQL?
  4. 他们成功的心得是什么?

招商银行为什么选择了MySQL?

在这一点上,听作者讲他们内部也做了大量的调研,例如PostgreSQL,TiDB,MySQL等。 最终选择了MySQL,不是说MySQL最优秀,也不是其它DB不优秀, 更重要的MySQL可以对他们的业务模型的支撑上更方便一点,同时团队的更加容易上手。具体的原因如下:
  1. 明确业务模型,不为可能需要的功能买单. MySQL足以支持现有的业务, 而且基准测试性能不弱。
  2. 本着简单的原则,不选复杂。 更多的人熟悉,更利于团队开发。
  3. 组建分布式DB,更方便快速扩容。
  4. 通过主从复制,读写分离等技术,更方便的实现多地多活技术。
  5. 选择MySQL更利用云服务化和DevOPS开发。

招商银行在MySQL使用上大概的规模

目前招商银行在使用MySQL将近两年, 目前作者讲有单业务集群分片在上百规模。 按这个分片来推算可以说是我听过最大的MySQL分布式系统。 该集群主要用于银行帐单查询相关业务。

招商银行如何管理他们的MySQL

这个直接引入原文中的PPT吧。 真的不能小看招商银行两年时间在MySQL云化平台做的相当出色。
上面可以看出来他们在资源申请上分三个类型,对于资源对齐方面确实做的不错。 想做MySQL平台的同学,仔细看看上面的两个图片。

招商银行在MySQL大规模使用上成功的心得

在这点招商银行总结金融架构13条(请阅读PPT),也很赞。 这里限于篇幅不在逻列。 我更多的关注对方在MySQL上的使用,这里看一个他们的分库分表决策: 分库常见几个基本问题: 在分库分表中作者见解也比较独到。也可以说是经过实际项目磨练出来的经验。 PPT已经发布在3306π的公众号上,关注3306pai公众号,回复“bj” 获取全部PPT。 作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

禁止修改varchar到int|[运维规范]

在MySQL更改数据类型前一定要特别小心,分析一下是不是可行,另外在更改前,需要先进行备份,备份,备份!!!

环境描述

表结构:

CREATE TABLE `t_mobile` (
 `mobile_no` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

写入数据:

root@zhishutang:mysql3306.sock [zst]>insert into t_mobile(mobile_no) values('13501245755'),('18800108001'),('13996000001');

确认数据无误:

root@zhishutang:mysql3306.sock [zst]>select * from t_mobile;
+-------------+
| mobile_no |
+-------------+
| 13501245755 |
| 18800108001 |
| 13996000001 |
+-------------+
3 rows in set (0.00 sec)

溢出

修改数类型:

root@zhishutang:mysql3306.sock [zst]>alter table t_mobile change mobile_no mobile_no int;
Query OK, 3 rows affected, 3 warnings (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 3

查看警告:

root@zhishutang:mysql3306.sock [zst]>show warnings;
+---------+------+----------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------+
| Warning | 1264 | Out of range value for column 'mobile_no' at row 1 |
| Warning | 1264 | Out of range value for column 'mobile_no' at row 2 |
| Warning | 1264 | Out of range value for column 'mobile_no' at row 3 |
+---------+------+----------------------------------------------------+
3 rows in set (0.00 sec)

到这里实质就可以宣布,死定了。数据已溢出。

查看数据:

root@zhishutang:mysql3306.sock [zst]>select * from t_mobile;
+------------+
| mobile_no |
+------------+
| 2147483647 |
| 2147483647 |
| 2147483647 |
+------------+
3 rows in set (0.00 sec)

这里真是的男人哭吧,哭吧, … 如果是线上环境,想死的心估计大家都有了,不是简单的哭了。如果没有备份,这么重要一例数据没了,有可能意为着项目也有可能受到严重的影响。

这时也不要心存幻想在改回去就好,来看一下操作,请死心!!!

root@zhishutang:mysql3306.sock [zst]>alter table t_mobile change mobile_no mobile_no varchar(11);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

root@zhishutang:mysql3306.sock [zst]>select * from t_mobile;
+------------+
| mobile_no |
+------------+
| 2147483647 |
| 2147483647 |
| 2147483647 |
+------------+
3 rows in set (0.00 sec)

结论

  • 生产环境更数据类型明确提出: 不允许varchar 改成int.
  • 更改数据前一定要做好备份,无论是update,delete,或是数据类型更改。

操作Tips: 如保备份一列数据?

select pk , 修改的列 from tb where 条件 ;

用命令行记录也可以,用outfile处理也行。

恢复可以用awk反向生成update语句:
cat bak.txt |awk ‘{print “update tb set 修改列名=“ 2,”where pk=1”;”}

大概这样生成。 在执行恢复即可。

 

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

[TIPS]安装数据库提示无法解析机器名处理

作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

在做MySQL初始化时,如果机器的名不能进行反解会出现以下错误:

WARNING: The host 'node2' could not be looked up with resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges !
#/usr/local/mysql/bin/resolveip node2

/usr/local/mysql/bin/resolveip: Unable to find hostid for ‘node2’: host not found

处理过程如下

1. 查看机器的名

#hostanme

node2

2. 查看/etc/hosts文件

#cat /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

可见/etc/hosts中无相应的机器名

添ip(本机的ip) 到机器的对应到/etc/hosts中:

最终/etc/hosts内容如下:

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
10.10.60.148 node2

3.使用resolveip确认是否ok

#/usr/local/mysql/bin/resolveip node2

IP address of node2 is 10.10.60.148

4. 在次运行初始化程序

cd /usr/local/mysql
./script/mysql_db_install

Good luck!

PHP调用存储过程返回值不一致的问题

作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式: wubingxi#gmail.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.
今天遇一个同学聊存储过程返回值经常得到意外的值为null, 因为白天有事,晚上给做一个实验放在这里供有相应问题的同学查看一下。
存储过程:

delimiter //
create procedure usp_s2(out par1 int)
begin
	select inet_ntoa(ip) , port from proxy_list limit 5;
	select count(*) into par1 from proxy_list;
END//
delimiter ;

session 1执行:

mysql> call usp_s2(@a);
+---------------+------+
| inet_ntoa(ip) | port |
+---------------+------+
| 1.34.21.86    | 8088 |
| 1.34.59.50    | 8088 |
| 1.34.69.15    | 8088 |
| 1.34.73.110   | 8088 |
| 1.34.76.218   | 8088 |
+---------------+------+
5 rows in set (0.00 sec)

Query OK, 1 row affected (0.01 sec)

mysql> select @a;
+------+
| @a   |
+------+
| 4430 |
+------+
1 row in set (0.00 sec)

session 2执行:

mysql> select @a;
+------+
| @a   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

可见两个session得到的结果不一致。 基本可以肯定两次调用落入不同的会话中会得到不同的值。
为了一致可以如用如下调用:

multi_query("call usp_s2(@total); select @total;");

if ($result) {
        do {
        if ($r = $db->store_result()) {
                if ( $r->field_count == 2){
                        while( $row = $r->fetch_row() ){
                                print "ip: $row[0], port: $row[1]\n";
                        }
                }else{
                        $row  = $r->fetch_row();
                        print "total: $row[0]\n";
                }
        }
        } while ( $db->next_result() );
}
$db->close();
?>
$php t_proc_return.php 
ip: 1.34.21.86, port: 8088
ip: 1.34.59.50, port: 8088
ip: 1.34.69.15, port: 8088
ip: 1.34.73.110, port: 8088
ip: 1.34.76.218, port: 8088
total: 4430

Good luck.

记录一次truncate操作数据恢复

作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式: wubingxi#gmail.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

实际线上的场景比较复杂,当时涉及了truncate, delete 两个操作,经确认丢数据差不多7万多行,等停下来时,差不多又有共计1万多行数据写入。 这里为了简单说明,只拿弄一个简单的业务场景举例。

测试环境: Percona-Server-5.6.16
日志格式: mixed 没起用gtid

表结构如下:

CREATE TABLE `tb_wubx` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

基于某个时间点有一个备份或是有全量的binlog是能恢复数据的一个唯一保证。 例如我们的备份就是一个表结构创建语句,binlog pos相关信息: mysql-bin.000004 , 4,然后进行了如下:

–t1时间 程序写入:
insert into tb_wubx(name) values(‘张三’),(‘李四’);
insert into tb_wubx(name) values(‘隔壁老王’);
–t2时间 某个人员失误
truncate table tb_wubx;
–t3时间 程序写入
insert into tb_wubx(name) values(‘老赵’);
update tb_wubx set name=’老赵赵’ where id=1;

现在表里的数据情况:

mysql>select * from tb_wubx;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 老赵赵 |
+----+-----------+
1 row in set (0.00 sec)

可以见truncate table操作后,表的自增id又变更为从1开始,原来写入的数据应该是:
+—-+———–+
| id | name |
+—-+———–+
| 1 | 张三 |
+—-+———–+
| 2 | 李四 |
+—-+———–+
| 3 | 隔壁老王 |
+—-+———–+

如果没生truncate table操作,实际的数据应该为:
+—-+———–+
| id | name |
+—-+———–+
| 1 | 张三 |
+—-+———–+
| 2 | 李四 |
+—-+———–+
| 3 | 隔壁老王 |
+—-+———–+
| 4 | 老赵赵 |
+—-+———–+

而且线上的恢复那个表时和序序开发人员了解才知道,原来那个id和缓存及其它地方有依赖,因为id乱了,也会造成程序错乱。这个时间修复id在程序层错乱的事,留给开发人员了关建是给他们讲明白恢复的结果是什么样,我们的关建任务是把数据恢复出来。好,接下来的工作是开始从binlog中恢复数据。
利用: show binary logs; 查看当的log文件分布, 然后利用show binlog events in ‘binary log文件’; 查看log文件的内容,目的是找到truncate发生的日志位置。
另外因为基于备份(由log的启始位置)或是从量log, 如果基于备份有log的起始位置,我们需要处理的log文件是启始位置到发生truncate的日值(后面的数据处理不了,会发生主建冲突的错误造成truncate后的数据不能恢复),
如果是全量日志,需要从创建完mysql后库后的日志去处理到当前的发生truncate的位置(后面数据会因为主建冲突写不进去)
恢复准备工作,创建一个库用于恢复数据,这里创建了一个re_wubx, 及原结构的表: tb_wubx (相当于恢复了备份,过程省略)
作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式: wubingxi#gmail.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 143 |
| mysql-bin.000002 | 261 |
| mysql-bin.000003 | 562 |
| mysql-bin.000004 | 1144 |
+------------------+-----------+
4 rows in set (0.00 sec)

我这里有一个备份文件就是那个创建表的sql语句,位置是mysql-bin.000004 , 4
在这个案例里我只用cover住mysql-bin.000004这个文件。

mysql>show binlog events in 'mysql-bin.000004';
+------------------+------+-------------+-----------+-------------+----------------------------------------------------+
| Log_name         | Pos | Event_type    | Server_id   | End_log_pos | Info |
+------------------+------+-------------+-----------+-------------+----------------------------------------------------+
| mysql-bin.000004 | 4   | Format_desc   | 753306 | 120 | Server ver: 5.6.16-64.2-rel64.2-log, Binlog ver: 4 |
| mysql-bin.000004 | 120 | Query         | 753306 | 209 | use `wubx`; truncate table tb_wubx |
| mysql-bin.000004 | 209 | Query         | 753306 | 281 | BEGIN |
| mysql-bin.000004 | 281 | Table_map     | 753306 | 334 | table_id: 91 (wubx.tb_wubx) |
| mysql-bin.000004 | 334 | Write_rows    | 753306 | 393 | table_id: 91 flags: STMT_END_F |
| mysql-bin.000004 | 393 | Xid           | 753306 | 424 | COMMIT /* xid=1073 */ |
| mysql-bin.000004 | 424 | Query         | 753306 | 496 | BEGIN |
| mysql-bin.000004 | 496 | Table_map     | 753306 | 549 | table_id: 91 (wubx.tb_wubx) |
| mysql-bin.000004 | 549 | Write_rows    | 753306 | 602 | table_id: 91 flags: STMT_END_F |
| mysql-bin.000004 | 602 | Xid           | 753306 | 633 | COMMIT /* xid=1074 */ |
| mysql-bin.000004 | 633 | Query         | 753306 | 722 | use `wubx`; truncate table tb_wubx |
| mysql-bin.000004 | 722 | Query         | 753306 | 794 | BEGIN |
| mysql-bin.000004 | 794 | Table_map     | 753306 | 847 | table_id: 92 (wubx.tb_wubx) |
| mysql-bin.000004 | 847 | Write_rows    | 753306 | 894 | table_id: 92 flags: STMT_END_F |
| mysql-bin.000004 | 894 | Xid           | 753306 | 925 | COMMIT /* xid=1081 */ |
| mysql-bin.000004 | 925 | Query         | 753306 | 997 | BEGIN |
| mysql-bin.000004 | 997 | Table_map     | 753306 | 1050 | table_id: 92 (wubx.tb_wubx) |
| mysql-bin.000004 | 1050 | Update_rows  | 753306 | 1113 | table_id: 92 flags: STMT_END_F |
| mysql-bin.000004 | 1113 | Xid          | 753306 | 1144 | COMMIT /* xid=1084 */ |
+------------------+------+-------------+-----------+-------------+----------------------------------------------------+
19 rows in set (0.00 sec)

看到这个表刚开始就发生一次truncate, 那其实也可以说明我就恢复刚开始那个truncate到后来那个误操作的truncate table的语句之间的数据就是丢失的数据。
这个恢复可以从mysql-bin.000004 pos: 4到mysql-bin.000004 pos: 633 即:

mysqlbinlog --rewrite-db='wubx->re_wubx' --start-position=4 --stop-position=633 mysql-bin.000004 |mysql -S /tmp/mysql.sock re_wubx

恢复结果如下:

mysql -S /tmp/mysql.sock re_wubx;
mysql>select count(*) from tb_wubx;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.02 sec)

mysql>select * from tb_wubx;
+----+--------------+
| id | name |
+----+--------------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 隔壁老王 |
+----+--------------+
3 rows in set (0.00 sec)

mysql>insert into tb_wubx(name) select name from wubx.tb_wubx;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> rename table wubx.tb_wubx to wubx.bak_tb_wubx;
Query OK, 0 rows affected (0.04 sec)

mysql> rename table re_wubx.tb_wubx to wubx.tb_wubx;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from wubx.tb_wubx;
+----+--------------+
| id | name |
+----+--------------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 隔壁老王 |
| 4 | 老赵赵 |
+----+--------------+
4 rows in set (0.00 sec)

恢复完成。
想一想,如果我跳过那个truncate继续执行那些binlog会怎么样 ?

php连接不上MySQL问题排查

作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式: wubingxi#gmail.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

现象
在PHP error log里发现:
PHP Warning: mysqli::mysqli(): (HY000/2003): Can’t connect to MySQL server on ‘XXX.XXX.XXX.XXX’ (99) in /u1/www/XXXX.php on line 10
PHP Warning: mysqli::close(): Couldn’t fetch mysqli in /u1/www/XXXX.php on line 11
推断:只有在高并发的环境下出现

诊断分析:
通过MySQL数据库上抓包,没发现异常。又把目标转到php 服务器上。
BTW:
linux开着selinux连接MySQL在测试中基本上属于1ms+,禁掉selinux后在0.96左右。selinux还是要禁掉的。
既然又怀疑是PHP的问题就写一个程序测试(禁掉selinux后):
cat tconn.php

function microtime_float()
{
list($usec, $sec) = explode(" ", microtime());
return ((float)$usec + (float)$sec);
}

$time_start = microtime_float();
for ( $i=0; $i<30000; $i++){  
$dbh=new mysqli("XXX.XXX.XXX.XXX", "wubx", "wubxwubx", "userdb", 3308);
$dbh->close();
}
$time_end= microtime_float();
$time_use= ($time_end - $time_start)/30000;
print "$time_use\n";

#php tconn.php
0.00090954260031382
再次运行就开始大量的报错。

PHP Warning: mysqli::mysqli(): (HY000/2003): Can't connect to MySQL server on 'XXX.XXX.XXX.XXX' (99) in /u1/www/XXXX.php on line 10
PHP Warning: mysqli::close(): Couldn't fetch mysqli in /u1/www/XXXX.php on line 11

中止该程序后,通过
#strace php tconn.php 运行
得到:

connect(3, {sa_family=AF_INET, sin_port=htons(3308), sin_addr=inet_addr("XXX.XXX.XXX.XXX")}, 16) = -1 EADDRNOTAVAIL (Cannot assign requested address)
shutdown(3, 2 /* send and receive */) = -1 ENOTCONN (Transport endpoint is not connected)

看到这个大概明白是本地的网络可能注册不上了,也难怪在MySQL抓包看也正常。
参考:http://www.unixguide.net/network/socketfaq/4.5.shtml

看样子是本地tcp不能重用造成的。改一下在测试
sysctl -w net.ipv4.tcp_tw_reuse=1;
在次测试问题不存在了。在这个上面碰了一下后顺便改一下/etc/sysctl.conf添加:

net.ipv4.tcp_max_syn_backlog = 819200
net.core.netdev_max_backlog = 400000
net.core.somaxconn = 4096
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_tw_recycle=0

#sysctl -p

总结:
php连接MySQL的原因有很多,这个只是一个高并发环境会出现的问题也比较难查。其它连不上只要好重现都比较好查,一般通过抓包都能分析出来。 这个同样适用于做为App的Server大量和中间层交互的也需要注意。

MySQL数据库负载很高连接数很多怎么处理

作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式: wubingxi#gmail.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

在MySQL数据库连接数很多,而且大多属于活跃的状态时MySQL机器基本上负载很高,属于基本上快要死去的状态了.
这时怎么办呢?

有可能两个办法.
第一先限制Innodb的并发处理.如果innodb_thread_concurrency = 0 可以先改成 16或是64 看机器压力,如果
非常大,先改成16让机器的压力下来,然后慢慢增达,适应自已的业务.
处理方法: set global innodb_thread_concurrency=16;

第二: 对于连接数已经超过600或是更多的情况,可以考虑适当的限制一下连接数,让前端报一下错,也别让DB挂了.
DB在了,总是可以用来加载一下数据,当数据加载到了nosql里了,慢慢的DB压力也会降下来的.
限制单用户连接数在500以下. 如:
set global max_user_connections=500;

(MySQL随着连接数的增加性能会是下降的,这也是thread_pool出现的原因)
另外对于有的监控程序会读取information_schema下面的表的程序可以考虑关闭下面的参数
innodb_stats_on_metadata=0
set global innodb_stats_on_metadata=0;

这个参数主要防止对读取information_schema时造成大量读取磁盘进行信息统计(如果慢查询中出现关于information_schema中表时,也可以考虑禁用该参数)

处理依据:
当学校的一个食堂一分钟只能为两个打饭, 忽然来了100个时人来打饭,又没排队, 不出会现了打饭的师傅要用点时间
去选择为那个用户服务了, 人越多,场面就越乱, 难免出现用户大吼该他的场面, 最后有可能就出现不是打饭了,而时之间相互
打架了,打饭的师傅也将收到同时有90个以上的Server too busy. 如果能排一下队.最多也就50分钟能处理完了.

以前办法,应该可以让MySQLD不会挂掉.如果业务支撑受到限制,还是想办法处理一下.

问题:
高峰期的业务业务支撑数就是服务器的最终需求数吗?

FAQ:为什么binlog大小会大于max_binlog_size

作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式: wubingxi#gmail.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

CASE:
mysql的IO处理上有抖动, 从日值上观查binlog最大的有(512M)大于max_binlog_size(128M)

原因:
当mysql在处理大事务时,不会进行binlog切换,所有的日值还是会写到一个数据文件里.
如: bulk insert之类的操作
解决办法:
拆分大事务. 或是把大事务处理放到业务低峰期,避免大事务对业务的冲击.

特别提示: 

看到这种现象可以通过分析binlog查到是什么操作, 可以通过业务的方面考虑一下怎么规避.