Hello,
I try to fix performance/cache-hit-rate problems on a MaxDB v 7.5.00.44.
I've fixed and improved several effects but there is still a pure Data-Cache-Hitrate
> Cache Sizes
>
>
> I/O Buffer Cache: 1.562,50 MB
>
> Data Cache: 1.549,00 MB
> Converter: 12,88 MB
> Misc.: 0,62 MB
>
> Catalog Cache: 411.648,00 MB
>
> Sequence Cache: 0,01 MB
>
> Cache Accesses
>
> Acesses Successful Unsuccessful Hit Rate
>
> Data Cache: 8.143.185.274 7.915.878.680 227.306.594 97,21 %
> SQL Data: 8.140.734.533 7.913.427.939 227.306.594 97,21 %
>
> History/Undo: 2.450.874 2.450.874 0 100,00 %
>
> Catalog Cache: 3.334.089.052 3.150.536.080 183.554.889 94,49 %
> Sequence Cache: 1.063 1.062 1 99,91 %
>
> Parameters
>
> CACHE_SIZE: 200000
>
> CAT_CACHE_SUPPLY: 52690944
>
> USE_MEM_ENHANCE:
>
> MEM_ENHANCE_LIMIT:
The DB-analyzer with interval 60 sec reports to me that there are more than 200 table-scans within a minute
> ===== #2 at 2008-03-26 14:46:17
> * I SQL commands executed: 10859, avg. 182.11 per second
> CON: PureSQL_Cmds > INTERVAL * 5
> VAL: 10859 > 61 * 5
> * W3 Data cache hitrate (SQL Pages) 80.69%, 448569 of 2322671 accesses failed
> CON: DC_Hit < 96 && ( PReads ) > MAX_IDLE_IO_ALL_DEVS
> VAL: 80.69 < 96 && ( 448570 ) > 2440
> ACT: In addition to enlarging the data cache (note the paging risk of the operating system), search for the cause of the high read activity. Frequently, individual SQL statements cause a high percentage of the total logical and physical read activities. Enlarging the cache only moves the load from the disk to the CPU although an additional index could transform a read-intensive table scan into a cheap direct access.
> DES: For a running database application the data cache hitrate should not be less than 99%, otherwise too much data has to be read physically. Data cache hitrates less than 99% for intervals of 15 minutes or more must be avoided.
> * W3 Selects and fetches selectivity 0.02%: 2779 selects and fetches, 20199200 rows read, 4033 rows qualified
> CON: SelFetSel < 0.3
> VAL: 0.02 < 0.3
> * W1 Updates selectivity 3.66%: 316 updates, 4129 rows read, 151 rows qualified
> CON: UpdSel < 5.0
> VAL: 3.66 < 5.0
> * W3 215 table scans, selectivity 0.00%: 21087692 rows read, 323 rows qualified
> CON: TabScanSel < 0.3
> VAL: 0.00 < 0.3
> * I Number of physical reads: 448570
> CON: PReads > ( 4 * MAX_IDLE_IO_ALL_DEVS )
> VAL: 448570 > ( 4 * 2440 )
My DBA-user holds 273 tables (some with millions of rows) with 563 indizies on it - my feeling is at last one index is still missing...
Our own CRM-system offers the possibility to "click together" requests - what makes it nearly impossible to get reasonable informations concerning the WHERE clauses.
The high amount of tablescans let me think that it must be in a often used function - but i can't figure out which ...
Now I have two questions
1.) Is there any "best-practice" to find out (on server-side) which statement (WHERE-part?)
caused the tablescans ( a trace had no stisfying result to me - maybe you can give
me a hint with the right option and debug-level)
2.) More common question to shared memory resources:
Referring to the parameter "CACHE_IN_SHARED_MEMORY" ( "...can lead to performance problems on NUMA architectures.")
Which CACHE is ment by this parameter - set this to YES and tell OS to share three of four gigs of memory?
Is there any documentation/commendation what is best memory-setup on SMP-Linux?
Any hints are welcome - thanks in advance
Harald Flaucher
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.