Re: Sudden connection and load average spikes with postgresql 9.3

Dear Josh,
I'm sorry I didn't write before, but we have been very busy with this issue
and, you know, when something goes wrong, the apocalypse comes with it.

I've been working on everything you suggested.

I used your tables and script and I can give you a sample of it on
locked_query_start
2015-07-02 14:49:45.972129+02 | 15314 | | 4001 |
| "TABLE_Z" | tuple | ExclusiveLock | 24018:24 | relation
| ShareUpdateExclusiveLock | | | YYY.YYY.YYY.YYY/32
| 2015-07-02 14:49:26.635599+02 | 2015-07-02 14:49:26.635599+02 | 2015-07-02
14:49:26.635601+02 | INSERT INTO "TABLE_X" ("field1", "field2", "field3",
"field4", "field5", "field6", "field7") VALUES (22359509, 92, 5, 88713,
'XXX.XXX.XXX.XXX', 199, 10) | | | 2015-07-02
14:11:45.368709+02 | 2015-07-02 14:11:45.368709+02 | active |
2015-07-02 14:11:45.36871+02 | autovacuum: VACUUM ANALYZE public.TABLE_Z

2015-07-02 14:49:45.972129+02 | 15857 | | 4001 |
| "TABLE_Z" | tuple | ExclusiveLock | 24018:24 | relation
| ShareUpdateExclusiveLock | | | YYY.YYY.YYY.YYY/32
| 2015-07-02 14:49:22.79166+02 | 2015-07-02 14:49:22.79166+02 | 2015-07-02
14:49:22.791665+02 | INSERT INTO "TABLE_X" ("field1", "field2", "field3",
"field4", "field5", "field6", "field7") VALUES (14515978, 92, 5, 88713,
'XXX.XXX.XXX.XXX', 199, 10) | | | 2015-07-02
14:11:45.368709+02 | 2015-07-02 14:11:45.368709+02 | active |
2015-07-02 14:11:45.36871+02 | autovacuum: VACUUM ANALYZE public.TABLE_Z

2015-07-02 14:49:45.972129+02 | 15314 | | 14712 |
| "TABLE_Z" | tuple | ExclusiveLock | 24018:24 | relation
| AccessShareLock | | |
1YYY.YYY.YYY.YYY/32 | 2015-07-02 14:49:26.635599+02 | 2015-07-02
14:49:26.635599+02 | 2015-07-02 14:49:26.635601+02 | INSERT INTO "TABLE_X"
("field1", "field2", "field3", "field4", "field5", "field6", "field") VALUES
(22359509, 92, 5, 88713, 'XXX.XXX.XXX.XXX', 199, 10) | |
185.10.253.72/32 | 2015-07-02 14:48:48.841375+02 | 2015-07-02
14:48:48.841375+02 | active | 2015-07-02 14:48:48.841384+02 | INSERT
INTO "TABLE_Y" ("email_id", "sendout_id", "feed_id", "isp_id") VALUES
(46015879, 75471, 419, 0)

All that was recorded during a spike. From this log I have to point
something:
Tables TABLE_X and TABLE_Y have both a TRIGGER that does an INSERT to
TABLE_Z
As you can see, TABLE_Z was being VACUUM ANALYZED. I wonder if TRIGGERS and
VACUUM work well together, just to check another perspective.

We also have carefully looked at our scripts and we have performed some code
optimitzations (like close db connections earlier), but the spikes continue
to happen.

FS is ext4 and I don't know how can I check the transaction log
configuration

This is how IO lookslike before and after any problem happens:
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 357,00 7468,00 8840,00 7468 8840
avg-cpu: %user %nice %system %iowait %steal %idle
5,02 0,00 2,44 0,06 0,00 92,47
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 471,00 7032,00 13760,00 7032 13760
avg-cpu: %user %nice %system %iowait %steal %idle
5,14 0,00 2,92 0,03 0,00 91,92
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 376,00 7192,00 8048,00 7192 8048
avg-cpu: %user %nice %system %iowait %steal %idle
4,77 0,00 2,57 0,03 0,00 92,63
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 304,00 7280,00 8252,00 7280 8252

And this is how it looks like when the spike happens:
http://pastebin.com/2hAYuDZ5

Hope it can help into determining what's happening.

Thanks for all your efforts and collaboration!
Eudald

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