From: Thomas Finneid <tfinneid@student.matnat.uio.no>
To: Greg Smith <gsmith@gregsmith.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: slow update of index during insert/copy
Date: Thu, 04 Sep 2008 10:49:28 +0200
Message-ID: <48BFA118.9010701@ifi.uio.no> (raw)
In-Reply-To: <Pine.GSO.4.64.0809020340370.17733@westnet.com>
References: <48BA9D65.2020001@ifi.uio.no>
<48BB4A2D.9080404@postnewspapers.com.au>
<48BBD22E.4090308@ifi.uio.no>
<Pine.GSO.4.64.0809011558080.10428@westnet.com>
<48BCDF9C.5020906@ifi.uio.no>
<Pine.GSO.4.64.0809020340370.17733@westnet.com>
What about filesystem properties?
on linux I am using:
ext3(with journal) and auto,rw,async,noatime,nodiratime
on disks for data and journal
I am unsure if I need a journal in the fs or if the db covers that
problem. With regards to that, do I then need to set some linux setting
to force inode syncing (dont rememver the name for the filesystem
structure in unix memory). The same question can be asked about the
async option.
any thoughts?
thomas
Greg Smith wrote:
> On Tue, 2 Sep 2008, Thomas Finneid wrote:
>
>>> 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
>>
>> The production machine is Solaris 10 running on a Sun v980. Do you
>> know of it has any issues like these?
>
> On Solaris you can safely use open_datasync which is a bit better than
> open_sync. For best results, you need to separate the xlog onto a
> separate partition and mount it using forcedirectio, because Postgres
> doesn't know how to use direct I/O directly on Solaris yet.
>
>> Additionally, would I need to do any config changes when going from
>> linux to solaris?
>
> Assuming the same amount of memory, the postgresql.conf should be
> basically the same, except for the wal_sync_method change mentioned
> above. If there's more RAM in the production server you can ramp up
> shared_buffers, effective_cache_size, and possibly work_mem
> proportionately. The settings I suggested for maintenance_work_mem and
> wal_buffers are already near the useful upper limits for those parameters.
>
> There are a few operating system level things you should consider
> tweaking on Solaris 10 for better PostgreSQL performance. You need to
> be a bit more careful about the parameters used for the filesystem than
> on Linux, and the settings there vary considerably depending on whether
> you're using UFS or ZFS. The best intro to that I know of is at
> http://blogs.sun.com/jkshah/entry/postgresql_east_2008_talk_best ; I
> added some clarification to a few points in there and some other Solaris
> notes at http://notemagnet.blogspot.com/2008_04_01_archive.html Those
> should get you started.
>
> I hope you're already looking into some sort of repeatable benchmarking
> that's representative of your application you can run. You'll go crazy
> playing with all these settings without something like that.
>
> --
> * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tfinneid@student.matnat.uio.no, gsmith@gregsmith.com
Subject: Re: slow update of index during insert/copy
In-Reply-To: <48BFA118.9010701@ifi.uio.no>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox