From: Tomas Vondra <tv@fuzzy.cz>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Greg Smith <greg@2ndquadrant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: SSD options, small database, ZFS
Date: Fri, 18 Nov 2011 17:30:40 +0100
Message-ID: <82b3dbde9ae3559f7e91e0fec379b309.squirrel@sq.gransy.com> (raw)
In-Reply-To: <CAOR=d=14dTQpPsn7+rCw8WU+Um9u7cNt1xG1KGhqECnejcEktw@mail.gmail.com>
References: <0464974E-CF82-41D7-A266-5BAE3CBCEC51@morefoo.com>
<4E9811DE.6030601@tweakers.net>
<F60C018B-9DCD-461A-A098-D9657A536698@morefoo.com>
<4EC62ED0.7040408@2ndQuadrant.com>
<CAOR=d=14dTQpPsn7+rCw8WU+Um9u7cNt1xG1KGhqECnejcEktw@mail.gmail.com>
On 18 Listopad 2011, 17:17, Scott Marlowe wrote:
> On Fri, Nov 18, 2011 at 3:09 AM, Greg Smith <greg@2ndquadrant.com> wrote:
>> 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.
>
> effective_cache_size tells the db how much memory you have. Since you
> have to set it, it can be anything you want, but if you've set it to
> something much higher on the new machine then it can affect query
> planning.
That's only half of the truth. effective_cache_size is used to estimate
the page cache hit ratio, nothing else. It influences the planning a bit
(AFAIK it's used only to estimate a nested loop with inner index scan) but
it has no impact on things like work_mem, maintenance_work_mem,
wal_buffers etc.
People often bump these settings up (especially work_mem) on new hw
without properly testing the impact. PostgreSQL will happily do that
because it was commanded to, but then the system starts swapping or the
OOM killer starts shooting the processes.
Tomas
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: tv@fuzzy.cz, scott.marlowe@gmail.com, greg@2ndquadrant.com
Subject: Re: SSD options, small database, ZFS
In-Reply-To: <82b3dbde9ae3559f7e91e0fec379b309.squirrel@sq.gransy.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