[HACKERS] pgbench: faster version of tpcb-like transaction

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема [HACKERS] pgbench: faster version of tpcb-like transaction
Дата
Msg-id CAMkU=1y-66FzO=ALLuwaF4cw3AfXcuB8M+1SCh2=_kNBDEW0mQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: [HACKERS] pgbench: faster version of tpcb-like transaction  (Peter Geoghegan <pg@bowt.ie>)
Re: [HACKERS] pgbench: faster version of tpcb-like transaction  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: [HACKERS] pgbench: faster version of tpcb-like transaction  (Fabien COELHO <coelho@cri.ensmp.fr>)
Re: [HACKERS] pgbench: faster version of tpcb-like transaction  (Fabien COELHO <coelho@cri.ensmp.fr>)
Список pgsql-hackers
If all the data is in memory and you have a system with fast fsyncs (or are running with fsync off, or unlogged tables, or synchronous_commit off), then the big bottleneck in pgbench is the amount of back and forth between the pgbench program and the backend.  There are 7 commands per transaction.

It is easy to package 5 of those commands into a single PL/pgSQL function, with the other two being implicit via the standard auto-commit behavior when explicit transactions are not opened.  The attached patch does that, under the name tpcb-func.  I first named it tpcb-like-func, but one builtin name can't be a prefix or another so that won't work.

It creates the function unconditionally during -i, because there is no way to know if the run-time will end up using it or not.  I think this is OK.  PL/pgSQL is installed by default in all supported versions. If someone has gone through the bother of uninstalling it, I don't see a need to accommodate them here.

I get nearly a 3 fold speed up using the new transaction, from 9184 to 26383 TPS, on 8 CPU machine using scale 50 and:

PGOPTIONS="-c synchronous_commit=off" pgbench -c32 -j32 -T60 -b tpcb-like

I think this should be committed as a built-in, not just a user-defined transaction, because I would like to see it widely used.  In fact, if it weren't for historical consistency I would say it should be the default transaction.  Wanting to measure IPC overhead is a valid thing to do, but certainly isn't the most common thing people want to do with pgbench.  If a user is limited by IO, it wouldn't matter which transaction they use, and if they are not limited by IO then this transaction is more likely to be the right one for them than the current default one transaction is.

Also, as a user-defined transaction with -f, you have to go out of your way to create the function (no "-i" support) and to make sure :scale gets set correctly during runs (as it won't be automatically read from pgbench_branches table, you have manually give -D).

Cheers,

Jeff
Вложения

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

Предыдущее
От: Jeff Janes
Дата:
Сообщение: Re: [HACKERS] subscription worker signalling wal writer too much
Следующее
От: Michael Malis
Дата:
Сообщение: [HACKERS] Poor cost estimate with interaction between table correlation andpartial indexes