Обсуждение: 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
On Fri, Jul 30, 2010 at 11:18 PM, Gnanakumar <gnanam@zoniac.com> 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.
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?
On Sat, Jul 31, 2010 at 1:00 AM, Gnanakumar <gnanam@zoniac.com> wrote: > Hi Scott, > > Thanks for your valuable suggestion. Our 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. > >> 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? 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. Is 2.5M entries in a single > table or is it something else? max_fsm_pages is set to 10M >> 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? 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. -- To understand recursion, one must first understand recursion.
> 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".