We recently upgraded 9.3 to 9.4 in live environment and one batch-job
query floods system with write requests. Query itself is basically
seq-scan on insert-only table.
DB: Postgres 9.4.4
OS: Ubuntu 12.04, 64-bit
HW: AWS EC2, EBS, 16G RAM
Story:
1) New VM is created, fresh initdb
2) Data is replicated in, with COPY (Londiste), indexes are created.
3) ANALYZE
4) Let machine run under normal load couple hours. It is not reproducible
immediately on fresh VM.
5) Run query (with DECLARE data_maint_cur NO SCROLL CURSOR WITH HOLD FOR).
The table has seen only inserts thus far. Here is EXPLAIN of normal run:
chatdb=# explain analyze select account_id, max(event_nr) as first_event_nr from account.event where store_time <
now()- '1 month'::interval group by 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=843632.92..843634.31 rows=139 width=24) (actual time=11938.390..12201.234 rows=285135
loops=1)
Group Key: account_id
-> Seq Scan on event (cost=0.00..837636.04 rows=1199378 width=24) (actual time=166.772..10883.828 rows=1049812
loops=1)
Filter: (store_time < (now() - '1 mon'::interval))
Rows Removed by Filter: 24698500
Planning time: 1.194 ms
Execution time: 12406.133 ms
6) So good run is 12s, bad run is ~1h with huge write load. The table is ~3G,
but Postgres seems to repeatedly rewrite those files.
7) It seems when I cancel bad run and VACUUM the table, it will proceed normally.
Attached is strace during bad run and backtrace.
fds for strace:
lrwx------ 1 postgres postgres 64 Jul 16 04:01 17 -> /var/lib/postgresql/9.4/main/base/16385/19297
lrwx------ 1 postgres postgres 64 Jul 16 04:01 18 -> /var/lib/postgresql/9.4/main/base/16385/19297.1
lrwx------ 1 postgres postgres 64 Jul 16 04:01 19 -> /var/lib/postgresql/9.4/main/base/16385/19297.2
--
marko