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.