deadlocks preventing updates to triggers, etc

[cerasus]

Hi,

I'm running into a very limiting issue where updating a trigger or detaching a template from a large number of hosts is resulting in a deadlock. I've upgraded to 4.4.7 but still experiencing this issue.

A example error that resulted from updating a trigger condition is below.

Cannot update trigger pg_query(): Query failed: ERROR: deadlock detected DETAIL: Process 1420 waits for ShareLock on transaction 2078016030; blocked by process 12618. Process 12618 waits for ShareLock on transaction 2078014572; blocked by process 1420. HINT: See server log for query details. CONTEXT: while updating tuple (53035,255) in relation "triggers" [triggers.php:395 → CFrontendApiWrapper->update() → CApiWrapper->__call() → CFrontendApiWrapper->callMethod() → CApiWrapper->callMethod() → CFrontendApiWrapper->callClientMethod() → CLocalApiClient->callMethod() → call_user_func_array() → CTrigger->update() → CTriggerGeneral->inherit() → CTriggerGeneral->inheritOnHost() → CTriggerGeneral->updateReal() → DB::update() → DBexecute() → pg_query() in include/db.inc.php:487] Error in query [UPDATE triggers SET expression='{10591717}',recovery_expression='',des cription='{HOST.NAME}: No iDrac data for 30 minutes' WHERE triggerid='3175630'] [ERROR: deadlock detected DETAIL: Process 1420 waits for ShareLock on transaction 2078016030; blocked by process 12618. Process 12618 waits for ShareLock on transaction 2078014572; blocked by process 1420. HINT: See server log for query details. CONTEXT: while updating tuple (53035,255) in relation "triggers"] SQL statement execution has failed "UPDATE triggers SET expression='{10591717}',recovery_expression='',des cription='{HOST.NAME}: No data for 30 minutes' WHERE triggerid='3175630'".

We do have a large number of hosts. Is there a known issue and most importantly a workaround?

Thanks for any tips.

[gofree]

just from curiosity whats "large number" ?

in my opininon ( not a db expert ) you need to tweak your db installation ( whats your db, cpu, ram, on separate server ) or add more power to it - basically you configure your DB from web browser and zabbix server takes this configuration in to account

[cerasus]

We were able to work around the problem with changing the trigger by disabling it first, adjusting it and then re-enabling it. I doubt it's a resource problem. The db is on a powerful machine and professionally configured.

[newlandk]

I've been experiencing the same issue and would like to better understand what the root cause is.

The server is happily running with 37K hosts, 40k triggers, and ~2K VPS when no template changes are occurring.

The environment I'm working with has a Postgres DB instance that is separate from the install with 8 CPU and 16GB of RAM and its utilization is running around 3% CPU, 5GB of RAM and no disk or CPU IO wait. The server and a few proxies have a similar configuration and are all within standard resource utilization.

I've noticed even when disabling a trigger or performing any task on a template that has the potential to impact thousands of hosts, the DB will increment locks and the UI becomes inaccessible. Another impact when this occurs is that history syncing from the server appears to get backed up and causes the system to run out of history write cache (currently set to 32MB) presumably since the DB is not accessible for the write tasks due to the locks.

Looking into the long-running queries you will generally many "SELECT nextid from ids WHERE table_name='auditlog' AND field_name='auditid' FOR UPDATE" and these will not go away until I turn down the processes for the web UI, kill the processes and bring the UI back up.

Thank you for reading and consideration!