Nagios XI offloaded db size increasing frequently

[taashee]

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.

[benjaminsmith]

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.

Code: Select all

systemctl status crond

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.

Code: Select all

 PHP Warning:  mysqli_real_connect(): (HY000/2003): Can't connect to MySQL server on 'ip address of remote db server' (111) in /usr/local/nagiosxi/html/db/adodb/drivers/adodb-mysqli.inc.php on line 125, referer: http://<ipaddress</nagiosxi/includes/components/xicore/status.php?show=hostdetail&host=mow-tableau-db.corp.drreddys.com

Run the following query to list the sizes of the current tables and post it the the thread. Thanks, Benjamin

Code: Select all

echo "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');" | mysql -h 127.0.0.1 -uroot -pnagiosxi --table

[taashee]

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]#

[taashee]

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)

[ssax]

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

Code: Select all

 xi_eventqueue | 3.81 |
| xi_events | 507.24 |
| xi_meta | 10732.01 |

Code: Select all

mysql -h X.X.X.X -unagiosxi -p'n@gweb' nagiosxi -e "truncate table xi_events; truncate table xi_meta; truncate table xi_eventqueue;"

Or as root:

Code: Select all

mysql -h X.X.X.X -uroot -p'nagiosxi' nagiosxi -e "truncate table xi_events; truncate table xi_meta; truncate table xi_eventqueue;"

[taashee]

Hi Benjamin ,
I did as guided by you .

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

[ssax]

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.