on 04-22-2008 1:53 PM
Is there a way to determine out of the statistics data in the dbanalyzer what would be the optimum cache size for the database?
I want to know that because we plan to buy a new box and I want to size it so the actual "daily used data" is in the cache -> no (or few) User-UKTs doing reading I/O. So basically building something like an "in memory database".
Markus
Hi,
the optimum size for the data cache depends on the used Perm size of the database and on the application.
For SAP Systems we please check the formula in SDN using the following link:
[Memory Sizing|https://wiki.sdn.sap.com/wiki/x/RWY]
Regards, Christiane
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Christiane,
> the optimum size for the data cache depends on the used Perm size of the database and on the application.
Yes - I know that - that's why I ask thist question how to find out.
> For SAP Systems we please check the formula in SDN using the following link:
> [Memory Sizing|https://wiki.sdn.sap.com/wiki/x/RWY]
Our database size is 1.8 TB - the given rule-of-thumb would mean we should have 36 GB CACHE_SIZE. We currently have 32 GB but there's still a lot of I/O done (logical I/O: 48.751.253.597 vs. physical I/O 386.955.885).
Since the dbanalyzer gives many statistical values I'm curious if there is a way to find out, which size of the cache would eleminate (at least theoretically) UKTs doing I/O
Markus
Hi Markus,
interesting are the physical I/O. This tells us that not all pages which have to be processed can be found in the data Cache.
The parameter LRU_FOR_SCAN restricts the size of the data cache which is used for Scans. More Info about the Data Cache configuration you will find in the MaxDb Internals slides: [Parameters|http://maxdb.sap.com/training/internals_7.6/kernel_parameters_EN_76.pdf]
Could it be that we have a lot of scans in the system on large tables?
Did you get any more detailrd information with the Command Monitor which commands (Reports) have to read from disk?
May be we should analyze this directly on your system via OSS call to get more ideas.
Regards, Christiane
> interesting are the physical I/O. This tells us that not all pages which have to be processed can be found in the data Cache.
Yes - and there's a lot of it
> The parameter LRU_FOR_SCAN restricts the size of the data cache which is used for Scans. More Info about the Data Cache configuration you will find in the MaxDb Internals slides: [Parameters|http://maxdb.sap.com/training/internals_7.6/kernel_parameters_EN_76.pdf]
LRU_FOR_SCAN is set to NO. If we would set that to YES and one person would start a big job (MRP-runs for PP, CO precaluations etc.) this would "trash" the full cache and all other UKTs would need to do I/O to get their current data again.
> Could it be that we have a lot of scans in the system on large tables?
> Did you get any more detailrd information with the Command Monitor which commands (Reports) have to read from disk?
No full table scans but "just" longrunning jobs (tablename, runtime, Cache I/O, Disk I/O)
Table Runtime P accesses P Cache I/O # Disk I/O
===================================================================
COVP 61.866,92 6.289.191 196.602 6.092.589
ACCTIT 35.678,21 5.215.440 896.540 4.318.900
COSS 12.321,35 1.872.759 606.268 1.266.491
BALHDR 12.238,66 1.134.360 65.534 1.068.826
COSS 12.109,92 1.764.038 601.264 1.162.774
VBRP 11.909,13 1.850.768 660.289 1.190.479
COSS 11.188,26 1.872.726 601.976 1.270.750
BKPF 8.734,46 916.325 65.534 850.791
There's not a single table scan done, all are done via IX_RG or PK_RG or a combination of them. Those are just the "top 10", there are much more of those.
What I now want to archive is the Disk-I/O becoming 0 due to a big CACHE_SIZE.
>
> May be we should analyze this directly on your system via OSS call to get more ideas.
340157/2008 - same question there too
Markus
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.