Re: Sudden connection and load average spikes with postgresql 9.3

Dear Tom,
Thanks for your fast approach.

First of all, yes, queries seems to take more time to process and they are
like queued up (you can even see inserts with status waiting on top/htop).

I didn't know about that connection tip, and I will absolutely find a moment
to add a pg_pooler to reduce the amount of active/idle connections shown at
the database.

I'm monitoring the pg_stat_activity table and when connections raise there's
no query being processed that could block a table and cause the other
queries to stack waiting. I'm checking pg_lock table too, with results to be
determined yet.

At least for the moment, it has spiked up once again (it's happening with
more frequency). I was able to catch this log from I/O. These are 24 300Gb
SAS disks on Raid 10 with hot spare.

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 42,00 136,00 96,00 136 96
avg-cpu: %user %nice %system %iowait %steal %idle
14,82 0,00 52,75 0,61 0,00 31,82
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 598,00 1764,00 15188,00 1764 15188
avg-cpu: %user %nice %system %iowait %steal %idle
17,79 0,00 82,19 0,01 0,00 0,01
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 230,00 28,00 6172,00 28 6172
avg-cpu: %user %nice %system %iowait %steal %idle
26,79 0,00 72,49 0,12 0,00 0,60
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 200,00 96,00 8364,00 96 8364
avg-cpu: %user %nice %system %iowait %steal %idle
17,88 0,00 81,95 0,01 0,00 0,15
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 87,00 48,00 4164,00 48 4164
avg-cpu: %user %nice %system %iowait %steal %idle
26,09 0,00 66,86 1,69 0,00 5,36
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 563,00 1932,00 9004,00 1932 9004
avg-cpu: %user %nice %system %iowait %steal %idle
19,30 0,00 80,38 0,10 0,00 0,21
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 159,00 348,00 5292,00 348 5292
avg-cpu: %user %nice %system %iowait %steal %idle
28,15 0,00 68,03 0,19 0,00 3,63
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 250,00 196,00 7388,00 196 7388
avg-cpu: %user %nice %system %iowait %steal %idle
11,03 0,00 38,68 0,14 0,00 50,16
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 294,00 1508,00 2916,00 1508 2916

Hope it helps. If you feel we should reduce shared_buffers, what would be a
nice value?

Thanks!

--
View this message in context: http://postgresql.nabble.com/Sudden-connection-and-load-average-spikes-with-postgresql-9-3-tp5855895p5855900.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.