Re: glibc updarte 2.31 to 2.38
On 9/22/24 12:53, Adrian Klaver wrote: On 9/22/24 09:48, Paul Förster wrote: Hi Joe On 22. Sep, 2024, at 15:47, Joe Conway wrote: Note that moving to ICU might improve things, but there are similar potential issues with ICU as well. The trick there would be to get your OS distro provider to maintain the same ICU version across major versions of the distro, which is not the case currently. Nor does the PGDG repo do that. Then I strongly suggest that the PostgreSQL developers develop a fail safe sorting mechanism that holds for generations of locale changes. https://www.postgresql.org/docs/17/release-17.html#RELEASE-17-HIGHLIGHTS Add a builtin platform-independent collation provider (Jeff Davis) This supports C and C.UTF-8 collations. Yep, what he said -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
Re: How batch processing works
On 2024-09-21 12:15:44 -0700, Adrian Klaver wrote:
> FYI, this is less of problem with psycopg(3) and pipeline mode:
>
[...]
> with db.pipeline():
> for i in range(1, num_inserts+1):
> csr.execute("insert into parent_table values(%s, %s)", (i, 'a'))
> if i % batch_size == 0:
> db.commit()
> db.commit()
[...]
>
> For remote to a database in another state that took the time from:
>
> Method 2: Individual Inserts with Commit after 50 Rows: 2.42e+02 seconds
>
> to:
>
> Method 2: Individual Inserts(psycopg3 pipeline mode) with Commit after 50
> Rows: 9.83 seconds
Very cool. I'll keep that in mind.
I've been using psycopg 3 for newer projects, but for throwaway code
I've been sticking to psycopg2, simply because it's available from the
repos of all my usual distributions. It's now in both Debian and Ubuntu,
so that will change.
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
signature.asc
Description: PGP signature
Re: How batch processing works
On 2024-09-21 20:55:13 +0530, Lok P wrote: > On Sat, Sep 21, 2024 at 8:07 PM Peter J. Holzer wrote: [... lots of code elided. method2 used 1 insert per row, method3 1 insert for 50 rows ...] > On my laptop, method2 is about twice as fast as method3. But if I > connect to a database on the other side of the city, method2 is now more > than 16 times faster than method3 . Simply because the delay in > communication is now large compared to the time it takes to insert those > rows. > > > > Thank you so much. > I was expecting method-3(batch insert) to be the fastest or atleast as you > said > perform with similar speed as method-2 (row by row insert with batch commit) Oops, sorry! I wrote that the wrong way around. Method 3 is the fastest. I guess I meant to write "method2 takes about twice as long as method3" or something like that. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: glibc updarte 2.31 to 2.38
On 2024-09-22 09:53:58 -0700, Adrian Klaver wrote: > On 9/22/24 09:48, Paul Förster wrote: > > Then I strongly suggest that the PostgreSQL developers develop a > > fail safe sorting mechanism that holds for generations of locale > > changes. > > https://www.postgresql.org/docs/17/release-17.html#RELEASE-17-HIGHLIGHTS > > Add a builtin platform-independent collation provider (Jeff Davis) > > This supports C and C.UTF-8 collations. Couldn't you always use "C" collation? The problem with that is that most people[1] want stuff ordered according to the rules of their language and C collation is in most cases very different. hp [1] I actually have LC_COLLATE=POSIX set in the shell. But I'm not normal. -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
start pglogical subscription from a specific lsn
Hi, Is it possible to start a pglogical subscription from a specific lsn? I am on 16.2 pg version and pglogical extension version is 2.4.4, don't see any argument to pass lsn in pglogical.create_subscription function.
Re: Logical Replication Delay
Hi Justin, Thank you for your suggestions and detailed insights. Due to certain business constraints, we are unable to split the tables into parallel publications. All of the tables involved are primary key tables, which adds further complexity to separating them into multiple publications. That said, we truly appreciate your recommendations regarding the use of binary mode and reviewing the use of IDENTITY SET TO FULL. We will ensure that the subscriber is operating in binary mode and will recheck the identity setup to minimize WAL size and overhead. Regards, Ram. On Sun, 22 Sept 2024 at 01:32, Justin wrote: > Hi Ramakrishna, > > 4GB of WAL generated per minute is a lot. I would expect the replay on > the subscriber to lag behind because it is a single process. PostgreSQL 16 > can create parallel workers for large transactions, however if there is a > flood of small transactions touching many tables the single LR worker is > going to fall behind. > > The only option is split the LR into multiple Publications and > Subscriptions as a single worker can't keep up. > > What is the justification to not split the tables across multiple > Publications and Subscriptions > > Additional items to check > > Make sure the Subscriber is using binary mode, this avoids an encoding > step. > https://www.postgresql.org/docs/current/sql-createsubscription.html > > Avoid the use of IDENTITY SET TO FULL on the publisher, if you do use > IDENTITY FULL make sure the subscriber table identity is set to a > qualifying unique index. In previous versions of PG the publisher and > subscriber identities had to match... > > IDENTITY SET TO FULL increase the size of the WAL and the work the > publisher and subscriber has to do. > > Hope this helps. > > > > On Sat, Sep 21, 2024 at 3:08 PM Ramakrishna m wrote: > >> Hi Team, >> >> We have configured bidirectional replication (but traffic can only flow >> in one direction) between two data centers (distance: 1000 km, maximum >> Network latency: 100 ms) with an application TPS (transactions per second) >> of 700 at maximum. >> >> We are fine with handling up to 500 TPS without observing any lag between >> the two data centers. However, when TPS increases, we notice a lag in WAL >> files of over 100 GB (initially, it was 1 TB, but after tuning, it was >> reduced to 100 GB). During peak times, WAL files are generated at a rate of >> 4 GB per minute. >> >> All transactions (Tx) take less than 200 ms, with a maximum of 1 second >> at times (no long-running transactions). >> >> *Here are the configured parameters and resources:* >> >>- *OS*: Ubuntu >>- *RAM*: 376 GB >>- *CPU*: 64 cores >>- *Swap*: 32 GB >>- *PostgreSQL Version*: 16.4 (each side has 3 nodes with Patroni and >>etcd configured) >>- *DB Size*: 15 TB >> >> *Parameters configured on both sides:* >> Name >> Setting Unit >> >> >> log_replication_commands off >> logical_decoding_work_mem 524288 kB >> max_logical_replication_workers 16 >> max_parallel_apply_workers_per_subscription 2 >> max_replication_slots 20 >> max_sync_workers_per_subscription 2 >> max_wal_senders 20 >> max_worker_processes 40 >> wal_level logical >> wal_receiver_timeout 60 ms >> wal_segment_size 1073741824 B >> wal_sender_timeout 60 ms >> >> *Optimizations applied:* >> >>1. Vacuum freeze is managed during off-hours; no aggressive vacuum is >>triggered during business hours. >>2. Converted a few tables to unlogged. >>3. Removed unwanted tables from publication. >>4. Partitioned all large tables. >> >> *Pending:* >> >>1. Turning off/tuning autovacuum parameters to avoid triggering >>during business hours. >> >> *Not possible: *We are running all tables in a single publication, and >> it is not possible to separate them. >> >> I would greatly appreciate any suggestions you may have to help avoid >> logical replication delays, whether through tuning database or operating >> system parameters, or any other recommendations >> >> -- >> Thanks & Regards, >> Ram. >> >
Re: IO related waits
Hello, On Sat, 2024-09-21 at 10:20 -0700, Adrian Klaver wrote: > On 9/21/24 02:36, veem v wrote: > > > > > > > > > Actually here the application is using kafka and flink stream and > > is > > using one of existing code in which it was doing row by row commit > > which > > is now changed to Batch commit i.e. the commit point is shifted > > from row > > by row to batch now. There are multiple sessions spawning at the > > same > > time to process the incoming messages 24/7. And also as described > > in > > another ongoing thread and also we saw in the past we did not see > > much > > difference between "batch commit" and "Batch insert" performance. > > We > > only realized the row by row commit is having worse performance. > > The bottom line is that to solve this a cost is going to have to be > paid > somewhere. Previously it was done with autocommit in the form of > slow > insert performance. You improved the speed of the inserts by wrapping > multiple inserts in transactions and that led you to this problem, > where > open transactions across sessions is leading to deadlock issues due > to > the same id being inserted in concurrent open sessions. Per my and > Greg > Sabino Mullane comments the solution is going to need planning. Right > now you are playing a round of Whack-a-Mole by making ad-hoc changes > of > portions of the process without reference to the process as a > whole.At > some point the parallel duplicate ids(records) need to be > straightened > out into a serial application of data. You and the other people > involved > need to come up with a coherent view of the process as whole with a > goal > to achieving that. Then you can start planning on where that cost is > best paid: 1) In the data before the streaming. 2) In the streaming > process itself 3) In the database or 4) Spread out across 1-4. > You are using Apache's flink to pump data into your database. It seems to me that you have this occurring:- pipelineA --> sessionA pipelineB --> sessionB etc. You haven't said if the SQL code doing the inserts is coming out of flink or if it is your own code. If it is your own code make sure you are handling SQLException events properly. If there is a flink mailing list or user group, you'll probably find more help with other flink users. Follow Adrian's advice. HTH, Rob
Re: glibc updarte 2.31 to 2.38
Hi Ron, > On 22. Sep, 2024, at 16:11, Ron Johnson wrote: > > The real question is why nobody notices it in other RDBMSs like Oracle, SQL > Server and MySQL. The answer is simple for Oracle: It includes a whole zoo of locale mappings and uses each one as it is needed. This is one of the many things with Oracle that only grows over time but does never get smaller again. I suspect it's similar with MariaDB, MySQL, SQL Server and others. Only PostgreSQL has no such thing as a local inventory and relies on either glibc or ICU. Cheers, Paul
Re: glibc updarte 2.31 to 2.38
On 9/22/24 09:48, Paul Förster wrote: Hi Joe On 22. Sep, 2024, at 15:47, Joe Conway wrote: Note that moving to ICU might improve things, but there are similar potential issues with ICU as well. The trick there would be to get your OS distro provider to maintain the same ICU version across major versions of the distro, which is not the case currently. Nor does the PGDG repo do that. Then I strongly suggest that the PostgreSQL developers develop a fail safe sorting mechanism that holds for generations of locale changes. https://www.postgresql.org/docs/17/release-17.html#RELEASE-17-HIGHLIGHTS Add a builtin platform-independent collation provider (Jeff Davis) This supports C and C.UTF-8 collations. Cheers, Paul -- Adrian Klaver adrian.kla...@aklaver.com
Re: glibc updarte 2.31 to 2.38
Hi Joe > On 22. Sep, 2024, at 15:47, Joe Conway wrote: > > Note that moving to ICU might improve things, but there are similar potential > issues with ICU as well. The trick there would be to get your OS distro > provider to maintain the same ICU version across major versions of the > distro, which is not the case currently. Nor does the PGDG repo do that. Then I strongly suggest that the PostgreSQL developers develop a fail safe sorting mechanism that holds for generations of locale changes. Cheers, Paul
Re: glibc updarte 2.31 to 2.38
On 9/21/24 15:19, Paul Foerster wrote: I already expressed the idea of changing all locales to ICU. The problem there is that I'd have to create new instances and then move each database individually. I wish I could convert already running databases… This also takes time. Still, I think I'm going to try this route. It's always a gamble if reindexing is needed or not with any glibc change. Note that moving to ICU might improve things, but there are similar potential issues with ICU as well. The trick there would be to get your OS distro provider to maintain the same ICU version across major versions of the distro, which is not the case currently. Nor does the PGDG repo do that. -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
Re: glibc updarte 2.31 to 2.38
I've been working on Unix-like systems for decades and though I thought I understood most of the issues to do with i18n/l10n, I've only just started using Postgres and I don't understand is why these changes ONLY seem to affect Postgres. Or is it more that it also affects text editors and the like, but we just tend to ignore that? On Sun, 22 Sep 2024, 14:47 Joe Conway, wrote: > On 9/21/24 15:19, Paul Foerster wrote: > > I already expressed the idea of changing all locales to ICU. The > > problem there is that I'd have to create new instances and then move > > each database individually. I wish I could convert already running > > databases… This also takes time. Still, I think I'm going to try > > this route. It's always a gamble if reindexing is needed or not with > > any glibc change. > > > Note that moving to ICU might improve things, but there are similar > potential issues with ICU as well. The trick there would be to get your > OS distro provider to maintain the same ICU version across major > versions of the distro, which is not the case currently. Nor does the > PGDG repo do that. > > > -- > Joe Conway > PostgreSQL Contributors Team > RDS Open Source Databases > Amazon Web Services: https://aws.amazon.com > > >
Re: glibc updarte 2.31 to 2.38
Shaheed, How often do you sort words in text editors? How often do you have your text editor care whether the word you just typed is the *only* instance of that word in the document? Not too often. So... yes, we ignore the problem. The real question is why nobody notices it in other RDBMSs like Oracle, SQL Server and MySQL. On Sun, Sep 22, 2024 at 9:59 AM Shaheed Haque wrote: > I've been working on Unix-like systems for decades and though I thought I > understood most of the issues to do with i18n/l10n, I've only just started > using Postgres and I don't understand is why these changes ONLY seem to > affect Postgres. Or is it more that it also affects text editors and the > like, but we just tend to ignore that? > > On Sun, 22 Sep 2024, 14:47 Joe Conway, wrote: > >> On 9/21/24 15:19, Paul Foerster wrote: >> > I already expressed the idea of changing all locales to ICU. The >> > problem there is that I'd have to create new instances and then move >> > each database individually. I wish I could convert already running >> > databases… This also takes time. Still, I think I'm going to try >> > this route. It's always a gamble if reindexing is needed or not with >> > any glibc change. >> >> >> Note that moving to ICU might improve things, but there are similar >> potential issues with ICU as well. The trick there would be to get your >> OS distro provider to maintain the same ICU version across major >> versions of the distro, which is not the case currently. Nor does the >> PGDG repo do that. >> >> >> -- >> Joe Conway >> PostgreSQL Contributors Team >> RDS Open Source Databases >> Amazon Web Services: https://aws.amazon.com >> >> >> -- Death to , and butter sauce. Don't boil me, I'm still alive. crustacean!
Re: How batch processing works
On 9/21/24 22:21, Lok P wrote: On Sun, Sep 22, 2024 at 12:46 AM Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: Thank you. So if I get it correct, if the client app(from which the data is getting streamed/inserted) is in the same data center/zone as the database (which is most of the time the case) then the batch insert does not appear to be much beneficial. No, the point is that once the client and the database are not on the same machine the network that they communicate across becomes a consideration. In a contrived example the client could be in the same same data center as the database server and talking to the server via a dialup modem and the data transfer would be worse then the same client talking to a database server a 1000 miles away across a fiber optic connection. This gets back to plan --> test. /|\| | <-- \|/ Which also means , people here were afraid of having triggers in such a high dml table as because this will make the "batch insert" automatically converted into "row by row" behind the scene, but considering the above results, it looks fine to go with a row by row approach (but just having batch commit in place in place of row by row commit). And not to worry about implementing the true batch insert approach as that is not making a big difference here in data load performance. This is getting ahead of the game. The immediate issue is the deadlock issues with the concurrent sessions and duplicate data. -- Adrian Klaver adrian.kla...@aklaver.com
Re: glibc updarte 2.31 to 2.38
Am Sun, Sep 22, 2024 at 02:59:34PM +0100 schrieb Shaheed Haque: > I've been working on Unix-like systems for decades and though I thought I > understood most of the issues to do with i18n/l10n, I've only just started > using Postgres and I don't understand is why these changes ONLY seem to > affect Postgres. Or is it more that it also affects text editors and the > like, but we just tend to ignore that? Text editors for example do not persist ordering based on locale. I'm sure there's software ignoring the issue, too. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: IO related waits
You may be able to solve this with advisory locks. In particular, transaction-level advisory locks with the "try-pass/fail" variant. Here, "123" is a unique number used by your app, related to this particular table. You also need to force read committed mode, as the advisory locks go away after the transaction ends, so at that point, we switch from advisory locks to the ON CONFLICT clause, which can only work smoothly if other processes can see the new row right away. drop table if exists t1; create table t1(id int primary key); -- Session 1: begin transaction isolation level read committed; -- inserts one row: insert into t1 select 1 where pg_try_advisory_xact_lock(123,1) on conflict(id) do nothing; -- Session 2: begin transaction isolation level read committed; -- inserts one row: insert into t1 select 2 where pg_try_advisory_xact_lock(123,2) on conflict(id) do nothing; -- Session 1: -- silently 'fails' because no lock is granted, so inserts zero rows: insert into t1 select 2 where pg_try_advisory_xact_lock(123,2) on conflict(id) do nothing; -- Session 2: -- silently 'fails' because no lock is granted, so inserts zero rows: insert into t1 select 1 where pg_try_advisory_xact_lock(123,1) on conflict(id) do nothing; -- inserts one row: insert into t1 select 3 where pg_try_advisory_xact_lock(123,3) on conflict(id) do nothing; commit; -- lock on 2 and 3 goes away -- Session 1: -- silently fails because of the on conflict clause insert into t1 select 3 where pg_try_advisory_xact_lock(123,3) on conflict(id) do nothing; Cheers, Greg
