Unlike a lot of database servers, MySQL is strangely conservative (by default) on how much memory it will allocate. If you’re not careful, you can have 16GB of RAM on your machine with MySQL only using 50MBs, leading to extremely poor performance under heavy load. I know firsthand that navigating MySQL configuration guides can be a daunting task, so I’ve prepared this post for those looking for a ‘quick fix’ to encourage MySQL to use a more healthy amount of memory.

Which database storage engine do you use primarily?

Many beginner users may not understand this, but with MySQL you have a choice in which storage engine implementation your database runs on. This is where performance tuning begins to get complicated, as you have to set the configuration variables that correspond to the storage engine you are using! You can see what engine you are relying on by opening MySQL Administrator and viewing your schema under the Catalogs tab. Each table should should have an engine associated with it, which likely says either MyISAM or InnoDB.

In this post, I’ll cover how to increase general memory usage for InnoDB:

Set “innodb_buffer_pool_size” to be up to 80% of RAM, or at least a few hundred megabytes. The default is 8MB and I imagine anyone running a MySQL server these days can at least spare 200 megabytes of memory, so it should at look like this:

innodb_buffer_pool_size=200M

Again, the default is 8M! So, if you’re not setting this variable, you’re choking your database. Feel free to give it 1-2GB if you have the available memory, but the most gain will be made by just going above the default.

There are more InnoDB settings you could set, but their benefits pale in comparison with the value you’ll gain by increasing this from the default of 8M.