The Implementation Of Online Testing Subsystem For Database Transaction Processing Performance Test And The Analysis Of InnoDB Query Processing

There are two parts in this paper. The first is about the implementation of online test subsystem for database transaction processing performance test and the test result of commercial database Oracle Enterprise 9i and homemade database RedBase 7.5 by using this subsystem. The second is about the principle of InnoDB query processor in open source database MySQL, and gives an example to describe how InnoDB processor works at the same time. The purpose of two parts is to meet the development requirements of homemade database. Database management system is not only the centre of base software technology, but also the high-technology that our country should develop first. Homemade database is necessary in many fields, such as improving national enterprises'creativity and market competition, maintaining the national information security, and developing our national information industry etc. Developing a general database OLTP test system will be benefit to a homemade database production. The system can be applied to design, develop, upgrade, and recursively test the database production. The research of open source database can accumulate the technology of database implementation, and provides necessary supports in developing homemade database. The OLTP test system is designed and implemented by the writer and the other two group members. This system can be applied to measure the performance of a database in online transaction processing. It bases on TPC-C benchmark, and its prototype is open source tool dbt2. TPC-C is an industry standard benchmark created by the Transaction Processing Council (TPC) to target online transaction processing (OLTP), and it is the authority in OLTP. The test system can provide test for two databases Oracle Enterprise 9i and RedBase 7.5 at present. The test system is composed of four subsystems: ACID test, data processing, online test, and result analyzer. This paper only presents implementations of the online test subsystem and the result analyzer subsystem. The other subsystems of the test system are presented in the papers of the writer's group members. The online test subsystem is the centre of the test system. It runs in the client, and connects with the database of the server through ODBC interface. During the course of test, the online test subsystem simulates muti-terminals to access the database simultaneously. Each terminal connects with the database through connection pool, and executes five types of transaction (New-Order, Payment, Order-Status, Delivery, and Stock-Level) continuously. Each type must meet response time constraints and isolation requirements. The value of tpmC is obtained by measuring how many New-Order transactions per minute in a tested system when the system under the high concurrency environment. The online test subsystem is made up of five modules: main program, connection pool, terminal users, transaction queue, and log. Main program module startups the connection pool and terminal users threads, then checks the execution of threads. When test ends, main program module will kill all threads. Connection pool module comprises many connection threads, and each connection thread takes out the transaction from the transaction queue and executes it. Terminal users module also comprises lots of terminal user threads. Each terminal user thread generates transaction node and puts it into the transaction queue. Transaction queue module is in charge of the communication between connection pool and terminal users module. After a terminal user thread generates a new transaction node and puts it into the transaction queue, the current thread is in waiting state until the transaction node is executed. A connection pool thread takes out a transaction from the head of the queue, and then executes it. Log module can write error information and execution results to two logs. Result analyzer subsystem reads execution results from the result log, and then calculates the response time of each type transaction and the value of tpmC. Oracle Enterprise 9i and RedBase 7.5 are tested by using the online test subsystem. The tests base on personal computer under windows system environment. According to the test results, RedBase is near to Oracle in OLTP. RedBase have some obvious shortages comparing with Oracle, especially in query optimizer. Ten members in our group spend half a year in analyzing the implementation of InnoDB storage engine in MySQL database. During...