MariaDB 10 CONNECT Engine - A Better Way to Access External Data

mariadb 에서 connect 엔진을 통해 csv 포맷의 로그 파일에 대한 빠른 접근을 지원해 주는 아티클이 있어 테스트 해보았습니다.

 

[테스트]

------------------------------------ mariadb ------------------------------------------

[root@localhost lib64]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.0.10-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

* connect 엔진 플러그인 인스톨

MariaDB [(none)]> install soname 'ha_connect';
Query OK, 0 rows affected (0.06 sec)

 

---------------------------------------------------------------------------------------------------

MariaDB [(none)]> install soname 'ha_connect';
ERROR 1126 (HY000): Can't open shared library '/usr1/maria/1010m/lib/plugin/ha_connect.so' (errno: 2, libodbc.so.1: cannot open shared object file: No such file or directory)

 

만약 connect 엔진 인스톨 시 위와 같은 에러가 난다면 리눅스 쉘에서 아래와 같이 처리해 주시면 됩니다.

 

[root@localhost maria]# yum install unixODBC
[root@localhost maria]# ln -s /usr/lib64/libodbc.so /usr/lib64/libodbc.so.1
---------------------------------------------------------------------------------------------------

 

* 설치 확인

MariaDB [(none)]> show plugins;
+-----------------------------+----------+--------------------+---------------+---------+
| Name                        | Status   | Type               | Library       | License |
+-----------------------------+----------+--------------------+---------------+---------+
.........................................................................................
.........................................................................................
.........................................................................................
| CONNECT                     | ACTIVE   | STORAGE ENGINE     | ha_connect.so | GPL     |
+-----------------------------+----------+--------------------+---------------+---------+

 

MariaDB [(none)]> drop database if exists kwon;
Query OK, 2 rows affected (0.15 sec)

 

MariaDB [(none)]> create database kwon;
Query OK, 1 row affected (0.00 sec)

 

* 테스트 테이블에 더미 데이터 적재

MariaDB [(none)]> create table kwon.test as select a.column_name from information_schema.columns as a cross join information_schema.columns as b limit 1000000;
Query OK, 1000000 rows affected (5.28 sec)

Records: 1000000  Duplicates: 0  Warnings: 0

 

* csv 로그성 파일 만들기

MariaDB [(none)]> select column_name into outfile '/data1/maria/test.csv' fields terminated by ',' from kwon.test;
Query OK, 1000000 rows affected (0.50 sec)

 

MariaDB [(none)]> exit
Bye

 

* 기본 test.csv 파일내용 중 500000 만번째 줄에 kwon 단어 추가하여 test1.csv 파일 만들기

[root@localhost maria]# sed '500000 i\kwon' /data1/maria/test.csv > /data1/maria/test1.csv

 

[root@localhost maria]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.0.10-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

* connect 엔진을 사용하여 csv 타입의 인덱스가 정의된 테이블만들기

MariaDB [(none)]> create table kwon.test1_csv (  column_name varchar(50) not null, key ( column_name ) ) engine=connect default CHARSET=UTF8 table_type=csv file_name='/data1/maria/test1.csv' header=0 quoted=1;
Query OK, 0 rows affected (1.46 sec)

 

* 테이블 정의와 동시에 바로 데이터 확인됨.

MariaDB [(none)]> select count(*) from kwon.test1_csv;

+----------+
| count(*) |
+----------+
|  1000001 | 
+----------+
1 row in set (1.13 sec)

 

* 백만건1건 중 1건 조회

MariaDB [(none)]> select * from kwon.test1_csv where column_name = 'kwon';
+-------------+
| column_name |
+-------------+
| kwon        |
+-------------+
1 row in set (0.00 sec)

 

* 실행계획

MariaDB [(none)]> explain extended select * from kwon.test1_csv where column_name = 'kwon';
+------+-------------+-----------+------+---------------+-------------+---------+-------+------+----------+--------------------------+
| id   | select_type | table     | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                    |
+------+-------------+-----------+------+---------------+-------------+---------+-------+------+----------+--------------------------+
|    1 | SIMPLE      | test1_csv | ref  | column_name   | column_name | 152     | const |    1 |   100.00 | Using where; Using index |
+------+-------------+-----------+------+---------------+-------------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

 

------------------------------------------------ mysql 과비교  -----------------------------------------

[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

mysql> create database if not exists kwon;

mysql> drop database if exists kwon;
Query OK, 0 rows affected (0.11 sec)

mysql> create database kwon;
Query OK, 1 row affected (0.00 sec)

mysql> create table kwon.test as select a.column_name from information_schema.columns as a cross join information_schema.columns as b limit 1000000;
Query OK, 1000000 rows affected (4.97 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> select column_name into outfile '/data1/mysql/test.csv' fields terminated by ',' from kwon.test;
Query OK, 1000000 rows affected (0.43 sec)

mysql> exit
Bye
[root@localhost ~]# sed '500000 i\kwon' /data1/mysql/test.csv > /data1/mysql/test1.csv

[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table kwon.test1_csv(column_name varchar(50) not null) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

 

mysql> LOAD DATA INFILE '/data1/mysql/test1.csv' INTO TABLE kwon.test1_csv FIELDS TERMINATED BY ',';
Query OK, 1000001 rows affected (3.85 sec) (
Records: 1000001  Deleted: 0  Skipped: 0  Warnings: 0

 

mysql> create index ix__test1_csv__column_name__only__01 on kwon.test1_csv(column_name);
Query OK, 0 rows affected (3.92 sec)
Records: 0  Duplicates: 0  Warnings: 0

------------------------------------------------------------------------------------------------------

mysql> select count(*) from kwon.test1_csv;
+----------+
| count(*) |
+----------+
|  1000001 |
+----------+
1 row in set (0.41 sec)

mysql> select * from kwon.test1_csv where column_name = 'kwon';
+-------------+
| column_name |
+-------------+
| kwon        |
+-------------+
1 row in set (0.00 sec)

mysql> mysql> explain extended select * from kwon.test1_csv where column_name = 'kwon';
+----+-------------+-----------+------+--------------------------------------+--------------------------------------+---------+-------+------+----------+--------------------------+
| id | select_type | table     | type | possible_keys                        | key                                  | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-----------+------+--------------------------------------+--------------------------------------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | test1_csv | ref  | ix__test1_csv__column_name__only__01 | ix__test1_csv__column_name__only__01 | 52      | const |    1 |   100.00 | Using where; Using index |
+----+-------------+-----------+------+--------------------------------------+--------------------------------------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

 

결론 
csv 형태로 로그를 저장한 후 DB로 적재하여 분석할 이슈가 있는 경우는 활용도가 높을 것으로 보입니다. 
위 테스트 결과대로 mysql 과 mariadb 단순 처리 시간 비교하여 보았습니다.

MYSQL : 0.01 sec (테이블생성시간) + 3.85 sec ( load data 적재 시간 ) + 3.92 sec ( 인덱스 생성 시간 ) = 7.78 sec
MARIADB : 1.46 sec (한방에 끝!)

 

출처 : https://mariadb.com/blog/mariadb-10-connect-engine-better-way-access-external-data

http://cafe.naver.com/mysqlpg/388

[출처] MariaDB 10 CONNECT Engine - A Better Way to Access External Data (MySQL Power Group) |작성자 더데빌