Performance test report for a high performance MySQL proxy (kingshard)

This is a creation in Article, where the information may have evolved or changed.

Performance test Report for Kingshard

Before the several blog, to everyone to share is Kingshard (Https://github.com/flike/kingshard) architecture and design. In fact, many people are also very concerned about the performance of Kingshard. Recently enthusiastic Netizen Bigpyer has done the detailed performance test to the Kingshard. Share it here.

1. Test environment

1.1 Server Configuration

category	name
OS	Cloud Host Ubuntu 14.04 LTS
Cpu	Common KVM CPU @ 2.40GHz
Ram	8GB
DISK	500GB
Kingshard	Master Branch
Mysql	v5.6.25
Sysbench	v0.5

2. Performance requirements

1. Test the performance gap between the two scenarios of forwarding SQL requests through Kingshard and direct-attached DB to send SQL requests.

2. The Max_conns_limit parameter in the test configuration file affects the Kingshard and finds the optimal value.

3. Test prep Work

3.1 Kingshard Performance test environment construction

A Kingshard performance test environment was built using the 4 servers configured above:

• Server A runs the Sysbench

• Server B is running the Kingshard system.

• Server C runs the main library

• Server d runs from the library

The four servers are in the same network segment. The specific topology diagram is as follows:

Refer to the documentation installation Kingshard for Kingshard system installation and configuration.
For installation and command options for Sysbench v0.5, refer to Sysbench.

4. Test process

Execute the following command to prepare the test data

time sysbench --test=/data/home/test_user/software/sysbench/sysbench/tests/db/oltp.lua \              --mysql-host=host \              --mysql-port=9696 \              --mysql-user=kingshard \              --mysql-password=kingshard \              --mysql-db=kingshard \              --oltp-tables-count=1 \              --oltp-table-size=1000000 \              --num-threads=50 \              --max-requests=1000000 \              --report-interval=1 \              prepare

The above command creates a table sbtest1 with a data volume of 100w, with the following statement:

CREATE TABLE `sbtest1` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `k` int(10) unsigned NOT NULL DEFAULT '0',  `c` char(120) NOT NULL DEFAULT '',  `pad` char(60) NOT NULL DEFAULT '',  KEY `xid` (`id`),  KEY `k_1` (`k`)) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8 MAX_ROWS=1000000;

Comparison of performance of 4.1 Kingshard and direct-Connect db

Two data metrics for Kingshard and MySQL systems:QPS and average processing time per SQL request, using the Sysbench test to send SQL requests via Kingshard forward SQL requests and Direct Connect DB.
Send four types of SQL requests via Sysbench: SELECT, UPDATE, insert, and delete, which are read-only, read-write 4:1, and write-only.

4.1.1 Kingshard vs. direct DB read-only comparison

Test kingshard forward SQL or direct-attached DB separately by modifying host, port, and executing the following command:

time sysbench --test=/data/home/test_user/software/sysbench/sysbench/tests/db/select.lua \              --mysql-host=host \              --mysql-port=9696 \              --mysql-user=kingshard \              --mysql-password=kingshard \              --mysql-db=kingshard \              --oltp-tables-count=1 \              --oltp-table-size=1000000 \              --num-threads=16 \              --max-requests=1000000 \              --report-interval=1 \              --max-time=20 \              run

By using Sysbench to test the number of concurrent threads, a select operation with a maximum number of requests of 100w is performed, and different concurrent threads can be obtained by modifying the--num-threads.
Test the QPS (the greaterthe QPS, the better the system performance) in both cases of connection Kingshard and direct-connect DB
Test the average latency of the execution SQL in both cases of connection Kingshard and Direct connect DB (the smaller the latency, the better the system performance),
Repeat the test for each group of data three times after the average, specific data for example as shown in the following table:

The above table corresponds to the QPS line chart as follows:

The delay line graph corresponding to the table above is as follows:

4.1.2 Kingshard vs. direct-connect DB read/write 4:1 comparison

Test kingshard forward SQL or direct-attached DB separately by modifying host, port, and executing the following command:

ysbench --test=/data/home/test_user/software/sysbench/sysbench/tests/db/oltp.lua \              --mysql-host=host \              --mysql-port=3306 \              --mysql-user=kingshard \              --mysql-password=ks \              --mysql-db=kingshard \              --oltp-tables-count=1 \              --oltp-table-size=1000000 \              --num-threads=16 \              --max-requests=1000000 \              --report-interval=1 \              --max-time=20 \              run

By using Sysbench to test the number of concurrent threads, we can perform mixed operations such as SELECT, UPDATE, INSERT, delete, etc. with a maximum number of requests of 100w respectively, and get different concurrent threads by modifying--num-threads.
Test the QPS (the greaterthe QPS, the better the system performance) in both cases of connection Kingshard and direct-connect DB
Test the average latency of the execution SQL in both cases of connection Kingshard and Direct connect DB (the smaller the latency, the better the system performance),
Repeat the test for each group of data three times after the average, specific data for example as shown in the following table:

The above table corresponds to the QPS line chart as follows:

The delay line graph corresponding to the table above is as follows:

4.1.3 Kingshard vs. direct db write-only comparison

Test kingshard forward SQL or direct-attached DB separately by modifying host, port, and executing the following command:

time sysbench --test=/data/home/test_user/software/sysbench/sysbench/tests/db/insert.lua \              --mysql-host=host \              --mysql-port=3306 \              --mysql-user=kingshard \              --mysql-password=ks \              --mysql-db=kingshard \              --oltp-tables-count=1 \              --oltp-table-size=1000000 \              --num-threads=16 \              --max-requests=1000000 \              --report-interval=1 \              --max-time=20 \              run

By using Sysbench to test the number of concurrent threads, insert operations with a maximum number of requests of 100w are performed, and different concurrent threads can be obtained by modifying the--num-threads.
Test the QPS (the greaterthe QPS, the better the system performance) in both cases of connection Kingshard and direct-connect DB
Test the average latency of the execution SQL in both cases of connection Kingshard and Direct connect DB (the smaller the latency, the better the system performance),
Repeat the test for each group of data three times after the average, specific data for example as shown in the following table:

The above table corresponds to the QPS line chart as follows:

The delay line graph corresponding to the table above is as follows:

* * As can be seen from the QPS line graph, when the concurrency test thread of sysbench is small, the QPS gap between connecting Kingshard and direct db is larger.
This is mainly because when sysbench concurrent threads are young, the performance of Kingshard is not being fully played,
Sysbench only a small number of threads send requests to kingshard, and the effect of network latency on QPS is the most important. **

* * When the concurrency test thread of sysbench is large, the performance of Kingshard is fully played,
The comparison of QPS is a real response to the comparison between Kingshard and direct DB performance, and the effect of network latency on QPS appears to be small. **

* * from the data on the scale of the above several tables, the QPS when forwarding select requests via Kingshard is about 80% of the direct-connected DB,
The corresponding QPS for the update and insert requests is higher, equivalent to about 85% of the direct-attached db, or even higher, the performance of direct-connect MySQL is lower than that of kingshards forwarding.
As a result, the performance degradation associated with Kingshard forwarding SQL requests has declined, but is completely acceptable, even in high concurrency scenarios where kingshard performance is better than direct-attached DB performance. This is also due to the kingshard in high concurrency, the full use of the role of the connection pool, reducing the high concurrency brought about by the competitive consumption. **

Sysbench the data for concurrent threads above 512 is not given, because direct-attached DB is unable to complete the test properly, but Kingshard can be completed.

Effect of 4.2 max_conns_limit parameters on Kingshard performance

Max_conns_limit is the number of long connections to the backend MySQL created at Kingshard initialization, and the difference in this value setting has a significant effect on kingshard performance.
We suspect that Max_conns_limit is related to the number of connections that the backend MySQL can accept in addition to the Kingshard host CPU core number .
We tested the QPS when Max_conns_limit was set to 16, 32, 64, 128, 256, 512, Kingshard forwarding select,update and insert three operation requests,
The mixed case of SQL is read and write 4:1, and the different SQL of Sysbench is in different transactions, and the results are as follows:

The QPS line chart for the previous number test is as follows:

From the QPS comparison of the three-class SQL operations handled by Kingshard, it is reasonable to set the Max_conns_limit parameter to about 128, which is higher than 128 and has no significant effect by raising the Max_conns_limit value.

The Max_conns_limit parameter value is not necessarily associated with the number of host cores in Kingshard, and is closely related to the number of backend MySQL hosts that can withstand connections.

5. Test conclusion

This article mainly tests the performance gap between the two scenarios of forwarding SQL requests with Kingshard and direct-attached DB to send SQL requests, and the Max_conns_limit value's performance impact on Kingshard. The average read and write performance of the
KS can reach 80% of native MySQL performance, up to 90% under certain conditions, and even beyond MySQL itself as the number of concurrent numbers increases.
KS can be protected by the MySQL, after the addition of KS, the DB layer can be shown to receive higher concurrency, and the length of the execution of different SQL using their own resources, resulting in resource isolation, MySQL will not have performance glitches.
combined with the above test results, Kingshard performance is excellent, and there is no noticeable performance degradation. The also found in the test that the Kingshard system is CPU intensive, and kingshard is most noticeable for CPU consumption relative to disk IO and memory usage, so it is recommended that you prioritize server CPU performance when deploying Kingshard.