DB2 9 XML performance characteristics (1)

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-test the XML Implementation of the Financial industry that contains data that complies with FIXML, and FIXML is the Financial Information eXchangeFIX) standard.
Remember, XML applications are roughly divided into the following two types:

High data volume and small documentation for data. This test is for this situation)
Variable data volume for documents, large documents)
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 the disk system AIX 5.3 and TotalStorage DS8100.

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, pureXML is significantly different from 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 shown in Figure 1 ):

Customer: a Customer can have one or more accounts ).
Account: each Account contains one or more holding objects ).
Holding: Number of securities.
Security: the identifier of a holding object, for example, the stock name ).
Order: an Order for an account to buy or sell a securities.

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 format for trading-related messages, such as purchase and sale orders) www.fixprotocol.org ). The order document size is 1 kb to 2 kb. Order documents have many attributes, and the proportion of data nodes is very high.
20833 securities documents) use 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, which 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.
Install a multi-path subsystem Device Driver SDD ). 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, connected 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 % = 5
Vmo-o maxclient % = 15
Vmo-o maxperm % = 15

In addition, to prevent the input file from being cached during data loading, use the-o cio option in the mounting command, 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 the previous ESS's use of the SSA loop, the DS8100 disk interconnect is a Switched Fiber Channel Arbitrated LoopFC-AL), which provides faster data access and high availability. DS8100 is configured with 128 disks and 16 volumes are created on these disks. Among them, 8 volumes and 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 and 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 type of running workloads, such as insert-only, query-only, or hybrid workloads.

Table 2. Database Configuration and Self-Tuning

DB Configuration Parameter Name	Initial settings
SELF_TUNING_MEM	ON default)
DATABASE_MEMORY	Default Value of AUTOMATIC)
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

Workload

Three XML workloads are designed, executed, and measured:

Insert write only)
Read-Only query)
Hybrid read-write)
These workloads are highly concurrent. The workload is executed by a Java driver, which generates one to n concurrent threads. Each thread simulates a user. The user connects to the database and submits a transaction stream, regardless of the number of times. Each transaction stream is a series of transactions randomly selected from a series of transaction templates in a weighted manner. Each transaction is assigned a weight, which determines the percentage of the transaction in the workload. During running, the parameter flag in the transaction is replaced with specific values that are extracted from the Configurable random value distribution and input list.

Insert workload: Write-only

Insert workload to fill the database with approximately 100 GB of raw XML data:

6 million CustAcc documents
30 million orders
20833 types of securities
First, 83 concurrent users insert all securities. Then, insert CustAcc and order documents in stages to verify that insertion performance is scalable. 100 concurrent users are used in each stage, as shown in table 4.

Table 4. Phased database Filling

Phase	Number of CustAcc documents in the database	Number of order documents in the database
1	100,000	500,000
2.1	200,000	1,000,000
2.2	300,000	1,500,000
2.3	400,000	2,000,000
2.4	500,000	2,500,000
2.5	600,000	3,000,000
3.1	1,000,000	5,000,000
3.2	1,500,000	7,500,000
3.3	2,000,000	10,000,000
4.1	2,500,000	12,500,000
4.2	3,000,000	15,000,000
4.3	3,500,000	17,500,000
4.4	4,000,000	20,000,000
5.1	4,500,000	22,500,000
5.2	5,000,000	25,000,000
5.3	5,500,000	27,500,000
5.4	6,000,000	30,000,000