之前整理了一期 Magento 服务器负载居高不下问题排除，没想到观察了几天发现服务器负载依然居高不下。

最终靠着 cloudflare 中设置访问规则，屏蔽了大量恶意爬虫，服务器负载终于恢复正常。

这里记录一下排除过程。特别是其中的 MySQL / MariaDB 相关的排查过程很有参考价值。

高负载依旧

在重建了 Magento 索引之后，没想到 MySQL 的 CPU 占用依旧很高，导致服务器负载居高不下。

排除交换分区的影响

我本以为是系统交换分区（swap）导致的高负载问题，查看了系统的 swap 使用情况，发现 swap 使用率很低，几乎没有使用到 swap 分区。

$ free -h
               total        used        free      shared  buff/cache   available
Mem:           7.7Gi       1.0Gi       6.5Gi       5.7Mi       363Mi       6.7Gi
Swap:          2.0Gi          0B       2.0Gi

以上是我本机的内存使用情况，并非服务器的，仅仅是为了说明 swap 使用率很低，几乎没有使用到 swap 分区。
这里的 available 内存是指系统可用的内存，free 内存是指系统空闲的内存，buff/cache 是指系统缓存的内存。总的来说，系统内存使用情况良好，没有出现内存不足的情况。

如果想看具体每个进程的 SWAP 分区使用情况，可以使用 smem 工具，安装方法如下：

sudo apt install smem

查看每个进程的 SWAP 分区使用情况：

$ sudo smem -t -k -s uss -r | head -25
  PID User     Command                         Swap      USS      PSS      RSS
  280 mysql    /usr/sbin/mysqld                   0   401.6M   403.6M   410.8M
  308 root     /usr/bin/dockerd -H fd:// -        0    78.8M    78.9M    81.9M
  211 root     /usr/bin/containerd                0    48.1M    48.1M    49.5M
  179 root     /usr/libexec/wsl-pro-servic        0    16.0M    16.0M    17.5M
  166 redis    /usr/bin/redis-server 127.0        0    13.0M    14.1M    20.3M
  164 root     php-fpm: master process (/e        0    11.2M    17.5M    34.2M

上面命令是查看 USS 占用排名前 25 的进程，USS 是指进程独占的内存大小，PSS 是指进程共享的内存大小，RSS 是指进程实际使用的物理内存大小。

show processlist 看不出问题

在 MySQL 中使用 show processlist 命令查看当前正在执行的 SQL 语句，发现没有明显的异常。
已经看不到满查询的情况了。之前没有建 magento 索引的时候，show processlist 中还会偶有慢查询，
但是现在已经没有了。大部分的 SQL 语句都是正常的查询语句，没有出现任何慢查询。

那为何从 top 命令看 MySQL 占用这么高的 CPU 呢？注意面试的时候，一定要说用了 top，否则一些脑残面试官，以为你啥也不知道。。。

查看 MySQL 每秒的 SQL 执行量

于是我怀疑是 MySQL 每秒的 SQL 执行量过高，导致 CPU 占用过高。

]> SHOW GLOBAL STATUS LIKE 'Questions';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Questions     | 705205 |
+---------------+--------+
1 row in set (0.00 sec)

MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'Questions';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Questions     | 715310 |
+---------------+--------+
1 row in set (0.00 sec)

大概间隔了 5 秒钟左右，发现 Questions 的值增加了 10000 左右，说明 MySQL 每秒的 SQL 执行量大约是 2000 左右。
但是，这种纯手工计时的方式太不精确，于是改用 watch 命令来查看 MySQL 每秒的 SQL 执行量。

watch -n 10 'mysqladmin -uroot -ppassword status | grep Questions'

watch 命令每隔 10 秒钟执行一次 mysqladmin 命令，查看 MySQL 的状态信息，并过滤出 Questions 这一行。
同时计算出每秒的 SQL 执行量，发现 MySQL 每秒的 SQL 执行量大约是 1500 左右。🥲

这也太离谱了，MySQL 每秒的 SQL 执行量居然这么高，难怪 CPU 占用会这么高。

Nginx 日志定位到问题

因为之前看过 cloudflare 及 Nginx 的访问日志，从日志看，请求量也就在每秒 20 次左右。
这个访问量很小，我一直没有当回事。确实是我大意了。

再次仔细看了一下 Nginx 日志，发现这每秒 20 次的请求，实际应该是同一来源。因为 User Agent 类似。

而且都是访问的 Magento 的产品列表页的属性过滤功能，但是属性参数每次都不一样。

这个网站 170 万多个产品，100 多属性，属性组合非常多，导致恶意爬虫在抓取产品列表页的时候，访问了大量的不同属性组合的 URL。
我怀疑是这个属性列表页的属性组合导致了 MySQL 每秒的 SQL 执行量过高，最终导致 MySQL CPU 占用过高。

而这个我又没法做本地缓存，因为磁盘空间不太够。

通过 cloudflare 屏蔽恶意爬虫

所以，先临时启用了 cloudflare 的访问规则，除了认证的正规爬虫（如 Googlebot、Bingbot 等）之外，其他的所有请求都开启人类验证（Challenge）模式。

但是，需要排除首页，毕竟需要防止宕机监控请求也被挑战。

尝试了一下只部分属性列表路径才开启挑战，但是发现相关的页面规则太多，最终还是选择了对所有路径开启挑战模式。

暂时看是正常了。系统负载也恢复正常了。

待续

• 为何属性组合会导致 MySQL 每秒的 SQL 执行量过高？
  
• 为何屏蔽了恶意爬虫之后，MySQL 的 CPU 占用没有立即下降？而且在重启 MySQL 之后，CPU 占用才正常？是因为数据库的 SQL 队列机制，还是 PHP 的请求队列机制？还是其他原因？

关于作者 🌱

我是来自山东烟台的一名开发者，有感兴趣的话题，或者软件开发需求，欢迎加微信 zhongwei 聊聊，或者关注我的个人公众号“大象工具”， 查看更多联系方式