After upgrade of system packages high traffic from mysql to all poller

kristoferus75 · 15 February 2022 09:46

After upgrade system packages of my librenms which is installed rrdcached ngnix php-fpm memcached
the traffic from mysql to the poller machines is extremly high and the webinterface is very slow

the mysql server and poller are other machines

Please help !

no error with validate

$ ./validate.php 
====================================
Component | Version
--------- | -------
LibreNMS  | 21.12.1-29-g2f2d43bab
DB Schema | 2021_11_29_165436_improve_ports_search_index (229)
PHP       | 7.4.27
Python    | 3.8.12
MySQL     | 5.7.33
RRDTool   | 1.7.2
SNMP      | 5.9
====================================

[OK]    Composer Version: 2.2.6
[OK]    Dependencies up-to-date.
[OK]    Database connection successful
[OK]    Database schema correct
[INFO]  Detected Python Wrapper
[OK]    Connection to memcached is ok

laf · 16 February 2022 21:56

Their were a lot of responses to your question on discord the other day, have you looked into those?

All I could suggest right now is to run a tcpdump and see what’s coming in to the MySQL box, or enable general mysql logging so you can see what queries are being run all the time.

kristoferus75 · 17 February 2022 18:04

general log:

Prepare UPDATE availability set availability_perc=? WHERE availability_id = ?
this i see in my general log file very often
and also
Execute UPDATE ports_statistics

can this be an issue of the high bandwith ?

here the tpdump anlysis


# 1s user time, 39.1ms system time, 38.26M rss, 53.91M vsz
# Current date: Thu Feb 17 19:19:54 2022
# Hostname: corenms03
# Files: mysql.tcp.txt
# Overall: 2.49k total, 9 unique, 393.50 QPS, 0.27x concurrency __________
# Time range: 2022-02-17 19:19:37.074692 to 19:19:43.405094
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time             2s       0     9ms   682us     4ms     1ms    84us
# Rows affecte          37       0       1    0.01       0    0.12       0
# Query size       201.05k      25     472   82.65  124.25   48.68   97.36
# Warning coun           4       0       1    0.00       0    0.04       0
# Boolean:
# No index use  15% yes,  84% no

# Profile
# Rank Query ID                            Response time Calls R/Call V/M 
# ==== =================================== ============= ===== ====== ====
#    1 0x2C57B2E9A802F6D2D38420E2CFE99B81   1.5432 90.7%   393 0.0039  0.00 SELECT ipv?_addresses ports
#    2 0x29D74D6FDD31C67B47E2772F9E169562   0.0720  4.2%   393 0.0002  0.00 SELECT devices
#    3 0x3DD6D6A0639BD987ABDBFF7A250FDEFD   0.0325  1.9%   393 0.0001  0.00 SELECT ipv?_addresses ports
# MISC 0xMISC                               0.0531  3.1%  1312 0.0000   0.0 <6 ITEMS>

# Query 1: 62.14 QPS, 0.24x concurrency, ID 0x2C57B2E9A802F6D2D38420E2CFE99B81 at byte 2108588
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2022-02-17 19:19:37.079341 to 19:19:43.404084
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         15     393
# Exec time     90      2s   630us     9ms     4ms     8ms     1ms     3ms
# Rows affecte   0       0       0       0       0       0       0       0
# Query size    24  49.55k     129     138  129.10  124.25    1.31  124.25
# Warning coun   0       0       0       0       0       0       0       0
# Boolean:
# No index use 100% yes,   0% no
# String:
# Hosts        172.30.31.29 (386/98%), 172.30.31.25 (7/1%)
# Statement id 174142 (1/0%), 174144 (1/0%), 174146 (1/0%)... 390 more
# Query_time distribution
#   1us
#  10us
# 100us  #
#   1ms  ################################################################
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS LIKE 'ipv4_addresses'\G
#    SHOW CREATE TABLE `ipv4_addresses`\G
#    SHOW TABLE STATUS LIKE 'ports'\G
#    SHOW CREATE TABLE `ports`\G
EXECUTE SELECT `device_id` FROM `ipv4_addresses` AS A, `ports` AS I WHERE A.ipv4_address = "vm_cust_01" AND I.port_id = A.port_id\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `device_id` FROM `ipv4_addresses` AS A, `ports` AS I WHERE A.ipv4_address = "vm_cust_01" AND I.port_id = A.port_id\G

# Query 2: 62.09 QPS, 0.01x concurrency, ID 0x29D74D6FDD31C67B47E2772F9E169562 at byte 1772353
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2022-02-17 19:19:37.075253 to 19:19:43.404780
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         15     393
# Exec time      4    72ms    83us   299us   183us   214us    37us   194us
# Rows affecte   0       0       0       0       0       0       0       0
# Query size    18  38.07k      99     117   99.18   97.36    1.54   97.36
# Warning coun   0       0       0       0       0       0       0       0
# String:
# Hosts        172.30.31.29 (387/98%), 172.30.31.25 (6/1%)
# Statement id 174141 (1/0%), 174143 (1/0%), 174147 (1/0%)... 390 more
# Query_time distribution
#   1us
#  10us  #####
# 100us  ################################################################
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS LIKE 'devices'\G
#    SHOW CREATE TABLE `devices`\G
EXECUTE SELECT `device_id` FROM devices WHERE `hostname` = "vm_cust_01" OR `sysName` = "vm_cust_01"\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `device_id` FROM devices WHERE `hostname` = "vm_cust_01" OR `sysName` = "vm_cust_01"\G

# Query 3: 62.14 QPS, 0.01x concurrency, ID 0x3DD6D6A0639BD987ABDBFF7A250FDEFD at byte 1768581
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2022-02-17 19:19:37.075614 to 19:19:43.400416
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         15     393
# Exec time      1    32ms    37us   122us    82us    98us    19us    89us
# Rows affecte   0       0       0       0       0       0       0       0
# Query size    22  45.29k     118     118     118     118       0     118
# Warning coun   0       0       0       0       0       0       0       0
# String:
# Hosts        172.30.31.29 (386/98%), 172.30.31.25 (7/1%)
# Statement id 174142 (1/0%), 174144 (1/0%), 174146 (1/0%)... 390 more
# Query_time distribution
#   1us
#  10us  ################################################################
# 100us  ######
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS LIKE 'ipv4_addresses'\G
#    SHOW CREATE TABLE `ipv4_addresses`\G
#    SHOW TABLE STATUS LIKE 'ports'\G
#    SHOW CREATE TABLE `ports`\G
PREPARE SELECT `device_id` FROM `ipv4_addresses` AS A, `ports` AS I WHERE A.ipv4_address = ? AND I.port_id = A.port_id\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `device_id` FROM `ipv4_addresses` AS A, `ports` AS I WHERE A.ipv4_address = ? AND I.port_id = A.port_id\G

# Prepared statements
# Rank Query ID                           PREP PREP Response EXEC EXEC Res
# ==== ================================== ==== ============= ==== ========
#    1 0x2C57B2E9A802F6D2D38420E2CFE99B81  393  0.0325  2.0%  393  1.5432 93.7% SELECT ipv?_addresses ports
#    2 0x29D74D6FDD31C67B47E2772F9E169562  393  0.0306  1.9%  393  0.0720  4.4% SELECT devices

kristoferus75 · 18 February 2022 10:26

i hope this helps us more:

Here again analyses of general log

# Overall: 1.49M total, 14 unique, 0 QPS, 0x concurrency _________________
# Time range: 2022-02-18T10:08:49.976215Z to 2022-02-18T10:10:59.039943Z
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time              0       0       0       0       0       0       0
# Query size        41.63M      14     169   29.26   28.75    1.25   28.75

# Profile
# Rank Query ID                            Response time Calls  R/Call V/M
# ==== =================================== ============= ====== ====== ===
#    1 0x9C7F3A670889D9B5440061F2EB7A7639   0.0000  0.0% 492344 0.0000  0.00 ADMIN CLOSE
#    2 0x2A89113BB920BA301228F184663C8627   0.0000  0.0% 492315 0.0000  0.00 ADMIN EXECUTE
#    3 0xDA556F9115773A1A99AA0165670CE848   0.0000  0.0% 492310 0.0000  0.00 ADMIN PREPARE
#    4 0x7417646A9FE969365D51E5F01B88B79E   0.0000  0.0%   3765 0.0000  0.00 ADMIN CONNECT
#    5 0xEDBC971AEC392917AA353644DE4C4CB4   0.0000  0.0%   3762 0.0000  0.00 ADMIN QUIT
#    6 0xDD749893CA4A219A6C099B0B73EA7633   0.0000  0.0%   3751 0.0000  0.00 USE
#    7 0x46B9E5597A64F0E744001A2E53C1AB70   0.0000  0.0%   3741 0.0000  0.00 SET
#    8 0x238EE23DCFCA5767B62F55BB3B3B94B1   0.0000  0.0%     28 0.0000  0.00 SET
#    9 0x4433FB38C52384296EB7B4798364020E   0.0000  0.0%      7 0.0000  0.00 SELECT devices
#   10 0x15B507BB800854696858BF66524CA958   0.0000  0.0%      7 0.0000  0.00 SELECT services devices
#   11 0x6C7EE4EA49F5AA1F958D575C126602EA   0.0000  0.0%      7 0.0000  0.00 UPDATE pollers
#   12 0x6C79C7368C06246FA61F6B1C6BF9ACD5   0.0000  0.0%      1 0.0000  0.00 SELECT devices
#   13 0xD5D717C73F4E33F21382A5529382F955   0.0000  0.0%      1 0.0000  0.00 SELECT services
#   14 0x87D64605C2F1D0379F5BF343C142564F   0.0000  0.0%      1 0.0000  0.00 SET

# Query 1: 0 QPS, 0x concurrency, ID 0x9C7F3A670889D9B5440061F2EB7A7639 at byte 346654866
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2022-02-18T10:08:49.976218Z to 2022-02-18T10:10:59.039669Z
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         32  492344
# Exec time      0       0       0       0       0       0       0       0
# Query size    31  13.15M      28      28      28      28       0      28
# String:
# Databases    customer
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
administrator command: Close\G

# Query 2: 0 QPS, 0x concurrency, ID 0x2A89113BB920BA301228F184663C8627 at byte 346657351
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2022-02-18T10:08:49.976215Z to 2022-02-18T10:10:59.039943Z
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         32  492315
# Exec time      0       0       0       0       0       0       0       0
# Query size    33  14.09M      30      30      30      30       0      30
# String:
# Databases    customer
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
administrator command: Execute\G

# Query 3: 0 QPS, 0x concurrency, ID 0xDA556F9115773A1A99AA0165670CE848 at byte 346655266
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2022-02-18T10:08:49.976217Z to 2022-02-18T10:10:59.039745Z
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         32  492310
# Exec time      0       0       0       0       0       0       0       0
# Query size    33  14.09M      30      30      30      30       0      30
# String:
# Databases    customer
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
administrator command: Prepare\G

# Query 4: 0 QPS, 0x concurrency, ID 0x7417646A9FE969365D51E5F01B88B79E at byte 345932932
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2022-02-18T10:08:49.989472Z to 2022-02-18T10:10:58.903981Z
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    3765
# Exec time      0       0       0       0       0       0       0       0
# Query size     0 110.30k      30      30      30      30       0      30
# String:
# Databases    customer
# Hosts        172.30.31.49 (823/21%), 172.30.31.52 (812/21%)... 5 more
# Users        customer
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
administrator command: Connect\G

# Query 5: 0 QPS, 0x concurrency, ID 0xEDBC971AEC392917AA353644DE4C4CB4 at byte 346648785
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2022-02-18T10:08:49.977623Z to 2022-02-18T10:10:59.037451Z
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    3762
# Exec time      0       0       0       0       0       0       0       0
# Query size     0  99.19k      27      27      27      27       0      27
# String:
# Databases    customer
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
administrator command: Quit\G

# Query 6: 0 QPS, 0x concurrency, ID 0xDD749893CA4A219A6C099B0B73EA7633 at byte 345935422
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2022-02-18T10:08:49.994132Z to 2022-02-18T10:10:58.904886Z
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    3751
# Exec time      0       0       0       0       0       0       0       0
# Query size     0  51.28k      14      14      14      14       0      14
# String:
# Databases    customer
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
use `customer`\G

# Query 7: 0 QPS, 0x concurrency, ID 0x46B9E5597A64F0E744001A2E53C1AB70 at byte 346579760
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2022-02-18T10:08:49.998219Z to 2022-02-18T10:10:59.019360Z
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    3741
# Exec time      0       0       0       0       0       0       0       0
# Query size     0  54.80k      15      15      15      15       0      15
# String:
# Databases    customer
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
SET sql_mode=''\G

# Query 8: 0 QPS, 0x concurrency, ID 0x238EE23DCFCA5767B62F55BB3B3B94B1 at byte 91351053
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2022-02-18T10:10:01.108133Z to 2022-02-18T10:10:01.908458Z
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0      28
# Exec time      0       0       0       0       0       0       0       0
# Query size     0     504      18      18      18      18       0      18
# String:
# Databases    customer
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
SET AUTOCOMMIT = 1\G

# Query 9: 0 QPS, 0x concurrency, ID 0x4433FB38C52384296EB7B4798364020E at byte 91353814
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2022-02-18T10:10:01.109283Z to 2022-02-18T10:10:01.934324Z
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0       7
# Exec time      0       0       0       0       0       0       0       0
# Query size     0     768     108     110  109.71  107.34       0  107.34
# String:
# Databases    customer
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `customer` LIKE 'devices'\G
#    SHOW CREATE TABLE `customer`.`devices`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT device_id FROM devices WHERE poller_group IN (0,2) AND disabled = 0 ORDER BY last_polled_timetaken DESC\G

# Query 10: 0 QPS, 0x concurrency, ID 0x15B507BB800854696858BF66524CA958 at byte 91340003
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2022-02-18T10:10:01.115182Z to 2022-02-18T10:10:01.843977Z
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0       7
# Exec time      0       0       0       0       0       0       0       0
# Query size     0   1.15k     167     169  168.71  166.51       0  166.51
# String:
# Databases    customer
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `customer` LIKE 'services'\G
#    SHOW CREATE TABLE `customer`.`services`\G
#    SHOW TABLE STATUS FROM `customer` LIKE 'devices'\G
#    SHOW CREATE TABLE `customer`.`devices`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT DISTINCT(services.device_id) FROM services LEFT JOIN devices ON services.device_id = devices.device_id WHERE devices.poller_group IN(0,2) AND devices.disabled = 0\G

# Query 11: 0 QPS, 0x concurrency, ID 0x6C7EE4EA49F5AA1F958D575C126602EA at byte 90739646
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2022-02-18T10:09:13.832073Z to 2022-02-18T10:09:25.190654Z
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0       7
# Exec time      0       0       0       0       0       0       0       0
# Query size     0     760     107     109  108.57  107.34       1  107.34
# String:
# Databases    customer
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `customer` LIKE 'pollers'\G
#    SHOW CREATE TABLE `customer`.`pollers`\G
UPDATE pollers SET last_polled=NOW(), devices='2800', time_taken='254' WHERE poller_name='[corenms06] Poller'\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select  last_polled=NOW(), devices='2800', time_taken='254' from pollers where  poller_name='[corenms06] Poller'\G

# Query 12: 0 QPS, 0x concurrency, ID 0x6C79C7368C06246FA61F6B1C6BF9ACD5 at byte 91267704
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2022-02-18T10:10:01.235960Z
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0       1
# Exec time      0       0       0       0       0       0       0       0
# Query size     0      49      49      49      49      49       0      49
# String:
# Databases    customer
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `customer` LIKE 'devices'\G
#    SHOW CREATE TABLE `customer`.`devices`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT max(device_id),min(device_id) FROM devices\G

# Query 13: 0 QPS, 0x concurrency, ID 0xD5D717C73F4E33F21382A5529382F955 at byte 91265053
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2022-02-18T10:10:01.115930Z
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0       1
# Exec time      0       0       0       0       0       0       0       0
# Query size     0      50      50      50      50      50       0      50
# String:
# Databases    customer
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `customer` LIKE 'services'\G
#    SHOW CREATE TABLE `customer`.`services`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT max(device_id),min(device_id) FROM services\G

# Query 14: 0 QPS, 0x concurrency, ID 0x87D64605C2F1D0379F5BF343C142564F at byte 346656971
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2022-02-18T10:10:59.039912Z
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0       1
# Exec time      0       0       0       0       0       0       0       0
# Query size     0      28      28      28      28      28       0      28
# String:
# Databases    customer
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+

kristoferus75 · 20 February 2022 16:26

So now i have the right output from mysql:

What causes this ?

select * from plugins where version = 2
…
| 61527 | ExamplePlugin | 0 | 2 | NULL |
| 61528 | ExamplePlugin | 0 | 2 | NULL |
| 61529 | ExamplePlugin | 0 | 2 | NULL |
| 61530 | ExamplePlugin | 0 | 2 | NULL |
| 61531 | ExamplePlugin | 0 | 2 | NULL |
| 61532 | ExamplePlugin | 0 | 2 | NULL |
| 61533 | ExamplePlugin | 0 | 2 | NULL |
±----------±--------------±--------------±--------±---------+
61532 rows in set (0.06 sec)

normal working librenms setup:
±----------±--------------±--------------±--------±---------+
| plugin_id | plugin_name | plugin_active | version | settings |
±----------±--------------±--------------±--------±---------+
| 2 | ExamplePlugin | 0 | 2 | NULL |
| 3 | ExamplePlugin | 0 | 2 | NULL |
| 4 | ExamplePlugin | 0 | 2 | NULL |
| 5 | ExamplePlugin | 0 | 2 | NULL |
| 6 | ExamplePlugin | 0 | 2 | NULL |
| 7 | ExamplePlugin | 0 | 2 | NULL |
| 8 | ExamplePlugin | 0 | 2 | NULL |
±----------±--------------±--------------±--------±---------+
7 rows in set (0.00 sec)

# Overall: 3.78M total, 261 unique, 17.43k QPS, 12.28x concurrency _______
# Time range: 2022-02-20T16:06:25 to 2022-02-20T16:10:02
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          2665s     1us     16s   704us   424us    15ms    73us
# Lock time            54s       0   732us    14us    63us    25us       0
# Rows sent        888.11M       0  60.09k  246.21    0.99   3.41k       0
# Rows examine       1.04G       0   6.51M  295.16    3.89   7.21k       0
# Query size       374.40M       0   3.24k  103.80  420.77  178.17   31.70

# Profile
# Rank Query ID                            Response time  Calls   R/Call  
# ==== =================================== ============== ======= ======= 
#    1 0xD92B740F1A2D69E61CD9F671E8ABE9E7  964.0623 36.2%   10297  0.0936  0.04 SELECT plugins
#    2 0x7244B742AA6D6A2A366E56D912B16F72  693.6645 26.0%   10225  0.0678  0.02 SELECT devices device_group_device
#    3 0xDA556F9115773A1A99AA0165670CE848  131.6527  4.9% 1250482  0.0001  0.00 ADMIN PREPARE
#    4 0x9ABDE132E3CC17FA0B0F8492E949C611  106.5023  4.0%  143719  0.0007  0.05 SELECT devices
#    5 0x4B997CEBD3D59F77FEABB4D0F7217FDD   43.3852  1.6%       3 14.4617  0.12 SELECT syslog device_group_device
#    6 0xA51752125BBD75FEE1D7711E6D3C4EF4   37.7429  1.4%   39426  0.0010  0.03 UPDATE availability
#    7 0xED7CEEAD819780BBADF3A865395B56D1   37.4749  1.4%   42897  0.0009  0.03 UPDATE ports_statistics
#    8 0x68B076317C4F880832E4DED98D0BE837   34.3540  1.3%   39425  0.0009  0.03 SELECT device_outages
#    9 0x5C2079292E87D7ADE53B776A8C304704   28.3317  1.1%   10268  0.0028  0.00 SELECT alert_rules alert_device_map alert_group_map device_group_device alert_location_map devices device_group_device
#   10 0x1E266DA60BA820BEE9FFB34A20A71FC3   28.1788  1.1%   28913  0.0010  0.03 UPDATE ports
#   11 0x829B362756A0A2EA93CF1DB743EC602D   19.0201  0.7%   20530  0.0009  0.04 SELECT devices
#   12 0xF89A684790429543122A3BAEFCE85213   17.4469  0.7%   24147  0.0007  0.04 SELECT sensors
#   13 0xCB57AC2073153040484C7288D3A93CCB   16.1029  0.6%    9846  0.0016  0.05 UPDATE devices
#   14 0xD71F5560A7497EE4CB7C3A384B69D4FD   14.9542  0.6%   10154  0.0015  0.05 INSERT device_perf
#   15 0x36C6C2355E7489837F3995F06E68D0CB   14.4047  0.5%   13723  0.0010  0.02 UPDATE ports
#   16 0xBB92C3D80A89B029DCDCCFB674C52570   13.6355  0.5%   10267  0.0013  0.04 SELECT devices device_group_device device_groups
#   17 0xACACDAD3F6498FF910D65D41BF8D9DF3   13.4551  0.5%    9864  0.0014  0.01 SELECT ports ports_statistics
#   18 0xCD48070D5B790B29355C61277557CE69   13.2952  0.5%   11319  0.0012  0.05 SELECT locations
#   19 0x09F7BB7B79196439BB992AA585C981BA   12.4524  0.5%    9910  0.0013  0.03 UPDATE devices
#   20 0xA9BFB0C95C8DE5C667CC3A5C0258CEB2   12.3446  0.5%   10267  0.0012  0.03 SELECT devices device_group_device device_groups
# MISC 0xMISC                              412.6714 15.5% 2076602  0.0002   0.0 <241 ITEMS>

# Query 1: 47.45 QPS, 4.44x concurrency, ID 0xD92B740F1A2D69E61CD9F671E8ABE9E7 at byte 230320754
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.04
# Time range: 2022-02-20T16:06:25 to 2022-02-20T16:10:02
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0   10297
# Exec time     36    964s    36ms   629ms    94ms   230ms    64ms    65ms
# Lock time      0   290ms    11us    77us    28us    40us     8us    28us
# Rows sent     68 604.24M  60.09k  60.09k  60.09k  60.09k       0  60.09k
# Rows examine  56 604.25M  60.09k  60.09k  60.09k  60.09k       0  60.09k
# Query size     0 432.39k      43      43      43      43       0      43
# String:
# Databases    customer
# Hosts        172.30.31.40 (2127/20%)... 6 more
# Users        customer
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ################################################################
# 100ms  ##############################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `customer` LIKE 'plugins'\G
#    SHOW CREATE TABLE `customer`.`plugins`\G
# EXPLAIN /*!50100 PARTITIONS*/
select * from `plugins` where `version` = 2\G

# Query 2: 48.00 QPS, 3.26x concurrency, ID 0x7244B742AA6D6A2A366E56D912B16F72 at byte 795064534
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02
# Time range: 2022-02-20T16:06:25 to 2022-02-20T16:09:58
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0   10225
# Exec time     26    694s    27ms   309ms    68ms   155ms    39ms    51ms
# Lock time      0   408ms    15us   131us    39us    57us    11us    38us
# Rows sent     31 280.27M  28.07k  28.07k  28.07k  27.29k       0  27.29k
# Rows examine  38 414.50M  41.51k  41.51k  41.51k  40.32k       0  40.32k
# Query size     1   5.13M     526     526     526     526       0     526
# String:
# Databases    customer
# Hosts        172.30.31.38 (2130/20%)... 6 more
# Users        customer
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ################################################################
# 100ms  ################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `customer` LIKE 'devices'\G
#    SHOW CREATE TABLE `customer`.`devices`\G
#    SHOW TABLE STATUS FROM `customer` LIKE 'device_group_device'\G
#    SHOW CREATE TABLE `customer`.`device_group_device`\G
# EXPLAIN /*!50100 PARTITIONS*/
select `devices`.`device_id`, `device_group_device`.`device_group_id` as `pivot_device_group_id`, `device_group_device`.`device_id` as `pivot_device_id` from `devices` inner join `device_group_device` on `devices`.`device_id` = `device_group_device`.`device_id` where `device_group_device`.`device_group_id` in (1, 2, 3, 4, 7, 10, 11, 12, 15, 16, 17, 18, 20, 22, 23, 24, 25, 28, 29, 31, 43, 44, 45, 46, 47, 51, 52, 53, 54, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 77, 78, 79, 80, 81, 82)\G

# Query 3: 5.76k QPS, 0.61x concurrency, ID 0xDA556F9115773A1A99AA0165670CE848 at byte 1132111869
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2022-02-20T16:06:25 to 2022-02-20T16:10:02
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         33 1250482
# Exec time      4    132s    10us     4ms   105us   224us    68us    84us
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size     9  35.78M      30      30      30      30       0      30
# String:
# Databases    customer
# Hosts        172.30.31.38 (259175/20%)... 6 more
# Users        customer
# Query_time distribution
#   1us
#  10us  ################################################################
# 100us  ##########################################
#   1ms  #
#  10ms
# 100ms
#    1s
#  10s+
administrator command: Prepare\G

PipoCanaja · 20 February 2022 22:36

Hi @kristoferus75
Do you have a high number of Cisco devices, switches in particular ? And do you have a lot of VLANs, possibly running RSTP or MSTP?
If yes, STP polling could waste a lot of polling time and DB updates. Try to check the polling time for STP module after upgrade.
And if yes, I would suggest to disable STP polling on cisco switches for now.

Martin_Millesich · 23 February 2022 06:37

On the left side you can see the mysql traffic to the pollers before the upgrade (~ 300 Mbit/s), after FreeBSD upgrading it was almost 4 times as high.

The Plugin Table contained over 61k “ExamplePlugin” entries, we deleted them all, after that the mysql traffic was normal again!

system · 2 March 2022 06:38

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.