From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: Skarsol <skarsol@gmail.com>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: desmodemone <desmodemone@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: WAL + SSD = slow inserts?
Date: Fri, 06 Dec 2013 11:01:00 +1300
Message-ID: <52A0F79C.3060200@catalyst.net.nz> (raw)
In-Reply-To: <CAMt8e=GbJw3yWK9ykusv3OM8wPS0k4ciOCOi28Y9NABmP0a9KA@mail.gmail.com>
References: <CAMt8e=Ee9BRLvNk3qq3vw-KKu-myMOpbx+9y4ABQee1dLcTq7Q@mail.gmail.com>
<CAEs9oFk4bFCA7cS=Q-EkN6joMbqwrGGpL8v8dU8d9S1rpA0HGA@mail.gmail.com>
<CAMt8e=FxA=0x8G-BTuV00BNGSz_-zkykrWG465mg0LBmO+sRxA@mail.gmail.com>
<CAOR=d=2zC19+SJLJ2SBNOb67imXGC8qDZEy_NzVOygeMUVOXUQ@mail.gmail.com>
<CAMt8e=GbJw3yWK9ykusv3OM8wPS0k4ciOCOi28Y9NABmP0a9KA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 06/12/13 05:13, Skarsol wrote:
> On Thu, Dec 5, 2013 at 9:50 AM, Scott Marlowe <scott.marlowe@gmail.com>wrote:
>
>> On Thu, Dec 5, 2013 at 8:16 AM, Skarsol <skarsol@gmail.com> wrote:
>>> psql (PostgreSQL) 9.2.5
>>> Red Hat Enterprise Linux Server release 6.4 (Santiago)
>>> Linux 2.6.32-358.6.1.el6.x86_64 #1 SMP Fri Mar 29 16:51:51 EDT 2013
>> x86_64
>>> x86_64 x86_64 GNU/Linux
>>> All relevant filesystems are ext4
>>>
>>> Changes from defaults:
>>> max_connections = 500
>>> shared_buffers = 32000MB
>>> temp_buffers = 24MB
>>> work_mem = 1GB
>>> maintenance_work_mem = 5GB
>>> wal_level = archive
>>> wal_buffers = 16MB
>>> checkpoint_completion_target = 0.9
>>> archive_mode = on
>>> archive_command = 'test ! -f /databases/pg_archive/db/%f && cp %p
>>> /databases/pg_archive/db/%f'
>>> effective_cache_size = 64000MB
>>> default_statistics_target = 5000
>>> log_checkpoints = on
>>> stats_temp_directory = '/tmp/pgstat'
>> OK I'd make the following changes.
>> 1: Drop shared_buffers to something like 1000MB
>> 2: drop work_mem to 16MB or so. 1GB is pathological, as it can make
>> the machine run out of memory quite fast.
>> 3: drop max_connections to 100 or so. if you really need 500 conns,
>> then work_mem of 1G is that much worse.
>>
>> Next, move pg_xlog OFF the SSDs and back onto spinning media and put
>> your data/base dir on the SSDs.
>>
>> SSDs aren't much faster, if at all, for pg_xlog, but are much much
>> faster for data/base files.
>>
>> Also changing the io schduler for the SSDs to noop:
>>
>>
>> http://git.kernel.org/cgit/linux/kernel/git/torvalds/linux.git/tree/Documentation/block/switching-sc...
>>
> Changing the scheduler to noop seems to have had a decent effect. I've made
> the other recommended changes other than the connections as we do need that
> many currently. We're looking to implement pg_bouncer which should help
> with that.
>
What model SSD are you using? Some can work better with deadline than
noop (as their own scheduling firmware may be pretty poor). Also, check
if there are updates for the SSD firmware. I have a couple of Crucial
M4s that changed from being fairly average to very fast indeed after
getting later firmware...
Cheers
Mark
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: mark.kirkwood@catalyst.net.nz, skarsol@gmail.com, scott.marlowe@gmail.com, desmodemone@gmail.com
Subject: Re: WAL + SSD = slow inserts?
In-Reply-To: <52A0F79C.3060200@catalyst.net.nz>
* 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