Re: Best OS for Postgres 8.2

On Tue, 8 May 2007, Trygve Laugstøl wrote:

>> currently ZFS is only available on Solaris, parts of it have been released
>> under GPLv2, but it doesn't look like enough of it to be ported to Linux
>> (enough was released for grub to be able to access it read-only, but not
>> the full filesystem). there are also patent concerns that are preventing
>> any porting to Linux.
>
> This is not entirely correct. ZFS is only under the CDDL license and it has
> been ported to FreeBSD.
>
> http://mail.opensolaris.org/pipermail/zfs-discuss/2007-April/026922.html
>
I wonder how they handled the license issues? I thought that if you
combined stuff that was BSD licensed with stuff with a more restrictive
license the result was under the more restrictive license. thanks for the
info.

here's a link about the GPLv2 stuff for zfs

http://blogs.sun.com/darren/entry/zfs_under_gplv2_already_exists
>From pgsql-performance-owner(at)postgresql(dot)org Tue May 8 07:22:31 2007
Received: from localhost (maia-4.hub.org [200.46.204.183])
by postgresql.org (Postfix) with ESMTP id 3ED559FB28A
for <pgsql-performance-postgresql(dot)org(at)postgresql(dot)org>; Tue, 8 May 2007 07:22:29 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
with ESMTP id 58273-05 for <pgsql-performance-postgresql(dot)org(at)postgresql(dot)org>;
Tue, 8 May 2007 07:22:26 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from oxford.xeocode.com (unknown [62.232.55.118])
by postgresql.org (Postfix) with ESMTP id 1ACC39FB1B7
for <pgsql-performance(at)postgresql(dot)org>; Tue, 8 May 2007 07:22:26 -0300 (ADT)
Received: from localhost ([127.0.0.1] helo=oxford.xeocode.com)
by oxford.xeocode.com with esmtp (Exim 4.67)
(envelope-from <stark(at)enterprisedb(dot)com>)
id 1HlMpr-00010j-V3; Tue, 08 May 2007 11:22:24 +0100
From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: "Pomarede Nicolas" <npomarede(at)corp(dot)free(dot)fr>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: truncate a table instead of vaccum full when count(*) is 0
In-Reply-To: <Pine(dot)LNX(dot)4(dot)64(dot)0705081125140(dot)20675(at)localhost> (Pomarede Nicolas's
message of "Tue, 8 May 2007 11:43:14 +0200 (CEST)")
Organization: EnterpriseDB
References: <Pine(dot)LNX(dot)4(dot)64(dot)0705081125140(dot)20675(at)localhost>
X-Draft-From: ("nnimap+mail01.enterprisedb.com:INBOX.performance" 216)
Date: Tue, 08 May 2007 11:22:23 +0100
Message-ID: <87d51b1uow(dot)fsf(at)oxford(dot)xeocode(dot)com>
User-Agent: Gnus/5.110006 (No Gnus v0.6) Emacs/21.4 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200705/100
X-Sequence-Number: 24490

"Pomarede Nicolas" <npomarede(at)corp(dot)free(dot)fr> writes:

> But for the data (dead rows), even running a vacuum analyze every day is not
> enough, and doesn't truncate some empty pages at the end, so the data size
> remains in the order of 200-300 MB, when only a few effective rows are there.

Try running vacuum more frequently. Once per day isn't very frequent for
vacuum, every 60 or 30 minutes isn't uncommon. For your situation you might
even consider running it continuously in a loop.

> I see in the 8.3 list of coming changes that the FSM will try to re-use pages
> in a better way to help truncating empty pages. Is this correct ?

There are several people working on improvements to vacuum but it's not clear
right now exactly what we'll end up with. I think most of the directly vacuum
related changes wouldn't actually help you either.

The one that would help you is named "HOT". If you're interested in
experimenting with an experimental patch you could consider taking CVS and
applying HOT and seeing how it affects you. Or if you see an announcement that
it's been comitted taking a beta and experimenting with it before the 8.3
release could be interesting. Experiments with real-world databases can be
very helpful for developers since it's hard to construct truly realistic
benchmarks.

> So, I would like to truncate the table when the number of rows reaches 0 (just
> after the table was processed, and just before some new rows are added).
>
> Is there an easy way to do this under psql ? For example, lock the table, do a
> count(*), if result is 0 row then truncate the table, unlock the table (a kind
> of atomic 'truncate table if count(*) == 0').
>
> Would this work and what would be the steps ?

It would work but you may end up keeping the lock for longer than you're happy
for. Another option to consider would be to use CLUSTER instead of vacuum full
though the 8.2 CLUSTER wasn't entirely MVCC safe and I think in your situation
that might actually be a problem. It would cause transactions that started
before the cluster (but didn't access the table before the cluster) to not see
any records after the cluster.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com