Re: Increasing catcache size

I am thinking we should scale it based on max_fsm_relations.

---------------------------------------------------------------------------

Tom Lane wrote:
> Awhile back, there was a discussion about psql \d display being really
> slow in a database with 4000 tables:
> http://archives.postgresql.org/pgsql-hackers/2005-09/msg01085.php
>
> I looked into this some, and it seems that part of the problem is that
> the catalog caches are limited to hold no more than 5000 tuples in total
> (MAXCCTUPLES in catcache.c). When you're looking at thousands of
> tables, the tuples you want age out of cache immediately and so every
> SearchSysCache call has to actually go to disk (or at least shared
> buffers).
>
> The 5000 setting is basically Berkeley-era (when we got the code from
> Berkeley, the limit was 300 tuples per cache, which I changed to a
> system-wide limit some years ago). A reasonable guess is that we're
> talking about entries of 100 to 200 bytes each, making the maximum
> catcache space usage a megabyte or less (per backend, that is). Seems
> like this is mighty small on modern machines.
>
> We could make a GUC variable to control this, or just arbitrarily bump
> up the constant to 50000 or so. Is it worth another GUC? Not sure.
>
> There's another hardwired setting right next to this one, which is the
> number of hash buckets per catcache. Since the caches are likely to
> have significantly different sizes, that is looking a bit obsolete too.
> We could put per-cache numbers into the setup lists in syscache.c
> easily. Making the cache sizes dynamic a la dynahash.c is another
> possibility, but I'm unsure if it's worth the trouble.
>
> Thoughts?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +