Tencent CDB copy selection and capacity evaluation
1. Purpose of pressure measurement:
-
CDB replication model selection
-
Reference for CDB capacity planning
CDB now support type copy type is more, here I choose the following copy type pressure measuring contrast: MySQL 5.6 [asynchronous | synchronous | enhancement and synchronous] replication, 5.7 asynchronous replication (5.7 only support asynchronous replication).
CDB type: high availability 4000MB memory and 200 gb storage space
2. Press: 4G, 2Core machine
Pressure measurement command:
sysbench /usr/local/share/sysbench/oltp_read_only.lua \
–db-driver=mysql \
–mysql-host=${DBIP} \
–mysql-port=${DBPORT} \
–mysql-db=wubx \
–mysql-user=wubx \
– mysql – password = ‘XXXX’ \
–tables=10 \
–table_size=5000000 \
–threads=200 \
–time=900 \
–report-interval=1 –percentile=99 run
(10 tables, each table with 5 million data and 200 concurrent pressure tests), each model was run three times, and the average was taken.
3. Pressure measurement results (Data in July 2018) :
Intuitively, the results don’t seem to make much difference. It’s close. But just to get a sense of who’s better, here’s another picture of QPS:
MySQL 5.6 enhanced semi-synchronous replication in CDB performs better than asynchronous replication in MySQL5.7. It can also be said that CDB’s MySQL5.6 optimization is quite good. (The delay of the standby node is relatively large during the pressure measurement)
4. Analyze whether the results are worthwhile
But on second thought, the result is too close, so let’s look at the network card traffic for that reason:
It was found that the network card traffic was stable at 165.xMB, and the network card of the pressure tester I used was only 1.5g, which made the network card traffic very scary. Consult the technical staff of CDB, it turns out that their CDB does not limit the network card flow, and a single CDB is a 10 gigabit network card (laugh if you use CDB), in addition to explain an amazing secret: CDB pressure test that did not limit IO, this is a big benefit (this data for more than half a year, now is not so, not guaranteed, you need to be interested in pressure test, self-verification). If you feel the response time is slow using CDB, please check your business SQL and Schema design for optimization.
Based on this test, I’ve basically eliminated the CDB performance problem and only considered disk size for capacity planning. So the pressure test is not continued.
5. Conclusion:
1. For copy selection:
For the CDB environment, you don’t need to consider the operation part, so I suggest you can directly enhance the semi-synchronous line, of course, in order to use 5.7 version, you have to choose asynchronous replication.
2. Capacity planning
Consider space first line, network card traffic, IO first do not consider. Feel sometimes think of Tencent do game gene here is also the performance of the king of the usual style.
6. Think about:
Why does MySQL 5.6 replication in CDB seem more interesting than MySQL5.7? CDB MySQL 5.6
Follow the public account: MySQLBeginner