I have multiple PHP applications running under different virtual host on an Apache server. Each application uses MySQL as backend. Each application uses different MySQL servers in the same data center. Some of our application processes a large amount of data from the database to create some aggregate reports, which sometimes takes around 30-40 seconds (We are planning to run this asynchronously in the future). This reporting service uses a set of small queries instead of a large query takes a lot of time. This was intended to reduce the time a single table is locked. Even though each of our application uses separate databases, running this report causes serious performance degradation on other applications hosted on this server. Sometimes even request timeouts are also occurring on other applications.
The RAM/CPU utilisation on the application server is below 50% while running these reports.
I'm using PHP 7.2, on Apache 2.4 using PHP-FPM.
proxy_fcgi modules are enabled on Apache and MySQL version is 5.7
The main reason we are using multiple database servers is to reduce the effect of one application on other. Why this behaviour is happening? and how this can be solved?