Optimizing database load times
A lot has been said and done about SAP performance. To compare different types of server hardware, the SAP Standard Application Benchmark has been introduced more than 15 years ago. But this benchmark has a problem: it does not tell you which setting affects performance to what degree.
Working as an on-site engineer in the SAP LinuxLab, I have the unique possibility to run the SLCS, the SAP LinuxLab Certification Suite, several times and compare the effect that specific settings do to performance. The SLCS is used to SAP-certify hardware for Linux. It has several parts, and I want to concentrate on the install part here. It installs an ERP 2005 SR 1 for you. The output you get is the time the system needed for
- the database creation
- the database load
- the database initialization
I want to focus on the database load. In this series of articles, I will run the database load several times on the same machine, changing some well-identified settings and observe the impact on performane.
There are several questions I want to clarify:
- how is the impact on performance if you have caching switched on or not?
- how is the impact on performance if you are using raw devices vs. files?
- how can the distribution of data on the harddisks optimize performance?
I ran a database load two times on the same machine, once with disk cache enabled and once without. The results were:
- without caching: 5173 seconds for an ERP 2005 SR 1 database load
- with caching: 4530 seconds for an ERP 2005 SR 1 database load
This comes to a difference of 12.4%.
Looking at this result, it comes clear that caching in real-world is not a performance booster like using multi-core technology. But it is worth taking a look on your system management software to see if disk caching is turned on. For example, did you know that caching can be turned off accidentially just because the battery of your RAID controller is not fully charged? So, having a look here pays!