Patch for testing query modes on pgbench

Hi,

V3 protocol seems to be slower than V2 if we don't use prepared statement
together. I measured performance of protocols on 8.3beta using pgbench -S
with modification to use PQsendQueryParams and PQsendQueryPrepared
instead of PQsendQuery. (I'll send the patch to -patches shortly.)

[V2] PQsendQuery (V2; original)
[V3] PQsendQueryParams (V3)
[V3P] PQsendQueryPrepared (V3 with prepared statements)

V3 was 12% slower than V2, and V3P was 40% faster than V2.

There seems to be some inefficient behaviors in the handling of
V3 protocol or in libpq. Are there any TODO items here?

----
pg_ctl start -o "-c shared_buffers=1GB"
./pgbench -n -S -s50 -c16 -t100000 -M [V2|V3|PREPARE]
transaction type: SELECT only
scaling factor: 50
number of clients: 16
number of transactions per client: 100000
number of transactions actually processed: 1600000/1600000

sql mode: V2
tps = 19025.932203 (including connections establishing)
tps = 19036.285885 (excluding connections establishing)

sql mode: V3
tps = 16699.173521 (including connections establishing)
tps = 16707.135700 (excluding connections establishing)

sql mode: PREPARE
tps = 26629.710398 (including connections establishing)
tps = 26649.901658 (excluding connections establishing)

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> writes:
> [V2] PQsendQuery (V2; original)
> [V3] PQsendQueryParams (V3)
> [V3P] PQsendQueryPrepared (V3 with prepared statements)

> V3 was 12% slower than V2, and V3P was 40% faster than V2.

Those aren't really comparable, because the functionality is different.
Did you check plain PQSendQuery on both V2 and V3?

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> writes:
> > [V2] PQsendQuery (V2; original)
> > [V3] PQsendQueryParams (V3)
> > [V3P] PQsendQueryPrepared (V3 with prepared statements)
>
> > V3 was 12% slower than V2, and V3P was 40% faster than V2.
>
> Those aren't really comparable, because the functionality is different.
> Did you check plain PQSendQuery on both V2 and V3?

Oops, I called 'simple query' V2. All of my tests used V3 protocol.
The results need to be read as:

PQsendQuery = (simple query)
vs.
PQsendQueryParams = (parse + bind + describe + execute + sync)

If the latter is slower than the former, the another version of
PQsendQueryParams, that fills parameters into SQL and uses simple-query,
might be considerable for performance. For example in Java, using
PreparedStatement class with V2 protocol.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

Here is a patch for testing query modes on pgbench. It is discussed on
http://archives.postgresql.org/pgsql-hackers/2007-10/msg00755.php

I think this is useful to measure performance of protocols and
prepared statements. I hope the extended protocol will be improved
as fast as the simple protocol. It is 10-20% slower now.
(for 8.4 item)

The querymode option is added:
-M querymode
Choose the query mode from the follows:
simple: using simple query protocol.
extended: using extended protocol.
prepared: using extended protocol with prepared statements.
default is simple.

Usage:
$ pgbench -M [simple|extended|prepared]

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

This has been saved for the 8.4 release:

http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---------------------------------------------------------------------------

ITAGAKI Takahiro wrote:
> Here is a patch for testing query modes on pgbench. It is discussed on
> http://archives.postgresql.org/pgsql-hackers/2007-10/msg00755.php
>
> I think this is useful to measure performance of protocols and
> prepared statements. I hope the extended protocol will be improved
> as fast as the simple protocol. It is 10-20% slower now.
> (for 8.4 item)
>
> The querymode option is added:
> -M querymode
> Choose the query mode from the follows:
> simple: using simple query protocol.
> extended: using extended protocol.
> prepared: using extended protocol with prepared statements.
> default is simple.
>
> Usage:
> $ pgbench -M [simple|extended|prepared]
>
> Regards,
> ---
> ITAGAKI Takahiro
> NTT Open Source Software Center
>

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +