DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=buwPJP0LhxCRGTxBytowFMatyl3pIwvuyp1XxDUrEV+6tDCmHM7hajgl5e/uquHk4c
	YWmeRxDIUB4dOO6LAipn4oQfkp563w+JzKCFPPczw06VA01uT/xB2LQqdsFxPk4vdin6
	czOATMk5tWhm/XbLCEcI3dch7AlLXBc4PWgnI=
MIME-Version: 1.0
In-Reply-To: <4B465E7B.3060904@2ndquadrant.com>
References: <852badbc1001070438j36eb10d3qa22398ffcbef405f@mail.gmail.com>
	<4B45E36E.4040706@tweakers.net>
	<852badbc1001070547p48b47175vf87efc473e3bd41b@mail.gmail.com>
	<26014999.20100107150535@erwied.eu>
	<852badbc1001070610p698dc3cfr4628b1e7768990e6@mail.gmail.com>
	<4B465E7B.3060904@2ndquadrant.com>
Date: Thu, 7 Jan 2010 23:57:04 +0100
Message-ID: <852badbc1001071457m42d4042brbb5c6b5fa7efce20@mail.gmail.com>
Subject: Re: Air-traffic benchmark
From: Lefteris <lsidir@gmail.com>
To: Greg Smith <greg@2ndquadrant.com>
Cc: Jochen Erwied <jochen@pgsql-performance.erwied.eu>,
	pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi Greg,

thank you for your help. The changes I did on the dataset was just
removing the last comma from the CSV files as it was interpreted by pg
as an extra column. The schema I used, the load script and queries can
be found at:

http://homepages.cwi.nl/~lsidir/postgres/

(I understood that if I attach these files here, my email will not
reach the list so I give you a link to download them).

Also since you are interesting on the benchmark, you can also check

http://homepages.cwi.nl/~mk/ontimeReport

for a report of various experiments with MonetDB and comparison with
previously published numbers.

The schema I used for pg is slightly different from that one of
MonetDB since the delay fields could not be parsed by pg as integers
but only as varchar(4). Hence the extra index on DepDelay field:)

Also at http://homepages.cwi.nl/~lsidir/PostgreSQL-ontimeReport you
can see the detailed times I got from postgres.

I really appreciate your help! this is a great opportunity for me to
get some feeling and insights on postgres since I never had the chance
to use it in a large scale project.

lefteris


On Thu, Jan 7, 2010 at 11:21 PM, Greg Smith <greg@2ndquadrant.com> wrote:
> Lefteris wrote:
>>
>> So we all agree that the problem is on the scans:)
>>
>> So the next question is why changing shared memory buffers will fix
>> that? i only have one session with one connection, do I have like many
>> reader workers or something?
>>
>
> I wouldn't expect it to. =A0Large sequential scans like this one are opti=
mized
> in PostgreSQL to only use up a small portion of the shared_buffers cache.
> =A0Allocating more RAM to the database won't improve the fact that you're
> spending the whole time waiting for physical I/O to happen very much.
>
> What might help is increasing effective_cache_size a lot though, because
> there you might discover the database switching to all new sorts of plans
> for some of these queries. =A0But, again, that doesn't impact the situati=
on
> where a sequential scan is the only approach.
>
> I have this whole data set on my PC already and have been trying to find
> time to get it loaded and start my own tests here, it is a quite interest=
ing
> set of information. =A0Can you tell me what you had to do in order to get=
 it
> running in PostgreSQL? =A0If you made any customizations there, I'd like =
to
> get a copy of them. =A0Would save me some time and help me get to where I
> could give suggestions out if I had a "pgdumpall --schema-only" dump from
> your database for example, or however you got the schema into there, and =
the
> set of PostgreSQL-compatible queries you're using.
>
> By the way: =A0if anybody else wants to join in, here's a script that
> generates a script to download the whole data set:
>
> #!/usr/bin/env python
> for y in range(1988,2010):
> =A0 for m in range(1,13):
> =A0 =A0 =A0 print "wget --limit-rate=3D100k
> http://www.transtats.bts.gov/Download/On_Time_On_Time_Performance_%s_%s.z=
ip"
> % (y,m)
>
> It's 3.8GB of download that uncompresses into 46GB of CSV data, which is =
why
> I put the rate limiter on there--kept it from clogging my entire Internet
> connection.
>
> --
> Greg Smith =A0 =A02ndQuadrant =A0 Baltimore, MD
> PostgreSQL Training, Services and Support
> greg@2ndQuadrant.com =A0www.2ndQuadrant.com
>
>