Re: Calculate optimal value for DS_POOLSIZE
On Oct 16, 4:10 am, Colin Dawson <cjd_1...@hotmail.com> wrote:
> For what it's worth, these are my settings
>
> # Dictionary Cache Size
> DD_HASHSIZE 499 # Number of dictionary hash buckets
> DD_HASHMAX 68 # Maximum number of dictionary entries per hash bucket>
> # Distribution Cache Size
> DS_HASHSIZE 503 # Number of distribution hash buckets
> DS_POOLSIZE 4000 # Maximum number of distribution entries retained in cache>
> # UDR Cache Size
> PC_HASHSIZE 127 # Number of Procedure hash buckets
> PC_POOLSIZE 2000 # Maximum number of stored procedure entries retained in cache>
> I had some discussions with UK Tech Support about the settings and their respective values. This is their response:
>
> Reproduction:
>
> # Created 1500 tables, populated each with 500 rows
>
> # Ran update stats high on all tables
>
> # Queried each table to force the population of the distributions into the cache.
>
> # The structure of interest, hangs off of rhead_t ...
>
> onstat -g dmp 0xa000000 hlQA | grep rhead>
> onstat -g dmp 0xa008800 rhead_t | grep sh_copy_of_shdist>
> onstat -g dmp 0x15588018 shcache_t>
> # Same tests with different DS_POOLSIZE / DS_HASHSIZE values ...
>
> DS_POOLSIZE 4000
>
> DS_HASHSIZE 1003>
> 0000: sh_hashsize = 1003
>
> 0004: sh_usage = 1
>
> 0008: sh_uid = 1508
>
> 000c: sh_cleaned = 0
>
> 0010: sh_cachesize = 4000
>
> 0014: sh_curcount = 1508
>
> 0018: sh_inusecount = 0
>
> 001c: sh_lowthresh = 50
>
> 0020: sh_highthresh = 75
>
> 0024: sh_pool = 0x6d172c
>
> 0028: sh_entry = 0x15588050
>
> 002c: sh_lru = 0x15a2ec30
>
> 0030: sh_mru = 0x1558af88
>
> 0034: sh_mutex = 0x15585328
>
> }
>
> DS_POOLSIZE 3000
>
> DS_HASHSIZE 503>
> *(struct shcache_t *)0x15586288 = { /* sizeof = 56 = 0x38 */
>
> 0000: sh_hashsize = 503
>
> 0004: sh_usage = 1
>
> 0008: sh_uid = 1500
>
> 000c: sh_cleaned = 1
>
> 0010: sh_cachesize = 3000
>
> 0014: sh_curcount = 1499
>
> 0018: sh_inusecount = 0
>
> 001c: sh_lowthresh = 50
>
> 0020: sh_highthresh = 75
>
> 0024: sh_pool = 0x6d172c
>
> 0028: sh_entry = 0x155862c0
>
> 002c: sh_lru = 0x15802830
>
> 0030: sh_mru = 0x175d4030
>
> 0034: sh_mutex = 0x15585328
>
> }
>
> DS_HASHSIZE 503
>
> DS_POOLSIZE 2000>
> *(struct shcache_t *)0x15586288 = { /* sizeof = 56 = 0x38 */
>
> 0000: sh_hashsize = 503
>
> 0004: sh_usage = 1
>
> 0008: sh_uid = 1500
>
> 000c: sh_cleaned = 501
>
> 0010: sh_cachesize = 2000
>
> 0014: sh_curcount = 999
>
> 0018: sh_inusecount = 0
>
> 001c: sh_lowthresh = 50
>
> 0020: sh_highthresh = 75
>
> 0024: sh_pool = 0x6d172c
>
> 0028: sh_entry = 0x155862c0
>
> 002c: sh_lru = 0x16215830
>
> 0030: sh_mru = 0x16209be0
>
> 0034: sh_mutex = 0x15585328
>
> }
>
> The tests show the influencing parameter is DS_POOLSIZE, and the reason the integer SH_CURCOUNT only shows half the value defined (when the pool is flooded) is down to the sh_lowtheshold value. This appears to keep the dist pool cache current value to half the pools size.
>
> I can't find any where in the code which suggests why we look to be keeping this cache within the 50-75 % threshold. The functions to manipulate these values look to be generic for any cache (procedure, distribution or the dictionary) and may be part of the problem.
>
> There is one place where this counter is incremented and decremented:
>
> retrieve_cache() / cleanup()
>
> >From the customers output of onstat -g dsc, the difference in current value (999) and that listed by an incremental counter while printing each entry (1004) I cannot replicate. I suspect this may be due to their system being in use at the time, and the values updated while this onstat was taken, and it would soon settle back to 999. So the actual poolsize is indeed 2000.
>
> Something else that was noticed by Advanced Support is the output of onstat -g dsc in later IDS 9.40 versions is different, the 'Number of entries' is no longer shown:
>
> InformixDynamic Server Version 9.40.UC1 -- On-Line -- Up 33 days 18:10:09 -- 117120 Kbytes
>
> Distribution Cache:
>
> Number of lists : 31
>
> DS_POOLSIZE : 127>
> Distribution Cache Entries:
>
> list# id ref_cnt dropped? heap_ptr distribution name
>
> -----------------------------------------------------------------
>
> = = = 8< = = =
>
> Total number of distribution entries: 42.
>
> Number of entries in use : 0
>
> Compare this to the onstat -g dsc output you provided:
>
> InformixDynamic Server Version 7.31.FD4 -- On-Line -- Up 02:41:14 -- 10290176 Kbytes
>
> Distribution Cache:
>
> Number of lists : 503
>
> DS_POOLSIZE : 2000>
> Number of entries : 999
>
> Number of entries in use : 0
>
> Distribution Cache Entries:
>
> list# id ref_cnt dropped? heap_ptr distribution name
>
> -----------------------------------------------------------------
>
> = = = 8< = = =
>
> Total number of distribution entries: 1004.
>
> I cannot find any known issues or bugs that suggest why this change was made, but as this behaviour in 9.40 is different I am doubtful that a bug fix will be provided. Although this is not an entirely conclusive answer, it does confirm that the actual poolsize is indeed 2000.
>
> Regards
>
> Colin
>
> There are 10 types of people in the world, those that understand binary and those that don't
>
>
>
> > Date: Tue, 16 Oct 2007 11:16:49 +0100
> > From: richard.harn...@lineone.net
> > Subject: Re: Calculate optimal value for DS_POOLSIZE
> > To:informix-l...@iiug.org
>
> > Art S. Kagel wrote:
> >> On Oct 15, 6:49 pm, Mark Jamison wrote:
> >>> Actually I believe the confusion is betwen the DS cache values with the DD cache variables.
>
> >>> DD_HASHSIZE and DD_HASHMAX mean something else. DD_HASHSIZE is the number of buckets or lists, and DD_HASHMAX is the number of entries (soft limit) per buck or list.
>
> >>> DS_POOLSIZE indicates the maximum number of entries for the Distribution Cache, this is a hard limit and not a soft limit.
> >>> DS_HASHSIZE is the number of buckets or lists for the Cache.
>
> >>>From the Administrator's Reference:
>
> >> 'The DS_POOLSIZE parameter specifies the maximum number of entries in
> >> each hash bucket in the data-distribution cache ...'
>
> >> 'The DS_HASHSIZE parameter specifies the number of hash buckets in the
> >> data-distribution cache ...'
>
> >> DS_POOLSIZE is a max, but it's a max per hash bucket not an absolute
> >> total.
>
> >> If there's confusion it's in the manuals and it's been there since the
> >> beginning.
>
> > The manual is wrong. It works as Mark described it.
>
> > The Performance Guide (ct1t9na.pdf, 4-33) contradicts the Admin-Ref:
>
> > The following formula determines the number of column distributions
> > that can be stored in one bu