Re: When extended query protocol ends?

Поиск
Список
Период
Сортировка
От Vladimir Sitnikov
Тема Re: When extended query protocol ends?
Дата
Msg-id CAB=Je-HUXTgxnwDT9mxGYnBhrJJdPSj+qhq6MOzhRfBP0GdqYA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: When extended query protocol ends?  (Jelte Fennema-Nio <postgres@jeltef.nl>)
Ответы Re: When extended query protocol ends?  (Jelte Fennema-Nio <postgres@jeltef.nl>)
Список 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

$ cat svpnt

BEGIN;
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
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;

$ 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;

Vladimir

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Ashutosh Bapat
Дата:
Сообщение: Re: A problem about partitionwise join
Следующее
От: Daniel Gustafsson
Дата:
Сообщение: Re: Test to dump and restore objects left behind by regression