How to find the cause of application performance drop (apache, php, mysql)?

Question

PHP

There is a site in the local network where employees conduct document management.
Employees enter visitor data, plus background scripts enter their own data - an average of 70 database queries per second. This works on PHP 7.2, MySQL 5.7, Apache 2.4 on Windows Server 2012 r2. The application has been written in native PHP since 2015, gradually becoming more complex.

Recently, periodically during the day, performance sags heavily. How and how to provoke such a fall (to study in the laboratory) I do not understand.

What happens: 32 cores of the virtual machine get a load of 90% (memory is free), almost all queries in the database fall into the slow query log with a value of 5-10-80 seconds instead of the usual 0.1-0.5-1.

How we treat: we restart apache (the method was found empirically, restarting the rest does not change the situation).
It lasts for a while, then the problem reappears. Or it does not repeat and the server lives quietly for several days.

There are several hypotheses (autocommit, regular background write requests for 6-10 seconds), but they all require rewriting the application, and not a fact that will help.

Please help! Can you tell me how to identify the problem? What are the diagnostic tools?

Answer 1

Poorly optimized work with the database and a bad database structure leads to such results. You need to start with the application itself and gradually optimize the code so that one query is executed at a time to fetch data and one to update it. If there are several hundred requests, then group by a hundred and send (or by thousands, check empirically).

Answer 2

queries in the database almost all fall into the slow query log with a value of 5-10-80 seconds instead of the usual 0.1-0.5-1.

Start optimizing queries - 0.5-1 in 90% is a lot == excessive load on the CPU / disk.
I would also separate the web and MySQL on different machines, it will be easier to analyze and optimize.
Plus - create a replica and send requests for reading there.

Answer 3

Hi. process needs to be optimized. I'll give you advice. Doesn't optimize blindly. Here is the XHProf tool to help you understand the bottlenecks. Can be used in combat. https://github.com/tideways/php-xhprof-extension

Answer 4

Intermediate answer
So far limited to the following measures:
- Removed query profiling (it was included all this time, it turned out since the development);
- corrected cache settings, etc. for mysql according to recommendations from highload.ru .;
- lowered apache's gzip compression ratio from 9 to 3 (this is what i think is the most impactful change to apache's brakes right now).
At the same time, as it turned out, not 70, but 1000 requests per second are made. I can’t say how I counted before - I can’t say :).
Of this 1000 percent, 90% are repeated attempts to queue send / receive data from a third-party system that has recently failed. But this is a separate load from background scripts, it does not go through the Apache that devours the processor.
However, even now at this 1000 requests per second with the above changes, everything works very stable. The download of the processor is on average 30-50% and only from Apache. The amount of memory consumed has not increased.
Included a log of all requests. Block times are microscopic. I think that the main reason for drawdowns is the stage of the web server.
Of course, it is necessary to revise the architecture, spread the bases for reading and writing, but this is definitely not this summer.
I'll be sure to post as soon as I make any major changes.
Thanks everyone for the helpful tips!

Answer 5

Judging by the symptoms described, the application is the culprit and it seems that the DBMS in this case keeps connections open for a long time. As a result, the web server is forced to hold a message with the client until it receives a response from the application. The DBMS may be missing the necessary indexes on the tables, causing it to do more work.
Without application monitoring, there will be no complete picture. The first step is to integrate NewRelic or equivalent. Then it will be clear which queries are expensive on visual graphs and which parts need to be optimized or completely redone.
Only then make a decision about separating the DBMS and the web server from the web application.