솔라리스에서 ZFS를 사용하는 경우 데이터 디렉토리 마운트
솔라리스에서 InnoDB기반의 MySQL을 사용하려면, 성능을 위해 Direct I/O를 사용하거나 InnoDB의 버퍼링을 모두 꺼야 한다. MySQL Innodb ZFS Best Practices, Oracle® Solaris 11.4 Tunable Parameters Reference Manual - Tuning ZFS for Database Products를 읽어보자.
하지만 ZFS는 현재 Direct I/O를 지원하지 않는다. 따라서 ZFS의 recordsize 파라메터를 MySQL에서 사용하는 InnoDB 엔진의 레코드 크기인 16kB
로 설정하는 것이 매우 중요하다.
아래 예에서 데이터 디렉토리는 /usr/local/mysql/data를 사용했다.
root@wl ~ # cd /usr/local/mysql
root@wl /usr/local/mysql # rm -rf data
root@wl /usr/local/mysql # zfs create rpool/mysqldata
root@wl /usr/local/mysql # zfs set mountpoint=/usr/local/mysql/data rpool/mysqldata
root@wl /usr/local/mysql # zfs set primarycache=metadata rpool/mysqldata
root@wl /usr/local/mysql # zfs set logbias=throughput rpool/mysqldata
root@wl /usr/local/mysql # zfs set recordsize=16k rpool/mysqldata
root@wl /usr/local/mysql # zfs get primarycache,recordsize,logbias rpool/mysqldata
NAME PROPERTY VALUE SOURCE
rpool/mysqldata primarycache metadata local
rpool/mysqldata recordsize 16K local
rpool/mysqldata logbias throughput local
만약 서버가 MySQL 전용이라면, ZFS 옵션도 조절해주는 것이 좋다.
root@wl ~ # vi /etc/system
set zfs:zfs_prefetch_disable = 1
set zfs:zfs_nocacheflush = 1
아예 별도의 볼륨을 생성한 후, UFS를 사용해 포맷한 후 Direct I/O를 사용하는 것도 성능 향상에 도움된다. UFS를 사용하는 방법은 윈디하나의 솔라나라: 디스크 관리 - 디스크 추가를 읽어보자.
my.cnf 설정
MySQL 설정 파일을 생성한다. 사용 가능한 전체 옵션은 아래 페이지를 참조한다.
아래의 예에서는 캐릭터셋, 버퍼 풀 크기 설정, ZFS를 위한 버퍼 메모리 설정등이 들어가 있다.
root@wl ~ # cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
root@wl ~ # vi /etc/my.cnf
[mysqld]
# CharacterSet
collation_server=utf8mb4_unicode_ci
character_set_server=utf8mb4
# General MySQL Setting
skip-external-locking
skip-networking
memlock
large-pages
#super-large-pages
explicit_defaults_for_timestamp
expire_logs_days = 30
slow_query_log
long_query_time = 1
query_cache_type = 1
# MySQL Memory Setting
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
# InnoDB Setting
innodb_buffer_pool_size = 256M
innodb_log_file_size = 64M
#innodb_flush_method=O_DIRECT_NO_FSYNC
innodb_flush_method = O_DSYNC
innodb_doublewrite = 0
innodb_flush_log_at_trx_commit = 2
innodb_checksum_algorithm=crc32
innodb_flush_method에서, 솔라리스는 O_DIRECT 를 지원하지만 ZFS가 O_DIRECT 및 O_DIRECT_NO_FSYNC를 지원하지 않는다. 따라서 O_DSYNC 로 세팅한다. 만약 UFS 를 사용한다면 O_DIRECT_NO_FSYNC 옵션을 사용할 수 있다. (사용전에 테스트는 필수)
MySQL 설치후 설정
my.cnf설정이 완료되었으면, mysql 사용자와 dba그룹을 추가하고, 최초DB를 생성한 다음 최초 기동하자. 기본적인 보안 설정도 진행하자.
root@wl ~ # groupadd -g 201 dba
root@wl ~ # useradd -d /export/home/mysql -g dba -m -u 202 -s /bin/false mysql 1)
root@wl ~ # cd /usr/local/mysql
root@wl /usr/local/mysql # scripts/mysql_install_db --user=mysql 2)
root@wl /usr/local/mysql # support-files/mysql.server start 3)
root@wl /usr/local/mysql # cat data/*.err
...
2014-12-XX XX:XX:XX 000 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-12-XX XX:XX:XX 000 [Note] InnoDB: The InnoDB memory heap is disabled
2014-12-XX XX:XX:XX 000 [Note] InnoDB: Mutexes and rw_locks use Solaris atomic functions
2014-12-XX XX:XX:XX 000 [Note] InnoDB: Memory barrier is not used
2014-12-XX XX:XX:XX 000 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-12-XX XX:XX:XX 000 [Note] InnoDB: Not using CPU crc32 instructions
2014-12-XX XX:XX:XX 000 [Note] InnoDB: Initializing buffer pool, size = 256.0M
2014-12-XX XX:XX:XX 000 [Note] InnoDB: Completed initialization of buffer pool
2014-12-XX XX:XX:XX 000 [Note] InnoDB: Highest supported file format is Barracuda.
2014-12-XX XX:XX:XX 000 [Note] InnoDB: 128 rollback segment(s) are active.
2014-12-XX XX:XX:XX 000 [Note] InnoDB: Waiting for purge to start
2014-12-XX XX:XX:XX 000 [Note] InnoDB: 5.6.22 started; log sequence number 1625987
2014-12-XX XX:XX:XX 000 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.22-log' socket: '/tmp/mysql.sock' port: 0 MySQL Community Server (GPL)
root@wl /usr/local/mysql # bin/mysql_secure_installation 4)
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
Set root password? [Y/n]
New password:패스워드입력
Re-enter new password:패스워드입력
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n]
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n]
... Success!
By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n]
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n]
... Success!
All done! If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!
Cleaning up...
root@wl /usr/local/mysql #
1) MySQL을 실행할 계정인 mysql 사용자를 생성한다. 패스워드를 지정할 필요는 없다. 솔라리스 11에는 MySQL 사용자가 생성되어있기 때문에 이 작업을 할 필요 없다.
2) 기본 MySQL DB를 생성한다. 데이터는 /usr/local/mysql/data에 생성된다. 이후 소유자를 mysql로 변경한다.
3) mysql_secure_installation(1)을 실행하기 위해 MySQL 서버를 시작한다.
4) mysql_secure_installation(1)은 MySQL에 root 사용자의 패스워드를 설정하고, 몇가지 보안 설정을 한다. 당연해 해야할 설정이라고 생각하기 때문에, 그냥 엔터만 치고 넘어가면 된다.
MySQL 시작 및 종료
MySQL 시작 및 종료에는 mysql.server(1)을 사용한다. 이 실행 파일은 support-files디렉토리에 있다.
시작은 아래와 같이 한다.
root@wl /usr/local/mysql # support-files/mysql.server start
종료는 아래와 같이 한다.
root@wl /usr/local/mysql # support-files/mysql.server stop
메시지 한글화
한글로된 오류 메시지도 지원하며 아래와 같이 사용할 수 있지만 그다지 추천하지는 않는다.
root@wl /usr/local/mysql # vi /etc/my.cnf
[mysqld]
lc_messages_dir=/usr/local/mysql/share
lc_messages=ko_KR
root@wl /usr/local/mysql # support-files/mysql.server restart
아래와 같이 테스트해볼 수 있다.
root@wl ~ # mysql ...
mysql> select *;
ERROR 1096 (HY000): 어떤 테이블도 사용되지 않았습니다.
MySQL 테스트
MySQL 설치및 바이너리에 문제가 없는지 한번 테스트해본다.
root@wl /usr/local/mysql/mysql-test # ./mysql-test-run
실패해도 서버의 문제가 아니라, 테스트 슈트의 문제가 있을 수 있기 때문에 메시지를 보고 판단한다.
MySQL 환경설정
PATH를 정해준다.
root@wl ~ # vi /etc/profile
...
PATH=/usr/local/mysql/bin:$PATH; export PATH
root@wl ~ # . /etc/profile
데이터베이스 생성
서버에 root 계정으로 접속해 서버 상태를 확인하고, windytest 라는 데이터베이스를 생성한다.
root@wl ~ # mysql -u root -p
Enter password: 패스워드
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.22-log MySQL Community Server (GPL)
...
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> status
...
Threads: 1 Questions: 4 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 26 Queries per second avg: 0.97
mysql> CREATE DATABASE windytest;
Query OK, 1 row affected (0.00 sec)
mysql> exit
Bye
root@wl ~ #
사용자추가
windy
라는 사용자를 추가하고, 패스워드는 12345
로 정한다.windy
에게 windytest
데이터베이스에 대한 모든 권한
을 부여한다.
root@wl ~ # mysql -u root -p
Enter password:
...
mysql> CREATE USER windy IDENTIFIED BY '12345';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON windytest.* TO 'windy'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
root@wl ~ #
이후의 모든 MySQL 작업은 root 계정이 아닌 windy 계정으로 하면 된다.
테이블 생성
windy 사용자로 접속해 테이블을 생성한다.
root@wl ~ # mysql -u windy -p12345 windytest
...
mysql> status
--------------
mysql Ver 14.14 Distrib 5.6.22, for solaris10 (i386) using EditLine wrapper
...
Current database: windytest
Current user: windy@localhost
...
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
...
--------------
mysql>
create table if not exists authinfo (
seq integer unsigned auto_increment,
constraint authinfo_seq_pk primary key (seq),
userid varchar(255) not null,
constraint authinfo_userid_uq unique(userid),
pswd char(42) not null,
salt char(4) not null,
ldate datetime not null,
sdate date not null,
pdate datetime not null
);
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show tables;
+---------------------+
| Tables_in_windytest |
+---------------------+
| authinfo |
+---------------------+
1 row in set (0.01 sec)
mysql> desc authinfo;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| seq | int(10) unsigned | NO | PRI | NULL | auto_increment |
| userid | varchar(255) | NO | UNI | NULL | |
| pswd | char(32) | NO | | NULL | |
| salt | char(4) | NO | | NULL | |
| ldate | datetime | NO | | NULL | |
| sdate | date | NO | | NULL | |
| pdate | datetime | NO | | NULL | |
+--------+------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
mysql> exit
Bye
root@wl ~ #
삽입, 조회
mysql>
insert into authinfo(userid, pswd, salt, ldate, sdate, pdate)
values ('windy', sha1(concat('12345', '5xdx')), '5xdx', now(), now(), now());
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> select * from authinfo;
+-----+--------+------------------------------------------+------+---------------------+---------------------+---------------------+
| seq | userid | pswd | salt | ldate | sdate | pdate |
+-----+--------+------------------------------------------+------+---------------------+---------------------+---------------------+
| 1 | windy | 43d0010fd46d062064a6e7b52adb16ba0cb0acbb | 5xdx | 2010-12-29 20:00:00 | 2010-12-29 20:00:00 | 2010-12-29 20:00:00 |
+-----+--------+------------------------------------------+------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> select sha1('123455xdx');
+------------------------------------------+
| sha1('123455xdx') |
+------------------------------------------+
| 43d0010fd46d062064a6e7b52adb16ba0cb0acbb |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from authinfo where userid = 'windy' and pswd = sha1(concat('12345', salt));
+-----+--------+------------------------------------------+------+---------------------+---------------------+---------------------+
| seq | userid | pswd | salt | ldate | sdate | pdate |
+-----+--------+------------------------------------------+------+---------------------+---------------------+---------------------+
| 1 | windy | 43d0010fd46d062064a6e7b52adb16ba0cb0acbb | 5xdx | 2010-12-29 20:00:00 | 2010-12-29 20:00:00 | 2010-12-29 20:00:00 |
+-----+--------+------------------------------------------+------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)
MySQL Help Tables
도움말 파일을 설치한다. 이 파일은 수시로 업데이트 되기 때문에 자주 들러보는 것이 좋다.
root@wl ~/src # wget http://downloads.mysql.com/docs/fill_help_tables-5.6.sql.gz
root@wl ~/src # gunzip fill_help_tables-5.6.sql.gz
root@wl ~/src # mysql --init-command="SET sql_log_bin=0" -u root -p mysql < fill_help_tables-5.6.sql
이 스크립트는 mysql 데이터베이스에 help_topic, help_category, help_keyword, help_relation테이블을 생성할 것이다.
도움말 정보는 help명령어를 사용해 검색할 수 있다.
root@wl ~ # mysql -u root -p
...
mysql> help select
Name: 'SELECT'
Description:
Syntax:
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[PARTITION partition_list]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]
SELECT is used to retrieve rows selected from one or more tables, and
can include UNION statements and subqueries. See [HELP UNION], and
http://dev.mysql.com/doc/refman/5.6/en/subqueries.html.
The most commonly used clauses of SELECT statements are these:
o Each select_expr indicates a column that you want to retrieve. There
must be at least one select_expr.
o table_references indicates the table or tables from which to retrieve
rows. Its syntax is described in [HELP JOIN].
...
In the WHERE expression, you can use any of the functions and
operators that MySQL supports, except for aggregate (summary)
functions. See
http://dev.mysql.com/doc/refman/5.6/en/expressions.html, and
http://dev.mysql.com/doc/refman/5.6/en/functions.html.
SELECT can also be used to retrieve rows computed without reference to
any table.
URL: http://dev.mysql.com/doc/refman/5.6/en/select.html
Employees 데이터베이스
Employees 데이터베이스는 Patrick Crews, Giuseppe Maxia 이 개발한 샘플 데이터베이스로, 총 6개의 테이블, 약 400만개의 로우수를 가진 데이터베이스다. 덤프된 용량이 160MB정도다.
root@wl ~/src # wget --no-check-certificate \
"https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2"
root@wl ~/src # tar xvfj employees_db-full-1.0.6.tar.bz2
root@wl ~/src # cd employees_db
root@wl ~/src/employees_db # vi employees.sql
set storage_engine = InnoDB;
root@wl ~/src/employees_db # mysql -t < employees.sql
+-----------------------------+
| INFO |
+-----------------------------+
| CREATING DATABASE STRUCTURE |
+-----------------------------+
+------------------------+
| INFO |
+------------------------+
| storage engine: InnoDB |
+------------------------+
...
+------------------+
| INFO |
+------------------+
| LOADING salaries |
+------------------+
root@wl ~/src/employees_db # mysql -t < test_employees_sha.sql
+----------------------+
| INFO |
+----------------------+
| TESTING INSTALLATION |
+----------------------+
+--------------+------------------+------------------------------------------+
| table_name | expected_records | expected_crc |
+--------------+------------------+------------------------------------------+
| employees | 300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |
| departments | 9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |
| dept_manager | 24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |
| dept_emp | 331603 | d95ab9fe07df0865f592574b3b33b9c741d9fd1b |
| titles | 443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |
| salaries | 2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |
+--------------+------------------+------------------------------------------+
...
+--------------+---------------+-----------+
| table_name | records_match | crc_match |
+--------------+---------------+-----------+
| employees | OK | ok |
| departments | OK | ok |
| dept_manager | OK | ok |
| dept_emp | OK | ok |
| titles | OK | ok |
| salaries | OK | ok |
+--------------+---------------+-----------+
root@wl ~/src/employees_db # mysql -u root employees
...
mysql> show tables;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments |
| dept_emp |
| dept_manager |
| employees |
| salaries |
| titles |
+---------------------+
6 rows in set (0.00 sec)
mysql> select
concat(table_schema,'.',table_name) table_name,
table_rows rows,
data_length data,
index_length idx
from
information_schema.tables
where
table_schema = 'employees';
+------------------------+---------+-----------+----------+
| table_name | rows | data | idx |
+------------------------+---------+-----------+----------+
| employees.departments | 9 | 16384 | 16384 |
| employees.dept_emp | 331883 | 12075008 | 11567104 |
| employees.dept_manager | 24 | 16384 | 32768 |
| employees.employees | 300695 | 15220736 | 0 |
| employees.salaries | 2844513 | 100270080 | 36241408 |
| employees.titles | 443060 | 20512768 | 11059200 |
+------------------------+---------+-----------+----------+
...
mysql> select * from employees limit 10;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
| 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 |
| 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
| 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
| 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
| 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
| 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 |
| 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 |
| 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 |
| 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 |
+--------+------------+------------+-----------+--------+------------+
...
mysql> exit
Bye
root@wl ~/src/employees_db #
Sakila 데이터베이스
MySQL 에서 제공하는 샘플 데이터베이스다. 아래와 같은 구성으로 되어있다.
root@wl ~ # wget http://downloads.mysql.com/docs/sakila-db.tar.gz
root@wl ~ # tar xvfz sakila-db.tar.gz
root@wl ~ # cd sakila-db
root@wl ~/sakila-db # mysql -u root
mysql> source sakila-schema.sql
mysql> source sakila-data.sql
mysql> use sakila;
mysql> show tables;
+----------------------------+
| Tables_in_sakila |
+----------------------------+
| actor |
| actor_info |
| address |
| category |
| city |
| country |
| customer |
| customer_list |
| film |
| film_actor |
| film_category |
| film_list |
| film_text |
| inventory |
| language |
| nicer_but_slower_film_list |
| payment |
| rental |
| sales_by_film_category |
| sales_by_store |
| staff |
| staff_list |
| store |
+----------------------------+
23 rows in set (0.00 sec)
World 데이터베이스
MySQL 에서 제공하는 세계 데이터 베이스다. MySQL 의 공식 샘플 데이터다. InnoDB을 사용한 파일과 MyISAM 을 사용한 SQL 파일이 따로 있다. 아래는 InnoDB 를 사용한 파일을 설치하는 방법을 다룬다.
root@wl ~ # wget http://downloads.mysql.com/docs/world_innodb.sql.gz
root@wl ~ # gunzip world_innodb.sql.gz
root@wl ~ # mysql -u root
mysql> create database world;
mysql> use world;
mysql> source world_innodb.sql
mysql> desc City;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> desc Country;
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Code | char(3) | NO | PRI | | |
| Name | char(52) | NO | | | |
| Continent | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') | NO | | Asia | |
| Region | char(26) | NO | | | |
| SurfaceArea | float(10,2) | NO | | 0.00 | |
| IndepYear | smallint(6) | YES | | NULL | |
| Population | int(11) | NO | | 0 | |
| LifeExpectancy | float(3,1) | YES | | NULL | |
| GNP | float(10,2) | YES | | NULL | |
| GNPOld | float(10,2) | YES | | NULL | |
| LocalName | char(45) | NO | | | |
| GovernmentForm | char(45) | NO | | | |
| HeadOfState | char(60) | YES | | NULL | |
| Capital | int(11) | YES | | NULL | |
| Code2 | char(2) | NO | | | |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
15 rows in set (0.01 sec)
mysql> desc CountryLanguage;
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3) | NO | PRI | | |
| Language | char(30) | NO | PRI | | |
| IsOfficial | enum('T','F') | NO | | F | |
| Percentage | float(4,1) | NO | | 0.0 | |
+-------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
개요
SQL은 데이터를 물리적으로 어디에 어떻게 저장할 것인지는 정하지 않는다. 그렇기 때문에 데이터의 저장은, DBMS의 몫이다. MySQL의 주 저장 엔진인 MyISAM과 InnoDB에 대해 살펴보면, MyISAM은 각각의 테이블을 운영체제상의 파일로 저장했고, InnoDB는 운영체제상의 디렉토리에 하나의 큰 파일(Table Space)을 생성하고 이 파일 안에 하나 이상의 테이블을 저장한다. 하지만 SQL만 보면 데이터가 운영체제의 어느 파일에 들어있는지는 알 수 없다.
두 저장 엔진의 공통점이자 제약 사항중 하나는 하나의 테이블은 OS안의 하나의 파일에 저장
된다는 점이다. 파일이 가지고 있는 제약을 그대로 가지고 있게된 셈이다. 이를 극복하기 위해 파티셔닝(Partitioning, 분할)
이 나왔다. 파티셔닝이란, 하나의 테이블 데이터를 두개 이상의 파일
에 분할해 저장하는 것을 의미한다.
두개 이상의 파일
이 위치하는 마운트 지점이 달라 물리적으로 다른 디스크를 사용하고 있다면, 입출력 다중화(I/O Multiflexing)를 통해 성능향상을 도모할 수 있으며, 마운트 지점의 용량보다 더 큰 데이터를 테이블에 저장할 수 있다.
또한 한 테이블에 일정개수 이상(1억건 이상)의 데이터가 있는 경우 데이터 입/출력으로 인한 부하가 크기 때문에 파티셔닝은 필수다.
설정
파티셔닝은 MySQL의 기본기능으로, (일부러 빼고 설치한 경우를 제외하면) 특별히 설정하거나 설치해야할 것은 없다. MySQL에서 파티셔닝 지원 여부는 아래와 같이 확인할 수 있다.
mysql> SHOW VARIABLES LIKE '%partition%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+
1 row in set (0.00 sec)
mysql> SHOW PLUGINS;
+-----------------------+--------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+-----------------------+--------+--------------------+---------+---------+
...
+-----------------------+--------+--------------------+---------+---------+
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+-----------------------+--------+--------------------+---------+---------+
종류
파티셔닝을 하려면, 데이터를 어떠한 기준으로 나눌 것인지 결정해야 한다. MySQL에서 제공해주는 파티셔닝 방법은 아래와 같다. 자세한 사항은 MySQL 5.6 Reference Manual :: 18 Partitioning :: 18.2 Partitioning Types을 참조하자.
- RANGE 파티셔닝: 주어진 컬럼이 가진 '값의 범위'에 따라 파티셔닝한다. 가장 기본적인 파티셔닝 방법이다.
- LIST 파티셔닝: RANGE와 비슷하지만, '값의 범위'가 아닌 '값'을 가지고 파티셔닝 한다.
- COLUMNS 파티셔닝: MySQL 5.5 부터 지원되는 RANGE, LIST 파티셔닝의 컬럼 확장이다. 두개 이상의 컬럼및 다양한 데이터타입에 대해 파티셔닝 가능하게 해준다.
- HASH 파티셔닝: '해시 표현식'을 사용해 생성한 해시값을 통해 파티셔닝. 선형 해시인 LINEAR HASH1) 확장도 가능하다.
- KEY 파티셔닝: HASH와 비슷하다. 단 표현식이 아닌 MySQL에서 제공하는 해시 함수를 사용한다는 점이 다르다. 선형 키인 LINEAR KEY 확장도 가능하다.
1) LINEAR HASH 는 해시에 사용되는 모듈러 함수(나머지를 구하는 함수)의 지수로 2의 지수승을 사용한 해시 함수다. 쉽게 말해 일반적인 해시 함수로 시도해보다, 만족할만큼 분산 저장되지 않는다면 리니어로 시도해볼만 한다. 리니어 해시
에 대한 자세한 사항은 알고리즘 관련 책을 보자.
사용
아래의 예제는 로그 저장 용도로 사용하는 visitlog테이블을 파티셔닝하는 예제다. 2000년도 미만 데이터는 p0 파티션에, 2010년도 미만 데이터는 p1 파티션, 2020년도 미만 데이터는 p2파티션에 저장될 것이다. 나머지 데이터는 p3에 저장된다.
단 이렇게 나누면 2000년과 2010년에 걸쳐 조회하는 쿼리는, 파티셔닝 하지 않은 경우보다 속도가 느려진다. 파티셔닝할때 어떤 기준으로 파티셔닝 할 것인지 신중하게 결정하고, 파티셔닝 한 이후에는 정책상 2개 이상의 파티션을 사용하는 쿼리를 사용하지 않도록 해야한다.
mysql> CREATE TABLE visitlog (
seq INT,
loc VARCHAR(255) NOT NULL,
rdate DATETIME NOT NULL
);
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER TABLE visitlog
PARTITION BY RANGE(YEAR(rdate)) (
PARTITION p0 VALUES LESS THAN (2000),
PARTITION p1 VALUES LESS THAN (2010),
PARTITION p2 VALUES LESS THAN (2020),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into visitlog values (1, 'Solanara Root', '2000-01-01 00:00:00');
Query OK, 1 row affected (0.00 sec)
mysql> insert into visitlog values (2, 'Manual', '2010-01-01 00:00:00');
Query OK, 1 row affected (0.00 sec)
mysql> insert into visitlog values (3, 'For Test', '2020-01-01 00:00:00');
Query OK, 1 row affected (0.00 sec)
mysql> EXPLAIN PARTITIONS SELECT * FROM visitlog WHERE rdate < '2010-01-01';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | visitlog | p0,p1 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM visitlog WHERE rdate < '2010-01-01';
+------+---------------+---------------------+
| seq | loc | rdate |
+------+---------------+---------------------+
| 1 | Solanara Root | 2000-01-01 00:00:00 |
+------+---------------+---------------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE visitlog REMOVE PARTITIONING;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> EXPLAIN PARTITIONS SELECT * FROM visitlog WHERE rdate < '2010-01-01';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | visitlog | NULL | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM visitlog WHERE rdate < '2010-01-01';
+------+---------------+---------------------+
| seq | loc | rdate |
+------+---------------+---------------------+
| 1 | Solanara Root | 2000-01-01 00:00:00 |
+------+---------------+---------------------+
1 row in set (0.00 sec)
PRIMARY KEY 제약사항
MySQL에서 PRIMARY KEY 가 있는 경우 파티셔닝 하려면 파티셔닝의 키 컬럼은 반드시 PRIMARY KEY 에 포함되어야 한다.
아래와 같은 테이블이 있는 경우를 가정해보자.
mysql> CREATE TABLE visitlog (
seq INT PRIMARY KEY AUTO_INCREMENT,
loc VARCHAR(255) NOT NULL,
rdate DATETIME NOT NULL
);
Query OK, 0 rows affected (0.24 sec)
mysql> desc visitlog;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| seq | int(11) | NO | PRI | NULL | auto_increment |
| loc | varchar(255) | NO | | NULL | |
| rdate | datetime | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
이때 rdate 컬럼에 파티셔닝을 하려하면 1503 오류가 발생한다.
mysql> ALTER TABLE visitlog
PARTITION BY RANGE(TO_DAYS(rdate)) (
PARTITION p0 VALUES LESS THAN (TO_DAYS('2000-01-01')),
PARTITION p1 VALUES LESS THAN (TO_DAYS('2010-01-01')),
PARTITION p2 VALUES LESS THAN (TO_DAYS('2020-01-01')),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
이를 해결하려면, seq 와 rdate 에 PRIMARY KEY 가 모두 걸려 있어야 한다. 아래와 같이 바꿔주자.
mysql> ALTER TABLE visitlog MODIFY seq INT NOT NULL;
mysql> ALTER TABLE visitlog DROP PRIMARY KEY;
mysql> ALTER TABLE visitlog ADD PRIMARY KEY (seq, rdate);
mysql> ALTER TABLE visitlog MODIFY seq INT AUTO_INCREMENT NOT NULL;
mysql> desc visitlog;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| seq | int(11) | NO | PRI | NULL | auto_increment |
| loc | varchar(255) | NO | | NULL | |
| rdate | datetime | NO | PRI | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
이후 파티션을 생성하면 된다.
mysql> ALTER TABLE visitlog
PARTITION BY RANGE(TO_DAYS(rdate)) (
PARTITION p0 VALUES LESS THAN (TO_DAYS('2000-01-01')),
PARTITION p1 VALUES LESS THAN (TO_DAYS('2010-01-01')),
PARTITION p2 VALUES LESS THAN (TO_DAYS('2020-01-01')),
PARTITION p3 VALUES LESS THAN MAXVALUE
);