I recently learned that innodb_autoextend_increment should be set at around 1% to 5% of the entire file system. Right now we’re using a very small autoextend-size and I’m therefore planning on increasing it.
However, I’m concerned that our ibdata now is pretty fragmented all over the disk because of this low autoextend-size. Should I be concerned about this?
I’m considering rebuilding the table by mysqldumping the entire thing and importing it with new settings. Would this work as intended?
How big is your DB and how much RAM do you have in your server?
Because if you have more RAM in the server than the size of the DB (and the innodb_buffer_pool_size setting of course) then you don’t really have to rebuild anything since basically the entire DB is cached in RAM during normal operations.
Good point. All our innodb-tables (we are in the process of converting all tables to innodb from myisam) fits in memory so I guess we’re all good.
However, I’m curious… what is the innodb’s did not fit into the buffer pool? Would it then be wise to rebuild? Or would the performance gain be too small to notice?
Depends on how much of the data that does not fit into the buffer pool and how your application is retrieving the data.
Usually you have some data that is fetch very often by the application and then you have some data that is fetched very seldom.
So very often you won’t notice any perticular degradation in the beginning when the data is just a little bit larger than what fits into the buffer pool.
Begin to look at your server, if you don’t have any IO wait then you can omit this.
But if you start to get a lot of IO wait then you could possibly benefit from rebuilding the database.
But for normal size DB’s it’s usually better to buy more RAM and increase the buffer pool instead of fiddling with rebuilding the DB. You will get much more performance with RAM.