How to optimize the database (2 GB hard drive)?

Question

MySQL

Hello.
There is a DB which occupies approximately 2 Gb. on the hard drive. It is assumed that most queries will be SELECT (95%), almost always with JOIN, sometimes with LIKE operator, the selection is always about 100 records, and few INSERT, UPDATE (5%). How to optimize the database so as not to fall under loads and the database processes at least several thousand requests per second? Does it make sense to move the entire database somehow into memory and use synchronous / asynchronous replication with another database server on the same server, but which will make changes already on the hard disk? Or maybe there are some special settings in the database servers?
The database can be MySQL or PostgreSQL.

Answer 1

2GB base is not much.
It is necessary to look towards the normalization of the base model.
Left / Right JOIN queries are not critical, but Inner / Outer can spoil performance very much. In my practice, I had to denormalize and partition the model to prevent JOINs only when working with 30GB+ tablets.
It makes no sense to take out the database on a B-tree in memory, it's easier to use Redis or hashtable indexes.
In the case of MySQL, InnoDB has a built-in cache for first-order keys...
You just need to set up a second-level cache like memcached or ehcache and don't bother.
As @affka said full text search engines are 100% needed.
Personally, I don't really like Sphinx, I'm more inclined towards Solr / Elastic Search and the built-in full-text engine PosgreSQL.
You can read this and this .
For PostgreSQL, you can look at this
. It is also advisable to remember about VACUUM and use pg_reorg to prevent locks.
In principle, your problems are 100% solved by EXPLAIN and normal caching with model normalization.
Sharding / replication and partitioning are too big a hassle if the most elementary issues are not resolved. Moreover, MySQL and PostgreSQL master-master replication is not a cake at all. This is the "Plan I" for any project.

Answer 2

If not much data is involved in queries (not entirely several tables, but only some columns), then well-placed indexes will help. DBMS indexes cache themselves in memory and search through them is very fast.
If you have LIKE queries for some large blob fields, then the database structure is probably incorrect and you need to put the information you are looking for in advance, at the recording stage, into separate columns. Especially since you have 95% select requests.
In any case, it is better to get rid of LIKE requests, the server will fail under loads even if you have everything in memory. If this is a text search, then I advise you to look towards search engines, such as sphinx.

Answer 3

2GB is ridiculously small and should definitely be in memory entirely. But this is just one of the optimization steps.