Recently IBM published various best practices papers about DB2 for Linux, UNIX, and Windows. Some of them are also of interest for everyone running SAP on DB2. One of the papers is outstanding as it answers a question which arises very often when installing a new SAP system: What about the database layout?
Best Practices Database Storage
The mapping of data to physical disk subsystems is an important performance factor in every SAP installation. The overall goal of this mapping is it to avoid I/O bottlenecks (or ‘hot-spots’) in the system. The paper “Best practices Database Storage” offers very useful hints and guidelines on how to achieve exactly this and puts them into context. Here are some examples:
- Use 15-20 dedicated spindles per CPU core
- Stripe at most in two places.
- Separate DB2 transaction logs and data
- Use file systems instead of raw devices – create one file system per LUN
- Use RAID-10 for transaction logs, RAID-10 or RAID-5 for data LUNS
I know that if it comes to database layout every recommendation you give will be debatable. I got through lots of discussions about using raw devices or not or where to stripe, and the participants sometimes stand up for their arguments in a ferociously manner. Here, however, reasons for these best practices are presented. Even if you do not agree with every guideline – the paper serves as an excellent basis for further discussion. And, by the way, the sentence I like most in this paper is ‘Simplicity is the key to ensuring good database storage design.’
From the SAP perspective there is one guideline which is arguable:
- Set EXTENTSIZE to RAID stripe size
As you may know, SAP installations do not follow this advice per default. Instead, SAPinst sets the extent size of every tablespace to a fixed value of 2 pages. On a system with the default page size of 16K this translates to 32K extents. Here are the reasons why we think that this is an appropriate setting for SAP systems:
- The database schema of a SAP system contains several thousand tables, and usually a lot of these tables (depending on the application usage) are empty. On the other hand DB2 allocates 2 extents for every table and 1 extent for every index at creation time. Therefore, increasing the extent size leads to substantially higher disk space consumption.
- MDC tables are used in SAP BI installations. Pages of MDC tables are grouped into extent-sized blocks. To avoid sparsely filled MDC blocks for MDC dimensions with a higher cardinality it is reasonable to keep the extent size small.
The extent size is set during the creation of the tablespaces and cannot be changed afterwards. If you have good reasons to install your SAP system with an extent size different from 2, SAPinst offers a so called exit step before it creates the tablespaces. This way you can temporarily stop the SAP installation and create the tablespaces manually. To do this you can simply edit and execute the SQL script which SAPinst generated for your installation. The script is called ‘createTablespaces.sql’ and is located in the SAPinst installation directory.
All in all I would highly recommend to every DB2 administrator to read the paper “Best Practices Database Storage”.
Other best practices papers of interest
While in general very well written, not all of these papers are of relevance for SAP systems. Here is a list of the most interesting papers for SAP administrators:
- ‘Minimizing Planned Outages’ gives recommendations on how to minimize or avoid situations in which the database is not available. It discusses methods for improving the availability of a system during table and index maintenance, data loading, performance tuning, storage management, backups and database upgrades.
- ‘Improving Data Server Utilization and Management through Virtualization’ gives best practices for running DB2 with the IBM System p virtualization technology in a logical partition (LPAR).
- ‘Tuning and Monitoring Database System Performance’ gives configuration and monitoring advices and discusses performance tuning and how to deal with performance problems. Regarding configuration, there is a small chapter for SAP environments included. While the paper focuses on command line monitoring, SAP users usually prefer the SAP transaction DBACOCKPIT. Performance troubleshooting is presented in a very structured approach.
- ‘Writing and Tuning Queries for Optimal Performance’ is primarily useful for application developers and explains – as the title suggest – how SQL statements should be written with regard to best performance. It contains some recommendations for SAP systems (especially SAP BI), but SAP’s OpenSQL in particular is not covered.
Again, you can find the complete list of the DB2 for Linux, UNIX, and Windows best practices papers here.