Re: BUG #13484: Performance problem with logical decoding

On July 3, 2015 11:02:17 AM GMT+02:00, olivier(dot)gosseaume(at)free(dot)fr wrote:
>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.

Around 4096 transactions are getting spilled to disk.

>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)

That's less than linear growth...

>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")

So you just had 4096 changes here?

>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")

The SQL interface isn't really the best thing to test this - the output as a whole is stored first in memory, and then when getting to large, spilled to disk. Additionally the starting/stopping of the slot can take a long while because EAL may need to be reread.

Please test the same using the streaming interface. You can use pg-recvlogical.

Regards,

Andres

---
Please excuse brevity and formatting - I am writing this on my mobile phone.