InnoDB Caches

If you have tables using the InnoDB engine, there are a couple of parameters you must adjust. The first is innodb_buffer_pool_size = 4G, where you must replace “4G” with the amount of RAM you want to dedicate to the InnoDB caching system. The MySQL manual recommends at least 70% of the available RAM on dedicated servers, however this is overkill if you are using a MySQL with both MyISAM and InnoDB tables. The size of the InnoDB buffer should be based on the amount of data stored in InnoDB tables. Check what the following output from reports:

InnoDB data size / buffer pool: 304.0K/4.0G

As you can see, we are reserving 4 GiB of RAM to store only 304 kiB of data, so we could easily decrease (a lot) the size of the InnoDB buffer pool, and free a lot of RAM for other uses.

An important parameter that you must add to your my.cnf file is innodb_flush_method=O_DIRECT. This will prevent the operating system from caching data that is already cached.

Gabriel Torres is a Brazilian best-selling ICT expert, with 24 books published. He started his online career in 1996, when he launched Clube do Hardware, which is one of the oldest and largest websites about technology in Brazil. He created Hardware Secrets in 1999 to expand his knowledge outside his home country.