A couple of proposed pgbench changes

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp>
Cc: pgsql-patches(at)postgreSQL(dot)org
Subject: A couple of proposed pgbench changes
Date: 2005-11-30 00:08:50
Message-ID: 11316.1133309330@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-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.

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.

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.

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

regards, tom lane

Index: pgbench.c
===================================================================
RCS file: /cvsroot/pgsql/contrib/pgbench/pgbench.c,v
retrieving revision 1.48
diff -c -r1.48 pgbench.c
*** pgbench.c 23 Nov 2005 12:19:12 -0000 1.48
--- pgbench.c 29 Nov 2005 23:51:46 -0000
***************
*** 411,416 ****
--- 411,417 ----
CState *st = &state[n];
Command **commands;

+ top:
commands = sql_files[st->use_file];

if (st->listen)
***************
*** 489,494 ****
--- 490,496 ----
{
st->state = 0;
st->use_file = getrand(0, num_files - 1);
+ commands = sql_files[st->use_file];
}
}

***************
*** 572,577 ****
--- 574,581 ----
free(val);
st->listen = 1;
}
+
+ goto top;
}
}

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Josh Berkus 2005-11-30 02:04:54 Please let us know if you will come to the PostgreSQL Anniversary
Previous Message Bruce Momjian 2005-11-29 23:51:53 Re: Using multi-row technique with COPY

Browse pgsql-patches by date

  From Date Subject
Next Message Michael Meskes 2005-11-30 13:01:24 Re: Add missing const qualifier in ECPG
Previous Message Martijn van Oosterhout 2005-11-29 11:25:28 Re: Reduce dependancies of postmaster (without --as-needed)