Re: WAL + SSD = slow inserts?

On Thu, Dec 5, 2013 at 8:16 AM, Skarsol <skarsol(at)gmail(dot)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-sched.txt?id=HEAD