From: Shaun Thomas <sthomas@peak6.com>
To: John Lister <john.lister@kickstone.co.uk>
Cc: pgsql-performance@postgresql.org
Subject: Re: Configuration Recommendations
Date: Wed, 25 Apr 2012 16:29:16 -0500
Message-ID: <4F986CAC.4060904@peak6.com> (raw)
In-Reply-To: <4F97ABBB.1000708@kickstone.co.uk>
References: <CANxH4hGW+RYXsB8BdAY9hoGzje26EapX1H6H-jrNZAMQKZ+hMQ@mail.gmail.com>
<4F96FFE1.2030809@peak6.com>
<4F97ABBB.1000708@kickstone.co.uk>
On 04/25/2012 02:46 AM, John Lister wrote:
> Hi, I'd be grateful if you could share any XFS performance tweaks as I'm
> not entirely sure I'm getting the most out of my setup and any
> additional guidance would be very helpful.
Ok, I'll give this with a huge caveat: these settings came from lots of
testing, both load and pgbench based. I'll explain as much as I can.
For initializing the XFS filesystem, you can take advantage of a few
settings that are pretty handy.
* -d agcount=256 - Higher amount of allocation groups works better with
multi-CPU systems. We used 256, but you'll want to do tests to confirm
this. The point is that you can have several threads writing to the
filesystem simultaneously.
* -l lazy-count=1 - Log data is written more efficiently. Gives a
measurable performance boost. Newer versions set this, but CentOS 5 has
the default to 0. I'm not sure about CentOS 6. Just enable it. :)
* -l version=2 - Forces the most recent version of the logging
algorithm; allows a larger log buffer on mount. Since you're using
CentOS, the default value is still probably 1, which you don't want.
And then there are the mount options. These actually seemed to make more
of an impact in our testing:
* allocsize=256m - Database files are up to 1GB in size. To prevent
fragmentation, always pre-allocate in 256MB chunks. In recent 3.0+
kernels, this setting will result in phantom storage allocation as each
file is initially allocated with 256MB until all references have exited
memory. Due to aggressive Linux inode cache behavior, this may not
happen for several hours. On 3.0 kernels, this setting should be
removed. I think the 2.6.35 kernel had this backported, so *TEST THIS
SETTING BEFORE USING IT!*
* logbufs=8 - Forces more of the log buffer to remain in RAM, improving
file deletion performance. Good for temporary files. XFS often gets
knocked for file deletion performance, and this brings it way up. Not
really an issue with PG usage, but handy anyway. See logbsize.
* logbsize=256k - Larger log buffers keep track of modified files in
memory for better performance. See logbufs.
* noatime - Negates touching the disk for file accesses. Reduces disk IO.
* attr2 - Opportunistic improvement in the way inline extended
attributes are stored on-disk. Not strictly necessary, but handy.
I'm hoping someone else will pipe in, because these settings are pretty
"old" and based on a CentOS 5.5 setup. I haven't done any metrics on the
newer kernels, but I have followed enough to know allocsize is dangerous
on new systems.
Your mileage may vary. :)
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas@peak6.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
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: sthomas@peak6.com, john.lister@kickstone.co.uk
Subject: Re: Configuration Recommendations
In-Reply-To: <4F986CAC.4060904@peak6.com>
* 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