SAP MaxDB Database Configuration FAQ's
1. Is there any specific size limit for a SAP MaxDB database?
One can configure a maximum of 255 data volumes in the SAP MaxDB standard layout (parameter: VOLUMENO_BIT_COUNT or ConverterVolumeIDLayout= 8). The maximum size of a data volume can be 128 GB. However the maximum total size of the data volumes can be 32 TB. The log area can also use a maximum of 32 TB.
2. Is there any limited number of simultaneous sessions of a SAP MaxDB database?
No, there is no limit for the number of simultaneous sessions of an SAP MaxDB database. The database parameter MaxUserTasks is used to configure the number of database sessions that can be logged on simultaneously to the database.
The number of database users in OLTP systems should be configured to at least 2 x + 4.
The number of database users in OLTP systems should be configured to at least 3 x + 4.
The maximum number of connections to the database in the connection pool is determined for each J2EE instance (NW 7.1 is the default 70). The sum of connections (connection pool) of all J2EE instances + 4 is used to calculate the number of parallel user sessions (MaxDB parameter: MaxUserTasks).
The formula which is used to calculate the value for the database parameter MaxUserTasks for liveCaches in SCM system 4.1 and lower is:
2 x + 4
The formula which is applied for liveCaches in SCM system 5.0 and above is:
3 x + 4
One can also refer to 757914.
3. Why is it essential to run the MaxDB on a 64-bit platform?
One can refer to 1013441: Advantages for MaxDB on 64-bit platforms, for more information.
4. How large is it preferred to configure the data volumes of a SAP MaxDB database?
The optimum use of the I/O system is critical for I/O performance. Thus, it becomes beneficial to distribute the volumes evenly across the available I/O channels.
The parallelism of the I/O gets affected by the number of data volumes.
The asynchronous I/O of the operating system is used on Windows.
On UNIX the number of configured I/O threads determines the parallelism with which the SAP MaxDB/liveCache database transfers the I/O requests to the operating system.
o SAP MaxDB version lower than Version 7.7
The number of volumes * number of I/O threads for each volume (_IOPROCS_PER_DEV), gives the number of I/O threads.
o SAP MaxDB Version 7.7 or higher
Volumes * (total of low/med/high queues for each volume), gives the number of I/O threads. But it can get limited by the database parameter MaxIOPoolWorkers.
The number of threads gets increased by a number of threads that was configured too high. This results in reaching the limits of the operating system resources.
It is recommended to use the following formula to determine the size of SAP MaxDB data volumes: 'square root of the system size in GB, rounded up'.
10 GB: 4 data volumes
50 GB: 8 data volumes
100 GB: 10 data volumes
200 GB: 15 data volumes
500 GB: 23 data volumes
1 TB: 32 data volumes
It is preferred to have the same size for all the data volumes.
5. Do I need to change a data volume configuration which does not correspond to the recommendations of this note?
No, it is not recommended to change the existing configurations. In case of the occurrence of any serious I/O performance issues, one should analyze the problems in detail to determine their actual cause.
6. Is there any specific limit for the number of data volumes?
A maximum of 255 data volumes can be configured in the SAP MaxDB standard layout.
7. Is it recommended to create the SAP MaxDB volumes on raw devices or files?
- The volumes of the type "File" and of the type "Raw" can be defined on UNIX.
- A raw device is a hard disk or its any part which is not managed by the operating system. Data volumes of the type "raw device" can be configured for databases on UNIX.
- Since the administrative effort which is required for file systems does not apply and so the access to raw devices is generally faster.
- As the operating system does not have to check the consistency of the file system on raw devices, so it can usually boot faster.
- Because of the above mentioned advantages, it is recommended to use raw devices on UNIX platforms. However, volumes of the type "File" on UNIX are also supported.
- Volumes of the type "File" are the recommended standard in LINUX.
8. Where can you find the information about the configuration of SAP MaxDB volumes of the type "File"?
The performance of the database is greatly influenced by the speed with which the database system can read data from the data volumes and can write data to the volumes. For ensuring good performance while operating the database later, one should see 993848 (Direct I/O mount options for LiveCache/MaxDB) for information about creating and configuring volumes of the type "File".
9. Is it recommended to configure all data volumes in the same LUN?
It is recommended to distribute the data volumes across several LUNs. As per the various experiences, approximately 5 LUNs can be configured for each LUN.
10. How should the access authorizations for volumes be set?
Information is available in SDN at: wiki.sdn.sap.com/wiki/x/pYCdAw
11. Does the data get distributed evenly on all volumes, if a new data volume is added?
This mechanism can be activated using parameter EnableDataVolumeBalancing, as of MaxDB Version 7.7.06.09.
When the parameter EnableDataVolumeBalancing (deviating from the default) is set to value YES, all the data gets implicitly distributed evenly to all data volumes once a new data volume is either added or deleted.
An even distribution of data is triggered during the restart.
12. Is it preferred to keep the data volumes and log volumes on separate hard disks?
One should see 869267: FAQ: MaxDB LOG area.
13. How should the database parameter MAXCPU be set for DUAL core CPUs?
It is recommended to use the number of cores as a basis for calculating MAXCPU (as of Version 7.7., this is MaxCPUs), because the dual core CPUs have two cores with separate execution units (a separate L1 cache and sometimes even a separate L2 cache).
One can also see FAQ 936058: MaxDB Runtime Environment, for information about setting the database parameter MAXCPU (MaxCPUs).
14. Can additional CPUs be assigned to the database in live operation?
MaxDB Version 7.8 provides the option to use the parameter UseableCPUs to dynamically add additional CPUs to the database or to reduce the number of using CPUs. The parameter MaxCPUs continues to control the maximum number of CPUs to be used.
15. How large is it preferred to configure the IO buffer cache?
The database parameter CACHE_SIZE or (as of Version 7.7.03) the database parameter CacheMemorySize should be used to configure the IO buffer cache.
The converter cache and the data cache of an SAP MaxDB database are included in the IO buffer cache.
The database performance is greatly influenced by the size of the IO buffer cache. The larger the dimensions of the IO buffer cache, the fewer time-consuming hard disk accesses have to be executed.
The data volume that is to be processed in day-to-day business and the application determine the size of the IO buffer cache that is to be set.
It is generally recommended to process all the data in a system that is up and running, without accessing the hard disk which is in the data cache. However it is often not possible in the BI environment and OLTP environment.
It should be possible to hold all data that is to be processed in the IO buffer cache, while using the SAP liveCache technology. Generally, the results of the Quick Sizer should be used to configure the IO buffer cache for SAP liveCache technology.
The following applies to the IO buffer cache: the larger, the better (provided that sufficient physical memory is available).
It should be noted that a heap memory is also allocated by the database in addition to the IO buffer cache. The overall memory consumption of an SAP MaxDB database can be determined using the information from the system table MEMORYALLOKATORSTATISTICS.
The number of successful and failed accesses to the data cache determines the data cache hit ratio. The hit ratio indicates whether the size of the data cache is well configured. However, if exceptional applications are running at the time of the analysis then the data cache hit ratio does not provide sufficient information. For example, during year-end closing the hit ratio may deteriorate because this data does not have to be held in the cache permanently. After directly restarting the database, it is not indicated by the data cache hit ratio that whether the system is well configured, because all data must first be loaded into the cache.
The settings for the size of the IO buffer cache which has been tried and tested by many OLTP customers and BW customers for SAP systems are as follows:
OLTP NON-UNICODE: 1% of the data volume
OLTP UNICODE: 2% of the data volume
BW NON-UNICODE: 2% of the data volume
BW UNICODE: 4% of the data volume
It is important to analyze the performance problems in detail that are reported with a poor hit ratio in the Database Analyzer. It has been seen very often that increasing the size of the data cache does not solve the problem. The command monitor (for example) should be used in the analysis to determine access strategies that may be insufficient, and that can be corrected by an additional index or by changing the application.
16. Where is the information about SAP MaxDB database parameters found?
One can find information about the SAP MaxDB database parameters in 1139904: FAQ: MaxDB/liveCache kernel parameters.
17. Where is the information about SAP MaxDB and storage systems found?
One can see 912905 FAQ: Storage systems used with MaxDB, to find information about SAP MaxDB and storage systems.