Reply-To: <gnanam@zoniac.com>
From: "Gnanakumar" <gnanam@zoniac.com>
To: "'Scott Marlowe'" <scott.marlowe@gmail.com>
Cc: <pgsql-admin@postgresql.org>
References: <002301cb306f$c8967dc0$59c37940$@com>
	<AANLkTin6oBtd9gS79mRmNiW7iVa7Hfv63UYvXWvYTUWr@mail.gmail.com>
	<005101cb307e$1b12f1b0$5138d510$@com>
	<AANLkTimM7NKjOGX8D3jKz4hOC14v3Rs678pWFdrcnv9E@mail.gmail.com>
In-Reply-To: <AANLkTimM7NKjOGX8D3jKz4hOC14v3Rs678pWFdrcnv9E@mail.gmail.com>
Subject: Re: Unexpected disk space growth controlling measures
Date: Sat, 31 Jul 2010 13:16:29 +0530
Organization: Zoniac Software Private Limited
Message-ID: <005501cb3084$7ebccd70$7c366850$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AcswgNSK0kpmMBijRQaL0SnpJWosIAAAgzIw
Content-Language: en-us

> 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".