MYSQL: InnoDB Storage Architecture

InnoDB Storage Engine Architecture

   

1.InnoDB Storage Engine 기본 기능

① 프라이머리 키에 의한 클러스터링

• InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링 되어 저장된다.

• 즉 프라이머리 키 값의 순서대로 디스크에 저장된다는 뜻이며, 이로 인해 프라이머리 키에 의한 레인지 스캔은 상당히 빨리 처리될 수 있다. 결론적으로 쿼리의 실행 계획에서 프라이머리 키는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정(쿼리의 실행 계획에서 다른 보조 인덱스보다 프라이머리 키를 선택될 확률이 높음)된다.

② 잠금이 필요 없는 일관된 읽기(Non-locking consistent read)

• MVCC(Multi Version Concurrency Control)라는 기술을 이용해 락을 걸지 않고 읽기 작업을 수행(Undo 활용)

③ 외래 키 지원

• MyISAM이나 Memory 테이블에서는 사용할 수 없다.

• 부모, 자식 테이블 간에 데이터 Check하는 작업으로 인하여 락, 데드락이 발생될 때가 많다.

④ 자동 데드락 감지

• 그래프 기반의 데드락 체크 방식을 사용하기 때문에 데드락이 발생함과 동시에 바로 감지되고, 감지된 데드락은 관련 트랙잭션 중에서 Rollback이 가장 용이한 트랜잭션을 자동적으로 강제 종료해 버린다. => 실제로는 완벽하지는 않을 듯.

⑤ 자동화된 장애 복구

• Mysql서버가 시작될 때, 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된 데이터 페이지(Partial Write)등에 대한 일련의 복구 작업이 자동으로 진행된다. (SMON,PMON과 비슷한 역할을 내역이 있는 듯)

⑥ 오라클의 아키텍처 적용

• Oracle DBMS와 비슷한 부분이 많다.(UNDO, SYSTEM TBS, TBS 등)

   

   

2.InnoDB Architecture

① Oracle VS MySQL

• Oracle과 흡사한 형태이지만 MySQL은 Storage의 선택에 따라 세부 구조가 달라진다. 주로 RDBMS로 InnoDB가 사용되는데 Oracle 구조와 비슷한 형태인 것을 확인할 수 있다. (아래 상세 구성도 확인)

  

   

② MySQL InnoDB 상세구조

1) InnoDB 전체 구성도

• 사용자는 mysqld를 통하여 InnoDB에 접근하게 되며 Buffer Pool, Storage의 Access를 통하여 RDBMS의 Transaction이 발생됩니다.

 

   

2) Buffer Pool & 기타 Caching 영역

• Buffer Pool

  • 가장 핵심적인 부분으로 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간 MyISAM 키 캐시가 인덱스의 캐시만을 주로 처리하는데 비해 InnoDB의 버퍼 풀은 데이터와 인덱스 모두 캐시하고 쓰기 버퍼링의 역할까지 모두 처리함.

  • Innodb_buffer_pool_size 설정 중요

  • OS를 고려하여 50~80%수중으로 메모리 크기 설정

  • Oracle과 비슷하게 바로 변경된 사항에 대하여 디스크에 반영하지 않고 메모리에만 변경를 먼저 변경한 뒤, 디스크에 기록되지 않는 변경된 데이터를 가지고 있다(더티 페이지)

  • 체크포인트가 발생되면 Write 스레드가 필요한 만큼 디스크로 기록 진행

  • Oracle Buffer Cache 영역과 비슷한 기능

     

• Undo log

  • 트랜잭션의 롤백 대비용

  • 트랜잭션의 격리 수준을 유지하면서 높은 동시성을 제공하는 데 사용된다.

  • Oracle 과 다르게 별도의 TBS가 아닌 System TBS에서 관리하는 것으로 구조상으로는 확인됩니다.

  • Oracle UNDO TBS 와 비슷한 기능

     

• Insert Buffer

  • Data Insert, Update에 따른 변경작업이 발생될 때 인덱스에도 해당 내역을 반영해야 하는데 해당 테이블에 인덱스가 많다면, 이 작업 시 랜덤하게 디스크를 읽은 작업을 하면서 상당히 많은 자원을 소모하기 때문에 별도의 Insert Buffer공간(임시공간)을 만들어 성능 향상

  • Innodb_change_buffering 파라미터로 설정

  • MySQL 5.5이전 버전은 Insert까지만 지원, 이후에는 Insert, Delete로 인해 키를 추가하거나 삭제하는 작업에 대해서도 버퍼링이 될 수 있도록 개선 => Update의 경우는 확인이 필요할 듯.

  • 0.인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트 수행

  • 1. 임시 공간(Insert Buffer)에 필요한 데이터를 디스크에서 읽어 와서 데이터 변경작업 진행

  • 2. 인덱스 레코드가 조작된 조각은 이후 백그라운드 스레드에 의해 병합되는데 이 스레드를 버퍼 머지 스레드라고 한다.

  • 3. 완료 (Oracle의 PGA + TEMP와 비슷한 기능)

   

3) Log Buffer & Redo Log

   

• Commit시 바로 Disk 에 변경된 데이터 조작을 하지않고 ACID(원자성,일관성,격리성,데이터 유지)를 보장하면서 체크포인트가 발생할 경우 일괄적으로 반영하는 형태로 부하를 줄이기 위한 기능
• 자동화된 자동 복구시 해당 Redo log를 확인하여 복구 진행
• 로그 버퍼의 크기는 일반적으로 1~8MB 수전으로 설정 (단, BLOB이나 TEXT와 같은 큰데이터를 자주 변경한다면 더 크게 설정하는 것이 좋다)
• Oracle Redo log file과 log buffer 영역과 비슷한 기능

   

3. MySQL 기본 명령어

① MySQL START / STOP

[root@localhost mysql]# /etc/init.d/mysqld stop Stopping mysqld: [ OK ] [root@localhost mysql]# /etc/init.d/mysqld start

   

② MySQL Password 변경 및 Login

[root@localhost mysql]# mysqladmin -u root password 새비밀번호 [root@localhost mysql]# mysql -uroot -p => 패스워드 입력

   

③ MySQL 기본 정보 검색

show databases; show variables like 'have_innodb'; select version(); show engines; select @@innodb_version; show plugins; show variables where Variable_Name LIKE "innodb_buffer%"; show variables where Variable_Name LIKE "innodb_change_buffering%"; show variables where Variable_Name LIKE "innodb_log%"; show variables where Variable_Name LIKE "%innodb_write_io_threads%";     SHOW VARIABLES WHERE Variable_Name LIKE "%dir"; /var/lib/mysql mysql 8350 8143 0 03:25 pts/1 00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock     [접속 IP 권한 할당] mysql> GRANT ALL PRIVILEGES ON *.* TO root@'ip주소' IDENTIFIED BY '비밀번호'' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO root@192.168.22.123 IDENTIFIED BY 'mysql' WITH GRANT OPTION;     mysql> show information_schema mysql> SHOW TABLES; mysql> DESC 테이블이름; mysql> DESCRIBE 테이블이름; mysql> EXPLAIN 테이블이름;

 

④ MySQL 기본 Dictionary 조회

[현재 DB 전체 Size 확인] mysql> SELECT table_schema "Database Name", -> SUM(data_length + index_length) / 1024 / 1024 "Size(MB)" -> FROM information_schema.TABLES -> GROUP BY table_schema; +--------------------+------------+ | Database Name | Size(MB) | +--------------------+------------+ | information_schema | 0.00390625 | | mysql | 0.52499485 | +--------------------+------------+ 2 rows in set (0.04 sec)     mysql>         mysql> SELECT -> count(*) NUM_OF_TABLE, -> table_schema,concat(round(sum(table_rows)/1000000,2),'M') rows, -> concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA, -> concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx, -> concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size, -> round(sum(index_length)/sum(data_length),2) idxfrac -> FROM information_schema.TABLES -> GROUP BY table_schema -> ORDER BY sum(data_length+index_length) DESC LIMIT 10; +--------------+--------------------+-------+-------+-------+------------+---------+ | NUM_OF_TABLE | table_schema | rows | DATA | idx | total_size | idxfrac | +--------------+--------------------+-------+-------+-------+------------+---------+ | 18 | mysql | 0.00M | 0.00G | 0.00G | 0.00G | 0.14 | | 17 | information_schema | NULL | 0.00G | 0.00G | 0.00G | NULL | +--------------+--------------------+-------+-------+-------+------------+---------+ 2 rows in set (0.00 sec)     mysql>

[특정 Table 사이즈 확인] mysql> SELECT -> concat(table_schema,'.',table_name), -> concat(round(table_rows/1000000,2),'M') rows, -> concat(round(data_length/(1024*1024*1024),2),'G') DATA, -> concat(round(index_length/(1024*1024*1024),2),'G') idx, -> concat(round((data_length+index_length)/(1024*1024*1024),2),'G') total_size, -> round(index_length/data_length,2) idxfrac -> FROM information_schema.TABLES -> where table_name = 'events_statements_current' ; +-------------------------------------+-------+-------+-------+------------+---------+ | concat(table_schema,'.',table_name) | rows | DATA | idx | total_size | idxfrac | +-------------------------------------+-------+-------+-------+------------+---------+ | mysql.TEST | 0.00M | 0.00G | 0.00G | 0.00G | 93.09 | +-------------------------------------+-------+-------+-------+------------+---------+ 1 row in set (0.00 sec)

[특정 스키마 테이블 정보 확인] mysql> SELECT table_name, table_type, engine -> FROM information_schema.tables -> WHERE table_schema = 'mysql' -> ORDER BY table_name DESC; +---------------------------+------------+--------+ | table_name | table_type | engine | +---------------------------+------------+--------+ | user | BASE TABLE | MyISAM | | time_zone_transition_type | BASE TABLE | MyISAM | | time_zone_transition | BASE TABLE | MyISAM | | time_zone_name | BASE TABLE | MyISAM | | time_zone_leap_second | BASE TABLE | MyISAM | | time_zone | BASE TABLE | MyISAM | | TEST | BASE TABLE | MyISAM | | tables_priv | BASE TABLE | MyISAM | | procs_priv | BASE TABLE | MyISAM | | proc | BASE TABLE | MyISAM | | host | BASE TABLE | MyISAM | | help_topic | BASE TABLE | MyISAM | | help_relation | BASE TABLE | MyISAM | | help_keyword | BASE TABLE | MyISAM | | help_category | BASE TABLE | MyISAM | | func | BASE TABLE | MyISAM | | db | BASE TABLE | MyISAM | | columns_priv | BASE TABLE | MyISAM | +---------------------------+------------+--------+ 18 rows in set (0.00 sec)

[성능 분석 Setting] SHOW VARIABLES LIKE 'perf%'; +---------------------------------------------------+---------+ | Variable_name | Value | +---------------------------------------------------+---------+ | performance_schema | OFF |     /etc/my.cnf UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name like 'events_waits%';     SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE ENGINE='PERFORMANCE_SCHEMA'\G; SHOW ENGINES\G;     [TOP 5 Wait EVENT] select event_name, count_star, sum_timer_wait from performance_schema. events_waits_summary_global_by_event_name where event_name like 'wait/synch/mutex/innodb%' and count_star > 0 order by sum_timer_wait desc limit 5;     [Slow SQL 추출] # vi /etc/my.cnf [mysqld] log-slow-queries = /usr/local/mysql/var/mysql-slow.log long_query_time = 3 위 내용은 쿼리 타임이 '3초'를 초과 하는 쿼리에 대해 /usr/local/mysql/var/mysql-slow.log파일에 기록 한다는 뜻입니다. my.cnf에 설정 및 mysql을 Restart 한 뒤 운영 하다 보면 아래와 같이 slow-query-log가 남게 됩니다.

[mysql-slow.log파일] use iamroot; select max(wr_comment) as max_comment from g4_write_ja where wr_parent = '92' and wr_is_comment = 1; # Time: 120809 16:08:15 # User@Host: iamroot[iamroot] @ [] # Query_time: 253 Lock_time: 0 Rows_sent: 1 Rows_examined: 419562 select max(wr_comment) as max_comment from test1 where wr_parent = '92' and wr_is_comment = 1; # Time: 120809 16:08:17 # User@Host: iamroot[iamroot] @ [] # Query_time: 94 Lock_time: 0 Rows_sent: 0 Rows_examined: 640675 use iamroot; SELECT count(*) from emp where boardcode=1045552594 and boardidx=274; # Time: 120809 16:08:23 # User@Host: iamroot[iamroot] @ [] # Query_time: 183 Lock_time: 0 Rows_sent: 1 Rows_examined: 268576     [로그 내용 중 각 항목에 대한 설명] Query_time : 쿼리 수행시간 Lock_time : 테이블 LOCK 이 걸린시간 Row_sent : 쿼리 처리 결과 ROW 수 Rows_examined : 쿼리 처리 대상의 ROW 수

   

[참고 자료 및 사이트]

https://www.mysql.com/

http://www.oracle.com/technetwork/database/mysql/index.html

http://www.slideshare.net/morgo/inno-db-presentation

Real MySQL Book