Account Options

  1. Sign in
The old Google Groups will be going away soon, but your browser is incompatible with the new version.
Google Groups Home
« Groups Home
pgsql.performance
+ new post
About this group
Subscribe to this group
This is a Usenet group - learn more
Message from discussion Autovacuum Tuning advice
The group you are posting to is a Usenet group. Messages posted to this group will make your email address visible to anyone on the Internet.
Your reply message has not been sent.
Your post was successful
 
From:
To:
Cc:
Followup To:
Add Cc | Add Followup-to | Edit Subject
Subject:
Validation:
For verification purposes please type the characters you see in the picture below or the numbers you hear by clicking the accessibility icon. Listen and type the numbers you hear
 
Scott Marlowe  
View profile  
 More options Mar 1 2010, 1:58 am
Newsgroups: pgsql.performance
From: scott.marl...@gmail.com (Scott Marlowe)
Date: Sun, 28 Feb 2010 23:58:28 -0700
Local: Mon, Mar 1 2010 1:58 am
Subject: Re: [PERFORM] Autovacuum Tuning advice
Print | View thread | Show original | Report this message | Find messages by this author

On Sun, Feb 28, 2010 at 8:09 PM, Plugge, Joe R. <JRPlu...@west.com> wrote:
> I have a very busy system that takes about 9 million inserts per day and each record gets updated at least once after the insert (all for the one same table), there are other tables that get hit but not as severely.  As suspected I am having a problem with table bloat.  Any advice on how to be more aggressive with autovacuum?  I am using 8.4.1.  My machine has 4 Intel Xeon  3000 MHz Processors with 8 GB of Ram.

What kind of drive system do you have?  That's far more important than
CPU and RAM.

Let's look at a two pronged attack.  1: What can you maybe do to
reduce the number of updates for each row.  if you do something like:

update row set field1='xyz' where id=4;
update row set field2='www' where id=4;

And you can combine those updates, that's a big savings.

Can you benefit from HOT updates by removing some indexes?  Updating
indexed fields can cost a fair bit more than updating indexed ones IF
you have a < 100% fill factor and therefore free room in each page for
a few extra rows.

2: Vacuum tuning.

> Currently I am using only defaults for autovac.

This one:

> #autovacuum_vacuum_cost_delay = 20ms

is very high for a busy system with a powerful io subsystem.  I run my
production servers with 1ms to 4ms so they can keep up.

Lastly there are some settings you can make per table for autovac you
can look into (i.e. set cost_delay to 0 for this table), or you can
turn off autovac for this one table and then run a regular vac with no
cost_delay on it every minute or two.

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


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy
©2013 Google