Message-ID: <4DB890D8.5000604@2ndquadrant.com>
Date: Wed, 27 Apr 2011 17:55:36 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Tomas Vondra <tv@fuzzy.cz>
CC: pgsql-performance@postgresql.org
Subject: Re: Performance
References: <DF2D5436-117C-4D02-9B3C-A55723B7DDE1@darkstatic.com>
	<20110412171855.GA14292@tux>
	<FC3A3A2B-3ECB-41BA-8F94-356D6FED3695@darkstatic.com>
	<4DA496FA.3070908@fuzzy.cz>
	<8F22D592-23C1-4A3C-94A5-48363332ADD3@darkstatic.com>
	<4DA4BFA2.5060601@fuzzy.cz>
	<B93DFBBB-DA56-4044-A508-0B7E4A2CFD28@darkstatic.com>
	<4DA4D40A.4010200@fuzzy.cz>
	<A0B9339E-9D58-4842-A206-050B773360B6@darkstatic.com>
	<4DA56DA8020000250003C783@gw.wicourts.gov>
	<BANLkTinJqXqErAtQg--1MOaw2LJNFZmwOg@mail.gmail.com>
	<BANLkTimyWkoX8Dj=4CKAjhY82ibru-An7g@mail.gmail.com>
	<4DA6216E.9020907@fuzzy.cz>
	<BANLkTim_m-oz3UMXEKUdFjWJ6uuAza79cw@mail.gmail.com>
	<4DA63125.5070106@fuzzy.cz>
	<BANLkTikYtnzTfS8Yjc-3ap9kysXLLpJwmg@mail.gmail.com>
	<5c6c67e9f0c4abed2b7ac84e83fe1f32.squirrel@sq.gransy.com>
	<4DB8652D.7010804@2ndquadrant.com>
	<4DB820A0020000250003CF5C@gw.wicourts.gov>
	<4DB87F7F.8060403@fuzzy.cz>
In-Reply-To: <4DB87F7F.8060403@fuzzy.cz>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Tomas Vondra wrote:
> Hmmm, just wondering - what would be needed to build such 'workload
> library'? Building it from scratch is not feasible IMHO, but I guess
> people could provide their own scripts (as simple as 'set up a a bunch
> of tables, fill it with data, run some queries') and there's a pile of
> such examples in the pgsql-performance list.
>   

The easiest place to start is by re-using the work already done by the 
TPC for benchmarking commercial databases.  There are ports of the TPC 
workloads to PostgreSQL available in the DBT-2, DBT-3, and DBT-5 tests; 
see http://wiki.postgresql.org/wiki/Category:Benchmarking for initial 
information on those (the page on TPC-H is quite relevant too).  I'd 
like to see all three of those DBT tests running regularly, as well as 
two tests it's possible to simulate with pgbench or sysbench:  an 
in-cache read-only test, and a write as fast as possible test.

The main problem with re-using posts from this list for workload testing 
is getting an appropriately sized data set for them that stays 
relevant.  The nature of this sort of benchmark always includes some 
notion of the size of the database, and you get different results based 
on how large things are relative to RAM and the database parameters.  
That said, some sort of systematic collection of "hard queries" would 
also be a very useful project for someone to take on.

People show up regularly who want to play with the optimizer in some 
way.  It's still possible to do that by targeting specific queries you 
want to accelerate, where it's obvious (or, more likely, hard but still 
straightforward) how to do better.  But I don't think any of these 
proposed exercises adjusting the caching model or default optimizer 
parameters in the database is going anywhere without some sort of 
benchmarking framework for evaluating the results.  And the TPC tests 
are a reasonable place to start.  They're a good mixed set of queries, 
and improving results on those does turn into a real commercial benefit 
to PostgreSQL in the future too.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books