Weekly issue - DB connection threads peaks

[lvaillant]

So... Bad news. The issue did not occurs this week-end.
No crashed server, no crashed tables...

But DB maintenance is now down :

screenshot-nagios-2021.10.13-11_25_01.png

(Not sure if related to discussed issue)

Trying to launch dbmaint.php :

Code: Select all

# php /usr/local/nagiosxi/cron/dbmaint.php
CREATING: /usr/local/nagiosxi/var/dbmaint.lock
CLEANING ndoutils TABLE 'commenthistory'...
SQL: DELETE FROM nagios_commenthistory WHERE entry_time < FROM_UNIXTIME(1602582843)
CLEANING ndoutils TABLE 'processevents'...
SQL: DELETE FROM nagios_processevents WHERE event_time < FROM_UNIXTIME(1602582843)
CLEANING ndoutils TABLE 'externalcommands'...
SQL: DELETE FROM nagios_externalcommands WHERE entry_time < FROM_UNIXTIME(1633514043)
CLEANING ndoutils TABLE 'logentries'...
SQL: DELETE FROM nagios_logentries WHERE logentry_time < FROM_UNIXTIME(1626342843)
CLEANING ndoutils TABLE 'notifications'...
SQL: DELETE FROM nagios_notifications WHERE start_time < FROM_UNIXTIME(1628934843)
CLEANING ndoutils TABLE 'contactnotifications'...
SQL: DELETE FROM nagios_contactnotifications WHERE start_time < FROM_UNIXTIME(1628934843)
CLEANING ndoutils TABLE 'contactnotificationmethods'...
SQL: DELETE FROM nagios_contactnotificationmethods WHERE start_time < FROM_UNIXTIME(1628934843)
CLEANING ndoutils TABLE 'statehistory'...
SQL: DELETE FROM nagios_statehistory WHERE state_time < FROM_UNIXTIME(1602582843)
CLEANING ndoutils TABLE 'timedevents'...
SQL: DELETE FROM nagios_timedevents WHERE event_time < FROM_UNIXTIME(1634118543)
CLEANING ndoutils TABLE 'systemcommands'...
SQL: DELETE FROM nagios_systemcommands WHERE start_time < FROM_UNIXTIME(1634118543)
CLEANING ndoutils TABLE 'servicechecks'...
SQL: DELETE FROM nagios_servicechecks WHERE start_time < FROM_UNIXTIME(1634118543)
CLEANING ndoutils TABLE 'hostchecks'...
SQL: DELETE FROM nagios_hostchecks WHERE start_time < FROM_UNIXTIME(1634118543)
CLEANING ndoutils TABLE 'eventhandlers'...
SQL: DELETE FROM nagios_eventhandlers WHERE start_time < FROM_UNIXTIME(1634118543)
TIME TO OPTIMIZE
LASTOPT:  1633818302
INTERVAL: 60
NOW:      1634118843
OPTTIME:  1633821902
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
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
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'commands'...
SQL: DELETE FROM xi_commands WHERE processing_time < FROM_UNIXTIME(1634090043) AND status_code = 2
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'events'...
SQL: DELETE FROM xi_events WHERE processing_time < FROM_UNIXTIME(1634090043) AND status_code = 2
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'auth_tokens'...
SQL: DELETE FROM xi_auth_tokens WHERE auth_valid_until < FROM_UNIXTIME(1634032443)
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'cmp_trapdata_log'...
SQL: DELETE FROM xi_cmp_trapdata_log WHERE trapdata_log_datetime < FROM_UNIXTIME(1626342843)
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'cmp_scheduledreports_log'...
SQL: DELETE FROM xi_cmp_scheduledreports_log WHERE report_run < FROM_UNIXTIME(1602582843)
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
SQL1: SELECT xi_meta.meta_id FROM xi_meta LEFT JOIN xi_events ON xi_meta.metaobj_id=xi_events.event_id WHERE metatype_id='1' AND event_id IS NULL
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
PHP Warning:  Invalid argument supplied for foreach() in /usr/local/nagiosxi/cron/dbmaint.php on line 207
SQL2: Deleted 0 (DELETE FROM xi_meta WHERE meta_id IN (SELECT xi_meta.meta_id FROM xi_meta LEFT JOIN xi_events ON xi_meta.metaobj_id=xi_events.event_id WHERE metatype_id='1' AND event_id IS NULL))
CLEANING nagiosxi TABLE 'auditlog'...
SQL: DELETE FROM xi_auditlog WHERE log_time < FROM_UNIXTIME(1618566843)
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
PHP Fatal error:  Call to a member function GetArray() on a non-object in /usr/local/nagiosxi/cron/dbmaint.php on line 225

Code: Select all

MariaDB [(none)]> SELECT table_name AS table_name, engine, table_rows, round(((data_length + index_length) / 1024 / 1024), 2) Size_in_MB FROM information_schema.TABLES WHERE table_rows > 1000000;
+-----------------------------+--------+------------+------------+
| table_name                  | engine | table_rows | Size_in_MB |
+-----------------------------+--------+------------+------------+
| nagios_contactnotifications | MyISAM |   14347842 |    1298.31 |
| nagios_logentries           | MyISAM |   19603891 |    4999.73 |
| nagios_notifications        | MyISAM |    1158018 |     230.54 |
| nagios_statehistory         | MyISAM |    7534314 |     939.08 |
| xi_auditlog                 | InnoDB |   16922346 |    5073.58 |
| xi_events                   | InnoDB |   21986561 |    1749.80 |
| xi_meta                     | InnoDB |   22584725 |   29168.00 |
+-----------------------------+--------+------------+------------+
7 rows in set (0.90 sec)

And because "Optimize" SQL statement locks the table it works on, my Gearman workers are no more feeded during at leat 2min and are stopped, then restart minutes later.
But I now have checks latency and CPU/RAM peaks.

Inkedmultistacked_graph (3)_LI.jpg

[gsmith]

Hi,

It's good that nagios_logentries has been repaired. But it still looks like the db is having issues.

Can you please rerun /usr/local/nagiosxi/scripts/repair_databases.sh and verify there are no other
crashed tables please?

reference document:https://assets.nagios.com/downloads/nag ... tabase.pdf

Once you have verified there are no crashed tables please send your System Profile

1. Login to the Nagios XI GUI using a web browser.
2. Click the "Admin" > "System Profile" Menu
3. Click the "Download Profile" button
4. Save the profile.zip file and share the file in a private message and then reply to this post to bring it up in the queue.


Thanks

[lvaillant]

New Issue this week-end.

Capture01.PNG

Due to nagiosxi DB size and related backups, /usr/local partition was full (/usr/local/store/... > 60GB)
And as a result, the perfdata stored in ramdisk saturate the ramdisk (500MB).

Old backups deleted, DB repair done, DB ok, nagios restarted...

The Nagios XI DB is huge !
xi_meta table is 31GB...

Code: Select all

MariaDB [(none)]> SELECT COUNT(*) AS Total_Table_Count ,table_schema ,CONCAT(ROUND(SUM(table_rows)/1000000,2),'M') AS Total_Row_Count ,CONCAT(ROUND(SUM(data_length)/(1024*1024*1024),2),'G') AS Total_Table_Size ,CONCAT(ROUND(SUM(index_length)/(1024*1024*1024),2),'G') AS Total_Table_Index ,CONCAT(ROUND(SUM(data_length+index_length)/(1024*1024*1024),2),'G') Total_Size FROM information_schema.TABLES GROUP BY table_schema ORDER BY SUM(data_length+index_length) DESC;         +-------------------+--------------------+-----------------+------------------+-------------------+------------+
| Total_Table_Count | table_schema       | Total_Row_Count | Total_Table_Size | Total_Table_Index | Total_Size |
+-------------------+--------------------+-----------------+------------------+-------------------+------------+
|                20 | nagiosxi           | 64.86M          | 35.86G           | 2.18G             | 38.04G     |
|                60 | nagios             | 43.97M          | 5.01G            | 2.38G             | 7.39G      |
|                95 | nagiosql           | 0.01M           | 0.00G            | 0.00G             | 0.00G      |
|                25 | mysql              | 0.00M           | 0.00G            | 0.00G             | 0.00G      |
|                62 | information_schema | NULL            | 0.00G            | 0.00G             | 0.00G      |
|                17 | performance_schema | 0.02M           | 0.00G            | 0.00G             | 0.00G      |
+-------------------+--------------------+-----------------+------------------+-------------------+------------+
6 rows in set (0.40 sec)

Code: Select all

MariaDB [(none)]> SELECT table_name AS table_name, engine, table_rows, round(((data_length + index_length) / 1024 / 1024), 2) Size_in_MB FROM information_schema.TABLES WHERE table_rows > 1000000;
+-----------------------------+--------+------------+------------+
| table_name                  | engine | table_rows | Size_in_MB |
+-----------------------------+--------+------------+------------+
| nagios_contactnotifications | MyISAM |   15281027 |    1379.90 |
| nagios_logentries           | MyISAM |   19068302 |    4875.00 |
| nagios_notifications        | MyISAM |    1210457 |     241.91 |
| nagios_statehistory         | MyISAM |    7832736 |     975.95 |
| xi_auditlog                 | InnoDB |   17962549 |    5413.63 |
| xi_events                   | InnoDB |   23842265 |    1892.83 |
| xi_meta                     | InnoDB |   24000773 |   31646.00 |
+-----------------------------+--------+------------+------------+
7 rows in set (0.45 sec)

DB maintenance is still down.

Capture02.PNG

And due to locks on tables during hourly optimization, gearman workers stop working during the lock time and check latency is important.

Gearman workers.jpg

Master latency.jpg

You'll find my profile file in MP.
Thank you in advance.
Regards

[lvaillant]

The DB locks deeply impact the checks as the data expire.

latency impact.jpg

[gsmith]

Hi

Lets get that size down.

1.Stop nagios, cron, apache.
2. echo "truncate table xi_events; truncate table xi_meta; truncate table xi_eventqueue;" | mysql -u root -pnagiosxi nagiosxi
(this may take 10 to 15 minutes for it to run and update the status)
3. Then start things up.
4. Check that DB Maintenance is working.

Thanks

[lvaillant]

Hi

Before the truncate :

Code: Select all

MariaDB [(none)]> SELECT table_name AS table_name, engine, table_rows, round(((data_length + index_length) / 1024 / 1024), 2) Size_in_MB FROM information_schema.TABLES WHERE table_name like 'xi_%';
+-----------------------------+--------+------------+------------+
| table_name                  | engine | table_rows | Size_in_MB |
+-----------------------------+--------+------------+------------+
| xi_auditlog                 | InnoDB |   18267169 |    5471.64 |
...
| xi_eventqueue               | InnoDB |        292 |       1.03 |
| xi_events                   | InnoDB |   24133179 |    1914.83 |
| xi_meta                     | InnoDB |   23265160 |   32025.00 |
...
+-----------------------------+--------+------------+------------+

Doing the truncate...

Code: Select all

# systemctl stop httpd
# systemctl stop crond
# systemctl stop nagios
# time echo "truncate table xi_events; truncate table xi_meta; truncate table xi_eventqueue;" | mysql -u root -pnagiosxi nagiosxi

real    0m42.658s
user    0m0.005s
sys     0m0.002s
# systemctl start nagios
# systemctl start crond
# systemctl start httpd

After the truncate :

Code: Select all

MariaDB [(none)]> SELECT table_name AS table_name, engine, table_rows, round(((data_length + index_length) / 1024 / 1024), 2) Size_in_MB FROM information_schema.TABLES WHERE table_name like 'xi_%';
+-----------------------------+--------+------------+------------+
| table_name                  | engine | table_rows | Size_in_MB |
+-----------------------------+--------+------------+------------+
| xi_auditlog                 | InnoDB |   18331357 |    5471.64 |
...
| xi_eventqueue               | InnoDB |          0 |       0.03 |
| xi_events                   | InnoDB |        256 |       0.05 |
| xi_meta                     | InnoDB |        236 |       0.33 |
...
+-----------------------------+--------+------------+------------+

Using dbmaint.php :

Code: Select all

# php /usr/local/nagiosxi/cron/dbmaint.php
CREATING: /usr/local/nagiosxi/var/dbmaint.lock
CLEANING ndoutils TABLE 'commenthistory'...
SQL: DELETE FROM nagios_commenthistory WHERE entry_time < FROM_UNIXTIME(1603176259)
CLEANING ndoutils TABLE 'processevents'...
SQL: DELETE FROM nagios_processevents WHERE event_time < FROM_UNIXTIME(1603176259)
...
OPTIMIZING NDOUTILS TABLE: nagios_timeperiods
SQL: OPTIMIZE TABLE nagios_timeperiods
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'commands'...
SQL: DELETE FROM xi_commands WHERE processing_time < FROM_UNIXTIME(1634683459) AND status_code = 2
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'events'...
SQL: DELETE FROM xi_events WHERE processing_time < FROM_UNIXTIME(1634683459) AND status_code = 2
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'auth_tokens'...
SQL: DELETE FROM xi_auth_tokens WHERE auth_valid_until < FROM_UNIXTIME(1634625859)
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'cmp_trapdata_log'...
SQL: DELETE FROM xi_cmp_trapdata_log WHERE trapdata_log_datetime < FROM_UNIXTIME(1626936259)
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'cmp_scheduledreports_log'...
SQL: DELETE FROM xi_cmp_scheduledreports_log WHERE report_run < FROM_UNIXTIME(1603176259)
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
SQL1: SELECT xi_meta.meta_id FROM xi_meta LEFT JOIN xi_events ON xi_meta.metaobj_id=xi_events.event_id WHERE metatype_id='1' AND event_id IS NULL
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
PHP Warning:  Invalid argument supplied for foreach() in /usr/local/nagiosxi/cron/dbmaint.php on line 207
SQL2: Deleted 0 (DELETE FROM xi_meta WHERE meta_id IN (SELECT xi_meta.meta_id FROM xi_meta LEFT JOIN xi_events ON xi_meta.metaobj_id=xi_events.event_id WHERE metatype_id='1' AND event_id IS NULL))
CLEANING nagiosxi TABLE 'auditlog'...
SQL: DELETE FROM xi_auditlog WHERE log_time < FROM_UNIXTIME(1619160259)
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
PHP Fatal error:  Call to a member function GetArray() on a non-object in /usr/local/nagiosxi/cron/dbmaint.php on line 225

No log entries in mariaDB log files.

Is there some PHP/MariaDB timeout/parameter to increase ?

Code: Select all

MariaDB [(none)]> show variables like '%wait_timeout%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50       |
| lock_wait_timeout        | 31536000 |
| wait_timeout             | 28800    |
+--------------------------+----------+
3 rows in set (0.00 sec)
MariaDB [(none)]> show variables like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 1048576    |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)

[gsmith]

Hi

Please edit /etc/php.ini:

Code: Select all

	
    max_input_vars = 50000
    memory_limit = 1024M
    max_execution_time = 120
    max_input_time = 300

Restart the webserver after that.

And then try running:

Code: Select all

php /usr/local/nagiosxi/cron/dbmaint.php

Thanks

[lvaillant]

Hi

Code: Select all

;;;;;;;;;;;;;;;;;;;
; Resource Limits ;
;;;;;;;;;;;;;;;;;;;

; Maximum execution time of each script, in seconds
; http://php.net/max-execution-time
; Note: This directive is hardcoded to 0 for the CLI SAPI
;max_execution_time = 60
max_execution_time = 120

; Maximum amount of time each script may spend parsing request data. It's a good
; idea to limit this time on productions servers in order to eliminate unexpectedly
; long running scripts.
; Note: This directive is hardcoded to -1 for the CLI SAPI
; Default Value: -1 (Unlimited)
; Development Value: 60 (60 seconds)
; Production Value: 60 (60 seconds)
; http://php.net/max-input-time
;max_input_time = 120
max_input_time = 300

; Maximum input variable nesting level
; http://php.net/max-input-nesting-level
;max_input_nesting_level = 64

; How many GET/POST/COOKIE input variables may be accepted
max_input_vars = 5000
suhosin.post.max_vars = 5000
suhosin.request.max_vars = 5000

; Maximum amount of memory a script may consume (128MB)
; http://php.net/memory-limit
;memory_limit = 256M
memory_limit = 1024M

After the restart of httpd, same behavior

Code: Select all

OPTIMIZING NDOUTILS TABLE: nagios_timeperiods
SQL: OPTIMIZE TABLE nagios_timeperiods
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'commands'...
SQL: DELETE FROM xi_commands WHERE processing_time < FROM_UNIXTIME(1634712012) AND status_code = 2
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'events'...
SQL: DELETE FROM xi_events WHERE processing_time < FROM_UNIXTIME(1634712012) AND status_code = 2
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'auth_tokens'...
SQL: DELETE FROM xi_auth_tokens WHERE auth_valid_until < FROM_UNIXTIME(1634654412)
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'cmp_trapdata_log'...
SQL: DELETE FROM xi_cmp_trapdata_log WHERE trapdata_log_datetime < FROM_UNIXTIME(1626964812)
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'cmp_scheduledreports_log'...
SQL: DELETE FROM xi_cmp_scheduledreports_log WHERE report_run < FROM_UNIXTIME(1603204812)
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
SQL1: SELECT xi_meta.meta_id FROM xi_meta LEFT JOIN xi_events ON xi_meta.metaobj_id=xi_events.event_id WHERE metatype_id='1' AND event_id IS NULL
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
PHP Warning:  Invalid argument supplied for foreach() in /usr/local/nagiosxi/cron/dbmaint.php on line 207
SQL2: Deleted 0 (DELETE FROM xi_meta WHERE meta_id IN (SELECT xi_meta.meta_id FROM xi_meta LEFT JOIN xi_events ON xi_meta.metaobj_id=xi_events.event_id WHERE metatype_id='1' AND event_id IS NULL))
CLEANING nagiosxi TABLE 'auditlog'...
SQL: DELETE FROM xi_auditlog WHERE log_time < FROM_UNIXTIME(1619188812)
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
PHP Fatal error:  Call to a member function GetArray() on a non-object in /usr/local/nagiosxi/cron/dbmaint.php on line 225

What about increasing max_allowed_packet in /etc/my.cnf ?
(recurring answer in google search)

[gsmith]

Hi

First please change: max_input_vars = 5000

to: max_input_vars = 50000

in /etc/php.ini

Thanks

[lvaillant]

Sorry, I missed the total number of 0...
But same behavior...

Code: Select all

# vi /etc/php.ini
...
; How many GET/POST/COOKIE input variables may be accepted
;max_input_vars = 5000
;suhosin.post.max_vars = 5000
;suhosin.request.max_vars = 5000
max_input_vars = 50000
suhosin.post.max_vars = 50000
suhosin.request.max_vars = 50000
...
# systemctl restart httpd
# php /usr/local/nagiosxi/cron/dbmaint.php
...
SQL: OPTIMIZE TABLE nagios_timeperiods
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'commands'...
SQL: DELETE FROM xi_commands WHERE processing_time < FROM_UNIXTIME(1634715210) AND status_code = 2
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'events'...
SQL: DELETE FROM xi_events WHERE processing_time < FROM_UNIXTIME(1634715210) AND status_code = 2
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'auth_tokens'...
SQL: DELETE FROM xi_auth_tokens WHERE auth_valid_until < FROM_UNIXTIME(1634657610)
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'cmp_trapdata_log'...
SQL: DELETE FROM xi_cmp_trapdata_log WHERE trapdata_log_datetime < FROM_UNIXTIME(1626968010)
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'cmp_scheduledreports_log'...
SQL: DELETE FROM xi_cmp_scheduledreports_log WHERE report_run < FROM_UNIXTIME(1603208010)
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
SQL1: SELECT xi_meta.meta_id FROM xi_meta LEFT JOIN xi_events ON xi_meta.metaobj_id=xi_events.event_id WHERE metatype_id='1' AND event_id IS NULL
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
PHP Warning:  Invalid argument supplied for foreach() in /usr/local/nagiosxi/cron/dbmaint.php on line 207
SQL2: Deleted 0 (DELETE FROM xi_meta WHERE meta_id IN (SELECT xi_meta.meta_id FROM xi_meta LEFT JOIN xi_events ON xi_meta.metaobj_id=xi_events.event_id WHERE metatype_id='1' AND event_id IS NULL))
CLEANING nagiosxi TABLE 'auditlog'...
SQL: DELETE FROM xi_auditlog WHERE log_time < FROM_UNIXTIME(1619192010)
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
PHP Fatal error:  Call to a member function GetArray() on a non-object in /usr/local/nagiosxi/cron/dbmaint.php on line 225

Code: Select all

MariaDB [(none)]> OPTIMIZE TABLE nagios.nagios_timeperiods;
+---------------------------+----------+----------+-----------------------------+
| Table                     | Op       | Msg_type | Msg_text                    |
+---------------------------+----------+----------+-----------------------------+
| nagios.nagios_timeperiods | optimize | status   | Table is already up to date |
+---------------------------+----------+----------+-----------------------------+
1 row in set (0.00 sec)

During dbmaint execution, 'OPTIMIZE TABLE nagios_logentries' lasts more than 280 seconds...

Code: Select all

MariaDB [(none)]> SELECT * FROM information_schema.processlist where COMMAND!='Sleep';
| ID     | USER     | HOST      | DB     | COMMAND | TIME | STATE                           | INFO

                                                                                               | TIME_MS    | STAGE | MAX_STAGE | PROGRESS |
+--------+----------+-----------+--------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+-------+-----------+----------+
...
| 607995 | ndoutils | localhost | nagios | Query   |  281 | Sorting index                   | OPTIMIZE TABLE nagios_logentries                                                                                                                                                                                                                                                                                                                                                                                                                                                           | 281167.550 |     0 |         0 |    0.000 |
...