[GENERAL] 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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Load testing across 2 machines
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Load testing across 2 machines
On Sun, 09 Apr 2006 17:00:14 +0100 Simon Riggs <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Load testing across 2 machines
Gavin Hamill wrote: On Sun, 09 Apr 2006 17:00:14 +0100 Simon Riggs <[EMAIL PROTECTED]> 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/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Load testing across 2 machines
On Sun, 09 Apr 2006 17:00:14 +0100 Simon Riggs <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Load testing across 2 machines
Gavin Hamill wrote: On Sun, 09 Apr 2006 17:00:14 +0100 Simon Riggs <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match