'InnoDB Storage'에 해당되는 글 1건



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

   


블로그 이미지

운명을바꾸는자

IT와 함께 살아가는 삶

,