参考资料:
1、sysbench的使用: https://blog.51cto.com/u_15739244/5781745
1、 安装和部署
1.1 、安装sysbench
git clonehttps://github.com/akopytov/sysbench.git
cd sysbench
git checkout 0.5
yum -y install make automake libtool pkgconfig libaio-devel
yum -y install mariadb-devel ./autogen.sh
./configure
make -j
make install
1.2 配置vtgate的ddl权限
标红处"vschema_ddl_authorized_users=%"
vtgate --topo_implementation etcd2 --topo_global_server_address localhost:2379 --topo_global_root /vitess/global --log_dir /home/vitess/my-vitess-example/examples/local/vtdataroot/tmp --log_queries_to_file /home/vitess/my-vitess-example/examples/local/vtdataroot/tmp/vtgate_querylog.txt --port 15001 --grpc_port 15991 --mysql_server_port 15306 --mysql_server_socket_path /tmp/mysql.sock --cell zone1 --cells_to_watch zone1 --tablet_types_to_wait PRIMARY,REPLICA --service_map grpc-vtgateservice --pid_file /home/vitess/my-vitess-example/examples/local/vtdataroot/tmp/vtgate.pid --mysql_auth_server_impl none --vschema_ddl_authorized_users=%
备注:默认2PC模式
vtgate --topo_implementation etcd2 --topo_global_server_address localhost:2379 --topo_global_root /vitess/global --log_dir /home/vitess/my-vitess-example/examples/local/vtdataroot/tmp --log_queries_to_file /home/vitess/my-vitess-example/examples/local/vtdataroot/tmp/vtgate_querylog.txt --port 15001 --grpc_port 15991 --mysql_server_port 15306 --mysql_server_socket_path /tmp/mysql.sock --cell zone1 --cells_to_watch zone1 --tablet_types_to_wait PRIMARY,REPLICA --service_map grpc-vtgateservice --pid_file /home/vitess/my-vitess-example/examples/local/vtdataroot/tmp/vtgate.pid --mysql_auth_server_impl none --vschema_ddl_authorized_users=% --transaction_mode twopc
1.3、手动执行DDL配置
a) 将/usr/local/share/sysbench/common.lua 中相关的表的ddl执行注解掉,把创建表函数注释掉
b) 初始化
CREATE TABLE customer.sbtest1 (id bigint NOT NULL, k int(10) unsigned NOT NULL DEFAULT '0',c char(120) NOT NULL DEFAULT '',pad char(60) NOT NULL DEFAULT '',PRIMARY KEY(id),KEY `k_1` (`k`));
alter vschema on customer.sbtest1 add vindex hash(id) using hash;
create table commerce.sbtest1_seq(id bigint, next_id bigint, cache bigint, primary key(id)) comment 'vitess_sequence';
insert into commerce.sbtest1_seq(id, next_id, cache) values(0, 1000000000, 1000000000);
alter vschema on customer.sbtest1_seq add vindex hash(id) using hash;
alter vschema add sequence commerce.sbtest1_seq;
alter vschema on customer.sbtest1 add auto_increment id using commerce.sbtest1_seq;
清理动作(重新执行时清理):
drop table commerce.sbtest1_seq;
alter vschema on sbtest1 drop vindex hash;
drop table sbtest1 ;
2 、测试方法
2.1 数据准备(预热1w条)
sysbench --test='/usr/local/share/sysbench2/oltp.lua' --oltp-tables-count=1 --report-interval=10 --oltp-table-size=10000 --mysql-user=vt_allprivs --mysql-password=123 --mysql-table-engine=innodb --rand-init=on --mysql-host=127.0.0.1 --mysql-port=15306 --mysql-db=customer --max-time=300 --max-requests=0 --oltp_skip_trx=off --oltp_auto_inc=1 --oltp_secondary=0 --oltp_range_size=5 --num-threads=5 prepare
2.2 数据测试 (执行10w条)
sysbench --test='/usr/local/share/sysbench/oltp.lua' --oltp-tables-count=1 --report-interval=10 --oltp-table-size=100000 --mysql-user=vt_allprivs --mysql-password=123 --mysql-table-engine=innodb --rand-init=on --mysql-host=127.0.0.1 --mysql-port=15306 --mysql-db=customer --max-time=300 --max-requests=0 --oltp_skip_trx=off --oltp_auto_inc=1 --oltp_secondary=0 --oltp_range_size=5 --num-threads=5 run
2.3 数据清理
sysbench --test='/usr/local/share/sysbench/oltp.lua' --oltp-tables-count=1 --report-interval=10--oltp-table-size=100000 --mysql-user=vt_allprivs --mysql-password=123 --mysql-table-engine=innodb --rand-init=on --mysql-host=127.0.0.1 --mysql-port=15306 --mysql-db=customer --max-time=300 --max-requests=0 --oltp_skip_trx=off --oltp_auto_inc=1 --oltp_secondary=0 --oltp_range_size=5 --num-threads=5 cleanup