언제가 될지 모르겠지만..MYSQL 세미나를 하게 될 날이 올 것 같아서 개인적으로 이것 저것 테스트를 해보고 있습니다.
시간적인 여유만 있다면... 처음부터 차근 차근 나만의 정리 공간을 만들고 싶지만... 이놈의 시간은 언제쯤,,......;;;
시간 날때 마다 조금씩 일단 기본 과정 부터 차근 차근 정리를 해보려 합니다.
첫 파트는 MYSQL의 스토리지 엔진 부분입니다.
MYSQL 은 ORACLE 과는 다르게 테이블의 형식 중 Engine 을 선택하여 만들 수 있습니다.
( 디폴트는 MyISAM 입니다.)
이 엔진의 선택에 따라서 해당 서버의 환경은 크게 변할 수 있습니다.
(빅사이트가 아닌 중소 규모의 서버들은 전문적인 관리자도 없기에 아무 생각 없이 MYISAM만 쓰더군요..)
스토리지 엔진의 종류에는 여러가지가 있으나, 그 중 실무에서 사용되는 것은 크게 MyISAM, InnoDB, Federate 정도 되겠습니다.
(개인적으로 Federate는 알려지지 않는 에러나, 버그성이 조금 있어.. 안정성 측면에서는 조금 떨어집니다.. ORACLE의 디비 링크 같은 역할)
일단 테스트는 업무 시간에 잠깐 돌려 본 것이라.. 이 글에 대한 정리는 나중에 해야 겠습니다. -_-;
머 테스트를 하게 된 목적은 이것 입니다.
MyISAM 엔진의 테이블은 AUTOCOMMIT입니다. 쿼리가 수행되는 순간 커밋이 이뤄집니다.
하여 따로 커밋이나 롤백 등의 트랜잭션 작업을 진행할 수 없습니다.
또한 테이블의 UPDATE, DELETE 등의 DML 문이 발생했을 경우 해당 테이블 전체를 잠궈 버리기 때문에( 테이블 락)
다른 세션에서의 SELECT 나 DML 작업을 동시에 병행 할 수 없는 문제가 있지요.
InnoDB는 오라클과 매우 유사한 환경을 제공합니다. COMMIT 과 ROLLBACK을 수행 할 수 있으며, (로우 락)
이전 값을 UNDO에 저장 하기 때문에 해당 테이블의 DML이 발생하였을 때, 다른 세션에서 접근 시 이전 값(OLD_DATA)를
보장합니다.
이러한 정의 하에 관련 테스트를 진행하였습니다.
먼저 MYISAM 의 트랜젝션 테스트 입니다.
Name: mytest
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 42991624
Avg_row_length: 7
Data_length: 594542648
Max_data_length: 1970324836974591
Index_length: 1024
Data_free: 293601280
Auto_increment: NULL
Create_time: 2011-04-20 15:17:25
Update_time: 2011-04-20 16:04:13
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment:
[db-repl2-db1][changsun]> desc mytest;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
=====================================
업데이트 (테이블 락)
##테이블 카운트
[db-repl2-db1][changsun]> select count(*) from mytest;
+----------+
| count(*) |
+----------+
| 63963143 |
+----------+
1 row in set (0.00 sec)
## 1) Update 시작
[db-repl2-db1][changsun]> update mytest set a=2 where a=1;
Query OK, 42991616 rows affected (4 min 49.40 sec)Rows matched: 42991616 Changed: 42991616 Warnings: 0
## 2) 조회 구문
[db-repl2-db1][changsun]> select * from mytest where a=7;
+------+
| a |
+------+
| 7 |
+------+
1 row in set (5 min 2.10 sec) -> 업데이트가 완료 될때까지 대기 상태
## 프로세스 상태
[db-repl2-db1][(none)]> show processlist;
+----+-----------------+-----------+----------+---------+-------+-----------------------------+---------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+----------+---------+-------+-----------------------------+---------------------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 78415 | Waiting for next activation | NULL |
| 53 | root | localhost | changsun | Query | 20 | Updating | update mytest set a=2 where a=1 |
| 55 | root | localhost | changsun | Query | 17 | Locked | select * from mytest where a=7 |
| 57 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+-----------------+-----------+----------+---------+-------+-----------------------------+---------------------------------+
===============================================================================================================
인서트 ( 영향 없음 락 X)
## 1) Insert 시작
[db-repl2-db1][changsun]> insert into mytest select * from mytest where a=3;
Query OK, 20971520 rows affected (41.42 sec)Records: 20971520 Duplicates: 0 Warnings: 0
## 2) 조회 구문
[db-repl2-db1][changsun]> select * from mytest where a=7;
+------+
| a |
+------+
| 7 |
+------+
1 rows in set (14.96 sec) -> 동시 수행됨
## 프로세스 상태
[db-repl2-db1][(none)]> show processlist;
+----+-----------------+-----------+----------+---------+-------+------------------------------+---------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+----------+---------+-------+------------------------------+---------------------------------------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 79036 | Waiting for next activation | NULL |
| 53 | root | localhost | changsun | Query | 15 | Copying to tmp table on disk | insert into mytest select * from mytest where a=3 |
| 55 | root | localhost | changsun | Query | 13 | Sending data | select * from mytest where a=7 |
| 57 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+-----------------+-----------+----------+---------+-------+------------------------------+---------------------------------------------------+
4 rows in set (0.00 sec)
===============================================================================================================
델리트 (테이블 락)
## 1) Delete 시작
[db-repl2-db1][changsun]> delete from mytest where a=3;
Query OK, 41943040 rows affected (4 min 14.14 sec)
## 2) 조회 구문
[db-repl2-db1][changsun]> select * from mytest where a=7;
+------+
| a |
+------+
| 7 |
+------+
2 rows in set (4 min 29.99 sec) -> 델리트가 완료 될때까지 대기 상태
## 프로세스 상태
[db-repl2-db1][(none)]> show processlist;
+----+-----------------+-----------+----------+---------+-------+-----------------------------+--------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+----------+---------+-------+-----------------------------+--------------------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 79214 | Waiting for next activation | NULL |
| 53 | root | localhost | changsun | Query | 15 | updating | delete from mytest where a=3 |
| 55 | root | localhost | changsun | Query | 13 | Locked | select * from mytest where a=7 |
| 57 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+-----------------+-----------+----------+---------+-------+-----------------------------+--------------------------------+
=============================================================================
이노디비의 트랜젝션
# 테이블 조회
[db-repl2-db1][changsun]> select * from test;
+------+------+
| a |
+------+------+
| 100 |
| 100 |
| 3 |
| 4 |
| 5 |
| 6 |
| 100 |
| 100 |
| 100 |
| NULL |
| 3 |
| 30 |
| 5 |
| 100 |
+------+
14 rows in set (0.00 sec)
# 업데이트 수행
[db-repl2-db1][changsun]> update test set a=1;
Query OK, 14 rows affected (0.00 sec)
Rows matched: 14 Changed: 14 Warnings: 0
# 해당 세션에서의 조회[db-repl2-db1][changsun]> select * from test;
+------+
| a |
+------+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
+------+
14 rows in set (0.00 sec) -> 자신에게는 변경된 값으로 보인다.
# 다른 세션에서의 COMMIT 전 조회
[db-repl2-db1][changsun]> select * from test;
+------+
| a |
+------+
| 100 |
| 100 |
| 3 |
| 4 |
| 5 |
| 6 |
| 100 |
| 100 |
| 100 |
| NULL |
| 3 |
| 30 |
| 5 |
| 100 |
+------+
14 rows in set (0.00 sec) -> 다른 세션에서는 트랜젝션 종료 될때까지, 이전 값 (old_data) 을 보게 된다.
# 트렌젹션 커밋 수행[db-repl2-db1][changsun]> commit;
Query OK, 0 rows affected (0.07 sec)
# 다른 세션에서의 COMMIT 후 조회
[db-repl2-db1][changsun]> select * from test;
+------+
| a |
+------+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
+------+
14 rows in set (0.00 sec) -> commit 이후에 다른 세션에서의 DML 변경 결과를 보게 된다.
===========================================================================================================
정리 안된 개인 자료
[db-repl2-db1][changsun]> CREATE TABLE `sample` (
`id` int(11) NOT NULL,
`heritage_id` int(11) default NULL,
`review_count` int(11) default NULL,
`status` char(1) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.11 sec)
[db-repl2-db1][changsun]> create table test(a int, b int) ENGINE=InnoDB;
Query OK, 0 rows affected (0.10 sec)
[db-repl2-db1][changsun]> create table mytest (a int);
Query OK, 0 rows affected (0.09 sec)
[db-repl2-db1][changsun]> show table status\G
*************************** 1. row ***************************
Name: mytest
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 1970324836974591
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2011-04-20 15:17:25
Update_time: 2011-04-20 15:17:25
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment:
*************************** 2. row ***************************
Name: sample
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 56623104
Auto_increment: NULL
Create_time: 2011-04-20 11:38:43
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
*************************** 3. row ***************************
Name: test
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 14
Avg_row_length: 1170
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 56623104
Auto_increment: NULL
Create_time: 2011-04-20 11:39:31
Update_time: NULL
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment:
MySQL 엔진의 캐릭터셋 < DATABASE 캐릭터셋 < 테이블의 캐릭터 셋
MY.CNF 이노디비 설정값
innodb_data_home_dir = /data1/mydata/
innodb_data_file_path = ibdata1:10M;ibdata2:10M;ibdata3:10M;ibdata4:10M;ibdata5:10M;ibdata6:10M;
innodb_log_group_home_dir = /data1/mysqllog
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
댓글 없음:
댓글 쓰기