473,836 Members | 1,987 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

DB cache size strategies


Here's some of my current notions on pgsql performance tuning strictly as it
relates to pgsql tuning parameters in the context of a dedicated linux or
hpux server. I'm particularly focusing on the shared_buffers setting. I
invite any corrective or confirming feedback. I realize there are many
other hugely important performance factors outside this scope.

One key aspect of pgsql performance tuning is to adjust the memory
consumption settings (shared_buffers , sort_mem, etc) large enough to hold
as much of the database in shared memory as possible while not causing page
swap-ins. I understand that both page swap-outs and swap space usage is
normal and OK, but lots of page swap-ins are bad). In other words, for
absolute fastest performance, we want a database buffer cache hit rate of
as close to 100% as possible.

Now, how do we accurately measure this cache hit rate at any one point?
Well, here's what I currently know as the best measure for a given cluster
of databases:

SELECT SUM(pg_stat_get _db_blocks_hit( d.oid))
/ SUM(pg_stat_get _db_blocks_fetc hed(d.oid)) AS hit_rate
FROM pg_database d;

How do we identify when we have sub-optimal pgsql configuration parameters
in this regard? My understanding: to whatever extent the hit rate is
below 100%, our shared buffers are not large enough to hold the data we're
accessing.

How do we fix the problem? We want to increase the shared_buffers setting
and/or maybe some combination sort_mem, wal_buffers, vacuum_mem, reset our
stats, and resume monitoring. I assume which of these we increase for
maximum benefit depends on the sources of the cache overflow (routine
queries, sorting, vacuuming), but that shared_buffers is the most impactful
in practice. Again, we want to do this without causing page swap-ins.
When you see swap-ins, you've gone too far. If we experience swap-ins and
less than 100% cache hit rate by any significant amount, then we need more
RAM or less RAM consumption.

The end result is that page swap-ins (sar -W) and cache hit rate (query
above) are two very key measures to use as warning bells.

Glad to hear your thoughts.

Ed
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 22 '05
26 6286
"Ed L." <pg***@bluepolk a.net> writes:
In general, would it be true to say that if one does *not* anticipate
contention for kernel disk cache space from non-DB processes (e.g., the
dedicated db server), then you probably want to use theory (1)? If one
*does* anticipate such contention (e.g. the all-on-one-box web-db app),
then you probably want to use theory (2) in order to ensure an adequate
cache?


If the box is doing other things then I think you definitely want to
keep shared_buffers pretty small, relying on the kernel to allocate the
available resources as best it can.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #21
"Ed L." <pg***@bluepolk a.net> writes:
Then what scenarios, if any, merit theory (2) over theory (1)?


I'd only consider a large-cache setting on a machine that's dedicated to
running the database (where "dedicated" means "that's the only thing you
care about performance of", as in your first scenario). Even then I'd
test it against the other way. As Andrew Sullivan notes nearby, our
experience has been that the PostgreSQL buffer manager isn't all that
efficient about managing large caches. It's possible that Jan's current
work will change that situation in 7.5, but I'd still test first ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 22 '05 #22
On Wednesday February 11 2004 9:18, Tom Lane wrote:
"Ed L." <pg***@bluepolk a.net> writes:
In general, would it be true to say that if one does *not* anticipate
contention for kernel disk cache space from non-DB processes (e.g., the
dedicated db server), then you probably want to use theory (1)? If one
*does* anticipate such contention (e.g. the all-on-one-box web-db app),
then you probably want to use theory (2) in order to ensure an adequate
cache?


If the box is doing other things then I think you definitely want to
keep shared_buffers pretty small, relying on the kernel to allocate the
available resources as best it can.


Then what scenarios, if any, merit theory (2) over theory (1)? I can think
of two where I'd expect that to be the case.

First, suppose DB processes are more important (and thus more deserving of
cache) than other processes in an all-on-one-box case. For example, the
non-DB processes consisted strictly of various non-performance-critical
programs to analyze large log files, etc. Would it then make sense to use
theory (2) to ensure those non-critical programs do not inadvertently
increase I/O for the DB processes?

Second, suppose we have 2 clusters running on a dedicated DB server, each
with a large enough dataset to cause the other's data to be completely
crowded out of the kernel cache during backups under theory (1), causing a
lot of disk I/O for the other as the other gradually reloads. If we use
theory (2), allocating roughly half of available RAM to each DB cache
(minus breathing room for admin, OS), I would expect that over time, the
entire DB dataset for each cluster would work its way into each cluster's
DB cache and be largely immune to the activities of the other cluster.
We'd consider that a good thing. Would this be an appropriate scenario for
theory (2)?


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 22 '05 #23
On Wednesday February 11 2004 9:57, Tom Lane wrote:
"Ed L." <pg***@bluepolk a.net> writes:
Then what scenarios, if any, merit theory (2) over theory (1)?


I'd only consider a large-cache setting on a machine that's dedicated to
running the database (where "dedicated" means "that's the only thing you
care about performance of", as in your first scenario). Even then I'd
test it against the other way. As Andrew Sullivan notes nearby, our
experience has been that the PostgreSQL buffer manager isn't all that
efficient about managing large caches. It's possible that Jan's current
work will change that situation in 7.5, but I'd still test first ...


Great. Thx to all for feedback, very informative, interesting, and helpful
in practice.

Ed

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 22 '05 #24
On Wed, Feb 11, 2004 at 09:47:35AM -0700, Ed L. wrote:
First, suppose DB processes are more important (and thus more deserving of
cache) than other processes in an all-on-one-box case. For example, the
non-DB processes consisted strictly of various non-performance-critical
programs to analyze large log files, etc. Would it then make sense to use
theory (2) to ensure those non-critical programs do not inadvertently
increase I/O for the DB processes?
Are you assuming that shared memory will not be swapped out? If you allocate
lots of the shared memory to the cluster that it's not going to used anyway
most of the time, you're just going to find it's being swapped.
Second, suppose we have 2 clusters running on a dedicated DB server, each
with a large enough dataset to cause the other's data to be completely
crowded out of the kernel cache during backups under theory (1), causing a
lot of disk I/O for the other as the other gradually reloads. If we use
theory (2), allocating roughly half of available RAM to each DB cache
(minus breathing room for admin, OS), I would expect that over time, the
entire DB dataset for each cluster would work its way into each cluster's
DB cache and be largely immune to the activities of the other cluster.
We'd consider that a good thing. Would this be an appropriate scenario for
theory (2)?
I'd expect the kernel the swap out enough of the shared memory in each
cluster to match the actual working set. Although, I imagine PostgreSQL's
buffer management may affect the way it's gets swapped. I have no idea
whether PostgreSQL's buffer management code does a lot of page touching.

Note, some OS's don't swap out shared memory, so the above doesn't apply to
them.

--
Martijn van Oosterhout <kl*****@svana. org> http://svana.org/kleptog/ (... have gone from d-i being barely usable even by its developers
anywhere, to being about 20% done. Sweet. And the last 80% usually takes
20% of the time, too, right?) -- Anthony Towns, debian-devel-announce


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFAKo4FY5T wig3Ge+YRAvkMAK C0mNBg4P6Tr3Rp6 anJuaYal9qkNwCd HK6A
bdzipH++nDu2VnF CLAhhP5Y=
=0vKr
-----END PGP SIGNATURE-----

Nov 22 '05 #25
Has anyone discussed adding to postgres the ability to figure this out
on it's own. Couldn't it gather some statistics about the kind of
resources that it is actually using and adjust accordingly. You could
give it a max amount to use for the shared buffers but if it was so high
that it degraded performance postgres could just cut back on what it
actually used.

Is this even feasile? Correct me if I am wrong but it seems that most
other dbs seem to work this way.

It would make installing a nice tuned postgres a much more turn key
operation.

rick
Ed L. wrote:
On Wednesday February 11 2004 9:57, Tom Lane wrote:
"Ed L." <pg***@bluepolk a.net> writes:
Then what scenarios, if any, merit theory (2) over theory (1)?


I'd only consider a large-cache setting on a machine that's dedicated to
running the database (where "dedicated" means "that's the only thing you
care about performance of", as in your first scenario). Even then I'd
test it against the other way. As Andrew Sullivan notes nearby, our
experience has been that the PostgreSQL buffer manager isn't all that
efficient about managing large caches. It's possible that Jan's current
work will change that situation in 7.5, but I'd still test first ...

Great. Thx to all for feedback, very informative, interesting, and helpful
in practice.

Ed

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

Nov 22 '05 #26
On Wednesday 11 February 2004 21:40, Rick Gigger wrote:
Has anyone discussed adding to postgres the ability to figure this out
on it's own. Couldn't it gather some statistics about the kind of
resources that it is actually using and adjust accordingly. You could
give it a max amount to use for the shared buffers but if it was so high
that it degraded performance postgres could just cut back on what it
actually used.

Is this even feasile? Correct me if I am wrong but it seems that most
other dbs seem to work this way.

It would make installing a nice tuned postgres a much more turn key
operation.


What if making the DB run faster makes everything else run slower? How does it
know whether 0.1sec i/o time was just it, or if there was another process
contending for disk access?

Then again, maybe interactive speed isn't important, but your bulk update is.
Or, perhaps your report can wait, but a realtime response is vital.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 22 '05 #27

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
5608
by: xixi | last post by:
i am using db2 udb v8.1 on win 64 bit, i found this on my db2diag.log 2003-11-12-13.20.05.550001 Instance:DB2 Node:000 PID:1840(db2syscs.exe) TID:3068 Appid:AC100453.G761.00F8D15749BE access plan manager sqlra_cache_mem_please Probe:21 Database:NJIPD Package Cache Number Overflows 0x000006FAFFBF141C : 0x00000001 .... 2003-11-12-13.20.05.780000 Instance:DB2 Node:000
2
4216
by: xixi | last post by:
we are using db2 udb v8.1 on win64bit 2003 server, i found this in db2diag.log, our application used jdbc for connection to this remote server, why the overflow number keep increasing, what should i do with this? our maxappls=60, pckcachesz = 859 2004-05-05-14.53.11.720000 Instance:DB2 Node:000 PID:2232(db2syscs.exe) TID:464 Appid:AC1004ED.G890.00FC56AF3D38 access plan manager sqlra_cache_mem_please Probe:21 Database:NJIPD
1
2590
by: aj | last post by:
DB2 v8 FP5 Red Hat AS/EL 2.1 I found the following in my db2diag.log: 2004-09-07-10.01.37.747898 Instance:oltp Node:000 PID:14637(db2agent (NDRDB)) TID:8192 Appid:C0455584.K508.00E507141135 access plan manager sqlra_cache_mem_please Probe:21 Database:NDRDB Package Cache Number Overflows
10
4861
by: datapro01 | last post by:
Running DB2 8.1.6A on AIX 5.1 We are experience package cache overflows. The high water mark for package cache is showing as 16,108,513 bytes, or approximately 3933 4K pages. The package cache size is set at Maxapples * 8 Maxapples is at 500.
17
4838
by: emerth | last post by:
Hello all: I have read references to optimizing C code to exploit the CPU cache of <insert your favourite CPU>. Can anyone point me towards some info on the 'net that gets into techniques for this kind of thing in C (or C++) in some depth? I imagine this can get specific to the CPU in question.
6
1778
by: Adam | last post by:
On an xp machine, the caching works as expected. I have deployed to a win2k server, and an item I add to the cache expires almost immediately some times and in under a minute in other times. The aspnet_wp process is not restarting during this time, and there are no other requests, so nothing should be clearing this out. I have added a CacheItemRemovedCallback which writes debug info out to a DefaultTraceListener, but no output shows up on...
6
2526
by: ShaveDave27 | last post by:
Hi, I'm trying to create a cache which will use direct mapping and output a miss rate for a list of integers. I'm struggling quite a bit. I think I have created the cache OK, probably not though - heres the code. import java.util.*; import java.util.LinkedHashMap; public class Cache<A,B> {
3
5733
by: aj | last post by:
DB2 LUW 8.1 fixpak 14 Red Hat EL AS 4.4 I'm trying to diagnose some nocturnal CPU pressure, and am trying to understand the dynamic statement cache as it applies to LUW. The only doc/redbooks I am finding are for Z/OS, which I am completely ignorant of. I am using only Java and JDBC in my applications. No static SQL.
10
1868
by: =?Utf-8?B?TWFyaw==?= | last post by:
Hi... We've been trying to migrate our asp.net apps off older, underpowered hardware to newer, bigger boxes but when we do, we see our databases start to melt. When I started to look into it, I found that the older boxes all had bigger EffectivePrivateBytesLimit values than the newer boxes, which seems very counter-intuitive to me. And it seemed to me that a much smaller Cache would be pushing more requests back to the databases.
0
9820
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9670
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10844
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10548
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9374
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7792
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6979
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5826
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3115
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.