I was comparing the oprofile results of our OLTP workload between 
7.5devel and 8.0beta3 test and noticed a new FunctionCall2 in the
8.0beta3 profile that's on the top of the list for PostgreSQL.  I
see about an 32% decrease in overall performance.  Any suggestions
for determining if FunctionCall2 is really having that kind of an
impact on the test?

Links to results are here:
7.5devel - http://www.osdl.org/projects/dbt2dev/results/dev4-010/128/
8.0beta3 - http://www.osdl.org/projects/dbt2dev/results/dev4-010/180/

Unfortunitely, I lost the database parameters for 7.5devel. :(

-- 
Mark Wong - - markw@osdl.org
Open Source Development Lab Inc - A non-profit corporation
12725 SW Millikan Way - Suite 400 - Beaverton, OR 97005
(503) 626-2455 x 32 (office)
(503) 626-2436      (fax)
http://developer.osdl.org/markw/

Mon, 4 Oct 2004 09:21:20 -0700
Mark Wong <markw@osdl.org>
FunctionCall2 performance
Mark Wong <markw@osdl.org> writes:

> Links to results are here:
> 7.5devel - http://www.osdl.org/projects/dbt2dev/results/dev4-010/128/
> 8.0beta3 - http://www.osdl.org/projects/dbt2dev/results/dev4-010/180/

Are those cyclic spikes an artifact of the load of the tests? Or are they
artifacts of the postgres checkpoint process?

If they're an artifact of the test is it possible to randomize the number
iterations and time each connection does before issuing the slower queries? Or
somehow avoid generating these spikes?

If they're an artifact of the checkpoint process I wonder if there are
parameters that can be tweaked to smooth it out more. Some of those
transactions/second drops are quite dramatic. It seems like you're averaging
200 txn/s normally but having it drop down to less than half that
periodically.

-- 
greg


04 Oct 2004 14:42:43 -0400
Greg Stark <gsstark@mit.edu>
Re: FunctionCall2 performance
On Mon, Oct 04, 2004 at 02:42:43PM -0400, Greg Stark wrote:
> Mark Wong <markw@osdl.org> writes:
> 
> > Links to results are here:
> > 7.5devel - http://www.osdl.org/projects/dbt2dev/results/dev4-010/128/
> > 8.0beta3 - http://www.osdl.org/projects/dbt2dev/results/dev4-010/180/
> 
> Are those cyclic spikes an artifact of the load of the tests? Or are they
> artifacts of the postgres checkpoint process?
> 
> If they're an artifact of the test is it possible to randomize the number
> iterations and time each connection does before issuing the slower queries? Or
> somehow avoid generating these spikes?
> 
> If they're an artifact of the checkpoint process I wonder if there are
> parameters that can be tweaked to smooth it out more. Some of those
> transactions/second drops are quite dramatic. It seems like you're averaging
> 200 txn/s normally but having it drop down to less than half that
> periodically.
> 

In the past, they have been artifacts of the checkpoint process.  They
do smooth out as I increase the checkpoint segments and the timeout.
Although I know that doesn't exactly exclude other explanations.

There are 5000 emulated clients connected through 100 database connections
for both of those tests, attempting 1 of 5 different transactions.  I hope
that's random enough. :)

When I run a less intense workload, everything smooths out a bit more too
but it doesn't stress the system nearly as much.

Mark

Wed, 6 Oct 2004 10:01:42 -0700
Mark Wong <markw@osdl.org>
Re: FunctionCall2 performance
On Wed, Oct 06, 2004 at 10:01:42AM -0700, Mark Wong wrote:
> On Mon, Oct 04, 2004 at 02:42:43PM -0400, Greg Stark wrote:
> > Mark Wong <markw@osdl.org> writes:
> > 
> > > Links to results are here:
> > > 7.5devel - http://www.osdl.org/projects/dbt2dev/results/dev4-010/128/
> > > 8.0beta3 - http://www.osdl.org/projects/dbt2dev/results/dev4-010/180/
> > 
> > Are those cyclic spikes an artifact of the load of the tests? Or are they
> > artifacts of the postgres checkpoint process?
>
> In the past, they have been artifacts of the checkpoint process.  They
> do smooth out as I increase the checkpoint segments and the timeout.
> Although I know that doesn't exactly exclude other explanations.

Another knob to test would be bgwriter parameters, say bgwriter_percent
to maybe 5 or 10.  That should distribute more evenly the load.

http://archives.postgresql.org/pgsql-hackers/2004-09/msg00623.php

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La rebeldï¿½a es la virtud original del hombre" (Arthur Schopenhauer)


Wed, 6 Oct 2004 13:27:26 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: FunctionCall2 performance
On Wed, Oct 06, 2004 at 01:27:26PM -0400, Alvaro Herrera wrote:
> On Wed, Oct 06, 2004 at 10:01:42AM -0700, Mark Wong wrote:
> > On Mon, Oct 04, 2004 at 02:42:43PM -0400, Greg Stark wrote:
> > > Mark Wong <markw@osdl.org> writes:
> > > 
> > > > Links to results are here:
> > > > 7.5devel - http://www.osdl.org/projects/dbt2dev/results/dev4-010/128/
> > > > 8.0beta3 - http://www.osdl.org/projects/dbt2dev/results/dev4-010/180/
> > > 
> > > Are those cyclic spikes an artifact of the load of the tests? Or are they
> > > artifacts of the postgres checkpoint process?
> >
> > In the past, they have been artifacts of the checkpoint process.  They
> > do smooth out as I increase the checkpoint segments and the timeout.
> > Although I know that doesn't exactly exclude other explanations.
> 
> Another knob to test would be bgwriter parameters, say bgwriter_percent
> to maybe 5 or 10.  That should distribute more evenly the load.
> 
> http://archives.postgresql.org/pgsql-hackers/2004-09/msg00623.php
> 

Cool, I increased bgwriter_percent from the default to 10, and that
certainly smoothed things out:
	http://www.osdl.org/projects/dbt2dev/results/dev4-010/182/

The overall throughput didn't really improved though. :(

Mark

Thu, 7 Oct 2004 08:39:25 -0700
Mark Wong <markw@osdl.org>
Re: FunctionCall2 performance