MongoDB, HandlerSocket and MySQL performance testing

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 use Webbench to pressure the Web server.

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, adjusted 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 from becoming a bottleneck, the test program was set to share the same connection for every 1,000 requests, and was set to perform 1,000 data requests for each page request.

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

2. Test results

1. 1 million records

1. Inquiry

2. Insert

2. 10 million records

1. Inquiry

2. Insert

​\

3. 20 million records

1. Inquiry

2. Insert

4. 50 million records

1. Inquiry

2. Insert

3. Summary of test analysis

1. I/O reading and writing status

It can be seen from the TPS data in the case of insertion,
There are obvious differences in the data of MySQL, HandlerSocket and Mongodb, 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 number of QPS and the number of TPS are basically the same, that is, each Insert operation corresponds to an I/O write operation. Some optimizations can be done from the MySQL database itself, but this test did not cover this scenario.

HandlerSocket internally uses Bulk
Insert operation, so it can be seen that the number of QPS is significantly greater than the number of TPS, and the batch insertion operation significantly improves the overall performance.

Mongodb uses a merge operation internally, in which data is first stored in memory and then flushed to disk. So, from the testIt can be seen from the data that the slope of the TPS curve is very large: sometimes the TPS is zero, and it is still in the memory and has not been flushed to the disk; sometimes the TPS is very high, and at this time the CPU is also very high, almost It is 100%. At this time, the operation of Flush to disk is being performed. Based on this mechanism, we will do some more detailed optimization and testing in the future, because there may be several problems:

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

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

2. CPU usage

It can be seen from the CPU data in the query situation that MySQL and Mongodb are almost 100%, while HandlerSocket eliminates various Sql
For Parser and related operations, the CPU usage remains between 40% and 60%, which is within a reasonable range.

From the CPU data during insertion, we can see that the CPU usage of HandlerSocket remains between 40% and 60%, which is lower than MySQL and Mongodb. MySQL and Mongodb remain between 50% and 90% in most cases.

3. QPS situation

It can be seen from the QPS data in the query situation that the query performance of HandlerSocket and Mongodb are almost the same, both reaching more than 30,000, and as the amount of data increases, the performance does not fall back and remains above 30,000. Currently, it is only tested up to 50 million data, and higher values ​​have not been covered in this test. The performance of MySQL is worse in comparison, generally between 18,000 and 25,000. Of course, there is not much optimization for MySQL this time. It just increases the size of innodb_thread_pool and the size of the data block allocated each time. If it is optimized for MySQL, it may be possible to improve the performance of HandlerSocket and MySQL at the same time.

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