Thread: Effective Cache Size
Thanks. Some theoretical questions. The documentation says that Effective Cache Size "sets the optimizer's assumption about the effective size of the disk cache ( that is, the portion of the kernel's disk cache that will be used for PostgreSQL data files ). What then will be the effect of setting this too high? And too low? How does it impact on other applications eg Java ? > -----Original Message----- > From: scott.marlowe [SMTP:scott.marlowe@ihs.com] > Sent: 01 July 2003 03:20 > To: Howard Oblowitz > Cc: pgsql-performance@postgresql.org > Subject: Re: FW: [PERFORM] Version 7 question > > The best way to set it is to let the machine run under normal load for a > while, then look at the cache / buffer usage using top (or some other > program, top works fine for this). > > My server with 1.5 gig ram shows 862328K cache right now. So, just divide > > by page size (usually 8192) and I get ~ 100,000 blocks. > > On Tue, 1 Jul 2003, Howard Oblowitz wrote: > > > What would be the best value range for effective_cache_size > > on Postgres 7.3.2, assuming say 1.5 GB of RAM and > > shared_buffers set to 8192, and shmmax set to 750mb? > > > > And what are the most important factors one should take > > into account in determining the value? > > > > > > > > > -----Original Message----- > > > From: scott.marlowe [SMTP:scott.marlowe@ihs.com] > > > Sent: 01 July 2003 02:56 > > > To: Michael Mattox > > > Cc: Hilary Forbes; pgsql-performance@postgresql.org > > > Subject: Re: [PERFORM] Version 7 question > > > > > > 8192 is only 64 megs of RAM, not much, but a good number. Keep in > mind > > > that the kernel tends to be better at buffering huge amounts of disk, > > > while postgresql is better left to use buffers that are large enough > for > > > the current working set (i.e. not your whole database, just the > largest > > > amount of data you're slinging about on a regular basis in one query.) > > > > > > On a machine with 1.5 gig of RAM, I've found settings as high as 32768 > > > > (256 megs of ram) to run well, but anything over that doesn't help. > Of > > > course, we don't toss around more than a hundred meg or so at a time. > If > > > > > > our result sets were in the gigabyte range, I'd A: want more memory > and B: > > > > > > Give more of it to postgresql. > > > > > > The original poster was, I believe running 7.0.x, which is way old, so > no, > > > > > > I don't think there was an equivalent of effective_cache_size in that > > > version. Upgrading would be far easier than performance tuning 7.0. > since > > > > > > the query planner was much simpler (i.e. more prone to make bad > decisions) > > > > > > in 7.0. > > > > > > On Tue, 1 Jul 2003, Michael Mattox wrote: > > > > > > > I have my shared buffers at 8192 and my effective cache at 64000 > (which > > > is > > > > 500 megs). Depends a lot on how much RAM you have. I have 1.5 gigs > and > > > > I've been asking my boss for another 512megs for over a month now. > I > > > have > > > > no idea if my buffers are too high/low. > > > > > > > > Michael > > > > > > > > > -----Original Message----- > > > > > From: pgsql-performance-owner@postgresql.org > > > > > [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Hilary > > > > > Forbes > > > > > Sent: Tuesday, July 01, 2003 2:10 PM > > > > > To: pgsql-performance@postgresql.org > > > > > Subject: [PERFORM] Version 7 question > > > > > > > > > > > > > > > I'm just trying to improve performance on version 7 before doing > > > > > some tests and hopefully upgrading to 7.3. > > > > > > > > > > At the moment we have > > > > > B=64 (no of shared buffers) > > > > > N=32 (no of connections) > > > > > in postmaster.opt which I take it is the equivalent of the new > > > > > postgresql.conf file. > > > > > > > > > > From all that is being written about later versions I suspect > > > > > that this is far too low. Would I be fairly safe in making the > > > > > no of shared buffers larger? Also is there an equivalent of > > > > > effective_cache_size that I can set for version 7? > > > > > > > > > > Many thanks in advance > > > > > Hilary > > > > > > > > > > > > > > > > > > > > > > > > > Hilary Forbes > > > > > ------------- > > > > > DMR Computer Limited: http://www.dmr.co.uk/ > > > > > Direct line: 01689 889950 > > > > > Switchboard: (44) 1689 860000 Fax: (44) 1689 860330 > > > > > E-mail: hforbes@dmr.co.uk > > > > > > > > > > ********************************************************** > > > > > > > > > > > > > > > ---------------------------(end of > > > broadcast)--------------------------- > > > > > TIP 5: Have you checked our extensive FAQ? > > > > > > > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > > > > > > > > > > > > > > > > > > ---------------------------(end of > broadcast)--------------------------- > > > > TIP 5: Have you checked our extensive FAQ? > > > > > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > > > > > > > > > > ---------------------------(end of > broadcast)--------------------------- > > > TIP 6: Have you searched our list archives? > > > > > > http://archives.postgresql.org > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >
Good questions. Basically, telling postgresql it has a larger disk cache makes it favor index operations, smaller makes it favor seq scans. If your machine has super fast I/O then you may want it to favor seq scans, whereas if you have more CPU power than I/O bandwidth then you'd likely want it to favor index operations. Note that even if you are running java and it is using a few hundred megs of ram, it's quite likely that postgresql is still using most of the disk cache, as the memory java is using is likely allocated to hold its created data structures, not stuff loaded from disk. On Tue, 1 Jul 2003, Howard Oblowitz wrote: > Thanks. > > Some theoretical questions. > > The documentation says that Effective Cache Size "sets the optimizer's > assumption > about the effective size of the disk cache ( that is, the portion of the > kernel's disk > cache that will be used for PostgreSQL data files ). > > What then will be the effect of setting this too high? > > And too low? > > How does it impact on other applications eg Java ? > > > > -----Original Message----- > > From: scott.marlowe [SMTP:scott.marlowe@ihs.com] > > Sent: 01 July 2003 03:20 > > To: Howard Oblowitz > > Cc: pgsql-performance@postgresql.org > > Subject: Re: FW: [PERFORM] Version 7 question > > > > The best way to set it is to let the machine run under normal load for a > > while, then look at the cache / buffer usage using top (or some other > > program, top works fine for this). > > > > My server with 1.5 gig ram shows 862328K cache right now. So, just divide > > > > by page size (usually 8192) and I get ~ 100,000 blocks. > > > > On Tue, 1 Jul 2003, Howard Oblowitz wrote: > > > > > What would be the best value range for effective_cache_size > > > on Postgres 7.3.2, assuming say 1.5 GB of RAM and > > > shared_buffers set to 8192, and shmmax set to 750mb? > > > > > > And what are the most important factors one should take > > > into account in determining the value? > > > > > > > > > > > > > -----Original Message----- > > > > From: scott.marlowe [SMTP:scott.marlowe@ihs.com] > > > > Sent: 01 July 2003 02:56 > > > > To: Michael Mattox > > > > Cc: Hilary Forbes; pgsql-performance@postgresql.org > > > > Subject: Re: [PERFORM] Version 7 question > > > > > > > > 8192 is only 64 megs of RAM, not much, but a good number. Keep in > > mind > > > > that the kernel tends to be better at buffering huge amounts of disk, > > > > while postgresql is better left to use buffers that are large enough > > for > > > > the current working set (i.e. not your whole database, just the > > largest > > > > amount of data you're slinging about on a regular basis in one query.) > > > > > > > > On a machine with 1.5 gig of RAM, I've found settings as high as 32768 > > > > > > (256 megs of ram) to run well, but anything over that doesn't help. > > Of > > > > course, we don't toss around more than a hundred meg or so at a time. > > If > > > > > > > > our result sets were in the gigabyte range, I'd A: want more memory > > and B: > > > > > > > > Give more of it to postgresql. > > > > > > > > The original poster was, I believe running 7.0.x, which is way old, so > > no, > > > > > > > > I don't think there was an equivalent of effective_cache_size in that > > > > version. Upgrading would be far easier than performance tuning 7.0. > > since > > > > > > > > the query planner was much simpler (i.e. more prone to make bad > > decisions) > > > > > > > > in 7.0. > > > > > > > > On Tue, 1 Jul 2003, Michael Mattox wrote: > > > > > > > > > I have my shared buffers at 8192 and my effective cache at 64000 > > (which > > > > is > > > > > 500 megs). Depends a lot on how much RAM you have. I have 1.5 gigs > > and > > > > > I've been asking my boss for another 512megs for over a month now. > > I > > > > have > > > > > no idea if my buffers are too high/low. > > > > > > > > > > Michael > > > > > > > > > > > -----Original Message----- > > > > > > From: pgsql-performance-owner@postgresql.org > > > > > > [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Hilary > > > > > > Forbes > > > > > > Sent: Tuesday, July 01, 2003 2:10 PM > > > > > > To: pgsql-performance@postgresql.org > > > > > > Subject: [PERFORM] Version 7 question > > > > > > > > > > > > > > > > > > I'm just trying to improve performance on version 7 before doing > > > > > > some tests and hopefully upgrading to 7.3. > > > > > > > > > > > > At the moment we have > > > > > > B=64 (no of shared buffers) > > > > > > N=32 (no of connections) > > > > > > in postmaster.opt which I take it is the equivalent of the new > > > > > > postgresql.conf file. > > > > > > > > > > > > From all that is being written about later versions I suspect > > > > > > that this is far too low. Would I be fairly safe in making the > > > > > > no of shared buffers larger? Also is there an equivalent of > > > > > > effective_cache_size that I can set for version 7? > > > > > > > > > > > > Many thanks in advance > > > > > > Hilary > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Hilary Forbes > > > > > > ------------- > > > > > > DMR Computer Limited: http://www.dmr.co.uk/ > > > > > > Direct line: 01689 889950 > > > > > > Switchboard: (44) 1689 860000 Fax: (44) 1689 860330 > > > > > > E-mail: hforbes@dmr.co.uk > > > > > > > > > > > > ********************************************************** > > > > > > > > > > > > > > > > > > ---------------------------(end of > > > > broadcast)--------------------------- > > > > > > TIP 5: Have you checked our extensive FAQ? > > > > > > > > > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > > > > > > > > > > > > > > > > > > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > > > > TIP 5: Have you checked our extensive FAQ? > > > > > > > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > > > > > > > > > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > > > TIP 6: Have you searched our list archives? > > > > > > > > http://archives.postgresql.org > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 2: you can get off all lists at once with the unregister command > > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend >
On Tue, 1 Jul 2003 15:50:14 +0200 , Howard Oblowitz <HowardO@LEWIS-STORES.com> wrote: >What then will be the effect of setting this too high? The planner might choose an index scan where a sequential scan would be faster. >And too low? The planner might choose a sequential scan where an index scan would be faster. >How does it impact on other applications eg Java ? It doesn't -- at least not directly. (There could be very subtle effects when Postgres does a sequential scan over a large relation thus pushing everything else out of the cache, where an index scan would have read only a small number of pages. Or when a large index scan turns your machine from CPU bound to I/O bound.) Servus Manfred
On 1 Jul 2003 at 15:50, Howard Oblowitz wrote: > The documentation says that Effective Cache Size "sets the optimizer's > assumption > about the effective size of the disk cache ( that is, the portion of the > kernel's disk > cache that will be used for PostgreSQL data files ). > > What then will be the effect of setting this too high? > > And too low? Let's say postgresql is preparing a plan for a scan and it estimates data set size as 100MB whereas your shared buffers+effective cache is 80M. So postgresql would deduce that it would be better off with sequential scan rather than index scan. Where in fact you have much more memory to make a file system cache and the machine can afford index scan. There is nothing too low or too high of a setting. This isn't exactly performance tuning paramter as other. This is more of information to postgresql. The closer it gets to truer, the plans produced would get optimal. About how to set this parameter, it is roughly effective cache size= (Physical RAM size-shared buffers-requirement for other apps) * 0.8 This is very very rough. You need to make sure that some setting does not trigger a swap avelanche HTH Bye Shridhar -- Slous' Contention: If you do a job too well, you'll get stuck with it.