How to reduce the load on the database with a large number of requests?

Question

MySQL

A small note: there is a script that spawns a certain number of child processes (also scripts) for background work with the database. In general, there are more write operations than read operations. At the moment, mysql fully loads the CPU, requests fall into the queue. In addition to optimizing queries and reducing their number, what else would be appropriate? Because scripts run in the background whether the user requests a web page or not.

Answer 1

It is possible to change my.cnf to large.cnf if RAM allows.
In 90% of projects, this action solves problems for a long time.

Answer 2

Are you using transactions in your queries? If you have logs or revisions, take them to hell. Scripts or JS itself is a client language, it works on the client in the browser, not on the server. If the whole problem is in the scripts, then you can run them on demand, and not keep them on all the time.

Answer 3

To the above written - clustering.

Answer 4

Not the fact that a problem because of a DBMS. After all, the task of the DBMS is to serve application requests.
In general, it is necessary to monitor the processes in the DBMS in more detail in order to say exactly where the bottleneck is. You can use built-in tools, as well as the Percona toolkit .
I also advise you to check the script program for unoptimized insert queries in the database.
If there is an insertion of a large number of records, then it is possible to optimize with one insert, wrapping it in a transaction.

Answer 5

1) Move to PostgreSql
2) Use batch insert, I write 100,000 records and won’t stutter
3) Delete the B-Tree index, it re-indexes the entire tree with each insert, in PostgreSql it’s a little better with this reindexing is performed deferred.
For Mysql, the brakes start at 2.000.000 records in the PostgreSql table after 5.000.000.
4) If you switch to PostgreSql, you can raise the second database in master-slave mode. Write to master
, read from slave. And synchronize from in asynchronous mode. This way the slave will lag a bit behind the master (about 2 seconds depending on the load) but the slave will just fly.
#PostgreSQLRussia meetup at Avito.ru
https://www.youtube.com/watch?v=2LDAcGZRAEM