Обсуждение: Help on maintaining pgsql/data folder size

Hi all,

I am using PostgreSQL 7.4.3. The problem I am facing is that the size of pgsql/data folder keeps increasing and at a point the it takes up all the disk space and it freezes the system. The pg_xlog and the pg_clog folders are taking up most of the disk space. In the documentation, it says that I have to move the pg_xlog folder to some other location and create a symbolic link to that path from the original path. But the problem is for our application, we are using a 512 MB compact flash and there is no harddisk....so I need to check the database folder size somehow. Could anyone help me out in this regard.

Between in order to recover the disk space, I am using a script which vacuums the database regularly.

Thanks in advance for all your valuable inputs.

Regards,
Pradeep

"Pradeepkumar, Pyatalo (IE10)" <Pradeepkumar.Pyatalo@honeywell.com> writes:
> I am using PostgreSQL 7.4.3. The problem I am facing is that the size of
> pgsql/data folder keeps increasing and at a point the it takes up all the
> disk space and it freezes the system. The pg_xlog and the pg_clog folders
> are taking up most of the disk space. In the documentation, it says that I
> have to move the pg_xlog folder to some other location and create a symbolic
> link to that path from the original path. But the problem is for our
> application, we are using a 512 MB compact flash and there is no
> harddisk...

pg_xlog shouldn't grow unreasonably big unless you've somehow turned off
checkpointing.  pg_clog shouldn't grow unreasonably big unless you've
neglected appropriate vacuuming procedures (see the manual).  So I think
this is mostly pilot error.

It might be worth your while to reduce the size of xlog segments --- if
you have three or four 8M or 4M segments instead of three or four 16M
segments, that makes a difference when you're trying to fit in 512M.
It would require some fooling with the source code to make that happen
in 7.4; you should consider moving to 8.0 where there's just one
configuration #define to adjust.

In general though I wonder whether you shouldn't have picked another
database.  PG isn't really designed for that sort of environment.
Aside from the fact that we aren't targeting a tiny disk footprint,
we expect to rewrite the WAL files again and again and again.  What's
the write-cycles lifetime spec for your flash?

            regards, tom lane

-----Original Message-----
From: Pradeepkumar, Pyatalo (IE10) [mailto:Pradeepkumar.Pyatalo@honeywell.com]
Sent: Montag, 07. März 2005 08:08
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Help on maintaining pgsql/data folder size

Hi all,

I am using PostgreSQL 7.4.3. The problem I am facing is that the size of pgsql/data folder keeps increasing and at a point the it takes up all the disk space and it freezes the system. The pg_xlog and the pg_clog folders are taking up most of the disk space. In the documentation, it says that I have to move the pg_xlog folder to some other location and create a symbolic link to that path from the original path. But the problem is for our application, we are using a 512 MB compact flash and there is no harddisk....so I need to check the database folder size somehow. Could anyone help me out in this regard.

Between in order to recover the disk space, I am using a script which vacuums the database regularly.

Thanks in advance for all your valuable inputs.

Regards,
Pradeep

Hi Tom,

> pg_xlog shouldn't grow unreasonably big unless you've somehow turned off
checkpointing.  pg_clog shouldn't grow unreasonably big unless you've
neglected
> appropriate vacuuming procedures (see the manual).  So I think this is
mostly pilot error.

After going through the manual, I changed the checkpoint_segments parameter
in postgresql.conf file to 1 from default of 3. With this setting as per the
manual, there wont be more than 4 segments each of 16 MB. If this is the
case, then there is no issues with the disk space. I don't want it to
increase more than 64 MB. As for as vaccuming is considered...i have written
a script file which will vacuum the database at regular intervals.

> It might be worth your while to reduce the size of xlog segments --- if
you have three or four 8M or 4M segments instead of three or four 16M
segments, that makes a
> difference when you're trying to fit in 512M.
> It would require some fooling with the source code to make that happen in
7.4; you should consider moving to 8.0 where there's just one configuration
#define to adjust.

Well if fooling with the source code is not worth a major risk...then I
would be interested in doing that. In future we may consider 8.0 but as of
now NO.

> In general though I wonder whether you shouldn't have picked another
database.  PG isn't really designed for that sort of environment.

Before deciding on PgSQL, we did consider other databases like mySQL, Sybase
and the likes....but out of them PgSQL seemed to be the best of the lot
because of its support to triggers and stored procedures, so the effort
required is less. Only compromise is on the performance.

> Aside from the fact that we aren't targeting a tiny disk footprint, we
expect to rewrite the WAL files again and again and again.  What's the
write-cycles lifetime spec for > your flash?

Well I have no idea regarding the write-cycles lifetime of the flash...I
have to check out with the hardware ppl.

Regards,
Pradeep