Thread: FunctionCall2 performance
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/
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
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
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)
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