Re: slow update of index during insert/copy

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@gregsmith.com http://www.gregsmith.com Baltimore, MD