From: Alexey Kondratov <a.kondratov@postgrespro.ru>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
To: Erik Rijkers <er@xs4all.nl>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Cc: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
Subject: Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions
Date: Tue, 18 Dec 2018 17:07:08 +0300
Message-ID: <126e3a30-a7f5-ede4-2bd0-b217749c8b33@postgrespro.ru> (raw)
In-Reply-To: <b95ba070-cd21-72b9-2979-8cdf4d3dd1c6@2ndquadrant.com>
References: <688b0b7f-2f6c-d827-c27b-216a8e3ea700@2ndquadrant.com>
<bbe1333f-17ba-1156-0713-7cc0bfe2d842@2ndquadrant.com>
<bc83b847-2fc7-6561-4ef1-a3c9d154cc31@2ndquadrant.com>
<47223121-222d-8e04-4928-a28ecdc5327d@2ndquadrant.com>
<CAD21AoBa2mhbhZW6hnDRKs1ggycJOBE69eHk3KgCJ5Cwj9-ZkA@mail.gmail.com>
<b9b306a3-c0a2-7eb9-92eb-a3ca111bd1bc@2ndquadrant.com>
<4762f6bd-b147-cd06-f286-22f54790b77d@2ndquadrant.com>
<cb2530b102afad854e051cca44577c6a@xs4all.nl>
<d807dc44-55f9-5c23-97e5-a5881f4135e2@2ndquadrant.com>
<5eddec05-404f-3fc4-bafd-15e9e4787705@2ndquadrant.com>
<e84013b6-b890-1c4c-2b82-eca185a2197c@2ndquadrant.com>
<8940f374-6651-26d0-cefe-6c719454b6fe@2ndquadrant.com>
<40c38758-04b5-74f4-c963-cf300f9e5dff@postgrespro.ru>
<b95ba070-cd21-72b9-2979-8cdf4d3dd1c6@2ndquadrant.com>
On 18.12.2018 1:28, Tomas Vondra wrote:
>> 4) There was a problem with marking top-level transaction as having
>> catalog changes if one of its subtransactions has. It was causing a
>> problem with DDL statements just after subtransaction start (savepoint),
>> so data from new columns is not replicated.
>>
>> 5) Similar issue with schema send. You send schema only once per each
>> sub/transaction (IIRC), while we have to update schema on each catalog
>> change: invalidation execution, snapshot rebuild, adding new tuple cids.
>> So I ended up with adding is_schema_send flag to ReorderBufferTXN, since
>> it is easy to set it inside RB and read in the output plugin. Probably,
>> we have to choose a better place for this flag.
>>
> Hmm. Can you share an example how to trigger these issues?
Test cases inside 014_stream_tough_ddl.pl and old ones (with
streaming=true option added) should reproduce all these issues. In
general, it happens in a txn like:
INSERT
SAVEPOINT
ALTER TABLE ... ADD COLUMN
INSERT
then the second insert may discover old version of catalog.
> Interesting. Any idea where does the extra overhead in this particular
> case come from? It's hard to deduce that from the single flame graph,
> when I don't have anything to compare it with (i.e. the flame graph for
> the "normal" case).
I guess that bottleneck is in disk operations. You can check
logical_repl_worker_new_perf.svg flame graph: disk reads (~9%) and
writes (~26%) take around 35% of CPU time in summary. To compare,
please, see attached flame graph for the following transaction:
INSERT INTO large_text
SELECT (SELECT string_agg('x', ',')
FROM generate_series(1, 2000)) FROM generate_series(1, 1000000);
Execution Time: 44519.816 ms
Time: 98333,642 ms (01:38,334)
where disk IO is only ~7-8% in total. So we get very roughly the same
~x4-5 performance drop here. JFYI, I am using a machine with SSD for tests.
Therefore, probably you may write changes on receiver in bigger chunks,
not each change separately.
> So I'm not particularly worried, but I'll look into that. I'd be much
> more worried if there was measurable overhead in cases when there's no
> streaming happening (either because it's disabled or the memory limit
> was not hit).
What I have also just found, is that if a table row is large enough to
be TOASTed, e.g.:
INSERT INTO large_text
SELECT (SELECT string_agg('x', ',')
FROM generate_series(1, 1000000)) FROM generate_series(1, 1000);
then logical_work_mem limit is not hit and we neither stream, nor spill
to disk this transaction, while it is still large. In contrast, the
transaction above (with 1000000 smaller rows) being comparable in size
is streamed. Not sure, that it is easy to add proper accounting of
TOAST-able columns, but it worth it.
--
Alexey Kondratov
Postgres Professional https://www.postgrespro.com
Russian Postgres Company
Attachments:
logical_repl_worker_text_stream_perf.zip (application/zip, 42.1K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: a.kondratov@postgrespro.ru, tomas.vondra@2ndquadrant.com, peter.eisentraut@2ndquadrant.com, er@xs4all.nl, sawada.mshk@gmail.com, k.knizhnik@postgrespro.ru
Subject: Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions
In-Reply-To: <126e3a30-a7f5-ede4-2bd0-b217749c8b33@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox