'ids' table very busy

[cyberklin]

we had the same issue with zabbix 1.6.5 on oracle10.
with number of pollers > 30 we had a deadlock in DBget_maxid

our enviroment:

Number of items (monitored/disabled)	23064(16133/5730)
Number of triggers (enabled/disabled)	8861(7986/875)

running on solaris10, oracle10.

we have a patch for this problem, but it`s only for oracle:

diff -cur zabbix-1.6.5/src/libs/zbxdbhigh/db.c zabbix-1.6.5p/src/libs/zbxdbhigh/db.c
--- zabbix-1.6.5/src/libs/zbxdbhigh/db.c        С▒~@ и▒~Nл▒~O 29 11:14:53 2009
+++ zabbix-1.6.5p/src/libs/zbxdbhigh/db.c       Ч▒~B и▒~Nл▒~O 30 03:57:21 2009
@@ -2002,7 +2002,11 @@
        }

        do {
+#ifdef HAVE_ORACLE
+               result = DBselect("select nextid from ids where nodeid=%d and table_name='%s' and field_name='%s' for update",
+#else
                result = DBselect("select nextid from ids where nodeid=%d and table_name='%s' and field_name='%s'",
+#endif
                        nodeid,
                        tablename,
                        fieldname);
@@ -2083,6 +2087,9 @@
                                        found = SUCCEED;
                        }
                }
+               // ARSTEL patch
+               DBcommit();
+               // end
        }
        while(FAIL == found);

[benc]

MySQL INNODB Fix

Hey all,

For anyone else struggling with the update ids issue with mysql innodb here is a fix/workaround which is working for us.

Simply change the storage engine of the ids table from innodb to myisam:

ALTER TABLE ids ENGINE = MYISAM;

This should result in a much faster update time on the ids table.
Also note that mysql is smart enough not to break anything when changing storage engines on a table.

Ben

[joeuhl]

Still a problem in 1.8.4

This is still a problem in 1.8.4. My install will run for days without any issues (and very little load on the server) before entering massive deadlock state and it is always hundreds of these "select nextid" queries. I've seen it for nextids on auditlog and for events. The only fix is to restart Postgres and zabbix-server. I've got a couple hundred hosts and collect 95 events/sec but the machine I have my DB on should be able to handle thousands of transactions/sec.

I've modified the code to skip inserting into auditlog and am about to add the FOR UPDATE clause mentioned above to try and fix it more generally.

Is this bug being tracked somewhere?

It seems like this logic should be using sequences in Postgres, they are made specifically for this purpose.