Problems with autovacuum

From: Łukasz Jagiełło <lukasz(dot)jagiello(at)gforces(dot)pl>
To: pgsql-performance(at)postgresql(dot)org
Subject: Problems with autovacuum
Date: 2009-05-24 19:46:38
Message-ID: de899fb00905241246x5e40610ex76ebbe6841f66c9e@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,

Recent change postgresql server from Amazon EC2 small into large one.
That gives me x86_64 arch, two core cpu and 7.5GB ram. Atm got almost
~2000 small databases at that server and autovacuum working hole time
(witch isn't good for performance as I notice at cacti, one core is
busy in 60% hole time). How can I tweak postgresql.conf to get better
performance ? Maybe number of database is huge but most of them are
unused most of time and others (~400-500) do mainly selects only with
small number of inserts or deletes from time to time.

My configuration is Fedora Core 10:
postgresql-libs-8.3.7-1.fc10.x86_64
postgresql-8.3.7-1.fc10.x86_64
postgresql-server-8.3.7-1.fc10.x86_64
postgresql-devel-8.3.7-1.fc10.x86_64

postgresql.conf:
#v+
max_connections = 500
shared_buffers = 200MB
work_mem = 4096
maintenance_work_mem = 256MB
max_fsm_pages = 204800
max_fsm_relations = 4000
vacuum_cost_delay = 0
vacuum_cost_page_hit = 1
vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20
vacuum_cost_limit = 200
effective_cache_size = 2048MB
logging_collector = on
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
track_activities = off
track_counts = on
log_parser_stats = off
log_planner_stats = off
log_executor_stats = off
log_statement_stats = off
autovacuum = on
log_autovacuum_min_duration = -1
autovacuum_max_workers = 3
autovacuum_naptime = 10min
autovacuum_vacuum_threshold = 10000
autovacuum_analyze_threshold = 10000
autovacuum_vacuum_scale_factor = 0.5
autovacuum_analyze_scale_factor = 0.4
autovacuum_freeze_max_age = 200000000
autovacuum_vacuum_cost_delay = 20
autovacuum_vacuum_cost_limit = -1
#v-

Regards
--
Łukasz Jagiełło
System Administrator
G-Forces Web Management Polska sp. z o.o. (www.gforces.pl)

Ul. Kruczkowskiego 12, 80-288 Gdańsk
Spółka wpisana do KRS pod nr 246596 decyzją Sądu Rejonowego Gdańsk-Północ

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Shaul Dar 2009-05-25 13:51:59 Putting tables or indexes in SSD or RAM: avoiding double caching?
Previous Message Tom Lane 2009-05-24 18:42:02 Re: Bad Plan for Questionnaire-Type Query