buy tb500

A quick performance tune for mysql – innodb_buffer_pool_size

If you are using MySQL with InnoDB tables, you should really look at the innodb_buffer_pool_size.   This tells MySQL how much memory it should use to cache data on your InnoDB tables.  This cuts down on disc IO.  It can save you alot of lookup time if you are pulling out of the cache as opposed to making another call to the database table.  As a good rule of thumb this value should be set to 10% larger than the size of your database… that is assuming you have that much memory available on your server.  By default innodb_buffer_pool_size is set to 8MB.  If you have a decent sized data set, and most of you probably do, then you are going to want to increase this.  My database is about 1GB, so I set my innodb_buffer_pool_size to 2GB, so that I can account for some growth in the future.  Just be sure not to set the value too high.  If you only have 2GB of memory on your box, then don’t set it to 2GB.  This can cause paging at the OS level and that is bad news.

So here is how you set that parameter in mysql

open up your /etc/my.cnf in your favorite editor and add the parameter in the mysqld section

[mysqld]
innodb_buffer_pool_size = 2G

Then you restart mysql and the changes should be picked up.

/etc/init.d/mysqld restart

You can double check by issuing the following command in mysql client.  It will show you the variable and its value

mysql> SHOW VARIABLES;

NOW THATS A QUICK AND EASY PERFORMANCE TUNE!!

Thank you.

Leave a Reply