Re: When extended query protocol ends? - Mailing list pgsql-hackers
| From | Vladimir Sitnikov |
|---|---|
| Subject | Re: When extended query protocol ends? |
| Date | |
| Msg-id | CAB=Je-HUXTgxnwDT9mxGYnBhrJJdPSj+qhq6MOzhRfBP0GdqYA@mail.gmail.com Whole thread Raw |
| In response to | Re: When extended query protocol ends? (Jelte Fennema-Nio <postgres@jeltef.nl>) |
| Responses |
Re: When extended query protocol ends?
|
| List | pgsql-hackers |
>When splitting a multi insert statement you're going to duplicate some work
I do not understand why I am going to duplicate some work.
I assume the database does its best to perform all the needed preparation when processing "parse" message,
and it should perform only the minimum required work when processing bind+exec messages.
Unfortunately, it is not completely the case, so using bind+exec+bind+exec is suboptimal even for trivial insert statements.
Please, take into account the following sequence:
One-time-only:
parse S1 as insert into table(id, name) values(?,?)
Some time later:
bind S1 ...
exec S1
bind S1 ...
exec S1
bind S1 ...
exec S1
bind S1 ...
exec S1
sync
I do not know how this could be made more efficient as I execute parse only once, and then I send bind+exec+bind+exec
without intermediate sync messages, so the data should flow nicely in TCP packets.
As I said above, the same flow for multi-value insert beats the bind+exec+bind+exec sequence at a cost of poor reporting.
For instance, for multivalue insert we can't tell how many rows are generated for each statement.
---
Here are some measurements regarding savepoints for simple vs extended
Sure they are not very scientific, however, they show improvement for simple protocol
BEGIN;
SAVEPOINT PGJDBC_AUTOSAVE;
RELEASE SAVEPOINT PGJDBC_AUTOSAVE;
COMMIT;
$ cat svpnt
SAVEPOINT PGJDBC_AUTOSAVE;
RELEASE SAVEPOINT PGJDBC_AUTOSAVE;
COMMIT;
$ pgbench -f svpnt --protocol=extended --time=10 --progress=1 -r
progress: 1.0 s, 4213.8 tps, lat 0.237 ms stddev 0.034, 0 failed
progress: 2.0 s, 4367.9 tps, lat 0.229 ms stddev 0.024, 0 failed
progress: 3.0 s, 4296.2 tps, lat 0.233 ms stddev 0.038, 0 failed
progress: 4.0 s, 4382.0 tps, lat 0.228 ms stddev 0.026, 0 failed
progress: 5.0 s, 4374.1 tps, lat 0.228 ms stddev 0.026, 0 failed
progress: 6.0 s, 4305.7 tps, lat 0.232 ms stddev 0.035, 0 failed
progress: 7.0 s, 4111.1 tps, lat 0.243 ms stddev 0.182, 0 failed
progress: 8.0 s, 4245.0 tps, lat 0.235 ms stddev 0.042, 0 failed
progress: 9.0 s, 4219.9 tps, lat 0.237 ms stddev 0.036, 0 failed
progress: 10.0 s, 4231.1 tps, lat 0.236 ms stddev 0.031, 0 failed
progress: 2.0 s, 4367.9 tps, lat 0.229 ms stddev 0.024, 0 failed
progress: 3.0 s, 4296.2 tps, lat 0.233 ms stddev 0.038, 0 failed
progress: 4.0 s, 4382.0 tps, lat 0.228 ms stddev 0.026, 0 failed
progress: 5.0 s, 4374.1 tps, lat 0.228 ms stddev 0.026, 0 failed
progress: 6.0 s, 4305.7 tps, lat 0.232 ms stddev 0.035, 0 failed
progress: 7.0 s, 4111.1 tps, lat 0.243 ms stddev 0.182, 0 failed
progress: 8.0 s, 4245.0 tps, lat 0.235 ms stddev 0.042, 0 failed
progress: 9.0 s, 4219.9 tps, lat 0.237 ms stddev 0.036, 0 failed
progress: 10.0 s, 4231.1 tps, lat 0.236 ms stddev 0.031, 0 failed
transaction type: svpnt
scaling factor: 1
query mode: extended
number of clients: 1
number of threads: 1
maximum number of tries: 1
duration: 10 s
number of transactions actually processed: 42748
number of failed transactions: 0 (0.000%)
latency average = 0.234 ms
latency stddev = 0.065 ms
initial connection time = 2.178 ms
tps = 4275.562760 (without initial connection time)
statement latencies in milliseconds and failures:
0.058 0 BEGIN;
0.058 0 SAVEPOINT PGJDBC_AUTOSAVE;
0.058 0 RELEASE SAVEPOINT PGJDBC_AUTOSAVE;
0.060 0 COMMIT;
scaling factor: 1
query mode: extended
number of clients: 1
number of threads: 1
maximum number of tries: 1
duration: 10 s
number of transactions actually processed: 42748
number of failed transactions: 0 (0.000%)
latency average = 0.234 ms
latency stddev = 0.065 ms
initial connection time = 2.178 ms
tps = 4275.562760 (without initial connection time)
statement latencies in milliseconds and failures:
0.058 0 BEGIN;
0.058 0 SAVEPOINT PGJDBC_AUTOSAVE;
0.058 0 RELEASE SAVEPOINT PGJDBC_AUTOSAVE;
0.060 0 COMMIT;
$ pgbench -f svpnt --protocol=simple --time=10 --progress=1 -r
progress: 1.0 s, 4417.7 tps, lat 0.225 ms stddev 0.033, 0 failed
progress: 2.0 s, 4446.0 tps, lat 0.225 ms stddev 0.079, 0 failed
progress: 3.0 s, 4377.1 tps, lat 0.228 ms stddev 0.048, 0 failed
progress: 4.0 s, 4485.0 tps, lat 0.223 ms stddev 0.024, 0 failed
progress: 5.0 s, 4355.9 tps, lat 0.229 ms stddev 0.353, 0 failed
progress: 6.0 s, 4444.3 tps, lat 0.225 ms stddev 0.035, 0 failed
progress: 7.0 s, 4530.7 tps, lat 0.220 ms stddev 0.020, 0 failed
progress: 8.0 s, 4431.1 tps, lat 0.225 ms stddev 0.022, 0 failed
progress: 9.0 s, 4497.1 tps, lat 0.222 ms stddev 0.027, 0 failed
progress: 10.0 s, 4507.0 tps, lat 0.222 ms stddev 0.024, 0 failed
transaction type: svpnt
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
duration: 10 s
number of transactions actually processed: 44493
number of failed transactions: 0 (0.000%)
latency average = 0.224 ms
latency stddev = 0.116 ms
initial connection time = 2.690 ms
tps = 4450.372095 (without initial connection time)
statement latencies in milliseconds and failures:
0.056 0 BEGIN;
0.056 0 SAVEPOINT PGJDBC_AUTOSAVE;
0.056 0 RELEASE SAVEPOINT PGJDBC_AUTOSAVE;
0.057 0 COMMIT;
progress: 2.0 s, 4446.0 tps, lat 0.225 ms stddev 0.079, 0 failed
progress: 3.0 s, 4377.1 tps, lat 0.228 ms stddev 0.048, 0 failed
progress: 4.0 s, 4485.0 tps, lat 0.223 ms stddev 0.024, 0 failed
progress: 5.0 s, 4355.9 tps, lat 0.229 ms stddev 0.353, 0 failed
progress: 6.0 s, 4444.3 tps, lat 0.225 ms stddev 0.035, 0 failed
progress: 7.0 s, 4530.7 tps, lat 0.220 ms stddev 0.020, 0 failed
progress: 8.0 s, 4431.1 tps, lat 0.225 ms stddev 0.022, 0 failed
progress: 9.0 s, 4497.1 tps, lat 0.222 ms stddev 0.027, 0 failed
progress: 10.0 s, 4507.0 tps, lat 0.222 ms stddev 0.024, 0 failed
transaction type: svpnt
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
duration: 10 s
number of transactions actually processed: 44493
number of failed transactions: 0 (0.000%)
latency average = 0.224 ms
latency stddev = 0.116 ms
initial connection time = 2.690 ms
tps = 4450.372095 (without initial connection time)
statement latencies in milliseconds and failures:
0.056 0 BEGIN;
0.056 0 SAVEPOINT PGJDBC_AUTOSAVE;
0.056 0 RELEASE SAVEPOINT PGJDBC_AUTOSAVE;
0.057 0 COMMIT;
Vladimir
pgsql-hackers by date: