On Thu, Dec 5, 2013 at 9:13 AM, Skarsol <skarsol@xxxxxxxxx> wrote: > On Thu, Dec 5, 2013 at 9:50 AM, Scott Marlowe <scott.marlowe@xxxxxxxxx> > wrote: >> >> On Thu, Dec 5, 2013 at 8:16 AM, Skarsol <skarsol@xxxxxxxxx> 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 > > > 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. > > Moving the whole database to SSD isn't an option currently due to size. > > The slowest inserts are happening on tables that are partitioned by creation > time. As part of the process there is a rule to select curval from a > sequence but there are no other selects or anything in the trigger > procedure. Could the sequence be slowing it down? I dont see a way to change > the tablespace of one. Rules have a lot of overhead. Is there a reason you're not using defaults or triggers? -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
