From: Gnanakumar <gnanam@zoniac.com>
To: 'Scott Marlowe' <scott.marlowe@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Unexpected disk space growth controlling measures
Date: Sat, 31 Jul 2010 13:16:29 +0530
Message-ID: <005501cb3084$7ebccd70$7c366850$@com> (raw)
In-Reply-To: <AANLkTimM7NKjOGX8D3jKz4hOC14v3Rs678pWFdrcnv9E@mail.gmail.com>
References: <002301cb306f$c8967dc0$59c37940$@com>
<AANLkTin6oBtd9gS79mRmNiW7iVa7Hfv63UYvXWvYTUWr@mail.gmail.com>
<005101cb307e$1b12f1b0$5138d510$@com>
<AANLkTimM7NKjOGX8D3jKz4hOC14v3Rs678pWFdrcnv9E@mail.gmail.com>
> 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".
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: gnanam@zoniac.com, scott.marlowe@gmail.com
Subject: Re: Unexpected disk space growth controlling measures
In-Reply-To: <005501cb3084$7ebccd70$7c366850$@com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox