Finding rare performance problems on backend

Polyglot Projects LAMP Stack
1

Hi, Percona.

We have very rare problems on our Drupal-based portal (few times per month, mostly under havy load).

Problem symptoms are:

  1. Number of apache processes spikes to maximum.
  2. Number of connections to MySQL spikes to # of Apache processes.
  3. There are no spikes on MySQL qps graph.
  4. Project in not working, but apache restart solves problem.
  5. There is no unreal CPU load on backend.
  6. Before apache restart there is no queries in MySQL (we do “show processlist” before restart).

My questions:

  1. Does empty mysql process list mean that problem is probably on backend?
  2. What steps can we perform to search for performance problems on backend (we can’t do profiling with xdebug on heavy site waiting problem for weeks)? One such rare problem with similar symptoms we found (connecting from our code to slow and unreliable external service).

Thanks in advance.

2

Hi,

The proper instrumentation which is light enough to run in production can be great in such cases. You can see example here:
[URL]Google Code Archive - Long-term storage for Google Code Project Hosting.

This way you can see where from application side the performance goes,
is it to connect to mysql ? run the query. When checking the query times as application sees them and comparing them to how server sees them you will be able to see if there are network issues.

If MySQL server is slow to respond generally queries have to spend a lot of time in the processlist so in majority of cases you can say empty processlist=not mysql issue.