Message-ID: <54B02B55.2050803@pinpointresearch.com>
Date: Fri, 09 Jan 2015 11:26:13 -0800
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.3.0
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: New server optimization advice
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

I will soon be migrating to some recently acquired hardware and seek 
input from those who have gone before.

A quick overview: the dataset size is ~100GB, (~250-million tuples) with 
a workload that consists of about 2/3 writes, mostly single record 
inserts into various indexed tables, and 1/3 reads. Queries per second 
peak around 2,000 and our application typically demands fast response - 
for many of these queries the timeout is set to 2-seconds and the 
application moves forward and recovers later if that is exceeded.

Although by count they are minimal, every hour there are dozens both of 
import and of analysis queries involving multiple tables and tens of 
thousands of records. These queries may take up to a few minutes on our 
current hardware.

Old hardware is 4-core, 24GB RAM, battery-backed RAID-10 with four 15k 
drives.

New hardware is quite different. 2x10-core E5-2660v3 @2.6GHz, 128GB 
DDR4-2133 RAM and 800GB Intel DC P3700 NVMe PCIe SSD. In essence, the 
dataset will fit in RAM and will be backed by exceedingly fast storage.

This new machine is very different than any we've had before so any 
current thinking on optimization would be appreciated. Do I leave 
indexes as is and evaluate which ones to drop later? Any recommendations 
on distribution and/or kernels (and kernel tuning)? PostgreSQL tuning 
starting points? Whatever comes to mind.

Thanks,
Steve


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance