Toolsdb sees a big spike in the number of queries between midnight and 01:00 UTC. What's happening?
Description
Description
Related Objects
Related Objects
- Mentioned In
- T301949: ToolsDB upgrade => Bullseye, MariaDB 10.4
Event Timeline
Comment Actions
Top at 00:07:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 1932 mysql 20 0 46.753g 0.034t 11060 S 1091 55.4 29639,55 mysqld 7398 root 20 0 44484 15364 6360 S 3.6 0.0 0:37.20 iotop 62 root 20 0 0 0 0 S 1.3 0.0 786:37.45 ksoftirqd/10 10 root 20 0 0 0 0 I 0.3 0.0 416:56.33 rcu_sched 321 root 0 -20 0 0 0 I 0.3 0.0 189:56.86 kworker/4:1H-xf 496 root 20 0 12020 4548 1476 S 0.3 0.0 544:47.66 haveg
iotop at 00:08:
TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND 26907 be/4 mysql 13.48 M/s 0.00 B/s 0.00 % 50.59 % mysqld 8267 be/4 mysql 15.79 M/s 0.00 B/s 0.00 % 41.70 % mysqld 15423 be/4 mysql 32.09 M/s 0.00 B/s 0.00 % 39.61 % mysqld 1714 be/4 mysql 8.25 M/s 0.00 B/s 0.00 % 26.25 % mysqld 2338 be/4 mysql 2.37 M/s 1385.92 K/s 0.00 % 10.73 % mysqld 8271 be/4 mysql 46.07 K/s 556.67 K/s 0.00 % 6.17 % mysqld 1992 be/4 mysql 395.43 K/s 49.91 K/s 0.00 % 3.23 % mysqld 1718 be/4 mysql 11.52 K/s 107.49 K/s 0.00 % 1.18 % mysqld 2337 be/4 mysql 199.63 K/s 0.00 B/s 0.00 % 1.17 % mysqld
Comment Actions
MariaDB [(none)]> SHOW PROCESSLIST;
+-----------+-----------------+--------------------+---------------------------------+-------------+----------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+-----------+-----------------+--------------------+---------------------------------+-------------+----------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+
| 2 | event_scheduler | localhost | NULL | Daemon | 19987108 | Waiting on empty queue | NULL | 0.000 |
| 272348354 | root | localhost | heartbeat | Sleep | 0 | | NULL | 0.000 |
| 678901120 | s54918 | 172.16.1.58:51293 | s54918__mismatch_finder_staging | Sleep | 1 | | NULL | 0.000 |
| 678901566 | s54438 | 172.16.1.81:33797 | s54438__rwwiki | Sleep | 28 | | NULL | 0.000 |
| 678901567 | s54438 | 172.16.1.81:55448 | s54438__rwwiki | Sleep | 13 | | NULL | 0.000 |
| 685565329 | s55096 | 172.16.0.98:56651 | s55096__quest_p | Sleep | 2 | | NULL | 0.000 |
| 743599932 | s54796 | 172.16.1.87:34406 | s54796__mismatch_finder | Sleep | 0 | | NULL | 0.000 |
| 827019329 | repl | 172.16.5.119:48946 | NULL | Binlog Dump | 2188349 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | 0.000 |
| 869135022 | s51055 | 172.16.0.34:21125 | s51055__anomiebot | Sleep | 246 | | NULL | 0.000 |
| 878551828 | s53213 | 172.16.0.98:54586 | s53213__wmopbot | Sleep | 71 | | NULL | 0.000 |
| 916180995 | s55078 | 172.16.0.98:20566 | s55078__wdrc_p | Sleep | 2 | | NULL | 0.000 |
| 926500823 | s51055 | 172.16.0.98:9838 | s51055__anomiebot | Sleep | 49 | | NULL | 0.000 |
| 934357168 | s54874 | 172.16.0.134:33762 | s54874__editgroups_commons | Sleep | 93 | | NULL | 0.000 |
| 935040056 | s51138 | 172.16.2.108:43616 | s51138__heritage_p | Sleep | 0 | | NULL | 0.000 |
| 935135764 | s51412 | 172.16.5.42:46404 | s51412__data | Sleep | 0 | | NULL | 0.000 |
| 941199037 | s51537 | 172.16.2.241:51546 | s51537__checkpersondata | Sleep | 0 | | NULL | 0.000 |
| 941747759 | s51203 | 172.16.0.34:1128 | s51203__inventory_p | Sleep | 0 | | NULL | 0.000 |
| 942237316 | s51080 | 172.16.2.47:16856 | s51080__checkwiki_p | Sleep | 64 | | NULL | 0.000 |
| 942338193 | s51230 | 172.16.5.48:36542 | s51230__linkwatcher | Sleep | 48 | | NULL | 0.000 |
| 942338209 | s51230 | 172.16.5.48:36582 | s51230__linkwatcher | Sleep | 34 | | NULL | 0.000 |
| 942338213 | s51230 | 172.16.5.48:36596 | s51230__linkwatcher | Sleep | 153 | | NULL | 0.000 |
| 942338217 | s51230 | 172.16.5.48:36608 | s51230__linkwatcher | Sleep | 8 | | NULL | 0.000 |
| 942338225 | s51230 | 172.16.5.48:36636 | s51230__linkwatcher | Sleep | 30 | | NULL | 0.000 |
| 942338243 | s51230 | 172.16.5.48:36682 | s51230__linkwatcher | Sleep | 31 | | NULL | 0.000 |
| 943019927 | s51203 | 172.16.6.95:46000 | s51203__baglama2_p | Sleep | 13 | | NULL | 0.000 |
| 943019943 | s51203 | 172.16.6.95:46004 | NULL | Sleep | 13 | | NULL | 0.000 |
| 943053326 | s51434 | 172.16.6.95:45062 | s51434__mixnmatch_p | Sleep | 13 | | NULL | 0.000 |
| 943053341 | s51434 | 172.16.6.95:45078 | NULL | Sleep | 13 | | NULL | 0.000 |
| 943075820 | s53220 | 172.16.3.214:34616 | s53220__quickstatements_p | Sleep | 0 | | NULL | 0.000 |
| 943075953 | s53220 | 172.16.3.214:34760 | s53220__quickstatements_p | Sleep | 41 | | NULL | 0.000 |
| 943075996 | s53220 | 172.16.3.214:34798 | s53220__quickstatements_p | Sleep | 37 | | NULL | 0.000 |
| 943131393 | s53220 | 172.16.3.214:52914 | s53220__quickstatements_p | Sleep | 1 | | NULL | 0.000 |
| 943257972 | s51229 | 172.16.5.40:36086 | s51230__linkwatcher | Sleep | 12 | | NULL | 0.000 |
| 943368744 | s53220 | 172.16.3.214:33732 | s53220__quickstatements_p | Sleep | 8 | | NULL | 0.000 |
| 943393267 | s53685 | 172.16.2.67:45592 | s53685__editgroups | Sleep | 9 | | NULL | 0.000 |
| 943394488 | s53220 | 172.16.3.214:47298 | s53220__quickstatements_p | Sleep | 100 | | NULL | 0.000 |
| 943394598 | s53220 | 172.16.3.214:47490 | s53220__quickstatements_p | Sleep | 2 | | NULL | 0.000 |
| 943420975 | s51055 | 172.16.0.134:35006 | s51055__anomiebot | Sleep | 70 | | NULL | 0.000 |
| 943495824 | s53842 | 172.16.5.188:46320 | s53842__ipcheck | Query | 0 | Sending data | SELECT * FROM tempisps WHERE name = 'EWE-Tel GmbH' | 0.000 |
| 943495825 | s54406 | 172.16.0.243:58156 | s54406__ipcheck | Query | 0 | Sending data | SELECT * FROM tempisps WHERE name = 'CHINA UNICOM China169 Backbone' | 0.000 |
| 943542531 | s54857 | 172.16.1.189:35947 | s54857__fixsuggesterbot | Sleep | 57 | | NULL | 0.000 |
| 943585407 | s55175 | 172.16.6.95:59228 | s55175__ganfilter | Sleep | 462 | | NULL | 0.000 |
| 943637743 | s51138 | 172.16.4.170:42650 | s51138__heritage_p | Sleep | 12 | | NULL | 0.000 |
| 943652675 | s51330 | 172.16.0.222:1771 | s51330__copyvios_p | Sleep | 311 | | NULL | 0.000 |
| 943655373 | s51434 | 172.16.2.67:8724 | s51434__mixnmatch_p | Execute | 2478 | Sending data | SELECT e1.id,e2.q FROM entry e1,entry e2
WHERE e1.ext_name=e2.ext_name AND e1.id!=e2.id | 0.000 |
| 943661680 | s51229 | 172.16.5.40:59764 | s51230__linkwatcher | Sleep | 18 | | NULL | 0.000 |
| 943662042 | s51330 | 172.16.0.222:19314 | s51330__copyvios_p | Sleep | 4 | | NULL | 0.000 |
| 943668015 | s51330 | 172.16.0.222:35731 | s51330__copyvios_p | Sleep | 156 | | NULL | 0.000 |
| 943673395 | s53098 | 172.16.5.62:60254 | s53098__fountain | Sleep | 221 | | NULL | 0.000 |
| 943673396 | s53098 | 172.16.5.62:60270 | s53098__fountain | Sleep | 221 | | NULL | 0.000 |
| 943673437 | s51154 | 172.16.7.6:54774 | s51154__words | Sleep | 1 | | NULL | 0.000 |
| 943673810 | s54847 | 172.16.2.47:5913 | s54847__airflow | Sleep | 7 | | NULL | 0.000 |
| 943673925 | s51229 | 172.16.5.40:48028 | s51230__linkwatcher | Sleep | 328 | | NULL | 0.000 |
| 943676179 | s54847 | 172.16.2.47:50229 | s54847__airflow | Sleep | 0 | | NULL | 0.000 |
| 943677909 | s54857 | 172.16.1.189:44589 | s54857__fixsuggesterbot | Sleep | 0 | | NULL | 0.000 |
| 943678724 | s52978 | 172.16.0.61:33852 | s52978__replacer | Sleep | 39 | | NULL | 0.000 |
| 943678904 | s51541 | 172.16.2.67:20641 | s51541__sulwatcher | Sleep | 179 | | NULL | 0.000 |
| 943679048 | s51537 | 172.16.2.247:44220 | s51537__checkpersondata | Sleep | 0 | | NULL | 0.000 |
| 943679058 | s51229 | 172.16.5.40:53632 | s51230__linkwatcher | Sleep | 345 | | NULL | 0.000 |
| 943679430 | s51055 | 172.16.0.98:23339 | s51055__anomiebot | Sleep | 123 | | NULL | 0.000 |
| 943679652 | s51330 | 172.16.0.222:53758 | s51330__copyvios_p | Sleep | 279 | | NULL | 0.000 |
| 943679830 | s52978 | 172.16.0.61:53932 | s52978__replacer | Sleep | 505 | | NULL | 0.000 |
| 943679980 | s51114 | 172.16.5.1:54082 | s51114__enwp10 | Ping | 0 | updating status | NULL | 0.000 |
| 943680177 | s51412 | 172.16.7.6:53314 | s51412__data | Query | 156 | Copying to tmp table on disk | INSERT INTO de_td_statistics (st_tmpl, st_cnt_all, st_cnt_pages, st_max_page_count) SELECT use_tmpl, | 0.000 |
| 943680194 | s51698 | 172.16.4.165:39646 | s51698__yetkin | Query | 470 | executing | SELECT
B.title,
SUM(vpa.vpa_count) AS count,
SUM(vpa.vpa_mobile_count) AS mobile_count,
| 0.000 |
| 943680402 | s52978 | 172.16.0.61:21741 | s52978__replacer | Sleep | 453 | | NULL | 0.000 |
| 943680616 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST | 0.000 |
| 943680642 | s52978 | 172.16.0.61:65298 | s52978__replacer | Sleep | 431 | | NULL | 0.000 |
| 943680731 | s54044 | 172.16.1.189:26184 | s54044__wdumper | Sleep | 45 | | NULL | 0.000 |
| 943680786 | s54044 | 172.16.1.189:10373 | s54044__wdumper | Sleep | 422 | | NULL | 0.000 |
| 943680804 | s52978 | 172.16.0.61:58149 | s52978__replacer | Sleep | 420 | | NULL | 0.000 |
| 943680999 | s54044 | 172.16.1.189:24532 | s54044__wdumper | Sleep | 402 | | NULL | 0.000 |
| 943681005 | s51080 | 172.16.0.98:49826 | s51080__checkwiki_p | Sleep | 0 | | NULL | 0.000 |
| 943681031 | s52978 | 172.16.0.61:3906 | s52978__replacer | Sleep | 398 | | NULL | 0.000 |
| 943681044 | s54044 | 172.16.1.189:28428 | s54044__wdumper | Sleep | 396 | | NULL | 0.000 |
| 943681319 | s52978 | 172.16.0.61:41343 | s52978__replacer | Sleep | 367 | | NULL | 0.000 |
| 943681441 | s51055 | 172.16.1.117:23429 | s51055__anomiebot | Sleep | 357 | | NULL | 0.000 |
| 943681572 | s54044 | 172.16.1.189:26723 | s54044__wdumper | Sleep | 348 | | NULL | 0.000 |
| 943681581 | s54044 | 172.16.1.189:44555 | s54044__wdumper | Sleep | 347 | | NULL | 0.000 |
| 943681893 | s54044 | 172.16.1.117:60246 | s54044__wdumper | Sleep | 327 | | NULL | 0.000 |
| 943681943 | s54044 | 172.16.1.189:22627 | s54044__wdumper | Sleep | 324 | | NULL | 0.000 |
| 943682580 | s51205 | 172.16.2.47:58353 | s51205__terminator_p | Query | 279 | updating | DELETE FROM items WHERE score<15 | 0.000 |
| 943682832 | s51114 | 172.16.5.1:37602 | s51114__enwp10 | Sleep | 0 | | NULL | 0.000 |
| 943682854 | s54044 | 172.16.1.189:2409 | s54044__wdumper | Sleep | 265 | | NULL | 0.000 |
| 943682872 | s53952 | 172.16.1.74:6510 | s53952__freebase_p | Query | 263 | Sending data | SELECT `keys`.topic_id AS keys_topic_id, `keys`.`key` AS keys_key
FROM `keys`
WHERE `keys`.`key` = | 0.000 |
| 943683051 | s53952 | 172.16.1.74:1456 | s53952__freebase_p | Query | 248 | Sending data | SELECT `keys`.topic_id AS keys_topic_id, `keys`.`key` AS keys_key
FROM `keys`
WHERE `keys`.`key` = | 0.000 |
| 943683092 | s51229 | 172.16.5.40:59080 | s51230__linkwatcher | Sleep | 233 | | NULL | 0.000 |
| 943683386 | s51083 | 172.16.1.12:54014 | s51083__ukbot | Sleep | 11 | | NULL | 0.000 |
| 943683401 | s54207 | 172.16.1.170:31633 | s54207__wikishield_p | Sleep | 217 | | NULL | 0.000 |
| 943683579 | s51080 | 172.16.1.142:20980 | s51080__checkwiki_p | Sleep | 8 | | NULL | 0.000 |
| 943683682 | s55084 | 172.16.1.188:57800 | s51306__copyright_p | Sleep | 201 | | NULL | 0.000 |
| 943683719 | s54943 | 172.16.1.189:13873 | s54943__laravel | Sleep | 0 | | NULL | 0.000 |
| 943684127 | s51055 | 172.16.0.98:19449 | s51055__anomiebot | Sleep | 168 | | NULL | 0.000 |
| 943684247 | s54044 | 172.16.1.189:16865 | s54044__wdumper | Sleep | 160 | | NULL | 0.000 |
| 943684343 | s54113 | 172.16.1.185:9928 | s54113__spacemedia | Sleep | 152 | | NULL | 0.000 |
| 943684493 | s52978 | 172.16.0.61:48508 | s52978__replacer | Sleep | 142 | | NULL | 0.000 |
| 943684594 | s51434 | 172.16.2.67:24287 | s51434__mixnmatch_p | Execute | 19 | Sorting result | SELECT auxiliary.id,entry_id,0,aux_p,aux_name FROM entry,auxiliary
WHERE entry_id=entry | 0.000 |
| 943685139 | s51080 | 172.16.2.67:33929 | s51080__checkwiki_p | Sleep | 0 | | NULL | 0.000 |
| 943685146 | s51080 | 172.16.2.67:13742 | s51080__checkwiki_p | Sleep | 16 | | NULL | 0.000 |
| 943685147 | s51080 | 172.16.2.67:10741 | s51080__checkwiki_p | Sleep | 0 | | NULL | 0.000 |
| 943685174 | s54113 | 172.16.2.67:14728 | s54113__spacemedia | Sleep | 1 | | NULL | 0.000 |
| 943685263 | s51114 | 172.16.5.1:56918 | s51114__enwp10 | Sleep | 0 | | NULL | 0.000 |
| 943685300 | s54113 | 172.16.2.67:6471 | s54113__spacemedia | Sleep | 0 | | NULL | 0.000 |
| 943685319 | s51114 | 172.16.5.1:56938 | s51114__enwp10 | Query | 0 | Writing to net | COMMIT | 0.000 |
| 943685348 | s54113 | 172.16.2.67:41547 | s54113__spacemedia | Sleep | 0 | | NULL | 0.000 |
| 943685356 | s54044 | 172.16.1.189:53796 | s54044__wdumper | Sleep | 72 | | NULL | 0.000 |
| 943685370 | s51114 | 172.16.5.1:45150 | s51114__enwp10 | Sleep | 0 | | NULL | 0.000 |
| 943685428 | s51229 | 172.16.5.40:34220 | s51230__linkwatcher | Sleep | 19 | | NULL | 0.000 |
| 943685436 | s51114 | 172.16.5.1:43608 | s51114__enwp10 | Sleep | 0 | | NULL | 0.000 |
| 943685443 | s54044 | 172.16.1.117:63809 | s54044__wdumper | Sleep | 61 | | NULL | 0.000 |
| 943685455 | s54113 | 172.16.2.67:20337 | s54113__spacemedia | Sleep | 58 | | NULL | 0.000 |
| 943685738 | s51187 | 172.16.5.124:52220 | s51187__xtools_prod | Sleep | 34 | | NULL | 0.000 |
| 943685823 | s52978 | 172.16.0.61:3745 | s52978__replacer | Sleep | 24 | | NULL | 0.000 |
| 943685865 | s52481 | 172.16.1.117:39227 | s52481__stats_global | Sleep | 22 | | NULL | 0.000 |
| 943685906 | s51434 | 172.16.2.67:50337 | s51434__mixnmatch_p | Sleep | 0 | | NULL | 0.000 |
| 943685921 | s51114 | 172.16.5.1:52584 | s51114__enwp10 | Sleep | 0 | | NULL | 0.000 |
| 943686004 | s51114 | 172.16.5.1:38882 | s51114__enwp10 | Query | 0 | Writing to net | COMMIT | 0.000 |
| 943686015 | s52978 | 172.16.0.61:6254 | s52978__replacer | Sleep | 5 | | NULL | 0.000 |
| 943686025 | u4133 | 172.16.0.134:42236 | u4133__idx_hist | Query | 0 | Sending data | SELECT prh_status, COUNT(*) cnt
FROM pages
JOIN pr_history ON pages.page_id = pr_history.prh_page_id | 0.000 |
| 943686030 | s52481 | 172.16.0.98:5263 | s52481__citationhunt_lv | Sleep | 3 | | NULL | 0.000 |
| 943686031 | s52481 | 172.16.0.98:44447 | s52481__stats_global | Query | 3 | Sorting result | SELECT ts, snippet_id, url, inter_id FROM requests
WHERE url LIKE "%redirect%" AND
t | 0.000 |
| 943686045 | s52481 | 172.16.1.117:15666 | s52481__citationhunt_en | Query | 2 | Sending data | SELECT category_id, title, article_count
FROM categories, category_article_count
| 0.000 |
| 943686055 | s52481 | 172.16.1.117:48212 | s52481__citationhunt_en | Query | 1 | Sending data | SELECT category_id, title, article_count
FROM categories, category_article_count
| 0.000 |
| 943686066 | s51114 | 172.16.5.1:41542 | s51114__enwp10 | Sleep | 0 | | NULL | 0.000 |
| 943686067 | s51187 | 172.16.5.124:52596 | s51187__xtools_prod | Sleep | 0 | | NULL | 0.000 |
| 943686070 | s51211 | 172.16.1.142:38192 | s51211__duplicity_p | Query | 0 | Sorting result | SELECT * FROM no_wd WHERE random>0.37719600665252 order by random limit 500 | 0.000 |
| 943686071 | s51187 | 172.16.5.124:52614 | s51187__xtools_prod | Sleep | 0 | | NULL | 0.000 |
+-----------+-----------------+--------------------+---------------------------------+-------------+----------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+
127 rows in set (0.00 sec)Comment Actions
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 1932 mysql 20 0 46.753g 0.034t 11252 S 316.2 55.4 29647,47 mysqld 7558 root 20 0 44796 15516 6316 S 7.0 0.0 3:56.66 iotop 496 root 20 0 12020 4548 1476 S 0.3 0.0 545:04.99 haveged 1918 root 0 -20 0 0 0 I 0.3 0.0 163:53.50 kworker/13:1H-x 7397 root 20 0 39596 3356 2660 R 0.3 0.0 0:21.77 top 10589 root 20 0 0 0 0 I 0.3 0.0 0:00.09 kworker/0:0-k
Total DISK READ : 480.60 K/s | Total DISK WRITE : 1599.50 K/s Actual DISK READ: 465.58 K/s | Actual DISK WRITE: 1657.69 K/s TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND 15424 be/4 mysql 0.00 B/s 604.50 K/s 0.00 % 5.48 % mysqld 1712 be/4 mysql 180.23 K/s 11.26 K/s 0.00 % 0.64 % mysqld 23569 be/4 mysql 15.02 K/s 0.00 B/s 0.00 % 0.52 % mysqld 1984 be/4 mysql 60.08 K/s 26.28 K/s 0.00 % 0.33 % mysqld 2025 be/4 mysql 0.00 B/s 37.55 K/s 0.00 % 0.29 % mysqld 1719 be/4 mysql 45.06 K/s 7.51 K/s 0.00 % 0.28 % mysqld 20379 be/4 mysql 0.00 B/s 22.53 K/s 0.00 % 0.27 % mysqld 4747 be/4 mysql 15.02 K/s 22.53 K/s 0.00 % 0.23 % mysqld 2035 be/4 mysql 30.04 K/s 11.26 K/s 0.00 % 0.17 % mysqld 1953 be/4 mysql 0.00 B/s 750.94 K/s 0.00 % 0.10 % mysqld
MariaDB [(none)]> SHOW PROCESSLIST;
+-----------+-----------------+--------------------+---------------------------------+-------------+----------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+-----------+-----------------+--------------------+---------------------------------+-------------+----------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+
| 2 | event_scheduler | localhost | NULL | Daemon | 19991394 | Waiting on empty queue | NULL | 0.000 |
| 272348354 | root | localhost | heartbeat | Sleep | 0 | | NULL | 0.000 |
| 678901120 | s54918 | 172.16.1.58:51293 | s54918__mismatch_finder_staging | Sleep | 1 | | NULL | 0.000 |
| 678901566 | s54438 | 172.16.1.81:33797 | s54438__rwwiki | Sleep | 20 | | NULL | 0.000 |
| 678901567 | s54438 | 172.16.1.81:55448 | s54438__rwwiki | Sleep | 5 | | NULL | 0.000 |
| 685565329 | s55096 | 172.16.0.98:56651 | s55096__quest_p | Sleep | 4 | | NULL | 0.000 |
| 743599932 | s54796 | 172.16.1.87:34406 | s54796__mismatch_finder | Sleep | 1 | | NULL | 0.000 |
| 827019329 | repl | 172.16.5.119:48946 | NULL | Binlog Dump | 2192634 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | 0.000 |
| 869135022 | s51055 | 172.16.0.34:21125 | s51055__anomiebot | Sleep | 285 | | NULL | 0.000 |
| 878551828 | s53213 | 172.16.0.98:54586 | s53213__wmopbot | Sleep | 6 | | NULL | 0.000 |
| 916180995 | s55078 | 172.16.0.98:20566 | s55078__wdrc_p | Sleep | 1 | | NULL | 0.000 |
| 934357168 | s54874 | 172.16.0.134:33762 | s54874__editgroups_commons | Sleep | 77 | | NULL | 0.000 |
| 935040056 | s51138 | 172.16.2.108:43616 | s51138__heritage_p | Sleep | 0 | | NULL | 0.000 |
| 935135764 | s51412 | 172.16.5.42:46404 | s51412__data | Sleep | 0 | | NULL | 0.000 |
| 941199037 | s51537 | 172.16.2.241:51546 | s51537__checkpersondata | Sleep | 0 | | NULL | 0.000 |
| 941747759 | s51203 | 172.16.0.34:1128 | s51203__inventory_p | Sleep | 0 | | NULL | 0.000 |
| 942237316 | s51080 | 172.16.2.47:16856 | s51080__checkwiki_p | Sleep | 20 | | NULL | 0.000 |
| 942338193 | s51230 | 172.16.5.48:36542 | s51230__linkwatcher | Sleep | 13 | | NULL | 0.000 |
| 942338209 | s51230 | 172.16.5.48:36582 | s51230__linkwatcher | Sleep | 37 | | NULL | 0.000 |
| 942338213 | s51230 | 172.16.5.48:36596 | s51230__linkwatcher | Sleep | 27 | | NULL | 0.000 |
| 942338217 | s51230 | 172.16.5.48:36608 | s51230__linkwatcher | Sleep | 18 | | NULL | 0.000 |
| 942338225 | s51230 | 172.16.5.48:36636 | s51230__linkwatcher | Sleep | 30 | | NULL | 0.000 |
| 942338243 | s51230 | 172.16.5.48:36682 | s51230__linkwatcher | Sleep | 24 | | NULL | 0.000 |
| 943075820 | s53220 | 172.16.3.214:34616 | s53220__quickstatements_p | Sleep | 6 | | NULL | 0.000 |
| 943075953 | s53220 | 172.16.3.214:34760 | s53220__quickstatements_p | Sleep | 5 | | NULL | 0.000 |
| 943075996 | s53220 | 172.16.3.214:34798 | s53220__quickstatements_p | Sleep | 6 | | NULL | 0.000 |
| 943131393 | s53220 | 172.16.3.214:52914 | s53220__quickstatements_p | Sleep | 5 | | NULL | 0.000 |
| 943257972 | s51229 | 172.16.5.40:36086 | s51230__linkwatcher | Query | 3 | Sending data | SELECT (webbug) FROM linkwatcher_webbug WHERE domain='org.archive.web.' | 0.000 |
| 943368744 | s53220 | 172.16.3.214:33732 | s53220__quickstatements_p | Execute | 0 | Sorting result | SELECT * FROM batch WHERE `status` IN ('INIT','RUN') AND NOT EXISTS (SELECT * FROM command WHERE bat | 0.000 |
| 943393267 | s53685 | 172.16.2.67:45592 | s53685__editgroups | Sleep | 0 | | NULL | 0.000 |
| 943394598 | s53220 | 172.16.3.214:47490 | s53220__quickstatements_p | Sleep | 5 | | NULL | 0.000 |
| 943420975 | s51055 | 172.16.0.134:35006 | s51055__anomiebot | Sleep | 100 | | NULL | 0.000 |
| 943585407 | s55175 | 172.16.6.95:59228 | s55175__ganfilter | Sleep | 974 | | NULL | 0.000 |
| 943655373 | s51434 | 172.16.2.67:8724 | s51434__mixnmatch_p | Execute | 6763 | Sending data | SELECT e1.id,e2.q FROM entry e1,entry e2
WHERE e1.ext_name=e2.ext_name AND e1.id!=e2.id | 0.000 |
| 943661680 | s51229 | 172.16.5.40:59764 | s51230__linkwatcher | Sleep | 382 | | NULL | 0.000 |
| 943673395 | s53098 | 172.16.5.62:60254 | s53098__fountain | Sleep | 103 | | NULL | 0.000 |
| 943679430 | s51055 | 172.16.0.98:23339 | s51055__anomiebot | Sleep | 17 | | NULL | 0.000 |
| 943680616 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST | 0.000 |
| 943683579 | s51080 | 172.16.1.142:20980 | s51080__checkwiki_p | Sleep | 3 | | NULL | 0.000 |
| 943685139 | s51080 | 172.16.2.67:33929 | s51080__checkwiki_p | Sleep | 0 | | NULL | 0.000 |
| 943685146 | s51080 | 172.16.2.67:13742 | s51080__checkwiki_p | Sleep | 1 | | NULL | 0.000 |
| 943686746 | s51080 | 172.16.0.98:46965 | s51080__checkwiki_p | Sleep | 3 | | NULL | 0.000 |
| 943694101 | s53220 | 172.16.3.214:42444 | s53220__quickstatements_p | Sleep | 1 | | NULL | 0.000 |
| 943695390 | s53098 | 172.16.5.62:39716 | s53098__fountain | Sleep | 103 | | NULL | 0.000 |
| 943700339 | s54728 | 172.16.6.95:33676 | s54728__wikiconcursos | Sleep | 10 | | NULL | 0.000 |
| 943701467 | s51330 | 172.16.0.222:22072 | s51330__copyvios_p | Sleep | 67 | | NULL | 0.000 |
| 943703263 | s51330 | 172.16.0.222:12482 | s51330__copyvios_p | Sleep | 22 | | NULL | 0.000 |
| 943708904 | s51330 | 172.16.0.222:23740 | s51330__copyvios_p | Sleep | 63 | | NULL | 0.000 |
| 943711200 | s51330 | 172.16.0.222:56701 | s51330__copyvios_p | Sleep | 319 | | NULL | 0.000 |
| 943712732 | s51330 | 172.16.0.222:31025 | s51330__copyvios_p | Sleep | 23 | | NULL | 0.000 |
| 943716080 | s54857 | 172.16.1.189:49069 | s54857__fixsuggesterbot | Sleep | 335 | | NULL | 0.000 |
| 943716182 | s51138 | 172.16.4.170:45046 | s51138__heritage_p | Sleep | 2 | | NULL | 0.000 |
| 943718300 | s51154 | 172.16.5.106:60902 | s51154__words | Sleep | 0 | | NULL | 0.000 |
| 943718668 | s54847 | 172.16.2.47:30419 | s54847__airflow | Sleep | 3 | | NULL | 0.000 |
| 943720372 | s51229 | 172.16.5.40:56212 | s51230__linkwatcher | Sleep | 0 | | NULL | 0.000 |
| 943721057 | s54847 | 172.16.2.47:14516 | s54847__airflow | Sleep | 2 | | NULL | 0.000 |
| 943722988 | s54857 | 172.16.1.189:59689 | s54857__fixsuggesterbot | Sleep | 0 | | NULL | 0.000 |
| 943726658 | s51055 | 172.16.0.98:40536 | s51055__anomiebot | Sleep | 130 | | NULL | 0.000 |
| 943726995 | s51114 | 172.16.5.1:46034 | s51114__enwp10 | Sleep | 0 | | NULL | 0.000 |
| 943731763 | s51114 | 172.16.5.1:48918 | s51114__enwp10 | Query | 0 | init | COMMIT | 0.000 |
| 943733194 | s51055 | 172.16.0.98:26404 | s51055__anomiebot | Sleep | 25 | | NULL | 0.000 |
| 943733628 | s52978 | 172.16.0.61:1392 | s52978__replacer | Sleep | 61 | | NULL | 0.000 |
| 943735647 | s52978 | 172.16.0.61:11382 | s52978__replacer | Sleep | 483 | | NULL | 0.000 |
| 943735655 | s54044 | 172.16.1.189:62626 | s54044__wdumper | Sleep | 8 | | NULL | 0.000 |
| 943735695 | s51541 | 172.16.2.67:32689 | s51541__sulwatcher | Sleep | 477 | | NULL | 0.000 |
| 943736377 | s52978 | 172.16.0.61:14957 | s52978__replacer | Sleep | 422 | | NULL | 0.000 |
| 943736489 | s54044 | 172.16.1.189:58761 | s54044__wdumper | Sleep | 415 | | NULL | 0.000 |
| 943736724 | s54044 | 172.16.1.117:59614 | s54044__wdumper | Sleep | 398 | | NULL | 0.000 |
| 943737852 | s51229 | 172.16.5.40:35932 | s51230__linkwatcher | Sleep | 179 | | NULL | 0.000 |
| 943737863 | s51083 | 172.16.0.222:51314 | s51083__ukbot | Sleep | 12 | | NULL | 0.000 |
| 943738096 | s51080 | 172.16.0.134:52549 | s51080__checkwiki_p | Sleep | 0 | | NULL | 0.000 |
| 943738269 | s54207 | 172.16.1.170:18497 | s54207__wikishield_p | Sleep | 302 | | NULL | 0.000 |
| 943738404 | s54113 | 172.16.1.185:36907 | s54113__spacemedia | Sleep | 293 | | NULL | 0.000 |
| 943738464 | s52978 | 172.16.0.61:31717 | s52978__replacer | Sleep | 289 | | NULL | 0.000 |
| 943738582 | s52978 | 172.16.0.61:1636 | s52978__replacer | Sleep | 279 | | NULL | 0.000 |
| 943738583 | s53098 | 172.16.5.62:41246 | s53098__fountain | Sleep | 11 | | NULL | 0.000 |
| 943738584 | s53098 | 172.16.5.62:41252 | s53098__fountain | Sleep | 11 | | NULL | 0.000 |
| 943738633 | s54044 | 172.16.1.189:45862 | s54044__wdumper | Sleep | 275 | | NULL | 0.000 |
| 943738668 | s51434 | 172.16.2.67:25776 | s51434__mixnmatch_p | Sleep | 0 | | NULL | 0.000 |
| 943738897 | s54113 | 172.16.2.67:49173 | s54113__spacemedia | Sleep | 0 | | NULL | 0.000 |
| 943738903 | s54044 | 172.16.1.189:34652 | s54044__wdumper | Sleep | 253 | | NULL | 0.000 |
| 943738945 | s54113 | 172.16.2.67:26487 | s54113__spacemedia | Sleep | 0 | | NULL | 0.000 |
| 943739005 | s54113 | 172.16.2.67:6418 | s54113__spacemedia | Sleep | 0 | | NULL | 0.000 |
| 943739029 | s54044 | 172.16.1.189:22960 | s54044__wdumper | Sleep | 243 | | NULL | 0.000 |
| 943739052 | s54113 | 172.16.2.67:24705 | s54113__spacemedia | Sleep | 0 | | NULL | 0.000 |
| 943739063 | s54044 | 172.16.1.189:8354 | s54044__wdumper | Sleep | 240 | | NULL | 0.000 |
| 943739550 | s52978 | 172.16.0.61:32060 | s52978__replacer | Sleep | 200 | | NULL | 0.000 |
| 943739598 | s54044 | 172.16.1.189:62896 | s54044__wdumper | Sleep | 196 | | NULL | 0.000 |
| 943739612 | s54044 | 172.16.1.189:63926 | s54044__wdumper | Sleep | 194 | | NULL | 0.000 |
| 943739785 | s54044 | 172.16.1.117:27946 | s54044__wdumper | Sleep | 182 | | NULL | 0.000 |
| 943739845 | s54044 | 172.16.1.189:20765 | s54044__wdumper | Sleep | 176 | | NULL | 0.000 |
| 943740003 | s55084 | 172.16.1.188:35154 | s51306__copyright_p | Sleep | 163 | | NULL | 0.000 |
| 943740030 | s54010 | 172.16.0.61:50612 | s54010__isa | Sleep | 79 | | NULL | 0.000 |
| 943740048 | s54010 | 172.16.0.61:11450 | s54010__isa | Sleep | 88 | | NULL | 0.000 |
| 943740224 | s52978 | 172.16.0.61:55578 | s52978__replacer | Sleep | 140 | | NULL | 0.000 |
| 943740319 | s54010 | 172.16.0.61:53073 | s54010__isa | Sleep | 87 | | NULL | 0.000 |
| 943740430 | s52978 | 172.16.0.61:20523 | s52978__replacer | Sleep | 129 | | NULL | 0.000 |
| 943740546 | s54044 | 172.16.1.189:7766 | s54044__wdumper | Sleep | 114 | | NULL | 0.000 |
| 943740597 | s54010 | 172.16.0.61:11122 | s54010__isa | Sleep | 87 | | NULL | 0.000 |
| 943740685 | s52978 | 172.16.0.61:2470 | s52978__replacer | Sleep | 98 | | NULL | 0.000 |
| 943740925 | s51537 | 172.16.7.6:56586 | s51537__checkpersondata | Sleep | 0 | | NULL | 0.000 |
| 943741211 | s51434 | 172.16.2.67:24972 | s51434__mixnmatch_p | Execute | 7 | Copying to tmp table on disk | SELECT auxiliary.id,entry_id,0,aux_p,aux_name FROM entry,auxiliary
WHERE entry_id=entry | 0.000 |
| 943741389 | s55175 | 172.16.5.188:56344 | s55175__ganfilter | Sleep | 0 | | NULL | 0.000 |
| 943741390 | s55175 | 172.16.5.188:56346 | s54328__goodarticles_p | Sleep | 7 | | NULL | 0.000 |
| 943741418 | s52978 | 172.16.0.61:10225 | s52978__replacer | Sleep | 3 | | NULL | 0.000 |
| 943741430 | s54207 | 172.16.1.170:57617 | s54207__wikishield_p | Sleep | 2 | | NULL | 0.000 |
| 943741438 | s51211 | 172.16.1.183:4790 | s51211__duplicity_p | Sleep | 0 | | NULL | 0.000 |
| 943741446 | s51114 | 172.16.5.1:46244 | s51114__enwp10 | Sleep | 0 | | NULL | 0.000 |
| 943741449 | s53666 | 172.16.1.142:43809 | s53666__testwiki2 | Sleep | 0 | | NULL | 0.000 |
| 943741451 | s53666 | 172.16.1.142:22897 | s53666__testwiki2 | Sleep | 0 | | NULL | 0.000 |
| 943741453 | s51187 | 172.16.5.124:52046 | s51187__xtools_prod | Query | 0 | Sending data | SELECT 1 FROM usage_api_timeline
WHERE date = '2022-12-07'
| 0.000 |
+-----------+-----------------+--------------------+---------------------------------+-------------+----------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+
111 rows in set (0.00 sec)Comment Actions
This one is a pretty big DB (>30GB):
| 943680177 | s51412 | 172.16.7.6:53314 | s51412__data | Query | 156 | Copying to tmp table on disk | INSERT INTO de_td_statistics (st_tmpl, st_cnt_all, st_cnt_pages, st_max_page_count) SELECT use_tmpl, | 0.000 |
This one is even bigger (~60GB), and was running for >5min:
| 943680194 | s51698 | 172.16.4.165:39646 | s51698__yetkin | Query | 470 | executing | SELECT
B.title,
SUM(vpa.vpa_count) AS count,
SUM(vpa.vpa_mobile_count) AS mobile_count,
| 0.000 |This on is >20GB:
| 943682872 | s53952 | 172.16.1.74:6510 | s53952__freebase_p | Query | 263 | Sending data | SELECT `keys`.topic_id AS keys_topic_id, `keys`.`key` AS keys_key FROM `keys` WHERE `keys`.`key` = | 0.000 | | 943683051 | s53952 | 172.16.1.74:1456 | s53952__freebase_p | Query | 248 | Sending data | SELECT `keys`.topic_id AS keys_topic_id, `keys`.`key` AS keys_key FROM `keys` WHERE `keys`.`key` = | 0.000 |
And this one is our second biggest DB, 160GB:
| 943655373 | s51434 | 172.16.2.67:8724 | s51434__mixnmatch_p | Execute | 6763 | Sending data | SELECT e1.id,e2.q FROM entry e1,entry e2
WHERE e1.ext_name=e2.ext_name AND e1.id!=e2.id | 0.000 |There's also this one (~34GB) writing to net:
| 943686004 | s51114 | 172.16.5.1:38882 | s51114__enwp10 | Query | 0 | Writing to net | COMMIT | 0.000 |
My guess would be that that time is when a bunch of crons/reports from the most db-intensive tools get trigger at the same time xd
Maybe some can be splayed around +-1h from midnight?
(aside from debugging the queries and improving their performance, maybe not all the data is needed, indices might be missing...)
Comment Actions
So the likely action here is -- identify the tools running those giant queries, ask them to move their crons to different times. Correct?
Comment Actions
So the likely action here is -- identify the tools running those giant queries
Yes, but they don't look like giant queries, just many queries:
Apparently, they're all of type "commit" and "admin_commands"
I couldn't find much information online, but this thread seems to suggest admin_commands should be rare, and a spike could be caused by too many PING commands issued by a client library (I'm not convinced that this is what we are seeing here).
The impact on replication lag is not that significant:
Same for the impact on IOPS:
Comment Actions
This is still happening, but was not a blocker for the ToolsDB upgrade.
I removed the parent task, but it would be interesting to find out which tool is generating this huge number of queries every 24 hours.