Database sizing for LQE Relational Store

Authors: TWikiUser, TWikiUser
Build basis: Products, editions, or versions that apply to the content. If no build basis applies to this content, set the build basis to None.

UNDER CONSTRUCTION! Do not use for sizing!

Introduction

This page provides guidance for sizing database servers that host the Lifecycle Query Engine - Relational store (LQE-RS) application. LQE-RS is an implementation of the Lifecycle Query Engine that replaces Jena with a relational database. This improves report scalability while reducing LQE resource requirements.

Standard disclaimer

The information in this document is distributed AS IS. The use of this information or the implementation of any of these techniques is a customer responsibility and depends on the customer’s ability to evaluate and integrate them into the customer’s operational environment. While each item may have been reviewed by IBM for accuracy in a specific situation, there is no guarantee that the same or similar results will be obtained elsewhere. Customers attempting to adapt these techniques to their own environments do so at their own risk. Any pointers in this publication to external Web sites are provided for convenience only and do not in any manner serve as an endorsement of these Web sites. Any performance data contained in this document was determined in a controlled environment, and therefore, the results that may be obtained in other operating environments may vary significantly. Users of this document should verify the applicable data for their specific environment.

Performance is based on measurements and projections using standard IBM benchmarks in a controlled environment. The actual throughput or performance that any user will experience will vary depending upon many factors, including considerations such as the amount of multi-programming in the user’s job stream, the I/O configuration, the storage configuration, and the workload processed. Therefore, no assurance can be given that an individual user will achieve results similar to those stated here.

This testing was done as a way to compare and characterize the differences in performance between different versions of the product. The results shown here should thus be looked at as a comparison of the contrasting performance between different versions, and not as an absolute benchmark of performance.

Summary of results

We ran a series of tests to determine how much database memory was optimal for our performance workload. Based on these tests, we recommend setting your buffer cache or buffer pool to 10% of the disk size of your LQE RS database. Then, monitor the I/O on your database and adjust that number up or down based on your own reporting workload.

Background

The Jazz Reporting Service is the default reporting option for the IBM® Engineering solutions. You can quickly and easily consolidate data from a variety of sources across your tools and project areas. It consists of three components: Report Builder, Data Collection Component, and Lifecycle Query Engine.

The Lifecycle Query Engine (LQE) implements a Linked Lifecycle Data Index over data provided by one or more lifecycle tools. A lifecycle tool makes its data available for indexing by exposing its Linked Lifecycle Data via a Tracked Resource Set. With this index, you can build sophisticated traceability reports to understand relationships between your data (for example, find all requirements that are associated with failing test cases).

When LQE was introduced, it stored index data using Apache Jena TDB. Reports were implemented using SPARQL queries. The architecture is shown below:

This architecture has several disadvantages for large indexes:

• The TDB index must be local to the LQE server machine, which prevents addressing scale issues through clustering
• Large Jena indexes must be almost entirely cached in memory for performance reasons, so that LQE servers require large amounts of RAM (as well as large Java heaps)
• Jena has issues in dealing with concurrent load. A single slow report could cause LQE to become unresponsive.
• Jena does not have a sophisticated query optimization engine, so complex traceability reports could be slow.

Because of these disadvantages, Jena is being replaced with a relational database (see below). LQE continues to access application data via TRS feeds, but the results of indexing are stored on a relational database server. Report Builder executes reports by running SQL against the relational store.

This improves the scalability of the LQE solution in several ways.

• Clustering becomes possible through standard database solutions like Oracle RAC
• Database optimization engines are more sophisticated than Jena, allowing for improved query execution
• Relational databases are designed for high data volumes and high concurrency. One slow query will not disrupt reporting.
• Since query processing shifts from the LQE server to the database server, the LQE server requires less RAM.

It is also possible to deploy the two LQE architectures side-by-side, to reduce risk during the transition to the new architecture. A Report Builder can be configured to run both Jena and SQL queries, and even to compare the queries for correctness and performance.

Test results

The goal of these tests was to determine how much database memory should be allocated to the database caches, specifically:

• The buffer cache on Oracle
• The buffer pool on DB2

When a database executes a SQL statement, it will read table and index data from disk into these caches. Performance degrades if the database doesn't have enough of the data it needs in memory, since reading from disk is significantly slower than reading from memory. The amount of memory required depends on what the reports are doing, how big the index is, and how many reports are running concurrently. These tradeoffs are discussed below, but in order to estimate memory requirements, we used an LQE index of fixed size and executed a load test against the index. We then monitored I/O on the database server for a range of cache sizes. When caches are undersized, we would expect to see high physical I/O. Increasing the cache size should then reduce I/O, and we can determine the optimum cache size by charting I/O vs. cache size and looking for a point of diminishing returns.

For Oracle, we monitored the read IO requests per second, using an AWR snapshot covering the period of the load test. When this number is high, Oracle is not finding data in the buffer cache and therefore making requests out to disk to read in the data.

On DB2, we monitored the disk utilization on the operating system. DB2 allows us to disable file system caching, so we don't have to worry about the Linux page cache influencing the I/O numbers. When the disk utilization is high, DB2 is not finding data in the buffer pool and therefore making requests out to disk.

Test results for Oracle

TBD

Test results for DB2

TBD

#Factors

Factors impacting database sizing

TBD

Test environment and data

In these tests, we use a single test deployment of the Engineering Lifecycle management applications, and index that deploying using LQE RS (both DB2 and Oracle), as well as indexing with the Jena-based LQE. This allows for comparison of reports (both performance and content) across the 3 LQEs. The deployment topology is shown below:

The servers in the topology are all physical servers. The hardware specifications are listed below.

Role	Server	Machine type	Processor	Total processors	Memory	Storage	OS and version
Proxy Server	IBM HTTP Server and WebSphere Plugin	IBM System x3550 M3	2 x Intel Xeon X5667 3.07 GHz (quad-core)	16	16 GB	RAID 5 – 279GB SAS Disk x 2	RHEL 7
DNG	WebSphere Liberty	IBM System x3550 M4	2 x Intel Xeon E5-2640 2.5GHz (six-core)	24	64 GB	RAID 5 – 279GB SAS Disk x 4	RHEL 7
JTS/GC/LDX	WebSphere Liberty	IBM System x3550 M4	2 x Intel Xeon E5-2640 2.5GHz (six-core)	24	32 GB	RAID 5 – 279GB SAS Disk x 4	RHEL 7
ETM	WebSphere Liberty	IBM System x3550 M4	2 x Intel Xeon E5-2640 2.5GHz (six-core)	24	32 GB	RAID 5 – 279GB SAS Disk x 4	RHEL 7
EWM	WebSphere Liberty	IBM System x3550 M4	2 x Intel Xeon E5-2640 2.5GHz (six-core)	24	32 GB	RAID 5 – 279GB SAS Disk x 4	RHEL 7
LQE RS Oracle/Jena	Oracle 19c	IBM System SR650	2 x Xeon Silver 4114 10C 2.2GHz (ten-core)	40	768 GB	RAID 10 – 900GB SAS Disk x 16	RHEL 7
LQE RS DB2	DB2 11.5	IBM System SR650	2 x Xeon Silver 4114 10C 2.2GHz (ten-core)	40	768 GB	RAID 10 – 900GB SAS Disk x 16	RHEL 7
Report Builder - DB2	WebSphere Liberty	IBM System x3550 M4	2 x Intel Xeon E5-2640 2.5GHz (six-core)	24	64 GB	RAID 5 – 279GB SAS Disk x 4	RHEL 7
Report Builder - Oracle	WebSphere Liberty	IBM System x3550 M4	2 x Intel Xeon E5-2640 2.5GHz (six-core)	24	64 GB	RAID 5 – 279GB SAS Disk x 4	RHEL 7

Abbreviations:

• JTS = Jazz Team Server
• GC = Global configuration application
• LDX = Link index provider
• DNG = DOORS Next Generation
• EWM = Engineering Workflow Management
• ETM = Engineering Test Management
• LQE RS = Lifecycle Query Engine - Relational Store
• RHEL = Red hat Enterprise Linux

Test data

The test deployment includes the following kinds of artifacts:

• DOORS Next: 10 million requirements and 60 million links. 5 million links have link validity.
• EWM: 2 million work items
• ETM: 5 million total test artifacts
• Global configurations: 1000 total components

When indexed, the LQE database sizes are:

• Jena LQE: 1320 G
• LQE RS Oracle: 698G
• LQE RS DB2: 665G

Related topics: Deployment web home, Deployment web home

External links:

• IBM

Additional contributors: TWikiUser, TWikiUser