如何处理mysql数据库并发更新问题

现象

Sysbench对MySQL进行压测, 并发数过大(>5k)时, Sysbench建立连接的步骤会超时.

猜想

猜想: 直觉上这很简单, Sysbench每建立一个连接, 都要消耗一个线程, 资源消耗过大导致超时.

验证: 修改Sysbench源码, 调大超时时间, 仍然会发生超时.

检查环境

猜想失败, 回到常规的环境检查:

MySQL error log 未见异常.

syslog 未见异常.

tcpdump 观察网络包未见异常, 连接能完成正常的三次握手只观察到在出问题的连接中, 有一部分的TCP握手的第一个SYN包发生了重传, 另一部分没有发生重传.

自己写一个简单的并发发生器, 替换sysbench, 可重现场景. 排除sysbench的影响

猜想2

怀疑 MySQL 在应用层因为某种原因, 没有发送握手包, 比如卡在某一个流程上:

检查MySQL堆栈未见异常, 仿佛MySQL在应用层没有看到新连接进入.

通过strace检查MySQL, 发现 accept() 调用确实没有感知到新连接.

怀疑是OS的原因, Google之, 得到参考文档: A TCP “stuck” connection mystery【http://www.evanjones.ca/tcp-stuck-connection-mystery.html】

分析

参考文档中的现象跟目前的状况很类似, 简述如下:

正常的TCP连接流程:

Client 向 Server 发起连接请求, 发送SYN.

Server 预留连接资源, 向 Client 回复SYN-ACK.

Client 向 Server 回复ACK.

Server 收到 ACK, 连接建立.

在业务层上, Client和Server间进行通讯.

当发生类似SYN-flood的现象时, TCP连接的流程会使用SYN-cookie, 变为:

Client 向 Server 发起连接请求, 发送SYN.

Server 不预留连接资源, 向 Client 回复SYN-ACK, 包中附带有签名A.

Client 向 Server 回复ACK, 附带 f(签名A) (对签名进行运算的结果).

Server 验证签名, 分配连接资源, 连接建立.

在业务层上, Client和Server间进行通讯.

当启用SYN-cookie时, 第3步的ACK包因为 某种原因 丢失, 那么:

从Client的视角, 连接已经建立.

从Server的视角, 连接并不存在, 既没有建立, 也没有”即将建立” (若不启用SYN-cookie, Server会知道某个连接”即将建立”)

发生这种情况时:

若业务层的第一个包应是从 Client 发往 Server, 则会进行重发或抛出连接错误

若业务层的第一个包应是从 Server 发往 Client的, Server不会发出第一个包. MySQL的故障就属于这种情况.

TCP握手的第三步ACK包为什么丢失

参考文档中, 对于TCP握手的第三步ACK包的丢失原因, 描述为:

Some of these packets get lost because some buffer somewhere overflows.

我们可以通过Systemtap进一步探究原因. 通过一个简单的脚本:

probe kernel.function("cookie_v4_check").return

{

source_port = @cast($skb->head + $skb->transport_header, "struct tcphdr")->source

printf("source=%d, return=%d\n",readable_port(source_port), $return)

}

function readable_port(port) {

return (port &((1<<9)-1)) <<8 | (port >>8)

}

观察结果, 可以确认cookie_v4_check (syn cookie机制进行包签名检查的函数)会返回 NULL(0). 即验证是由于syn cookie验证不通过, 导致TCP握手的第三步ACK包不被接受.

之后就是对其中不同条件进行观察, 看看是哪个条件不通过. 最终原因是accept队列满(sk_acceptq_is_full):

static inline bool sk_acceptq_is_full(const struct sock  *sk){   return sk->sk_ack_backlog >sk-   >sk_max_ack_backlog}

恢复故障与日志的正关联

在故障处理的一开始, 我们就检查了syslog, 结论是未见异常.

当整个故障分析完成, 得知了故障与syn cookie有关, 回头看syslog, 里面是有相关的信息, 只是和故障发生的时间不匹配, 没有正关联, 因此被忽略.

检查Linux源码:

if (!queue->synflood_warned &&

sysctl_tcp_syncookies != 2 &&

xchg(&queue->synflood_warned, 1) == 0)

pr_info("%s: Possible SYN flooding on port %d. %s.

Check SNMP counters.\n",

proto, ntohs(tcp_hdr(skb)->dest), msg)

可以看到日志受到了抑制, 因此日志与故障的正关联被破坏.

粗看源码, 每个listen socket只会发送一次告警日志, 要获得日志与故障的正关联, 必须每次测试重启MySQL.

解决方案

这种故障一旦形成, 难以检测系统日志中只会出现一次, 在下次重启MySQL之前就不会再出现了Client如果没有合适的超时机制, 万劫不复.

解决方案:

1. 修改MySQL的协议, 让Client先发握手包. 显然不现实.

2. 关闭syn_cookie. 有安全的人又要跳出来了.

3. 或者调高syn_cookie的触发条件 (syn backlog长度). 降低系统对syn flood的敏感度, 使之可以容忍业务的syn波动.

有多个系统参数混合影响syn backlog长度, 参看【http://blog.dubbelboer.com/2012/04/09/syn-cookies.html】

下图为精华总结

请点击输入图片描述

有一个思路，你可以用ajax局部刷新数据，不刷新整个页面，同时用缓存，例如第一个30秒的数据放入缓存，页面从缓存里取数据，第二个30秒，你从数据库取出数据后，写个对象对比的方法，来比对新数据和第一个30秒存入缓存的数据，如果数据没有改变，那页面什么 *** 作也不用做，因为数据没变化，刷新也没意义，如果第二个30秒数据发生变化，那么就刷新缓存为第二个30秒的数据，并且局部刷新页面数据。数据库的查询是避免不了的，所以没发减轻数据库的压力，只能是优化数据的显示。就象我上边提到的，如果前后2个30秒数据没有变化，那么就不要刷新页面。

处理大量数据并发 *** 作可以采用如下几种方法:

1.使用缓存：使用程序直接保存到内存中。或者使用缓存框架: 用一个特定的类型值来保存，以区别空数据和未缓存的两种状态。

2.数据库优化：表结构优化SQL语句优化，语法优化和处理逻辑优化分区分表索引优化使用存储过程代替直接 *** 作。

3.分离活跃数据:可以分为活跃用户和不活跃用户。

4.批量读取和延迟修改: 高并发情况可以将多个查询请求合并到一个。高并发且频繁修改的可以暂存缓存中。

5.读写分离: 数据库服务器配置多个，配置主从数据库。写用主数据库，读用从数据库。

6.分布式数据库: 将不同的表存放到不同的数据库中，然后再放到不同的服务器中。

7.NoSql和Hadoop: NoSql，not only SQL。没有关系型数据库那么多限制，比较灵活高效。Hadoop，将一个表中的数据分层多块，保存到多个节点（分布式）。每一块数据都有多个节点保存（集群）。集群可以并行处理相同的数据，还可以保证数据的完整性。

拓展资料:

大数据（big data），指无法在一定时间范围内用常规软件工具进行捕捉、管理和处理的数据集合，是需要新处理模式才能具有更强的决策力、洞察发现力和流程优化能力的海量、高增长率和多样化的信息资产。

在维克托·迈尔-舍恩伯格及肯尼斯·库克耶编写的《大数据时代》中大数据指不用随机分析法（抽样调查）这样捷径，而采用所有数据进行分析处理。大数据的5V特点（IBM提出）：Volume（大量）、Velocity（高速）、Variety（多样）、Value（低价值密度）、Veracity（真实性）。

参考资料:网页链接

---