ibdata1 file size increasing in fusion server

[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

[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?

[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

[cdienger]

Please provide the output of:

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

[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

[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.