wehub-resource-sync
556 lines
16 KiB
Markdown
556 lines
16 KiB
Markdown
[MySQL](https://www.mysql.com/)是著名的开源的关系型数据库，为了使用户更快捷地访问mysql并充分利用bthread的并发能力，brpc直接支持mysql协议。示例程序：[example/mysql_c++](https://github.com/brpc/brpc/tree/master/example/mysql_c++/)
|
||
|
||
**注意**：只支持MySQL 4.1 及之后的版本的文本协议，支持事务，支持Prepared statement。目前支持的鉴权方式为mysql_native_password，使用事务的时候不支持single模式。
|
||
|
||
相比使用[libmysqlclient](https://dev.mysql.com/downloads/connector/c/)(官方client)的优势有：
|
||
|
||
- 线程安全。用户不需要为每个线程建立独立的client。
|
||
- 支持同步、异步、半同步等访问方式，能使用[ParallelChannel等](combo_channel.md)组合访问方式。
|
||
- 支持多种[连接方式](client.md#连接方式)。支持超时、backup request、取消、tracing、内置服务等一系列brpc提供的福利。
|
||
- 明确的返回类型校验，如果使用了不正确的变量接受mysql的数据类型，将抛出异常。
|
||
- 调用mysql标准库会阻塞框架的并发能力，使用本实现将能充分利用brpc框架的并发能力。
|
||
- 使用brpc实现的mysql不会造成pthread的阻塞，使用libmysqlclient会阻塞pthread [线程相关](bthread.md)，使用mysql的异步api会使编程变得很复杂。
|
||
# 访问mysql
|
||
|
||
创建一个访问mysql的Channel：
|
||
|
||
```c++
|
||
# include <brpc/policy/mysql/mysql.h>
|
||
# include <brpc/policy/mysql/mysql_authenticator.h>
|
||
# include <brpc/channel.h>
|
||
|
||
brpc::ChannelOptions options;
|
||
options.protocol = brpc::PROTOCOL_MYSQL;
|
||
options.connection_type = FLAGS_connection_type;
|
||
options.timeout_ms = FLAGS_timeout_ms /*milliseconds*/;
|
||
options.max_retry = FLAGS_max_retry;
|
||
options.auth = new brpc::policy::MysqlAuthenticator("yangliming01", "123456", "test",
|
||
"charset=utf8&collation_connection=utf8_unicode_ci");
|
||
if (channel.Init("127.0.0.1", 3306, &options) != 0) {
|
||
LOG(ERROR) << "Fail to initialize channel";
|
||
return -1;
|
||
}
|
||
```
|
||
|
||
向mysql发起命令。
|
||
|
||
```c++
|
||
// 执行各种mysql命令，可以批量执行命令如："select * from tab1;select * from tab2"
|
||
std::string command = "show databases"; // select,delete,update,insert,create,drop ...
|
||
brpc::MysqlRequest request;
|
||
if (!request.Query(command)) {
|
||
LOG(ERROR) << "Fail to add command";
|
||
return false;
|
||
}
|
||
brpc::MysqlResponse response;
|
||
brpc::Controller cntl;
|
||
channel.CallMethod(NULL, &cntl, &request, &response, NULL);
|
||
if (!cntl.Failed()) {
|
||
std::cout << response << std::endl;
|
||
} else {
|
||
LOG(ERROR) << "Fail to access mysql, " << cntl.ErrorText();
|
||
return false;
|
||
}
|
||
return true;
|
||
```
|
||
|
||
上述代码的说明：
|
||
|
||
- 请求类型必须为MysqlRequest，回复类型必须为MysqlResponse，否则CallMethod会失败。不需要stub，直接调用channel.CallMethod，method填NULL。
|
||
- 调用request.Query()传入要执行的命令，可以批量执行命令，多个命令用分号隔开。
|
||
- 依次调用response.reply(X)弹出操作结果，根据返回类型的不同，选择不同的类型接收，如：MysqlReply::Ok，MysqlReply::Error，const MysqlReply::Columnconst MysqlReply::Row等。
|
||
- 如果只有一条命令则reply为1个，如果为批量操作返回的reply为多个。
|
||
|
||
目前支持的请求操作有：
|
||
|
||
```c++
|
||
bool Query(const butil::StringPiece& command);
|
||
```
|
||
|
||
对应的回复操作：
|
||
|
||
```c++
|
||
// 返回不同类型的结果
|
||
const MysqlReply::Auth& auth() const;
|
||
const MysqlReply::Ok& ok() const;
|
||
const MysqlReply::Error& error() const;
|
||
const MysqlReply::Eof& eof() const;
|
||
// 对result set结果集的操作
|
||
// get column number
|
||
uint64_t MysqlReply::column_number() const;
|
||
// get one column
|
||
const MysqlReply::Column& MysqlReply::column(const uint64_t index) const;
|
||
// get row number
|
||
uint64_t MysqlReply::row_number() const;
|
||
// get one row
|
||
const MysqlReply::Row& MysqlReply::next() const;
|
||
// 结果集中每个字段的操作
|
||
const MysqlReply::Field& MysqlReply::Row::field(const uint64_t index) const;
|
||
```
|
||
|
||
# 事务操作
|
||
|
||
事务可以保证在一个事务中的多个RPC请求最终要么都成功，要么都失败。
|
||
|
||
```c++
|
||
rpc::Channel channel;
|
||
// Initialize the channel, NULL means using default options.
|
||
brpc::ChannelOptions options;
|
||
options.protocol = brpc::PROTOCOL_MYSQL;
|
||
options.connection_type = FLAGS_connection_type;
|
||
options.timeout_ms = FLAGS_timeout_ms /*milliseconds*/;
|
||
options.connect_timeout_ms = FLAGS_connect_timeout_ms;
|
||
options.max_retry = FLAGS_max_retry;
|
||
options.auth = new brpc::policy::MysqlAuthenticator(
|
||
FLAGS_user, FLAGS_password, FLAGS_schema, FLAGS_params);
|
||
if (channel.Init(FLAGS_server.c_str(), FLAGS_port, &options) != 0) {
|
||
LOG(ERROR) << "Fail to initialize channel";
|
||
return -1;
|
||
}
|
||
|
||
// create transaction
|
||
brpc::MysqlTransactionOptions options;
|
||
options.readonly = FLAGS_readonly;
|
||
options.isolation_level = brpc::MysqlIsolationLevel(FLAGS_isolation_level);
|
||
auto tx(brpc::NewMysqlTransaction(channel, options));
|
||
if (tx == NULL) {
|
||
LOG(ERROR) << "Fail to create transaction";
|
||
return false;
|
||
}
|
||
|
||
brpc::MysqlRequest request(tx.get());
|
||
if (!request.Query(*it)) {
|
||
LOG(ERROR) << "Fail to add command";
|
||
tx->rollback();
|
||
return false;
|
||
}
|
||
brpc::MysqlResponse response;
|
||
brpc::Controller cntl;
|
||
channel.CallMethod(NULL, &cntl, &request, &response, NULL);
|
||
if (cntl.Failed()) {
|
||
LOG(ERROR) << "Fail to access mysql, " << cntl.ErrorText();
|
||
tx->rollback();
|
||
return false;
|
||
}
|
||
// handle response
|
||
std::cout << response << std::endl;
|
||
bool rc = tx->commit();
|
||
```
|
||
|
||
# Prepared Statement
|
||
|
||
Prepared statement对于一个需要执行很多次的SQL语句，它把这个SQL语句注册到mysql-server，避免了每次请求在mysql-server端都去解析这个SQL语句，能得到性能上的提升。
|
||
|
||
```c++
|
||
rpc::Channel channel;
|
||
// Initialize the channel, NULL means using default options.
|
||
brpc::ChannelOptions options;
|
||
options.protocol = brpc::PROTOCOL_MYSQL;
|
||
options.connection_type = FLAGS_connection_type;
|
||
options.timeout_ms = FLAGS_timeout_ms /*milliseconds*/;
|
||
options.connect_timeout_ms = FLAGS_connect_timeout_ms;
|
||
options.max_retry = FLAGS_max_retry;
|
||
options.auth = new brpc::policy::MysqlAuthenticator(
|
||
FLAGS_user, FLAGS_password, FLAGS_schema, FLAGS_params);
|
||
if (channel.Init(FLAGS_server.c_str(), FLAGS_port, &options) != 0) {
|
||
LOG(ERROR) << "Fail to initialize channel";
|
||
return -1;
|
||
}
|
||
|
||
auto stmt(brpc::NewMysqlStatement(channel, "select * from tb where name=?"));
|
||
if (stmt == NULL) {
|
||
LOG(ERROR) << "Fail to create mysql statement";
|
||
return -1;
|
||
}
|
||
|
||
brpc::MysqlRequest request(stmt.get());
|
||
if (!request.AddParam("lilei")) {
|
||
LOG(ERROR) << "Fail to add name param";
|
||
return NULL;
|
||
}
|
||
|
||
brpc::MysqlResponse response;
|
||
brpc::Controller cntl;
|
||
channel->CallMethod(NULL, &cntl, &request, &response, NULL);
|
||
if (cntl.Failed()) {
|
||
LOG(ERROR) << "Fail to access mysql, " << cntl.ErrorText();
|
||
return NULL;
|
||
}
|
||
|
||
std::cout << response << std::endl;
|
||
```
|
||
|
||
|
||
|
||
# 性能测试
|
||
|
||
我在example/mysql_c++目录下面写了两个测试程序，mysql_press.cpp mysqlclient_press.cpp，mysql_go_press.go 一个是使用了brpc框架，一个是使用了的libmysqlclient访问mysql，一个是使用[go-sql-driver](https://github.com/go-sql-driver)/**go-mysql**访问mysql
|
||
|
||
启动单线程测试
|
||
|
||
##### brpc框架访问mysql（单线程）
|
||
|
||
./mysql_press -thread_num=1 -op_type=0 // insert
|
||
|
||
```
|
||
qps=3071 latency=320
|
||
qps=3156 latency=311
|
||
qps=3166 latency=310
|
||
qps=3151 latency=312
|
||
qps=3093 latency=317
|
||
qps=3146 latency=312
|
||
qps=3139 latency=313
|
||
qps=3114 latency=315
|
||
qps=3055 latency=321
|
||
qps=3135 latency=313
|
||
qps=2611 latency=376
|
||
qps=3072 latency=320
|
||
qps=3026 latency=324
|
||
qps=2792 latency=352
|
||
qps=3181 latency=309
|
||
qps=3181 latency=309
|
||
qps=3197 latency=307
|
||
qps=3024 latency=325
|
||
```
|
||
|
||
./mysql_press -thread_num=1 -op_type=1
|
||
|
||
```
|
||
qps=6414 latency=151
|
||
qps=5292 latency=182
|
||
qps=6700 latency=144
|
||
qps=6858 latency=141
|
||
qps=6915 latency=140
|
||
qps=6822 latency=142
|
||
qps=6722 latency=144
|
||
qps=6852 latency=141
|
||
qps=6713 latency=144
|
||
qps=6741 latency=144
|
||
qps=6734 latency=144
|
||
qps=6611 latency=146
|
||
qps=6554 latency=148
|
||
qps=6810 latency=142
|
||
qps=6787 latency=143
|
||
qps=6737 latency=144
|
||
qps=6579 latency=147
|
||
qps=6634 latency=146
|
||
qps=6716 latency=144
|
||
qps=6711 latency=144
|
||
```
|
||
|
||
./mysql_press -thread_num=1 -op_type=2 // update
|
||
|
||
```
|
||
qps=3090 latency=318
|
||
qps=3452 latency=284
|
||
qps=3239 latency=303
|
||
qps=3328 latency=295
|
||
qps=3218 latency=305
|
||
qps=3251 latency=302
|
||
qps=2516 latency=391
|
||
qps=2874 latency=342
|
||
qps=3366 latency=292
|
||
qps=3249 latency=302
|
||
qps=3346 latency=294
|
||
qps=3486 latency=282
|
||
qps=3457 latency=284
|
||
qps=3439 latency=286
|
||
qps=3386 latency=290
|
||
qps=3352 latency=293
|
||
qps=3253 latency=302
|
||
qps=3341 latency=294
|
||
```
|
||
|
||
##### libmysqlclient实现（单线程）
|
||
|
||
./mysqlclient_press -thread_num=1 -op_type=0 // insert
|
||
|
||
```
|
||
qps=3166 latency=313
|
||
qps=3157 latency=314
|
||
qps=2941 latency=337
|
||
qps=3270 latency=303
|
||
qps=3305 latency=300
|
||
qps=3445 latency=287
|
||
qps=3455 latency=287
|
||
qps=3449 latency=287
|
||
qps=3486 latency=284
|
||
qps=3551 latency=279
|
||
qps=3517 latency=281
|
||
qps=3283 latency=302
|
||
qps=3353 latency=295
|
||
qps=2564 latency=386
|
||
qps=3243 latency=305
|
||
qps=3333 latency=297
|
||
qps=3598 latency=275
|
||
qps=3714 latency=267
|
||
```
|
||
|
||
./mysqlclient_press -thread_num=1 -op_type=1
|
||
|
||
```
|
||
qps=8209 latency=120
|
||
qps=8022 latency=123
|
||
qps=7879 latency=125
|
||
qps=8083 latency=122
|
||
qps=8504 latency=116
|
||
qps=8112 latency=121
|
||
qps=8278 latency=119
|
||
qps=8698 latency=113
|
||
qps=8817 latency=112
|
||
qps=8755 latency=112
|
||
qps=8734 latency=113
|
||
qps=8390 latency=117
|
||
qps=8230 latency=120
|
||
qps=8486 latency=116
|
||
qps=8038 latency=122
|
||
qps=8640 latency=114
|
||
```
|
||
|
||
./mysqlclient_press -thread_num=1 -op_type=2 // update
|
||
|
||
```
|
||
qps=3583 latency=276
|
||
qps=3530 latency=280
|
||
qps=3610 latency=274
|
||
qps=3492 latency=283
|
||
qps=3508 latency=282
|
||
qps=3465 latency=286
|
||
qps=3543 latency=279
|
||
qps=3610 latency=274
|
||
qps=3567 latency=278
|
||
qps=3381 latency=293
|
||
qps=3514 latency=282
|
||
qps=3461 latency=286
|
||
qps=3456 latency=286
|
||
qps=3517 latency=281
|
||
qps=3492 latency=284
|
||
```
|
||
|
||
##### golang访问mysql（单线程）
|
||
|
||
go run test.go -thread_num=1
|
||
|
||
```
|
||
qps = 6905 latency = 144
|
||
qps = 6922 latency = 143
|
||
qps = 6931 latency = 143
|
||
qps = 6998 latency = 142
|
||
qps = 6780 latency = 146
|
||
qps = 6980 latency = 142
|
||
qps = 6901 latency = 144
|
||
qps = 6887 latency = 144
|
||
qps = 6943 latency = 143
|
||
qps = 6880 latency = 144
|
||
qps = 6815 latency = 146
|
||
qps = 6089 latency = 163
|
||
qps = 6626 latency = 150
|
||
qps = 6361 latency = 156
|
||
qps = 6783 latency = 146
|
||
qps = 6789 latency = 146
|
||
qps = 6883 latency = 144
|
||
qps = 6795 latency = 146
|
||
qps = 6724 latency = 148
|
||
qps = 6861 latency = 145
|
||
qps = 6878 latency = 144
|
||
qps = 6842 latency = 146
|
||
```
|
||
|
||
从以上测试结果看来，使用brpc实现的mysql协议和使用libmysqlclient在插入、修改、删除操作上性能是类似的，但是在查询操作看会逊色于libmysqlclient，查询的性能和golang实现的mysql类似。
|
||
|
||
##### brpc框架访问mysql（50线程）
|
||
|
||
./mysql_press -thread_num=50 -op_type=1 -use_bthread=true
|
||
|
||
```
|
||
qps=18843 latency=2656
|
||
qps=22426 latency=2226
|
||
qps=22536 latency=2203
|
||
qps=22560 latency=2193
|
||
qps=22270 latency=2226
|
||
qps=22302 latency=2247
|
||
qps=22147 latency=2225
|
||
qps=22517 latency=2228
|
||
qps=22762 latency=2176
|
||
qps=23061 latency=2162
|
||
qps=23819 latency=2070
|
||
qps=23852 latency=2077
|
||
qps=22682 latency=2214
|
||
qps=22381 latency=2213
|
||
qps=24041 latency=2069
|
||
qps=24562 latency=2022
|
||
qps=24874 latency=2004
|
||
qps=24821 latency=1988
|
||
qps=24209 latency=2073
|
||
qps=21706 latency=2281
|
||
```
|
||
|
||
##### libmysqlclient实现（50线程）
|
||
|
||
./mysql_press -thread_num=50 -op_type=1 -use_bthread=true
|
||
|
||
```
|
||
qps=23656 latency=378
|
||
qps=16190 latency=555
|
||
qps=20136 latency=445
|
||
qps=22238 latency=401
|
||
qps=22229 latency=403
|
||
qps=19109 latency=470
|
||
qps=22569 latency=394
|
||
qps=26250 latency=343
|
||
qps=28208 latency=318
|
||
qps=29649 latency=301
|
||
qps=29874 latency=301
|
||
qps=30033 latency=301
|
||
qps=25911 latency=345
|
||
qps=28048 latency=317
|
||
qps=27398 latency=329
|
||
```
|
||
|
||
##### golang访问mysql（50协程）
|
||
|
||
go run ../mysql_go_press.go -thread_num=50
|
||
|
||
```
|
||
qps = 23660 latency = 2049
|
||
qps = 23198 latency = 2160
|
||
qps = 23765 latency = 2181
|
||
qps = 23323 latency = 2149
|
||
qps = 14833 latency = 2136
|
||
qps = 23822 latency = 2853
|
||
qps = 20389 latency = 2474
|
||
qps = 23290 latency = 2151
|
||
qps = 23526 latency = 2153
|
||
qps = 21426 latency = 2613
|
||
qps = 23339 latency = 2155
|
||
qps = 25623 latency = 2084
|
||
qps = 23048 latency = 2210
|
||
qps = 20694 latency = 2423
|
||
qps = 23705 latency = 2122
|
||
qps = 23445 latency = 2125
|
||
qps = 24368 latency = 2054
|
||
qps = 23027 latency = 2175
|
||
qps = 24307 latency = 2063
|
||
qps = 23227 latency = 2096
|
||
qps = 23646 latency = 2173
|
||
```
|
||
|
||
以上是启动50并发的查询请求，看上去qps都比较相似，但是libmysqlclient延时明显低。
|
||
|
||
##### brpc框架访问mysql（100线程）
|
||
|
||
./mysql_press -thread_num=100 -op_type=1 -use_bthread=true
|
||
|
||
```
|
||
qps=26428 latency=3764
|
||
qps=26305 latency=3780
|
||
qps=26390 latency=3779
|
||
qps=26278 latency=3787
|
||
qps=26326 latency=3787
|
||
qps=26266 latency=3792
|
||
qps=26394 latency=3773
|
||
qps=26263 latency=3797
|
||
qps=26250 latency=3783
|
||
qps=26362 latency=3782
|
||
qps=26212 latency=3796
|
||
qps=26260 latency=3800
|
||
qps=24666 latency=4035
|
||
qps=25569 latency=3896
|
||
qps=26223 latency=3794
|
||
qps=25538 latency=3890
|
||
qps=20065 latency=4958
|
||
qps=23023 latency=4331
|
||
qps=25808 latency=3875
|
||
```
|
||
|
||
##### libmysqlclient实现（100线程）
|
||
|
||
./mysql_press -thread_num=50 -op_type=1 -use_bthread=true
|
||
|
||
```
|
||
qps=29467 latency=304
|
||
qps=29413 latency=305
|
||
qps=29459 latency=304
|
||
qps=29562 latency=302
|
||
qps=30657 latency=291
|
||
qps=30445 latency=295
|
||
qps=30179 latency=298
|
||
qps=30072 latency=297
|
||
qps=29802 latency=299
|
||
qps=29752 latency=301
|
||
qps=29701 latency=304
|
||
qps=29731 latency=301
|
||
qps=29622 latency=299
|
||
qps=29440 latency=304
|
||
qps=29495 latency=306
|
||
qps=29297 latency=303
|
||
qps=29626 latency=306
|
||
qps=29482 latency=300
|
||
qps=28649 latency=313
|
||
qps=29537 latency=305
|
||
qps=29634 latency=299
|
||
```
|
||
|
||
##### golang访问mysql（100协程）
|
||
|
||
go run ../mysql_go_press.go -thread_num=100
|
||
|
||
```
|
||
qps = 22108 latency = 4553
|
||
qps = 21930 latency = 4536
|
||
qps = 20653 latency = 4906
|
||
qps = 22100 latency = 4443
|
||
qps = 21091 latency = 4850
|
||
qps = 21718 latency = 4600
|
||
qps = 21444 latency = 4488
|
||
qps = 17832 latency = 5859
|
||
qps = 18296 latency = 5378
|
||
qps = 20463 latency = 4963
|
||
qps = 21611 latency = 4880
|
||
qps = 18441 latency = 5424
|
||
qps = 20731 latency = 4834
|
||
qps = 20611 latency = 4837
|
||
qps = 20188 latency = 4979
|
||
qps = 15450 latency = 5723
|
||
qps = 20927 latency = 5328
|
||
qps = 19893 latency = 5027
|
||
qps = 21080 latency = 4782
|
||
qps = 20192 latency = 4970
|
||
```
|
||
|
||
以上是启动100并发的查询请求，看上去qps都比较相似，但是libmysqlclient延时明显低。
|
||
|
||
并发调整到150的时候，mysql-server已经报错"Too many connections"。
|
||
|
||
为什么并发数50或者100的时候libmysqlclient的延时会那么低呢？因为libmysqlclient使用的IO模式为阻塞模式，我们运行的mysql_press和mysqlclient_press都是使用的bthread模式（-use_bthread=true），底层默认都是9个pthread，使用阻塞模式的libmysqlclient和mysql交互的相当于并发度是9个线程，mysql会启动9个线程，使用非阻塞模式的rpc访问mysql并发度相当于100个，mysql会启动100个线程，所以会造成mysql的频繁上线文切换。
|
||
|
||
如果将libmysqlclient的执行方式改为不使用bthread，那么100个线程的执行效果为如下：
|
||
|
||
```
|
||
qps=26919 latency=1927
|
||
qps=27155 latency=2037
|
||
qps=28054 latency=1784
|
||
qps=26738 latency=1856
|
||
qps=27807 latency=1781
|
||
qps=26734 latency=1730
|
||
qps=26562 latency=1939
|
||
qps=27473 latency=1845
|
||
qps=26677 latency=1806
|
||
qps=27369 latency=1948
|
||
qps=27955 latency=1618
|
||
qps=26574 latency=2151
|
||
qps=27343 latency=1777
|
||
qps=26705 latency=1822
|
||
qps=26668 latency=1807
|
||
qps=25347 latency=2104
|
||
qps=26651 latency=1560
|
||
qps=27815 latency=1979
|
||
qps=27221 latency=1762
|
||
qps=26516 latency=2017
|
||
```
|
||
|
||
这个结果就和brpc框架启动100个bthread访问mysql的效果类似了。
|
||
|
||
|
||
|
||
以上为我的一些简单测试，以及一些简单的分析，在低并发的情况下同步IO的效率高于异步IO，可以阅读[IO相关的内容](io.md)有更多解释，后续还将继续分析性能问题，优化协议，给出更多测试。 |