Thread: Load testing across 2 machines

Hi,

I'm asking here in case this kind of thing has been done before, but
I've not been able to find it..

We have two pg 8.1.3 servers, one live and one test. What I'd like to do
is have something like pgpool to act as a connection broker, but
instead of using pgpool's own replication where all queries are sent to
both servers, and SELECTs are split between both servers, I'm aiming
for this scenario:

UPDATE/DELETE/INSERT go only to live, - Slony is replicating live
to test. This permits test to go offline if necessary and easily 'catch
up' later - much more convenient than pgpool's suggestion of 'stop both
servers, then rsync the db files from master to slave'.

SELECTS go to *both* live and test, but only the answers from live are
sent back to clients - the answers from test are discarded...

This would very gracefully allow the test machine to be monitored with
real workload but without any danger of affecting the performance of
the live system / returning bad data..

Has this been done already? Can it be done by extending pgpool or
otherwise without requiring C coding skills? :)

Cheers,
Gavin.

On Sat, 2006-04-08 at 15:10 +0100, Gavin Hamill wrote:

> SELECTS go to *both* live and test, but only the answers from live are
> sent back to clients - the answers from test are discarded...

Put log_min_duration_statement = 0 so all SELECTs go to the log.

Sniff the live log for SELECT statements (plus their live durations),
then route those same statements to the dev box and get a timing from
there also. That way you'll be able to do this without any C coding,
plus you'll have both the live and test elapsed times as a comparison.

Best Regards, Simon Riggs

On Sun, 09 Apr 2006 17:00:14 +0100
Simon Riggs <simon@2ndquadrant.com> wrote:

> Sniff the live log for SELECT statements (plus their live durations),

Wow, how wonderfully low-tech - hence it's right up my street :) Yay,
some tail + psql fun coming up!

Cheers,
Gavin.

Gavin Hamill wrote:
> On Sun, 09 Apr 2006 17:00:14 +0100
> Simon Riggs <simon@2ndquadrant.com> wrote:
>
>> Sniff the live log for SELECT statements (plus their live durations),
>
> Wow, how wonderfully low-tech - hence it's right up my street :) Yay,
> some tail + psql fun coming up!

You can even tell it to only show you queries that taken longer the (n)
where (n) is milliseconds.

Joshua D. Drake

>
> Cheers,
> Gavin.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>


--

             === The PostgreSQL Company: Command Prompt, Inc. ===
       Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
       Providing the most comprehensive  PostgreSQL solutions since 1997
                      http://www.commandprompt.com/

On Sun, 09 Apr 2006 17:00:14 +0100
Simon Riggs <simon@2ndquadrant.com> wrote:

> On Sat, 2006-04-08 at 15:10 +0100, Gavin Hamill wrote:
>
> > SELECTS go to *both* live and test, but only the answers from live
> > are sent back to clients - the answers from test are discarded...
>
> Put log_min_duration_statement = 0 so all SELECTs go to the log.
>
> Sniff the live log for SELECT statements (plus their live durations),
> then route those same statements to the dev box and get a timing from
> there also. That way you'll be able to do this without any C coding,
> plus you'll have both the live and test elapsed times as a comparison.

Ah, having eaten and had my brain finally switch on, I've realised that
there's an unfortunate flaw in the plan; only a single process will be
executing the SELECT-log which pretty much defeats the purpose of the
experiment to simulate identical load patterns on both machines.

I might be wrong, but if I just end up grepping for 'SELECT' then
feeding the results into psql, then only a single connection will be
made to the test server, and all queries will be processed serially on
a single CPU, no?

Cheers,
Gavin.

Gavin Hamill wrote:
> On Sun, 09 Apr 2006 17:00:14 +0100
> Simon Riggs <simon@2ndquadrant.com> wrote:
>
>> Sniff the live log for SELECT statements (plus their live durations),
>
> Wow, how wonderfully low-tech - hence it's right up my street :) Yay,
> some tail + psql fun coming up!

Be careful though - concurrency issues mean queries might not be
returning the same results, so not taking the same time anyway. Should
work nicely for load-testing though.

--
   Richard Huxton
   Archonet Ltd