I'm really struggling with a performance issue currently, where all my database queries takes up to a 1000 times more than normally, when my database server is under "heavy" load.
I have a server that runs ubuntu 12.04 and hosts my postgresql 9.2 database. The server is on Hetzner.de and it is a EX6s (8 core xenon with 32Gb ram and two 3T HDD's in a RAID1 setup using ubuntus software raid). When I run a high volume of queries (which I often done in the night) then I see that almost all CPU usage is spend on CPU I/O wait. I have new relic monitoring installed, and I can't seem to find any other indications of what could be the root cause of this CPU I/O wait, which clearly is a bottleneck to my performance and throughput.
So the question is, what is this CPU I/O wait and what is it waiting for?
I added All the overview charts I can get hold on from new relic beneath. What am I overlooking? There must be an obvious bottleneck? Where should I dive in?
Database server CPU usage - The evil one that tells me something is wrong https://rpm.newrelic.com/public/charts/cEdIvvoQZCr
Database server load average https://rpm.newrelic.com/public/charts/cMNdrYW51QJ
Database server physical memory https://rpm.newrelic.com/public/charts/c3dZBntNpa1
Database server disk I/O utulization - as you can see the disk doesn't seem to be utilized very much (almost nothing) https://rpm.newrelic.com/public/charts/9YEVw6RekFG
Database server network I/O (Mb/s) - the network is a gigabit internal network that all communications are made on. https://rpm.newrelic.com/public/charts/lKiZ0Szmwe7
Top 5 database operations by wall clock time https://rpm.newrelic.com/public/charts/dCt45YH12FK
Database throughput https://rpm.newrelic.com/public/charts/bIbtQ1mDzMI
Database response time https://rpm.newrelic.com/public/charts/fPcNL8WA6xx
After doing a
sudo iostat -k 1 I'm starting to get suspicious.
I get a lot of output like this, that I don't see in NR:
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn sda 0.00 0.00 0.00 0 0 sdb 0.00 0.00 0.00 0 0 sdc 546.00 2296.00 6808.00 2296 6808 sdd 593.00 1040.00 7416.00 1040 7416 md1 0.00 0.00 0.00 0 0 md0 0.00 0.00 0.00 0 0 md2 1398.00 3328.00 13064.00 3328 13064 md3 0.00 0.00 0.00 0 0