From: Gavin Flower <GavinFlower@archidevsys.co.nz>
To: Nick Eubank <nickeubank@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Tuning Postgres for Single connection use
Date: Tue, 15 Apr 2014 12:29:01 +1200
Message-ID: <534C7D4D.9010501@archidevsys.co.nz> (raw)
In-Reply-To: <CAFWQgOk_xwwnFj2xehzDqbtsY5=Tk5gJhOQGrFs6VhajP7FXfQ@mail.gmail.com>
References: <CAFWQgOmjM=KWxBwhK+gZtc6WeFKCYCPjAOg8P=wVs6buzQMoPQ@mail.gmail.com>
<534C6435.3060500@archidevsys.co.nz>
<CAFWQgOk_xwwnFj2xehzDqbtsY5=Tk5gJhOQGrFs6VhajP7FXfQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
In this list, please bottom post!
I've added potentially useful advice below.
On 15/04/14 11:39, Nick Eubank wrote:
> Thanks Gavin -- would LOVE to. Sadly I'm in a weird situation
> where my hardware is not under my control, so I'm stuck making the
> best of what I have. Next time though! :)
>
> On Monday, April 14, 2014, Gavin Flower <GavinFlower@archidevsys.co.nz
> <mailto:GavinFlower@archidevsys.co.nz>> wrote:
>
> 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
>> <http://wiki.postgresql.org/wiki/Performance_Optimization;, this
>> <http://media.revsys.com/talks/djangocon/2011/secrets-of-postgresql-performance.pdf;,
>> this
>> <http://www.revsys.com/writings/postgresql-performance.html; 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
>
Yeah, I know the feeling!
I have a client that uses MySQL (ugh!), but I won't even bother
mentioning PostgreSQL!
Hopefully, someone more knowledgeable will give you some good advice
specific to your O/S.
For tables that don't change, consider a packing density of 100%.
Take care in how you design your tables, and the column types.
Consider carefully the queries you are likely to use, so you can design
appropriate indexes.
Some advice will depend on the schema you plan to use, and the type of
queries.
Cheers,
Gavin
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: GavinFlower@archidevsys.co.nz, nickeubank@gmail.com
Subject: Re: Tuning Postgres for Single connection use
In-Reply-To: <534C7D4D.9010501@archidevsys.co.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