Unexpected disk space growth controlling measures

Hi,

What are all the controlling factors/measures that one should consider/take
into account, when there is an unexpected growth in disk space usage of the
database?

Some of the factors that comes to my mind immediately are:
1) Running autovacuum daemon aggressively
2) Setting/tuning appropriate value for the parameters "max_fsm_pages" and
"max_fsm_relations"

Is there any other factors that I need to consider apart from the above?

Hope experts can help me in the right direction.

Regards,
Gnanam


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Unexpected disk space growth controlling measures

On Fri, Jul 30, 2010 at 11:18 PM, Gnanakumar wrote:
> Hi,
>
> What are all the controlling factors/measures that one should consider/take
> into account, when there is an unexpected growth in disk space usage of the
> database?
>
> Some of the factors that comes to my mind immediately are:
> 1) Running autovacuum daemon aggressively
> 2) Setting/tuning appropriate value for the parameters "max_fsm_pages" and
> "max_fsm_relations"

Those are both good steps. Note that they won't reduce bloat, just prevent it.

> Is there any other factors that I need to consider apart from the above?

Look into monitoring your database for bloat etc. Esp in 8.3 and
before where blowing out the free space map is quite easy to do and it
can go unnoticed for some time. A first simple step is to email
yourself the output of vacuum verbose every night to make sure you
haven't outrun fsm. Also, fsm is pretty cheap. Where I work we use
about 2.5M entries but have our fsm set to 10M so we don't have to
worry about blowing it out overnight or anything.

Lastly, make sure your IO subsystem can keep up. If you're on the
hairy edge, then vacuum may never be able to keep up.

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Unexpected disk space growth controlling measures

Hi Scott,

Thanks for your valuable suggestion. Our production server is running
PostgreSQL 8.2.3 on Red Hat 4.1.1-52.

> Esp in 8.3 and before where blowing out the free space map is quite easy
to do and it can go unnoticed for some time.

As you pointed out rightly, recently in our Production server there is a
warning showing up in VACUUM ANALYZE VERBOSE.
WARNING: relation "public.oneofmytable" contains more than "max_fsm_pages"
pages with useful free space
HINT: Consider compacting this relation or increasing the configuration
parameter "max_fsm_pages".

Currently, there are 439 tables and 743 indexes, adding up 1182 relations.
What would you recommend me to set the value for "max_fsm_pages" and
"max_fsm_relations" parameters?

> Where I work we use about 2.5M entries but have our fsm set to 10M so we
don't have to worry about blowing it out overnight or anything.
If you don't mind, can you make me clear here. Is 2.5M entries in a single
table or is it something else?

> Lastly, make sure your IO subsystem can keep up. If you're on the hairy
edge, then vacuum may never be able to keep up.
How do I confirm/make sure that IO subsystem can keep up?



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Unexpected disk space growth controlling measures

On Sat, Jul 31, 2010 at 1:00 AM, Gnanakumar wrote:
> Hi Scott,
>
> Thanks for your valuable suggestion. =A0Our production server is running
> PostgreSQL 8.2.3 on Red Hat 4.1.1-52.

Definitely look at updating to the latest 8.2 release, there's a lot
of bug fixes since 8.2.3.

>
>> =A0Esp in 8.3 and before where blowing out the free space map is quite e=
asy
> to do and it can go unnoticed for some time.
>
> As you pointed out rightly, recently in our Production server there is a
> warning showing up in VACUUM ANALYZE VERBOSE.
> WARNING: =A0relation "public.oneofmytable" contains more than "max_fsm_pa=
ges"
> pages with useful free space
> HINT: =A0Consider compacting this relation or increasing the configuration
> parameter "max_fsm_pages".
>
> Currently, there are 439 tables and 743 indexes, adding up 1182 relations.
> What would you recommend me to set the value for "max_fsm_pages" and
> "max_fsm_relations" parameters?

Usually I set max_fsm_pages to 2x or more whatever vacuum verbose says
it needs. As for max_fsm_relations, it only needs to be big enough
to hold all tables and indexes, so if you've got 1182, then 2000 or so
would be fine. I work with one db that has 50k or more tables and
indexes, and on that one we have it set to something lik 500k so we
don't hit the limit.

>> Where I work we use about 2.5M entries but have our fsm set to 10M so we
> don't have to worry about blowing it out overnight or anything.
> If you don't mind, can you make me clear here. =A0Is 2.5M entries in a si=
ngle
> table or is it something else?

max_fsm_pages is set to 10M

>> Lastly, make sure your IO subsystem can keep up. =A0If you're on the hai=
ry
> edge, then vacuum may never be able to keep up.
> How do I confirm/make sure that IO subsystem can keep up?

Keep an eye on your system with tools like iostat.

iostat -xd 10 /dev/sdb

for instance if your db is on /dev/sdb. Keep an eye on %Util. If
it's always at 100% for hours on end, then your IO subsystem is likely
maxed out.


--=20
To understand recursion, one must first understand recursion.

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Unexpected disk space growth controlling measures

> Definitely look at updating to the latest 8.2 release, there's a lot of
bug fixes since 8.2.3.
Yes, we're planning out to upgrade the version.

> Usually I set max_fsm_pages to 2x or more whatever vacuum verbose says it
needs.
A snippet of my VACUUM ANALYZE VERBOSE is pasted below. To set this
parameter rightly, which line of statement should be considered. Based on
my understanding, I should look out for something like "XXX pages contain
useful free space". Is my understanding correct?

In my case, it is showing "2013888 pages contain useful free space." . So,
can I set "max_fsm_pages" to 2013888 * 2 = 4027776

> Keep an eye on your system with tools like iostat.
> iostat -xd 10 /dev/sdb
> for instance if your db is on /dev/sdb. Keep an eye on %Util. If it's
always at 100% for hours on end, then your IO subsystem is likely maxed out.
Thank you again for this valuable suggestion. I'll start monitoring on our
IO subsystem also.

VACUUM ANALYZE VERBOSE Snippet
=======================
INFO: analyzing "public.statisticscount"
INFO: "statisticscount": scanned 0 of 0 pages, containing 0 live rows and 0
dead rows; 0 rows in sample, 0 estimated total rows
INFO: vacuuming "public.tab_timesheet_daily"
INFO: scanned index "ttd_did_idx" to remove 6084037 row versions
DETAIL: CPU 5.58s/3.00u sec elapsed 62.23 sec.
INFO: scanned index "ttd_eid_idx" to remove 6084037 row versions
DETAIL: CPU 7.53s/3.65u sec elapsed 99.49 sec.
INFO: scanned index "ttd_pid_idx" to remove 6084037 row versions
DETAIL: CPU 5.47s/2.93u sec elapsed 63.43 sec.
INFO: scanned index "ttd_uid_idx" to remove 6084037 row versions
DETAIL: CPU 8.39s/3.78u sec elapsed 202.86 sec.
INFO: "tab_timesheet_daily": removed 6084037 row versions in 79203 pages
DETAIL: CPU 1.16s/0.27u sec elapsed 21.06 sec.
INFO: index "ttd_did_idx" now contains 0 row versions in 520651 pages
DETAIL: 6079686 index row versions were removed.
520545 index pages have been deleted, 499489 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "ttd_eid_idx" now contains 0 row versions in 668819 pages
DETAIL: 5671343 index row versions were removed.
668593 index pages have been deleted, 643127 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "ttd_pid_idx" now contains 0 row versions in 520562 pages
DETAIL: 6072492 index row versions were removed.
520456 index pages have been deleted, 499428 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "ttd_uid_idx" now contains 0 row versions in 740272 pages
DETAIL: 6083626 index row versions were removed.
739948 index pages have been deleted, 708486 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "tab_timesheet_daily": found 6084037 removable, 0 nonremovable row
versions in 2013888 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 145598370 unused item pointers.
2013888 pages contain useful free space.
0 pages are entirely empty.
CPU 49.17s/15.51u sec elapsed 648.01 sec.
WARNING: relation "public.tab_timesheet_daily" contains more than
"max_fsm_pages" pages with useful free space
HINT: Consider compacting this relation or increasing the configuration
parameter "max_fsm_pages".



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin