Nagios XI GUI showing SQL errors

[jwelch]

Our Nagios XI (2014R2.7) started showing SQL errors on the GUI this morning.

Code: Select all

SQL: SQL Error [nagiosxi] : ERROR: deadlock detected DETAIL: Process 11570 waits for ShareLock on transaction 186147139; blocked by process 7988. Process 7988 waits for ShareLock on transaction 186147054; blocked by process 2196. Process 2196 waits for ShareLock on transaction 186147126; blocked by process 11570. HINT: See server log for query details.

I tried repairing the mysql db, but no joy.

I found a hit on the forum that it might be postgresql so I tried vacuuming it. Still no joy.
I also bumped up the max_connecions in /var/lib/pgsql/data/postgresql.conf from 100 to 1000 and restarting the postgresql service. No change.

The entries in /var/lib/pgsql/data/pg_log/postgresql-Wed.log look like:

Code: Select all

STATEMENT:  UPDATE xi_options SET value='101' WHERE name='im_component_version'
ERROR:  deadlock detected
DETAIL:  Process 23839 waits for ExclusiveLock on tuple (24,75) of relation 930881 of database 16385; blocked by process 32759.
        Process 32759 waits for ShareLock on transaction 186167664; blocked by process 25034.
        Process 25034 waits for ShareLock on transaction 186167608; blocked by process 23839.
        Process 23839: UPDATE xi_options SET value='101' WHERE name='im_component_version'
        Process 32759: UPDATE xi_options SET value='101' WHERE name='im_component_version'
        Process 25034: UPDATE xi_options SET value='101' WHERE name='im_component_version'
HINT:  See server log for query details.

[abrist]

It looks like you had multiple processes try to lock the im_component_version row.
Does restarting httpd and then postgres resolve this issue?

Code: Select all

service httpd restart
service postgresql restart

[jwelch]

briefly, but the problem comes back in a minute or two.

[jwelch]

I'm not familiar with that table. Does it relate to the number of connected users, number of checks, or some other resource?

BTW, is there an easy way to count the number of concurrent users? I can't use netstat since a significant number of users
may be sharing an IP at any given time (VPN).

[abrist]

Does it relate to the number of connected users, number of checks, or some other resource?

The xi_options table holds most of the options that are configurable in the XI UI.

BTW, is there an easy way to count the number of concurrent users? I can't use netstat since a significant number of users
may be sharing an IP at any given time (VPN).

Not really, though you could watch the quantity of apache requests over time. See:
https://exchange.nagios.org/index.php?o ... %20request
Or you could write your plugin to deal with the output from apachetop:
https://code.google.com/p/apachetop/

[abrist]

Oh, and concerning your issue with the deadlocked postgres table. Could you open a ticket by emailing: [email protected] ?
I would like to move this incident to a remote session.

[jwelch]

Email sent. I referenced this topic to save typing/cutting/pasting/knitting/stitching/etc..

[tmcdonald]

Email sent. I referenced this topic to save typing/cutting/pasting/knitting/stitching/etc..

We also accept crotchet!

I'll close this up now.