Eweek Authoritative Test Shows Mysql 4.0.1 to match Oracle 9i

xiaoxiao2021-03-06  47

MySQL latest version is mysql4.0.5

Eweek Labs / PC Labs can be said to be a boss of a benchmark test. As early as October 1993, their sisters magazine PC Magazine did the same test. This time and PC Magazine collaborates to test the performance of five databases on the Java application server. The result shows that Mysql's latest 4.0.1 version performance can be compared with Oracle 9i, and the lower is of course a Microsoft's SQL Server 2000. :-) These five databases are: IBM DB2 7.2 FixPack 5, Microsoft's SQL Server 2000 Enterprise Edition SP2, MySQL AB's mysql 4.0.1 max, Oracle Oracle9i Enterprise Edition 9.0.1.1.1 and Sybase ASE (Adaptive Server Enterprise) 12.5.0.1.

Test compatibility is also a major purpose of the benchmark test. All databases are tested under the same hardware conditions: HP NetServer LT 6000R has four 700MHz Xeon CPU, 2GB memory, and 24 sets of 10,000 rpm 9.1Gb Ultra3 SCSI disks, operations The system is Windows 2000 Advanced Server SP2.

The test-based application is a web-based bookstore app called NILE. NILE uses Empirix's test suite 6.0, which can load 50 to 10,000 concurrent users.

The application used by the test is the WebLogic 6.1 SP1 of the BEA, and the NILE application is written in JSP.

Each test runs an hour to generate 50,000 orders, 150,000 to 200,000-related records, the best scalability we get is on two 6-way HP NetServer LT 6000R (4GB memory, Gigabit network card) server Run 6 WebLogic routines. HTTP flow balanced assignment into these six routines.

The overall results of the test show that Oracle9i and MySQL have the best performance and scalability, but 9i is only better than MySQL, ASE, DB2, Oracle9i and MySQL arrive at 550 concurrent users, and the performance of ASE has dropped. 500 pages per second, 100 pages lower than 9i and MySQL, DB2 under high-load cases, performance decreased is very powerful, only 200 pages per second.

SQL Server can only reach 200 pages per second throughout the test due to the problem of JDBC drivers.

Drivers, memory optimization, and database design issues are the main factors affecting performance, and after hand-made performance, there is twice as long as the performance of no tuning.

Oracle and MySQL drivers have complete JDBC feature and stability (Mysql employees using JDBC drivers written by Mark Matthews because they don't have their own JDBC drivers).

Finding the best memory configuration for a variety of databases is a challenging job, we spent a few days on this issue.

The configuration of SQL Server and MySQL is the easiest, and Oracle9i is the most complicated. 9i uses a lot of independent memory buffers, each database connection needs to consume a lot of memory (approximately 400kB), while DB2 only needs 177 kb, SQL Server, MySQL and ASE only need 50kb, resulting is 9i data And the buffer of the implementation plan is smaller than other databases.

MySQL's high-performance self-adopted query result set buffer in memory, this is a new feature of 4.0.1, and we do not use this buffer, mysql performance will drop two-thirds. In addition, Mysql employees also use different database engines depending on the table. All order tables use mysql's InnoDB engine (supporting transactions, line locks, multi-version synchronization), directory, and user tables, do not require transaction support, with mysql lightweight, non-transaction's Myisam Engine.

The newly introduced high-speed query buffer in MySQL 4.0.1 is inconsistent, and other databases do not have this feature. If the text of the query has the same match, Mysql can directly remove data without compiling query, lock, or search indexes, this technology is very useful in the case where the table is not frequently updated.

Microsoft's SQL 2000 does not perform well on the Java platform, but when we rewrite the benchmark test with ASP.NET, use IIS 5.0, and OLE DB connection, the result may make Bill Gates pine, 870 per second Page.

Before the test, we invited five companies to send members to participate in the test, only MySQL and Sybase are pleased to go, IBM just promises to communicate through email, Microsoft and Oracle refuse to participate. So their database adjustments are all of us.

In the test, our surprised discovery driver is the biggest root of the problem.

In five test databases, only 9i and mysql can run Nile 8 hours, DB2's JDBC driver does not support updatable result sets, so we can only open all result sets (using concur_read_only), using SQL Update The statement is updated and eventually adopted for 8 hours of stability testing.

When using Sybase's JCONNECT 5.5 driver, we found that JConnect is stored on the client's memory to increase the command processing speed of the subsequent cursor to increase the command processing of the next subsequent cursor. Ma Ma Tiger, but when the user reaches hundreds, the application server consumes a few hundred megabytes of memory. The results were less than 8 hours, and our 6 application server processes hang up.

In order to solve this problem, we rewrite the application's browsing logic, only the forward cursor (JConnect is not in the client memory buffer), in order to ensure access to the previous book, we need to run the same query twice, get the total number of books Then get the data of the book, which affects the performance of ASE.

However, the result of this is the running base test of ASE, and the client can benefit from the application of C / S structure, but for the application server, this is a poor choice.

Microsoft's JDBC design has a defect. We found that every Java virtual machine is Garbage Collection, which is less than 8 hours, which is less than 8 hours.

?

(文 翻久, please be sure to indicate when reprint: freeelamp.com Xu Yongjun. Thank you!)

转载请注明原文地址:https://www.9cbs.com/read-116875.html

New Post(0)