TiProxy Performance Test Report

This report tests the performance of TiProxy in the OLTP scenario of Sysbench and compares it with HAProxy.

The results are as follows:

  • The QPS upper limit of TiProxy is affected by the type of workload. Under the basic workloads of Sysbench and the same CPU usage, the QPS of TiProxy is about 25% lower than that of HAProxy.
  • The number of TiDB server instances that TiProxy can hold varies according to the type of workload. Under the basic workloads of Sysbench, a TiProxy can hold 5 to 12 TiDB server instances of the same model.
  • The row number of the query result set has a significant impact on the QPS of TiProxy, and the impact is the same as that of HAProxy.
  • The performance of TiProxy increases almost linearly with the number of vCPUs. Therefore, increasing the number of vCPUs can effectively improve the QPS upper limit.
  • The number of long connections and the frequency of creating short connections have minimal impact on the QPS of TiProxy.

Test environment

Hardware configuration

ServiceMachine typeCPU modelInstance count
TiProxy4C8GIntel(R) Xeon(R) Silver 4214R CPU @ 2.40GHz1
HAProxy4C8GIntel(R) Xeon(R) Silver 4214R CPU @ 2.40GHz1
PD4C8GIntel(R) Xeon(R) Silver 4214R CPU @ 2.40GHz3
TiDB8C16GIntel(R) Xeon(R) Silver 4214R CPU @ 2.40GHz8
TiKV8C16GIntel(R) Xeon(R) Silver 4214R CPU @ 2.40GHz8
Sysbench8C16GIntel(R) Xeon(R) Silver 4214R CPU @ 2.40GHz1

Software

ServiceSoftware version
TiProxyv1.0.0
HAProxy2.9.0
PDv8.0.0
TiDBv8.0.0
TiKVv8.0.0
Sysbench1.0.17

Configuration

TiProxy configuration

In the test, TLS is not enabled between the client and TiProxy, or between TiProxy and TiDB server.

  1. proxy.conn-buffer-size: 131072

HAProxy configuration - haproxy.cfg file

  1. global # Global configuration.
  2. log 127.0.0.1 local2 # Specifies the global syslog server. You can define up to two.
  3. pidfile /var/run/haproxy.pid # Write the PID of the HAProxy process to pidfile.
  4. maxconn 4096 # The maximum number of concurrent connections that a single HAProxy process can accept, equivalent to the command line parameter "-n".
  5. nbthread 4 # The maximum number of threads. The upper limit of the number of threads is the same as the number of CPUs.
  6. user haproxy # Same as UID parameter.
  7. group haproxy # Same as GID parameter. A dedicated user group is recommended.
  8. daemon # Run HAProxy as a daemon in the background, which is equivalent to the command line parameter "-D". You can also disable it with the "-db" parameter on the command line.
  9. stats socket /var/lib/haproxy/stats # The location where the statistics are saved.
  10. defaults # Default configuration.
  11. log global # Inherit the settings of the global configuration section.
  12. retries 2 # The maximum number of times to try to connect to the upstream server. If the number of retries exceeds this value, the backend server is considered unavailable.
  13. timeout connect 2s # The timeout period for HAProxy to connect to the backend server. If the server is located on the same LAN as HAProxy, set it to a shorter time.
  14. timeout client 30000s # The timeout period for the client to be inactive after the data transmission is completed.
  15. timeout server 30000s # The timeout period for the server to be inactive.
  16. listen tidb-cluster # Configure database load balancing.
  17. bind 0.0.0.0:3390 # Floating IP address and listening port.
  18. mode tcp # HAProxy uses layer 4, the transport layer.
  19. balance leastconn # The server with the least number of connections receives the connection first. `leastconn` is recommended for long session services, such as LDAP, SQL, and TSE, rather than short session protocols, such as HTTP. This algorithm is dynamic, and the server weight is adjusted during operation for slow-start servers.
  20. server tidb-1 10.9.18.229:4000 check inter 2000 rise 2 fall 3 # Detects port 4000 at a frequency of once every 2000 milliseconds. If it is detected as successful twice, the server is considered available; if it is detected as failed three times, the server is considered unavailable.
  21. server tidb-2 10.9.39.208:4000 check inter 2000 rise 2 fall 3
  22. server tidb-3 10.9.64.166:4000 check inter 2000 rise 2 fall 3

Basic workload test

Test plan

This test aims to compare the QPS of TiProxy and HAProxy under four types of workloads: point select, read only, write only, and read write. Each type of workload is tested with different concurrency to compare the QPS of TiProxy and HAProxy.

The following command is used to perform the test:

  1. sysbench $testname \
  2. --threads=$threads \
  3. --time=1200 \
  4. --report-interval=10 \
  5. --rand-type=uniform \
  6. --db-driver=mysql \
  7. --mysql-db=sbtest \
  8. --mysql-host=$host \
  9. --mysql-port=$port \
  10. run --tables=32 --table-size=1000000

Point Select

TiProxy test results:

ThreadsQPSAvg latency (ms)P95 latency (ms)TiProxy CPU usageTiDB overall CPU usage
20412730.480.64190%900%
501002550.500.62330%1900%
1001376880.731.01400%2600%

HAProxy test results:

ThreadsQPSAvg latency (ms)P95 latency (ms)HAProxy CPU usageTiDB overall CPU usage
20448330.450.61140%1000%
501036310.480.61270%2100%
1001630690.610.77360%3100%

Read Only

TiProxy test results:

ThreadsQPSAvg latency (ms)P95 latency (ms)TiProxy CPU usageTiDB overall CPU usage
507207611.0912.75290%2500%
10010970414.5817.63370%3800%
20011751927.2132.53400%4100%

HAProxy test results:

ThreadsQPSAvg latency (ms)P95 latency (ms)HAProxy CPU usageTiDB overall CPU usage
507576010.5612.08250%2600%
10012173013.1415.83350%4200%
20013171224.2730.26370%4500%

Write Only

TiProxy test results:

ThreadsQPSAvg latency (ms)P95 latency (ms)TiProxy CPU usageTiDB overall CPU usage
100819577.3210.27290%3900%
30010304017.4531.37330%4700%
50010486928.5952.89340%4800%

HAProxy test results:

ThreadsQPSAvg latency (ms)P95 latency (ms)HAProxy CPU usageTiDB overall CPU usage
100817087.3410.65240%3700%
30010600816.9531.37320%4800%
50012236924.4547.47350%5300%

Read Write

TiProxy test results:

ThreadsQPSAvg latency (ms)P95 latency (ms)TiProxy CPU usageTiDB overall CPU usage
505857117.0719.65250%2600%
1008843222.6029.19330%3900%
20010875836.7351.94380%4800%

HAProxy test results:

ThreadsQPSAvg latency (ms)P95 latency (ms)HAProxy CPU usageTiDB overall CPU usage
506122616.3319.65190%2800%
1009656920.7026.68290%4100%
20012016331.2849.21340%5200%

Result set test

Test plan

This test aims to compare the performance of TiProxy and HAProxy under different result set row numbers. This test uses 100 concurrency, and compares the QPS of TiProxy and HAProxy with result set row numbers of 10, 100, 1000, and 10000.

The following command is used to perform the test:

  1. sysbench oltp_read_only \
  2. --threads=100 \
  3. --time=1200 \
  4. --report-interval=10 \
  5. --rand-type=uniform \
  6. --db-driver=mysql \
  7. --mysql-db=sbtest \
  8. --mysql-host=$host \
  9. --mysql-port=$port \
  10. --skip_trx=true \
  11. --point_selects=0 \
  12. --sum_ranges=0 \
  13. --order_ranges=0 \
  14. --distinct_ranges=0 \
  15. --simple_ranges=1 \
  16. --range_size=$range_size
  17. run --tables=32 --table-size=1000000

Test results

TiProxy test results:

Range sizeQPSAvg latency (ms)P95 latency (ms)TiProxy CPU usageTiDB overall CPU usageInbound network (MiB/s)Outbound network (MiB/s)
10801571.251.61340%2600%140140
100559361.792.43370%2800%820820
1000103139.6913.70310%1500%13701370
10000106493.88142.39250%600%14301430

HAProxy test results:

Range sizeQPSAvg latency (ms)P95 latency (ms)HAProxy CPU usageTiDB overall CPU usageInbound network (MiB/s)Outbound network (MiB/s)
10943761.061.30250%4000%150150
100701291.421.76270%3300%890890
1000950111.1814.73240%1500%11801180
10000955104.61320.17180%1200%12001200

Scalability test

Test plan

This test aims to verify that the performance of TiProxy is proportional to its specifications, to ensure that upgrading the specifications of TiProxy can improve its QPS upper limit. This test uses TiProxy instances with different vCPU numbers and concurrency to compare the QPS.

The following command is used to perform the test:

  1. sysbench oltp_point_select \
  2. --threads=$threads \
  3. --time=1200 \
  4. --report-interval=10 \
  5. --rand-type=uniform \
  6. --db-driver=mysql \
  7. --mysql-db=sbtest \
  8. --mysql-host=$host \
  9. --mysql-port=$port \
  10. run --tables=32 --table-size=1000000

Test results

vCPUThreadsQPSAvg latency (ms)P95 latency (ms)TiProxy CPU usageTiDB overall CPU usage
240585080.680.97190%1200%
4801048900.761.16390%2000%
61201555200.771.14590%2900%
81602021340.791.18800%3900%

Long connection test

Test plan

This test aims to verify that a large number of idle connections have minimal impact on the QPS when the client uses long connections. This test creates 5000, 10000, and 15000 idle long connections, and then executes sysbench.

This test uses the default value for the conn-buffer-size configuration:

  1. proxy.conn-buffer-size: 32768

Use the following command to perform the test:

  1. sysbench oltp_point_select \
  2. --threads=50 \
  3. --time=1200 \
  4. --report-interval=10 \
  5. --rand-type=uniform \
  6. --db-driver=mysql \
  7. --mysql-db=sbtest \
  8. --mysql-host=$host \
  9. --mysql-port=$port \
  10. run --tables=32 --table-size=1000000

Test results

Connection countQPSAvg latency (ms)P95 latency (ms)TiProxy CPU usageTiProxy memory usage (MB)TiDB overall CPU usage
5000966200.520.64330%9201800%
10000961430.520.65330%17101800%
15000960480.520.65330%25701900%

Short connection test

Test plan

This test aims to verify that frequent creation and destruction of connections have minimal impact on the QPS when the client uses short connections. This test starts another client program to create and disconnect 100, 200, and 300 short connections per second while executing sysbench.

Use the following command to perform the test:

  1. sysbench oltp_point_select \
  2. --threads=50 \
  3. --time=1200 \
  4. --report-interval=10 \
  5. --rand-type=uniform \
  6. --db-driver=mysql \
  7. --mysql-db=sbtest \
  8. --mysql-host=$host \
  9. --mysql-port=$port \
  10. run --tables=32 --table-size=1000000

Test results

New connections per secondQPSAvg latency (ms)P95 latency (ms)TiProxy CPU usageTiDB overall CPU usage
100955970.520.65330%1800%
200946920.530.67330%1800%
300941020.530.68330%1900%