<html><head></head><body>Hi Karl,<br /><br />Thank you for the quick response! I'll digest what you've written and apply it to my situation and see how things go. Your answers were very clear and straight-forward, which is a great help.<br /><br />-Michel<br /><br /><hr /><strong>From: </strong><span title="schendel@kbcomputer.com">"Karl and Betty Schendel" <schendel@kbcomputer.com></span><br /><strong>Date: </strong>Thu, Jan 4, 2018 3:30 pm<br /><strong>To: </strong><span title="info-ingres@lists.planetingres.org">"Ingres and related product discussion forum" <info-ingres@lists.planetingres.org></span><br /><strong>Subject: </strong>Re: [Info-ingres] Effective Ingres Caching<br />
<article><br />> On Jan 4, 2018, at 5:03 PM, Michel Forget <mvf@cyberren.com> wrote:<br />> <br />> Hi,<br />> <br />> I'm looking for some advice on cache sizes in Ingres.<br />> [snip]<br />> So, some specific questions I am hoping to find answers to:<br />> <br />> 1. Is increasing dmf_cache_size the correct approach toward the goal of consuming more memory and increasing overall read performance by not having to hit the physical disk?<br /><br />Yes, up to a point. dmf_cache_size only affects the single-page area, and one may<br />also want to add group buffers; dmf_group_size to make them larger and dmf_group_count<br />to add more of them. The "right" group size depends on usage patterns, but in general<br />I'd try to adjust it so that groups work out to anywhere between 64k and 512k. I would<br />generally not go much larger because the penalty for tossing a group becomes very high,<br />and group!
management is not coded with large (100's of pages) groups in mind.<br /><br />> 2. Are there negative consequences to setting this value too high?<br /><br />Absolutely. The cache algorithms and code were written with cache size of a<br />few-thousand to few-tens-of-thousand pages in mind. It was never optimized for many<br />tens-of-thousands and up. (The code is better at large caches than it used to be,<br />back in the version 2.x and earlier days, but it's still not good at huge numbers of pages.)<br /><br />Part of the reason for this is that Ingres takes advantage of another cache which you<br />aren't seeing directly, and that's the filesystem cache. Ingres doesn't use raw or<br />direct I/O, unless you tell it to. My rough rule of thumb is to set the cache size so that<br />it's in the few-tens-of-thousands of pages range, and a few thousand groups,<br />and let the OS filesystem cache do the rest. The filesyste!
m cache is generally better<br />designed for using up many gigabytes of memory.<br /><br />(This isn't conventional DBMS wisdom, but conventional DBMS wisdom also assumes<br />either raw/direct I/O, or massive tablespaces where the underlying table structure is<br />hidden from the OS filesystem. Ingres never did it that way, and it can use the filesystem<br />cache a lot more effectively because of it.)<br /><br /><br />> 3. I've noticed that the output of "help table sometable;" shows a field called "cac! he priority". If I know which of my tables I consider to be most cache-worthy, can I use this setting to purposely bias the cache in favor of the tables I care about? If so, how can I do that? I've never seen anything that indicates how I could set this. <br /><br />modify tablename to priority=N<br />or modify tablename to <structure> with priority=N<br />N can be 1 to 8 (low to high).<br /><br />You have to be pretty careful with this. I personally don't think the imp!
lementation of this<br />feature was as good as it should have been. I'd use it sparingly and on smallish tables. Also, I<br />suspect that the only useful value is 8; I don't think you'll get much good out of setting a table's<br />cache priority to (say) 2 or 3.<br /><br />> 4. I realize this is super-general, but what do people generally consider "good" cache hit rates? I mean, if I keep throwing more memory at the cache can I reasonably expect to drive cache hit rates up to values approaching 100% (ie: if the cache were larger than the actual database itself)?<br /><br />Again, very very rough rule of thumb, but above 90% is usually more or less OK, while below<br />90% needs some thought. Below say 60% needs some real 'splainin; it might in fact be<br />the best one can do given some usage patterns, so it's not an automatic "make the cache<br />bigger". Also, remember that hit rates aren't e!
verything, a 90% hit rate is no good if the<br />cache has become so bloated that every reclaim/replace takes milliseconds of CPU time.<br /><br />By the way, I don't think you'll ever hit 100% because of the way it handles core catalogs.<br />I haven't experimented, and I could be wrong, but I'd be surprised to see a pure 100%<br />cache hit rate except under artificial circumstances such as no DDL, read-mostly load,<br />and either no session connects or all databases (including iidbdb) pinned open for the duration.<br /><br />Karl<br /><br />_______________________________________________<br />Info-ingres mailing list<br />Info-ingres@lists.planetingres.org<br />http://lists.planetingres.org/mailman/listinfo/info-ingres</article></body></html>