kyct888 wrote on Sat, 05 February 2011 10:51It sounds a bit contradictory what you are describing.
It is common to have a smaller innodb_buffer_pool_size than the total data size that you have. Since you usually have some parts of the data that aren't accessed as frequently as others.
But that should not mean that the swap should start to grow. If the swap starts to grow it means you are allowing MySQL to allocate too much memory.

Actually let me clarify this a bit.
If the swap starts to use more memory = grow that doesn't need to be a bad thing. But what is a bad thing is if you start to get a lot of I/O as a result of pages switching in and out of the swap. The reason why I'm pointing out this distinction is that you can have quite a lot of swap allocated by parts of process data that aren't used very frequently, which is a good thing since the RAM can be used for frequently accessed data.
The negative side comes when data has to be switched in and out of the swap all the time.
And this negative effect is actually so big that it is usually better to be bit on the safe side (while still trying to use as much RAM as possible) than to end up with a server that during peak load suddenly starts to swap and degrades the performance a _lot_ causing more load on the server, etc, etc

As a last word, if you are continuously accessing almost all data all the time you are often lacking indexes which causes table scans = access all data, or you are running reports on all data in the database which can be a bit trickier to speed up since you actually need to scan all data.