MySQL Fabric 分片性能测试

苦逼的人生,开始了新一轮调研。这次是上面要看 MySQL Fabric 分片性能,好吧,开搞。

1 啥是 MySQL Fabric

其实就是一个Python进程和应用端的Connector的组合。来一张官方图:

MySQL Fabric 分片性能测试

看到了吧,Fabric就是会启动一个python daemon进程作为xml rpc服务器,应用端的Connector会自动连接这个服务器获取信息判断该连接哪个MySQL服务器。Fabric服务器还会监控各个HA组,出现问题时自动切换主从。尼玛这性能能好才有鬼呢!

2 前置条件

  • 需要 MySQL 版本 > 5.6.
  • 需要单独一台 MySQL 作为Fabric服务器的 backing store.
  • 各个受控的 MySQL 服务器要求打开: gtid_mode (GTID), bin_log (binary logging), 和 log_slave_updates ,并且 server_id 不能有冲突.
  • 需要 Python > 2.6.
  • 如果是写Java程序的话, Connector/J 版本 > 5.1.27.

3 安装 Fabric

官方文档说从1.6开始Fabric从MySQL Utilities里独立出来了,但是坑爹的是看了半天论坛又发现并没有1.6的包。最新就是1.5.6,请老实下载MySQL Utilities 1.5.6并安装吧,少年!

这里先提供一份我的 fabric.cnf 配置文件,具体就是配置 Fabric 的数据库信息,受控服务器的3种账号,客户端连接 Fabric 服务器使用 XmlRpc 或 MySQL 协议的信息。我这里把disable_authentication 打开了,取消连接 Fabric 时需要的验证,因为需要验证的情况我没试成功,总是 Permission Denie.

#fabric.cnf

[DEFAULT]

prefix =

sysconfdir = /home/will

logdir = /var/log

[storage]

address = 10.202.8.33:23308

user = root

password = root123

database = mysql_fabric

auth_plugin = mysql_native_password

connection_timeout = 6

connection_attempts = 6

connection_delay = 1

[servers]

user = root

password = root123

backup_user = root

backup_password = root123

restore_user = root

restore_password = root123

unreachable_timeout = 5

[protocol.xmlrpc]

address = 10.202.8.33:32274

threads = 5

user = admin

password = root123

disable_authentication = yes

realm =

ssl_ca =

ssl_cert =

ssl_key =

[protocol.mysql]

address = 10.202.8.33:32275

user = admin

password = root123

disable_authentication = yes

ssl_ca =

ssl_cert =

ssl_key =

[executor]

executors = 5

[logging]

level = INFO

url = file:///var/log/fabric.log

[sharding]

mysqldump_program = /usr/bin/mysqldump

mysqlclient_program = /usr/bin/mysql

[statistics]

prune_time = 3600

[failure_tracking]

notifications = 300

notification_clients = 50

notification_interval = 60

failover_interval = 0

detections = 3

detection_interval = 6

detection_timeout = 1

prune_time = 3600

[connector]

ttl = 1

4 安装数据库

做分片需要至少2台分片用服务器,1台全局服务器存不分片数据(这台的数据会同步的前2台分片服务器上),1台做back store,总共4台MySQL。记得打开: gtid_mode (GTID), bin_log (binary logging), 和 log_slave_updates。我这里打算用mysqld_multi启动多台实例,哈哈48 核,192G内存的机器。

#my.cnf

[mysqld1]

port            = 23306

socket          =/home/will/mysql/mysql.sock

datadir         =/home/will/mysql/data

pid-file        =/home/will/mysql/mysql.pid

user            =mysql

log-bin         =master-bin

log-bin-index   =master-bin.index

binlog_format   = ROW

binlog-row-image= minimal

binlog-do-db    =rcc_will

server-id       =1

symbolic-links  =0

character_set_server=utf8

skip-external-locking

innodb_flush_log_at_trx_commit = 2

default-storage-engine =innodb

slave-skip-errors=all

max_binlog_size=200M

enforce-gtid-consistency = ON

gtid-mode = ON

log_slave_updates

master_info_repository = TABLE

relay_log_info_repository = TABLE

[mysqld2]

port            = 23307

socket          = /home/will/mysql2/mysql.sock

datadir         =/home/will/mysql2/data

pid-file        =/home/will/mysql2/mysql.pid

user            =mysql

server-id       =2

log-bin         =master-bin

log-bin-index   =master-bin.index

binlog_format   = ROW

binlog-row-image= minimal

binlog-do-db    =rcc_will

symbolic-links  =0

character_set_server=utf8

skip-external-locking

innodb_flush_log_at_trx_commit = 2

default-storage-engine =innodb

slave-skip-errors=all

max_binlog_size=200M

enforce-gtid-consistency = ON

gtid-mode = ON

log_slave_updates

master_info_repository = TABLE

relay_log_info_repository = TABLE

[mysqld_back]

port            = 23308

socket          = /home/will/mysql3/mysql.sock

datadir         =/home/will/mysql3/data

pid-file        =/home/will/mysql3/mysql.pid

user            =mysql

server-id       =3

symbolic-links  =0

character_set_server=utf8

skip-external-locking

innodb_flush_log_at_trx_commit = 2

default-storage-engine =innodb

slave-skip-errors=all

max_binlog_size=200M

enforce-gtid-consistency = ON

gtid-mode = ON

log_slave_updates

master_info_repository = TABLE

relay_log_info_repository = TABLE

[mysqld_global]

port            = 23309

socket          = /home/will/mysql4/mysql.sock

datadir         =/home/will/mysql4/data

pid-file        =/home/will/mysql4/mysql.pid

user            =mysql

server-id       =4

log-bin         =master-bin

log-bin-index   =master-bin.index

binlog_format   = ROW

binlog-row-image= minimal

binlog-do-db    =rcc_will

symbolic-links  =0

character_set_server=utf8

skip-external-locking

innodb_flush_log_at_trx_commit = 2

default-storage-engine =innodb

slave-skip-errors=all

max_binlog_size=200M

enforce-gtid-consistency = ON

gtid-mode = ON

log_slave_updates

master_info_repository = TABLE

relay_log_info_repository = TABLE

5 初始化Fabric

先初始化 Backing Store 即 Fabric 服务器的 MySQL:

mysqlfabric --config=./fabric.cnf manage setup

再启动 Fabric 服务器:

mysqlfabric manage start

6 分组搞分片

Fabric的HA是按组来搞得,每个组的服务器组成主从并且可以自动切换。我这里每组就一台好了,主要是测分片嘛。

先来3个组:

mysqlfabric --config=./fabric.cnf group create my_group1

mysqlfabric --config=./fabric.cnf group create my_group2

mysqlfabric --config=./fabric.cnf group create my_group_global

再把服务器加入:

mysqlfabric --config=./fabric.cnf group add my_group1 10.202.8.33:23306

mysqlfabric --config=./fabric.cnf group add my_group2 10.202.8.33:23307

mysqlfabric --config=./fabric.cnf group add my_group_global 10.202.8.33:23309

搞起HA:

mysqlfabric --config=./fabric.cnf group promote my_group1

mysqlfabric --config=./fabric.cnf group promote my_group2

mysqlfabric --config=./fabric.cnf group promote my_group_global

激活自动切换,这步可选:

mysqlfabric --config=./fabric.cnf group activate my_group1

mysqlfabric --config=./fabric.cnf group activate my_group2

mysqlfabric --config=./fabric.cnf group activate my_group_global

定义分片的mapping(全局组,分片策略HASH):

mysqlfabric --config=./fabric.cnf sharding create_definition HASH my_group_global

给定义好的mapping(id 1)添加分片的table和列:

mysqlfabric --config=./fabric.cnf sharding add_table 1 db_will.db_users name

添加分片组:

mysqlfabric --config=./fabric.cnf sharding add_shard 1 "my_group1, my_group2" --state=ENABLED

7 创建数据库

这里需要在mysqld_global那台机器上手动create database db_will,然后就自动同步到其他服务器了。

8 Java客户端

// 数据源
FabricMySQLDataSource ds = new FabricMySQLDataSource();
ds.setServerName("10.202.8.33");
ds.setPort(32274);
ds.setDatabaseName("db_will");
ds.setFabricShardTable("db_users");
ds.setUser("root");
ds.setPassword("root123"); //创建表
JDBC4FabricMySQLConnection conn = (JDBC4FabricMySQLConnection) ds.getConnection();
Statement stat = conn.createStatement();
stat.execute(“CREATE TABLE `db_users` ( … ”); //插入数据
JDBC4FabricMySQLConnection conn = (JDBC4FabricMySQLConnection) ds.getConnection();
PreparedStatement stat = conn.prepareStatement("insert into db_users (`name`) values (?)");
for (long i = 0; i < 20 * 1024 * 1024; i++) {
conn.setShardKey(String.valueOf(i));
stat.setString(1, String.valueOf(i));
stat.executeUpdate();
} //查询数据
conn.setShardKey(imsi);
ResultSet rs = stat.executeQuery("SELECT * FROM `db_users` WHERE name=" + name);

可以看到主要是通过conn.setShardKey来实现选择分片组的。

9 性能

插入20M的数据,尼玛花了5个小时,每秒也就1000条吧。

然后增删改查基本都在20毫秒的级别。

最后同时查询也就能达到130个连接,再多就挂了,python服务器撑不住啊。

上一篇:看懂SqlServer查询计划 SQL语句优化分析


下一篇:SQL Server经典sql语句大全(转)