Re: Autovacuum Tuning advice - Mailing list pgsql-performance

Sorry, additional info:

OS is Red Hat Enterprise Linux ES release 4 (Nahant Update 5)

DISK - IBM DS4700 Array - 31 drives and 1 hot spare - RAID10 - 32MB stripe

Sysctl.conf
kernel.shmmax=6442450944
kernel.shmall=1887436
kernel.msgmni=1024
kernel.msgmnb=65536
kernel.msgmax=65536
kernel.sem=250 256000 32 1024

Problem Child table: This table is partitioned so that after the data has rolled past 30 days, I can just drop the
table.


             Table "public.log_events_y2010m02"
    Column     |              Type              | Modifiers
---------------+--------------------------------+-----------
 callseq       | character varying(32)          | not null
 eventid       | character varying(40)          | not null
 msgseq        | character varying(32)          | not null
 eventdate     | timestamp(0) without time zone | not null
 hollyid       | character varying(20)          |
 ownerid       | character varying(60)          |
 spownerid     | character varying(60)          |
 applicationid | character varying(60)          |
 clid          | character varying(40)          |
 dnis          | character varying(40)          |
 param         | character varying(2000)        |
 docid         | character varying(40)          |
Indexes:
    "log_events_y2010m02_pk" PRIMARY KEY, btree (callseq, msgseq)
    "loev_eventid_idx_y2010m02" btree (eventid)
    "loev_ownerid_cidx_y2010m02" btree (ownerid, spownerid)
Check constraints:
    "log_events_y2010m02_eventdate_check" CHECK (eventdate >= '2010-02-01'::date AND eventdate < '2010-03-01'::date)
Inherits: log_events


Parent Table:

                 Table "public.log_events"
    Column     |              Type              | Modifiers
---------------+--------------------------------+-----------
 callseq       | character varying(32)          | not null
 eventid       | character varying(40)          | not null
 msgseq        | character varying(32)          | not null
 eventdate     | timestamp(0) without time zone | not null
 hollyid       | character varying(20)          |
 ownerid       | character varying(60)          |
 spownerid     | character varying(60)          |
 applicationid | character varying(60)          |
 clid          | character varying(40)          |
 dnis          | character varying(40)          |
 param         | character varying(2000)        |
 docid         | character varying(40)          |
Triggers:
    insert_log_events_trigger BEFORE INSERT ON log_events FOR EACH ROW EXECUTE PROCEDURE insert_log_events()


schemaname |           tablename            | size_pretty | total_size_pretty
------------+--------------------------------+-------------+-------------------
 public     | log_events_y2010m02            | 356 GB      | 610 GB



-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Monday, March 01, 2010 12:58 AM
To: Plugge, Joe R.
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Autovacuum Tuning advice

On Sun, Feb 28, 2010 at 8:09 PM, Plugge, Joe R. <JRPlugge@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
theinsert (all for the one same table), there are other tables that get hit but not as severely.  As suspected I am
havinga problem with table bloat.  Any advice on how to be more aggressive with autovacuum?  I am using 8.4.1.  My
machinehas 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.