How much RAM is needed for mysql with a table size of 2gb?

Question

MySQL

There is a database for the site. It has one table - 5 million rows, 2GB size.
Initially, it was all spinning on vps with 1GB of memory. Then I began to refine the functionality, and realized that any change in the table schema or heavy queries would hang the database tightly
. Now I changed the server to 4GB of RAM and 2 cpu.
I understand correctly that in order for everything to spin quickly, it is necessary that the table fit into the RAM with a margin?
Or do you need to tweak something in the mysql settings?
mysql 5.7.22 innodb table

Answer 1

It will not be possible to give an unequivocal answer how much RAM is needed.
Because it all depends on how you work with the data.
Do you have a large number of non-limited samples?
Perhaps you need to refactor the code:
- limit the selection by the number of records
- limit the selection to only the necessary data
- add caching

Answer 2

Here it is important that indexes fit into memory. Well, the MySQL settings must be set so that there is enough allocated memory for them. It would also be nice to have an SSD if there are not enough writes: performance takes off with it.

Answer 3

I understand correctly that in order for everything to spin quickly, it is necessary that the table fit into the RAM with a margin?

Of course no. These are completely unrelated things.
Unlike newfangled buzzwords like nosql, relational databases are designed to work with volumes that are many times the size of RAM.
But in order to work normally, the system must be tuned. Wherein
It's one problem, with its own solutions, and
-- TOTALLY different, with completely different solutions
When modifying tables, the first thing that matters is free disk space.
As well as the performance of the disk system in general. Moreover, on cheap (idea) disks, the disk subsystem loads the processor, and not the memory at all
. It is necessary to deal with "heavy" requests separately.

Answer 4

I understand correctly that in order for everything to spin quickly, it is necessary that the table fit into the RAM with a margin?
Or do you need to tweak something in the mysql settings?

it is necessary to tune requests, work with the structure. to do indexes, to be engaged in a caching. Of course, you can increase the power to infinity, but if you do not rationally use the base, this will be an endless process. Look for bottlenecks, resource-intensive requests, think about how to optimize. Each case has its own optimal method. Sometimes this is an increase in RAM, but this is the last measure. Before there are a bunch of others.

Answer 5

adding RAM will definitely help with SELECT queries. But it’s not enough to add a RAM, you also need to edit the mysql config so that this RAM can, in principle, be used, it won’t be right away by default.
In the case of a change in the scheme, everything usually rests on the disk. Because all operations with the database must occur atomically (that is, they must either be performed completely or not performed at all). If the power is turned off in the middle of the process or someone kills the process without waiting, the tables should be saved as they were before the change.
This is a very complex process, and as I said, it rests more on the disk. Replacing hdd with ssd will help.
You can make a copy of the database (or only one desired table) and change the schema, then rename it. But if the record goes to the database, then it just won't work either.