Re: Logical replication performance
Em sex., 29 de nov. de 2019 às 17:06, Florian Philippon < florian.philip...@doctolib.com> escreveu: > Hello community! > > Hi Florian > We are currently testing PostgreSQL 11's built-in logical replication. We > are trying to initialize a subscriber (from scratch) from a publisher with > a large database (+6TB) with around 220 tables. > > We tweaked the configuration parameters below, both on publisher and > subscriber, in order to minimize the initial copy data phase delay: > > - max_replication_slots > - max_wal_senders > - max_wal_size > - max_worker_processes > - max_logical_replication_workers > - max_sync_workers_per_subscription > - max_worker_processes > > The two PostgreSQL instances are using the same hardware: 48 vCPU, 384 GB > ram, 10GB network and same version of software (PostgreSQL 11.6). > > We pre-loaded the full schema of the database (with indexes and > constraints) on the subscriber since it's mandatory to have the logical > replication working. > > However, the initial copy data phase is quite long (+2 days and still > running) for largest tables in the database. There is no load on the > publisher since it's a staging environment. > We noticed that logical replication workers processes on the subscriber > can reach more than 90% CPU usage per worker. > > We understand that we cannot have more than one worker per table running > but we would like to know if there is anything that could help us to > achieve this initial copy phase more quickly. > > We tried another solution: we loaded a minimal schema (without indexes and > constraints) on the subscriber and created the subscription. The initial > copy phase was way faster (a few hours). Then we created indexes and > constraints. Is this a suitable solution for production? Will the logical > replication flow be buffered by the replication slots during index creation > and get in sync afterwards or will it conflict due to locking issues? > > You can try the pg_dump over a snapshot and use parallel restore (pg_restore -j option) to your initial data load, it should be much faster than an initial sync. Take a look here: https://www.postgresql.org/docs/11/logicaldecoding-explanation.html#id-1.8.14.8.5 Best, Flavio
Logical replication performance
Hello community! We are currently testing PostgreSQL 11's built-in logical replication. We are trying to initialize a subscriber (from scratch) from a publisher with a large database (+6TB) with around 220 tables. We tweaked the configuration parameters below, both on publisher and subscriber, in order to minimize the initial copy data phase delay: - max_replication_slots - max_wal_senders - max_wal_size - max_worker_processes - max_logical_replication_workers - max_sync_workers_per_subscription - max_worker_processes The two PostgreSQL instances are using the same hardware: 48 vCPU, 384 GB ram, 10GB network and same version of software (PostgreSQL 11.6). We pre-loaded the full schema of the database (with indexes and constraints) on the subscriber since it's mandatory to have the logical replication working. However, the initial copy data phase is quite long (+2 days and still running) for largest tables in the database. There is no load on the publisher since it's a staging environment. We noticed that logical replication workers processes on the subscriber can reach more than 90% CPU usage per worker. We understand that we cannot have more than one worker per table running but we would like to know if there is anything that could help us to achieve this initial copy phase more quickly. We tried another solution: we loaded a minimal schema (without indexes and constraints) on the subscriber and created the subscription. The initial copy phase was way faster (a few hours). Then we created indexes and constraints. Is this a suitable solution for production? Will the logical replication flow be buffered by the replication slots during index creation and get in sync afterwards or will it conflict due to locking issues? Many thanks for your help. -- Florian Philippon
Re: Considerable performance downgrade of v11 and 12 on Windows
Laurenz, There is no way to run copy without the "type" on v11. See this thread https://www.postgresql.org/message-id/CAKHmqNCfTMM6%3DPqc6RUMEQ_2BPfo5KGGG-0fzRXZCVooo%3DwdNA%40mail.gmail.com My machine is running on NVMe disks, so the I/O subsystem very strong. The 100% overload is not constant but periodical, as if there are some kind of dumps for recovery performed in the background. maintenance_work_mem is the same in both cases. Regards пт, 29 нояб. 2019 г. в 15:04, Laurenz Albe : > > On Fri, 2019-11-29 at 13:04 +0300, Eugene Podshivalov wrote: > > I'm using PostgreSQL on Windows for Planet OSM database and have > > noticed considirable decrease in performance when upgrading from v10 > > to 11 or 12. Here are the details of the experiment I conducted trying > > to figure out what is causing the issue. > > > > Installed PostgreSQL 10 from scratch. Created a database and a table. > > [...] > > SET synchronous_commit TO OFF; > > COPY ways FROM 'E:\ways.txt'; > > ALTER TABLE ONLY ways ADD CONSTRAINT pk_ways PRIMARY KEY (id); > > > > The file is 365GB in size. > > > > The copy operation took 3.5h and the resulting table size is 253GB. > > The primary key operation took 20 minutes and occuped 13GB of disk > > space. > > > > Then I unstalled PostgreSQL v10, deleted the data directory and > > installed v11 from scratch. Created the same kind of database and > > table. v11 is not able to handle large files, so the I piped the data > > through the cmd type command, and then added the primary key with the > > same command as above. synchronous_commit turned off beforehand as > > above. > > > > COPY ways FROM PROGRAM 'cmd /c "type E:\ways.txt"'; > > > > The copy operation took 7 hours and adding primary key took 1h 40m ! > > The resulting table and pk sizes are the same as in v10. Also very > > high load on disk drive (quite often at 100%) was observed. > > > > v12 performs the same as v11. > > > > Here are the changes in v11 default postgresql.conf file compared to > > v10 one. Differences in Authentication, Replication and Logging > > sections are skipped. > > > > -#replacement_sort_tuples = 15 > > +#max_parallel_maintenance_workers = 2 > > +#parallel_leader_participation = on > > ~max_wal_size = 1GB (in v10 is commented out) > > ~min_wal_size = 80MB(in v10 is commented out) > > +#enable_parallel_append = on > > +#enable_partitionwise_join = off > > +#enable_partitionwise_aggregate = off > > +#enable_parallel_hash = on > > +#enable_partition_pruning = on > > +#jit_above_cost = 10 > > +#jit_inline_above_cost = 50 > > +#jit_optimize_above_cost = 50 > > +#jit = off > > +#jit_provider = 'llvmjit' > > +#vacuum_cleanup_index_scale_factor = 0.1 > > > > Any ideas pleaes on what is trapping the performance? > > Seems like you have a very weak I/O subsystem. > > For the COPY, try doing it the same way in both cases (without the "type"). > > For the index creation, perhaps set "max_parallel_maintenance_workers = 0" > so that your system doesn't get overloaded. > > Is "maintenance_work_mem" set to the same value in both cases? > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com >
Re: Considerable performance downgrade of v11 and 12 on Windows
On Fri, 2019-11-29 at 13:04 +0300, Eugene Podshivalov wrote: > I'm using PostgreSQL on Windows for Planet OSM database and have > noticed considirable decrease in performance when upgrading from v10 > to 11 or 12. Here are the details of the experiment I conducted trying > to figure out what is causing the issue. > > Installed PostgreSQL 10 from scratch. Created a database and a table. > [...] > SET synchronous_commit TO OFF; > COPY ways FROM 'E:\ways.txt'; > ALTER TABLE ONLY ways ADD CONSTRAINT pk_ways PRIMARY KEY (id); > > The file is 365GB in size. > > The copy operation took 3.5h and the resulting table size is 253GB. > The primary key operation took 20 minutes and occuped 13GB of disk > space. > > Then I unstalled PostgreSQL v10, deleted the data directory and > installed v11 from scratch. Created the same kind of database and > table. v11 is not able to handle large files, so the I piped the data > through the cmd type command, and then added the primary key with the > same command as above. synchronous_commit turned off beforehand as > above. > > COPY ways FROM PROGRAM 'cmd /c "type E:\ways.txt"'; > > The copy operation took 7 hours and adding primary key took 1h 40m ! > The resulting table and pk sizes are the same as in v10. Also very > high load on disk drive (quite often at 100%) was observed. > > v12 performs the same as v11. > > Here are the changes in v11 default postgresql.conf file compared to > v10 one. Differences in Authentication, Replication and Logging > sections are skipped. > > -#replacement_sort_tuples = 15 > +#max_parallel_maintenance_workers = 2 > +#parallel_leader_participation = on > ~max_wal_size = 1GB (in v10 is commented out) > ~min_wal_size = 80MB(in v10 is commented out) > +#enable_parallel_append = on > +#enable_partitionwise_join = off > +#enable_partitionwise_aggregate = off > +#enable_parallel_hash = on > +#enable_partition_pruning = on > +#jit_above_cost = 10 > +#jit_inline_above_cost = 50 > +#jit_optimize_above_cost = 50 > +#jit = off > +#jit_provider = 'llvmjit' > +#vacuum_cleanup_index_scale_factor = 0.1 > > Any ideas pleaes on what is trapping the performance? Seems like you have a very weak I/O subsystem. For the COPY, try doing it the same way in both cases (without the "type"). For the index creation, perhaps set "max_parallel_maintenance_workers = 0" so that your system doesn't get overloaded. Is "maintenance_work_mem" set to the same value in both cases? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Considerable performance downgrade of v11 and 12 on Windows
I don't think so. Why adding primary key shows the same downgraded performance as well then? пт, 29 нояб. 2019 г. в 13:37, Thomas Kellerer : > > Eugene Podshivalov schrieb am 29.11.2019 um 11:04: > > Imported ways data from a file and added a primary key. > > > > SET synchronous_commit TO OFF; > > COPY ways FROM 'E:\ways.txt'; > > > ... > > COPY ways FROM PROGRAM 'cmd /c "type E:\ways.txt"'; > > Those two commands are not doing the same thing - the piping through the TYPE > command is most probably eating all the performance > > > >
Re: Considerable performance downgrade of v11 and 12 on Windows
Eugene Podshivalov schrieb am 29.11.2019 um 11:04: > Imported ways data from a file and added a primary key. > > SET synchronous_commit TO OFF; > COPY ways FROM 'E:\ways.txt'; > ... > COPY ways FROM PROGRAM 'cmd /c "type E:\ways.txt"'; Those two commands are not doing the same thing - the piping through the TYPE command is most probably eating all the performance
Considerable performance downgrade of v11 and 12 on Windows
Hi, I'm using PostgreSQL on Windows for Planet OSM database and have noticed considirable decrease in performance when upgrading from v10 to 11 or 12. Here are the details of the experiment I conducted trying to figure out what is causing the issue. Installed PostgreSQL 10 from scratch. Created a database and a table. CREATE TABLE ways ( id bigint NOT NULL, version int NOT NULL, user_id int NOT NULL, tstamp timestamp without time zone NOT NULL, changeset_id bigint NOT NULL, tags hstore, nodes bigint[] ); Imported ways data from a file and added a primary key. SET synchronous_commit TO OFF; COPY ways FROM 'E:\ways.txt'; ALTER TABLE ONLY ways ADD CONSTRAINT pk_ways PRIMARY KEY (id); The file is 365GB in size. The copy operation took 3.5h and the resulting table size is 253GB. The primary key operation took 20 minutes and occuped 13GB of disk space. Then I unstalled PostgreSQL v10, deleted the data directory and installed v11 from scratch. Created the same kind of database and table. v11 is not able to handle large files, so the I piped the data through the cmd type command, and then added the primary key with the same command as above. synchronous_commit turned off beforehand as above. COPY ways FROM PROGRAM 'cmd /c "type E:\ways.txt"'; The copy operation took 7 hours and adding primary key took 1h 40m ! The resulting table and pk sizes are the same as in v10. Also very high load on disk drive (quite often at 100%) was observed. v12 performs the same as v11. Here are the changes in v11 default postgresql.conf file compared to v10 one. Differences in Authentication, Replication and Logging sections are skipped. -#replacement_sort_tuples = 15 +#max_parallel_maintenance_workers = 2 +#parallel_leader_participation = on ~max_wal_size = 1GB (in v10 is commented out) ~min_wal_size = 80MB(in v10 is commented out) +#enable_parallel_append = on +#enable_partitionwise_join = off +#enable_partitionwise_aggregate = off +#enable_parallel_hash = on +#enable_partition_pruning = on +#jit_above_cost = 10 +#jit_inline_above_cost = 50 +#jit_optimize_above_cost = 50 +#jit = off +#jit_provider = 'llvmjit' +#vacuum_cleanup_index_scale_factor = 0.1 Any ideas pleaes on what is trapping the performance? Regards
