Re: Clarify this MERGE warning? "Only columns from the target table that attempt to match data_source rows should appear in join_condition."
On 2024-09-09 14:02:50 +0100, Philip Hazelden wrote: > The MERGE docs[1] give this warning: > > > Only columns from the target table that attempt to match > > `data_source` rows should appear in `join_condition`. > > `join_condition` subexpressions that only reference the target > > table's columns can affect which action is taken, often in > > surprising ways. > > (The docs for upcoming v17 have the same line.) > > But when I tested this, it seems to work fine. For example, consider a > two-level primary key, where the source table implicitly has a fixed > value for one level: [...] The warning is a bit misleading, IMHO. I think what this is trying to say is that this is effectively data_source LEFT JOIN target ON condition, and any row from data_source not matched by condition will end up in the "NOT MATCHED" set. So you might insert rows from data_source which you thought you had excluded in the condition. So it's important to get the match right, and then decide what to do in the WHEN clauses. > merge into t1 using t2 > on t1.k2 = t2.k2 and t1.k1 = 1 > when matched then update set v = t2.v > when not matched then insert values (1, t2.k2, t2.v); I think that's ok. The t1.k1 = 1 is used to match rows from the target to the data source (for each row in the data source, find the rows in the target which have the same k2 and k1 = 1). But "columns from the target table that attempt to match data_source` rows" for me sort of sounds like those columns have to have a counterpart in the data_source, which k1 hasn't. Also maybe the order is the wrong way around? "Match rows in the target to rows in the data_source" would fit my mental model better. 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: IO related waits
On 2024-09-21 15:06:45 +0530, veem v wrote: > On Sat, 21 Sept 2024 at 03:47, Peter J. Holzer wrote: > > On 2024-09-20 14:11:38 -0700, Adrian Klaver wrote: > > On 9/20/24 1:01 PM, veem v wrote: > > > Able to reproduce this deadlock graph as below. Now my > > > question is , this is a legitimate scenario in which the same > > > ID can get inserted from multiple sessions and in such cases > > > it's expected to skip that (thus "On conflict Do nothing" is > > > used) row. But as we see it's breaking the code > > > > Yeah, as I see it that would not work with concurrent > > uncommitted sessions as it would be unresolved whether a > > conflict actually exists until at least one of the sessions > > completes. > > > > > with deadlock error during race conditions where a lot of > > > parallel threads are operating. So how should we handle this > > > scenario? Will setting the "lock_timeout" parameter at session > > > level will help us anyway here? [...] > SO that leads as to another solution: > > Retry each batch (possibly after reducing the batch size) until it > succeeds. > > > > 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. > > Now, in such a scenario when the messages are coming from a streaming > framework using kafka +flink and also the insert is happening using > row by row only (but just the commit is shifted to batch), i don't see > any way to sort the ID columns in this streaming process, so that they > won't overlap across session. I have never used Kafka or Flink, but I imagine that they can stream to various targets, not just to PostgreSQL. So I would write a program which can receive such a stream. This program would then buffer rows until either a number of rows or some timeout was exceeded. It could then preprocess those rows (e.g. by sorting them) and then open a transaction and try to insert them. If the transaction fails because of a deadlock, serialization error or similar, simply retry the transaction. If everything went well, go back to receiving the next batch of rows. > In such a situation , the only way seems to have the messages replayed > for which the deadlock error happens , as I think during a deadlock > error, one session gets terminated by postgres No, the *session* doesn't get terminated. The transaction fails. It can be rolled back and tried again. > and that messages perhap we can save in some exception table and then > replay? Not sure what you mean by "exception table", but if you mean that you keep all the rows from that transaction around until the commit succeeds (so that you can try to insert them again if the transaction fails), then yes, that's what I meant. 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 Sat, Sep 21, 2024 at 9:51 AM Michał Kłeczek wrote: > Hi, > > > On 19 Sep 2024, at 07:30, Lok P wrote: > > > [snip] > > > > Method-4 > > > > INSERT INTO parent_table VALUES (1, 'a'), (2, 'a'); > > INSERT INTO child_table VALUES (1,1, 'a'), (1,2, 'a'); > > commit; > > I’ve done some batch processing of JSON messages from Kafka in Java. > By far the most performant way was to: > > 1. Use prepared statements > 2. Parse JSON messages in Postgres > 3. Process messages in batches > > All three can be achieved by using arrays to pass batches: > > WITH parsed AS ( > SELECT msg::json FROM unnest(?) > ), > parents AS ( > INSERT INTO parent SELECT … FROM parsed RETURNING ... > ) > INSERT INTO child SELECT … FROM parsed… > > Not the single parameter that you can bind to String[] > > Hope that helps. > > Got your point. But wondering why we don't see any difference in performance between method-2 and method-3 above. So does it mean that,I am testing this in a wrong way or it's the expected behaviour and thus there is no meaning in converting the row by row inserts into a bulk insert, but just changing the commit frequency will do the same job in a row by row insert approach?
Logical Replication Delay
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
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. Now, in such a scenario when the messages are coming from a streaming framework using kafka +flink and also the insert is happening using row by row only (but just the commit is shifted to batch), i don't see any way to sort the ID columns in this streaming process, so that they won't overlap across session. In such a situation , the only way seems to have the messages replayed for which the deadlock error happens , as I think during a deadlock error, one session gets terminated by postgres and that messages perhap we can save in some exception table and then replay? -- Adrian Klaver adrian.kla...@aklaver.com
Re: How batch processing works
On Sun, Sep 22, 2024 at 12:46 AM Adrian Klaver
wrote:
> On 9/21/24 07:36, Peter J. Holzer wrote:
> > On 2024-09-21 16:44:08 +0530, Lok P wrote:
>
> >
> ---
> > #!/usr/bin/python3
> >
> > import time
> > import psycopg2
> >
> > num_inserts = 10_000
> > batch_size = 50
> >
> > db = psycopg2.connect()
> > csr = db.cursor()
> >
> > csr.execute("drop table if exists parent_table")
> > csr.execute("create table parent_table (id int primary key, t text)")
> > db.commit()
> >
> > start_time = time.monotonic()
> > 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()
> > end_time = time.monotonic()
> > elapsed_time = end_time - start_time
> > print(f"Method 2: Individual Inserts with Commit after {batch_size}
> Rows: {elapsed_time:.3} seconds")
> >
> > # vim: tw=99
> >
> ---
>
> FYI, this is less of problem with psycopg(3) and pipeline mode:
>
> import time
> import psycopg
>
> num_inserts = 10_000
> batch_size = 50
>
> db = psycopg.connect("dbname=test user=postgres host=104.237.158.68")
> csr = db.cursor()
>
> csr.execute("drop table if exists parent_table")
> csr.execute("create table parent_table (id int primary key, t text)")
> db.commit()
>
> start_time = time.monotonic()
> 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()
> end_time = time.monotonic()
> elapsed_time = end_time - start_time
> print(f"Method 2: Individual Inserts(psycopg3 pipeline mode) with Commit
> after {batch_size} Rows: {elapsed_time:.3} seconds")
>
>
> 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
>
> > #!/usr/bin/python3
> >
> > import itertools
> > import time
> > import psycopg2
> >
> > num_inserts = 10_000
> > batch_size = 50
> >
> > db = psycopg2.connect()
> > csr = db.cursor()
> >
> > csr.execute("drop table if exists parent_table")
> > csr.execute("create table parent_table (id int primary key, t text)")
> > db.commit()
> >
> > start_time = time.monotonic()
> > batch = []
> > for i in range(1, num_inserts+1):
> > batch.append((i, 'a'))
> > if i % batch_size == 0:
> > q = "insert into parent_table values" + ",".join(["(%s, %s)"] *
> len(batch))
> > params = list(itertools.chain.from_iterable(batch))
> > csr.execute(q, params)
> > db.commit()
> > batch = []
> > if batch:
> > q = "insert into parent_table values" + ",".join(["(%s, %s)"] *
> len(batch))
> > csr.execute(q, list(itertools.chain(batch)))
> > db.commit()
> > batch = []
> >
> > end_time = time.monotonic()
> > elapsed_time = end_time - start_time
> > print(f"Method 3: Batch Inserts ({batch_size}) with Commit after each
> batch: {elapsed_time:.3} seconds")
> >
> > # vim: tw=99
> >
> ---
>
> The above can also be handled with execute_batch() and execute_values()
> from:
>
> https://www.psycopg.org/docs/extras.html#fast-execution-helpers
>
> >
> > 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 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.
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.
Re: How batch processing works
On 9/21/24 07:36, Peter J. Holzer wrote:
On 2024-09-21 16:44:08 +0530, Lok P wrote:
---
#!/usr/bin/python3
import time
import psycopg2
num_inserts = 10_000
batch_size = 50
db = psycopg2.connect()
csr = db.cursor()
csr.execute("drop table if exists parent_table")
csr.execute("create table parent_table (id int primary key, t text)")
db.commit()
start_time = time.monotonic()
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()
end_time = time.monotonic()
elapsed_time = end_time - start_time
print(f"Method 2: Individual Inserts with Commit after {batch_size} Rows:
{elapsed_time:.3} seconds")
# vim: tw=99
---
FYI, this is less of problem with psycopg(3) and pipeline mode:
import time
import psycopg
num_inserts = 10_000
batch_size = 50
db = psycopg.connect("dbname=test user=postgres host=104.237.158.68")
csr = db.cursor()
csr.execute("drop table if exists parent_table")
csr.execute("create table parent_table (id int primary key, t text)")
db.commit()
start_time = time.monotonic()
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()
end_time = time.monotonic()
elapsed_time = end_time - start_time
print(f"Method 2: Individual Inserts(psycopg3 pipeline mode) with Commit
after {batch_size} Rows: {elapsed_time:.3} seconds")
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
#!/usr/bin/python3
import itertools
import time
import psycopg2
num_inserts = 10_000
batch_size = 50
db = psycopg2.connect()
csr = db.cursor()
csr.execute("drop table if exists parent_table")
csr.execute("create table parent_table (id int primary key, t text)")
db.commit()
start_time = time.monotonic()
batch = []
for i in range(1, num_inserts+1):
batch.append((i, 'a'))
if i % batch_size == 0:
q = "insert into parent_table values" + ",".join(["(%s, %s)"] *
len(batch))
params = list(itertools.chain.from_iterable(batch))
csr.execute(q, params)
db.commit()
batch = []
if batch:
q = "insert into parent_table values" + ",".join(["(%s, %s)"] * len(batch))
csr.execute(q, list(itertools.chain(batch)))
db.commit()
batch = []
end_time = time.monotonic()
elapsed_time = end_time - start_time
print(f"Method 3: Batch Inserts ({batch_size}) with Commit after each batch:
{elapsed_time:.3} seconds")
# vim: tw=99
---
The above can also be handled with execute_batch() and execute_values()
from:
https://www.psycopg.org/docs/extras.html#fast-execution-helpers
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.
hp
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: Logical Replication Delay
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: glibc updarte 2.31 to 2.38
Hi Peter, > On 21 Sep 2024, at 00:33, Peter J. Holzer wrote: > > I don't use SLES but I would expect it to have an RPM for it. > > If you have any test machine which you can upgrade before the production > servers (and given the amount of data and availability requirements you > have, I really hope you do) you should be set. One of our admins did me a favor and upgraded my build server ahead of schedule. So I can both test our current PostgreSQL version as well as rebuild it if necessary. I can't test all of our data. That'd take quite a few months or more. I just can try to identify some crucial databases and columns. When those tests are done, I can only pray and hope for the best. 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. Cheers, Paul
Re: IO related waits
On Sat, 21 Sept 2024 at 03:47, Peter J. Holzer wrote: > On 2024-09-20 14:11:38 -0700, Adrian Klaver wrote: > > On 9/20/24 1:01 PM, veem v wrote: > > > Able to reproduce this deadlock graph as below. Now my question is , > > > this is a legitimate scenario in which the same ID can get inserted > from > > > multiple sessions and in such cases it's expected to skip that (thus > "On > > > conflict Do nothing" is used) row. But as we see it's breaking the code > > > > Yeah, as I see it that would not work with concurrent uncommitted > sessions > > as it would be unresolved whether a conflict actually exists until at > least > > one of the sessions completes. > > > > > with deadlock error during race conditions where a lot of parallel > > > threads are operating. So how should we handle this scenario? Will > > > setting the "lock_timeout" parameter at session level will help us > > > anyway here? > > > > Serializable transaction?: > > > > > https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE > > Doesn't help here, at least not directly. It would help indirectly > because isolation level serializable makes it very proable that > serialization errors occur. So an application designed for serializable > would have some kind of retry logic already in place. > > SO that leads as to another solution: > > Retry each batch (possibly after reducing the batch size) until it > succeeds. > > 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. Now, in such a scenario when the messages are coming from a streaming framework using kafka +flink and also the insert is happening using row by row only (but just the commit is shifted to batch), i don't see any way to sort the ID columns in this streaming process, so that they won't overlap across session. In such a situation , the only way seems to have the messages replayed for which the deadlock error happens , as I think during a deadlock error, one session gets terminated by postgres and that messages perhap we can save in some exception table and then replay?
Re: How batch processing works
On 2024-09-21 16:44:08 +0530, Lok P wrote:
> But wondering why we don't see any difference in performance between method-2
> and method-3 above.
The code runs completely inside the database. So there isn't much
difference between a single statement which inserts 50 rows and 50
statements which insert 1 row each. The work to be done is (almost) the
same.
This changes once you consider an application which runs outside of the
database (maybe even on a different host). Such an application has to
wait for the result of each statement before it can send the next one.
Now it makes a difference whether you are waiting 50 times for a
statement which does very little or just once for a statement which does
more work.
> So does it mean that,I am testing this in a wrong way or
That depends on what you want to test. If you are interested in the
behaviour of stored procedures, the test is correct. If you want to know
about the performance of a database client (whether its written in Java,
Python, Go or whatever), this is the wrong test. You have to write the
test in your target language and run it on the client system to get
realistic results (for example, the round-trip times will be a lot
shorter if the client and database are on the same computer than when
one is in Europe and the other in America).
For example, here are the three methods as Python scripts:
---
#!/usr/bin/python3
import time
import psycopg2
num_inserts = 10_000
db = psycopg2.connect()
csr = db.cursor()
csr.execute("drop table if exists parent_table")
csr.execute("create table parent_table (id int primary key, t text)")
start_time = time.monotonic()
for i in range(1, num_inserts+1):
csr.execute("insert into parent_table values(%s, %s)", (i, 'a'))
db.commit()
end_time = time.monotonic()
elapsed_time = end_time - start_time
print(f"Method 1: Individual Inserts with Commit after every Row:
{elapsed_time:.3} seconds")
# vim: tw=99
---
#!/usr/bin/python3
import time
import psycopg2
num_inserts = 10_000
batch_size = 50
db = psycopg2.connect()
csr = db.cursor()
csr.execute("drop table if exists parent_table")
csr.execute("create table parent_table (id int primary key, t text)")
db.commit()
start_time = time.monotonic()
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()
end_time = time.monotonic()
elapsed_time = end_time - start_time
print(f"Method 2: Individual Inserts with Commit after {batch_size} Rows:
{elapsed_time:.3} seconds")
# vim: tw=99
---
#!/usr/bin/python3
import itertools
import time
import psycopg2
num_inserts = 10_000
batch_size = 50
db = psycopg2.connect()
csr = db.cursor()
csr.execute("drop table if exists parent_table")
csr.execute("create table parent_table (id int primary key, t text)")
db.commit()
start_time = time.monotonic()
batch = []
for i in range(1, num_inserts+1):
batch.append((i, 'a'))
if i % batch_size == 0:
q = "insert into parent_table values" + ",".join(["(%s, %s)"] *
len(batch))
params = list(itertools.chain.from_iterable(batch))
csr.execute(q, params)
db.commit()
batch = []
if batch:
q = "insert into parent_table values" + ",".join(["(%s, %s)"] * len(batch))
csr.execute(q, list(itertools.chain(batch)))
db.commit()
batch = []
end_time = time.monotonic()
elapsed_time = end_time - start_time
print(f"Method 3: Batch Inserts ({batch_size}) with Commit after each batch:
{elapsed_time:.3} seconds")
# vim: tw=99
---
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.
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 Sat, Sep 21, 2024 at 8:07 PM Peter J. Holzer wrote:
> On 2024-09-21 16:44:08 +0530, Lok P wrote:
> > But wondering why we don't see any difference in performance between
> method-2
> > and method-3 above.
>
> The code runs completely inside the database. So there isn't much
> difference between a single statement which inserts 50 rows and 50
> statements which insert 1 row each. The work to be done is (almost) the
> same.
>
> This changes once you consider an application which runs outside of the
> database (maybe even on a different host). Such an application has to
> wait for the result of each statement before it can send the next one.
> Now it makes a difference whether you are waiting 50 times for a
> statement which does very little or just once for a statement which does
> more work.
>
> > So does it mean that,I am testing this in a wrong way or
>
> That depends on what you want to test. If you are interested in the
> behaviour of stored procedures, the test is correct. If you want to know
> about the performance of a database client (whether its written in Java,
> Python, Go or whatever), this is the wrong test. You have to write the
> test in your target language and run it on the client system to get
> realistic results (for example, the round-trip times will be a lot
> shorter if the client and database are on the same computer than when
> one is in Europe and the other in America).
>
> For example, here are the three methods as Python scripts:
>
>
> ---
> #!/usr/bin/python3
>
> import time
> import psycopg2
>
> num_inserts = 10_000
>
> db = psycopg2.connect()
> csr = db.cursor()
>
> csr.execute("drop table if exists parent_table")
> csr.execute("create table parent_table (id int primary key, t text)")
>
> start_time = time.monotonic()
> for i in range(1, num_inserts+1):
> csr.execute("insert into parent_table values(%s, %s)", (i, 'a'))
> db.commit()
> end_time = time.monotonic()
> elapsed_time = end_time - start_time
> print(f"Method 1: Individual Inserts with Commit after every Row:
> {elapsed_time:.3} seconds")
>
> # vim: tw=99
>
> ---
> #!/usr/bin/python3
>
> import time
> import psycopg2
>
> num_inserts = 10_000
> batch_size = 50
>
> db = psycopg2.connect()
> csr = db.cursor()
>
> csr.execute("drop table if exists parent_table")
> csr.execute("create table parent_table (id int primary key, t text)")
> db.commit()
>
> start_time = time.monotonic()
> 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()
> end_time = time.monotonic()
> elapsed_time = end_time - start_time
> print(f"Method 2: Individual Inserts with Commit after {batch_size} Rows:
> {elapsed_time:.3} seconds")
>
> # vim: tw=99
>
> ---
> #!/usr/bin/python3
>
> import itertools
> import time
> import psycopg2
>
> num_inserts = 10_000
> batch_size = 50
>
> db = psycopg2.connect()
> csr = db.cursor()
>
> csr.execute("drop table if exists parent_table")
> csr.execute("create table parent_table (id int primary key, t text)")
> db.commit()
>
> start_time = time.monotonic()
> batch = []
> for i in range(1, num_inserts+1):
> batch.append((i, 'a'))
> if i % batch_size == 0:
> q = "insert into parent_table values" + ",".join(["(%s, %s)"] *
> len(batch))
> params = list(itertools.chain.from_iterable(batch))
> csr.execute(q, params)
> db.commit()
> batch = []
> if batch:
> q = "insert into parent_table values" + ",".join(["(%s, %s)"] *
> len(batch))
> csr.execute(q, list(itertools.chain(batch)))
> db.commit()
> batch = []
>
> end_time = time.monotonic()
> elapsed_time = end_time - start_time
> print(f"Method 3: Batch Inserts ({batch_size}) with Commit after each
> batch: {elapsed_time:.3} seconds")
>
> # vim: tw=99
>
> ---
>
> 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) if we do it within the procedure inside the database. But because
the context switching will be minimal in method-3 as it will prepare the
insert and submit to the database in one shot in one DB call, so it should
be a bit fast. But from your figures , it appears to be the opposite ,
i.e.
