Very command problem we face with innodb is its memory usage. When we see ibdata1(irrespective of innodb file per table) becoming too big than usual, we can check the below options.
In general ibdata1 file contains meta data, undo logs, double write buffers and change buffers. The two
changes of ibdata1 to grow huge is wither heavy undo logs or lot many secondary index's to be flushed
> undo log records
./innochecksum: used to see undo log portion of a given ibdata1 file.
Example:
======================================
./innochecksum /var/lib/mysql/ibdata1
......
........
10124 FIL_PAGE_UNDO_LOG
......
======================================
i.e There are 10124 undo log pages are there.
Solution: Check innodb status output for any long lived transactions and make sure they will be
closed properly and they would release these undo logs.
> double write buffers
On busy traffic servers, secondary index changes be placed into double write buffers in ibdata1 and
be merged into table pages by some separate thread during server idle time. If the server is quite
busy for longer times, these get piled up resulting some slowness as well as increase in ibdata1 file
size.
NOTE
If the innodb thread is busy doing double write buffer merging, the same would be seen in innodb
status output.
Fragmentation:
If we delete rows from an innodb table randomly, those gaps become useless and be seen as
fragmented. This fragmented space can be identified in "show table status" command.
In case of file per table enabled, this space can be released to OS by OPTIMIZING/ empty ALTER.
NOTE: There are cases where a 100MB table occupied 100GB space on disc due to fragmentation.
Mainly session tables which gets inserted and deleted records quite often.
In general ibdata1 file contains meta data, undo logs, double write buffers and change buffers. The two
changes of ibdata1 to grow huge is wither heavy undo logs or lot many secondary index's to be flushed
> undo log records
./innochecksum: used to see undo log portion of a given ibdata1 file.
Example:
======================================
./innochecksum /var/lib/mysql/ibdata1
......
........
10124 FIL_PAGE_UNDO_LOG
......
======================================
i.e There are 10124 undo log pages are there.
Solution: Check innodb status output for any long lived transactions and make sure they will be
closed properly and they would release these undo logs.
> double write buffers
On busy traffic servers, secondary index changes be placed into double write buffers in ibdata1 and
be merged into table pages by some separate thread during server idle time. If the server is quite
busy for longer times, these get piled up resulting some slowness as well as increase in ibdata1 file
size.
NOTE
If the innodb thread is busy doing double write buffer merging, the same would be seen in innodb
status output.
Fragmentation:
If we delete rows from an innodb table randomly, those gaps become useless and be seen as
fragmented. This fragmented space can be identified in "show table status" command.
In case of file per table enabled, this space can be released to OS by OPTIMIZING/ empty ALTER.
NOTE: There are cases where a 100MB table occupied 100GB space on disc due to fragmentation.
Mainly session tables which gets inserted and deleted records quite often.