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

栏目: 数据库 · Mysql · 发布时间: 5年前

内容简介：当我们在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    |
+----+------+
复制代码

进阶示例

准备

  1. 使用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
复制代码
  1. 我们首先进行第一个基准测试，来检查一个刚初始化的单一节点的性能:
/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
复制代码
  1. 接下来为了测试方便，通过别名定义一个访问 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' 

复制代码
  1. 在所有 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模式

Mysql分库分表方案之spider存储引擎（一）
  1. 在 spider1 节点上定义 backend 服务器配置信息
  2. 在 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

复制代码
Mysql分库分表方案之spider存储引擎（一）

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

2. 分片模式

Mysql分库分表方案之spider存储引擎（一）
  1. 在spider上配置backend1，类似Federation模式中的配置backend
  2. 在spider上配置backend2，同上
  3. 创建需要分片的表，与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
复制代码

性能测试

  1. 把最初的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 |
+----------+
复制代码

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

  1. 使用更少的查询进行基准测试
#/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存储引擎（一）

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

查看所有标签

猜你喜欢:

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

码农书籍
算法分析导论

算法分析导论

（美）Robert Sedgewick、（法）Philippe Flajolet / 冯舜玺、李学武、裴伟东、等其他 / 机械工业出版社 / 2006-4 / 38.00元

本书阐述了用于算法数学分析的主要方法，所涉及的材料来自经典数学课题，包括离散数学、初等实分析、组合数学，以及来自经典的计算机科学课题，包括算法和数据结构，本书内容集中覆盖基础、重要和有趣的算法，前面侧重数学，后面集中讨论算法分析的应用，重点的算法分的的数学方法。每章包含大量习题以及参考文献，使读者可以更深入地理解书中的内容。 本书适合作为高等院校数学、计算机科学以及相关专业的本科生和研究生的......一起来看看 《算法分析导论》 这本书的介绍吧!

码农工具
Base64 编码/解码
Base64 编码/解码

Base64 编码/解码

URL 编码/解码
URL 编码/解码

URL 编码/解码

RGB CMYK 转换工具
RGB CMYK 转换工具

RGB CMYK 互转工具

  • New
  • 文章
  • 话题
  • 教程
    • 关注 码农网 公众号
    版权所有，保留一切权利！© 2018-2024 码农网 粤ICP备17054400号-3