I applied Jeff's patch but changed this to address concerns about the
program getting stuck running for too long in the function:
#define plpgsql_loops 512
This would be better named as "plpgsql_batch_size" or something similar
instead, the current name suggests it's how many loops to run which is
confusing.
My main performance concern here was whether this change really matter
so much once a larger number of clients were involved. Some of the
other things you can do to optimize single-client performance aren't as
useful with lots of them. Here's how the improvements in this mode
worked for me on a server with 4 Hyper-Threaded cores (i870);
shared_buffers=256MB, scale=100:
1 client:
-S: 11533
-S -M prepared: 19498
-P: 49547
12 clients, 4 workers:
-S: 56052
-S -M prepared: 82043
-P: 159443
96 clients, 8 workers:
-S: 49940
-S -M prepared: 76099
-P: 137942
I think this is a really nice new workload to demonstrate. One of the
things we tell people is that code works much faster when moved
server-side, but how much faster isn't always easy to show. Having this
mode available lets them see how dramatic that can be quite easily. I
know I'd like to be able to run performance tests for clients of new
hardware using PostgreSQL and tell them something like this: "With
simple clients executing a statement at a time, this server reaches 56K
SELECTs/section. But using server-side functions to execute them in
larger batches it can do 159K".
The value this provides for providing an alternate source for benchmark
load generation, with a very different profile for how it exercises the
server, is good too.
Things to fix in the patch before it would be a commit candidate:
-Adjust the loop size/name, per above
-Reformat some of the longer lines to try and respect the implied right
margin in the code formatting
-Don't include the "plgsql function created." line unless in debugging mode.
-Add the docs. Focus on how this measures how fast the database can
execute SELECT statements using server-side code. An explanation that
the "transaction" block size is 512 is important to share. It also
needs a warning that time based runs ("-T") may have to wait for a block
to finish and go beyond its normally expected end time.
-The word "via" in the "transaction type" output description is probably
not the best choice. Changing to "SELECT only using PL/pgSQL" would
translate better, and follow the standard case use for the name of that
language.
--
Greg Smith 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
