每天中午 12 点左右交通拥堵导致车流量减少

每天中午 12 点左右交通拥堵导致车流量减少

我检查了慢速日志,在 2 小时内只收到了 4 个查询,所有查询都与此类似:

"SELECT HEX(uhash) AS uhash, vehid, IF(deleted = 0 AND follow_price_drop = 1, 1, 0) AS follow_price_drop, email, deleted 
       FROM wp_ product_favorite_count AS cfc 
       INNER JOIN wp_ product_favorite_user AS cfu ON cfc. product_favorite_user_uhash = cfu.uhash
       WHERE cfc.updated > '2021-09-23 12:49:02' OR cfu.updated > '2021-09-23 12:49:02'"

我检查了 top 和 htop,发现 6 个 CPU 核心的 CPU 使用率通常都达到 100。

大部分 CPU 使用率来自 mysqld,因此我记录了数据库:

https://pastebin.com/BBv7ngW5

iostat -xm 5 3 给了我:

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          11.34    0.01    1.80    1.13    0.08   85.65

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
xvda             39.75   720.61   79.81  192.29     0.99     3.57    34.30     0.02    0.09    0.19    0.04   0.09   2.53

^[[A^[[A^[[Aavg-cpu:  %user   %nice %system %iowait  %steal   %idle
          84.15    0.00    6.16    0.05    0.03    9.61

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
xvda              0.80    31.00   14.40   19.80     0.65     0.20    50.95     0.02    0.73    0.93    0.58   0.43   1.48

^[[A^[[Bavg-cpu:  %user   %nice %system %iowait  %steal   %idle
          84.54    0.00    4.95    0.10    0.05   10.36

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
xvda              0.00     2.40   22.60    1.60     1.77     0.02   151.40     0.02    1.02    1.04    0.75   0.64   1.56

ulimit -a

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 128341
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 128341
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

在检查了慢查询日志后,我检查了常规查询日志,并惊讶地发现我收到了如此多的查询。当流量正常时,我收到了:136235 个查询,其中大多数是 10 分钟后的 SELECT 查询。而当流量很高时,我收到了:10 分钟内 195650 个查询。我怀疑是不是有 195650 名访问者,但出于某种原因,这些调用都在 general_log 中。slow_query_log 只有 4 个查询,它们看起来不像未优化的查询。我还应该看些什么吗,或者这足以推测它来自流量,我们应该升级服务器吗?

顶部大致如下所示,我无法及时捕捉到它,但是当它达到 95%+ cpu 时,屏幕如下所示:

top - 13:04:51 up 1140 days, 19:59,  2 users,  load average: 26.57, 16.21, 8.92
Tasks: 429 total,  12 running, 421 sleeping,   0 stopped,   0 zombie
Cpu(s): 91.3%us,  1.6%sy,  0.0%ni, 65.7%id,  3.1%wa,  0.0%hi,  0.2%si,  0.1%st
Mem:  32877280k total, 31367584k used,  1509696k free,  3960824k buffers
Swap:        0k total,        0k used,        0k free,  3980580k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                 
14576 mysql     20   0 12.9g 8.5g 8424 S 951.6 27.2  18841:47 mysqld                                                  
 6032  martind     20   0  510m  65m 9160 S 61.4  0.2   2:49.40 php-fpm                                                  
 7329  martind     20   0  498m  63m 5556 R 57.6  0.2   0:47.15 php-fpm                                                  
 7321  martind     20   0  487m  52m 5532 R 46.1  0.2   0:45.18 php-fpm                                                  
 7160  martind     20   0  488m  52m 5540 R 44.1  0.2   1:02.67 php-fpm                                                  
 6031  martind     20   0  511m  67m 8076 S 42.2  0.2   2:50.87 php-fpm                                                  
 6696  martind     20   0  498m  63m 5700 S 38.4  0.2   1:36.38 php-fpm                                                  
 7283  martind     20   0  494m  59m 5268 S 34.5  0.2   0:46.19 php-fpm                                                  
 7314  martind     20   0  490m  55m 5536 R 33.0  0.2   0:44.22 php-fpm                                                  
 7330  martind     20   0  496m  60m 5436 R 26.4  0.2   0:46.82 php-fpm                                                  
 7305  martind     20   0  494m  58m 5572 R 25.4  0.2   0:48.85 php-fpm                                                  
 6706  martind     20   0  507m  62m 8060 S 13.7  0.2   1:40.55 php-fpm                                                  
 7276  martind     20   0  498m  63m 5264 S  7.7  0.2   0:49.89 php-fpm                                                  
17464 redis     20   0 4328m 2.3g  888 R  7.7  7.3   7827:30 redis-server                                             
 6402  martind     20   0  511m  67m 8056 S  5.8  0.2   2:15.21 php-fpm                                                  
 6405  martind     20   0  512m  69m 9204 S  5.8  0.2   2:14.32 php-fpm                                                  
 6703  martind     20   0  513m  67m 8056 S  5.8  0.2   1:39.40 php-fpm                                                  
 6705  martind     20   0  513m  68m 9040 S  5.8  0.2   1:36.18 php-fpm                                                  
 7303  martind     20   0  493m  57m 6556 S  5.8  0.2   0:47.04 php-fpm                                                  
 7304  martind     20   0  494m  59m 5264 S  5.8  0.2   0:48.70 php-fpm                                                  
 7323  martind     20   0  511m  67m 7772 S  5.8  0.2   0:45.53 php-fpm                                                  
24515 nginx     20   0  123m  66m 2452 S  5.8  0.2   7231:17 nginx                                                    
 6039  martind     20   0  507m  63m 8200 S  3.8  0.2   2:48.39 php-fpm                                                  
 6400  martind     20   0  511m  68m 8204 S  3.8  0.2   2:13.54 php-fpm                                                  
 6401  martind     20   0  510m  66m 9052 S  3.8  0.2   2:13.36 php-fpm                                                  
 6404  martind     20   0  512m  68m 9048 S  3.8  0.2   2:12.75 php-fpm 

因此,由于 SQL 查询太多,导致速度变慢,因此我认为这是由高流量引起的。我检查了 cronjobs(wordpress cronjobs 和 php cronjobs),当速度变慢时似乎没有运行任何程序,可能同时有一个 rsync 进程在运行,但 rsync 进程一直在运行,所以我怀疑这不是由这个引起的。有什么我可以检查的吗?

答案1

全球状况和变量的分析:

观察结果:

  • 版本:10.4.12-MariaDB
  • 32 GB 内存
  • 正常运行时间 = 19 天 23:11:43
  • 看来您正在运行 MyISAM 和 InnoDB。
  • 每秒 240 次

更重要的问题:

更改long_query_time1,以便您可以在慢速日志中捕获更多查询。(您现在有 10 秒的时间;这可能解释了为什么您只找到了 4 个查询。)有几个线索表明某些查询运行效率低下。以下是查找此类查询的方法: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

为什么要使用 MyISAM?这些值令人困惑——就好像你为一个大型 MyISAM 表[重新]建立了一个索引,但没有做太多其他事情。在大多数情况下,最好使用 InnoDB。

innodb_buffer_pool_size可能可以增加以提高 InnoDB 查询速度。

要小心general_log——它会很快填满磁盘。

“查询缓存”运行效率低下。我建议将其完全关闭:query_cache_type=offquery_cache_size=0

Max_used_connectionshit 152,表示很多用户同时连接。(这并不是说有 152 个查询同时运行。)

详细信息和其他观察结果:

从 MyISAM 到 InnoDB 的转换 ( Key_blocks_used * 1024 / key_buffer_size ) = 460 * 1024 / 128M = 0.35%-- key_buffer 的使用百分比。高水位线。-- 降低 key_buffer_size(现在为 134217728)以避免不必要的内存使用。

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((128M / 0.20 + 8192M / 0.70)) / 32768M = 37.7%-- 大多数可用 RAM 应用于缓存。--http://mysql.rjweb.org/doc.php/memory

( general_log ) = general_log = ON-- 运行的所有查询的日志(文件或表)。-- 不使用时关闭 general_log(现在打开)。该日志可以非常迅速地填满磁盘。

( innodb_buffer_pool_size ) = 8,192 / 32768M = 25.0%-- 用于 InnoDB 缓冲池的 RAM 百分比 -- 设置为可用 RAM 的 70% 左右。(太低效率较低;太高有交换风险。)

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((128M / 0.20 + 8192M / 0.70)) / 32768M = 37.7%--(判断 RAM 使用情况的指标)

( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096-- 页面清理器每秒的工作量。-- “InnoDB:page_cleaner:预期循环花费 1000 毫秒...”可通过降低 lru_scan_depth 来解决:考虑 1000/innodb_page_cleaners(现在为 4)。还请检查交换。

( innodb_lru_scan_depth ) = 1,024 -- “InnoDB:page_cleaner:预期循环花费 1000ms...” 可以通过降低 lru_scan_depth 来修复

( innodb_io_capacity ) = 200-- 刷新时,使用这么多的 IOP。-- 读取可能会很缓慢或不稳定。

( Innodb_log_writes ) = 43,856,157 / 1725103 = 25 /sec

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 137,804,939,264 / (1725103 / 3600) / 2 / 48M = 2.86-- 比率 -- (见会议纪要)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 1,725,103 / 60 * 48M / 137804939264 = 10.5-- InnoDB 日志轮换之间的分钟数从 5.6.8 开始,可以动态更改;请确保也更改 my.cnf。--(轮换之间建议 60 分钟有点武断。)调整 innodb_log_file_size(现在为 50331648)。(无法在 AWS 中更改。)

( innodb_flush_method ) = innodb_flush_method = fsync-- InnoDB 应如何要求操作系统写入块。建议使用 O_DIRECT 或 O_ALL_DIRECT (Percona) 来避免双重缓冲。(至少对于 Unix 而言。)有关 O_ALL_DIRECT 的注意事项,请参阅 chrischandler

( default_tmp_storage_engine ) = default_tmp_storage_engine =

( innodb_flush_neighbors ) = 1-- 将块写入磁盘时进行小幅优化。-- 对于 SSD 驱动器使用 0;对于 HDD 使用 1。

( innodb_io_capacity ) = 200-- 磁盘每秒的 I/O 操作数。慢速驱动器为 100;旋转驱动器为 200;SSD 为 1000-2000;乘以 RAID 因子。

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON-- 通常应为 ON。-- 在某些情况下,OFF 更好。另请参阅 innodb_adaptive_hash_index_parts(现在是 8)(5.7.9 之后)和 innodb_adaptive_hash_index_partitions(MariaDB 和 Percona)。ON 已与罕见的崩溃有关(错误 73890)。10.5.0 决定默认为 OFF。

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF-- 是否记录所有死锁。-- 如果您受到死锁的困扰,请启用此功能。注意:如果您有大量死锁,这可能会将大量数据写入磁盘。

( character_set_server ) = character_set_server = latin1 -- 通过将 character_set_server(现在是 latin1)设置为 utf8mb4 可能会有助于解决字符集问题。这是未来的默认设置。

( local_infile ) = local_infile = ON -- local_infile(现在为 ON)= ON 是一个潜在的安全问题

( Key_blocks_used * 1024 / key_buffer_size ) = 460 * 1024 / 128M = 0.35%-- 使用的 key_buffer 的百分比。高水位线。-- 降低 key_buffer_size(现在为 134217728)以避免不必要的内存使用。

( Key_writes / Key_write_requests ) = 19,978,377 / 40284646 = 49.6%-- key_buffer 写入有效性 -- 如果您有足够的 RAM,则值得增加 key_buffer_size(现在为 134217728)。

( query_cache_size ) = 524,288 = 0.5MB-- QC 的大小 -- 太小 = 用处不大。太大 = 开销太大。建议为 0 或不超过 50M。

( Qcache_lowmem_prunes ) = 125,234,412 / 1725103 = 73 /sec-- QC 空间不足 -- 增加 query_cache_size (现在为 524288)

( Qcache_lowmem_prunes/Qcache_inserts ) = 125,234,412/146211296 = 85.7%-- 删除率(由于内存不足而需要修剪的频率)

( Qcache_not_cached ) = 78,413,835 / 1725103 = 45 /sec-- SQL_CACHE 尝试,但被忽略 -- 重新考虑缓存;调整 qcache

( Qcache_hits / Qcache_inserts ) = 37,201,050 / 146211296 = 0.254-- 命中插入率 -- 越高越好 -- 考虑关闭查询缓存。

( Qcache_hits / (Qcache_hits + Com_select) ) = 37,201,050 / (37201050 + 282029692) = 11.7%-- 命中率 -- 使用 QC 的 SELECT -- 考虑关闭查询缓存。

( Qcache_hits / (Qcache_hits + Qcache_inserts + Qcache_not_cached) ) = 37,201,050 / (37201050 + 146211296 + 78413835) = 14.2%-- 查询缓存命中率 -- 最好关闭 QC。

( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (524288 - 78344) / 82 / 16384 = 0.332-- query_alloc_block_size 与公式 -- 调整 query_alloc_block_size(现在为 16384)

( Created_tmp_tables ) = 96,501,765 / 1725103 = 56 /sec——作为复杂 SELECT 的一部分创建“临时”表的频率。

( Created_tmp_disk_tables ) = 23,539,653 / 1725103 = 14 /sec-- 创建频率磁盘“临时”表作为复杂 SELECT 的一部分 - 增加 tmp_table_size(现在为 16777216)和 max_heap_table_size(现在为 16777216)。检查使用 MEMORY 而不是 MyISAM 时的临时表规则。也许较小的架构或查询更改可以避免使用 MyISAM。更好的索引和查询的重新表述更有可能有所帮助。

( Created_tmp_disk_tables / Questions ) = 23,539,653 / 414140316 = 5.7%-- 需要磁盘临时表的查询的百分比。-- 更好的索引/没有 blob/等等。

( Select_full_join / Com_select ) = 30,333,225 / 282029692 = 10.8%-- 无索引连接的选择百分比 -- 为 JOIN 中使用的表添加合适的索引。

( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (87669877 + 27242 + 0 + 0 + 1452911 + 0) / 1725103 = 52 /sec-- 写入次数/秒 -- 50 次写入次数/秒 + 日志刷新可能会使 HDD 驱动器的 I/O 写入容量达到最大值。如果您有 SSD,那么这个指标可能没问题。

( binlog_format ) = binlog_format = MIXED-- 语句/行/混合。-- 5.7 (10.3) 首选行

( long_query_time ) = 10-- 定义“慢速”查询的截止时间(秒)。-- 建议 2

( Max_used_connections / max_connections ) = 152 / 151 = 100.7%-- 连接峰值百分比 -- 增加 max_connections(现在为 151)和/或减少 wait_timeout(现在为 28800)。或者加快查询速度。

( Connections ) = 11,987,448 / 1725103 = 6.9 /sec-- 连接 -- 增加 wait_timeout(现在为 28800);使用池?

( Connection_errors_accept + Connection_errors_internal + Connection_errors_peer_address + Connection_errors_select + Connection_errors_tcpwrap ) = 0 + 26 + 0 + 0 + 0 = 26-- 除了 max_connections 之外的连接错误。-- 有关更多信息,请参阅 SHOW GLOBAL STATUS LIKE 'Connection_errors%'

异常小:

Created_tmp_files = 0.094 /HR
innodb_spin_wait_delay = 4

异常大:

Aria_pagecache_writes = 34 /sec
Aria_transaction_log_syncs = 25,641
Com_show_warnings = 40 /HR
Connection_errors_internal = 0.054 /HR
Handler_read_key = 85109 /sec
Handler_tmp_update = 839 /sec
Innodb_buffer_pool_read_requests = 675158 /sec
Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads ) = 100.0%
Innodb_rows_updated = 356 /sec
performance_schema_max_cond_classes = 90

异常字符串:

Innodb_have_punch_hole = OFF
aria_recover_options = BACKUP,QUICK
disconnect_on_expired_password = OFF
ft_boolean_syntax = + -><()~*:
innodb_fast_shutdown = 1
log_output = TABLE
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT
optimizer_trace = enabled=off

相关内容