Re: Any better plan for this query?.. - Mailing list pgsql-performance

On 5/12/09, Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> wrote:
> Dimitri wrote:
>> Hi Stefan,
>>
>> sorry, I did not have a time to bring all details into the toolkit -
>> but at least I published it instead to tell a "nice story" about :-)
>
> fair point and appreciated. But it seems important that benchmarking
> results can be verified by others as well...

until now there were only people running Solaris or Linux :-))

>
>>
>> The client process is a binary compiled with libpq. Client is
>> interpreting a scenario script and publish via SHM a time spent on
>> each SQL request. I did not publish sources yet as it'll also require
>> to explain how to compile them :-)) So for the moment it's shipped as
>> a freeware, but with time everything will be available (BTW, you're
>> the first who asking for sources (well, except IBM guys who asked to
>> get it on POWER boxes, but it's another story :-))
>
> well there is no licence tag(or a copyright notice) or anything als
> associated with the download which makes it a bit harder than it really
> needs to be.
> The reason why I was actually looking for the source is that all my
> available benchmark platforms are none of the ones you are providing
> binaries for which kinda reduces its usefulness.
>

agree, will improve this point

>>
>> What is good is each client is publishing *live* its internal stats an
>> we're able to get live data and follow any kind of "waves" in
>> performance. Each session is a single process, so there is no
>> contention between clients as you may see on some other tools. The
>> current scenario script contains 2 selects (representing a Read
>> transaction) and delete/insert/update (representing Write
>> transaction). According a start parameters each client executing a
>> given number Reads per Write. It's connecting on the beginning and
>> disconnecting at the end of the test.
>
> well I have seen clients getting bottlenecked internally (like wasting
> more time in getting rid/absorbing of the actual result than it took the
> server to generate the answer...).
> How sure are you that your "live publishing of data" does not affect the
> benchmark results(because it kinda generates an artifical think time)
> for example?

On all my test tools client are publishing their data via shared
memory segment (ISM), all they do is just *incrementing* their current
stats values and continuing their processing. Another dedicated
program should be executed to print these stats - it's connecting to
the same SHM segment and printing a *difference* between values for
the current and the next interval. Let me know if you need more
details.

> But what I get from your answer is that you are basically doing one
> connect/disconnect per client and the testcase you are talking about has
> 256 clients?

Exactly, only one connect/disconnect per test, and number of clients
is growing progressively from 1, 2, 4, 8, 16, .. to 256

Rgds,
-Dimitri

>
>
> Stefan
>