Page 2 of 2
Re: ibdata1 file size increasing in fusion server
Posted: Thu Aug 09, 2018 9:26 am
by amane
Hi cdienger,
Even after running the script, the size for ibdata1 is not reducing on our fusion server.
We have run the 'echo $?' command after running the script and the output is 0.
./truncate_polled.php echo 'OPTIMIZE TABLE polled_extras;' |mysql -u fusion -pfusion fusion
[root@lxfusnagprd001 ~]# echo $?
0
ibdata1 size is 120gb in our fusion server.
Thanks & Regards,
Avinash Mane
Re: ibdata1 file size increasing in fusion server
Posted: Thu Aug 09, 2018 11:31 am
by cdienger
Please not that the truncate script and the optimize two separate commands. If you run the on the same line make sure to separate them with a semicolon:
./truncate_polled.php; echo 'OPTIMIZE TABLE polled_extras;' |mysql -u fusion -pfusion fusion
You can also verify the sizes of tables with:
echo 'show table status from fusion;' |mysql -u fusion -pfusion fusion
The above said, why does this need to be fixed prior to the upgrade?
Re: ibdata1 file size increasing in fusion server
Posted: Fri Aug 10, 2018 4:57 am
by amane
Thanks cdienger,
We can not upgrade our prod environment directly. and for upgrade we have to go through the CRQ and cab meeting and once.
We are upgrading our QA environment first and then prod. ibdata1 size is around 120 gb and there is no space available on this filesystem that's why we are trying to clear the space.
However we got the below output after running the script and it is not reduced the size for ibdata1.
[nagios@lxfusnagprd001 scripts]$ ./truncate_polled.php; echo 'OPTIMIZE TABLE polled_extras;' |mysql -u fusion -pfusion fusion
polled tables truncated!
Table Op Msg_type Msg_text
fusion.polled_extras optimize note Table does not support optimize, doing recreate + analyze instead
fusion.polled_extras optimize status OK
Thanks & Regards,
Avinash
Re: ibdata1 file size increasing in fusion server
Posted: Fri Aug 10, 2018 9:55 am
by cdienger
Please provide the output of:
echo 'show table status from fusion;' |mysql -u fusion -pfusion fusion
Re: ibdata1 file size increasing in fusion server
Posted: Sat Aug 11, 2018 1:23 am
by amane
Hi cdienger,
Please find the output below for "echo 'show table status from fusion;' |mysql -u fusion -pfusion fusion".
Code: Select all
[nagios@lxfnnagprd001 ~]$ echo 'show table status from fusion;' |mysql -u fusion -pfusion fusion
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 120413224960 1 2018-02-16 01:11:04 NULL NULL utf8_general_ciNULL
dashboards InnoDB 10 Compact 152 107 16384 0 0 120413224960 156 2018-02-16 01:11:04 NULL NULL utf8_general_ciNULL
dashlets InnoDB 10 Compact 197 249 49152 0 0 120413224960 260 2018-02-16 01:11:04 NULL NULL utf8_general_ciNULL
dashlets_params InnoDB 10 Compact 107 153 16384 0 0 120413224960 NULL 2018-02-16 01:11:04 NULL NULL utf8_general_ciNULL
log InnoDB 10 Compact 5665 280 1589248 0 0 120413224960 3724559 2018-02-16 01:11:04 NULL NULL utf8_general_ci NULL
meta InnoDB 10 Compact 1574 72 114688 0 0 120413224960 1492 2018-02-16 01:11:04 NULL NULL utf8_general_ci NULL
options InnoDB 10 Compact 40 409 16384 0 0 120413224960 41 2018-02-16 01:11:04 NULL NULL utf8_general_ci NULL
polled_averages InnoDB 10 Compact 130 126 16384 0 16384 120413224960 2457 2018-03-06 04:40:31 NULL NULL utf8_general_ciNULL
polled_data InnoDB 10 Compact 1195 274 327680 0 0 120413224960 1230 2018-03-06 04:39:47 NULL NULL utf8_general_ciNULL
polled_deltas InnoDB 10 Compact 130 126 16384 0 16384 120413224960 2301 2018-03-06 04:40:42 NULL NULL utf8_general_ciNULL
polled_extras InnoDB 10 Compact 19857 362965 7207403520 0 1589248 120413224960 13184 2018-08-10 05:48:12 NULL NULL utf8_general_ci NULL
polling_lock InnoDB 10 Compact 68 240 16384 0 0 120413224960 69 2018-03-06 04:41:03 NULL NULL utf8_general_ciNULL
servers InnoDB 10 Compact 7 2340 16384 0 16384 120413224960 15 2018-02-16 01:11:04 NULL NULL utf8_general_ci NULL
sysstat InnoDB 10 Compact 11 1489 16384 0 16384 120413224960 5265207 2018-02-16 01:11:04 NULL NULL utf8_general_ci NULL
users InnoDB 10 Compact 395 248 98304 0 16384 120413224960 329 2018-02-16 01:11:04 NULL NULL utf8_general_ci NULL
users_servers InnoDB 10 Compact 76 215 16384 0 0 120413224960 NULL 2018-02-16 01:11:04 NULL NULL utf8_general_ciNULL
views InnoDB 10 Compact 1249 170 212992 0 0 120413224960 1313 2018-02-16 01:11:04 NULL NULL utf8_general_ci NULL
Re: ibdata1 file size increasing in fusion server
Posted: Mon Aug 13, 2018 7:22 am
by jomann
This is an issue with InnoDB and how it works. With innodb_file_per_table set to 0 (the default) the InnoDB database is stored in one big file, and it will not de-allocate space when the database shrinks. So if you had a lot of data at one point, even if you remove it, the file will not shrink the database size.
This was solved in Fusion 4.1.0 and requires an upgrade. You can manually upgrade only the database if you wanted to. If you download the latest Nagios Fusion tarball, extract it, and go into nagiosfusion/setup/upgrades there is a file called 000-fix-ibdata.sh that you can run. You should backup your database before running it although it does it's own backup too.