Fusion 4 Database Size

[daniel.mazik]

I've noticed that our new Fusion 4 server is chewing through HDD space recently. While looking at the fusion MySQL DB I noticed the polled_extras table is using 30+ GB of disk space. Is there a way to clean this table and/or limit it's size?

[tgriep]

There is a cron job that runs every 5 minutes which should be clearing the table of old entries.
Maybe there is a corrupt table causing the issue.

If your server is running on Centos / RHEL 6, run the following and post the output.

Code: Select all

tail -100 /var/log/mysqld.log

If it is running on Centos / RHEL 7, run the following and post the output.

Code: Select all

tail -100 /var/log/mariadb/mariadb.log

Run these commands as root and post the output.

Code: Select all

tail -50 /usr/local/nagiosfusion/var/log/dbmaint_subsys.log
echo 'SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = "fusion";' | mysql  -t -u fusion -pfusion

Thanks

[daniel.mazik]

I am running the Nagios VMWare appliance (OVA) on centos-release-6-9.el6.12.3.x86_64. Below is the output from the mysql.log. NOTE the root partition was completely out of disk space this morning which caused the errors seen. After I grew the partition they stopped (see entries at 170807 10:10:00):

Code: Select all

InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
170807  9:23:12  InnoDB: Started; log sequence170807 09:23:12 mysqld_safe Number of processes running now: 0
170807 09:23:12 mysqld_safe mysqld restarted
170807  9:23:12  InnoDB: Initializing buffer pool, size = 8.0M
170807  9:23:12  InnoDB: Completed initialization of buffer pool
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
170807  9:23:12  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
170807  9:23:12  InnoDB: Started; log sequence number 7 1175073293
170807  9:23:12 [ERROR] /usr/libexec/mysqld: Error writing file '/var/run/mysqld/mysqld.pid' (Errcode: 28)
170807  9:23:12 [ERROR] Can't start server: can't create PID file: No space left on device
170807 09:23:12 mysqld_safe Number of processes running now: 0
170807 09:23:12 mysqld_safe mysqld restarted
170807  9:23:12  InnoDB: Initializing buffer pool, size = 8.0M
170807  9:23:12  InnoDB: Completed initialization of buffer pool
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
170807  9:23:12  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
170807  9:23:12  InnoDB: Started; log sequence number 7 1175073293
170807  9:23:12 [ERROR] /usr/libexec/mysqld: Error writing file '/var/run/mysqld/mysqld.pid' (Errcode: 28)
170807  9:23:12 [ERROR] Can't start server: can't create PID file: No space left on device
170807 09:23:12 mysqld_safe Number of processes running now: 0
170807 09:23:12 mysqld_safe mysqld restarted
170807  9:23:12  InnoDB: Initializing buffer pool, size = 8.0M
170807  9:23:12  InnoDB: Completed initialization of buffer pool
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
170807  9:23:12  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
170807  9:23:12  InnoDB: Started; log sequence number 7 1175073293
170807  9:23:12 [ERROR] /usr/libexec/mysqld: Error writing file '/var/run/mysqld/mysqld.pid' (Errcode: 28)
170807  9:23:12 [ERROR] Can't start server: can't create PID file: No space left on device
170807 09:23:12 mysqld_safe Number of processes running now: 0
170807 09:23:12 mysqld_safe mysqld restarted
170807  9:23:12  InnoDB: Initializing buffer pool, size = 8.0M
170807  9:23:12  InnoDB: Completed initialization of buffer pool
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
170807  9:23:12  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
170807  9:23:12  InnoDB: Started; log sequence number 7 1175073293
170807  9:23:12 [ERROR] /usr/libexec/mysqld: Error writing file '/var/run/mysqld/mysqld.pid' (Errcode: 28)
170807  9:23:12 [ERROR] Can't start server: can't create PID file: No space left on device
170807 09:23:12 mysqld_safe Number of processes running now: 0
170807 09:23:12 mysqld_safe mysqld restarted
170807  9:23:13  InnoDB: Initializing buffer pool, size = 8.0M
170807  9:23:13  InnoDB: Completed initialization of buffer pool
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
170807  9:23:13  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
170807  9:23:13  InnoDB: Started; log sequence170807 09:23:13 mysqld_safe Number of processes running now: 0
170807 09:23:13 mysqld_safe mysqld restarted
170807  9:23:13  InnoDB: Initializing buffer pool, size = 8.0M
170807  9:23:13  InnoDB: Completed initialization of buffer pool
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
170807  9:23:13  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
170807  9:23:13  InnoDB: Started; log sequence number 7 1175073293
170807  9:23:13 [Note] Event Scheduler: Loaded 0 events
170807  9:23:13 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.73'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
170807 10:09:34 [Note] /usr/libexec/mysqld: Normal shutdown

170807 10:09:34 [Note] Event Scheduler: Purging the queue. 0 events
170807 10:09:34  InnoDB: Starting shutdown...
170807 10:09:36  InnoDB: Shutdown completed; log sequence number 7 1176057449
170807 10:09:36 [Note] /usr/libexec/mysqld: Shutdown complete

170807 10:09:36 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
170807 10:10:00 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
170807 10:10:00  InnoDB: Initializing buffer pool, size = 8.0M
170807 10:10:00  InnoDB: Completed initialization of buffer pool
170807 10:10:00  InnoDB: Started; log sequence number 7 1176057449
170807 10:10:00 [Note] Event Scheduler: Loaded 0 events
170807 10:10:00 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.73'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution

I checked dbmaint_subsys.log and it was empty.

Below is the output from the mysql select statement:

Code: Select all

| TABLE_NAME      | ENGINE |
+-----------------+--------+
| commands        | InnoDB |
| dashboards      | InnoDB |
| dashlets        | InnoDB |
| dashlets_params | InnoDB |
| log             | InnoDB |
| meta            | InnoDB |
| options         | InnoDB |
| polled_averages | InnoDB |
| polled_data     | InnoDB |
| polled_deltas   | InnoDB |
| polled_extras   | InnoDB |
| polling_lock    | InnoDB |
| servers         | InnoDB |
| sysstat         | InnoDB |
| users           | InnoDB |
| users_servers   | InnoDB |
| views           | InnoDB |
+-----------------+--------+

[tgriep]

I have more commands for you. Can you run these and post the output?

Code: Select all

echo 'show table status;' |mysql -u fusion -pfusion fusion -t
/usr/bin/php /usr/local/nagiosfusion/cron/dbmaint_subsys.php
ls -l /var/lib/mysql
ls -l /var/lib/mysql/fusion
df -h
df -i

[daniel.mazik]

echo 'show table status;' |mysql -u fusion -pfusion fusion -t:

Code: Select all

+-----------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name            | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+-----------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| commands        | InnoDB |      10 | Compact    |      0 |              0 |       16384 |               0 |            0 |   4194304 |              1 | 2017-07-26 16:30:09 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| dashboards      | InnoDB |      10 | Compact    |     21 |            780 |       16384 |               0 |            0 |   4194304 |             22 | 2017-07-26 16:30:09 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| dashlets        | InnoDB |      10 | Compact    |     95 |            172 |       16384 |               0 |            0 |   4194304 |            105 | 2017-07-26 16:30:09 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| dashlets_params | InnoDB |      10 | Compact    |    180 |             91 |       16384 |               0 |            0 |   4194304 |           NULL | 2017-07-26 16:30:09 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| log             | InnoDB |      10 | Compact    |     13 |           1260 |       16384 |               0 |            0 |   4194304 |             14 | 2017-07-26 16:30:09 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| meta            | InnoDB |      10 | Compact    |     57 |            287 |       16384 |               0 |            0 |   4194304 |             60 | 2017-07-26 16:30:09 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| options         | InnoDB |      10 | Compact    |     48 |            341 |       16384 |               0 |            0 |   4194304 |             49 | 2017-07-26 16:30:09 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| polled_averages | InnoDB |      10 | Compact    |     14 |           1170 |       16384 |               0 |        16384 |   4194304 |          60937 | 2017-07-26 16:30:09 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| polled_data     | InnoDB |      10 | Compact    |  30564 |            257 |     7880704 |               0 |            0 |   4194304 |          30469 | 2017-07-26 16:30:09 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| polled_deltas   | InnoDB |      10 | Compact    |     14 |           1170 |       16384 |               0 |        16384 |   4194304 |          60923 | 2017-07-26 16:30:09 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| polled_extras   | InnoDB |      10 | Compact    | 249133 |         141839 | 35337011200 |               0 |     21577728 |   4194304 |         302722 | 2017-07-26 16:30:09 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| polling_lock    | InnoDB |      10 | Compact    |      4 |           4096 |       16384 |               0 |            0 |   4194304 |          30471 | 2017-07-26 16:30:09 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| servers         | InnoDB |      10 | Compact    |      4 |           4096 |       16384 |               0 |        16384 |   4194304 |              5 | 2017-07-26 16:30:09 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| sysstat         | InnoDB |      10 | Compact    |      9 |           1820 |       16384 |               0 |        16384 |   4194304 |         172299 | 2017-07-26 16:30:09 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| users           | InnoDB |      10 | Compact    |     12 |           1365 |       16384 |               0 |        16384 |   4194304 |             13 | 2017-07-26 16:30:09 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| users_servers   | InnoDB |      10 | Compact    |      6 |           2730 |       16384 |               0 |            0 |   4194304 |           NULL | 2017-07-26 16:30:09 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| views           | InnoDB |      10 | Compact    |     48 |            341 |       16384 |               0 |            0 |   4194304 |             49 | 2017-07-26 16:30:09 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+-----------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

/usr/bin/php /usr/local/nagiosfusion/cron/dbmaint_subsys.php: No output

ls -l /var/lib/mysql:

Code: Select all

total 35043888
drwx------ 2 mysql mysql        4096 Jul 26 16:30 fusion
-rw-rw---- 1 mysql mysql 35874430976 Aug  8 09:35 ibdata1
-rw-rw---- 1 mysql mysql     5242880 Aug  8 09:35 ib_logfile0
-rw-rw---- 1 mysql mysql     5242880 Aug  8 09:35 ib_logfile1
drwx------ 2 mysql mysql        4096 Jul 26 16:30 mysql
srwxrwxrwx 1 mysql mysql           0 Aug  7 10:10 mysql.sock
drwx------ 2 mysql mysql        4096 Jul 26 16:30 test

ls -l /var/lib/mysql/fusion:

Code: Select all

total 208
-rw-rw---- 1 mysql mysql  9008 Jul 26 16:30 commands.frm
-rw-rw---- 1 mysql mysql  8918 Jul 26 16:30 dashboards.frm
-rw-rw---- 1 mysql mysql  9102 Jul 26 16:30 dashlets.frm
-rw-rw---- 1 mysql mysql  8632 Jul 26 16:30 dashlets_params.frm
-rw-rw---- 1 mysql mysql    65 Jul 26 16:30 db.opt
-rw-rw---- 1 mysql mysql  8816 Jul 26 16:30 log.frm
-rw-rw---- 1 mysql mysql  8756 Jul 26 16:30 meta.frm
-rw-rw---- 1 mysql mysql  8632 Jul 26 16:30 options.frm
-rw-rw---- 1 mysql mysql  8782 Jul 26 16:30 polled_averages.frm
-rw-rw---- 1 mysql mysql 11752 Jul 26 16:30 polled_data.frm
-rw-rw---- 1 mysql mysql  8774 Jul 26 16:30 polled_deltas.frm
-rw-rw---- 1 mysql mysql  8714 Jul 26 16:30 polled_extras.frm
-rw-rw---- 1 mysql mysql  8712 Jul 26 16:30 polling_lock.frm
-rw-rw---- 1 mysql mysql  9304 Jul 26 16:30 servers.frm
-rw-rw---- 1 mysql mysql  8722 Jul 26 16:30 sysstat.frm
-rw-rw---- 1 mysql mysql  9296 Jul 26 16:30 users.frm
-rw-rw---- 1 mysql mysql  8748 Jul 26 16:30 users_servers.frm
-rw-rw---- 1 mysql mysql  8724 Jul 26 16:30 views.frm

df -h (I grew the default root partition on the Nagios appliance to 50G):

Code: Select all

Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
                       47G   35G  9.4G  79% /
tmpfs                 498M     0  498M   0% /dev/shm
/dev/sda1             477M   95M  357M  22% /boot

df -i:

Code: Select all

Filesystem            Inodes IUsed   IFree IUse% Mounted on
/dev/mapper/VolGroup-lv_root
                     3090672 46155 3044517    2% /
tmpfs                 127378     1  127377    1% /dev/shm
/dev/sda1             128016    56  127960    1% /boot

[daniel.mazik]

Also, after running the last set of commands suggested I am not able to see any data in Fusion for any of my fused servers. See screenshot attached.

[tgriep]

I brought this issue up with the developer and he it going to look at why the table is growing and it not getting truncated.
In the mean time, you can manually truncate the tables by running the following in a root shell on the server.

Code: Select all

cd /usr/local/nagiosfusion/scripts
./truncate_polled.php

Keep an eye on the size of the table and if it grows too large again, run the truncate script again.

[bheden]

How many servers do you have fused?

How many hosts/services for each server?

Was everything displaying properly at one point?

I *just* realized I did not leave the polling retention data option in the system settings!

[daniel.mazik]

We have 4 fused XI appliances with a total of 370 hosts and 7500 services. I was wondering where the polling retention settings were . Also, after running the truncate_polled.php script I started getting data from our fused servers again.

[tgriep]

That is good news that it is working for you.
Keep an eye on that table and if it gets that large again, let us know.