Methods for optimizing database workloads can vary, depending on the type of database and the purpose of the workload. For online transaction processing (OLTP) workloads, for example, there are thousands of I/Os per second (IOPS). We describe ways to optimize response time to the user for this type of workload. From the Business Analytics (BA) workload perspective, sometimes you might need to run multiple, sometimes complex, queries that can take a long time, even hours. For this type of workload, optimized I/O throughput is the goal.
IT departments cannot acquire the hardware and software to meet all needs for all projects. To address this requirement, select the equipment that meets as many project needs as possible, realizing that there are trade-offs. For database workloads, a delicate balance exists between CPU processing power and I/O throughput, so the trade-off can be considerable.
When analyzing the difference in OLTP and BA workloads, you must realize that it is impossible to have the best processing power and the highest IOPS from the same hardware and software. The tuning for these workloads differs. To compare, one component of OLTP workloads is database locking. Efficient locking is required because updates are going against the data, and inefficient locking degrades as the number of users grows.
In contrast, for BA, query optimization becomes much more important. Tools, such as Materialized Query Tables and Multidimensional Clustering Tables, help to speed up complex queries where the data might not change (is precomputed) but tend to be larger (just like the data in BA). Another factor, which becomes more prevalent, is query monitoring and management. Proper monitoring ensures that queries use only a certain amount of resources before either being stopped or moved down to a lower priority to limit the impact to the system. Additional effort is spent on both the denormalization and partitioning, to try and limit I/O effects.
This IBM® Redpaper™ publication describes IBM products that address requirements specific to OLTP and BA workloads. These products are the result of experience with clients and IBM Business Partners over many years. They convey the importance of database workload efficiency in multiple workload environments.
This paper details the features of IBM eX5 servers. IBM designed these servers to increase efficiency and lower operating costs, therefore, maximizing workload optimization for OLTP and BA workloads. Additionally, this paper describes other solutions that work in concert with the eX5 family of servers. This paper is based on in-house studies conducted by the IBM Strategy and Testing Laboratory (STL). For these studies, production-quality IBM System x3690 X5 servers were used.
This publication is directed to IT professionals and decision-makers, such as CEOs, CIOs, CFOs, IBM clients, Business Partners, information architects, business intelligence administrators, and database administrators.
Table of contents