2

I have ran a OPTIMIZE TABLE Table name; where Table name size is around 54GB. to reduce the size of this innodb table i ran optimize table command.But this increased the size of table to 59 GB! Any reasons for this will be more helpful.

Edit1: This table is used for logging purposes.

Edit2: This table is used earlier in MySQL and it then moved to MariaDB finally now its running in MySQL 5.7.

I am running MySQL 5.7 in an Ubuntu version having RAM around 50 GB.

Bannarisamy Shanmugam
  • 112
  • 7
  • 1
    I don't have a precise explanation, but I have seen similar cases. My guess is that some types of indexes result in greater storage fragmentation depending on the order data is inserted. As an experiment, I would try dropping all the secondary indexes, then optimize table, then re-create the indexes. – Bill Karwin Apr 22 '19 at 14:49
  • Thanks, The table i am mentioning here is used to log, and it has only one key(primary key) which is the id column in table. the table has no other keys, any other possibilities/suggestions will be helpful. Thanks for answering. – Bannarisamy Shanmugam Apr 23 '19 at 12:13
  • 1
    I know that InnoDB pages are initially filled only 15/16ths full by default, to allow for some expansion room as you update rows. It's possible that your table's pages were very tightly filled in the old table, and when you did optimize it copied the data to new pages with the extra space. So by coincidence it ended up expending and using 9.25% more space. This is just a guess. – Bill Karwin Apr 23 '19 at 20:54
  • Thanks @BillKarwin, The database I am using was present in MySQL and then the same database is moved to MariaDB Galera Cluster and then moved to MySQL 5.7. will data moved between the different RDBMS cause this type of issue? – Bannarisamy Shanmugam Apr 26 '19 at 09:00
  • Apparently, yes. I have not done this transition, but you have. What does your experience confirm? – Bill Karwin Apr 26 '19 at 15:15
  • Hi @BillKarwin, The transition is done by client side team. Since I have much restricted access I had only information about the transition. Since the transition is done between different version of MySQL and MariaDB there will be different levels of fragmentation, and I believe the versions makes size to be increased. If you/anyone have suggestions it will be helpful. – Bannarisamy Shanmugam Apr 30 '19 at 08:46
  • Please provide `SHOW CREATE TABLE` and `SHOW TABLE STATUS`; they may provide some clues. – Rick James May 15 '19 at 05:54
  • Did the `ROW_FORMAT` change? – Rick James May 15 '19 at 05:55
  • Bottom line: One more example of why `OPTIMIZE TABLE` is usually not worth the effort. – Rick James May 15 '19 at 05:56
  • Please provide `SHOW TABLE STATUS` (for more stastics) and `ls` or `dir` to see what size the OS has allocated. – Rick James Jul 12 '22 at 16:26

0 Answers0