MySQL MyISAM Questions (5.5.8-enterprise)
|
2011-04-05 |
v1.0 |
|
|
|
|
|
|
|
|
|
1.
MyISAM
A.
MyISAM
Storage Engine 을 이용할 때 생겼던 궁금증에 대해 정리했다.
2.
Questions about MyISAM
A.
MyISAM 구성요소중 하나라도 손실되는 경우 어떤 일이 일어나는가?
B.
Merge
Table 생성시 어떤 파일이 만들어지는가? 만들어진
파일중 일부가 손실되는 경우 어떤일이 일어나는가?
C.
Locked
Table 을 압축할 수 있는가? 또
압축한 테이블에 INSERT 가 가능한가?
D.
파일복사를 통해 다른 MySQL 에 데이터 이전이 가능한가?
E.
MyISAM
Table 생성시 INDEX 가 생성이
되는가?
F.
Lock
table 시 다른 세션에서 Unlock
tables 로 해제가 가능한지? flush 를 이용해
Unlock 이 가능한지 확인
3.
Answers
A.
MyISAM 구성요소중 하나라도 손실되는 경우 어떤 일이 일어나는가?
|
mysql> create
table sample(id integer) engine=myisam; Query OK, 0 rows
affected (0.01 sec) 경로를 확인한결과
frm, MYI, MYD 가 같이 생성된 걸 확인하였다. -rw-rw---- 1
mysql mysql 8556 Apr 5 15:26 sample.frm -rw-rw---- 1
mysql mysql 1024 Apr 5 15:30 sample.MYI -rw-rw---- 1
mysql mysql 14 Apr 5 15:30 sample.MYD 샘플 데이터를 두개 넣고 변화를 관찰했다. 삭제에 따른 변화를 확인하였다. <<rm 이 삭제되었을 때>> frm 이 삭제된 후에도 select, insert 가 가능하다. mysql> flush
table sample; Query OK, 0 rows
affected (0.00 sec) mysql> select
* from sample; ERROR 1146 (42S02): Table 'mysql.sample'
doesn't exist flush 후 select 실시시 해당 테이블이 존재하지 않는다고
나온다. mysql> show
tables; +---------------------------+ |
Tables_in_mysql | +---------------------------+ |
columns_priv | | db | | event | | func | |
general_log | |
help_category | |
help_keyword | |
help_relation | | help_topic | | host | |
ndb_binlog_index | | plugin | | proc | | procs_priv | |
proxies_priv | | servers | | slow_log | |
tables_priv | | time_zone | |
time_zone_leap_second | |
time_zone_name | |
time_zone_transition | |
time_zone_transition_type | | user | +---------------------------+ 24 rows in set
(0.00 sec) show tables 로 확인했을 때 테이블 자체가 없다고 나온다. 이 일련의
과정중 alert log 에는 아무런 특이사항이 남지 않았다. 사전에 백업해 놓았던
frm 파일을 삭제를 실시했던 위치에 복원후 아래의 명령을 실행하였다. mysql> flush
tables; Query OK, 0 rows
affected (0.00 sec) mysql> desc
sample; +-------+---------+------+-----+---------+-------+ | Field |
Type | Null | Key | Default | Extra
| +-------+---------+------+-----+---------+-------+ | id | int(11) | YES |
| NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set
(0.00 sec) mysql> select
* from sample; +------+ | id | +------+ | 3 | | 2 | +------+ 2 rows in set
(0.00 sec) 테스트 전과 동일하게 복원이 되었다. <<MYI 가 삭제되었을 때>> mysql> select
* from sample; +------+ | id | +------+ | 3 | | 2 | | 4 | +------+ 3 rows in set
(0.00 sec) mysql> insert
into sample values(5); Query OK, 1 row
affected (0.00 sec) mysql> select
* from sample; +------+ | id | +------+ | 3 | | 2 | | 4 | | 5 | +------+ 4 rows in set
(0.00 sec) mysql> flush
table sample; Query OK, 0 rows
affected (0.01 sec) mysql> desc
sample; ERROR 1017 (HY000): Can't find file:
'sample' (errno: 2) 백업본을 가지고 삭제했던 MYI 만 복원하였다. 아래의 결과를 보면 frm, MYD 는 삭제 전의 파일이니 데이터를 더 가지고 있어야 하나 백업당시의 데이터밖에 보이지 않는다. (frm 삭제의 경우 데이터 마지막 변경 시점까지 보인다.) mysql> desc
sample; +-------+---------+------+-----+---------+-------+ | Field |
Type | Null | Key | Default | Extra
| +-------+---------+------+-----+---------+-------+ | id | int(11) | YES |
| NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set
(0.00 sec) mysql> select
* from sample; +------+ | id | +------+ | 3 | | 2 | +------+ 2 rows in set
(0.00 sec) 위 결과는 flush 해도
똑같이 나온다. <<MYD 가 삭제되었을 때>> mysql> insert
into sample values(4); Query OK, 1 row
affected (0.00 sec) mysql> select
* from sample; +------+ | id | +------+ | 3 | | 2 | | 4 | +------+ 3 rows in set
(0.00 sec) mysql> flush
table sample; Query OK, 0 rows
affected (0.00 sec) mysql> desc
sample; ERROR 29 (HY000): File './mysql/sample.MYD'
not found (Errcode: 2) 삭제한 MYD 복원후
데이터를 조회해 보았다. MYI 와 마찬가지로 데이터가 백업시점까지밖에 보이지 않는다. mysql> desc
sample; +-------+---------+------+-----+---------+-------+ | Field |
Type | Null | Key | Default | Extra
| +-------+---------+------+-----+---------+-------+ | id | int(11) | YES |
| NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set
(0.00 sec) mysql> select
* from sample; +------+ | id | +------+ | 3 | | 2 | +------+ 2 rows in set
(0.00 sec) |
정리하면 다음과 같다.
<손실>
frm : ERROR
1146 (42S02): Table 'mysql.sample' doesn't exist
MYI : ERROR
1017 (HY000): Can't find file: 'sample' (errno: 2)
MYD : ERROR 29 (HY000): File
'./mysql/sample.MYD' not found (Errcode: 2)
위 모두의 경우에 대해 flush table 을 하기 전엔
이상상황이 감지되지 않았다. 또한 alert log 에도
아무런 메시지가 남지 않았다. 에러를 감지하는 방법으로는 위와같이 쿼리에 대한 에러로 확인하는 방법이다. frm 자체가 삭제된 경우에는 해당 테이블이 아예 없는 테이블로 인지가 되나
MYI, MYD 의 경우에는 타겟 테이블에 문제가 생긴 것으로 인지된다.
B.
Merge
Table 생성시 어떤 파일이 만들어지는가? 만들어진
파일중 일부가 손실되는 경우 어떤일이 일어나는가?
|
기존의 sample table 을 그대로 이용하되 sample1 테이블을
추가로 만들어 sample2 (storage engine : merge) 를 생성했다. mysql> insert into sample1 values(1); Query OK, 1 row affected (0.00 sec) mysql> insert into sample1 values(5); Query OK, 1 row affected (0.00 sec) mysql> select * from sample1; +------+ | id | +------+ | 1 | | 5 | +------+ 2 rows in set (0.00 sec) mysql> create table sample2 -> (id
integer) ->
engine=merge union=(sample,sample1); Query OK, 0 rows affected (0.01 sec) mysql> select * from sample2; +------+ | id | +------+ | 3 | | 2 | | 4 | | 1 | | 5 | +------+ 5 rows in set (0.00 sec) *MySQL 5.5.8 에서 engine 선택을 하지 않으면 default 로 InnoDB 를 사용한다. 때문에 innodb 와 myisam 을 멤버로 merge table 을 생성하는 경우가 발생한다. 이
경우 다음과 같은 에러가 반환된다. mysql> select * from sample2; ERROR 1168 (HY000): Unable to open underlying table
which is differently defined or of non-MyISAM type or doesn't exist merge table 에 대한 생성결과로
다음의 파일이 생겼다. -rw-rw---- 1 mysql mysql 15 Apr
5 16:51 sample2.MRG -rw-rw---- 1 mysql mysql 8556 Apr
5 16:51 sample2.frm 위
파일을 먼저 백업후 다음의 경우를 확인하였다. <<frm 이 삭제된 경우>> flush 후 조회를 시도하자 다음의
에러가 발생했다. (flush 전에는 정상조회된다.) mysql> select * from sample2; ERROR 1146 (42S02):
Table 'mysql.sample2' doesn't exist <<MRG 가 삭제된 경우>> flush 후 조회한 결과 다음과
같이 에러가 발생했다. mysql> select * from sample2; ERROR 1017 (HY000):
Can't find file: 'sample2' (errno: 2) MRG 파일을 복원후 (flush 없이) 정상 조회됨을 확인했다. |
C.
Locked
Table 을 압축할 수 있는가? 또
압축한 테이블에 INSERT 가 가능한가?
|
mysql> lock table sample write; Query OK, 0 rows affected (0.00 sec) Table 에 대해 Lock 을 건 상태에서 압축을 실시하였다. oracle32@/home/mysql/mysql/bin>/home/mysql/mysql/bin/myisampack
-f /home/mysql/mysql/data/mysql/sample Compressing /home/mysql/mysql/data/mysql/sample.MYD:
(4 records) - Calculating statistics - Compressing file -85.71% 명령어가
이상없이 수행되었다. 압축전후를
확인했을 때 sample.MYI 가 변경된 걸 확인할 수 있었다. *압축전 -rw-r----- 1 mysql mysql 28 Apr
5 16:05 sample.MYD -rw-r----- 1 mysql mysql 1024 Apr
5 16:05 sample.MYI -rw-r----- 1 mysql mysql 8556 Apr
5 16:05 sample.frm *압축후 -rw-r----- 1 mysql mysql 59 Apr
5 16:05 sample.MYD -rw-r----- 1 mysql mysql 8556 Apr
5 16:05 sample.frm -rw-r----- 1 mysql mysql 1024 Apr
5 17:22 sample.MYI mysql> insert into sample values(8); Query OK, 1 row affected (0.00 sec) |
D.
파일복사를 통해 다른 MySQL 에 데이터 이전이 가능한가?
|
가능하다. A ~ C 에서 MyISAM engine 기반의 테이블 확인을
하는 과정에 파일의 삭제/복원을 통해 확인이 되었다. 복원된
테이블에 대한 확인은 flush tables 을 수행한 후 확인 가능하다. |
E.
MyISAM
Table 생성시 INDEX 가 생성이
되는가?
|
테이블에
대한 (아키텍처상의) 구조 이야기는 접어두고 위에서 생성했던
테이블에 대해 확인했을 때 인덱스는 존재하지 않는다. mysql> show indexes from sample; Empty set (0.00 sec) mysql> show indexes from sample2; Empty set (0.00 sec) mysql> show indexes from sample1; Empty set (0.01 sec) |
F.
Lock
table 시 다른 세션에서 Unlock
tables 로 해제가 가능한지? unlock, flush 를 이용해 Unlock 이 가능한지 확인
|
1번세션 mysql> use mysql; Database changed mysql> lock table sample write; Query OK, 0 rows affected (0.00 sec) 2번세션 mysql> select * from sample; (대기상태) 명령을 Cancel 하고 다음의 명령을 수행 mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) 명령을 Cancel 하고 다음의 명령을 수행 mysql> select * from sample; (대기상태) 명령을 Cancel 하고 다음의 명령을 수행 mysql> flush table sample; (대기상태) |
결론을 이야기 하면 flush, unlock 을 이용해 타
세션이 걸었던 Lock 을 해제하지 못한다.
또한 Lock 을 걸었던 세션이 flush 를 수행하더라도 Lock 은 풀리지 않는다. 다시 말해 Lock 을 걸었던 세션이 Lock 을 해제해야 한다. 참고로 세션을 종료하는 경우에도 Lock 이 해제된다.