Skip site navigation (1) Skip section navigation (2)

Re: A couple of proposed pgbench changes

From: Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp>
To: tgl(at)sss(dot)pgh(dot)pa(dot)us
Cc: t-ishii(at)sra(dot)co(dot)jp, pgsql-patches(at)postgresql(dot)org
Subject: Re: A couple of proposed pgbench changes
Date: 2005-12-01 07:13:05
Message-ID: 20051201.161305.26273167.t-ishii@sraoss.co.jp (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
> Hi Tatsuo,
> 
> Attached is a proposed patch that deals with a couple of pgbench issues.
> 
> The change at line 490 updates doCustom's local variable "commands"
> after selecting a new file (command sequence).  I think that the
> existing coding will cause the thing to use the first command of the
> old sequence in the remainder of the routine, which would be a bug.
> I have not tried to set up a test case to prove it, though.

Thanks for pointing it out. It's definitely a bug. To reproduce the
problem, you have two SQL files, say,

a.sql:
SELECT 1;
SELECT 2;

b.sql:
SELECT 300;
SELECT 400;

then run,

pgbench -f a.sql -f b.sql

and see the SQL trace by enabling log_statement=all,

I see:
LOG:  statement: SELECT 300;
LOG:  statement: SELECT 400;
LOG:  statement: SELECT 300;
LOG:  statement: SELECT 2;
LOG:  statement: SELECT 1;
LOG:  statement: SELECT 400;
LOG:  statement: SELECT 300;
LOG:  statement: SELECT 400;
LOG:  statement: SELECT 300;
LOG:  statement: SELECT 2;
LOG:  statement: SELECT 1;
:
:

#1 and #2 are ok, but #3(SELECT 300) and #4(SELECT 2) combo is
supposed to be impossible if pgbench works correctly.

> The other two changes cause doCustom to loop after processing a
> meta-command.  This might be a bit controversial, but as the code
> is currently written, each meta-command "costs" one cycle of the
> outer select() loop.  Thus, for example, with the default TPC-B script,
> once a backend returns "COMMIT" it will not receive a new command
> until four cycles of issuing commands to other backends have elapsed.
> (You can see this very easily by strace'ing pgbench under load.)
> ISTM it is bad to have backends sitting idle waiting for pgbench to
> give them a new command.  On the other hand you could argue that
> finishing out several consecutive metacommands will delay issuance of
> new commands to other backends.  In the test case I was running,
> making this change made for a small but noticeable improvement in
> total CPU usage, but I'm not entirely convinced it'd always be a win.

Agreed.

> I get the impression that pgbench itself is a bit of a bottleneck when
> running on multi-CPU machines.  I can't get the total CPU usage to reach
> 90% with ten client threads on a dual HT-enabled Xeon, and the only
> explanation I can see is that there are too many backends waiting for
> pgbench to give them new commands instead of doing useful work.  strace
> shows that each select() iteration usually finds *all ten* sockets
> read-ready, which is definitely bad.  (I tried nice'ing pgbench to
> negative priority, but it did not improve matters.  It could easily be
> that this is a Heisenproblem, though, with strace itself slowing pgbench
> enough to cause the behavior.)  I wonder if it would help for pgbench to
> fork off multiple sub-processes and have each sub-process tend just one
> backend.

I'm not sure multiple sub-processes version of pgbench shows superior
performance than current implementation because of process context
switching overhead. Maybe threading is better? Mr. Yasuo Ohgaki
implemented pthead version of pgbench.

http://wiki.ohgaki.net/index.php?plugin=attach&pcmd=open&file=ppgbench-20050906-3.tar.bz2&refer=PostgreSQL%2Fppgbench

You might want to try.

> Anyway, since I'm not sure of either of these changes, I'm passing them
> to you for review.

Your patches seem fine.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

In response to

Responses

pgsql-hackers by date

Next:From: Junji TERAMOTODate: 2005-12-01 08:59:08
Subject: Vertical Partitioning with TOAST
Previous:From: David FetterDate: 2005-12-01 05:15:59
Subject: Re: [HACKERS] Upcoming PG re-releases

pgsql-patches by date

Next:From: Neil ConwayDate: 2005-12-01 08:12:01
Subject: Re: Check for integer overflow in datetime functions
Previous:From: Michael FuhrDate: 2005-12-01 02:36:20
Subject: Check for integer overflow in datetime functions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group