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: F406B740738CB44A8BCBEF9734ECF663683EC9@albus.intand.com (view raw or flat)
Thread:
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)

4GB RAM

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

 

 

pg_stat_bgwriter:

 

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.

 

Thanks,

 

Scott Otis

CIO / Lead Developer

Intand

www.intand.com

 

Responses

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-2014 The PostgreSQL Global Development Group