FAQ

On Sun, Jun 19, 2011 at 3:30 PM, Greg Smith wrote:
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.
Sorry it has taken me a year to get back to this patch. I have wanted
to use it, and to ask other people to run it and report their results,
several time recently, so I would like to get it into the core.

I've attached a new patch which addresses several of your concerns,
and adds the documentation. The description is much longer than the
descriptions of other nearby options, which mostly just give a simple
statement of what they do rather than a description of why that is
useful. I don't know if that means I'm starting a good trend, or a
bad one, or I'm just putting the exposition in the wrong place.

In addition to showing the benefits of coding things on the server
side when that is applicable, it also allows hackers to stress parts
of the server code that are not easy to stress otherwise.

Cheers,

Jeff

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 13 of 25 | next ›
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMay 29, '11 at 7:11p
activeJul 16, '12 at 1:57a
posts25
users9
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase