Database Maintenance (dbmaint) stale (802 seconds old)

This support forum board is for support questions relating to Nagios XI, our flagship commercial network monitoring solution.
Post Reply
netsol_tony
Posts: 3
Joined: Wed Aug 03, 2022 11:51 pm

Database Maintenance (dbmaint) stale (802 seconds old)

Post by netsol_tony »

HI All,

Hoping to get a few answers about what might be causing the alert in the title, it's been occurring multiple times a day for a number of months. Both the system running the check and the sytem being checked are on 5.9.1.

Watching the dbmaint.log most of the time the job completes in seconds, but while the issue occurs it appears to get stuck at SQL: OPTIMIZE TABLE nagios_logentries for a number of minutes:

Code: Select all

Repair Complete: Removing Lock File
CREATING: /usr/local/nagiosxi/var/dbmaint.lock
CLEANING ndoutils TABLE 'commenthistory'...
SQL: DELETE FROM nagios_commenthistory WHERE entry_time < FROM_UNIXTIME(1607142302)
CLEANING ndoutils TABLE 'processevents'...
SQL: DELETE FROM nagios_processevents WHERE event_time < FROM_UNIXTIME(1638678302)
CLEANING ndoutils TABLE 'externalcommands'...
SQL: DELETE FROM nagios_externalcommands WHERE entry_time < FROM_UNIXTIME(1669609502)
CLEANING ndoutils TABLE 'logentries'...
SQL: DELETE FROM nagios_logentries WHERE logentry_time < FROM_UNIXTIME(1662438302)
CLEANING ndoutils TABLE 'notifications'...
SQL: DELETE FROM nagios_notifications WHERE start_time < FROM_UNIXTIME(1662438302)
CLEANING ndoutils TABLE 'contactnotifications'...
SQL: DELETE FROM nagios_contactnotifications WHERE start_time < FROM_UNIXTIME(1662438302)
CLEANING ndoutils TABLE 'contactnotificationmethods'...
SQL: DELETE FROM nagios_contactnotificationmethods WHERE start_time < FROM_UNIXTIME(1662438302)
CLEANING ndoutils TABLE 'statehistory'...
SQL: DELETE FROM nagios_statehistory WHERE state_time < FROM_UNIXTIME(1607142302)
CLEANING ndoutils TABLE 'timedevents'...
SQL: DELETE FROM nagios_timedevents WHERE event_time < FROM_UNIXTIME(1670214002)
CLEANING ndoutils TABLE 'systemcommands'...
SQL: DELETE FROM nagios_systemcommands WHERE start_time < FROM_UNIXTIME(1670214002)
CLEANING ndoutils TABLE 'servicechecks'...
SQL: DELETE FROM nagios_servicechecks WHERE start_time < FROM_UNIXTIME(1670214002)
CLEANING ndoutils TABLE 'hostchecks'...
SQL: DELETE FROM nagios_hostchecks WHERE start_time < FROM_UNIXTIME(1670214002)
CLEANING ndoutils TABLE 'eventhandlers'...
SQL: DELETE FROM nagios_eventhandlers WHERE start_time < FROM_UNIXTIME(1670214002)
TIME TO OPTIMIZE
LASTOPT:  1670210401
INTERVAL: 60
NOW:      1670214302
OPTTIME:  1670214001
OPTIMIZING NDOUTILS TABLE: nagios_acknowledgements
SQL: OPTIMIZE TABLE nagios_acknowledgements
OPTIMIZING NDOUTILS TABLE: nagios_commands
SQL: OPTIMIZE TABLE nagios_commands
OPTIMIZING NDOUTILS TABLE: nagios_commenthistory
SQL: OPTIMIZE TABLE nagios_commenthistory
OPTIMIZING NDOUTILS TABLE: nagios_comments
SQL: OPTIMIZE TABLE nagios_comments
OPTIMIZING NDOUTILS TABLE: nagios_configfiles
SQL: OPTIMIZE TABLE nagios_configfiles
OPTIMIZING NDOUTILS TABLE: nagios_configfilevariables
SQL: OPTIMIZE TABLE nagios_configfilevariables
OPTIMIZING NDOUTILS TABLE: nagios_conninfo
SQL: OPTIMIZE TABLE nagios_conninfo
OPTIMIZING NDOUTILS TABLE: nagios_contact_addresses
SQL: OPTIMIZE TABLE nagios_contact_addresses
OPTIMIZING NDOUTILS TABLE: nagios_contact_notificationcommands
SQL: OPTIMIZE TABLE nagios_contact_notificationcommands
OPTIMIZING NDOUTILS TABLE: nagios_contactgroup_members
SQL: OPTIMIZE TABLE nagios_contactgroup_members
OPTIMIZING NDOUTILS TABLE: nagios_contactgroups
SQL: OPTIMIZE TABLE nagios_contactgroups
OPTIMIZING NDOUTILS TABLE: nagios_contactnotificationmethods
SQL: OPTIMIZE TABLE nagios_contactnotificationmethods
OPTIMIZING NDOUTILS TABLE: nagios_contactnotifications
SQL: OPTIMIZE TABLE nagios_contactnotifications
OPTIMIZING NDOUTILS TABLE: nagios_contacts
SQL: OPTIMIZE TABLE nagios_contacts
OPTIMIZING NDOUTILS TABLE: nagios_contactstatus
SQL: OPTIMIZE TABLE nagios_contactstatus
OPTIMIZING NDOUTILS TABLE: nagios_customvariables
SQL: OPTIMIZE TABLE nagios_customvariables
OPTIMIZING NDOUTILS TABLE: nagios_customvariablestatus
SQL: OPTIMIZE TABLE nagios_customvariablestatus
OPTIMIZING NDOUTILS TABLE: nagios_dbversion
SQL: OPTIMIZE TABLE nagios_dbversion
OPTIMIZING NDOUTILS TABLE: nagios_downtimehistory
SQL: OPTIMIZE TABLE nagios_downtimehistory
OPTIMIZING NDOUTILS TABLE: nagios_eventhandlers
SQL: OPTIMIZE TABLE nagios_eventhandlers
OPTIMIZING NDOUTILS TABLE: nagios_externalcommands
SQL: OPTIMIZE TABLE nagios_externalcommands
OPTIMIZING NDOUTILS TABLE: nagios_flappinghistory
SQL: OPTIMIZE TABLE nagios_flappinghistory
OPTIMIZING NDOUTILS TABLE: nagios_host_contactgroups
SQL: OPTIMIZE TABLE nagios_host_contactgroups
OPTIMIZING NDOUTILS TABLE: nagios_host_contacts
SQL: OPTIMIZE TABLE nagios_host_contacts
OPTIMIZING NDOUTILS TABLE: nagios_host_parenthosts
SQL: OPTIMIZE TABLE nagios_host_parenthosts
OPTIMIZING NDOUTILS TABLE: nagios_hostchecks
SQL: OPTIMIZE TABLE nagios_hostchecks
OPTIMIZING NDOUTILS TABLE: nagios_hostdependencies
SQL: OPTIMIZE TABLE nagios_hostdependencies
OPTIMIZING NDOUTILS TABLE: nagios_hostescalation_contactgroups
SQL: OPTIMIZE TABLE nagios_hostescalation_contactgroups
OPTIMIZING NDOUTILS TABLE: nagios_hostescalation_contacts
SQL: OPTIMIZE TABLE nagios_hostescalation_contacts
OPTIMIZING NDOUTILS TABLE: nagios_hostescalations
SQL: OPTIMIZE TABLE nagios_hostescalations
OPTIMIZING NDOUTILS TABLE: nagios_hostgroup_members
SQL: OPTIMIZE TABLE nagios_hostgroup_members
OPTIMIZING NDOUTILS TABLE: nagios_hostgroups
SQL: OPTIMIZE TABLE nagios_hostgroups
OPTIMIZING NDOUTILS TABLE: nagios_hosts
SQL: OPTIMIZE TABLE nagios_hosts
OPTIMIZING NDOUTILS TABLE: nagios_hoststatus
SQL: OPTIMIZE TABLE nagios_hoststatus
OPTIMIZING NDOUTILS TABLE: nagios_instances
SQL: OPTIMIZE TABLE nagios_instances
OPTIMIZING NDOUTILS TABLE: nagios_logentries
SQL: OPTIMIZE TABLE nagios_logentries

################### ^^^ Above took about ~4 Minutes #########################

LOCKFILE '/usr/local/nagiosxi/var/dbmaint.lock' EXISTS - EXITING!
OPTIMIZING NDOUTILS TABLE: nagios_notifications
SQL: OPTIMIZE TABLE nagios_notifications
OPTIMIZING NDOUTILS TABLE: nagios_objects
SQL: OPTIMIZE TABLE nagios_objects
OPTIMIZING NDOUTILS TABLE: nagios_processevents
SQL: OPTIMIZE TABLE nagios_processevents
OPTIMIZING NDOUTILS TABLE: nagios_programstatus
SQL: OPTIMIZE TABLE nagios_programstatus
OPTIMIZING NDOUTILS TABLE: nagios_runtimevariables
SQL: OPTIMIZE TABLE nagios_runtimevariables
OPTIMIZING NDOUTILS TABLE: nagios_scheduleddowntime
SQL: OPTIMIZE TABLE nagios_scheduleddowntime
OPTIMIZING NDOUTILS TABLE: nagios_service_contactgroups
SQL: OPTIMIZE TABLE nagios_service_contactgroups
OPTIMIZING NDOUTILS TABLE: nagios_service_contacts
SQL: OPTIMIZE TABLE nagios_service_contacts
OPTIMIZING NDOUTILS TABLE: nagios_servicechecks
SQL: OPTIMIZE TABLE nagios_servicechecks
OPTIMIZING NDOUTILS TABLE: nagios_servicedependencies
SQL: OPTIMIZE TABLE nagios_servicedependencies
OPTIMIZING NDOUTILS TABLE: nagios_serviceescalation_contactgroups
SQL: OPTIMIZE TABLE nagios_serviceescalation_contactgroups
OPTIMIZING NDOUTILS TABLE: nagios_serviceescalation_contacts
SQL: OPTIMIZE TABLE nagios_serviceescalation_contacts
OPTIMIZING NDOUTILS TABLE: nagios_serviceescalations
SQL: OPTIMIZE TABLE nagios_serviceescalations
OPTIMIZING NDOUTILS TABLE: nagios_servicegroup_members
SQL: OPTIMIZE TABLE nagios_servicegroup_members
OPTIMIZING NDOUTILS TABLE: nagios_servicegroups
SQL: OPTIMIZE TABLE nagios_servicegroups
OPTIMIZING NDOUTILS TABLE: nagios_services
SQL: OPTIMIZE TABLE nagios_services
OPTIMIZING NDOUTILS TABLE: nagios_servicestatus
SQL: OPTIMIZE TABLE nagios_servicestatus
OPTIMIZING NDOUTILS TABLE: nagios_statehistory
SQL: OPTIMIZE TABLE nagios_statehistory
OPTIMIZING NDOUTILS TABLE: nagios_systemcommands
SQL: OPTIMIZE TABLE nagios_systemcommands
OPTIMIZING NDOUTILS TABLE: nagios_timedeventqueue
SQL: OPTIMIZE TABLE nagios_timedeventqueue
OPTIMIZING NDOUTILS TABLE: nagios_timedevents
SQL: OPTIMIZE TABLE nagios_timedevents
OPTIMIZING NDOUTILS TABLE: nagios_timeperiod_timeranges
SQL: OPTIMIZE TABLE nagios_timeperiod_timeranges
OPTIMIZING NDOUTILS TABLE: nagios_timeperiods
SQL: OPTIMIZE TABLE nagios_timeperiods
CLEANING nagiosxi TABLE 'commands'...
SQL: DELETE FROM xi_commands WHERE processing_time < FROM_UNIXTIME(1670185502) AND status_code = 2
CLEANING nagiosxi TABLE 'events'...
SQL: DELETE FROM xi_events WHERE processing_time < FROM_UNIXTIME(1670185502) AND status_code = 2
CLEANING nagiosxi TABLE 'auth_tokens'...
SQL: DELETE FROM xi_auth_tokens WHERE auth_valid_until < FROM_UNIXTIME(1670127902)
CLEANING nagiosxi TABLE 'cmp_trapdata_log'...
SQL: DELETE FROM xi_cmp_trapdata_log WHERE trapdata_log_datetime < FROM_UNIXTIME(1662438302)
Here's the output of check_nagiosserver.php

Code: Select all

RESULT:
Array
(
    [headers] => Array
        (
            [Date] => Mon, 05 Dec 2022 05:50:31 GMT
            [Server] => Apache/2.4.6 (CentOS) OpenSSL/1.0.2k-fips PHP/5.4.16
            [X-Powered-By] => PHP/5.4.16
            [Access-Control-Allow-Origin] => *
            [Access-Control-Allow-Methods] => POST, GET, OPTIONS, DELETE, PUT
            [Content-Length] => 2206
            [Content-Type] => application/json
        )

    [body] => {"dbmaint":{"last_check":"1670219431"},"cleaner":{"last_check":"1670219401"},"nom":{"last_check":"1670219401"},"reportengine":{"last_check":"1670219401"},"dbbackend":{"last_checkin":"2009-10-13 07:29:33","bytes_processed":"36359614","entries_processed":"305085","connect_time":"2009-10-12 18:42:59","disconnect_time":"0000-00-00 00:00:00"},"daemons":{"updated":"2022-12-05 16:50:22","daemon":[{"@attributes":{"id":"nagioscore"},"name":"nagios","output":"           \u2514\u250055851 \/usr\/local\/nagios\/bin\/nagios -d \/usr\/local\/nagios\/etc\/nagios.cfg","return_code":"0","status":"0"},{"@attributes":{"id":"pnp"},"name":"npcd","output":"           \u2514\u25001110 \/usr\/local\/nagios\/bin\/npcd -d -f \/usr\/local\/nagios\/etc\/pnp\/npcd.cfg","return_code":"0","status":"0"}]},"nagioscore":{"updated":"2022-12-05 16:50:22","activehostchecks":{"val1":"174","val5":"256","val15":"288"},"passivehostchecks":{"val1":"0","val5":"0","val15":"0"},"activeservicechecks":{"val1":"315","val5":"707","val15":"810"},"passiveservicechecks":{"val1":"0","val5":"17","val15":"20"},"activehostcheckperf":{"min_latency":"0","max_latency":"1.149312973022461","avg_latency":"0.01658103135849842","min_execution_time":"0.003005","max_execution_time":"10.004845","avg_execution_time":"0.5293987083333335"},"activeservicecheckperf":{"min_latency":"0","max_latency":"257.998291015625","avg_latency":"0.5640724952147312","min_execution_time":"0","max_execution_time":"11.004851","avg_execution_time":"0.24553984722222244"}},"load":{"updated":"2022-12-05 16:50:22","load1":"2.29","load5":"2.01","load15":"2.27"},"memory":{"updated":"2022-12-05 16:50:22","total":"15876","used":"1785","free":"153","shared":"398","buffers":"13937","cached":"13365"},"swap":{"updated":"2022-12-05 16:50:22","total":"16383","used":"1001","free":"15382"},"iostat":{"updated":"2022-12-05 16:50:27","user":"39.34","nice":"0.00","system":"6.28","iowait":"0.73","steal":"0.00","idle":"53.65"},"sysstat":{"last_check":"1670219421"},"cmdsubsys":{"last_check":"1670219430"},"feedprocessor":{"last_check":"1670219422"},"perfdataprocessor":{"last_check":"1670219421"},"eventman":{"last_check":"1670219428"},"deadpool_reaper":{"last_check":"1670219401"}}

    [info] => Array
        (
            [url] => xxxxx
            [content_type] => application/json
            [http_code] => 200
            [header_size] => 293
            [request_size] => 187
            [filetime] => -1
            [ssl_verify_result] => 0
            [redirect_count] => 0
            [total_time] => 0.262153
            [namelookup_time] => 4.9E-5
            [connect_time] => 0.000131
            [pretransfer_time] => 0.117475
            [size_upload] => 0
            [size_download] => 2206
            [speed_download] => 8414
            [speed_upload] => 0
            [download_content_length] => 2206
            [upload_content_length] => 0
            [starttransfer_time] => 0.26215
            [redirect_time] => 0
            [certinfo] => Array
                (
                )

            [primary_ip] => xx.xx.xx.xx
            [primary_port] => 443
            [local_ip] => xx.xx.xx.xx
            [local_port] => 36608
            [redirect_url] =>
        )

)
XML DATA LOOKS OK
CHECKING JOB reportengine (Report Engine)
CHECKING JOB sysstat (System Statistics)
CHECKING JOB eventman (Event Manager)
CHECKING JOB feedprocessor (Feed Processor)
CHECKING JOB cmdsubsys (Command Subsystem)
CHECKING JOB nom (Nonstop Operations Manager)
CHECKING JOB dbmaint (Database Maintenance)
CHECKING JOB cleaner (Cleaner)
All jobs are running okay.
Any ideas on what could be occuring here?
netsol_tony
Posts: 3
Joined: Wed Aug 03, 2022 11:51 pm

Re: Database Maintenance (dbmaint) stale (802 seconds old)

Post by netsol_tony »

Bumping this.
gormank
Posts: 1114
Joined: Tue Dec 02, 2014 12:00 pm

Re: Database Maintenance (dbmaint) stale (802 seconds old)

Post by gormank »

netsol_tony
Posts: 3
Joined: Wed Aug 03, 2022 11:51 pm

Re: Database Maintenance (dbmaint) stale (802 seconds old)

Post by netsol_tony »

Yea did some scouring but none of the results i found resulted in a fix. I ended up logging a ticket with nagios support for this, they recommended changing the below settings to improve performance which seems to have solved the issue, here's the fix for anyone following along:
In Admin > Performance Settings > Databases tab for the 'Max Log Entries Age', reduce it *(from 90 to 45) to free up space to speed up the dbmaint process on it.

 

And in Admin > Performance Settings > Databases tab ALL THREE optimize Intervals on that page to 300.
Post Reply