polled_extras.ibd getting huge problem

[jenstar13]

Hi
We also had this problem with polled_extras.ibd filling up the fusion file system
I think I fixed it using stuff I found in these 3 posts

https://support.nagios.com/forum/viewto ... hp#p259348
https://support.nagios.com/forum/viewto ... ed#p276050
https://support.nagios.com/forum/viewto ... ed#p250491

I tried the last thing i saw after I trimmed the file with the truncate, but the response had me confused

[root@nagfusion01 fusion]# /usr/local/nagiosfusion/cron/dbmaint_subsys.php
2019-07-25 13:11:27[s: 0, u: 0] dbmaint_query_and_log() unable to gather query data: DELETE
FROM polled_extras
WHERE polled_data_id NOT IN
(SELECT polled_data_id FROM polled_data)

I did strace the pid because it took a long time, and i saw it doing stuff

close(4) = 0
lstat("/usr/local/nagiosfusion/var/log/dberrors.log", {st_mode=S_IFREG|0644, st_size=6142362, ...}) = 0
lstat("/usr/local/nagiosfusion/var/log", {st_mode=S_IFDIR|0755, st_size=8192, ...}) = 0
lstat("/usr/local/nagiosfusion/var", {st_mode=S_IFDIR|0755, st_size=137, ...}) = 0
open("/usr/local/nagiosfusion/var/log/dberrors.log", O_WRONLY|O_CREAT|O_APPEND, 0666) = 4
fstat(4, {st_mode=S_IFREG|0644, st_size=6142362, ...}) = 0
lseek(4, 0, SEEK_CUR) = 0
lseek(4, 0, SEEK_CUR) = 0
write(4, "SQL: [429] DELETE \n "..., 794) = 794
close(4) = 0
lstat("/usr/local/nagiosfusion/var/log/dberrors.log", {st_mode=S_IFREG|0644, st_size=6143156, ...}) = 0
lstat("/usr/local/nagiosfusion/var/log", {st_mode=S_IFDIR|0755, st_size=8192, ...}) = 0
lstat("/usr/local/nagiosfusion/var", {st_mode=S_IFDIR|0755, st_size=137, ...}) = 0
open("/usr/local/nagiosfusion/var/log/dberrors.log", O_WRONLY|O_CREAT|O_APPEND, 0666) = 4
fstat(4, {st_mode=S_IFREG|0644, st_size=6143156, ...}) = 0
lseek(4, 0, SEEK_CUR) = 0


Just wondering if that first output was ok or not
if it's ok, when you reply, you can also close the ticket

thanks in advance
Jenny

[ssax]

Please attach your /etc/my.cnf and all files from this directory:

Code: Select all

/etc/my.cnf.d

And send the output of these commands:

Code: Select all

ls -lh /var/lib/mysql/fusion
echo "SELECT table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema = 'fusion';" | mysql -ufusion -pfusion --table

Thank you

[jenstar13]

Ok they are attached as a zip file

[ssax]

Everything looks good, how's it running?

[jenstar13]

everything seems to be fine, it was the "unable to gather query data" when I ran that once script that had me worried

[ssax]

Ok, should be good, that was the reason for the truncation, are you still seeing that error?

[jenstar13]

I just used that page again, am I supposed to see "unable to gather query data"?

[root@nagfusion01 ~]# /usr/local/nagiosfusion/cron/dbmaint_subsys.php
2019-07-29 08:35:37[s: 0, u: 0] dbmaint_query_and_log() unable to gather query data: DELETE
FROM polled_extras
WHERE polled_data_id NOT IN
(SELECT polled_data_id FROM polled_data)

[ssax]

What is the output of this command?

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 = 'fusion';" | mysql -ufusion -pfusion --table

Any other messages in your /var/log/mysqld.log or /var/log/mariadb/mariadb.log?

As long as the table isn't growing out of control that should be okay, do you get that message every poll?

[jenstar13]

Hi

yes, every time that script is run it has that message

here is the output
+-----------------+------------+
| Table | Size in MB |
+-----------------+------------+
| auth_tokens | 0.03 |
| commands | 0.02 |
| dashboards | 0.02 |
| dashlets | 0.02 |
| dashlets_params | 0.02 |
| log | 21.02 |
| meta | 0.05 |
| options | 0.02 |
| polled_averages | 0.03 |
| polled_data | 0.05 |
| polled_deltas | 0.03 |
| polled_extras | 213.08 |
| polling_lock | 0.02 |
| servers | 0.03 |
| sysstat | 0.03 |
| users | 0.03 |
| users_servers | 0.02 |
| views | 0.06 |
+-----------------+------------+

[ssax]

What version of Fusion are you running?

Please send me that same DB output tomorrow so that I can see how much it's grown.