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

Seeking performance advice and explanation for high I/O on 8.3

From: "Scott Otis" <scott(dot)otis(at)intand(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Seeking performance advice and explanation for high I/O on 8.3
Date: 2009-09-02 20:44:42
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
Would love to get some advice on how to change my conf settings / setup
to get better I/O performance.


Server Specs:


2x Intel Xeon Quad Core (@2 Ghz - Clovertown,L5335)


4x Seagate 73GB SAS HDD 10k RPM - in RAID ( stripped and mirrored )


FreeBSD 6.4

Apache 2.2

PostgreSQL 8.3.6

PHP 5.2.9


~1500 databases w/ ~60 tables each


Total I/O (these number are pretty constant throughout the day):

Reads: ~ 100 / sec for about 2.6 Mb/sec

Writes: ~ 400 /sec for about 46.1Mb/sec


Conf settings:


listen_addresses = '*'

max_connections = 600

ssl = on

password_encryption = on

shared_buffers = 1GB

work_mem = 5MB

maintenance_work_mem = 256MB

max_fsm_pages = 2800000

max_fsm_relations = 160000

synchronous_commit = off

checkpoint_segments = 6

checkpoint_warning = 30s

effective_cache_size = 1GB





checkpoints_timed: 16660

checkpoints_req: 1309

buffers_checkpoint: 656346

buffers_clean: 120922

maxwritten_clean: 1

buffers_backend: 167623

buffers_alloc: 472802349


This server also handles web traffic and PHP script processing.


Most of the SQL happening is selects - very little inserts, updates and
deletes comparatively.


I have noticed that most/all of the I/O activity is coming from the
stats collector and autovacuum processes.  Would turning off the stats
collector and autovacuum be helpeful / recommended?  Could I change my
checkpoint_* or bgwriter_* conf values to help?


Let me know if you need more information / stats.


Any help would be much appreciated.




Scott Otis

CIO / Lead Developer




pgsql-performance by date

Next:From: Magnus HaganderDate: 2009-09-03 07:51:49
Subject: Re: Help: how to speed up query after db server reboot
Previous:From: Greg JamanDate: 2009-09-02 20:31:29
Subject: Re: partition query using Seq Scan even when index is present

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