Nagios Support Forum

Hello Team ,

There is one case where nagios db is offloaded onto another server . We recently truncated the tables in nagiosxi and nagios database . But size is increasing again and again resulting nagios is not working properly and often asking for db repair .

For your reference please find the system profile and let us know why size has increased largely .


Moderator's Note: The profile has been shared with the support team but has been removed from the public forum.

Hi @taashee,

There's a database maintenance script that runs as cron job every hour to optimize and trim the tables based on the settings in Admin > System Config > Performance Settings > Databases, make sure the cron service is running on this server.
In the apache log, I'm also seeing some connection issues to the database. You may have some network issues disrupting the connection. Do you have issues pinging the server. Run the following query to list the sizes of the current tables and post it the the thread. Thanks, Benjamin

Hello Benjamin ,

Please find the size of the databases .The command you have mentioned is not working but i checked db size otherway .

[root@nagios-db ~]# cd /var/lib/mysql/
[root@nagios-db mysql]# du -hs * | grep G
7.5G nagios
13G nagiosxi

[root@nagios-db mysql]# cd nagios
[root@nagios-db nagios]# du -hs * | grep G
2.0G nagios_logentries.MYD
1.9G nagios_logentries.MYI
1.3G nagios_statehistory.MYD
[root@nagios-db nagios]# cd ../nagiosxi/
[root@nagios-db nagiosxi]# du -hs * | grep G
11G xi_meta.MYD
[root@nagios-db nagiosxi]#

Hi Benjamin ,

command worked .please find the respective size .

MariaDB [(none)]> SELECT table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema IN ('nagios', 'nagiosql', 'nagiosxi');
+--------------------------------------------+------------+
| Table | Size in MB |
+--------------------------------------------+------------+
| nagios_acknowledgements | 7.14 |
| nagios_commands | 0.02 |
| nagios_commenthistory | 16.33 |
| nagios_comments | 0.02 |
| nagios_configfiles | 0.00 |
| nagios_configfilevariables | 0.01 |
| nagios_conninfo | 0.74 |
| nagios_contact_addresses | 0.00 |
| nagios_contact_notificationcommands | 0.02 |
| nagios_contactgroup_members | 0.00 |
| nagios_contactgroups | 0.00 |
| nagios_contactnotificationmethods | 3.32 |
| nagios_contactnotifications | 3.62 |
| nagios_contacts | 0.00 |
| nagios_contactstatus | 0.00 |
| nagios_customvariables | 0.73 |
| nagios_customvariablestatus | 0.78 |
| nagios_dbversion | 0.00 |
| nagios_downtimehistory | 5.90 |
| nagios_eventhandlers | 3.52 |
| nagios_externalcommands | 8.07 |
| nagios_flappinghistory | 11.89 |
| nagios_host_contactgroups | 0.00 |
| nagios_host_contacts | 0.06 |
| nagios_host_parenthosts | 0.00 |
| nagios_hostchecks | 0.00 |
| nagios_hostdependencies | 0.00 |
| nagios_hostescalation_contactgroups | 0.00 |
| nagios_hostescalation_contacts | 0.00 |
| nagios_hostescalations | 0.00 |
| nagios_hostgroup_members | 0.05 |
| nagios_hostgroups | 0.01 |
| nagios_hosts | 0.32 |
| nagios_hoststatus | 0.63 |
| nagios_instances | 0.00 |
| nagios_logentries | 2731.56 |
| nagios_notifications | 964.99 |
| nagios_objects | 2.77 |
| nagios_processevents | 0.16 |
| nagios_programstatus | 0.00 |
| nagios_runtimevariables | 0.00 |
| nagios_scheduleddowntime | 0.00 |
| nagios_service_contactgroups | 0.00 |
| nagios_service_contacts | 0.42 |
| nagios_service_parentservices | 0.00 |
| nagios_servicechecks | 0.00 |
| nagios_servicedependencies | 0.00 |
| nagios_serviceescalation_contactgroups | 0.00 |
| nagios_serviceescalation_contacts | 0.00 |
| nagios_serviceescalations | 0.00 |
| nagios_servicegroup_members | 0.01 |
| nagios_servicegroups | 0.00 |
| nagios_services | 1.76 |
| nagios_servicestatus | 4.69 |
| nagios_statehistory | 1630.55 |
| nagios_systemcommands | 0.04 |
| nagios_timedeventqueue | 0.00 |
| nagios_timedevents | 0.00 |
| nagios_timeperiod_timeranges | 0.02 |
| nagios_timeperiods | 0.00 |
| tbl_command | 0.04 |
| tbl_contact | 0.01 |
| tbl_contactgroup | 0.01 |
| tbl_contacttemplate | 0.01 |
| tbl_domain | 0.01 |
| tbl_host | 0.25 |
| tbl_hostdependency | 0.00 |
| tbl_hostescalation | 0.00 |
| tbl_hostextinfo | 0.00 |
| tbl_hostgroup | 0.01 |
| tbl_hosttemplate | 0.02 |
| tbl_info | 0.13 |
| tbl_lnkContactToCommandHost | 0.00 |
| tbl_lnkContactToCommandService | 0.00 |
| tbl_lnkContactToContactgroup | 0.00 |
| tbl_lnkContactToContacttemplate | 0.00 |
| tbl_lnkContactToVariabledefinition | 0.00 |
| tbl_lnkContactgroupToContact | 0.00 |
| tbl_lnkContactgroupToContactgroup | 0.00 |
| tbl_lnkContacttemplateToCommandHost | 0.00 |
| tbl_lnkContacttemplateToCommandService | 0.00 |
| tbl_lnkContacttemplateToContactgroup | 0.00 |
| tbl_lnkContacttemplateToContacttemplate | 0.00 |
| tbl_lnkContacttemplateToVariabledefinition | 0.00 |
| tbl_lnkHostToContact | 0.04 |
| tbl_lnkHostToContactgroup | 0.00 |
| tbl_lnkHostToHost | 0.00 |
| tbl_lnkHostToHostgroup | 0.02 |
| tbl_lnkHostToHosttemplate | 0.04 |
| tbl_lnkHostToVariabledefinition | 0.03 |
| tbl_lnkHostdependencyToHost_DH | 0.00 |
| tbl_lnkHostdependencyToHost_H | 0.00 |
| tbl_lnkHostdependencyToHostgroup_DH | 0.00 |
| tbl_lnkHostdependencyToHostgroup_H | 0.00 |
| tbl_lnkHostescalationToContact | 0.00 |
| tbl_lnkHostescalationToContactgroup | 0.00 |
| tbl_lnkHostescalationToHost | 0.00 |
| tbl_lnkHostescalationToHostgroup | 0.00 |
| tbl_lnkHostgroupToHost | 0.01 |
| tbl_lnkHostgroupToHostgroup | 0.00 |
| tbl_lnkHosttemplateToContact | 0.00 |
| tbl_lnkHosttemplateToContactgroup | 0.00 |
| tbl_lnkHosttemplateToHost | 0.00 |
| tbl_lnkHosttemplateToHostgroup | 0.00 |
| tbl_lnkHosttemplateToHosttemplate | 0.00 |
| tbl_lnkHosttemplateToVariabledefinition | 0.00 |
| tbl_lnkServiceToContact | 0.23 |
| tbl_lnkServiceToContactgroup | 0.00 |
| tbl_lnkServiceToHost | 0.25 |
| tbl_lnkServiceToHostgroup | 0.00 |
| tbl_lnkServiceToServicegroup | 0.00 |
| tbl_lnkServiceToServicetemplate | 0.26 |
| tbl_lnkServiceToVariabledefinition | 0.19 |
| tbl_lnkServicedependencyToHost_DH | 0.00 |
| tbl_lnkServicedependencyToHost_H | 0.00 |
| tbl_lnkServicedependencyToHostgroup_DH | 0.00 |
| tbl_lnkServicedependencyToHostgroup_H | 0.00 |
| tbl_lnkServicedependencyToService_DS | 0.00 |
| tbl_lnkServicedependencyToService_S | 0.00 |
| tbl_lnkServiceescalationToContact | 0.00 |
| tbl_lnkServiceescalationToContactgroup | 0.00 |
| tbl_lnkServiceescalationToHost | 0.00 |
| tbl_lnkServiceescalationToHostgroup | 0.00 |
| tbl_lnkServiceescalationToService | 0.00 |
| tbl_lnkServicegroupToService | 0.01 |
| tbl_lnkServicegroupToServicegroup | 0.00 |
| tbl_lnkServicetemplateToContact | 0.00 |
| tbl_lnkServicetemplateToContactgroup | 0.00 |
| tbl_lnkServicetemplateToHost | 0.00 |
| tbl_lnkServicetemplateToHostgroup | 0.00 |
| tbl_lnkServicetemplateToServicegroup | 0.00 |
| tbl_lnkServicetemplateToServicetemplate | 0.01 |
| tbl_lnkServicetemplateToVariabledefinition | 0.00 |
| tbl_lnkTimeperiodToTimeperiod | 0.00 |
| tbl_logbook | 0.00 |
| tbl_mainmenu | 0.00 |
| tbl_permission | 0.02 |
| tbl_permission_inactive | 0.02 |
| tbl_service | 1.52 |
| tbl_servicedependency | 0.00 |
| tbl_serviceescalation | 0.00 |
| tbl_serviceextinfo | 0.00 |
| tbl_servicegroup | 0.01 |
| tbl_servicetemplate | 0.02 |
| tbl_session | 0.00 |
| tbl_session_locks | 0.00 |
| tbl_settings | 0.00 |
| tbl_submenu | 0.00 |
| tbl_timedefinition | 0.01 |
| tbl_timeperiod | 0.01 |
| tbl_user | 0.01 |
| tbl_variabledefinition | 0.48 |
| xi_auditlog | 1.56 |
| xi_auth_tokens | 0.03 |
| xi_cmp_trapdata | 0.03 |
| xi_cmp_trapdata_log | 0.03 |
| xi_commands | 0.00 |
| xi_eventqueue | 3.81 |
| xi_events | 507.24 |
| xi_incidents | 0.02 |
| xi_meta | 10732.01 |
| xi_options | 0.02 |
| xi_sessions | 0.03 |
| xi_sysstat | 0.01 |
| xi_usermeta | 0.31 |
| xi_users | 0.02 |
+--------------------------------------------+------------+
166 rows in set (0.02 sec)

You are hitting a bug, you will need to truncate these tables to fix it:
Or as root:

Hi Benjamin ,
I did as guided by you .

But how you came to know that it is a bug . Can u clarify please .

This is a known bug. Those are temporary tables that should be cleaned out automatically by the cron jobs after being processed, if they are large like yours that means you're hitting the bug and it can only be fixed by truncating those temporary tables.