[9.4] SELECT repeatedly rewrites a table

From: Marko Kreen <markokr(at)gmail(dot)com>
To: Postgres Bugs List <pgsql-bugs(at)postgresql(dot)org>
Subject: [9.4] SELECT repeatedly rewrites a table
Date: 2015-07-17 11:22:59
Message-ID: 20150717112259.GA21878@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Attachment Content-Type Size
strace.txt.gz application/octet-stream 2.3 KB
backtrace.txt.gz application/octet-stream 2.7 KB
postgresql.conf text/plain 1.8 KB

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Sandeep Thakkar 2015-07-17 12:28:19 Re: BUG #13500: Windows binary zip doesn't include libintl.h
Previous Message priyanka 2015-07-17 10:45:07 BUG #13505: Create Extension