Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 8668365024B for ; Thu, 30 Oct 2008 18:27:01 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 20150-06 for ; Thu, 30 Oct 2008 18:26:58 -0300 (ADT) X-Greylist: delayed 00:20:21.197909 by SQLgrey-1.7.6 Received: from node02.cambriumhosting.nl (node02.cambriumhosting.nl [217.19.16.163]) by postgresql.org (Postfix) with ESMTP id D043165024A for ; Thu, 30 Oct 2008 18:26:57 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by node02.cambriumhosting.nl (Postfix) with ESMTP id 1B735B0000E8; Thu, 30 Oct 2008 22:06:30 +0100 (CET) X-Virus-Scanned: by ClamAV (clamav.cambrium.nl) at node.cambriumhosting.nl Received: from node02.cambriumhosting.nl ([127.0.0.1]) by localhost (node02.cambriumhosting.nl [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id fVyrSiDRdFtW; Thu, 30 Oct 2008 22:06:26 +0100 (CET) Received: from [192.168.1.35] (84-245-8-42.dsl.cambrium.nl [84.245.8.42]) by node02.cambriumhosting.nl (Postfix) with ESMTP id 9D0E6B0000B9; Thu, 30 Oct 2008 22:06:25 +0100 (CET) Cc: jd@commandprompt.com, pgsql-performance@postgresql.org Message-Id: <088F80FE-9332-4000-8561-5C5537940C4D@technocon.com> From: Christiaan Willemsen To: Scott Carey In-Reply-To: Content-Type: multipart/alternative; boundary=Apple-Mail-2-132965988 Mime-Version: 1.0 (Apple Message framework v929.2) Subject: Re: Configuring for maximum memory usage Date: Thu, 30 Oct 2008 22:06:25 +0100 References: <4909CF9C.80701@technocon.com> <4909D688.1090806@commandprompt.com> <4909D989.5020905@technocon.com> <1225382752.32621.4.camel@jd-laptop.pragmaticzealot.org> <1225385730.32621.21.camel@jd-laptop.pragmaticzealot.org> X-Mailer: Apple Mail (2.929.2) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200810/131 X-Sequence-Number: 31712 --Apple-Mail-2-132965988 Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes Content-Transfer-Encoding: 7bit Thanks guys, Lots of info here that I didn't know about! Since I have one of the latest Opensolaris builds, I guess the write throttle feature is already in there. Sadly, the blog doesn't say what build has it included. For writes, I do everything synchronized because we really need a consistent database on disk. We can see that during large inserts, the intend log is used a lot. What I'm going to te testing is a smaller shared_buffers value, and a large ARC cache, and exactly the other way around. Another question: since we have huge tables with hundreds of millions or rows, we partitioned the database (it actually is creating the partitions dynamically now on inserts with very good performance :D ), but the question is: is the size of the partions important for the memory parameters in config file? How can we determine the optimal size of the partition. obviously, when doing selects, you want those preferably only needing a single partition for speed. At the moment, that is for the majority of situations the case. But there might be some other things to think about... Kind regards, Christiaan On Oct 30, 2008, at 7:27 PM, Scott Carey wrote: > > > On Thu, Oct 30, 2008 at 9:55 AM, Joshua D. Drake > wrote: > On Thu, 2008-10-30 at 09:46 -0700, Scott Carey wrote: > > > > > 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. > > You can make it very large with a potentially serious performance hit. > It is very expensive to manage large amounts of shared buffers. It can > also nail your IO on checkpoint if you are not careful (even with > checkpoint smoothing). You are correct that I did not explain what I > meant very well because shared buffers are exactly that, shared > buffers. > > You can slam your I/O by havnig too large of either OS file cache or > shared_buffers, and you have to tune both. > In the case of large shared_buffers you have to tune postgres and > especially the background writer and checkpoints. > In the case of a large OS cache, you have to tune parameters to > limit the ammount of dirty pages there and force writes out smoothly. > Both layers attempt to delay writes for their own, often similar > reasons, and suffer when a large sync comes along with lots of dirty > data. > > Recent ZFS changes have been made to limit this, (http://blogs.sun.com/roch/entry/the_new_zfs_write_throttle > ) > in earlier ZFS versions, this is what usually killed databases -- > ZFS in some situations would delay writes too long (even if "long" > is 5 seconds) and get in trouble. This still has to be tuned well, > combined with good checkpoint tuning in Postgres as you mention. For > Linux, there are similar issues that have to be tuned on many > kernels, or up to 40% of RAM can fill with dirty pages not written > to disk. > > Letting the OS do it doesn't get rid of the problem, both levels of > cache share very similar issues with large sizes and dirty pages > followed by a sync. > > The buffer cache in shared_buffers is a lot more efficient for large > scanning queries -- A select count(*) test will be CPU bound if it > comes from shared_buffers or the OS page cache, and in the former > case I have seen it execute up to 50% faster than the latter, by > avoiding calling out to the OS to get pages, purely as a result of > less CPU used. > > > > > However that isn't the exact same thing as a "cache" at least as I was > trying to describe it. shared buffers are used to keep track of pages > (as well as some other stuff) and their current status. That is not > the > same as caching a relation. > > It is not possible to pin a relation to memory using PostgreSQL. > PostgreSQL relies on the operating system for that type of caching. > > The OS can't pin a relation either, from its point of view its all > just a bunch of disk data blocks, not relations -- so it is all > roughly equivalent. The OS can do a bit better job at data prefetch > on sequential scans or other predictable seek sequences (ARC stands > for Adaptive Replacement Cache) than postgres currently does (no > intelligent prefetch in postgres AFAIK). > > So I apologize if I made it sound like Postgres cached the actual > relation, its just pages -- but it is basically the same thing as > the OS cache, but kept in process closer to the code that needs it. > Its a cache that prevents disk reads. > > My suggestion for the OP is to try it both ways, and see what is > better for his workload / OS / Hardware combination. > > > Joshua D. Drake > > > > > > -- > > --Apple-Mail-2-132965988 Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Thanks = guys,

Lots of info here that I didn't know about! = Since I have one of the latest Opensolaris builds, I guess the write = throttle feature is already in there. Sadly, the blog doesn't say what = build has it included.

For writes, I do = everything synchronized because we really need a consistent database on = disk. We can see that during large inserts, the intend log is used a = lot. 

What  I'm going to te testing = is a smaller shared_buffers value, and a large ARC cache, and exactly = the other way around.

Another question: = since we have huge tables with hundreds of millions or rows, = we partitioned the database (it actually is creating the partitions = dynamically now on inserts with very good performance :D ), = but the question is: is the size of the partions important for the = memory parameters in config file? How can we determine the optimal size = of the partition. obviously, when doing selects, you want = those preferably only needing a single partition for speed. At the = moment, that is for the majority of situations the case. But there might = be some other things to think about...

Kind = regards,

Christiaan


On Oct 30, 2008, at = 7:27 PM, Scott Carey wrote:



On Thu, Oct 30, 2008 at 9:55 AM, Joshua D. Drake = <jd@commandprompt.com> = wrote:
On Thu, 2008-10-30 at 09:46 -0700, Scott = Carey wrote:

>
>         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.

You can make it very large with a = potentially serious performance hit.
It is very expensive to manage = large amounts of shared buffers. It can
also nail your IO on = checkpoint if you are not careful (even with
checkpoint smoothing). = You are correct that I did not explain what I
meant very well = because shared buffers are exactly that, shared
= buffers.

You can slam your I/O by havnig too large = of either OS file cache or shared_buffers, and you have to tune both. =
In the case of large shared_buffers you have to tune postgres and = especially the background writer and checkpoints.
In the case of a = large OS cache, you have to tune parameters to limit the ammount of = dirty pages there and force writes out smoothly. 
Both layers = attempt to delay writes for their own, often similar reasons, and suffer = when a large sync comes along with lots of dirty data. 
=
Recent ZFS changes have been made to limit this, (http:/= /blogs.sun.com/roch/entry/the_new_zfs_write_throttle)
in earlier = ZFS versions, this is what usually killed databases -- ZFS in some = situations would delay writes too long (even if "long" is 5 seconds) and = get in trouble.  This still has to be tuned well, combined with = good checkpoint tuning in Postgres as you mention. For Linux, there are = similar issues that have to be tuned on many kernels, or up to 40% of = RAM can fill with dirty pages not written to disk.

Letting the = OS do it doesn't get rid of the problem, both levels of cache share very = similar issues with large sizes and dirty pages followed by a sync. =

The buffer cache in shared_buffers is a lot more efficient for = large scanning queries -- A select count(*) test will be CPU bound if it = comes from shared_buffers or the OS page cache, and in the former case I = have seen it execute up to 50% faster than the latter, by avoiding = calling out to the OS to get pages, purely as a result of less CPU = used.
 
 


However that isn't the exact same = thing as a "cache" at least as I was
trying to describe it. shared = buffers are used to keep track of pages
(as well as some other = stuff) and their current status. That is not the
same as caching a = relation.

It is not possible to pin a relation to memory using = PostgreSQL.
PostgreSQL relies on the operating system for that type = of caching.

The OS can't pin a relation = either, from its point of view its all just a bunch of disk data blocks, = not relations -- so it is all roughly equivalent.  The OS can do a = bit better job at data prefetch on sequential scans or other predictable = seek sequences (ARC stands for Adaptive Replacement Cache) than postgres = currently does (no intelligent prefetch in postgres AFAIK).

So I = apologize if I made it sound like Postgres cached the actual relation, = its just pages -- but it is basically the same thing as the OS cache, = but kept in process closer to the code that needs it.  Its a cache = that prevents disk reads.

My suggestion for the OP is to try it = both ways, and see what is better for his workload / OS / Hardware = combination.
 

Joshua D. Drake





--

=


= --Apple-Mail-2-132965988--