Обсуждение: Database taking up space rapidly

Hi All,

I just moved over a db from 7.2.3 to 7.4.0 on a new RH9 server.

The dump, I remember was about 2GB, 350MB compressed, when I restored it only about 3-4 days ago…

Now, the slice stands at about 30GB!!
/dev/sda2             50830616  30034872  18213700  63% /var/lib/pgsql

The server seems a bit slow in responding, and the Postmaster is taking up a lot of CPU.

Here's where I find the bulk of the large files:

# pwd
/var/lib/pgsql/data/base/17142

Some of the files in this directory are:

-rw-------    1 postgres postgres 1073741824 Dec 23 18:57 17610
-rw-------    1 postgres postgres 1073741824 Dec 23 22:41 17610.1
-rw-------    1 postgres postgres 1073741824 Dec 25 16:21 17610.10
-rw-------    1 postgres postgres 1073741824 Dec 25 21:57 17610.11
-rw-------    1 postgres postgres 1073741824 Dec 26 03:44 17610.12
-rw-------    1 postgres postgres 1073741824 Dec 26 09:33 17610.13
-rw-------    1 postgres postgres 1073741824 Dec 26 15:38 17610.14
-rw-------    1 postgres postgres 1073741824 Dec 26  2003 17610.15
-rw-------    1 postgres postgres 1073741824 Dec 27  2003 17610.16
-rw-------    1 postgres postgres 1073741824 Dec 26 15:06 17610.17
-rw-------    1 postgres postgres 214654976 Dec 26 16:40 17610.18
-rw-------    1 postgres postgres 1073741824 Dec 24 02:44 17610.2
-rw-------    1 postgres postgres 1073741824 Dec 24 06:55 17610.3
-rw-------    1 postgres postgres 1073741824 Dec 24 11:12 17610.4
-rw-------    1 postgres postgres 1073741824 Dec 24 15:41 17610.5
-rw-------    1 postgres postgres 1073741824 Dec 24 20:14 17610.6
-rw-------    1 postgres postgres 1073741824 Dec 25 00:55 17610.7
-rw-------    1 postgres postgres 1073741824 Dec 25 05:46 17610.8
-rw-------    1 postgres postgres 1073741824 Dec 25 10:58 17610.9

Not sure what's going on here…also vacuum analyzed the db a few minutes ago.

Is there a way I can find out who is connected to the db and what process is running under the postmaster?

Appreciate any help.

Thanks,
Anjan

**************************************************************************
This e-mail and any files transmitted with it are intended for the use of the addressee(s) only and may be confidential and covered by the attorney/client and other privileges.  If you received this e-mail in error, please notify the sender; do not disclose, copy, distribute, or take any action in reliance on the contents of this information; and delete it from your system. Any other use of this e-mail is prohibited.

"Anjan Dave" <adave@vantage.com> writes:
> Here's where I find the bulk of the large files:
> -rw-------    1 postgres postgres 1073741824 Dec 23 18:57 17610
> -rw-------    1 postgres postgres 1073741824 Dec 23 22:41 17610.1
> [ etc ]

So, which table or index has relfilenode 17610 (look in pg_class)?
What sorts of operations are you doing on it?

> Not sure what's going on here...also vacuum analyzed the db a few
> minutes ago.

The question is more like how often do you run vacuum.  You probably
need more frequent routine vacuumings, larger FSM settings, or a
combination.

            regards, tom lane

Tom,
 
I was able to locate a bad reporting program (apparently the pg update to 7.4 caused some mismatched datatypes, i
believe,from the logs.) that was causing this.
 
 
I will look into pg_class for the relfilenode 17610 and others to see if i can free  up the space already taken up...
 
Thanks,
Anjan
 
-----Original Message----- 
From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
Sent: Fri 12/26/2003 6:29 PM 
To: Anjan Dave 
Cc: pgsql-admin@postgresql.org 
Subject: Re: [ADMIN] Database taking up space rapidly 



    "Anjan Dave" <adave@vantage.com> writes: 
    > Here's where I find the bulk of the large files: 
    > -rw-------    1 postgres postgres 1073741824 Dec 23 18:57 17610 
    > -rw-------    1 postgres postgres 1073741824 Dec 23 22:41 17610.1 
    > [ etc ] 

    So, which table or index has relfilenode 17610 (look in pg_class)? 
    What sorts of operations are you doing on it? 

    > Not sure what's going on here...also vacuum analyzed the db a few 
    > minutes ago. 

    The question is more like how often do you run vacuum.  You probably 
    need more frequent routine vacuumings, larger FSM settings, or a 
    combination. 

                            regards, tom lane