Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WZpZu-00056a-Tj for pgsql-performance@arkaria.postgresql.org; Mon, 14 Apr 2014 22:42:15 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WZpZu-00044a-8o for pgsql-performance@arkaria.postgresql.org; Mon, 14 Apr 2014 22:42:14 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WZpZt-00044U-9w for pgsql-performance@postgresql.org; Mon, 14 Apr 2014 22:42:13 +0000 Received: from mbx.knossos.net.nz ([202.160.48.10]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WZpZn-0002uP-PW for pgsql-performance@postgresql.org; Mon, 14 Apr 2014 22:42:13 +0000 Received: from [10.1.1.3] (121-99-178-175.bng1.nct.orcon.net.nz [121.99.178.175]) (authenticated bits=0) by mbx.knossos.net.nz (8.14.4/8.14.4) with ESMTP id s3EMfwom002215 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES128-SHA bits=128 verify=NOT); Tue, 15 Apr 2014 10:41:58 +1200 Message-ID: <534C6435.3060500@archidevsys.co.nz> Date: Tue, 15 Apr 2014 10:41:57 +1200 From: Gavin Flower Organization: ArchiDevSys User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:24.0) Gecko/20100101 Thunderbird/24.4.0 MIME-Version: 1.0 To: Nick Eubank , pgsql-performance@postgresql.org Subject: Re: Tuning Postgres for Single connection use References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------070401020906010401030500" X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multi-part message in MIME format. --------------070401020906010401030500 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 15/04/14 09:46, Nick Eubank wrote: > > Any rules of thumb for |work_mem|, |maintenance_work_mem|, > |shared_buffer|, etc. for a database that DOESN'T anticipate > concurrent connections and that is doing lots of aggregate functions > on large tables? All the advice I can find online on tuning (this > , this > , > this > etc.) is > written for people anticipating lots of concurrent connections. > > I'm a social scientist looking to use Postgres not as a database to be > shared by multiple users, but rather as my own tool for manipulating a > massive data set (I have 5 billion transaction records (600gb in csv) > and want to pull out unique user pairs, estimate aggregates for > individual users, etc.). This also means almost no writing, except to > creation of new tables based on selections from the main table. > > I'm on a Windows 8 VM with 16gb ram, SCSI VMware HD, and 3 cores if > that's important. > > Thanks! > Well for serious database work, I suggest upgrading to Linux - you will get better performance out of the same hardware and probably (a year or so ago, I noticed some tuning options did not apply to Microsoft O/S's, but I don't recall the details - these options may, or may not, apply to your situation) more scope for tuning. Apart from anything else, your processing will not be slowed down by having to run anti-virus software! Note that in Linux you have a wide choice of distributions and desktop environments: I chose Mate (http://mate-desktop.org), some people prefer xfce (http://www.xfce.org), I used to use GNOME 2. Cheers, Gavin --------------070401020906010401030500 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 15/04/14 09:46, Nick Eubank wrote:

Any rules of thumb for work_mem, maintenance_work_mem, shared_buffer, etc. for a database that DOESN'T anticipate concurrent connections and that is doing lots of aggregate functions on large tables? All the advice I can find online on tuning (this, this, this etc.) is written for people anticipating lots of concurrent connections.

I'm a social scientist looking to use Postgres not as a database to be shared by multiple users, but rather as my own tool for manipulating a massive data set (I have 5 billion transaction records (600gb in csv) and want to pull out unique user pairs, estimate aggregates for individual users, etc.). This also means almost no writing, except to creation of new tables based on selections from the main table. 

I'm on a Windows 8 VM with 16gb ram, SCSI VMware HD, and 3 cores if that's important.

Thanks!

Well for serious database work, I suggest upgrading to Linux - you will get better performance out of the same hardware and probably (a year or so ago, I noticed some tuning options did not apply to Microsoft O/S's, but I don't recall the details - these options may, or may not, apply to your situation) more scope for tuning.  Apart from anything else, your processing will not be slowed down by having to run anti-virus software!

Note that in Linux you have a wide choice of distributions and desktop environments: I chose Mate (http://mate-desktop.org), some people prefer xfce (http://www.xfce.org), I used to use GNOME 2.


Cheers,
Gavin
--------------070401020906010401030500--