通过proxy 写数据(单条insert)到mysql后,QPS下降了4倍多。
环境:
- cpu:intel i5-9400 6核,2.9Ghz *1
- 内存:16G
- 硬盘: KINGSTON SA400M8 (m2 240G),写入速度 350MB/S
- 系统:ubuntu bullseye/sid
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 63088
max locked memory (kbytes, -l) 2032193
max memory size (kbytes, -m) unlimited
open files (-n) 1000000
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 63088
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
max_connections = 10000
innodb_buffer_pool_size = 4G
skip_ssl
skip-name-resolve
#关闭binlog,普通日志
sysbench --test=/usr/local/share/sysbench/oltp_insert.lua \
--mysql-host=127.0.0.1 \
--mysql-port=3309 \
--mysql-user=root \
--mysql-password='root' \
--mysql-db=encrypt_db \
--db-driver=mysql \
--tables=1 \
--table-size=5000000 \
--report-interval=10 \
--threads=300 \
--time=180 \
run
proxy相关配置:
#server.yaml
mode:
type: Cluster
repository:
type: ZooKeeper
props:
namespace: governance_ds
server-lists: 172.16.6.219:2181
retryIntervalMilliseconds: 500
timeToLiveSeconds: 60
maxRetries: 3
operationTimeoutMilliseconds: 500
overwrite: true
#
rules:
- !AUTHORITY
users:
- root@%:root
- sharding@:sharding
provider:
#type: ALL_PRIVILEGES_PERMITTED
type: SCHEMA_PRIVILEGES_PERMITTED
props:
user-schema-mappings: root@=encrypt_db
# - !TRANSACTION
# defaultType: XA
# providerType: Atomikos
props:
max-connections-size-per-query: 5
kernel-executor-size: 16 # Infinite by default.
proxy-frontend-flush-threshold: 128 # The default value is 128.
proxy-opentracing-enabled: false
proxy-hint-enabled: false
sql-show: false
#sql-simple: true
check-table-metadata-enabled: false
show-process-list-enabled: false
# Proxy backend query fetch size. A larger value may increase the memory usage of ShardingSphere Proxy.
# The default value is -1, which means set the minimum value for different JDBC drivers.
proxy-backend-query-fetch-size: -1
check-duplicate-table-enabled: false
sql-comment-parse-enabled: false
proxy-frontend-executor-size: 0 # Proxy frontend executor size. The default value is 0, which means let Netty decide.
# # Available options of proxy backend executor suitable: OLAP(default), OLTP. The OLTP option may reduce time cost of writing packets to client, but it may increase the latency of SQL execution
# # if client connections are more than proxy-frontend-netty-executor-size, especially executing slow SQL.
proxy-backend-executor-suitable: OLTP
proxy-frontend-max-connections: 0 # Less than or equal to 0 means no limitation.
sql-federation-enabled: fals
#config-encrypt.yaml
schemaName: encrypt_db
dataSources:
ds_0:
url: jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC&useSSL=false&useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=8192&prepStmtCacheSqlLimit=1024
username: test
password: test
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 500
minPoolSize: 1
rules:
- !ENCRYPT
encryptors:
aes_encryptor:
type: AES
props:
aes-key-value: 123456abc
tables:
sbtest1:
columns:
pad:
plainColumn: pad
cipherColumn: pad_cipher
encryptorName: aes_encryptor
t_a:
columns:
phone:
plainColumn: phone
cipherColumn: phone_cipher
encryptorName: aes_encryptor
queryWithCipherColumn: false
测试结果:
#开启 proxy 自动加密
[ 10s ] thds: 300 tps: 2976.79 qps: 2976.79 (r/w/o: 0.00/2976.79/0.00) lat (ms,95%): 139.85 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 300 tps: 3014.71 qps: 3014.71 (r/w/o: 0.00/3014.71/0.00) lat (ms,95%): 142.39 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 300 tps: 2646.70 qps: 2646.70 (r/w/o: 0.00/2646.70/0.00) lat (ms,95%): 155.80 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 300 tps: 2599.96 qps: 2599.96 (r/w/o: 0.00/2599.96/0.00) lat (ms,95%): 158.63 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 300 tps: 2614.83 qps: 2614.83 (r/w/o: 0.00/2614.83/0.00) lat (ms,95%): 161.51 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 300 tps: 2605.03 qps: 2605.03 (r/w/o: 0.00/2605.03/0.00) lat (ms,95%): 161.51 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 300 tps: 2569.66 qps: 2569.66 (r/w/o: 0.00/2569.66/0.00) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 300 tps: 2594.76 qps: 2594.76 (r/w/o: 0.00/2594.76/0.00) lat (ms,95%): 176.73 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 300 tps: 2628.98 qps: 2628.98 (r/w/o: 0.00/2628.98/0.00) lat (ms,95%): 164.45 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 300 tps: 2527.61 qps: 2527.61 (r/w/o: 0.00/2527.61/0.00) lat (ms,95%): 179.94 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 300 tps: 2616.45 qps: 2616.45 (r/w/o: 0.00/2616.45/0.00) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 300 tps: 2587.21 qps: 2587.21 (r/w/o: 0.00/2587.21/0.00) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 300 tps: 2538.34 qps: 2538.34 (r/w/o: 0.00/2538.34/0.00) lat (ms,95%): 173.58 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 300 tps: 2640.87 qps: 2640.87 (r/w/o: 0.00/2640.87/0.00) lat (ms,95%): 153.02 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 300 tps: 2596.20 qps: 2596.20 (r/w/o: 0.00/2596.20/0.00) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00
[ 160s ] thds: 300 tps: 2523.33 qps: 2523.33 (r/w/o: 0.00/2523.33/0.00) lat (ms,95%): 170.48 err/s: 0.00 reconn/s: 0.00
[ 170s ] thds: 300 tps: 2526.18 qps: 2526.18 (r/w/o: 0.00/2526.18/0.00) lat (ms,95%): 176.73 err/s: 0.00 reconn/s: 0.00
[ 180s ] thds: 300 tps: 2630.50 qps: 2630.50 (r/w/o: 0.00/2630.50/0.00) lat (ms,95%): 158.63 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 0
write: 474703
other: 0
total: 474703
transactions: 474703 (2634.44 per sec.)
queries: 474703 (2634.44 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 180.1876s
total number of events: 474703
Latency (ms):
min: 6.09
avg: 113.80
max: 386.59
95th percentile: 167.44
sum: 54022979.31
Threads fairness:
events (avg/stddev): 1582.3433/1.82
execution time (avg/stddev): 180.0766/0.06
#直接写mysql
[ 10s ] thds: 300 tps: 12930.39 qps: 12930.39 (r/w/o: 0.00/12930.39/0.00) lat (ms,95%): 53.85 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 300 tps: 13789.39 qps: 13789.39 (r/w/o: 0.00/13789.39/0.00) lat (ms,95%): 51.02 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 300 tps: 13566.99 qps: 13566.99 (r/w/o: 0.00/13566.99/0.00) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 300 tps: 13616.97 qps: 13616.97 (r/w/o: 0.00/13616.97/0.00) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 300 tps: 12987.07 qps: 12987.07 (r/w/o: 0.00/12987.07/0.00) lat (ms,95%): 51.02 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 300 tps: 13906.23 qps: 13906.23 (r/w/o: 0.00/13906.23/0.00) lat (ms,95%): 52.89 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 300 tps: 13827.55 qps: 13827.55 (r/w/o: 0.00/13827.55/0.00) lat (ms,95%): 53.85 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 300 tps: 13890.74 qps: 13890.74 (r/w/o: 0.00/13890.74/0.00) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 300 tps: 13399.98 qps: 13399.98 (r/w/o: 0.00/13399.98/0.00) lat (ms,95%): 54.83 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 300 tps: 13765.86 qps: 13765.86 (r/w/o: 0.00/13765.86/0.00) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 300 tps: 13727.70 qps: 13727.70 (r/w/o: 0.00/13727.70/0.00) lat (ms,95%): 53.85 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 300 tps: 13908.74 qps: 13908.74 (r/w/o: 0.00/13908.74/0.00) lat (ms,95%): 53.85 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 300 tps: 13516.09 qps: 13516.09 (r/w/o: 0.00/13516.09/0.00) lat (ms,95%): 54.83 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 300 tps: 13816.87 qps: 13816.87 (r/w/o: 0.00/13816.87/0.00) lat (ms,95%): 52.89 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 300 tps: 13316.27 qps: 13316.27 (r/w/o: 0.00/13316.27/0.00) lat (ms,95%): 55.82 err/s: 0.00 reconn/s: 0.00
[ 160s ] thds: 300 tps: 13560.56 qps: 13560.56 (r/w/o: 0.00/13560.56/0.00) lat (ms,95%): 54.83 err/s: 0.00 reconn/s: 0.00
[ 170s ] thds: 300 tps: 13341.61 qps: 13341.61 (r/w/o: 0.00/13341.61/0.00) lat (ms,95%): 56.84 err/s: 0.00 reconn/s: 0.00
[ 180s ] thds: 299 tps: 13667.41 qps: 13667.41 (r/w/o: 0.00/13667.41/0.00) lat (ms,95%): 52.89 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 0
write: 2446156
other: 0
total: 2446156
transactions: 2446156 (13583.37 per sec.)
queries: 2446156 (13583.37 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 180.0830s
total number of events: 2446156
Latency (ms):
min: 0.25
avg: 22.07
max: 740.56
95th percentile: 52.89
sum: 53996198.43
Threads fairness:
events (avg/stddev): 8153.8533/109.12
execution time (avg/stddev): 179.9873/0.03