MySQL performance impact of increasing innodb_buffer_pool_size

innodbMySQLperformance

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?

Best 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

Apr 14, 2011 : What are the main differences between InnoDB and MyISAM?
Jan 11, 2012 : MySQL said "InnoDB: Initializing buffer pool, size = 128.0M", does it mean it is using 128MB RAM?
Jun 13, 2012 : What to set innodb_buffer_pool and why..?
Oct 22, 2012 : How large should be mysql innodb_buffer_pool_size?

Give it a Try !!!

PERSPECTIVE #1

When you update by Primary Key only in InnoDB, there is a rare but possible occasion when the clustered index (aka gen_clust_index) can get locked.

I once answered three posts from one individual on this subject

Please read through these carefully. The poster of these question found his own workaround based on the seeing InnoDB Clustered Index Locking behavior. Sadly, he did not post what the workaround was.

In addition, when you see the queries running slow, log into mysql and run SHOW ENGINE INNODB STATUS\G and starting looking for locks in the Clustered Index.

PERSPECTIVE #2

I see you commented out innodb_log_file_size. You have it at 5MB, the default. Since innodb_buffer_pool_size is set to 1G, innodb_log_file_size needs to be at 256M. Click here to go about setting innodb_log_file_size to 256M.

PERSPECTIVE #3

I see you are not using innodb_file_per_table. You may want to use it in order to have table updates done specifically for the one table with a million rows. Click here to see how to Clean Up InnoDB infrastructure to use innodb_file_per_table.

Thesql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size

The two answers given from @RickJames and @drogart are essentially the remedies. (+1 for each).

Right from the error log you present, the last two lines say:

InnoDB: Error: log file ./ib_logfile0 is of different size 0 134217728 bytes

InnoDB: than specified in the .cnf file 0 268435456 bytes! `

At that point, it was evident that you set innodb_log_file_size to 256M (268435456) in my.cnf while the InnoDB Transaction Logs (ib_logfile0,ib_logfile1) were respectively 128M (134217728) each. Looking back at the link to my StackOverflow answer in your question, you had to do the following:

Step 01) Add this to my.cnf:

[mysqld]
innodb_buffer_pool_size=4G
innodb_log_file_size=1G

Step 02) Run these command in the OS

mysql -u... -p... -e"SET GLOBAL innodb_fast_shutdown = 1"
service mysql stop
rm -f /var/lib/mysql/ib_logfile*
service mysql start

So as to have confidence in what is happening, run tail -f against the error log. You will see message telling you when each innodb log file is being created.

Best Answer

Related Solutions

Mysql – Innodb UPDATE performance

PERSPECTIVE #1

PERSPECTIVE #2

PERSPECTIVE #3

Thesql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size

Related Question