DB2 9 XML performance features, advantages and evaluation, performance optimization policies

Introduction

Now that DB2 9 has been released, it is time to update pureXML, one of its latest features.®To test the driver. Therefore, a simulated brokerage business environment is established. This environment has the following features:

• High transaction volume and concurrency
• Small transaction size
• A large number of small XML documents
• Variable XML document structure-tests that contain FIXML-compliant data, and FIXML is the Financial Information eXchange (FIX) standard Financial industry XML implementation.

Remember, XML applications are roughly divided into the following two types:

• Data-oriented (high data volume, small documentation, this test is for this situation)
• Document-oriented (variable data size, large Documentation)

In addition, database applications involving XML are also various, including the following situations:

• Publish relational data in XML format
• Use XML full text search for content and document management
• Merge different data sources
• Form Processing
• Backend support for Web services and service-oriented Architecture (SOA)
• Message-based transaction processing and XML-based online transaction processing (OLTP), especially in the financial industry

This article performs performance measurement in an XML-based transaction processing scenario, which simulates a data-oriented financial application. The test device includes the latest POWER5 server (p5 560Q) and AIX 5.3 and TotalStorage DS8100 disk systems.

DB2 9 and XML

The new XML in DB2 9 supports pure XML storage, XML indexing, XQuery, SQL/XML, and advanced XML Schema processing. "Pure" means to store and process XML documents in the form of annotation trees, which is different from any previous technology in commercial relational databases. In particular, there is a significant difference between pureXML and the technology of storing XML as a large object (BLOB or CLOB) or decomposing XML into a relational table. For more information, see the previous articles "What's new in DB2 Viper" (developerWorks, February 2006) and "Native XML Support in DB2 Universal Database ".

 

Test scenario: Online Brokerage

This test scenario models the online brokerage business. We once helped financial companies adopt XML. These experiences Help us understand their data and processing features. This scenario is intentionally simplified, but it is representative in terms of document, transaction, and XML schema.

The main logical data entities in this scenario are as follows (see figure 1 ):

• Customer:One customer can have one or moreAccount).
• Account:Each account contains one or moreHolding).
• Holding:ASecurities.
• Security:The identifier of a holding object (for example, the stock name ).
• Order:ForAccountBuying and sellingSecurities.

Figure 1. Data entity and XML Schema

Document Processing and size vary with document types:

• Each customer has a CustAcc document that contains all the customer information, account information, and owner information of the customer. The size of CustAcc is between 4 kb and 20 kb.
• Use FIXML 4.4 to represent an order. FIXML is an industry standard XML model (www.fixprotocol.org) used to trade related messages (such as sales orders ). The order document size is 1 kb to 2 kb. Order documents have many attributes, and the proportion of data nodes is very high.
• The securities document (20833) uses actual securities symbols and names to indicate the majority of stocks and mutual funds traded in the United States. They are between 3 kb and 10 KB.

Use the Toxgene data generator to generate instance documents for these three modes. For more information about Toxgene Data generators, see ToXgene-the ToX XML Data Generator.

 

Test devices and configurations

The test runs on the following devices:

• Processor:IBM System p5 560Q uses 8 processor logical partitions (LPAR), which is a medium IBM System p5 560Q. 8 processors run at 1.5 GHz.
• Memory:32 GB
• Operating System:AIX 5L v5.3 TL04 (system type: 9116-561, two 4-chip modules)
  • Concurrent multithreading provides 16 concurrent execution threads or logic processors.
  • A multi-path subsystem Device Driver (SDD) is installed ). This feature improves access to storage servers, such as improving data availability and dynamic I/O Load Balancing across fiber channel adapters on storage servers.
• Storage:IBM TotalStorage DS8100 connects to LPAR through four fiber channel adapters.

AIX Configuration

During the installation of DB2, all required operating system parameters are automatically adjusted. The following virtual memory management parameters are set to better control the memory used by the file system cache:

vmo -o minperm%=5vmo -o maxclient%=15vmo -o maxperm%=15

In addition, to prevent the input files from being cached during data loading-o cioOption to mount the file system containing the original XML input file with the concurrent I/O FEATURES OF THE JFS2 file system.

Storage Configuration

Use the standard default configuration of TotalStorage DS8100. The DS8100 is basically a POWER5 eServer p5 570 internally. Unlike earlier ESS's use of the SSA Loop, DS8100 disk interconnect is a Switched Fiber Channel Arbitrated Loop (FC-AL) that provides faster data access and high availability. DS8100 is configured with 128 disks and 16 volumes are created on these disks. Eight volumes (64 disks) are allocated to this LPAR. Use 6 + Parity + Spare to set the size of the four volumes to 388 GB. For the other four volumes, set 7 + Parity to 452 GB. Creates a volume group (VG) that spans all 8 volumes ). This volume group defines all the storage components of the DB2 database, including tablespaces, logs, and backups. Table 1 summarizes the configurations.

Table 1. Storage Configuration

Aspect	Configuration
Processor	Two processors, each with pSeries POWER5 1.9 GHz two-way CEC
Memory (cache)	32 GB
Disk Interconnection	Switched FC-AL
Disk quantity	128 (only 64 are used by the host LPAR)
Disk size/Speed	73 GB, 15000 RPM

DB2 Configuration

DB2 9 contains many new features, including new autonomous self-tuning features. In this test, several autonomous functions are used, including:

• Automatic Storage Management
• Self-tuning Memory Management

Because the self-tuning Memory Manager (STMM) of DB2 is started, it continuously adjusts the settings of a series of DB2 configuration parameters. Key DB2 configuration parameters managed and adjusted by STMM during the test run are shown in table 2. It is important to be aware that STMM will modify these values independently based on the running workload type (such as pure insert, pure query, or hybrid workload.

Table 2. Database Configuration and Self-Tuning

DB Configuration Parameter Name	Initial settings
SELF_TUNING_MEM	ON (default)
DATABASE_MEMORY	AUTOMATIC (default)
SORTHEAP	156
SHEAPTHRES_SHR	10000
LOCKLIST	53000
MAXLOCKS	80
PCKCACHESZ	27000
Buffer Pool name	Initial settings
Ibmdefabp BP	1100000
CATBP	4000
TEMPBP	1000

DBA only needs to execute a few database configuration tasks, as shown in table 3.

Table 3. Database Configuration, manual

Aspect	Configuration/settings
Database	Unicode. All tablespaces are automatically stored. DB2 log on a separate tape
Memory	Enable STMM for all tests
Page size	16 K (tablespace and buffer pool)
Tables and Indexes	Three tables: CustAcc, order, and security. 24 XML indexes: 10 on CustAcc, 5 on order, and 9 on security
Tablespace	There are 6 tablespaces in total: each of the three tables has one tablespace, and each table index has one tablespace. Disable file system cache for all tablespaces
Buffer Pool	A total of three buffer pools: default buffer pool, buffer pool used for cataloguing tablespace, and buffer pool used for temporary tablespace