From: Greg Smith <greg@2ndQuadrant.com>
To: pgsql-performance@postgresql.org
Subject: Re: SSD options, small database, ZFS
Date: Fri, 18 Nov 2011 05:09:20 -0500
Message-ID: <4EC62ED0.7040408@2ndQuadrant.com> (raw)
In-Reply-To: <F60C018B-9DCD-461A-A098-D9657A536698@morefoo.com>
References: <0464974E-CF82-41D7-A266-5BAE3CBCEC51@morefoo.com>
<4E9811DE.6030601@tweakers.net>
<F60C018B-9DCD-461A-A098-D9657A536698@morefoo.com>
On 11/17/2011 10:44 PM, CSS wrote:
> Is there any sort of simple documentation on the query planner that
> might cover how things like increased RAM could impact how a query is
> executed?
There is no *simple* documentation on any part of the query planner
that's also accurate. Query planning is inherently complicated.
I think this point wasn't quite made clearly. PostgreSQL has no idea
how much memory is in your system; it doesn't try to guess or detect
it. However, when people move from one system to a larger one, they
tend to increase some of the query planning parameters in the
postgresql.conf to reflect the new capacity. That type of change can
cause various types of query plan changes. Let's say your old system
has 16GB of RAM and you set effective_cache_size to 12GB; if you upgrade
to a 64GB server, it seems logical to increase that value to 48GB to
keep the same proportions. But that will can you different plans, and
it's possible they will be worse. There's a similar concern if you
change work_mem because you have more memory, because that will alter
how plans do things like sorting and hashing
But you don't have to make any changes. You can migrate to the new
hardware with zero modifications to the Postgres configuration, then
introduce changes later.
The whole memorys speed topic is also much more complicated than any
simple explanation can cover. How many banks of RAM you can use
effectively changes based on the number of CPUs and associated chipset
too. Someone just sent me an explanation recently of why I was seeing
some strange things on my stream-scaling benchmark program. That dove
into a bunch of trivia around how the RAM is actually accessed on the
motherboard. One of the reasons I keep so many samples on that
program's page is to help people navigate this whole maze, and have some
data points to set expectations against. See
https://github.com/gregs1104/stream-scaling for the code and the samples.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
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: greg@2ndQuadrant.com
Subject: Re: SSD options, small database, ZFS
In-Reply-To: <4EC62ED0.7040408@2ndQuadrant.com>
* 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