Hello!
We have separate db server for Messages table for our service, it’s 32 Gb ram, we have such a situation, we get perfomance problems after innodb data size exceeds innodb buffer pool limit (swap in os begins to grow) and everything slows down. WHy?
In a while we tried to keep this noproblem having enough Ram, buts it seems to be not a good solution.
30 Gb table seems to be not a big deal, even with a 216M rows (indexes about the same size), should we keep our strategy, or buffer pool size could be much less than data?
for us it is much cheaper to have big HDDs than enough RAM.
Unix load average fot this machine is about 10, QPS is only 150.
It 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.