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. mpm_event,fcgid, proxy and 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?

  • This needs to be moved to stackoverflow. – Rick James Feb 5 '19 at 1:13
  • I found a similar question :serverfault.com/questions/558077/… – Rohith Feb 5 '19 at 4:44
  • Yes, the thundering herd scenario is a possibility. It is best to keep Apache's max-clients low enough to prevent having hundreds of connections to MySQL stumbling over each other. What is the value of Max_used_connections during a spike (or at least before it crashes)? – Rick James Feb 5 '19 at 5:23
  • Current Max_used_connections is 13. – Rohith Feb 7 '19 at 11:50
  • Additional information request from your APACHE server. Post on pastebin.com and share the links. RAM size of your MySQL Host server A) complete (not edited) my.cnf or my.ini Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND very helpful information includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, from your APACHE server for tuning analysis. – Wilson Hauck Feb 14 '19 at 11:58
  • Use InnoDB, not MyISAM. This may be the answer to the locking.
  • Set innodb_buffer_pool_size appropriately. How much RAM do you have?
  • Consider building and maintaining "Summary table"; this should eliminate the need 30-40 second queries.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.