Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group