Thread: Database growth problem
Hi, I have about 15 different PC's running RedHat 7.3 Linux, each running the same database backed application (I use Postgresql 7.2.1 for the database) on each machine. The application essentially stores files on each of the PC's and the database on each machine stores information relevant to the files that are stored on that machine. The files themselves are stored on a RAID on each PC, around 300 GB in size. As each file is about .5MB in size, thus about 600,000 files are stored and managed by the Postgresql database on each machine. I note that the database is about 1GB in size on each of the machines. I am constantly receiving new files, and as the PC's fill with files, I simply add additonal PC to my farm of PC's. These databases therefore never have any data deleted from the databases. I just add files, and corresponding database entries each day, until I fill the machine with files. I run vacuum analyze on these databases about 3 times a day. I also keep one machine slightly different than the others. On this machine, as a backup to the other machines, instead of just storing files without deleting, I use a scheme that stores new files as they come in but also deletes the oldest files from the RAID and also deletes the corresponding entries describing those entries from the database. I use this machine as an element of redundancy to recent files on the other machines. On this system, I keep the RAID about 75% full of files, and thus I imagine that the Postgresql database should be about .75 of a GB in size. However, it isnt! I find that it keeps growing as time goes on. Its now about 3.9GB in size! In fact, I just had to move the database to another partition on my system disk, because it filled the var partition! But I run vaccuum analyze at the same frequency as on the PC's? How is it that deleting and saving and vacuum analyze doesnt keep it at a normal svelte <1Gb in size? What should I do to shrink it back down to its normal svelte shape? Thanks, Mitchell This way I have redundant copies of the most recent files that were stored on the other machines.
"mlaks" <mlaks@bellatlantic.net> wrote: > But I run vaccuum analyze at the same frequency as on the PC's? How is it that > deleting and saving and vacuum analyze doesnt keep it at a normal svelte <1Gb > in size? > > What should I do to shrink it back down to its normal svelte shape? Did you try to vacuum full and reindex the big tables ? Regards Gaetano Mendola
On Thu, Jul 17, 2003 at 10:32:24PM -0400, mlaks wrote:
> Hi,
>
> I have about 15 different PC's running RedHat 7.3 Linux, each running the
> same database backed application (I use Postgresql 7.2.1 for the database)
Upgrade that to 7.2.4, at least. Noo need to dump and restore, but
you get saved from some nasty bugs.
> On this system, I keep the RAID about 75% full of files, and thus I imagine
> that the Postgresql database should be about .75 of a GB in size. However, it
> isnt! I find that it keeps growing as time goes on. Its now about 3.9GB in
> size!
Could be that your free space map is too small; on 7.2, the default
is at least an order of magnitude too small. Also, perhaps you need
to reindex: the btrees in Postgres have a nasty habit of growing
without bound. (That's fixed in current CVS sources.)
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
On Thu, 17 Jul 2003, mlaks wrote: > Hi, > > I have about 15 different PC's running RedHat 7.3 Linux, each running the > same database backed application (I use Postgresql 7.2.1 for the database) > on each machine. Upgrade to 7.2.4 as soon as possible. There are bugs in 7.2.3 and before that can cause data loss. > I also keep one machine slightly different than the others. > > On this machine, as a backup to the other machines, instead of just storing > files without deleting, I use a scheme that stores new files as they come in > but also deletes the oldest files from the RAID and also deletes the > corresponding entries describing those entries from the database. I use this > machine as an element of redundancy to recent files on the other machines. > > On this system, I keep the RAID about 75% full of files, and thus I imagine > that the Postgresql database should be about .75 of a GB in size. However, it > isnt! I find that it keeps growing as time goes on. Its now about 3.9GB in > size! This sounds like index growth. I'm guessing you're indexing on a field that only increments, and the btree is growing in one direction only. There's a fix in for 7.4 for this, but for now, all you likely need to do is reindex the index that keeps growing. you can use oid2name (in /contrib/oid2name, easy install) and 'du -s' to figure out which files are the biggest and what they belong to. cd $PGDATA/base oid2name cd oidfromlastcommand du -s *|sort -n #<- this will put the biggest at the bottom of the list oid2name -d dbname |grep oid-from-previous-du-s
7/18/03 10:06:29 AM, "scott.marlowe" <scott.marlowe@ihs.com> wrote: >On Thu, 17 Jul 2003, mlaks wrote: > >> Hi, >> >> I have about 15 different PC's running RedHat 7.3 Linux, each running the >> same database backed application (I use Postgresql 7.2.1 for the database) >> on each machine. > >Upgrade to 7.2.4 as soon as possible. There are bugs in 7.2.3 and before >that can cause data loss. > >> I also keep one machine slightly different than the others. >> >> On this machine, as a backup to the other machines, instead of just storing >> files without deleting, I use a scheme that stores new files as they come in >> but also deletes the oldest files from the RAID and also deletes the >> corresponding entries describing those entries from the database. I use this >> machine as an element of redundancy to recent files on the other machines. >> >> On this system, I keep the RAID about 75% full of files, and thus I imagine >> that the Postgresql database should be about .75 of a GB in size. However, it >> isnt! I find that it keeps growing as time goes on. Its now about 3.9GB in >> size! > >This sounds like index growth. I'm guessing you're indexing on a field >that only increments, and the btree is growing in one direction only. >There's a fix in for 7.4 for this, but for now, all you likely need to do >is reindex the index that keeps growing. Just wondering ... Are you vacuuming the tables every now and then? I certainly didn't find out about it right away...