Re: Performance penalty during logical postgres replication
Hi, - on the receiving side, avoid creating indexes on the tables: create just > a necessary PK or UK, wait for the initial load to complete and then add > all the rest ones > Thanks, this is a good tip. We are going to add this We also noticed the code that was getting the next from the "queue" was doing the query with 'select for update', but without a 'SKIP LOCKED'. This is probably also something that caused more wait time if the server is more busy as usual during the replication. So we are going to add this and try again. We are also minimizing load on the queue during initial replication. On to the next try. Lars On Wed, Dec 9, 2020 at 6:45 PM Victor Yegorov wrote: > ср, 9 дек. 2020 г. в 10:21, Lars Vonk : > >> We are doing a logical postgres replication from Postgres 11 to 12. Our >> database is around 700GB (8 cpu's, 32 GB). >> During the replication process, at some point, we see a huge performance >> penalty on a particular table. This table acts as a queue with lots of >> inserts and deletes happening throughout the day. For most of the time this >> table is empty, but during this performance penalty the number of rows in >> this table grows to 10.000 rows, and processing is not fast enough to empty >> this table. Main reason for this (as far as we see) is that the performance >> of the query for selecting the next row to process drops from < 10MS to >> 400MS. This eventually causes too much cpu load on the Primary and we have >> to cancel the replication process. >> > We already tried the initial load three times, and it consistently fails >> with the same "error". Last try was a per table approach and excluding this >> "queue" table. >> After cancelling the replication the query is fast again and the load on >> the Primary goes back to normal. We see that this happens when replicating >> large tables (> millions of rows). During this performance penalty the >> explain of the query selecting the next row from this table tells us it is >> doing a sequential scan (there is an index but it is not used). >> >> - What could cause this performance penalty? >> - Is this something other people experienced as well during the initial >> load of a logical replication with large tables? >> - We are now thinking of temporarily increasing the number of CPU's and >> RAM for the migration. Would this help in this case? >> > > I've seen similar symptoms in cases with (a) home-made queues in the > tables and (b) long transactions. > Unfortunately, queue requires frequent vacuuming to preserve more or less > constant size of the queue and it's indexes. > And long transactions prevent the vacuum from cleaning up the queue. > Initial synchronization phase of the logical replication is in fact such a > transaction. > > I would recommend doing the following: > - avoid adding ALL tables to the publication > - instead, split all tables in a batches in such a way, that initial batch > processing takes limited time (say, 15-30 minutes at most) > - of course, this leaves the biggest tables alone — add those one by one > to the publication, preferably at the time slot with minimal load on the > queue. > - make sure to catch up on the queue processing and vacuum it between > batches > - on the receiving side, avoid creating indexes on the tables: create just > a necessary PK or UK, wait for the initial load to complete and then add > all the rest ones > > As for the queue, PGQ from skytools is using different approach to > maintain queue tables: > - once in a while (2 hours by default) processing is switched to a new > table, tab_1, tab_2, tab_3 are used in a round > - after the switch, any remaining entries can be moved from previous to > the live table (shouldn't be necessary if switch is done properly, although > might be tricky in a presence of a long transactions) > - previous table is TRUNCATEd > > In your case, you can do `VACUUM FULL` between replicating each batch of > tables. > > -- > Victor Yegorov >
Re: Performance penalty during logical postgres replication
It has been 4 hours and it is safe to say that the measurements we took have a huge positive effect: > 30 times faster and no noticeable effect on the running Primary at all. A 20GB table is now replicated under 10 minutes. - We removed all non PK and unique indices from the large tables - We the changed the query on the queue table to add the 'SKIP LOCKED' clause. - We do a per table approach for the larger tables. I think the indices have the most significant impact, but not sure how to proof this since we did multiple changes at the same time. Thanks again for the tips! -- Lars On Thu, Dec 10, 2020 at 9:12 AM Lars Vonk wrote: > Hi, > > - on the receiving side, avoid creating indexes on the tables: create just >> a necessary PK or UK, wait for the initial load to complete and then add >> all the rest ones >> > > Thanks, this is a good tip. We are going to add this > > We also noticed the code that was getting the next from the "queue" was > doing the query with 'select for update', but without a 'SKIP LOCKED'. This > is probably also something that caused more wait time if the server is > more busy as usual during the replication. So we are going to add this and > try again. We are also minimizing load on the queue during initial > replication. > > On to the next try. > > Lars > > On Wed, Dec 9, 2020 at 6:45 PM Victor Yegorov wrote: > >> ср, 9 дек. 2020 г. в 10:21, Lars Vonk : >> >>> We are doing a logical postgres replication from Postgres 11 to 12. Our >>> database is around 700GB (8 cpu's, 32 GB). >>> During the replication process, at some point, we see a huge performance >>> penalty on a particular table. This table acts as a queue with lots of >>> inserts and deletes happening throughout the day. For most of the time this >>> table is empty, but during this performance penalty the number of rows in >>> this table grows to 10.000 rows, and processing is not fast enough to empty >>> this table. Main reason for this (as far as we see) is that the performance >>> of the query for selecting the next row to process drops from < 10MS to >>> 400MS. This eventually causes too much cpu load on the Primary and we have >>> to cancel the replication process. >>> >> We already tried the initial load three times, and it consistently fails >>> with the same "error". Last try was a per table approach and excluding this >>> "queue" table. >>> After cancelling the replication the query is fast again and the load on >>> the Primary goes back to normal. We see that this happens when replicating >>> large tables (> millions of rows). During this performance penalty the >>> explain of the query selecting the next row from this table tells us it is >>> doing a sequential scan (there is an index but it is not used). >>> >>> - What could cause this performance penalty? >>> - Is this something other people experienced as well during the initial >>> load of a logical replication with large tables? >>> - We are now thinking of temporarily increasing the number of CPU's and >>> RAM for the migration. Would this help in this case? >>> >> >> I've seen similar symptoms in cases with (a) home-made queues in the >> tables and (b) long transactions. >> Unfortunately, queue requires frequent vacuuming to preserve more or less >> constant size of the queue and it's indexes. >> And long transactions prevent the vacuum from cleaning up the queue. >> Initial synchronization phase of the logical replication is in fact such a >> transaction. >> >> I would recommend doing the following: >> - avoid adding ALL tables to the publication >> - instead, split all tables in a batches in such a way, that initial >> batch processing takes limited time (say, 15-30 minutes at most) >> - of course, this leaves the biggest tables alone — add those one by one >> to the publication, preferably at the time slot with minimal load on the >> queue. >> - make sure to catch up on the queue processing and vacuum it between >> batches >> - on the receiving side, avoid creating indexes on the tables: create >> just a necessary PK or UK, wait for the initial load to complete and then >> add all the rest ones >> >> As for the queue, PGQ from skytools is using different approach to >> maintain queue tables: >> - once in a while (2 hours by default) processing is switched to a new >> table, tab_1, tab_2, tab_3 are used in a round >> - after the switch, any remaining entries can be moved from previous to >> the live table (shouldn't be necessary if switch is done properly, although >> might be tricky in a presence of a long transactions) >> - previous table is TRUNCATEd >> >> In your case, you can do `VACUUM FULL` between replicating each batch of >> tables. >> >> -- >> Victor Yegorov >> >
Re: Set COLLATE on a session level
-- Dirk Mika Software Developer mika:timing GmbH Strundepark - Kürtener Str. 11b 51465 Bergisch Gladbach Germany fon +49 2202 2401-1197 dirk.m...@mikatiming.de www.mikatiming.de AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884 Geschäftsführer: Harald Mika, Jörg Mika Am 06.12.20, 06:15 schrieb "Laurenz Albe" : On Sat, 2020-12-05 at 13:12 +0100, Peter Eisentraut wrote: > On 2020-12-04 17:18, Tom Lane wrote: > > > There is a SET COLLATION command in the SQL standard that does this. > > > Someone just has to implement it. It wouldn't be terribly difficult, I > > > think. > > > > [ squint... ] Just because it's in the standard doesn't mean it's a > > good idea. It sounds like this is morally equivalent to a GUC that > > changes query semantics. We have learned painfully that such behaviors > > are best avoided, because they break things you didn't expect to break. > > I think it would be analogous to the schema search path. Usually you notice right away if the "search_path" is wrong, because relations won't be found. But with a bad collation set in a session, the problems would be more subtle. For example, if someone asks you why an index isn't used to support sorting, you'd always have to remember to ask what collation has been set in the session. This is true, but it is already the case in Oracle. There it is usually even the case that an index on a VARCHAR2 column is not used for sorting, since the index is binary sorted, but the language setting is usually not. The SET COLLATION command would be exactly what we're looking for. ( BR Dirk
Re: Extended statistics for correlated columns, row estimates when values are not in MCVs list
On Mon, Dec 7, 2020 at 8:31 AM Tomas Vondra wrote: > What you might try is defining the statistics with only the functional > dependencies. That should consider the column-level correlation even > when the combination of values is not in the MCV. It might make the > "good" estimate worse, but that's an inherent trade-off. > Thanks very much. Removing MCVs type works perfectly for my use cases.
Re: JDBC driver - is "getGeneratedKeys()" guaranteed to return the ids in the same order a batch insert was made?
On Wed, Dec 9, 2020 at 1:31 PM electrotype wrote: > Agreed. > > > However, this isn't really the purview of JDBC - I'm doubting it does > anything that would cause the order to be different than what is received, > and the batch items are sent and results processed sequentially. > > The main question is whether any batch items are inserting multiple > records themselves - i.e., RETURNING * is producing multiple results. > Whatever order RETURNING * produces is what the driver will capture - but > it isn't responsible for guaranteeing that the order of multiple inserted > records going in matches what comes out. PostgreSQL needs to make that > claim. I don't see where it does (i've sent an email to see if adding such > a claim to the documentation is proper). Done manually one can always do > "WITH insert returning SELECT ORDER BY", but it doesn't seem workable for > the driver to try and do that when adding the returning clause, which I > presume is what is in scope here. > > David J. > > Thank you, it's appreciated! I'm sure this clarification would help other > developers too. > My take is that there is presently no guarantee, and that with current efforts to add parallelism it is quite probable that observation of such non-orderedness is simply a matter of time. With batching it seems best to combine its use with single inserts in order to avoid this problem. David J.
