I recently lost a hard drive and had to re-install, so the PG version I am
using is only about 3 weeks old. However, as I said in my original post, I
have A LOT of data in this database. Brick* implies 1334 tables, some of
which have 2.5M records... All told, I have about 355G of data. I don't
care if it takes DAYS to run queries like this, but I need them to finish.
Ok, you may be thinking that I am nuts to try to store this much data and to
break it up into so many tables. Well, I used to have it all in one
table.... It took several days to vacuum it, and because inserts are
happening all the time, I was unable to have the tables indexed and still be
able to keep up with the incoming data. This new structure addresses these
and other issues.
Any help would be most appreciated.
Mike Diehl,
Network Monitoring Tool Devl.
Sandia National Laboratories.
(505) 284-3137
jdiehl@sandia.gov
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: September 18, 2001 5:26 PM
> To: darcy@druid.net
> Cc: jdiehl@sandia.gov; pgsql-sql@postgresql.org
> Subject: Re: [SQL] Out of free buffers... HELP!
>
>
> darcy@druid.net (D'Arcy J.M. Cain) writes:
> > Thus spake Diehl, Jeffrey
> >> psql ids -c 'select src,dst,count(*) from brick* where src_port=135
> >> group by src,dst' > /tmp/135.dat
>
> > Hard to tell without knowing more but perhaps you need another table
> > instead of/in addition to this one that just tracks the counts. You
> > can use a trigger to increment it. If you only allow inserts this
> > should be easy. Deletes are easy too. Updates are a
> little trickier.
>
> The thing is, you shouldn't ever get "out of free buffers", period.
> Jeffrey must be running into some kind of buffer-leak bug ... but it's
> hard to think what, unless he's running an extremely old PG version.
> We haven't seen reports of buffer leaks in a long time. So I'd like
> to run that down, quite independently of whether he should change his
> query for efficiency.
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>