Message-ID: <4EC7D797.8010300@2ndQuadrant.com>
Date: Sat, 19 Nov 2011 11:21:43 -0500
From: Greg Smith <greg@2ndQuadrant.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.16) Gecko/20111110 Icedove/3.0.11
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Benchmarking tools, methods
References: <22E8DA4D-5D07-4B2B-920A-DA29650C1909@morefoo.com>
In-Reply-To: <22E8DA4D-5D07-4B2B-920A-DA29650C1909@morefoo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 11/18/2011 04:55 AM, CSS wrote:
> I'm also curious about benchmarking using my own data.  I tried something long ago that at least gave the illusion of working, but didn't seem quite right to me.  I enabled basic query logging on one of our busier servers, dumped the db, and let it run for 24 hours.  That gave me the normal random data from users throughout the day as well as our batch jobs that run overnight.  I had to grep out and reformat the actual queries from the logfile, but that was not difficult.   I then loaded the dump into the test server and basically fed the saved queries into it and timed the result.  I also hacked together a script to sample cpu and disk stats every 2S and had that feeding into an rrd database so I could see how "busy" things were.
>
> In theory, this sounded good (to me), but I'm not sure I trust the results.  Any suggestions on the general concept?  Is it sound?  Is there a better way to do it?  I really like the idea of using (our) real data.
>    

The thing that's hard to do here is replay the activity with the right 
timing.  Some benchmarks, such as pgbench, will hit the database as fast 
as it will process work.  That's not realistic.  You really need to 
consider that real applications have pauses in them, and worry about 
that both in playback speed and in results analysis.

See http://wiki.postgresql.org/wiki/Statement_Playback for some more 
info on this.

> ps - considering the new PostgreSQL Performance book that Packt has, any strong feelings about that one way or the other?  Does it go very far beyond what's on the wiki?
>    

Pages 21 through 97 are about general benchmarking and hardware setup; 
189 through 208 cover just pgbench.  There's almost no overlap between 
those sections and the wiki, which is mainly focused on PostgreSQL usage 
issues.  Unless you're much smarter than me,  you can expect to spent 
months to years reinventing wheels described there before reaching new 
ground in the areas it covers.  From the questions you've been asking, 
you may not find as much about ZFS tuning and SSDs as you'd like though.

http://www.2ndquadrant.com/en/talks/ has some updated material about 
things discovered since the book was published.  The "Bottom-Up Database 
Benchmarking" there shows the tests I'm running nowadays, which have 
evolved a bit in the last year.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us