Zabbix Postgresql Locking

[f.koch]

Try to set DBSyncer processes to 1 and try again, this should work

[dale]

2.0.6 did not fix it

I updated to the EPEL build of zabbix20-server-2.0.6-1.el6.x86_64 from http://koji.fedoraproject.org/koji/b...buildID=413466

I tried adding a 5th item to the CPU utilization graph in my Linux template. It produced a deadlock in the postgres log, but the update did work and the UI reported success and the graph was updated.

I tried to add a 6th item to the graph and it blew up as before.

I lowered the DBSyncer to 1 and tried to save the 6th item again. It blew up as before. UI output.

    * pg_query(): Query failed: ERROR: deadlock detected DETAIL: Process 17054 waits for ShareLock on transaction 157322419; blocked by process 16935. Process 16935 waits for ShareLock on transaction 157322415; blocked by process 17054. HINT: See server log for query details. CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."items" x WHERE "itemid" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x" [include/db.inc.php:511]
    * Error in query [INSERT INTO graphs_items (gitemid,graphid,itemid,drawtype,sortorder,color,yaxisside,calc_fnc,type) VALUES ('26263','6999','82667','0','4','990000','0','2','0')] [ERROR: deadlock detected DETAIL: Process 17054 waits for ShareLock on transaction 157322419; blocked by process 16935. Process 16935 waits for ShareLock on transaction 157322415; blocked by process 17054. HINT: See server log for query details. CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."items" x WHERE "itemid" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x"]
    * SQL statement execution has failed "INSERT INTO graphs_items (gitemid,graphid,itemid,drawtype,sortorder,color,yaxisside,calc_fnc,type) VALUES ('26263','6999','82667','0','4','990000','0','2','0')".

I'm using proxies for most but not all hosts yet. Thanks for the suggestion of stopping the server while manipulating the templates. Obviously it is less than ideal.

[f.koch]

please open a bug report

support.zabbix.com

[dale]

bug opened

Done. Thanks

https://support.zabbix.com/browse/ZBX-6532

[Colttt]

Zabbix actually has problems with lock contention in the database that prevents it from scaling. Databases are designed to ensure data consistency and as a result implement various algorithms to ensure that no two transactions can update a row simultaneously. Fundamentally, this is where Zabbix runs into problems as you have noticed with lock contention (share locks in postgresql).

MySQL or Postgresql, eventually you will hit a limit in terms of how far Zabbix can scale. We moved to MySQL, but looking at the innodb engine status I can see that the mutexes and spin waits are off the charts. Problem areas for Zabbix:

1. IDS table storing the next unique identifier in a table is a bad idea. I often see the error "Something impossible just happened" but it happens a lot, so it is not uncommon.

2. Somewhere in the write cache, zabbix needs to look at how history data is being inserted and rather depend on the database to figure out what to do, and need to sort an organize updates so that no two transactions are updating the same row in more than one transaction. Something like serializing the updates.

But this does not answer your question on how to fix the problem. If you can eliminate housekeeper, that will help. If you can relax some the databases consistency controls at the risk of losing some data, you are better off.

the zabbix-devs are resistant to advice (in german: beratungsresistent).. a postgresql-dev give some advice, but it was not accepted..

[fauust]

Hi!
Is this deadlock problem still affecting recent zabbix version?

PostgreSQL 10
Zabbix 4.0.12
DBSyncers 10 (tested: 4,10,20,30)
VPS: 1,2k
Hosts: 4k
Items: 500k
Proxies: 6

Mass update (clone trigger on a main template) is generating deadlocks and takes about 6min (GUI is not available during those locks) :

2019-12-11 14:52:58 CET [57592-8] zabbix@zab00 LOG:  duration: 550473.006 ms  statement: SELECT nextid FROM ids WHERE table_name='auditlog' AND field_name='auditid' FOR UPDATE
2019-12-11 14:52:58 CET [61761-6] zabbix@zab00 LOG:  duration: 546181.233 ms  statement: SELECT nextid FROM ids WHERE table_name='auditlog' AND field_name='auditid' FOR UPDATE
2019-12-11 14:52:59 CET [61786-6] zabbix@zab00 LOG:  duration: 538197.306 ms  statement: SELECT nextid FROM ids WHERE table_name='auditlog' AND field_name='auditid' FOR UPDATE
2019-12-11 14:52:59 CET [61792-6] zabbix@zab00 LOG:  duration: 532203.918 ms  statement: SELECT nextid FROM ids WHERE table_name='auditlog' AND field_name='auditid' FOR UPDATE
2019-12-11 14:52:59 CET [61805-6] zabbix@zab00 LOG:  duration: 523021.669 ms  statement: SELECT nextid FROM ids WHERE table_name='auditlog' AND field_name='auditid' FOR UPDATE
2019-12-11 14:52:59 CET [61670-6] zabbix@zab00 LOG:  duration: 554782.464 ms  statement: SELECT nextid FROM ids WHERE table_name='auditlog' AND field_name='auditid' FOR UPDATE
2019-12-11 14:52:59 CET [61711-6] zabbix@zab00 LOG:  duration: 549088.712 ms  statement: SELECT nextid FROM ids WHERE table_name='auditlog' AND field_name='auditid' FOR UPDATE
2019-12-11 14:52:59 CET [61728-6] zabbix@zab00 LOG:  duration: 541728.489 ms  statement: SELECT nextid FROM ids WHERE table_name='auditlog' AND field_name='auditid' FOR UPDATE
2019-12-11 14:52:59 CET [61815-6] zabbix@zab00 LOG:  duration: 523359.322 ms  statement: SELECT nextid FROM ids WHERE table_name='auditlog' AND field_name='auditid' FOR UPDATE
2019-12-11 14:52:59 CET [61770-6] zabbix@zab00 LOG:  duration: 534726.895 ms  statement: SELECT nextid FROM ids WHERE table_name='auditlog' AND field_name='auditid' FOR UPDATE
2019-12-11 14:52:59 CET [61129-6] zabbix@zab00 LOG:  duration: 550858.283 ms  statement: SELECT nextid FROM ids WHERE table_name='auditlog' AND field_name='auditid' FOR UPDATE
2019-12-11 14:53:00 CET [61810-6] zabbix@zab00 LOG:  duration: 524286.149 ms  statement: SELECT nextid FROM ids WHERE table_name='auditlog' AND field_name='auditid' FOR UPDATE
2019-12-11 14:53:00 CET [61777-6] zabbix@zab00 LOG:  duration: 540639.282 ms  statement: SELECT nextid FROM ids WHERE table_name='auditlog' AND field_name='auditid' FOR UPDATE
2019-12-11 14:53:00 CET [59346-8] zabbix@zab00 LOG:  duration: 557099.523 ms  statement: SELECT nextid FROM ids WHERE table_name='auditlog' AND field_name='auditid' FOR UPDATE
2019-12-11 14:53:00 CET [61807-6] zabbix@zab00 LOG:  duration: 523729.230 ms  statement: SELECT nextid FROM ids WHERE table_name='auditlog' AND field_name='auditid' FOR UPDATE
2019-12-11 14:53:00 CET [61713-6] zabbix@zab00 LOG:  duration: 534010.452 ms  statement: SELECT nextid FROM ids WHERE table_name='auditlog' AND field_name='auditid' FOR UPDATE
2019-12-11 14:53:00 CET [61806-6] zabbix@zab00 LOG:  duration: 529893.634 ms  statement: SELECT nextid FROM ids WHERE table_name='auditlog' AND field_name='auditid' FOR UPDATE
2019-12-11 14:53:00 CET [61793-6] zabbix@zab00 LOG:  duration: 530599.147 ms  statement: SELECT nextid FROM ids WHERE table_name='auditlog' AND field_name='auditid' FOR UPDATE
2019-12-11 14:53:00 CET [61738-6] zabbix@zab00 LOG:  duration: 548308.214 ms  statement: SELECT nextid FROM ids WHERE table_name='auditlog' AND field_name='auditid' FOR UPDATE

Any help would be really appreciated.
Regards,
Faustin