MariaDB FEDERATED 엔진 테이블 파티셔닝

Tech Note 정보: kimdubi 님이 작성하신 글입니다.; 카테고리: [ MariaDB ]; 게시됨: 19 August 2018; 작성됨: 18 August 2018; 최종 변경: 19 August 2018; 조회수: 6965

이전 federated 엔진 관련 글

https://sarc.io/index.php/mariadb/1167-mariadb-federated

https://sarc.io/index.php/mariadb/1180-mariadb-federated-issue

-1. 테스트 위한 환경 구성

=> source db에서 분산할 테이블을 1번서버 , 2번서버에 생성 혹은 import 한 뒤

source db에서 분산할 key 값으로 federated 엔진 테이블 생성

--1번서버

CREATE TABLE test.ferderate_test_1 (
num1 int NOT NULL,
num2 int NOT NULL,
num3 int NOT NULL,
  PRIMARY KEY (num1)
) ENGINE=innodb;

insert into test.ferderate_test_1 values (0,1,2);
insert into test.ferderate_test_1 values (1,1,2);
insert into test.ferderate_test_1 values (2,1,2);
insert into test.ferderate_test_1 values (10,1,2);
insert into test.ferderate_test_1 values (11,1,2);
insert into test.ferderate_test_1 values (12,1,2);

--2번 서버

CREATE TABLE test.ferderate_test_2 (
num1 int NOT NULL,
num2 int NOT NULL,
num3 int NOT NULL,
  PRIMARY KEY (num1)
) ENGINE=innodb;

insert into test.ferderate_test_2 values (0,2,2);
insert into test.ferderate_test_2 values (1,2,2);
insert into test.ferderate_test_2 values (2,2,2);
insert into test.ferderate_test_2 values (10,2,2);
insert into test.ferderate_test_2 values (11,2,2);
insert into test.ferderate_test_2 values (12,2,2);

--source서버

MariaDB [(none)]> install soname 'ha_federatedx.so';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                          | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                            | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                                               | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                           | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                                            | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                        | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES          | YES  | YES        |
| SEQUENCE           | YES     | Generated tables filled with sequential values                                   | YES          | NO   | YES        |
| FEDERATED          | YES     | FederatedX pluggable storage engine                                              | YES          | NO   | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                               | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

=> FEDERATEDX 엔진 설치

CREATE TABLE test.ferderate_test (
num1 int NOT NULL,
num2 int NOT NULL,
num3 int NOT NULL,
  PRIMARY KEY (num1)
) ENGINE=FEDERATED
PARTITION BY range (num1)
(PARTITION p0 VALUES less than (10) ENGINE = FEDERATED connection='mysql://root:test@1번서버:3306/test/ferderate_test_1',
 PARTITION p1 VALUES less than (20) ENGINE = FEDERATED connection='mysql://root:test@2번서버:3306/test/ferderate_test_2'
 );

=> num1 컬럼 10이하 값들은 파티션 p0 으로 1번 서버에 분산 ,

10 이상 20의 값들은 파티션 p1로 2번서버에 분산되도록 federated 테이블 생성

-2. 분산 테스트 - select

(source 서버)

 MariaDB [(none)]> select * from test.ferderate_test partition (p0);
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
|    0 |    1 |    0 |
|    1 |    1 |    2 |
|    2 |    1 |    2 |
|   10 |    1 |    2 |
|   11 |    1 |    2 |
|   12 |    1 |    2 |
+------+------+------+
6 rows in set (0.01 sec)

=> p0 파티션 즉, 1번서버에서 num1 컬럼 10 미만 값들이 조회 될 것이라 기대했으나 1번서버의 해당 테이블 모든 값들이 조회됨.

아래 내용 처럼 완벽히 지원되지 않는듯함

=> FEDERATED storage engine. Partitioning of FEDERATED tables is not supported; it is not possible to create partitioned FEDERATED tables.

https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-storage-engines.html

-2.1 분산 테스트 - insert

MariaDB [(none)]> insert into test.ferderate_test values (15,1,2);
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> select * from test.ferderate_test partition (p1);
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
|    0 |    2 |    2 |
|    1 |    2 |    2 |
|    2 |    2 |    2 |
|   10 |    2 |    2 |
|   11 |    2 |    2 |
|   12 |    2 |    2 |
|   15 |    1 |    2 |
+------+------+------+
7 rows in set (0.00 sec)

MariaDB [(none)]> insert into test.ferderate_test values (3,2,2);
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> commit;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select * from test.ferderate_test partition (p1);
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
|    0 |    2 |    2 |
|    1 |    2 |    2 |
|    2 |    2 |    2 |
|   10 |    2 |    2 |
|   11 |    2 |    2 |
|   12 |    2 |    2 |
|   15 |    1 |    2 |
+------+------+------+
7 rows in set (0.00 sec)

MariaDB [(none)]> select * from test.ferderate_test partition (p0);
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
|    0 |    1 |    0 |
|    1 |    1 |    2 |
|    2 |    1 |    2 |
|    3 |    2 |    2 |
|   10 |    1 |    2 |
|   11 |    1 |    2 |
|   12 |    1 |    2 |
+------+------+------+
7 rows in set (0.00 sec)

=> insert 는 파티셔닝 된 대로 잘 분산됨을 확인

ID 생성