Mysql性能测试

或非与

已于 2023-02-27 17:44:52 修改

阅读量272

点赞数

分类专栏： mysql 文章标签： lua 数据库 mysql

于 2023-02-27 17:42:04 首次发布

本文链接：https://blog.csdn.net/qq_27517377/article/details/129246469

版权

mysql 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

#数据库性能测试
mac系统 brew install sysbench   
centos: yum install -y sysbench

sysbench help 查看版本,
mac brew 安装的软件在/usr/local/Cellar
mac的lua脚本在: /usr/local/Cellar/sysbench/1.0.20_3/share/sysbench
centos的lua脚本在: /usr/share/sysbench

#准备十万条测试数据的量

sysbench --test=/usr/local/Cellar/sysbench/1.0.20_3/share/sysbench/oltp_common.lua --table-size=100000 --mysql-host=127.0.0.1 --mysql-port=14000 --mysql-user=test --mysql-password=123456 --mysql-db=test prepare

#插入10万条数据，8个线程执行，每10秒中报告一次，执行90秒

sysbench --test=/usr/local/Cellar/sysbench/1.0.20_3/share/sysbench/oltp_insert.lua --table-size=100000 --mysql-host=127.0.0.1 --mysql-port=14000 --mysql-user=test --mysql-password=123456 --mysql-db=test --threads=8 --max-requests=0 --report-interval=10  --time=90 run

[ 10s ] thds: 8 tps: 1644.72 qps: 1644.72 (r/w/o: 0.00/1644.72/0.00) lat (ms,95%): 9.06 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 8 tps: 1806.88 qps: 1806.88 (r/w/o: 0.00/1806.88/0.00) lat (ms,95%): 7.70 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 8 tps: 1582.82 qps: 1582.82 (r/w/o: 0.00/1582.82/0.00) lat (ms,95%): 10.46 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 8 tps: 1712.34 qps: 1712.34 (r/w/o: 0.00/1712.34/0.00) lat (ms,95%): 8.74 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 8 tps: 1634.62 qps: 1634.62 (r/w/o: 0.00/1634.62/0.00) lat (ms,95%): 9.22 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 8 tps: 1450.60 qps: 1450.60 (r/w/o: 0.00/1450.60/0.00) lat (ms,95%): 10.46 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 8 tps: 1537.27 qps: 1537.27 (r/w/o: 0.00/1537.27/0.00) lat (ms,95%): 10.09 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 8 tps: 1710.13 qps: 1710.13 (r/w/o: 0.00/1710.13/0.00) lat (ms,95%): 8.90 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 8 tps: 1424.82 qps: 1424.82 (r/w/o: 0.00/1424.82/0.00) lat (ms,95%): 10.84 err/s: 0.00 reconn/s: 0.00


#只读测试 [prepare|run|cleanup]

sysbench --test=/usr/local/Cellar/sysbench/1.0.20_3/share/sysbench/oltp_read_only.lua --mysql-host=127.0.0.1 --mysql-port=14000 --mysql-user=test --mysql-password=123456 --mysql-db=test  --report-interval=10  --max-time=120 --threads=8 cleanup run

[ 10s ] thds: 7 tps: 102.29 qps: 1643.75 (r/w/o: 1438.57/0.00/205.18) lat (ms,95%): 112.67 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 7 tps: 110.39 qps: 1767.84 (r/w/o: 1546.96/0.00/220.88) lat (ms,95%): 84.47 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 7 tps: 111.82 qps: 1786.67 (r/w/o: 1563.04/0.00/223.63) lat (ms,95%): 81.48 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 7 tps: 111.07 qps: 1775.67 (r/w/o: 1553.62/0.00/222.05) lat (ms,95%): 82.96 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 7 tps: 106.02 qps: 1695.04 (r/w/o: 1482.91/0.00/212.13) lat (ms,95%): 89.16 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 7 tps: 100.10 qps: 1604.45 (r/w/o: 1404.25/0.00/200.21) lat (ms,95%): 94.10 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 7 tps: 94.49 qps: 1514.84 (r/w/o: 1325.86/0.00/188.98) lat (ms,95%): 139.85 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 7 tps: 101.92 qps: 1629.00 (r/w/o: 1425.16/0.00/203.84) lat (ms,95%): 104.84 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 7 tps: 99.28 qps: 1587.04 (r/w/o: 1388.47/0.00/198.57) lat (ms,95%): 121.08 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 7 tps: 99.31 qps: 1590.89 (r/w/o: 1392.28/0.00/198.61) lat (ms,95%): 110.66 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 7 tps: 92.60 qps: 1481.17 (r/w/o: 1295.96/0.00/185.21) lat (ms,95%): 125.52 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 7 tps: 93.41 qps: 1493.29 (r/w/o: 1306.58/0.00/186.71) lat (ms,95%): 114.72 err/s: 0.00 reconn/s: 0.00

#测试完成,删除测试数据

sysbench --test=/usr/local/Cellar/sysbench/1.0.20_3/share/sysbench/oltp_read_only.lua --mysql-host=127.0.0.1 --mysql-port=14000 --mysql-user=test --mysql-password=123456 --mysql-db=test  --report-interval=10  --max-time=120 --threads=8 cleanup

#centos

sysbench --test=/usr/share/sysbench/oltp_common.lua --table-size=100000 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=AAAaaa111 --mysql-db=test prepare

sysbench --test=/usr/share/sysbench/oltp_insert.lua --table-size=100000 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=AAAaaa111 --mysql-db=test --threads=2 --max-requests=0 --report-interval=10  --time=90 run


[ 10s ] thds: 2 tps: 333.57 qps: 333.57 (r/w/o: 0.00/333.57/0.00) lat (ms,95%): 9.22 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 2 tps: 360.90 qps: 360.90 (r/w/o: 0.00/360.90/0.00) lat (ms,95%): 8.43 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 2 tps: 370.20 qps: 370.20 (r/w/o: 0.00/370.20/0.00) lat (ms,95%): 8.58 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 2 tps: 358.40 qps: 358.40 (r/w/o: 0.00/358.40/0.00) lat (ms,95%): 8.74 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 2 tps: 375.50 qps: 375.50 (r/w/o: 0.00/375.50/0.00) lat (ms,95%): 8.28 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 2 tps: 364.70 qps: 364.70 (r/w/o: 0.00/364.70/0.00) lat (ms,95%): 8.58 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 2 tps: 366.80 qps: 366.80 (r/w/o: 0.00/366.80/0.00) lat (ms,95%): 8.58 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 2 tps: 377.10 qps: 377.10 (r/w/o: 0.00/377.10/0.00) lat (ms,95%): 8.13 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 2 tps: 377.40 qps: 377.40 (r/w/o: 0.00/377.40/0.00) lat (ms,95%): 8.13 err/s: 0.00 reconn/s: 0.00