EWEEK Labs/PC Labs can be said to be the leader in Benchmark Testing. As early as October 1993, their sister Magazine PC Magazine did the same test. This cooperation with PC Magazine has tested the performance of five types of databases on the Java application server. The results show that the performance of MySQL's latest version 4.0.1 is comparable to that of Oracle 9i, of course, Microsoft's SQL Server 2000 is low. The five databases tested are: IBM DB2 7.2 FixPack 5, Microsoft SQL Server 2000 Enterprise Edition SP2, MySQL AB MySQL 4.0.1 Max, oracle Oracle9i Enterprise 9.0.1.1.1 and Sybase ASE (Adaptive Server Enterprise) 12.5.0.1. The purpose of the benchmark is to test compatibility. All databases are tested on the same hardware: HP NetServer LT 6000r has four 700 MHz Xeon CPUs, 2 GB memory and 24 10000 GB Ultra3 SCSI disks with 9.1 rpm. The operating system is Windows 2000 Advanced Server SP2. The tested application is a Web-based bookstore application called Nile. Nile uses the Empirix testing Suite 6.0 to load 50 to 10000 concurrent users. The application server used in the test is WebLogic 6.1 SP1 of BEA, and Nile applications are compiled using JSP. Each test runs 50 thousand orders per hour, with 0.15 million to 0.2 million records. The best scalability we get is that we have two 6-channel HP NetServer LT 6000r (4 GB memory, A Gigabit Nic) runs six WebLogic routines on the server. The allocation of HTTP traffic balance to these six cases. The overall test results show that Oracle9i and MySQL have the best performance and scalability, but 9i is only slightly better than MySQL, ASE, DB2, when Oracle9i and MySQL reach 550 concurrent users, the performance of ASE is reduced to 500 pages per second, which is 100 fewer than 9i and MySQL, under high load conditions, DB2's performance is also dropping sharply, with only 200 pages per second. Due to JDBC driver problems, SQL Server can only reach 200 pages per second throughout the test. Driver, memory optimization, and database design problems are the main factors affecting performance. After manual fine-tuning, the performance will be twice as bad as the performance without optimization. Oracle and MySQL drivers have complete JDBC features and stability (MySQL employees use JDBC drivers written by Mark donews because they do not have their own JDBC drivers ). Finding the best memory configuration for a variety of databases is a challenging task. We spent several days on this issue. SQL Server and MySQL are the simplest to configure, while Oracle9i is the most complex. 9i uses a lot of independent memory buffering, and each database connection consumes a lot of memory (about 177 KB), while DB2 only needs KB, SQL Server, mySQL and ASE both require only 50 kb, and the result is that 9i data and execution plan buffer is smaller than other databases. The high performance of MySQL comes from the buffer of the query result set in the memory. This is a new feature of 4.0.1. If we do not use this buffer, MySQL performance will decrease by 2/3. In addition, MySQL employees also use different database engines based on different tables. All order tables use the InnoDB Engine of MySQL (supports transactions, row locks, and multi-version synchronization), while directories and user tables do not require transaction support, and MySQL is lightweight, non-transaction MyISAM engine. The high-speed Query Buffer introduced by MySQL 4.0.1 is eye-catching. Other databases do not have this feature. If the query text matches exactly the same as that in the buffer, MySQL can directly buffer the data without compiling the query statement, obtaining the lock or searching the index, this technology is useful when tables are not updated frequently. Although Microsoft SQL 2000 is not doing well on the Java platform, when we use ASP. net rewrite benchmark test and use IIS 5.0 to connect to ole db. The result may give Bill Gates a sigh of relief, with 870 pages per second. Before the test, we invited five employees to participate in the test. Only MySQL and Sybase were happy to go. IBM only promised to communicate via email and both Microsoft and Oracle refused to participate. Therefore, our database adjustment work on our behalf. During the test, we were surprised to find that the driver was the biggest source of the problem. Among the five tested databases, only 9i and MySQL can run Nile for 8 consecutive hours. The DB2 JDBC driver does not support updatable result sets, therefore, we can only open all the result sets (using CONCUR_READ_ONLY), use SQL update statements to update, and finally pass the Stability Test for 8 hours. When we use the JConnect 5.5 driver of Sybase, we find that when the result set of the application request contains a bidirectional cursor, JConnect stores the entire result set in the memory of the client to increase the speed of subsequent cursor command processing. This work is still sloppy at low load, but when the number of users reaches hundreds, the application server consumes several hundred megabytes of memory. In less than 8 hours, all the six application server processes were suspended. To solve this problem, we rewrite the browsing logic of the application and only use forward cursor (JConnect is not in the client memory buffer). To ensure that the previous books are read, we need to run the same query twice to get the total number of books and then get the data of the books, which affects the performance of ASE. However, the result is that ASE can run the benchmark test all night, and the client can benefit from the application of the C/S structure, but this is a poor choice for the application server. Microsoft's JDBC design is flawed. On the WebLogic console, we found that every time the Java Virtual Machine is used as the garbage collection, the memory released is less, therefore, Microsoft's JDBC driver took less than eight hours to complete. (Translated in this article: Xu permanent, please note: FreeLAMP.com Xu permanent. Thank you !)
Trackback: http://tb.blog.csdn.net/TrackBack.aspx? PostId = 491321
