Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two databases one of size 170 MB and second one is of 15 MB, both having INNODB tables and innodb_buffer_pool_size is set to 8 MB.

My queries are responding well now i.e under mili seconds. Is there any impact on performance if I set innodb_buffer_pool_size more than the default?

share|improve this question

2 Answers 2

Yes there is impacts(better performance), you should set your innodb_buffer_pool_size to 70-80% on a dedicated machine.

I would indicate this post http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/ it will be a good start to choose the right size of your innodb_buffer_pool_size

share|improve this answer

If you want to size the innodb_buffer_pool_size bigger please run this:

SELECT CEILING(SUM(data_length+index_length)/POWER(1024,2)) RIBPS
FROM information_schema.tables WHERE engine='InnoDB';

This will give you the maximum amount of InnoDB Buffer Pool needed for your current data set.

Given the amount of data and index pages for your dataset, having 8MB Buffer Pool will just cause data and index pages accessed to rotate out and load new pages as needed. Setting it with the value based on that RIBPS query (RIBPS stands for Recommended InnoDB Buffer Pool Size), all of InnoDB will be accessed from RAM. Having the Buffer Pool too big would just wastes RAM.

Let's say that you ran this query and got the following:

mysql> SELECT CEILING(SUM(data_length+index_length)/POWER(1024,2)) RIBPS
    -> FROM information_schema.tables WHERE engine='InnoDB';
+-------+
| RIBPS |
+-------+
|  1493 |
+-------+

Take that number and place it in /etc/my.cnf

[mysqld]
innodb_buffer_pool_size=1493M

and restart mysql.

I have discussed this before

Give it a Try !!!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.