Performance case study using Zend Core for IBM® with IBM DB2® 9 to service 10,000 active database connections
[Nailah Bissoon, John Tran, Sunil Kamath] [IBM DB2 Database Solutions Development]
PHP (recursive acronym for PHP: Hypertext Preprocessor) is the market-leading dynamic language for producing modern Web applications. Its popularity for building content management and e-commerce systems stems from its portability, ease of use, and wide support for accessing database and enterprise data sources. IBM and Zend Technologies have partnered to deliver Zend Core for IBM®, which is the industry’s only certified and fully supported PHP development and production environment for IBM data servers.
The PHP application used for this study is a DVD store application that simulates users logging into an online catalog, browsing the catalog, and making DVD purchases. The entire system configuration is a three-tier model with tier one executing the driver that emulates the activities of Web users. Tier two comprises multiple Web application servers that intercept the requests and sends database transactions to a DB2 9 data server, configured on a separate machine.
The solution presented in this paper demonstrates how Zend Core for IBM with IBM DB2® 9 performs, scales, and offers the best total cost of ownership. This is showcased by scaling-out Web servers as users to the online store increase. The single DB2 data server manages this without requiring the addition of CPU and memory using its unique connection concentrator feature, which manages and balances the workload on the data server. This solution is able to efficiently service tens of thousands of simultaneous connections to the database using this powerful feature.
Zend Core for IBM
Zend Core for IBM  delivers a rapid development and production PHP foundation for applications using PHP with IBM data servers. The product includes tight integration with the entire IBM data server family including DB2 9 and the previous version of DB2 Universal Database™, Cloudscape™, and IDS (Informix® Dynamic Server). Native support for XML and Web services are also included. Zend Core for IBM is bundled with all of the software required for Web servers to communicate with the IBM data servers. It is easy to install, and includes Apache 2, DB2 Express, DB2 Runtime Client, Cloudscape, PHP 5 and PHP extensions such as ibm_db2. The partnership between Zend and IBM allows for continued support to the PHP community. Zend Core for IBM is free to download and is fully supported.
The DB2 product family is a high-performing, reliable data management system that has won various awards and has leading results on various benchmarks. IBM continues to streamline its offerings with the introduction of DB2 9 . New automated database administration features such as Self-Tuning Memory Management (STMM) are introduced, which significantly reduces the task of configuring the DB2 server by continuously updating configuration parameters, resizing buffer pools, and dynamically distributing available memory resources. This new feature reduces maintenance time because database administrators (DBAs) are not required to monitor the workload and manually change the memory heaps on the data server when the workload demands change.
The new row compression feature is also available to reduce storage costs for large databases. DB2 9 also includes support for XML data and comprehensive XML query capabilities with its pureXML™ technology. DB2 9 also includes other rich features such as:
- Automatic storage databases: Table spaces can be created whose container and space management characteristics are completely determined by the DB2 product.
- DB2 connection concentrator: This feature efficiently handles large numbers of incoming connections that have very short-lived transactions. It eliminates the requirement for a one-to-one mapping of client connections to server processes found on many other database management systems. This architecture reduces additional overall costs for memory and CPUs to handle increased workloads. This feature is included with the Enterprise Server Edition of DB2 9 and it is also available as a value-added feature for DB2 Workgroup Server Edition and DB2 Express.
The hardware setup used for this study comprised a driver machine, five Web servers, and a separate data server. The driver machine emulated Web users of an online DVD store generating HTTP requests. These HTTP requests were routed to the Web servers that contained the DVD store application logic. Each Web server used was an Intel®  Xeon® 5150 two-socket, dual-core processor machine with 4 GB of memory running Red Hat Enterprise Linux® Server 4 and the latest Zend Core for IBM 1.5.0. For the purpose of these experiments, up to five Web servers were added as the number of concurrent users to the online DVD store increased. Each Web server processed the HTTP requests from the Web browsers and maintained two thousand simultaneous Apache connections; at this many Apache processes, all the available 4 GB of memory was consumed on a single Web server. Persistent connections were maintained from the Web server to the data server, which allowed the connection handle to be preserved after the transaction completed; future incoming transactions re-used the same connection handle. A think time [i] of 160 seconds was used for all of the experiments.
The database server was a four-socket, dual-core Intel Xeon 7040 system equipped with 4 GB of memory running Novell SUSE Linux Enterprise Server 9 Service Pack 3 and DB2 9 FixPak 1 [ii] . A single IBM N3050 storage server  was connected to the data server via a Fibre Channel Protocol (FCP). Attached to the storage server were twenty eight 133 GB 10K RPM disks. A single aggregate was defined, on which a FlexVol was created for the database tables and logs. Figure 1 shows the system configuration used.
Figure 1: System architecture used to Service 10,000 PHP Requests
The workload used for demonstration in this paper simulates an Online Transaction Processing (OLTP) environment. The workload and database scripts can be obtained from here. A driver simulates users logging in and browsing the catalog of available products via simple search queries. Returning customers are presented with their online purchase transactions history, while new users may register to create customer accounts. Users may select items they would like to purchase and proceed to check out or continue to view available products. Restocking is also done once a minute to replenish the product quantities. Figure 2 shows the average percentage of execution for each transaction.
Figure 2: Workload Transaction Distribution
A 20 GB database is created representing 20 million customers, one million orders, and 100,000 products. The database is created using automatic storage, enabling DB2 9 to define and manage all space used by the database. The tuning of the database memory heaps are not predefined but are derived when invoking the STMM feature in DB2 9.
For Internet Web applications with many relatively transient connections, the DB2 connection concentrator allows handling of tens of thousands of client connections to be processed efficiently by the database. It helps reduce the memory footprint on the data server because multiple connections can be configured to require fewer DB2 agents. The connection concentrator feature is enabled by defining the MAX_CONNECTIONS setting in the database configuration. For these experiments, MAX_CONNECTIONS was set to 10,000, and MAX_AGENTS was set to 500. This means that the DB2 database would accept up to a total of 10,000 concurrent connections, which would be serviced by 500 back-end agents.
The experiments were conducted in five stages; with each stage, the number of concurrent Web users accessing the online DVD store increased by 2000. Additional Web servers were added to service these requests; however, no manual tuning of the DB2 data server was done [iii]. Each time additional Web servers were added, the database throughput performance and memory usage were noted on the DB2 server.
Throughput was measured as the number of orders processed by the DVD store application per minute. Figure 3 shows that the throughput on the data server increased proportionally as the numbers of users were scaled from 2000 to 10,000. At 10,000 active connections, the overall CPU utilization on the DB2 server was approximately 80%, and the throughput attained was approximately five times the throughput at 2000 users. This suggests that the throughput scaled as the number of connections to the database increased.
Figure 4 shows the memory usage on the data server as the number of connections to the database increased. From the graph, it can be noted that each additional 2000 connections to the data server increased the memory consumption by approximately 300 MB. The small controlled increase was due to the use of the DB2 connection concentrator feature, which balanced the workload of the active connections using only 500 fixed DB2 back-end agents. At 10,000 connections, the total memory utilized on the system was only a little below 3.0 GB and the memory required by the connection concentrator was only about 1.5 GB, much less than if 10,000 active connections were made to a database system with a one-to-one mapping of clients to data server processes.
Figure 3: CPU utilization and Throughput on the Database Server while Increasing the Number of Active Connections
Figure 4: Memory Requirements on the Database Server while Increasing the Number of Active Connections
In this paper, it was demonstrated how Zend Core for IBM and IBM DB2 9 can be used together to implement PHP solutions to service tens of thousands of database connections with little strain on system CPU and memory resources. It is not uncommon that large deployments of PHP consist of hundreds of PHP servers. With each server typically running hundreds of Apache worker processes, each with persistent database connections, the database needs to scale to handle thousands and tens of thousands of concurrent connections. For this specific test, a maximum of ten thousand active connections were made to the DB2 data server and by using the DB2 connection concentrator, we saw that this feature easily allowed systems to scale even at a large number of connections. The connection concentrator, a powerful feature offered in DB2 9, allows systems to scale and utilize available system resources efficiently instead of requiring the addition of more CPU and memory, a capability unmatched by other database management systems. DB2 9 also offers other unique autonomic features, which helps reduce the maintenance time on the data server. These unique DB2 features as well as partnership with Zend Core demonstrate how IBM is committed to bringing powerful, easy-to-use software to customers that saves money and adds efficiency.
[i] Think time is the time between Web requests for each user.
[ii] DB2 9 FixPak 1 is scheduled to be generally available by November 30th, 2006.
[iii] This paper is focused on optimizing the resource utilization in the database tier and additional optimization in the Web tier (Web server and PHP environment) could be applied in addition to the techniques discussed in this paper.
We would like to convey our special thanks to Intel for providing us with their Xeon 5150 and Xeon 7040 systems for conducting this study. We are also grateful to Network Appliance for providing us with the N3050 storage server used. Last, but not the least, we would like to thank Berni Schiefer, Grant Hutchison, Andi Gutmans and Cal Evans for their insight and review of this article.
- DB2 9 product family. Available: http://www-306.ibm.com/software/data/db2/
- Intel products and services. Available: http://www.intel.com/
- Network Appliance products and services. Available: http://www.netapp.com/
- Zend Core for IBM. Available: http://www.ibm.com/software/data/info/zendcore/
© Copyright IBM Corporation, 2006. All Rights Reserved
Produced in Canada
IBM, the IBM logo, Cloudscape, DB2, DB2 Universal Database, e(logo)business Software, Informix, and pureXML are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.
Intel and Xeon are registered trademarks of Intel Corporation or its subsidiaries in the United States and other countries.
Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.
UNIX is a registered trademark of The Open Group in the United States and other countries.
Other company, product, and service names may be trademarks or service marks of others.
INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS PAPER "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF NON-INFRINGEMENT, MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. Some states do not allow disclaimer of express or implied warranties in certain transactions, therefore, this statement may not apply to you.
Information in this paper as to the availability of products (including portlets) was believed accurate as of the time of publication. IBM cannot guarantee that identified products (including portlets) will continue to be made available by their suppliers.
This information could include technical inaccuracies or typographical errors. Changes may be made periodically to the information herein; these changes may be incorporated in subsequent versions of the paper. IBM may make improvements and/or changes in the product(s) and/or the program(s) described in this paper at any time without notice.
Any references in this document to non-IBM Web sites are provided for convenience only and do not in any manner serve as an endorsement of those Web sites. The materials at those Web sites are not part of the materials for this IBM product and use of those Web sites is at your own risk.
IBM may have patents or pending patent applications covering subject matter described in this document. The furnishing of this document does not give you any license to these patents. You can send license inquiries, in writing, to:
IBM Director of Licensing
4205 South Miami Boulevard
Research Triangle Park, NC 27709 U.S.A.