I've a strange problem that's repeatedly troubled me across 3 different servers now.

The problem

Please see the graph from NewRelic showing the huge amount of time spent in the database. enter image description here

The hardware;

Originally, I had a powerful but oversubscribed VPS, which is what I put the problem down to. I then moved to a fully dedicated server, which wasn't very powerful (but mine all mine), dual core Athlon, 4GB ram, 250G disk, Ubuntu 13.10, 100Mb/s connection.

I've now moved onto a fully dedicated box with a Xeon E3-1270 V2 3.5GHz, 4 core, 8 logical cores, 16GB DDR3 ram, 1TB disk, Ubuntu 13.10, 1Gb/s connection. The server's load average never exceeds 0.08.

The application

It's a simple, small PHP SMF forum.

The database

...is 20MB in total, 100 tables, mostly InnoDB. It's read-mostly, and all simple queries, getting and setting data, no "work" being done in the DB.

The database is Percona MySQL 5.6; Here's it's config; http://pastebin.com/yYCawtby

Slow query logs are enabled; there are no "slow queries".

If anyone can offer advice on how I might troubleshoot the issue, I'd much appreciate it. Thanks.

EDIT: IOTop output; enter image description here

EDIT2: CPU / MEM; enter image description here

EDIT3: The result; enter image description here

  • 1
    Disk Input/Output is the main suspect here. Use top and look out for processes in STAT "D" (waiting for I/O). Outputs from tools that look at disk read/writes like vmstat, iotop etc can also point to disk I/O issue from mysql. Look at general logs (kern, syslog) and mysql logs. With such a small data set, it could be all cached in RAM for reads. – LinuxDevOps Mar 25 '14 at 19:23
  • Hi, I've updated my answer with a screenshot from iotop. Thanks – i-CONICA Mar 25 '14 at 19:33
  • With 20mb files that is unliukely. What about CPU usage? Not having indices and the database doing very inefficient query plans (loops in loops) on joins would be my idea here. – TomTom Mar 25 '14 at 19:36
  • Thanks, the CPU is idling. It's such an incredibly quick CPU for this application it barely budges above zero. The memory is about 10%. Disk IO about 50% on average. I think you're right, the my.cnf config looks to be being very "cautious" and safe, and is causing lots of disk flushes. I've updated with another graph. – i-CONICA Mar 25 '14 at 19:41
  • 1
    Look at that final graph. That's exactly what I wanted to see. There are other subtle tweaks I can make to queries, indices, etc, but they're all marginal gains and were pointless while I had this major issue. Thanks. – i-CONICA Mar 25 '14 at 20:07

Your Answer

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

Browse other questions tagged or ask your own question.