DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=cCBaivcnAI/7vZ7+VHVqfq2ghbyYvts8bq+w3wrUvSMMDsXJMoDUAslaAC7SfTzuQaUxUQCT85FdSY/ImlaxZqMcTix7pduavmfbLGk4Ie2cvvyZUONQ9b5eA62Mx4bK6/L/7CdIrdoL9xj5ic7bd/uu441aE8Bw7X6W25pz7MI=
Message-ID: <f3c0b40805062819546605c0cc@mail.gmail.com>
Date: Tue, 28 Jun 2005 21:54:47 -0500
From: Matthew Nuzum <mattnuzum@gmail.com>
Reply-To: newz@bearfruit.org
To: Rudi Starcevic <tech@wildcash.com>
Subject: Re: optimized counting of web statistics
Cc: Postgresql Performance list <pgsql-performance@postgresql.org>
In-Reply-To: <42C2D7B5.4070707@wildcash.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <6EE64EF3AB31D5448D0007DD34EEB3415C2C08@Herge.rcsinc.local>
	<22455e7050628133978b17cd4@mail.gmail.com>
	<f3c0b408050628145545699c4e@mail.gmail.com>
	<42C2D7B5.4070707@wildcash.com>

On 6/29/05, Rudi Starcevic <tech@wildcash.com> wrote:
> Hi,
>=20
> >I do my batch processing daily using a python script I've written. I
> >found that trying to do it with pl/pgsql took more than 24 hours to
> >process 24 hours worth of logs. I then used C# and in memory hash
> >tables to drop the time to 2 hours, but I couldn't get mono installed
> >on some of my older servers. Python proved the fastest and I can
> >process 24 hours worth of logs in about 15 minutes. Common reports run
> >in < 1 sec and custom reports run in < 15 seconds (usually).
> >
> >
>=20
> When you say you do your batch processing in a Python script do you mean
> a you are using 'plpython' inside
> PostgreSQL or using Python to execut select statements and crunch the
> data 'outside' PostgreSQL?
>=20
> Your reply is very interesting.

Sorry for not making that clear... I don't use plpython, I'm using an
external python program that makes database connections, creates
dictionaries and does the normalization/batch processing in memory. It
then saves the changes to a textfile which is copied using psql.

I've tried many things and while this is RAM intensive, it is by far
the fastest aproach I've found. I've also modified the python program
to optionally use disk based dictionaries based on (I think) gdb. This
signfincantly increases the time to closer to 25 min. ;-) but drops
the memory usage by an order of magnitude.

To be fair to C# and .Net, I think that python and C# can do it
equally fast, but between the time of creating the C# version and the
python version I learned some new optimization techniques. I feel that
both are powerful languages. (To be fair to python, I can write the
dictionary lookup code in 25% (aprox) fewer lines than similar hash
table code in C#. I could go on but I think I'm starting to get off
topic.)
--=20
Matthew Nuzum
www.bearfruit.org