First thing that comes to my mind is that it is probably the best time to put your
application server and database server on different hosts. Having said that, in this case
increasing buffer pool size is still advisable as per my understanding. Your swap
consumption will go up in that case which is not very good either. But giving only 4 GB
to Innodb is even worse for the performance. It is subjective though. You should first
check if MySQL is actually using the allotted 4GB or not. If not, increasing the value
will not help.
Maybe someone can correct me if I'm wrong. :-)
Hope that helps!
From: Machiel Richards <machielr@stripped>
Sent: Thu, May 20, 2010 1:07:43 PM
Subject: Innodb buffer pool size
I just have a quick question.
I have done some research into how to determine the size of your Innodb
All of the sources I used, specified that the Innodb buffer pool size
should be the same size as your database + 10%.
However, as far as I understand it, the buffer size also relies on that
amount of memory being available. Thus if you increase the buffer size, the
amount of memory used will be increased.
My thinking however, is what happens when the database size grows bigger
than the amount of memory available to the hardware.
Say for instance, a server with MySQL also runs other applications. The
amount of memory on the server is 32Gb and about 31Gb is already in use.
The current Innodb buffer pool size is at 4Gb for instance, and the
innodb tables then grow to be about 8Gb in size.
What would be the appropriate actions for this to ensure the buffers are
set to the size to best suit the database needs?