Mysql分库分表方案之spider存储引擎（一）

内容简介：当我们在Spider引擎上创建一个表时，这个表会被链接到一个远程服务器上，这个远程服务器的存储引擎可以是任何类型。具体来说，表链接是通过建立从本地通过使用

Spider 是 MariaDB 从 10.0.4 版本开始引入的存储引擎，最近更新版本是 MariaDB 10.3.4 。 Spider 内置分片( sharding )特性，还支持分区( partition )和XA两阶段分布式事务，并且可以把不同实例上的表当作一个实例上的表处理。

2.Spider基本实现原理

当我们在Spider引擎上创建一个表时，这个表会被链接到一个远程服务器上，这个远程服务器的存储引擎可以是任何类型。具体来说，表链接是通过建立从本地 Mariadb 服务器到远程Mariadb服务器的连接来实现的。链接对于同一个事务的所有表都是共享的。

3. 使用

3.1 基本使用

通过使用 Create table 的 Comment 和/或者 Connection 子句传递远程服务器的信息，我们可以创建一个 spider 存储引擎格式的表。

比如远程服务器上存在下面这个表(这个远程节点是用 Mysql Sandbox 工具创建的，这个工具能够很容易的创建多个实例):

node1 >CREATE TABLE s(
  id INT NOT NULL AUTO_INCREMENT,
  code VARCHAR(10),
  PRIMARY KEY(id));
复制代码

在本地服务器上，可以使用如下命令创建一个表:

CREATE TABLE s(
  id INT NOT NULL AUTO_INCREMENT,
  code VARCHAR(10),
  PRIMARY KEY(id)
)
ENGINE=SPIDER 
COMMENT 'host "127.0.0.1", user "msandbox", password "msandbox", port "8607"';
复制代码

现在我们就可以在本地服务器( spider 服务器)上插入一条记录，这条记录最终会被保存到相应的远程服务器上:

INSERT INTO s(code) VALUES ('a');

node1 > SELECT * FROM s;
+----+------+
| id | code |
+----+------+
|  1 | a    |
+----+------+
复制代码

进阶示例

准备

使用sysbench工具，准备1000万条数据：

/usr/local/skysql/sysbench/bin/sysbench --test=oltp  --db-driver=mysql  --mysql-table-engine=innodb --mysql-user=skysql --mysql-password=skyvodka --mysql-host=192.168.0.202 --mysql-port=5054 --oltp-table-size=10000000  --mysql-db=test  prepare
复制代码

我们首先进行第一个基准测试，来检查一个刚初始化的单一节点的性能:

/usr/local/skysql/sysbench/bin/sysbench --test=oltp  --db-driver=mysql  --mysql-table-engine=innodb --mysql-user=skysql --mysql-password=skyvodka --mysql-host=192.168.0.202 --mysql-port=5054 --mysql-db=test --oltp-table-size=10000000 --num-threads=4 --max-requests=100000 --oltp-read-only=on run
复制代码

测试结果如下:

sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 4

Doing OLTP test.
Running mixed OLTP test
Doing read-only test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 100000
Threads started!
Done.

OLTP test statistics:
    queries performed:
        read:                            1400196
        write:                           0
        other:                           200028
        total:                           1600224
    transactions:                        100014 (1095.83 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1400196 (15341.58 per sec.)
    other operations:                    200028 (2191.65 per sec.)

Test execution summary:
    total time:                          91.2681s
    total number of events:              100014
    total time taken by event execution: 364.3693
    per-request statistics:
         min:                                  1.85ms
         avg:                                  3.64ms
         max:                                 30.70ms
         approx.  95 percentile:               4.66ms

Threads fairness:
    events (avg/stddev):           25003.5000/84.78
    execution time (avg/stddev):   91.0923/0.00
复制代码

接下来为了测试方便，通过别名定义一个访问 mariadb 或者 mysql 客户端的快捷方式：

alias backend1='/usr/local/skysql/mysql-client/bin/mysql  --user=skysql --password=skyvodka --host=192.168.0.202 --port=5054'
alias backend2='/usr/local/skysql/mysql-client/bin/mysql  --user=skysql --password=skyvodka --host=192.168.0.203 --port=5054' 
alias spider1='/usr/local/skysql/mysql-client/bin/mysql  --user=skysql --password=skyvodka --host=192.168.0.201 --port=5054' 

复制代码

在所有 backend 节点上重复创建存储数据所用的数据库和空表

backend1 << EOF 
CREATE DATABASE backend;
CREATE TABLE backend.sbtest (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  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 (k)
) ENGINE=InnoDB;
EOF

backend2 << EOF 
CREATE DATABASE backend;
CREATE TABLE backend.sbtest (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  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 (k)
) ENGINE=InnoDB;
EOF
复制代码

Spider支持的不同集群模式

1. Federation Setup模式

在 spider1 节点上定义 backend 服务器配置信息
在 spider1 节点上创建与 backend 服务器链接的表，通过 comment 定义服务器，表的存储引擎要为 spider

spider1 << EOF
CREATE SERVER backend 
  FOREIGN DATA WRAPPER mysql 
OPTIONS( 
  HOST '192.168.0.202', 
  DATABASE 'test',
  USER 'skysql',
  PASSWORD 'skyvodka',
  PORT 5054
);

CREATE  TABLE test.sbtest
(
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  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 (k)
) ENGINE=spider COMMENT='wrapper "mysql",srv "backend"';
SELECT * FROM test.sbtest LIMIT 10;
EOF

复制代码

在没有连接池或者 mariadb 线程池的情况下， HaProxy 和 spider 一直在保护TCP套接字溢出，而没有进行特定的TCP调整。在现实中，对于调优良好的TCP堆栈或线程池，曲线不应如此突然地减少到0。请参阅[Mariadb线程池](Refer to the MariaDB Thread Pool to explore this feature)以了解此功能.

2. 分片模式

在spider上配置backend1，类似Federation模式中的配置backend
在spider上配置backend2，同上
创建需要分片的表，与Federation模式的区别时增加了分区配置

#spider1 << EOF
CREATE SERVER backend1 
  FOREIGN DATA WRAPPER mysql 
OPTIONS( 
  HOST '192.168.0.202', 
  DATABASE 'backend',
  USER 'skysql',
  PASSWORD 'skyvodka',
  PORT 5054
);
CREATE SERVER backend2 
  FOREIGN DATA WRAPPER mysql 
OPTIONS( 
  HOST '192.168.0.203', 
  DATABASE 'backend',
  USER 'skysql',
  PASSWORD 'skyvodka',
  PORT 5054
);
CREATE DATABASE IF NOT EXISTS backend;
CREATE  TABLE backend.sbtest
(
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  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 (k)
) ENGINE=spider COMMENT='wrapper "mysql", table "sbtest"'
 PARTITION BY KEY (id) 
(
 PARTITION pt1 COMMENT = 'srv "backend1"',
 PARTITION pt2 COMMENT = 'srv "backend2"' 
) ;
EOF
复制代码

性能测试

把最初的sysbench表里的数据拷贝到spider表里

#/usr/local/skysql/mariadb/bin/mysqldump  --user=skysql --password=skyvodka --host=192.168.0.202 --port=5054 --no-create-info test sbtest | spider1 backend 

#backend2 -e"select count(*) from backend.sbtest;"
+----------+
| count(*) |
+----------+
|  3793316 |
+----------+
#backend1 -e"select count(*) from backend.sbtest;"
+----------+
| count(*) |
+----------+
|  6206684 |
+----------+
复制代码

我们观察到分区的一个常见问题是基于分区键散列算法，造成的后端之间的数据分布不均匀。

使用更少的查询进行基准测试

#/usr/local/skysql/sysbench/bin/sysbench --test=oltp  --db-driver=mysql  --mysql-table-engine=innodb --mysql-user=skysql --mysql-password=skyvodka --mysql-host=192.168.0.201 --mysql-port=5054 --mysql-db=backend --mysql-engine-trx=yes --oltp-table-size=10000000 --num-threads=4 --max-requests=100 --oltp-read-only=on run
复制代码

结果如下：

OLTP test statistics:
    queries performed:
        read:                            1414
        write:                           0
        other:                           202
        total:                           1616
    transactions:                        101    (22.95 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1414   (321.30 per sec.)
    other operations:                    202    (45.90 per sec.)

Test execution summary:
    total time:                          4.4009s
    total number of events:              101
    total time taken by event execution: 17.2960
    per-request statistics:
         min:                                114.48ms
         avg:                                171.25ms
         max:                                200.98ms
         approx.  95 percentile:             195.12ms

Threads fairness:
    events (avg/stddev):           25.2500/0.43
    execution time (avg/stddev):   4.3240/0.04
复制代码

响应时间减少到了0.04。正如预期，因为查询延迟是由多个网络往返增加的，并且条件下推尚未实现。sysbench执行了大量的范围查询。现在考虑一下，这个范围查询可能是一个比较差的优化查询。

我们需要提高并发度以获得更好的吞吐量

以上所述就是小编给大家介绍的《Mysql分库分表方案之spider存储引擎（一）》，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对码农网的支持！

查看所有标签

猜你喜欢:

本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。

码农书籍

The Seasoned Schemer

Daniel P. Friedman、Matthias Felleisen / The MIT Press / 1995-12-21 / USD 38.00

drawings by Duane Bibbyforeword and afterword by Guy L. Steele Jr.The notion that "thinking about computing is one of the most exciting things the human mind can do" sets both The Little Schemer (form......一起来看看《The Seasoned Schemer》这本书的介绍吧!

码农工具