Differences/delay last update and last check

[jmsanesteban.sgre]

Hi all!

I want to share a problem that we are suffering in two nagiosXI servers. We have one Nagios old server that we are "migrating" to a new one, it has a local database, let's call it OldNagios. We have another two servers with their database offloaded, one for production environment and another one for Integration environment, let's call NewNagios_PROD and NewNagios_INT.

Code: Select all

Architecture: x86_64
CPU(s): 32
RAM: 128GB Used/free (5.3/114)
load average: 3.48, 3.45, 4.88
NagiosXI: 5.6.7
Nagios Core: 4.4.5

Our NewNagios servers have a delay problem between objects' last checks and the last update time, and it is a big delay >= 20min. there is another symptom, in Admin -> Monitoring engine status some of the charts are empty: "Monitoring Engine Event Queue" and "Monitoring engine Checks Statistics"

It only occurs with the servers with offloaded database.

NewNagios_PROD

Diff_last_check_Last_updated.png

Monitoring Engine Status PROD

Monitoring_Engine_Status.png

I though it was a problem with NDO2DB data insertion, after activating the debug showing SQL sentences, the information inserted doesn't show that differences:

Sorry for using quote instead of code, but is for bold marks.

[1617802749.793706] [002.0] [pid=22331] INSERT INTO nagios_servicestatus SET instance_id='1', service_object_id='4903', status_update_time=FROM_UNIXTIME(1617802690), output='Out: 61\.28Kbps: In: 7\.88Kbps \(Sent 2\.56Mb, Received 336\.68Kb in 342 seconds\)',..., last_check=FROM_UNIXTIME(1617802421), next_check=FROM_UNIXTIME(1617802716), check_type='0', last_state_change=FROM_UNIXTIME(1617702588), last_hard_state_change=FROM_UNIXTIME(1617702588), last_hard_state='0', last_time_ok=FROM_UNIXTIME(1617802421), last_time_warning=FROM_UNIXTIME(1617702588), last_time_unknown=FROM_UNIXTIME(0), last_time_critical=FROM_UNIXTIME(1617658232), ... ON DUPLICATE KEY UPDATE instance_id='1', service_object_id='4903', status_update_time=FROM_UNIXTIME(1617802690), output='Out: 61\.28Kbps: In: 7\.88Kbps \(Sent 2\.56Mb, Received 336\.68Kb in 342 seconds\)',..., last_check=FROM_UNIXTIME(1617802421), next_check=FROM_UNIXTIME(1617802716), check_type='0', last_state_change=FROM_UNIXTIME(1617702588), last_hard_state_change=FROM_UNIXTIME(1617702588), last_hard_state='0', last_time_ok=FROM_UNIXTIME(1617802421), last_time_warning=FROM_UNIXTIME(1617702588), last_time_unknown=FROM_UNIXTIME(0), last_time_critical=FROM_UNIXTIME(1617658232), ...

Scheduled queue shows the information correctly:

sched_nagios_core.png

sched_nagios_XI.png

/etc/sysctl.conf
kernel.msgmnb = 131072000
kernel.msgmax = 131072000

ipcs -q

------ Message Queues --------
key msqid owner perms used-bytes messages
0x32000040 11 nagios 600 131023872 127953

We've suffered another problerm in the past with those queues, because meanwhile the database is purging the Xi metadata, you can't insert information and that queue is used to store the messages. So I've change those values but the problem persists, I mean, I don't know why we are saturating that values/queues.

kernel.msgmnb = 796432000
kernel.msgmax = 796432000
kernel.shmmax = 4294967295
kernel.shmall = 268435456

We are going to configure Jumbo frame, but i'm not sure this could help to the situation.

Any help or comment will be welcome.

Thanks in advance.

BR,
Juanma.

[jmsanesteban.sgre]

After cleaning the queues and restarting the NDO2DB service, the process start again and the queue start fill:

Queue_restarted_and_filling.png

Delay_restarted.png

[ssax]

Validate the date/time on both the XI server and the offloaded DB:

XI server:

Code: Select all

date
ls -l /etc/localtime
php -r 'echo date("D M j G:i:s T Y")."\n";'
grep "date.timezone =" /etc/php.ini

Offloaded DB:

Code: Select all

date
ls -l /etc/localtime
mysql -uroot -pnagiosxi -e 'SELECT NOW(); SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;'

Please PM me a copy of your profile from the failing system, you can download it from Admin > System Profile by clicking the Download Profile button.

[jmsanesteban.sgre]

Fist of all thanks for your reply.

I'm going to send the PM with the profile.

There is no problem with the local time or the date and time in DB. If you restart ndo2db the date get "synchronized" but the delay start to appear and it gests bigger until reach 25min of diff.

Code: Select all

[root@NewNagios_INT user@domain]# date
Fri Apr  9 07:11:05 CEST 2021
[root@NewNagios_INT user@domain]# ls -l /etc/localtime
lrwxrwxrwx. 1 root root 33 Aug 11  2020 /etc/localtime -> /usr/share/zoneinfo/Europe/Madrid
[root@NewNagios_INT user@domain]# php -r 'echo date("D M j G:i:s T Y")."\n";'
Fri Apr 9 7:11:05 CEST 2021
[root@NewNagios_INT user@domain]# grep "date.timezone =" /etc/php.ini
date.timezone = Europe/Madrid
[root@NewNagios_INT user@domain]#

MySQL time and variables:

MySQL_Date_TimeVariables.png

It seems to be something that doen't let the system to insert the data correctly in the database.

Thanks in advance.

BR,
Juanma.

[jmsanesteban.sgre]

Just to add some information:

I don't have access to the offloaded database, it is under other team scope.

Repairing databases shows error messages due to engine:

database_repair_fail.png

I'm trying to debug ndo2db, because the problems seems to be here:

ndo2do_fails.png

[ssax]

Those DB messages are not errors, they are just telling you that because the tables are innodb they do not support repair, only myisam supports repair, ignore them.

Please send the output of these commands as root from the XI server:

Code: Select all

sar
ulimit -a
su -s /bin/bash -c 'ulimit -a' nagios

Please send the output of these commands as root from the DB server:

Code: Select all

sar
ulimit -a
su -s /bin/bash -c 'ulimit -a' mysql
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 -uroot -pnagiosxi --table

What version of mysql/mariadb is the remote system running?

[jmsanesteban.sgre]

This is the problem I don't manage the database server, I don't have ssh access, I only can execute SQL sentences, so I'm a bit blind with that component

Nagios Server:

sar

Code: Select all

Linux 3.10.0-1160.15.2.el7.x86_64 (NewNagios_PROD)         04/09/2021      _x86_64_        (32 CPU)

12:00:01 AM     CPU     %user     %nice   %system   %iowait    %steal     %idle
12:10:01 AM     all      2.60      0.00      1.27      0.01      0.00     96.12
12:20:01 AM     all      2.65      0.00      1.21      0.01      0.00     96.12
12:30:01 AM     all      2.96      0.00      1.20      0.01      0.00     95.83
12:40:01 AM     all      2.84      0.00      1.15      0.01      0.00     95.99
12:50:01 AM     all      2.85      0.00      1.16      0.02      0.00     95.98
01:00:01 AM     all      2.85      0.00      1.15      0.02      0.00     95.98
01:10:01 AM     all      2.78      0.00      1.30      0.01      0.00     95.91
01:20:01 AM     all      2.83      0.00      1.29      0.01      0.00     95.87
01:30:01 AM     all      3.01      0.00      1.24      0.02      0.00     95.73
01:40:01 AM     all      2.84      0.00      1.22      0.01      0.00     95.92
01:50:01 AM     all      2.85      0.00      1.22      0.01      0.00     95.91
02:00:01 AM     all      2.75      0.00      1.24      0.01      0.00     96.01
02:10:01 AM     all      2.53      0.00      1.35      0.02      0.00     96.10
02:20:01 AM     all      2.80      0.00      1.39      0.02      0.00     95.79
02:30:01 AM     all      2.79      0.00      1.14      0.01      0.00     96.05
02:40:01 AM     all      2.82      0.00      1.15      0.01      0.00     96.02
02:50:01 AM     all      2.79      0.00      1.28      0.01      0.00     95.91
03:00:01 AM     all      2.83      0.00      1.15      0.02      0.00     96.00
03:10:01 AM     all      2.61      0.00      1.28      0.01      0.00     96.10
03:20:01 AM     all      2.74      0.00      1.21      0.01      0.00     96.04
03:30:01 AM     all      2.70      0.02      1.11      0.01      0.00     96.15
03:40:01 AM     all      2.70      0.00      1.08      0.02      0.00     96.20
03:50:01 AM     all      2.68      0.00      1.08      0.01      0.00     96.23
04:00:01 AM     all      2.70      0.00      1.05      0.01      0.00     96.24
04:10:01 AM     all      2.76      0.00      1.11      0.01      0.00     96.12
04:20:01 AM     all      2.59      0.00      1.05      0.02      0.00     96.34
04:30:01 AM     all      2.54      0.00      1.03      0.01      0.00     96.42
04:40:01 AM     all      2.61      0.00      1.06      0.01      0.00     96.32
04:50:01 AM     all      2.59      0.00      1.05      0.01      0.00     96.35
05:00:01 AM     all      2.73      0.00      1.19      0.01      0.00     96.08
05:10:01 AM     all      2.53      0.00      1.16      0.01      0.00     96.30
05:20:01 AM     all      2.61      0.00      1.10      0.01      0.00     96.28
05:30:01 AM     all      2.56      0.00      1.04      0.01      0.00     96.39
05:40:01 AM     all      2.84      0.00      1.14      0.02      0.00     96.01
05:50:01 AM     all      3.12      0.00      1.17      0.02      0.00     95.70
06:00:01 AM     all      2.90      0.00      1.20      0.01      0.00     95.89
06:10:01 AM     all      2.72      0.00      1.11      0.02      0.00     96.15
06:20:01 AM     all      2.60      0.00      1.06      0.01      0.00     96.33
06:30:01 AM     all      2.66      0.00      1.06      0.01      0.00     96.27
06:40:01 AM     all      2.72      0.00      1.06      0.01      0.00     96.20
06:50:01 AM     all      2.71      0.00      1.08      0.01      0.00     96.20
07:00:01 AM     all      2.85      0.00      1.11      0.01      0.00     96.03
07:10:01 AM     all      2.77      0.00      1.18      0.02      0.00     96.03
07:20:01 AM     all      2.76      0.00      1.12      0.02      0.00     96.10
07:30:01 AM     all      2.55      0.00      1.17      0.01      0.00     96.27
07:40:01 AM     all      2.83      0.00      1.17      0.02      0.00     95.99
07:50:01 AM     all      2.46      0.00      1.15      0.01      0.00     96.39
08:00:01 AM     all      2.68      0.00      1.28      0.01      0.00     96.03
08:10:01 AM     all      2.96      0.00      1.41      0.02      0.00     95.62
08:20:01 AM     all      3.15      0.00      1.46      0.01      0.00     95.37

08:20:01 AM     CPU     %user     %nice   %system   %iowait    %steal     %idle
08:30:01 AM     all      3.20      0.00      1.32      0.01      0.00     95.46
08:40:01 AM     all      2.69      0.00      1.33      0.01      0.00     95.97
08:50:01 AM     all      2.97      0.00      1.65      0.01      0.00     95.37
09:00:01 AM     all      2.84      0.00      1.55      0.01      0.00     95.61
09:10:01 AM     all      2.62      0.00      1.49      0.02      0.00     95.87
09:20:01 AM     all      3.03      0.00      1.76      0.01      0.00     95.21
09:30:01 AM     all      3.28      0.00      1.60      0.01      0.00     95.10
09:40:02 AM     all      3.06      0.00      1.32      0.01      0.00     95.61
09:50:01 AM     all      2.96      0.00      1.45      0.01      0.00     95.58
10:00:01 AM     all      2.69      0.00      1.28      0.01      0.00     96.02
10:10:01 AM     all      2.98      0.00      1.62      0.02      0.00     95.39
10:20:01 AM     all      3.54      0.00      1.57      0.01      0.00     94.87
10:30:01 AM     all      3.76      0.00      1.56      0.01      0.00     94.67
10:40:01 AM     all      3.35      0.00      1.47      0.01      0.00     95.16
10:50:01 AM     all      3.47      0.00      1.53      0.01      0.00     94.99
11:00:01 AM     all      3.34      0.00      1.38      0.01      0.00     95.27
11:10:01 AM     all      3.22      0.00      1.21      0.02      0.00     95.54
11:20:01 AM     all      3.40      0.00      1.22      0.02      0.00     95.36
11:30:01 AM     all      3.36      0.00      1.21      0.02      0.00     95.41
11:40:01 AM     all      3.30      0.00      1.24      0.02      0.00     95.43
11:50:01 AM     all      3.26      0.00      1.25      0.02      0.00     95.47
12:00:01 PM     all      3.10      0.00      1.23      0.02      0.00     95.65
12:10:01 PM     all      3.06      0.00      1.18      0.02      0.00     95.74
12:20:01 PM     all      3.90      0.00      2.63      0.02      0.00     93.45
12:30:01 PM     all      4.43      0.00      3.57      0.03      0.00     91.98
12:40:01 PM     all      4.42      0.00      3.57      0.03      0.00     91.99
12:50:01 PM     all      4.39      0.00      3.58      0.02      0.00     92.01
01:00:01 PM     all      3.06      0.00      3.08      0.02      0.00     93.84
01:10:01 PM     all      2.09      0.00      2.69      0.01      0.00     95.21
01:20:01 PM     all      2.06      0.00      2.68      0.01      0.00     95.25
01:30:01 PM     all      2.04      0.00      2.68      0.01      0.00     95.27
01:40:01 PM     all      2.06      0.00      2.70      0.02      0.00     95.23
01:50:01 PM     all      2.03      0.00      2.67      0.01      0.00     95.29
02:00:01 PM     all      2.03      0.00      2.67      0.01      0.00     95.29
02:10:01 PM     all      2.09      0.00      2.68      0.01      0.00     95.22
02:20:01 PM     all      2.23      0.00      2.70      0.01      0.00     95.07
02:30:01 PM     all      2.73      0.00      2.75      0.01      0.00     94.52
02:40:01 PM     all      2.72      0.00      2.75      0.01      0.00     94.52
02:50:01 PM     all      2.70      0.00      2.76      0.01      0.00     94.54
03:00:01 PM     all      2.72      0.00      2.75      0.01      0.00     94.52
03:10:01 PM     all      2.72      0.00      2.75      0.01      0.00     94.53
03:20:01 PM     all      2.69      0.00      2.75      0.01      0.00     94.55
03:30:01 PM     all      2.70      0.00      2.76      0.01      0.00     94.54
03:40:01 PM     all      2.72      0.00      2.75      0.01      0.00     94.53
03:50:01 PM     all      2.60      0.00      2.74      0.01      0.00     94.66
04:00:01 PM     all      2.53      0.00      2.73      0.01      0.00     94.74
04:10:01 PM     all      2.53      0.00      2.73      0.01      0.00     94.73
04:20:01 PM     all      2.50      0.00      2.73      0.01      0.00     94.76
04:30:01 PM     all      2.53      0.00      2.74      0.01      0.00     94.72
04:40:01 PM     all      2.53      0.00      2.73      0.01      0.00     94.74

04:40:01 PM     CPU     %user     %nice   %system   %iowait    %steal     %idle
04:50:01 PM     all      2.50      0.00      2.74      0.01      0.00     94.75
05:00:01 PM     all      2.52      0.00      2.73      0.01      0.00     94.74
05:10:01 PM     all      2.49      0.00      2.75      0.01      0.00     94.75
05:20:01 PM     all      2.06      0.00      2.68      0.01      0.00     95.26
05:30:01 PM     all      1.91      0.00      2.66      0.00      0.00     95.43
05:40:01 PM     all      1.77      0.00      2.63      0.00      0.00     95.59
05:50:01 PM     all      1.62      0.00      2.61      0.01      0.00     95.77
06:00:01 PM     all      1.67      0.00      2.62      0.01      0.00     95.70
06:10:01 PM     all      1.68      0.00      2.61      0.02      0.00     95.69
06:20:01 PM     all      2.86      0.00      3.15      0.02      0.00     93.96
06:30:01 PM     all      3.53      0.00      2.53      0.15      0.00     93.80
06:40:01 PM     all      2.58      0.00      1.13      0.03      0.00     96.26
06:50:01 PM     all      2.76      0.00      1.16      0.02      0.00     96.06
07:00:01 PM     all      3.13      0.00      1.27      0.02      0.00     95.58
07:10:01 PM     all      2.77      0.00      1.19      0.04      0.00     96.00
07:20:01 PM     all      2.69      0.00      1.15      0.02      0.00     96.14
07:30:01 PM     all      2.83      0.00      1.21      0.03      0.00     95.94
07:40:01 PM     all      2.74      0.00      1.19      0.02      0.00     96.05
07:50:01 PM     all      2.71      0.00      1.18      0.02      0.00     96.08
08:00:01 PM     all      2.74      0.00      1.17      0.02      0.00     96.06
08:10:01 PM     all      3.57      0.00      1.87      0.04      0.00     94.52
08:20:01 PM     all      3.32      0.00      1.40      0.04      0.00     95.24
08:30:01 PM     all      3.24      0.00      1.31      0.03      0.00     95.42
08:40:01 PM     all      3.10      0.00      1.22      0.03      0.00     95.65
08:50:01 PM     all      3.11      0.00      1.20      0.02      0.00     95.66
09:00:01 PM     all      3.13      0.00      1.22      0.02      0.00     95.63
09:10:01 PM     all      3.05      0.00      1.18      0.03      0.00     95.74
09:20:01 PM     all      3.22      0.00      1.20      0.03      0.00     95.55
09:30:01 PM     all      2.96      0.00      1.18      0.03      0.00     95.83
Average:        all      2.80      0.00      1.70      0.02      0.00     95.48

ulimit -a

Code: Select all

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 514929
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 10000
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 514929
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

su -s /bin/bash -c 'ulimit -a' nagios

Code: Select all

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 514929
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 10000
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 4096
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

Database server:
MySQL 5.7.29

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');

Code: Select all

# Table, Size in MB
'SAIM_comments_view', NULL
'SAIM_old_API_Values_view', NULL
'SAIM_old_view', NULL
'SAIM_view', NULL
'nagios_acknowledgements', '0.17'
'nagios_commands', '0.06'
'nagios_commenthistory', '13.03'
'nagios_comments', '0.16'
'nagios_configfiles', '0.03'
'nagios_configfilevariables', '0.02'
'nagios_conninfo', '0.14'
'nagios_contact_addresses', '0.03'
'nagios_contact_notificationcommands', '0.03'
'nagios_contactgroup_members', '0.03'
'nagios_contactgroups', '0.03'
'nagios_contactnotificationmethods', '11.55'
'nagios_contactnotifications', '13.06'
'nagios_contacts', '0.03'
'nagios_contactstatus', '0.03'
'nagios_customvariables', '2.38'
'nagios_customvariablestatus', '5.55'
'nagios_dbversion', '0.02'
'nagios_downtimehistory', '0.03'
'nagios_eventhandlers', '0.16'
'nagios_externalcommands', '0.09'
'nagios_flappinghistory', '5.52'
'nagios_host_contactgroups', '0.13'
'nagios_host_contacts', '0.03'
'nagios_host_parenthosts', '0.03'
'nagios_hostchecks', '0.03'
'nagios_hostdependencies', '0.03'
'nagios_hostescalation_contactgroups', '0.03'
'nagios_hostescalation_contacts', '0.03'
'nagios_hostescalations', '0.03'
'nagios_hostgroup_members', '0.30'
'nagios_hostgroups', '0.08'
'nagios_hosts', '0.58'
'nagios_hoststatus', '7.05'
'nagios_instances', '0.02'
'nagios_logentries', '1063.17'
'nagios_notifications', '9.75'
'nagios_objects', '0.89'
'nagios_processevents', '0.25'
'nagios_programstatus', '0.03'
'nagios_runtimevariables', '0.03'
'nagios_scheduleddowntime', '0.03'
'nagios_service_contactgroups', '0.22'
'nagios_service_contacts', '0.03'
'nagios_service_parentservices', '0.03'
'nagios_servicechecks', '0.06'
'nagios_servicedependencies', '0.03'
'nagios_serviceescalation_contactgroups', '0.03'
'nagios_serviceescalation_contacts', '0.03'
'nagios_serviceescalations', '0.03'
'nagios_servicegroup_members', '0.03'
'nagios_servicegroups', '0.03'
'nagios_services', '1.67'
'nagios_servicestatus', '11.55'
'nagios_statehistory', '528.05'
'nagios_systemcommands', '0.05'
'nagios_timedeventqueue', '0.09'
'nagios_timedevents', '0.09'
'nagios_timeperiod_timeranges', '0.03'
'nagios_timeperiods', '0.03'

[jmsanesteban.sgre]

More information if it can helps in anyway.

I've restarted the NDO2db service at 18:18:36 server time:

Code: Select all

20210409-18:18:36 0x09000040 18         nagios     600        1024         1
20210409-18:18:37 0x09000040 18         nagios     600        627712       613
20210409-18:18:38 0x09000040 18         nagios     600        934912       913
20210409-18:18:39 0x09000040 18         nagios     600        1172480      1145
20210409-18:18:40 0x09000040 18         nagios     600        1416192      1383
20210409-18:18:41 0x09000040 18         nagios     600        1560576      1524
20210409-18:18:42 0x09000040 18         nagios     600        1697792      1658
20210409-18:18:43 0x09000040 18         nagios     600        2062336      2014
...
...
20210409-21:55:53 0x70000040 19         nagios     600        584920064    571211
20210409-21:55:54 0x70000040 19         nagios     600        584919040    571210
20210409-21:55:55 0x70000040 19         nagios     600        584908800    571200
20210409-21:55:56 0x70000040 19         nagios     600        584907776    571199
20210409-21:55:57 0x70000040 19         nagios     600        584946688    571237

[jmsanesteban.sgre]

Hi all!

A little tip is to optimize the date we are inserting into the database, in my case I was able to disable notifications, but this is not the solution this is only a workaround that can give you some fresh air until you sovle the real problem.

BR,
Juanma.

[ssax]

I'm not sure what these tables are but they look crashed:

Code: Select all

'SAIM_comments_view', NULL
'SAIM_old_API_Values_view', NULL
'SAIM_old_view', NULL
'SAIM_view', NULL

If those are in the nagios database I wouldn't recommend that you use our databases when adding new tables/etc, you should create a separate database for them so it doesn't impact any of the functionality of the XI app (backup scripts/etc).

You may need to reach out to whoever manages the database to get access to information.

I need the one for the nagiosxi DB as well:
- You can get the X.X.X.X/user/pass from /usr/local/nagiosxi/html/config.inc.php

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 ('nagiosxi');" | mysql -h X.X.X.X -uyourusername -p'yourpassword' nagiosXI --table