MongoDB, HandlerSocket and MySQL performance test

1. Test environment

1. Test server status

A total of 4 test servers are involved:

Stress test server

Web server

MongoDB server

MySQL server.

The machine configuration is:

CPU: Intel(R) Core(TM)2 Duo CPU E7200 @ 2.53GHz

RAM: 8G DDR2 667

Disk: SATA

Operating system: Redhat 5.5

1. Stress test server

Install Webbench 1.5, and press the Web server through Webbench.

2. Web server

Nginx 0.8.54 + PHP 5.3.3
(php-fpm), the php driver with Mongodb and HandlerSocket installed.

The php driver of Mongodb is: mongodb-mongo-php-driver-1.1.1-19-gc584231.tar.gz

The php driver of HandlerSocket is: php-handlersocket-0.0.7.tar.gz

Call Mongodb and HandlerSocket through Php program.

3. MongoDB server

MongoDB version: 1.6.5

4. MySQL server

MySQL version: 5.1.53

HandlerSocket version: 1.0.6-60-gf51e061

MySQL storage engine: Innodb, adjust the Thread Pool Size of innodb to 2G

2. Test program and test data extraction

1.
In order to avoid opening the connection and the Http server becoming a bottleneck, in the test program, it is set to share the same connection for every 1000 requests, and at the same time, it is set to execute 1000 data requests for each page request.

2.
Test data, including QPS, CPU, IO, etc., from commands provided by the operating system (such as vmstat, iostat, etc.) or commands provided by Mongodb and Mysql (such as mongostat, mysqladmin, etc.) to get.

2. Test results

1. 1 million records

1. Query

2. Insert

2. 10 million records

1. Query

2. Insert

\

3. 20 million records

1. Query

2. Insert

4. 50 million records

1. Query

2. Insert

3. Summary of test analysis

1. I/O reading and writing

It can be seen from the TPS data in the case of insertion,
The data of MySQL, HandlerSocket and Mongodb are quite different, which is mainly related to their internal implementation and testing methods.

In the test scenario, MySQL uses a single Insert method, so it can be seen that the QPS number and TPS number are basically the same, that is, each Insert operation corresponds to an I/O write operation. You can do some optimization from the MySQL database itself, this test did not cover this scenario.

Internally, HandlerSocket uses Bulk
Insert operation, therefore, it can be seen that the QPS number is significantly greater than the TPS number, and the batch insert operation significantly improves the overall performance.

Mongodb adopts the method of merging internally, and adopts the method of storing data in memory first, and then flushing to disk. So, from the test��It can be seen that the slope of the TPS curve is very large: sometimes the TPS is zero, and at this time it is still stored in the memory, and has not been flushed to the disk; sometimes the TPS is very high, and at the same time the CPU is also very high, almost It is 100%, and at this time it is doing the Flush to disk operation. Based on this mechanism, we will do some more detailed optimization and testing in the future, because there may be several problems in this way:

First, the pressure on IO and CPU may be very high during a certain period of time, and even reach a peak. In this case, the overall health of the service will face some challenges.

second,
If the server restarts, data loss may occur, and the data in the memory that has not been flushed to the disk will be lost. Of course, this situation is two-sided, because using this method, it can be seen from the test results that the overall write performance is higher than that of MySQL and HandlerSocket. This is a trade-off, and it depends on whether the specific business can accept this. In exchange for high performance for data reliability, some services may be acceptable, such as feeds.

2. CPU usage

From the CPU data in the case of query, it can be seen that both MySQL and Mongodb are close to 100%, while HandlerSocket saves all kinds of Sql
For Parser and related operations, the CPU usage remains between 40% and 60%, which is within a reasonable range.

It can be seen from the CPU data in the case of insertion that the CPU usage of HandlerSocket remains between 40% and 60%, which is lower than that of MySQL and Mongodb. In most cases, MySQL and Mongodb remain between 50% and 90%.

3. QPS situation

From the QPS data in the query case, it can be seen that the query performance of HandlerSocket and Mongodb is almost the same, reaching more than 30,000, and with the increase of data volume, the performance has not dropped, and remains above 30,000. At present, it is only the case where the maximum data is tested to 50 million, and the higher value has not been covered by this test. The performance of MySQL is relatively poor, generally between 18000 and 25000. Of course, there is not much optimization for MySQL this time, but the size of innodb_thread_pool and the size of the data blocks allocated each time are increased. If optimized for MySQL, the performance of HandlerSocket and MySQL may be improved at the same time.

It can be seen from the QPS data in the insertion case that Mongodb obviously has a relatively large advantage, which is related to its implementation method mentioned earlier. As the amount of data increases, QPS decreases accordingly. In this regard, MySQL has the largest range. When the amount of data reaches more than 50 million, the insertion performance of MySQL is 2000-3000, while HandlerSocket can be maintained at more than 10,000. Mongodb is More than 20,000.