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

Re: slow update of index during insert/copy

From: Greg Smith <gsmith(at)gregsmith(dot)com>
To: Thomas Finneid <tfinneid(at)student(dot)matnat(dot)uio(dot)no>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: slow update of index during insert/copy
Date: 2008-09-02 00:46:22
Message-ID: Pine.GSO.4.64.0809011558080.10428@westnet.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, 1 Sep 2008, Thomas Finneid wrote:

> It does have a sata raid controller, but not have the battery pack, because 
> its a develmachine and not a production machine, I thought it was not needed. 
> But if you are saying the battery pack enables a cache which enables faster 
> disk writes I will consider it.

Some controllers will only let you enable a write-back cache if the 
battery if installed, but those are fairly rare.  On a development system, 
you usually can turn on write caching even if the battery that makes that 
safe for production isn't there.

> The controller I have is a Areca ARC-1220 Serial ATA 8 port RAID 
> Controller - PCI-E, SATA II, so I dont know exactly what it supports of 
> caching.

On that card I'm not sure you can even turn off the controller write 
caching if you wanted to.  There's one thing that looks like that though 
but isn't:  go into the BIOS, look at System Configuration, and there will 
be an option for "Disk Write Cache Mode".  That actually controls whether 
the caches on the individual disks are enabled or not, and the default of 
"Auto" sets that based on whethere there is a battery installed or not. 
See http://www.gridpp.rl.ac.uk/blog/2008/02/12/areca-cards/ for a good 
description of that.  The setting is quite confusing when set to Auto; I'd 
recommend just setting it to "Disabled" and be done with it.

You can confirm what each drive is actually set to by drilling down into 
the Physical Drives section, you'll find "Cache Mode: Write Back" if the 
individual disk write caches are on, and "Write Through" if they're off.

I'd suggest you take a look at 
http://notemagnet.blogspot.com/2008/08/linux-disk-failures-areca-is-not-so.html 
to find out more about the utilities that come with the card you can 
access under Linux.  You may have trouble using them under Ubuntu, I know 
I did.  Better to know about that incompatibility before you've got a disk 
failure.

I note that nobody has talked about your postgresql.conf yet.  I assume 
you've turned autovacuum off because you're not ever deleting things from 
these tables.  You'll still need to run VACUUM ANALYZE periodically to 
keep good statistics for your tables, but I don't think that's relevant to 
your question today.

I'd recommend changing all the memory-based parameters to use computer 
units.  Here's what your configuration turned into when I did that:

effective_cache_size = 1000MB
shared_buffers = 1000MB
work_mem = 512MB
maintenance_work_mem = 2000MB
wal_buffers = 256kB

Those are all close enough that I doubt fiddling with them will change 
much for your immediate problem.  For a system with 8GB of RAM like yours, 
I would suggest replacing the above with the below set instead; see 
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server for more 
information.

effective_cache_size = 7000MB
shared_buffers = 2000MB
work_mem = 512MB
maintenance_work_mem = 512MB
wal_buffers = 1024kB
checkpoint_completion_target = 0.9

Note that such a large work_mem setting can run out of memory (which is 
very bad on Linux) if you have many clients doing sorts at once.

> wal_sync_method = fdatasync

You should try setting this to open_sync , that can be considerably faster 
for some write-heavy situations.  Make sure to test that throughly though, 
there are occasional reports of issues with that setting under Linux; 
seems to vary based on kernel version.  I haven't had a chance to test the 
Ubuntu Hardy heavily in this area yet myself.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

In response to

Responses

pgsql-performance by date

Next:From: Duan LigongDate: 2008-09-02 01:38:21
Subject: Re: too many clog files
Previous:From: Scott CareyDate: 2008-09-01 21:03:11
Subject: Re: slow update of index during insert/copy

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