mysql简单测试代码_MySQL 简单性能测试记录

机器配置:

CPU: Intel(R) Core(TM) i5-4590 CPU @ 3.30GHz

Memory: 8GB

MySQL: 5.7.21-0ubuntu0.16.04.1 (Ubuntu)

准备工作:

sysbench --mysql-db=db_test \

--mysql-port=3306 --db-driver=mysql --mysql-user=root \

--mysql-password=root --test=oltp --oltp-table-size=10000000 \

--num-threads=100 --max-time=60 \

--oltp-test-mode=nontrx --oltp-nontrx-mode=select \

--max-requests=0 --percentile=95 prepare

1. 读取性能测试

sysbench --mysql-db=db_test \

--mysql-port=3306 --db-driver=mysql --mysql-user=root \

--mysql-password=root --test=oltp --oltp-table-size=10000000 \

--num-threads=100 --max-time=60 \

--oltp-test-mode=nontrx --oltp-nontrx-mode=select \

--max-requests=0 --percentile=95 run

结果:

OLTP test statistics:

queries performed:

read: 4248201

write: 0

other: 0

total: 4248201

transactions: 4248201 (70801.29 per sec.)

deadlocks: 0 (0.00 per sec.)

read/write requests: 4248201 (70801.29 per sec.)

other operations: 0 (0.00 per sec.)

Test execution summary:

total time: 60.0017s

total number of events: 4248201

total time taken by event execution: 5998.0902

per-request statistics:

min: 0.03ms

avg: 1.41ms

max: 266.43ms

approx. 95 percentile: 2.72ms

Threads fairness:

events (avg/stddev): 42482.0100/686.55

execution time (avg/stddev): 59.9809/0.00

可以看到差不多 7 万的 QPS

2. 写入性能测试

sysbench --mysql-db=db_test \

--mysql-port=3306 --db-driver=mysql --mysql-user=root \

--mysql-password=root --test=oltp --oltp-table-size=10000000 \

--num-threads=100 --max-time=60 \

--oltp-test-mode=nontrx --oltp-nontrx-mode=insert \

--max-requests=0 --percentile=95 run

OLTP test statistics:

queries performed:

read: 0

write: 49227

other: 0

total: 49227

transactions: 49227 (817.14 per sec.)

deadlocks: 0 (0.00 per sec.)

read/write requests: 49227 (817.14 per sec.)

other operations: 0 (0.00 per sec.)

Test execution summary:

total time: 60.2432s

total number of events: 49227

total time taken by event execution: 6022.6811

per-request statistics:

min: 29.59ms

avg: 122.35ms

max: 1234.17ms

approx. 95 percentile: 357.95ms

Threads fairness:

events (avg/stddev): 492.2700/8.26

execution time (avg/stddev): 60.2268/0.03

可以看到就只有 800 多的 QPS 了, 远远低于简单查询.

3. 更新(带索引)

sysbench --mysql-db=db_test \

--mysql-port=3306 --db-driver=mysql --mysql-user=root \

--mysql-password=root --test=oltp --oltp-table-size=10000000 \

--num-threads=100 --max-time=60 \

--oltp-test-mode=nontrx --oltp-nontrx-mode=update_key \

--max-requests=0 --percentile=95 run

结果

OLTP test statistics:

queries performed:

read: 0

write: 21942

other: 0

total: 21942

transactions: 21942 (363.68 per sec.)

deadlocks: 0 (0.00 per sec.)

read/write requests: 21942 (363.68 per sec.)

other operations: 0 (0.00 per sec.)

Test execution summary:

total time: 60.3338s

total number of events: 21942

total time taken by event execution: 6012.0994

per-request statistics:

min: 33.29ms

avg: 274.00ms

max: 2546.19ms

approx. 95 percentile: 629.71ms

Threads fairness:

events (avg/stddev): 219.4200/6.41

execution time (avg/stddev): 60.1210/0.04

4. 更新(不带 key)

sysbench --mysql-db=db_test \

--mysql-port=3306 --db-driver=mysql --mysql-user=root \

--mysql-password=root --test=oltp --oltp-table-size=10000000 \

--num-threads=100 --max-time=60 \

--oltp-test-mode=nontrx --oltp-nontrx-mode=update_nokey \

--max-requests=0 --percentile=95 run

结果:

OLTP test statistics:

queries performed:

read: 0

write: 28065

other: 0

total: 28065

transactions: 28065 (464.81 per sec.)

deadlocks: 0 (0.00 per sec.)

read/write requests: 28065 (464.81 per sec.)

other operations: 0 (0.00 per sec.)

Test execution summary:

total time: 60.3791s

total number of events: 28065

total time taken by event execution: 6034.7862

per-request statistics:

min: 32.02ms

avg: 215.03ms

max: 1426.55ms

approx. 95 percentile: 574.59ms

Threads fairness:

events (avg/stddev): 280.6500/8.18

execution time (avg/stddev): 60.3479/0.06

可以看到, update_nokey 比 update_key 略微高一些, 这也证明了对于更新频繁的表, 不要设置太多的索引的正确性.

4. 删除

sysbench --mysql-db=db_test \

--mysql-port=3306 --db-driver=mysql --mysql-user=root \

--mysql-password=root --test=oltp --oltp-table-size=10000000 \

--num-threads=100 --max-time=60 \

--oltp-test-mode=nontrx --oltp-nontrx-mode=delete \

--max-requests=0 --percentile=95 run

结果:

OLTP test statistics:

queries performed:

read: 0

write: 8062

other: 0

total: 8062

transactions: 8062 (119.45 per sec.)

deadlocks: 0 (0.00 per sec.)

read/write requests: 8062 (119.45 per sec.)

other operations: 0 (0.00 per sec.)

Test execution summary:

total time: 67.4955s

total number of events: 8062

total time taken by event execution: 6290.0099

per-request statistics:

min: 33.34ms

avg: 780.20ms

max: 16901.57ms

approx. 95 percentile: 3838.98ms

Threads fairness:

events (avg/stddev): 80.6200/12.56

execution time (avg/stddev): 62.9001/2.30

总结

操作类型

性能(QPS)

select

70801/s

insert

817/s

update

363/s

update_nokey

464/s

delete

119/s