Re: Freebsd vs linux and hardware question

On Fri, Sep 19, 2003 at 05:37:22PM -0700, Josh Berkus wrote:
> Aditya,
>
> > the main problem is that the data is a time-series and now() isn't
> > immutable under Postgres 7.2 meaning that indices on a timestamp field
> > don't work...ie.
> >
> > select * from weblogs where ts > (now() - interval '7 days');
>
> Yes, but there's an easy way around this ... simply call SELECT (NOW() - '7
> days'); from your client software, and pass that as a static timestamp to the
> query.

That's exactly the solution we chose in the end, even though it's ugly.

But actually one of the big problems was solved when we ran VACUUM FULL
on a problematic table. It shrank from 100 Megs to 6 Megs which on a
local disk isn't as big a problem as on a NAS.

We're also now running VACUUM ANALYZE on the table every five minutes
instead of once a day and that helps too.

>
> > will always use a sequential scan since now() isn't cacheable. Worse yet,
> > there is no "immutable" flag for functions under 7.2 so we can't wrap now()
> > in a function marked immutable...(thanks to Stephan Szabo who explained
> > this to me a few months ago on sfpug)
>
> True. And you don't want to mark it as ISCACHABLE, since under 7.2 that could
> mean getting a value in memory that is minutes or hours old.
>
> > a new database machine would allow us to use a newer version of Postgres
> > which could use an index and would avoid having to transfer an entire table
> > from the NAS for a sequential scan...(the network interface is a
> > bottle-neck in that case)
>
> Good to know .... sometime I'd like to talk to you about the cost/benefit of
> using a NAS for Postgres.

Here are the reasons I like our Netapp.

1. It's raid. If one of the disks fail, it will continue running. Since
it has hot swapable disks, it will rebuild the failed disk. You then
replace the bad disk with a good one.
2. You can add additional disk while it's running. Need to add another
36 Gigs -- no problem Just plug it in, run a command and your volume
will grow.
3. Reliable hardware with dual power supplies and all of that good
stuff. Netapp tests the disks and certifies them.
4. Writes are fast because of 256 Meg of NVRAM. In other words, when you
write data to the netapp, it writes it to battery backed RAM and acks
the operation to the client. Later it writes the actual data to disk in
larger blocks.
5. Reboots in a minute or so.

The main negative is that netapps are expensive. Ours was $10,000 for
200 Gig a couple of years ago.

Adi can tell you more.

Dror

>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco

--
Dror Matalon, President
Zapatec Inc
1700 MLK Way
Berkeley, CA 94709
http://www.zapatec.com