[ZBX-12954] Slow query related to 'Problem' table Created: 2017 Oct 27  Updated: 2024 Apr 10  Resolved: 2024 Jan 23

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: API (A), Frontend (F)
Affects Version/s: 3.4.3
Fix Version/s: None

Type: Incident report Priority: Trivial
Reporter: CHRETIEN Landry Assignee: Zabbix Development Team
Resolution: Fixed Votes: 3
Labels: odbc
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: PNG File Capture-1.PNG     PNG File Capture.PNG     Text File server_crash.txt    
Issue Links:
Duplicate
duplicates ZBX-10962 Crash/exit in poller performing ODBC ... Closed
duplicates ZBX-21477 Users who belong to the Administrator... Closed
duplicates ZBX-12133 API event.get performance regression ... Closed
is duplicated by ZBX-13789 Widget Problems : very bad request ex... Closed
Team: Team A
Team: Team A
Sprint: Sprint 25, Sprint 26

 Description   

Hello,

We just migrate from 2.4.8 to 3.4.3 and we switch from Virtual server to physical server with SAN storage for Mysql DB.

After some users connexions (Average of 17 users connected) we had slow query in related to view and widget 'Problem'

This is a kind of request taking around 30 - 40s

SELECT p.eventid,p.objectid,p.clock,p.ns FROM problem p WHERE p.source='0' AND p.object='0' AND NOT EXISTS (SELECT NULL FROM functions f,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid IN ('58','61','70','71') WHERE p.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MA:@permission)<2 OR MIN(permission) IS NULL OR MIN(permission)=0) AND (p.r_eventid IS NULL OR p.r_clock>1509091089) ORDER BY p.eventid DESC LIMIT 1001

SELECT p.eventid,p.objectid,p.clock,p.ns FROM problem p WHERE p.source='0' AND p.object='0' AND NOT EXISTS (SELECT NULL FROM functions f,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid IN ('23','50','67') WHERE p.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MA:@permission)<2 OR MIN(permission) IS NULL OR MIN(permission)=0) AND (p.r_eventid IS NULL OR p.r_clock>1509091078) AND p.eventid<='9851748' ORDER BY p.eventid DESC LIMIT 1001

Our Zabbix instance (32G Ram, 16Core 'E2609', 1To of SAN Drive with FC) iowait fall down to nearly 0

This zabbix instance host around :

  • 900 hosts
  • 100K Items
  • 50K Triggers

 Comments   
Comment by CHRETIEN Landry [ 2017 Oct 27 ]

Hello, just information, I had to restart the zabbix-server process a 10:11 AM (on the graph linked) in order to have idle back to 'Normal' values

I get a server crash few minutes after (see log linked)

Comment by Vladislavs Sokurenko [ 2017 Oct 27 ]

Backtrace for easier searching

167656:20171027:110728.343 === Backtrace: ===
167656:20171027:110728.348 21: /usr/sbin/zabbix_server: poller #4 [got 10 values in 0.004139 sec, getting values](zbx_log_fatal_info+0x13c) [0x7f915c114678]
167656:20171027:110728.348 20: /usr/sbin/zabbix_server: poller #4 [got 10 values in 0.004139 sec, getting values](+0xeaa8d) [0x7f915c114a8d]
167656:20171027:110728.348 19: /lib64/libc.so.6(+0x35670) [0x7f9158344670]
167656:20171027:110728.348 18: /usr/lib64/libmyodbc5.so(get_ctype+0x2e) [0x7f913288ef7e]
167656:20171027:110728.348 17: /usr/lib64/libmyodbc5.so(init_parser+0x1c) [0x7f913288efac]
167656:20171027:110728.348 16: /usr/lib64/libmyodbc5.so(parse+0x21) [0x7f913288fc41]
167656:20171027:110728.348 15: /usr/lib64/libmyodbc5.so(prepare+0x44) [0x7f9132894484]
167656:20171027:110728.348 14: /usr/lib64/libmyodbc5.so(SQLPrepareWImpl+0x8c) [0x7f913289ae9c]
167656:20171027:110728.348 13: /usr/lib64/libmyodbc5.so(SQLExecDirectW+0x9) [0x7f913289aeb9]
167656:20171027:110728.348 12: /lib64/libodbc.so.2(SQLExecDirect+0x27f) [0x7f915aea406f]
167656:20171027:110728.348 11: /usr/sbin/zabbix_server: poller #4 [got 10 values in 0.004139 sec, getting values](odbc_DBselect+0x76) [0x7f915c16c4c0]
167656:20171027:110728.348 10: /usr/sbin/zabbix_server: poller #4 [got 10 values in 0.004139 sec, getting values](+0x5fdbc) [0x7f915c089dbc]
167656:20171027:110728.349 9: /usr/sbin/zabbix_server: poller #4 [got 10 values in 0.004139 sec, getting values](get_value_db+0xa9) [0x7f915c089ff1]
167656:20171027:110728.349 8: /usr/sbin/zabbix_server: poller #4 [got 10 values in 0.004139 sec, getting values](+0x51afc) [0x7f915c07bafc]
167656:20171027:110728.349 7: /usr/sbin/zabbix_server: poller #4 [got 10 values in 0.004139 sec, getting values](+0x52f94) [0x7f915c07cf94]
167656:20171027:110728.349 6: /usr/sbin/zabbix_server: poller #4 [got 10 values in 0.004139 sec, getting values](poller_thread+0x1aa) [0x7f915c07dc1b]
167656:20171027:110728.349 5: /usr/sbin/zabbix_server: poller #4 [got 10 values in 0.004139 sec, getting values](zbx_thread_start+0x37) [0x7f915c121a73]
167656:20171027:110728.349 4: /usr/sbin/zabbix_server: poller #4 [got 10 values in 0.004139 sec, getting values](MAIN_ZABBIX_ENTRY+0x835) [0x7f915c06a202]
167656:20171027:110728.349 3: /usr/sbin/zabbix_server: poller #4 [got 10 values in 0.004139 sec, getting values](daemon_start+0x325) [0x7f915c113dbe]
167656:20171027:110728.349 2: /usr/sbin/zabbix_server: poller #4 [got 10 values in 0.004139 sec, getting values](main+0x317) [0x7f915c0699cb]
167656:20171027:110728.349 1: /lib64/libc.so.6(__libc_start_main+0xf5) [0x7f9158330b15]
167656:20171027:110728.349 0: /usr/sbin/zabbix_server: poller #4 [got 10 values in 0.004139 sec, getting values](+0x33ce9) [0x7f915c05dce9]
Comment by Vladislavs Sokurenko [ 2017 Oct 27 ]

Does disabling ODBC checks or moving them to proxy help ?
Are you using mariadb ?
Please provide output of

ldd /usr/lib64/libmyodbc5.s
ldd sbin/zabbix_server
Comment by CHRETIEN Landry [ 2017 Oct 27 ]

Hello, I confirm disabling the odbc monitoring help, no crash report since.

We are using mysql-commercial-libs-5.7.20-1.1.el7.x86_64

# ldd /usr/lib64/libmyodbc5.so
        linux-vdso.so.1 =>  (0x00007ffee9f45000)
        libodbc.so.2 => /lib64/libodbc.so.2 (0x00007fc6353e1000)
        libmysqlclient.so.18 => /usr/lib64/mysql/libmysqlclient.so.18 (0x00007fc634c48000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fc634a2b000)
        libm.so.6 => /lib64/libm.so.6 (0x00007fc634729000)
        libodbcinst.so.2 => /lib64/libodbcinst.so.2 (0x00007fc634517000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007fc634312000)
        libc.so.6 => /lib64/libc.so.6 (0x00007fc633f51000)
        libltdl.so.7 => /lib64/libltdl.so.7 (0x00007fc633d47000)
        libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007fc633a3e000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fc633828000)
        /lib64/ld-linux-x86-64.so.2 (0x00007fc6358ac000)

# ldd /sbin/zabbix_server
        linux-vdso.so.1 =>  (0x00007ffda69f1000)
        libmysqlclient.so.18 => /usr/lib64/mysql/libmysqlclient.so.18 (0x00007f3b5c641000)
        libiksemel.so.3 => /lib64/libiksemel.so.3 (0x00007f3b5c433000)
        libxml2.so.2 => /lib64/libxml2.so.2 (0x00007f3b5c0c9000)
        libodbc.so.2 => /lib64/libodbc.so.2 (0x00007f3b5be61000)
        libnetsnmp.so.31 => /lib64/libnetsnmp.so.31 (0x00007f3b5bb5f000)
        libssh2.so.1 => /lib64/libssh2.so.1 (0x00007f3b5b934000)
        libOpenIPMI.so.0 => /lib64/libOpenIPMI.so.0 (0x00007f3b5b627000)
        libOpenIPMIposix.so.0 => /lib64/libOpenIPMIposix.so.0 (0x00007f3b5b421000)
        libevent-2.0.so.5 => /lib64/libevent-2.0.so.5 (0x00007f3b5b1d8000)
        libssl.so.10 => /lib64/libssl.so.10 (0x00007f3b5af66000)
        libcrypto.so.10 => /lib64/libcrypto.so.10 (0x00007f3b5ab05000)
        libldap-2.4.so.2 => /lib64/libldap-2.4.so.2 (0x00007f3b5a8b1000)
        liblber-2.4.so.2 => /lib64/liblber-2.4.so.2 (0x00007f3b5a6a2000)
        libcurl.so.4 => /lib64/libcurl.so.4 (0x00007f3b5a43a000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f3b5a137000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f3b59f33000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f3b59d19000)
        libpcreposix.so.0 => /lib64/libpcreposix.so.0 (0x00007f3b59b15000)
        libpcre.so.1 => /lib64/libpcre.so.1 (0x00007f3b598b4000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f3b5969e000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f3b592dc000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f3b590c0000)
        libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f3b58db8000)
        libgnutls.so.28 => /lib64/libgnutls.so.28 (0x00007f3b58a82000)
        libgcrypt.so.11 => /lib64/libgcrypt.so.11 (0x00007f3b58801000)
        libgpg-error.so.0 => /lib64/libgpg-error.so.0 (0x00007f3b585fc000)
        libz.so.1 => /lib64/libz.so.1 (0x00007f3b583e5000)
        liblzma.so.5 => /lib64/liblzma.so.5 (0x00007f3b581c0000)
        libltdl.so.7 => /lib64/libltdl.so.7 (0x00007f3b57fb6000)
        libOpenIPMIutils.so.0 => /lib64/libOpenIPMIutils.so.0 (0x00007f3b57dac000)
        libgdbm.so.4 => /lib64/libgdbm.so.4 (0x00007f3b57ba3000)
        libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f3b57957000)
        libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f3b57671000)
        libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f3b5746d000)
        libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f3b5723b000)
        libsasl2.so.3 => /lib64/libsasl2.so.3 (0x00007f3b5701d000)
        libssl3.so => /lib64/libssl3.so (0x00007f3b56ddb000)
        libsmime3.so => /lib64/libsmime3.so (0x00007f3b56bb4000)
        libnss3.so => /lib64/libnss3.so (0x00007f3b5688d000)
        libnssutil3.so => /lib64/libnssutil3.so (0x00007f3b56661000)
        libplds4.so => /lib64/libplds4.so (0x00007f3b5645d000)
        libplc4.so => /lib64/libplc4.so (0x00007f3b56257000)
        libnspr4.so => /lib64/libnspr4.so (0x00007f3b56019000)
        libidn.so.11 => /lib64/libidn.so.11 (0x00007f3b55de6000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f3b5d25d000)
        libp11-kit.so.0 => /lib64/libp11-kit.so.0 (0x00007f3b55b9f000)
        libtspi.so.1 => /lib64/libtspi.so.1 (0x00007f3b5592e000)
        libtasn1.so.6 => /lib64/libtasn1.so.6 (0x00007f3b55719000)
        libnettle.so.4 => /lib64/libnettle.so.4 (0x00007f3b554e8000)
        libhogweed.so.2 => /lib64/libhogweed.so.2 (0x00007f3b552c1000)
        libgmp.so.10 => /lib64/libgmp.so.10 (0x00007f3b55049000)
        libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f3b54e3a000)
        libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f3b54c35000)
        libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f3b549fe000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f3b547f5000)
        libffi.so.6 => /lib64/libffi.so.6 (0x00007f3b545ed000)
        libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f3b543c5000)
        libfreebl3.so => /lib64/libfreebl3.so (0x00007f3b541c2000)
Comment by Vladislavs Sokurenko [ 2017 Oct 27 ]

Could you please increase log level of poller and attach the log when it crashes.

zabbix_server -R log_level_increase="poller"
Comment by Vladislavs Sokurenko [ 2017 Oct 27 ]

Thank you for your report, crash is a duplicate of ZBX-10962.

Comment by CHRETIEN Landry [ 2017 Oct 27 ]

Hello,

Ok for the duplicate entrie, but 2 problems appear at the same time, the Crash of the server, and slow queries,

Could you re-open this ticket regarding the initial problem ?

Comment by Valdis Murzins [ 2017 Oct 27 ]

Can you, please, turn on debug mode and show it's result on Monitoring -> Dashboard problems widget and Monitoring -> Problems?
You may want to hide some sensitive data in it.

Comment by Valdis Murzins [ 2017 Oct 27 ]

Also - can your problem be related to ZBX-12821 ?

Comment by CHRETIEN Landry [ 2017 Oct 27 ]

I've just added request frontend logs,

Regarding the issue that you mentionned, i didn't try to stay on a long time on the problem view, but after the migration, the problem widget was included in the default dashboard, so all users displayed this widget after login generating a lot of slow query.
After removing the widget from this default dashboard and stop/start the server, server idle comes back, but if i try to add the widget on another dashbord, or via the problems view, the query is still very long to get the result, around 20 seconds for 2 hostgroups displayed, theses 2 hostgroups should contain around 200 hosts.

Comment by Rostislav Palivoda [ 2017 Oct 27 ]

Thank you, files received and removed from the ticket. We will review logs and keep you update.

Comment by Valdis Murzins [ 2017 Oct 30 ]

Hello landry41,

Can you, please, tell us how many entries do you have in your problems table?
Maybe you have selected PROBLEM event generation mode as Multiple for your triggers without providing recovery expression to close them, as a result problem events may have overflowed your database.

By judging your installation size, Zabbix should be able to handle such.

Comment by CHRETIEN Landry [ 2017 Oct 30 ]

Hello,

The problem table had :

mysql> select count(*) from problem;
+----------+
| count(*) |
+----------+
|    77432 |
+----------+
1 row in set (0.03 sec)
Comment by Vladislavs Sokurenko [ 2017 Oct 30 ]

you could try performing explain on the query that is slow.

Please also do

show create table problem;
Comment by CHRETIEN Landry [ 2017 Oct 30 ] 
mysql> show create table problem;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| problem | CREATE TABLE `problem` (
  `eventid` bigint(20) unsigned NOT NULL,
  `source` int(11) NOT NULL DEFAULT '0',
  `object` int(11) NOT NULL DEFAULT '0',
  `objectid` bigint(20) unsigned NOT NULL DEFAULT '0',
  `clock` int(11) NOT NULL DEFAULT '0',
  `ns` int(11) NOT NULL DEFAULT '0',
  `r_eventid` bigint(20) unsigned DEFAULT NULL,
  `r_clock` int(11) NOT NULL DEFAULT '0',
  `r_ns` int(11) NOT NULL DEFAULT '0',
  `correlationid` bigint(20) unsigned DEFAULT NULL,
  `userid` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`eventid`),
  KEY `problem_1` (`source`,`object`,`objectid`),
  KEY `problem_2` (`r_clock`),
  KEY `c_problem_2` (`r_eventid`),
  CONSTRAINT `c_problem_1` FOREIGN KEY (`eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE,
  CONSTRAINT `c_problem_2` FOREIGN KEY (`r_eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

and for the explain :

The following request send back 64 rows in 4s

mysql> EXPLAIN SELECT DISTINCT p.eventid,p.objectid,p.clock,p.ns FROM problem p,functions f,items i,hosts_groups hg,triggers t WHERE p.source='0' AND p.object='0' AND NOT EXISTS (SELECT NULL FROM functions f,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid IN ('33','34','37','46','75') WHERE p.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MAX(permission)<2 OR MIN(permission) IS NULL OR MIN(permission)=0) AND p.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hg.hostid AND hg.groupid IN ('73','88','105','218') AND p.objectid=t.triggerid AND t.priority BETWEEN '0' AND '5' AND p.r_eventid IS NULL ORDER BY p.eventid DESC;
+----+--------------------+-------+------------+--------+-------------------------------+----------------+---------+---------------------+-------+----------+---------------------------------------------------------------------+
| id | select_type        | table | partitions | type   | possible_keys                 | key            | key_len | ref                 | rows  | filtered | Extra                                                               |
+----+--------------------+-------+------------+--------+-------------------------------+----------------+---------+---------------------+-------+----------+---------------------------------------------------------------------+
|  1 | PRIMARY            | p     | NULL       | ref    | problem_1,c_problem_2         | problem_1      | 8       | const,const         | 38382 |    50.00 | Using index condition; Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | t     | NULL       | eq_ref | PRIMARY                       | PRIMARY        | 8       | Zabbix1.p.objectid  |     1 |    11.11 | Using where; Distinct                                               |
|  1 | PRIMARY            | f     | NULL       | ref    | functions_1,functions_2       | functions_1    | 8       | Zabbix1.p.objectid  |     1 |   100.00 | Distinct                                                            |
|  1 | PRIMARY            | i     | NULL       | eq_ref | PRIMARY,items_1               | PRIMARY        | 8       | Zabbix1.f.itemid    |     1 |   100.00 | Distinct                                                            |
|  1 | PRIMARY            | hg    | NULL       | ref    | hosts_groups_1,hosts_groups_2 | hosts_groups_1 | 8       | Zabbix1.i.hostid    |     1 |    13.67 | Using where; Using index; Distinct                                  |
|  2 | DEPENDENT SUBQUERY | f     | NULL       | ref    | functions_1,functions_2       | functions_1    | 8       | Zabbix1.p.objectid  |     1 |   100.00 | Using temporary; Using filesort                                     |
|  2 | DEPENDENT SUBQUERY | i     | NULL       | eq_ref | PRIMARY,items_1               | PRIMARY        | 8       | Zabbix1.f.itemid    |     1 |   100.00 | NULL                                                                |
|  2 | DEPENDENT SUBQUERY | hgg   | NULL       | ref    | hosts_groups_1                | hosts_groups_1 | 8       | Zabbix1.i.hostid    |     1 |   100.00 | Using index                                                         |
|  2 | DEPENDENT SUBQUERY | r     | NULL       | ref    | rights_1,rights_2             | rights_2       | 8       | Zabbix1.hgg.groupid |     4 |   100.00 | Using where                                                         |
+----+--------------------+-------+------------+--------+-------------------------------+----------------+---------+---------------------+-------+----------+---------------------------------------------------------------------+
9 rows in set, 2 warnings (0.00 sec)
Comment by Valdis Murzins [ 2017 Nov 14 ]

Hello landry41,

We want to recreate environment of your size for testing.
Can you, please, provide also number of entries in these tables: functions, items, host_groups?

Thank you in advance.

Comment by CHRETIEN Landry [ 2017 Dec 19 ]

Hello,
Sorry for the delay,
Please find requested informations :

mysql> select count(*) from functions;
+----------+
| count(*) |
+----------+
|    80046 |
+----------+
1 row in set (0.02 sec)

mysql> select count(*) from items;
+----------+
| count(*) |
+----------+
|   171072 |
+----------+
1 row in set (0.12 sec)

mysql> select count(*) from hosts_groups;
+----------+
| count(*) |
+----------+
|     4452 |
+----------+
1 row in set (0.00 sec)
Comment by Alexander Vladishev [ 2018 Jan 04 ]

problem.get can be fixed with ZBX-12133.

Comment by CHRETIEN Landry [ 2018 Apr 05 ]

Hello,

Some slow queries are still present on our architecture updated in 3.4.6 some month ago, always on this kind of request : 

| 4743846 | Zabbix1 | XXXXXXXXXXXXXXXX | Zabbix1 | Query | 4 | Sending data | SELECT DISTINCT p.eventid,p.objectid,p.clock,p.ns FROM problem p,functions f,items i,hosts_groups hg,triggers t WHERE p.source='0' AND p.object='0' AND NOT EXISTS (SELECT NULL FROM functions f,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid IN ('42','43','48','51','95') WHERE p.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MAX(permission)<2 OR MIN(permission) IS NULL OR MIN(permission)=0) AND p.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hg.hostid AND hg.groupid IN ('88','201','218') AND p.objectid=t.triggerid AND t.priority IN ('0','1','3','4','5') AND (p.r_eventid IS NULL OR p.r_clock>1522930792) AND p.eventid<='30689189' ORDER BY p.eventid DESC LIMIT 1001 |

| 4744569 | Zabbix1 | XXXXXXXXXXXXXXXX | Zabbix1 | Query | 7 | Sending data | SELECT DISTINCT p.eventid,p.objectid,p.clock,p.ns FROM problem p,functions f,items i,hosts_groups hg WHERE p.source='0' AND p.object='0' AND NOT EXISTS (SELECT NULL FROM functions f,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid IN ('13','28','31') WHERE p.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MAX(permission)<2 OR MIN(permission) IS NULL OR MIN(permission)=0) AND p.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hg.hostid AND hg.groupid IN ('17','18','56','231') AND (p.r_eventid IS NULL OR p.r_clock>1522930789) ORDER BY p.eventid DESC LIMIT 1001

Theses slow queries can cause some locks on the Db (we experience it this day ) :

141624:20180405:083350.512 [Z3005] query failed: [1205] Lock wait timeout exceeded; try restarting transaction [insert into history_uint (itemid,clock,ns,value) values (96157,1522909932,846668199,1050),(302870,1522909934,117422075,15299600384),(303317,1522909934,124652513,1),(153349,1522909934,260807960,433),(153368,1522909934,260880295,6326255616),(153369,1522909934,260905163,4276305920),(153372,1522909934

Could you provide a feedback ?

Comment by psychomoise [ 2018 Jul 05 ]

check what I have found as a solution in ZBX-13789

Comment by CHRETIEN Landry [ 2018 Aug 21 ]

Tested, and for me it's better with your solution !  

Comment by Alexander Vladishev [ 2024 Jan 23 ]

Fixed as part of ZBXNEXT-5878.

Generated at Sat Apr 20 01:45:50 EEST 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.