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@xxxxxxxxxxxxxxxxx>
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, 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
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
|