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
