1

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

i-CONICA
  • 648
  • 1
  • 9
  • 22
  • 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
  • It's almost always I/O :-) Look into general mysql optimization, both config file (you have lots of RAM, use it) and database design (using indexes etc). It may also be hardware issue, check with `hdparm` or similar for some insight. – LinuxDevOps Mar 25 '14 at 19:49
  • You're right, I've loosened some of the strict "must not loose a thing if the plug is pulled" options in the my.cnf and reduced disk IO from the 40% average in the IOTop down to just 2%. I'll let it run and gather some new averages, warm up caches, etc and we'll see! :D Thanks – i-CONICA Mar 25 '14 at 19:57
  • WOW! Give me 10 minutes, then wait until you see this graph. That's categorically the answer... Disk IO. Even if you think it's low, or that it is low and isn't the problem, it probably damn well is. :D – i-CONICA Mar 25 '14 at 20:01
  • Yeah. Absolutely. IO IO IO IO IO ;) Generally it always is this. – TomTom Mar 25 '14 at 20:05
  • 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
  • glad to help ;-) also I see that you use memcache, you can ramp up also its memory setting – LinuxDevOps Mar 25 '14 at 20:11
  • Thanks, by default it's set to 64M, I've set it to 1024M, considering the small size of the app, I thought that was overkill. Do you know of any other ways memcache can be sped up? – i-CONICA Mar 25 '14 at 20:17
  • (someone post an answer) – ewwhite Mar 25 '14 at 22:21
  • ^^ Yes, LinuxDevOps, can you post a summary of your advice as an answer, thanks. Regarding memcache, I don't know why it was slow, but I've disabled it, and I'm averaging 40ms now, it was giving me nothing, just slowing me down. – i-CONICA Mar 26 '14 at 09:13

0 Answers0