After increasing the amount of data, mysql performance degrades. Need advice on systematic diagnosis

Question

We have an installed web application that is supported by the mysql database. We add data to various tables every month. After recent updates, database performance has deteriorated significantly, and we had to cancel the updates. So far I have identified requests related to the logic of the web service - there are no updates, just reading. I found that all queries use efficient table indexes.

I know that this is a difficult problem for diagnosis, and I do not expect a final answer here, I just need some tips for further diagnosis and hints of research that will identify the causes of the problem.

Answer 1

One thing that can have a huge impact is how you allocate MySQL memory for a given usage pattern.

Relative request cache size, innodb buffer, etc. should be customized to your specific needs.

Here are some links that helped me solve the same issue.

MySQL server performance (requires free registration)

10 MySQL variables you should keep track of

Understanding Performance Statistics

MySQLTuner is a great (free) tuning tool written by perl.

, . , , . , .

, , , Explain, , .

, MySQL Enterprise Sun , .