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
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 |