Обсуждение: 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...