Symptom
This note is a collection of questions that are often asked by colleagues and customers regarding the SAP MaxDB configuration. It provides answers and refers you to other information sources.
The note is in no way complete.
1. Is there a size limit for an SAP MaxDB database?
2. Are there limits for the number of simultaneous sessions of an SAP MaxDB database?
3. Why should the SAP MaxDB run on a 64-bit platform?
4. How large should I configure the data volumes of an SAP MaxDB database?
5. Must I change a data volume configuration that does not correspond to the recommendations in this note?
6. Is there a limit for the number of data volumes?
7. Should I create the SAP MaxDB volumes on raw devices or files?
8. Where do I find information about the configuration of SAP MaxDB volumes of the type "File"?
9. Is it advisable to configure all data volumes in the same LUN?
10. How should I set the access authorizations for volumes?
11. If a new data volume is added, is the data distributed evenly on all volumes?
12. Should data volumes and log volumes be on separate hard disks?
13. How should I set the database parameter MAXCPU for DUAL core CPUs?
14. Can I assign additional CPUs to the database in live operation?
15. How large should I configure the IO buffer cache?
16. Where do I find information about SAP MaxDB database parameters?
17. Where do I find information about SAP MaxDB and storage systems?
Reason and Prerequisites
You want to use an SAP MaxDB as of Version 7.6.
You have questions about the SAP MaxDB configuration.
Note 719652 contains further information about the SAP liveCache configuration.
Further FAQ notes about SAP MaxDB/liveCache are available in the SAP Developer Network (SDN) at:
https://wiki.sdn.sap.com/wiki/x/GkM
Solution
1. Is there a size limit for an SAP MaxDB database?
In the SAP MaxDB standard layout (parameter: VOLUMENO_BIT_COUNT or ConverterVolumeIDLayout= 8), you can configure a maximum of 255 data volumes. A data volume can have a maximum size of 128 GB.
The maximum total size of the data volumes is 32 TB.
The log area can use a maximum of 32 TB.
More information about this is available in the SAP MaxDB documentation on the maxdb.sap.com page:
http://maxdb.sap.com
-> Documentation -> Open the SAP MaxDB <version> Library -> Glossary
under the keyword 'Volume' or 'Data Volume'.
2. Are there limits for the number of simultaneous sessions of an SAP MaxDB database?
No, there are no limits. To configure the number of database sessions that can be logged on to the database simultaneously, use the database parameter MaxUserTasks.
OLTP:
You should configure the number of database users in OLTP systems to at least 2 x <number_SAP processes > + 4.
BW:
You should configure the number of database users in OLTP systems to at least 3 x <number_SAP processes > + 4.
Java applications:
In the connection pool, the maximum number of connections to the database are determined for each J2EE instance (NW 7.1 is the default 70).
The number of parallel user sessions (MaxDB parameter: MaxUserTasks) is calculated from the sum of connections (connection pool) of all J2EE instances + 4.
liveCache:
For liveCaches in SCM system 4.1 and lower, the value for the database parameter MaxUserTasks is calculated according to the formula
2 x <number_SAP processes > + 4
For liveCaches in SCM system 5.0 and above, the following formula applies
3 x <number_SAP processes > + 4
Also refer to Note 757914.
3. Why should the MaxDB run on a 64-bit platform?
For more information about this, refer to Note 1013441: Advantages for MaxDB on 64-bit platforms.
4. How large should I configure the data volumes of an SAP MaxDB database?
The optimum use of the I/O system is critical for I/O performance. Therefore, it is useful to evenly distribute the volumes across the available I/O channels.
The number of data volumes affects the parallelism of the I/O.
* Windows:
On Windows, the asynchronous I/O of the operating system is used.
* UNIX:
On UNIX, the parallelism, with which the SAP MaxDB/liveCache database transfers the I/O requests to the operating system, is determined by the number of configured I/O threads.
o SAP MaxDB version lower than Version 7.7
The number of I/O threads results from the number of volumes * number of I/O threads for each volume (_IOPROCS_PER_DEV).
o SAP MaxDB Version 7.7 or higher
The number of I/O threads results from volumes * (total of low/med/high queues for each volume), but can also be limited by the database parameter MaxIOPoolWorkers.
A number of threads that was configured too high increases the number of threads. As a result, you may reach the limits of the operating system resources.
We recommend that you use the following calculation formula for determining the size of SAP MaxDB data volumes: 'square root of the system size in GB, rounded up'
Examples:
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 best if all of the data volumes have the same size.
5. Must I change a data volume configuration that does not correspond to the recommendations in this note?
No. You should not change existing configurations. If serious I/O performance problems occur, you must analyze these problems in detail to determine the actual cause.
6. Is there a limit for the number of data volumes?
In the SAP MaxDB standard layout, you can configure a maximum of 255 data volumes.
7. Should I create the SAP MaxDB volumes on raw devices or files?
On UNIX, you can define volumes of the type "File" and of the type "Raw".
A raw device is a hard disk or part of a hard disk that is not managed by the operating system. On UNIX, you can configure data volumes of the type "raw device" for databases.
The access to raw devices is generally faster because the administrative effort that is required for file systems does not apply.
In addition, the operating system can usually boot faster because it does not have to check the consistency of the file system on raw devices.
Due to these advantages, we recommend that you use raw devices on UNIX platforms. However, we also support volumes of the type "File" on UNIX.
In Linux, volumes of the type "File" are the recommended standard.
8. Where do I find information about the configuration of SAP MaxDB volumes of the type "File"?
The speed with which the database system can read data from the data volumes and can write data to the volumes, greatly influences the performance of the database. To ensure good performance when you operate the database later, see Note 993848 (Direct I/O mount options for LiveCache/MaxDB) for information about creating and configuring volumes of the type "File".
9. Is it advisable to configure all data volumes in the same LUN?
It is advisable to distribute the data volumes across several LUNs. From experience, approximately 5 LUNs are configured for each LUN.
10. How should I set the access authorizations for volumes?
Information is available in SDN at:
https://wiki.sdn.sap.com/wiki/x/pYCdAw
11. If a new data volume is added, is the data distributed evenly on all volumes?
As of MaxDB Version 7.7.06.09, such a mechanism can be activated using parameter EnableDataVolumeBalancing.
If the parameter EnableDataVolumeBalancing (deviating from the default) is set to value YES, all data is implicitly distributed evenly to all data volumes after you add a new data volume or delete a data volume.
During restart, an even distribution of data is triggered.
12. Should data volumes and log volumes be on separate hard disks?
See Note 869267: FAQ: MaxDB LOG area.
13. How should I set the database parameter MAXCPU for DUAL core CPUs?
Since dual core CPUs actually have two cores with separate execution units (a separate L1 cache and sometimes even a separate L2 cache), you should use the number of cores as a basis when you calculate MAXCPU (as of Version 7.7., this is MaxCPUs).
For information about setting the database parameter MAXCPU (MaxCPUs), also see FAQ Note 936058: MaxDB Runtime Environment.
14. Can I assign additional CPUs to the database in live operation?
As of MaxDB Version 7.8 you have the option to use the parameter UseableCPUs to dynamically add additional CPUs to the database or to reduce the number of CPUs to be used. The maximum number of CPUs to be used continues to be controlled by the parameter MaxCPUs.
(PTS: 1147916)
15. How large should I configure the IO buffer cache?
To configure the IO buffer cache, use the database parameter CACHE_SIZE or (as of Version 7.7.03) use the database parameter CacheMemorySize.
The IO buffer cache includes the converter cache and the data cache of an SAP MaxDB database.
The size of the IO buffer cache generally has the greatest influence on database performance. The larger the dimensions of the IO buffer cache, the fewer time-consuming hard disk accesses have to be executed.
The size of the IO buffer cache that is to be set strongly depends on the application and on the data volume that is to be processed in day-to-day business.
In a system that is up and running, it is best if all data can be processed without hard disk accesses, that is, in the data cache. However, this is generally not possible in the OLTP environment and in the BI environment.
When using the SAP liveCache technology, it should be possible to hold all data that is to be processed in the IO buffer cache. Generally, you must use the results of the Quick Sizer to configure the IO buffer cache for SAP liveCache technology. Further information about the Quick Sizer is available on SAP Service Marketplace: http://service.sap.com/quicksizing
The following applies to the IO buffer cache: the larger, the better (provided that sufficient physical memory is available).
Note that the database also allocates heap memory in addition to the IO buffer cache. You can determine the overall memory consumption of an SAP MaxDB database using the information from the system table MEMORYALLOKATORSTATISTICS. For more information about this, see Note 1128916: MaxDB/liveCache heap management.
The data cache hit ratio is determined from the number of successful and failed accesses to the data cache. This hit ratio indicates whether the size of the data cache is well configured. The data cache hit ratio does not provide sufficient information if exceptional applications were running at the time of the analysis. During year-end closing (for example), the hit ratio may deteriorate because this data does not have to be held in the cache permanently. Directly after restarting the database, the data cache hit ratio does not indicate whether the system is well configured either because all data must first be loaded into the cache.
For SAP systems, the following setting for the size of the IO buffer cache has been tried and tested by many OLTP customers and BW customers:
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
Comment:
Performance problems that are reported with a poor hit ratio in the Database Analyzer must be analyzed in detail. In most cases, increasing the size of the data cache does not solve the problem. In the analysis, you must use the command monitor (for example) to determine access strategies that may be insufficient, and that can be corrected by an additional index or by changing the application.
16. Where do I find information about SAP MaxDB database parameters?
Information about the SAP MaxDB database parameters is available in Note 1139904: FAQ: MaxDB/liveCache kernel parameters.
17. Where do I find information about SAP MaxDB and storage systems?
See Note 912905 FAQ: Storage systems used with MaxDB.
Additional information and recommendations for the configuration are available in SDN in the Performance area. Use the following link:
https://wiki.sdn.sap.com/wiki/x/WGY