Re: Benchmarking tools, methods - Mailing list pgsql-performance

On Nov 19, 2011, at 11:21 AM, Greg Smith wrote:

> 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
ofworking, 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
jobsthat 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
soI 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,
willhit the database as fast as it will process work.  That's not realistic.  You really need to consider that real
applicationshave 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.

Thanks so much for this, and thanks to Cédric for also pointing out Tsung specifically on that page.  I had no idea any
ofthese tools existed.  I really like the idea of "application specific" testing, it makes total sense for the kind of
thingswe're trying to measure. 

I also wanted to thank everyone else that posted in this thread, all of this info is tremendously helpful.  This is a
reallyexcellent list, and I really appreciate all the people posting here that make their living doing paid consulting
takingthe time to monitor and post on this list.  Yet another way for me to validate choosing postgres over that
"other"open source db. 


>> 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
almostno overlap between those sections and the wiki, which is mainly focused on PostgreSQL usage issues.  Unless
you'remuch smarter than me,  you can expect to spent months to years reinventing wheels described there before reaching
newground in the areas it covers.  From the questions you've been asking, you may not find as much about ZFS tuning and
SSDsas you'd like though. 

We're grabbing a copy of it for the office.  Packt is running a sale, so we're also going to grab the "cookbook", it
looksintriguing. 

> 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.

Looks like good stuff, thanks.

Charles

> --
> Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
> PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance