Database taking up space rapidly

[Anjan Dave]

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.

[Tom Lane]

Post by Anjan Dave
-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?

Post by Anjan Dave
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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend