BUG #13484: Performance problem with logical decoding

From: olivier(dot)gosseaume(at)free(dot)fr
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #13484: Performance problem with logical decoding
Date: 2015-07-03 09:02:17
Message-ID: 20150703090217.1190.63940@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 13484
Logged by: Olivier Gosseaume
Email address: olivier(dot)gosseaume(at)free(dot)fr
PostgreSQL version: 9.4.4
Operating system: Windows 7 64 bits (dev system)
Description:

Problem: when a transaction involve more than 4095 operations, logical
decoding on the receiver end become very very slow.

Repro :
Open two psql sessions 1 and 2

On session 1 :
Prepare :
CREATE TABLE data(id serial primary key, data text);

On session 2 (receiver) :
Prepare :
\timing on
SELECT pg_create_logical_replication_slot('my_slot','test_decoding');

Run :
On session 1 : insert into data (data) values (generate_series(1,4095));
On session 2 : select pg_logical_slot_get_changes('my_slot', NULL, NULL);
--> returns in 80mS (plenty fast)

On session 1 : insert into data (data) values (generate_series(1,4095)); -->
repeat this 10 times to insert 40950 rows
On session 2 : select pg_logical_slot_get_changes('my_slot', NULL, NULL);
--> returns in 380mS (plenty fast)

Now the problem :
On session 1 : insert into data (data) values (generate_series(1,4096));
On session 2 : select pg_logical_slot_get_changes('my_slot', NULL, NULL);
--> returns in 4204mS (ie 52x times slower than "normal")

On session 1 : insert into data (data) values (generate_series(1,40950));
On session 2 : select pg_logical_slot_get_changes('my_slot', NULL, NULL);
--> returns in 34998mS (ie 92x times slower than "normal")

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Fujii Masao 2015-07-03 12:26:28 Re: BUG #13368: standby cluster immediately promotes after pg_basebackup from previously promoted master
Previous Message Michael Paquier 2015-07-03 07:03:03 Re: BUG #13368: standby cluster immediately promotes after pg_basebackup from previously promoted master