Re: Configuring for maximum memory usage
| От | Scott Carey |
|---|---|
| Тема | Re: Configuring for maximum memory usage |
| Дата | |
| Msg-id | a1ec7d000810300946i396821f6k44803f175103322b@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Configuring for maximum memory usage ("Joshua D. Drake" <jd@commandprompt.com>) |
| Ответы |
Re: Configuring for maximum memory usage
("Joshua D. Drake" <jd@commandprompt.com>)
|
| Список | pgsql-performance |
On Thu, Oct 30, 2008 at 9:05 AM, Joshua D. Drake <jd@commandprompt.com> wrote:
By my understanding, this is absolutely false. Postgres caches pages from tables/indexes in shared_buffers. You can make this very large if you wish.
Solaris ZFS ARC is the filesystem cache area for ZFS, it will yield to other apps by default, but you will
get better performance and consistency if you limit it to not compete with apps you know you want in memory, like a database.
In older versions, the postgres shared_buffers page cache was not very efficient, and the OS page caches were jsut better, so setting shared_buffers too large was a bad idea.
However, postgres uses a reasonable eviction algorithm now that doesn't evict recently used items as readily as it used to, or let full table scans kick out heavily accessed data (8.3 +).
The current tradeoff is that going from postgres to the OS cache incurs CPU overhead for reading. But The OS may be better at caching more relevant pages.
A good OS cache, like the ZFS ARC, is much more sophisticated in the algorithms used for determining what to cache and what to evict, and so it may be better at limiting disk usage. But accessing it versus the postgres page cache in shared_buffers incurs extra CPU cost, as both caches must look for, load, and potentially evict, rather than one.
It should be set to the expected size of the OS file cache (the size of the ZFS ARC cache in this case).
However, it will have very little impact on large data queries that don't use indexes. It has larger impact
for things that may do index scans when shared_buffers is small comapred to file system cache + shared_buffers.
On Thu, 2008-10-30 at 16:58 +0100, Christiaan Willemsen wrote:Well I actually can't answer this definitely. My knowledge of Solaris is
> Joshua D. Drake wrote:
> >
> > PostgreSQL is only going to use what it needs. It relies on the OS for
> > much of the caching etc...
> >
> So that would actually mean that I could raise the setting of the ARC
> cache to far more than 8 GB? As I said, our database is 250 GB, So I
> would expect that postgres needs more than it is using right now...
> Several tables have over 500 million records (obviously partitioned).
> At the moment we are doing queries over large datasets, So I would
> assume that postgress would need a bit more memory than this..
slimmer than my knowledge of other operating systems. However it appears
from a brief google that ARC cache is some predefined file level cache
that you can set with Solaris? If so, then you want that to be high
enough to keep your most active relations hot in cache.
Remember that PostgreSQL doesn't cache anything on its own so if you do
want to hit disk it has to be in file cache.
By my understanding, this is absolutely false. Postgres caches pages from tables/indexes in shared_buffers. You can make this very large if you wish.
Solaris ZFS ARC is the filesystem cache area for ZFS, it will yield to other apps by default, but you will
get better performance and consistency if you limit it to not compete with apps you know you want in memory, like a database.
In older versions, the postgres shared_buffers page cache was not very efficient, and the OS page caches were jsut better, so setting shared_buffers too large was a bad idea.
However, postgres uses a reasonable eviction algorithm now that doesn't evict recently used items as readily as it used to, or let full table scans kick out heavily accessed data (8.3 +).
The current tradeoff is that going from postgres to the OS cache incurs CPU overhead for reading. But The OS may be better at caching more relevant pages.
A good OS cache, like the ZFS ARC, is much more sophisticated in the algorithms used for determining what to cache and what to evict, and so it may be better at limiting disk usage. But accessing it versus the postgres page cache in shared_buffers incurs extra CPU cost, as both caches must look for, load, and potentially evict, rather than one.
That is correct it is not an actual allocation but it does vastly effect
> >
> > You are missing effective_cache_size. Try setting that to 32G.
> That one was set to 24 GB. But this setting only tells posgres how much
> caching it can expect from the OS? This is not actually memory that it
> will allocate, is it?
your query plans. PostgreSQL uses this parameter to help determine if a
someone is likely to be cached (see comment about file cache above).
It should be set to the expected size of the OS file cache (the size of the ZFS ARC cache in this case).
However, it will have very little impact on large data queries that don't use indexes. It has larger impact
for things that may do index scans when shared_buffers is small comapred to file system cache + shared_buffers.
10 is likely way too low. Try 150 and make sure you analyze after.
> >
> > You also didn't mention checkpoint_segments (which isn't memory but
> > still important) and default_statistics_target (which isn't memory but
> > still important).
> >
> is at the moment set to:
>
> checkpoint_segments = 40
> default_statistics_target is set to default (I think that is 10)
>
As I recall some other databases allow you to say, "you have this much
memory, use it". PostgreSQL doesn't do that. You just give it pointers
and it will use as much as it needs within the limits of the OS. The key
word here is needs.
There is obviously some variance to that (like work_mem).
Joshua D. Drake
--
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
В списке pgsql-performance по дате отправления: