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!!!