测试读写分离性能
开通读写分离功能后，事务会默认全部路由至主实例上执行。本文将以常用的MySQL压测工具Sysbench 0.5版本为例，介绍如何正确配置其参数来进行读写分离性能的测试。
前提条件
已开通读写分离功能。详细步骤，请参见开通读写分离。
已安装压测工具Sysbench 0.5。下载地址及安装步骤，请参见Sysbench的官方文档。
注意事项
建议测试读写分离的负载均衡不要用带prepare或者带事务的case。
避免因写压力过大而造成的主从延迟时间超过设定的监控检查阀值。
推荐使用如下Sysbench脚本，您可以实际情况构造具体的SQL。- [backcolor=transparent] [backcolor=transparent] function[backcolor=transparent] thread_init[backcolor=transparent] ([backcolor=transparent] thread_id[backcolor=transparent] )
- [backcolor=transparent] db_connect[backcolor=transparent]()
- [backcolor=transparent] [backcolor=transparent]end
- [backcolor=transparent] [backcolor=transparent]function[backcolor=transparent] [backcolor=transparent]event[backcolor=transparent]([backcolor=transparent]thread_id[backcolor=transparent])
- [backcolor=transparent] rs [backcolor=transparent]=[backcolor=transparent] db_query[backcolor=transparent]([backcolor=transparent]"select 1"[backcolor=transparent])
- [backcolor=transparent] [backcolor=transparent]end
设置Sysbench的参数
Sysbench oltp.lua脚本测试默认使用事务，若使用默认参数，所有SQL都会在事务中执行，即使是只读SQL也会全部路由至主库上执行。所以，使用Sysbench压测读写分离的性能时，必须根据需求设置Sysbench的参数。例如，您可以通过设置oltp-skip-trx参数可以使Sysbench运行SQL时不在事务中执行。
设置常用参数
请根据您的实际业务情况，设置如下参数值。
| 名称 | 描述 |
| test | 指定测试文件路径。 |
| mysql-host | MySQL服务器地址。 |
| mysql-port | MySQL服务器端口。 |
| mysql-user | 用户名。 |
| mysql-password | 密码。 |
| mysql-db | 测试使用数据库，需提前创建。 |
| oltp-tables-count | 建立表的个数。 |
| oltp-table-size | 每个表产生的记录数量。 |
| rand-init | 是否随机初始化数据。 |
| max-time | 压测持续时间。 |
| max-requests | 压测期间请求总数。 |
| num-threads | 并发线程数量。 |
| report-interval | 运行日志打印间隔。 |
设置事务及读写SQL相关参数
如下参数会影响事务及读写SQL，在进行读写分离性能测试时按照实际需求设置参数值。
| 名称 | 描述 |
| oltp-test-mode | 测试类型，但在Sysbench 0.5版本中此参数没有生效，可以忽略。可选参数值如下：
|
| oltp-skip-trx | 是否跳过SQL语句开头的begin和结尾的commit。可选参数值如下：
|
| oltp-read-only | 是否产生只读SQL。可选参数值如下：
|
压测示例
测试读写性能
执行如下命令，准备数据。[backcolor=transparent]sysbench [backcolor=transparent]--[backcolor=transparent]test[backcolor=transparent]=./[backcolor=transparent]tests[backcolor=transparent]/[backcolor=transparent]db[backcolor=transparent]/[backcolor=transparent]oltp[backcolor=transparent].[backcolor=transparent]lua [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]host[backcolor=transparent]=[backcolor=transparent]127.0[backcolor=transparent].[backcolor=transparent]0.1[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]port[backcolor=transparent]=[backcolor=transparent]3001[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]user[backcolor=transparent]=[backcolor=transparent]abc [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]password[backcolor=transparent]=[backcolor=transparent]abc123456 [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]db[backcolor=transparent]=[backcolor=transparent]testdb [backcolor=transparent]--[backcolor=transparent]oltp[backcolor=transparent]-[backcolor=transparent]tables[backcolor=transparent]-[backcolor=transparent]count[backcolor=transparent]=[backcolor=transparent]10[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]oltp[backcolor=transparent]-[backcolor=transparent]table[backcolor=transparent]-[backcolor=transparent]size[backcolor=transparent]=[backcolor=transparent]500000[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]report[backcolor=transparent]-[backcolor=transparent]interval[backcolor=transparent]=[backcolor=transparent]5[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]oltp[backcolor=transparent]-[backcolor=transparent]skip[backcolor=transparent]-[backcolor=transparent]trx[backcolor=transparent]=[backcolor=transparent]on [backcolor=transparent]--[backcolor=transparent]oltp[backcolor=transparent]-[backcolor=transparent]read[backcolor=transparent]-[backcolor=transparent]only[backcolor=transparent]=[backcolor=transparent]off [backcolor=transparent]--[backcolor=transparent]rand[backcolor=transparent]-[backcolor=transparent]init[backcolor=transparent]=[backcolor=transparent]on [backcolor=transparent]--[backcolor=transparent]max[backcolor=transparent]-[backcolor=transparent]requests[backcolor=transparent]=[backcolor=transparent]0[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]max[backcolor=transparent]-[backcolor=transparent]time[backcolor=transparent]=[backcolor=transparent]300[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]num[backcolor=transparent]-[backcolor=transparent]threads[backcolor=transparent]=[backcolor=transparent]100[backcolor=transparent] prepare[backcolor=transparent];
执行如下命令，运行测试。
说明：非事务的读写测试更新数据时容易出现类似ALERT: Error 1062 Duplicate entry 'xxx' for key 'PRIMARY'的错误，所以需要增加参数--mysql-ignore-errors=1062来跳过这个错误。若参数mysql-ignore-errors没有生效，则说明Sysbench版本较低，需将其升级至最新的0.5版本。
- [backcolor=transparent]sysbench [backcolor=transparent]--[backcolor=transparent]test[backcolor=transparent]=./[backcolor=transparent]tests[backcolor=transparent]/[backcolor=transparent]db[backcolor=transparent]/[backcolor=transparent]oltp[backcolor=transparent].[backcolor=transparent]lua [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]host[backcolor=transparent]=[backcolor=transparent]127.0[backcolor=transparent].[backcolor=transparent]0.1[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]port[backcolor=transparent]=[backcolor=transparent]3001[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]user[backcolor=transparent]=[backcolor=transparent]abc [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]password[backcolor=transparent]=[backcolor=transparent]abc123456 [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]db[backcolor=transparent]=[backcolor=transparent]testdb [backcolor=transparent]--[backcolor=transparent]oltp[backcolor=transparent]-[backcolor=transparent]tables[backcolor=transparent]-[backcolor=transparent]count[backcolor=transparent]=[backcolor=transparent]10[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]oltp[backcolor=transparent]-[backcolor=transparent]table[backcolor=transparent]-[backcolor=transparent]size[backcolor=transparent]=[backcolor=transparent]500000[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]report[backcolor=transparent]-[backcolor=transparent]interval[backcolor=transparent]=[backcolor=transparent]5[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]oltp[backcolor=transparent]-[backcolor=transparent]skip[backcolor=transparent]-[backcolor=transparent]trx[backcolor=transparent]=[backcolor=transparent]on [backcolor=transparent]--[backcolor=transparent]oltp[backcolor=transparent]-[backcolor=transparent]read[backcolor=transparent]-[backcolor=transparent]only[backcolor=transparent]=[backcolor=transparent]off [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]ignore[backcolor=transparent]-[backcolor=transparent]errors[backcolor=transparent]=[backcolor=transparent]1062[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]rand[backcolor=transparent]-[backcolor=transparent]init[backcolor=transparent]=[backcolor=transparent]on [backcolor=transparent]--[backcolor=transparent]max[backcolor=transparent]-[backcolor=transparent]requests[backcolor=transparent]=[backcolor=transparent]0[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]max[backcolor=transparent]-[backcolor=transparent]time[backcolor=transparent]=[backcolor=transparent]300[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]num[backcolor=transparent]-[backcolor=transparent]threads[backcolor=transparent]=[backcolor=transparent]100[backcolor=transparent] run[backcolor=transparent];
执行如下命令，清除数据。
- [backcolor=transparent]sysbench [backcolor=transparent]--[backcolor=transparent]test[backcolor=transparent]=./[backcolor=transparent]tests[backcolor=transparent]/[backcolor=transparent]db[backcolor=transparent]/[backcolor=transparent]oltp[backcolor=transparent].[backcolor=transparent]lua [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]host[backcolor=transparent]=[backcolor=transparent]127.0[backcolor=transparent].[backcolor=transparent]0.1[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]port[backcolor=transparent]=[backcolor=transparent]3001[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]user[backcolor=transparent]=[backcolor=transparent]abc [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]password[backcolor=transparent]=[backcolor=transparent]abc123456 [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]db[backcolor=transparent]=[backcolor=transparent]testdb [backcolor=transparent]--[backcolor=transparent]oltp[backcolor=transparent]-[backcolor=transparent]tables[backcolor=transparent]-[backcolor=transparent]count[backcolor=transparent]=[backcolor=transparent]10[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]oltp[backcolor=transparent]-[backcolor=transparent]table[backcolor=transparent]-[backcolor=transparent]size[backcolor=transparent]=[backcolor=transparent]500000[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]report[backcolor=transparent]-[backcolor=transparent]interval[backcolor=transparent]=[backcolor=transparent]5[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]oltp[backcolor=transparent]-[backcolor=transparent]skip[backcolor=transparent]-[backcolor=transparent]trx[backcolor=transparent]=[backcolor=transparent]on [backcolor=transparent]--[backcolor=transparent]oltp[backcolor=transparent]-[backcolor=transparent]read[backcolor=transparent]-[backcolor=transparent]only[backcolor=transparent]=[backcolor=transparent]off [backcolor=transparent]--[backcolor=transparent]rand[backcolor=transparent]-[backcolor=transparent]init[backcolor=transparent]=[backcolor=transparent]on [backcolor=transparent]--[backcolor=transparent]max[backcolor=transparent]-[backcolor=transparent]requests[backcolor=transparent]=[backcolor=transparent]0[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]max[backcolor=transparent]-[backcolor=transparent]time[backcolor=transparent]=[backcolor=transparent]300[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]num[backcolor=transparent]-[backcolor=transparent]threads[backcolor=transparent]=[backcolor=transparent]100[backcolor=transparent] cleanup[backcolor=transparent];
测试只读性能
执行如下命令，准备数据。[backcolor=transparent]sysbench [backcolor=transparent]--[backcolor=transparent]test[backcolor=transparent]=./[backcolor=transparent]tests[backcolor=transparent]/[backcolor=transparent]db[backcolor=transparent]/[backcolor=transparent]oltp[backcolor=transparent].[backcolor=transparent]lua [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]host[backcolor=transparent]=[backcolor=transparent]127.0[backcolor=transparent].[backcolor=transparent]0.1[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]port[backcolor=transparent]=[backcolor=transparent]3001[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]user[backcolor=transparent]=[backcolor=transparent]abc [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]password[backcolor=transparent]=[backcolor=transparent]abc123456 [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]db[backcolor=transparent]=[backcolor=transparent]testdb [backcolor=transparent]--[backcolor=transparent]oltp[backcolor=transparent]-[backcolor=transparent]tables[backcolor=transparent]-[backcolor=transparent]count[backcolor=transparent]=[backcolor=transparent]10[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]oltp[backcolor=transparent]-[backcolor=transparent]table[backcolor=transparent]-[backcolor=transparent]size[backcolor=transparent]=[backcolor=transparent]500000[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]report[backcolor=transparent]-[backcolor=transparent]interval[backcolor=transparent]=[backcolor=transparent]5[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]oltp[backcolor=transparent]-[backcolor=transparent]skip[backcolor=transparent]-[backcolor=transparent]trx[backcolor=transparent]=[backcolor=transparent]on [backcolor=transparent]--[backcolor=transparent]oltp[backcolor=transparent]-[backcolor=transparent]read[backcolor=transparent]-[backcolor=transparent]only[backcolor=transparent]=[backcolor=transparent]on [backcolor=transparent]--[backcolor=transparent]rand[backcolor=transparent]-[backcolor=transparent]init[backcolor=transparent]=[backcolor=transparent]on [backcolor=transparent]--[backcolor=transparent]max[backcolor=transparent]-[backcolor=transparent]requests[backcolor=transparent]=[backcolor=transparent]0[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]max[backcolor=transparent]-[backcolor=transparent]time[backcolor=transparent]=[backcolor=transparent]300[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]num[backcolor=transparent]-[backcolor=transparent]threads[backcolor=transparent]=[backcolor=transparent]100[backcolor=transparent] prepare[backcolor=transparent];
执行如下命令，运行测试。
- [backcolor=transparent]sysbench [backcolor=transparent]--[backcolor=transparent]test[backcolor=transparent]=./[backcolor=transparent]tests[backcolor=transparent]/[backcolor=transparent]db[backcolor=transparent]/[backcolor=transparent]oltp[backcolor=transparent].[backcolor=transparent]lua [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]host[backcolor=transparent]=[backcolor=transparent]127.0[backcolor=transparent].[backcolor=transparent]0.1[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]port[backcolor=transparent]=[backcolor=transparent]3001[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]user[backcolor=transparent]=[backcolor=transparent]abc [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]password[backcolor=transparent]=[backcolor=transparent]abc123456 [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]db[backcolor=transparent]=[backcolor=transparent]testdb [backcolor=transparent]--[backcolor=transparent]oltp[backcolor=transparent]-[backcolor=transparent]tables[backcolor=transparent]-[backcolor=transparent]count[backcolor=transparent]=[backcolor=transparent]10[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]oltp[backcolor=transparent]-[backcolor=transparent]table[backcolor=transparent]-[backcolor=transparent]size[backcolor=transparent]=[backcolor=transparent]500000[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]report[backcolor=transparent]-[backcolor=transparent]interval[backcolor=transparent]=[backcolor=transparent]5[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]oltp[backcolor=transparent]-[backcolor=transparent]skip[backcolor=transparent]-[backcolor=transparent]trx[backcolor=transparent]=[backcolor=transparent]on [backcolor=transparent]--[backcolor=transparent]oltp[backcolor=transparent]-[backcolor=transparent]read[backcolor=transparent]-[backcolor=transparent]only[backcolor=transparent]=[backcolor=transparent]on [backcolor=transparent]--[backcolor=transparent]rand[backcolor=transparent]-[backcolor=transparent]init[backcolor=transparent]=[backcolor=transparent]on [backcolor=transparent]--[backcolor=transparent]max[backcolor=transparent]-[backcolor=transparent]requests[backcolor=transparent]=[backcolor=transparent]0[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]max[backcolor=transparent]-[backcolor=transparent]time[backcolor=transparent]=[backcolor=transparent]300[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]num[backcolor=transparent]-[backcolor=transparent]threads[backcolor=transparent]=[backcolor=transparent]100[backcolor=transparent] run[backcolor=transparent];
执行如下命令，清除数据。
- [backcolor=transparent]sysbench [backcolor=transparent]--[backcolor=transparent]test[backcolor=transparent]=./[backcolor=transparent]tests[backcolor=transparent]/[backcolor=transparent]db[backcolor=transparent]/[backcolor=transparent]oltp[backcolor=transparent].[backcolor=transparent]lua [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]host[backcolor=transparent]=[backcolor=transparent]127.0[backcolor=transparent].[backcolor=transparent]0.1[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]port[backcolor=transparent]=[backcolor=transparent]3001[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]user[backcolor=transparent]=[backcolor=transparent]abc [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]password[backcolor=transparent]=[backcolor=transparent]abc123456 [backcolor=transparent]--[backcolor=transparent]mysql[backcolor=transparent]-[backcolor=transparent]db[backcolor=transparent]=[backcolor=transparent]testdb [backcolor=transparent]--[backcolor=transparent]oltp[backcolor=transparent]-[backcolor=transparent]tables[backcolor=transparent]-[backcolor=transparent]count[backcolor=transparent]=[backcolor=transparent]10[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]oltp[backcolor=transparent]-[backcolor=transparent]table[backcolor=transparent]-[backcolor=transparent]size[backcolor=transparent]=[backcolor=transparent]500000[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]report[backcolor=transparent]-[backcolor=transparent]interval[backcolor=transparent]=[backcolor=transparent]5[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]oltp[backcolor=transparent]-[backcolor=transparent]skip[backcolor=transparent]-[backcolor=transparent]trx[backcolor=transparent]=[backcolor=transparent]on [backcolor=transparent]--[backcolor=transparent]oltp[backcolor=transparent]-[backcolor=transparent]read[backcolor=transparent]-[backcolor=transparent]only[backcolor=transparent]=[backcolor=transparent]on [backcolor=transparent]--[backcolor=transparent]rand[backcolor=transparent]-[backcolor=transparent]init[backcolor=transparent]=[backcolor=transparent]on [backcolor=transparent]--[backcolor=transparent]max[backcolor=transparent]-[backcolor=transparent]requests[backcolor=transparent]=[backcolor=transparent]0[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]max[backcolor=transparent]-[backcolor=transparent]time[backcolor=transparent]=[backcolor=transparent]300[backcolor=transparent] [backcolor=transparent]--[backcolor=transparent]num[backcolor=transparent]-[backcolor=transparent]threads[backcolor=transparent]=[backcolor=transparent]100[backcolor=transparent] cleanup[backcolor=transparent];
展开
收起
问答分类：
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
相关问答
