pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAutovacuum Tuning advice
6+ messages / 4 participants
[nested] [flat]
* Autovacuum Tuning advice
@ 2010-03-01 03:09 Plugge, Joe R. <JRPlugge@west.com>
0 siblings, 2 replies; 6+ messages in thread
From: Plugge, Joe R. @ 2010-03-01 03:09 UTC (permalink / raw)
To: pgsql-performance
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.
Currently I am using only defaults for autovac.
shared_buffers = 768MB # min 128kB
work_mem = 1MB # min 64kB
maintenance_work_mem = 384MB
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
#autovacuum = on
#log_autovacuum_min_duration = -1
#autovacuum_max_workers = 3
#autovacuum_naptime = 1min
#autovacuum_vacuum_threshold = 50
#autovacuum_analyze_threshold = 50
#autovacuum_vacuum_scale_factor = 0.2
#autovacuum_analyze_scale_factor = 0.1
#autovacuum_freeze_max_age = 200000000
#autovacuum_vacuum_cost_delay = 20ms
#autovacuum_vacuum_cost_limit = -1
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Autovacuum Tuning advice
@ 2010-03-01 06:58 Scott Marlowe <scott.marlowe@gmail.com>
parent: Plugge, Joe R. <JRPlugge@west.com>
1 sibling, 1 reply; 6+ messages in thread
From: Scott Marlowe @ 2010-03-01 06:58 UTC (permalink / raw)
To: Plugge, Joe R. <JRPlugge@west.com>; +Cc: pgsql-performance
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 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Autovacuum Tuning advice
@ 2010-03-01 07:08 Greg Williamson <gwilliamson39@yahoo.com>
parent: Plugge, Joe R. <JRPlugge@west.com>
1 sibling, 0 replies; 6+ messages in thread
From: Greg Williamson @ 2010-03-01 07:08 UTC (permalink / raw)
To: pgsql-performance
Joe 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.
>
> Currently I am using only defaults for autovac.
>
> shared_buffers = 768MB # min 128kB
> work_mem = 1MB # min 64kB
> maintenance_work_mem = 384MB
<snip of default config settings>
Operating system ?
Any messages in logs ?
Greg W.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Autovacuum Tuning advice
@ 2010-03-01 12:39 Plugge, Joe R. <JRPlugge@west.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Plugge, Joe R. @ 2010-03-01 12:39 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: 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 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Autovacuum Tuning advice
@ 2010-03-01 12:50 Grzegorz Jaśkiewicz <gryzman@gmail.com>
parent: Plugge, Joe R. <JRPlugge@west.com>
0 siblings, 1 reply; 6+ messages in thread
From: Grzegorz Jaśkiewicz @ 2010-03-01 12:50 UTC (permalink / raw)
To: Plugge, Joe R. <JRPlugge@west.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-performance
storing all fields as varchar surely doesn't make:
- indicies small,
- the thing fly,
- tables small.
...
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Autovacuum Tuning advice
@ 2010-03-01 12:57 Plugge, Joe R. <JRPlugge@west.com>
parent: Grzegorz Jaśkiewicz <gryzman@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Plugge, Joe R. @ 2010-03-01 12:57 UTC (permalink / raw)
To: Grzegorz Jaśkiewicz <gryzman@gmail.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-performance
Sorry, this is a “black box” application, I am bound by what they give me as far as table layout, but I fully understand the rationale. I believe this application spent its beginnings with Oracle, which explains the blanket use of VARCHAR.
From: Grzegorz Jaśkiewicz [mailto:gryzman@gmail.com]
Sent: Monday, March 01, 2010 6:51 AM
To: Plugge, Joe R.
Cc: Scott Marlowe; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Autovacuum Tuning advice
storing all fields as varchar surely doesn't make:
- indicies small,
- the thing fly,
- tables small.
...
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-03-01 12:57 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-01 03:09 Autovacuum Tuning advice Plugge, Joe R. <JRPlugge@west.com>
2010-03-01 06:58 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-03-01 12:39 ` Plugge, Joe R. <JRPlugge@west.com>
2010-03-01 12:50 ` Grzegorz Jaśkiewicz <gryzman@gmail.com>
2010-03-01 12:57 ` Plugge, Joe R. <JRPlugge@west.com>
2010-03-01 07:08 ` Greg Williamson <gwilliamson39@yahoo.com>
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