2

I'm trying to understand how to properly size innodb_buffer_pool_size and when to optimize my tables. Questions are:

  • When sizing the innodb_buffer_pool_size, should I take into account the size of the tables on disk (du -sh /folder_with_db_data) or the size reported by a tool like mysqltunner.pl
  • When running mysqltunner.pl, it says that my tables are 0% fragmented, yet, the space in disk is much higher than the space reported by mysqltunner.pl. After running an optimize for all-databases I can see that the space on disk of the data shrinks until it matches the space previously reported by mysqltunner.pl. Question is, why if there is "no fragmentation" does the data on disk shrinks?
  • Could you help me understand what the "optimize" really does?
  • Setting the innodb_buffer_pool_instances parameter so I end up with 1 instance per each GB of innodb_buffer_pool_size, is a mariadb good practice. Does this setting could involve any risk? I ask this because of the application I'm using (Zimbra), does not even mention this setting and I'm curious about it.

Thank you very much!!!

migrated from serverfault.com Dec 1 '18 at 0:29

This question came from our site for system and network administrators.

0

innodb_buffer_pool_size

No; wrong approach.

Simply set innodb_buffer_pool_size to about 70% of available RAM.

If you make it too much larger than that, you risk swapping, which hurts performance terribly.

If you make it too much smaller than that, your queries run slower.

If you have a lot more data than that, you will simply hope that the "working set" is smaller, and the performance will still be good.

If you have a lot less data than that, you are wasting some RAM that you won't be using anyway.

Fragmentation

Bogus.

There are at least 3 ways in which InnoDB fragments. The available metrics measure only one of them.

Fragmentation has very little to do with performance. So, as long as you are not threatened with running out of disk space, don't defragment. Furthermore, promptly after defragmenting, the freed up space will be quickly gobbled up. The encouraging thing is that the used space will stop growing as fast after it gets to some steady state.

Catch 22: If you are about to run out of disk space, you probably can't run OPTIMIZE because it needs space to run!

OPTIMIZE TABLE

Ignore their advice; never use it for InnoDB tables.

It copies the table over, thereby removing any "free" space because the copying compacts things better. But, I repeat, this only squeezes out some of the "free" space.

Well, OK, there are rare cases where OPTIMIZE is useful. However, MysqlTuner "cry's wolf" a lot on this item.

innodb_buffer_pool_instances

Simple; don't worry.

Just set it to an integer that is innodb_buffer_pool_size/1G, but no less than 1 and no bigger than 16. No risk. But, also, virtually no gain. In a very busy system, it may speed things up by only a few percent. If you have a very busy system, you need to be looking at other ways to avoid falling off the cliff.

The application probably does not mention it because either (1) they did notice it when it was added (MySQL 5.5), or (2) they realized it did not matter much.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy