Mysql /MariaDB에서 Spider 엔진을 사용한 샤딩을 소개하겠습니다.
1편에서는 구성방법
2편에서는 sharding HA
3편에서는 성능 및 제한사항을 살펴보겠습니다.
-1. Test 환경
-
Spider node (1ea)
1) MariaDB 10.2.14 (33.333.333.33) -
Data node (2ea)
1) MariaDB 10.2.14 (11.111.111.11:3311)
2) MariaDB 10.2.14 (22.222.222.22:3311)
-2. Data Node 구성
- DB user 생성
Create user ‘spider_test’@‘%’ identified by ‘test123’;
Grant all privileges on *.* to ‘spider_test’@‘%;
Flush privileges;
2. 테이블 생성
=> sys bench 를 사용할 것이므로 sysbench의 기본테이블과 같은 구성으로 생성
create table backend.sbtest
(
id int(10) not null auto_increment,
k int(10) not null default ‘0’,
c char(120) not null default ‘’,
pad char(60) not null default ‘’,
primary key (id),
key k (k)
) engine=innodb
-3. 스파이더 엔진 구성
스파이더 노드에서 mariadb 10버전 이상부터 제공되는 spider 엔진 설치 스크립트 수행
mysql -uroot -p < ./share/install_spider.sql
1.Server 정보 등록
1)
create server backend1
foreign data wrapper mysql
Options(
HOST ’11.111.111.11’,
Database ‘backend’,
User ‘spider_test’,
Password ‘test123’,
Port 3311);
create server backend2
foreign data wrapper mysql
Options(
Host ’22.222.222.22’,
Database ‘backend’,
User ‘spider_test’,
Password ‘test123’,
Port 3311
);
2. 테이블 생성
create table backend.sbtest
(
id int(10) not null auto_increment,
k int(10) 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=‘database “backend”,table “sbtest”’
Partition by key(id)
(
Partition pr1 comment=‘srv “backend1”’,
Partition pr2 comment=‘srv “backend2”’
);
=> 파티션 pr1=> backend1 서버로
파티션 pr2 => backend2 서버에 데이터 저장
3. 생성 확인
Select * from mysql.spider_Tables;
*************************** 1. row ***************************
db_name: backend
table_name: sbtest#P#pr1
link_id: 0
priority: 1000000
server: backend1
scheme: NULL
host: NULL
port: NULL
socket: NULL
username: NULL
password: NULL
ssl_ca: NULL
ssl_capath: NULL
ssl_cert: NULL
ssl_cipher: NULL
ssl_key: NULL
ssl_verify_server_cert: 0
default_file: NULL
default_group: NULL
tgt_db_name: backend
tgt_table_name: sbtest
link_status: 1
*************************** 2. row ***************************
db_name: backend
table_name: sbtest#P#pr2
link_id: 0
priority: 1000000
server: backend2
scheme: NULL
host: NULL
port: NULL
socket: NULL
username: NULL
password: NULL
ssl_ca: NULL
ssl_capath: NULL
ssl_cert: NULL
ssl_cipher: NULL
ssl_key: NULL
ssl_verify_server_cert: 0
default_file: NULL
default_group: NULL
tgt_db_name: backend
tgt_table_name: sbtest
link_status: 1
2 rows in set (0.00 sec)
-4. Data 확인
mysql> insert into backend.sbtest select * from sbtest.sbtest1;
Query OK, 100000 rows affected (4.33 sec)
Records: 100000 Duplicates: 0 Warnings: 0
-. spider node
mysql> select count(*) from backend.sbtest;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.09 sec)
-.backend1
mysql> select count(*) from backend.sbtest;
+----------+
| count(*) |
+----------+
| 50449 |
+----------+
1 row in set (0.01 sec)
-. backend2
mysql> select count(*) from backend.sbtest;
+----------+
| count(*) |
+----------+
| 49551 |
+----------+
1 row in set (0.01 sec)
