6.3.1 MySQL 5.6 InnoDB
XtraDB는 기본적으로 MySQL의 InnoDB 스토리지 엔진의 모든 기능을 그대로 유지하면서 추가저긍로 성능이나 확장성이 개선된 스토리지 엔진입니다. 그래서 XtraDB 스토리지 엔진을 이해하기 위해서는 MySQL에 내장된 InnoDB 스토리지 엔진의 기능들을 알고 있어야 합니다.
6.3.1.1 영구적인 통계정보
MySQL 5.6의 InnoDB에서는 이전버전과는 달리 각 테이블의 통계 정보를 테이블로 관리하도록 보완되었습니다. MySQL 5.5버전까지의 InnoDB에서는 통계 정보를 단순히 각 스토리지 엔진에서 단순히 메모리상에서 관리했었는데, 이는 매우 자주 변경되고 마스터와 슬레이브에서 각각 다른 쿼리 실행 계획을 만들어 낼 정도로 변덕이 심했습니다. 그래서 MySQL 5.6의 InnoDB에서는 테이블의 전체 레코드 수나 인덱스별로 카디널리티 정보를 mysql 데이터베이스의 innodb_index_stats와 innodb_table_stats테이블로 관리하도록 개선되었습니다. 또한 이 정보가 자동으로 변경되는것을 허용할지 말지를 사용자가 결정할 수 있도록 innodb_stats_auto_recalc 시스템 변수가 도입되었습니다. 이제는 마스터의 통계 정보를 슬레이브로 복사해서 마스터와 슬레이브의 실행계획이 달라지는 현상을 막을 수 있게 되었습니다.
하지만 MySQL 5.6의 InnoDB에서 테이블의 통계정보를 더 풍부하게 관리하도록 개선된 것은 아닙니다. 단순히 저장소가 메모리에서 디스크 기반의 테이블로 변경된 것입니다. 이미 실행계획에서 살펴보았던 것처럼 MariaDB 10.0에서는 스토리지 엔진 레벨이 아닌 MariaDB서버 차원에서 스토리지 엔진의 종류에 관계없이 통계 정보를 mysql 데이터베이스에 있는 table_stats와 index_stats 그리고 column_stats테이블로 관리하고 있습니다. 또한 MariaDB 10,0에서는 인덱스되지 않은 컬럼에 대해서도 통계 정보를 관리할 뿐만 아니라 컬럼의 히스토그램까지 관리할 수 있도록 개선되었습니다.
6.3.1.2. 데이터 읽기 최적화
MySQL 5.6의 인덱스 컨디션 푸시다운(ICP)과 멀티 레인지 리드(MRR) 최적화를 위해서 MySQL서버의 핸들러 API가 개선되었는데, 이 기능들이 제대로 작동하기 위해서는 각 스토리지 엔진에서 이 핸들러 API를 모두 구현해야만 합니다. MySQL에서 많이 사용되는 InnoDB와 MyISAM스토리지 엔진은 이 API들을 모두 구현하고 있으므로 인덱스 컨디션 푸시 다운과 멀티 레인지 리드를 사용할 수 있습니다. 하지만 TokuDB나 Mroonga 스토리지 엔진과 같은 제3의 스토리지 엔진은 필요 시 직접 매뉴얼을 통해서 이런 기능이 지원되는지 확인해보는 것이 좋습니다.
6.3.1.3 커널 뮤텍스(Kernel mutex)
InnoDB는 내부적으로 많은 공유 메모리 객체들을 가지고 있습니다. 가장 대표적으로 버퍼 풀의 각 블록들과 리두 로그 등을 예로 들 수 있습니다. 이런 공유된 메모리 객체들을 수많은 클라이언트 커넥션들이 서로 경쟁하면서 점유했다가 다시 점유를 해제하면서 쿼리를 처리하는 것입니다. 수많은 메모리 객체들을 각 클라이언트 커넥션들이 서로 동시에 점유하지 못하도록 동기화 처리를 해야 하는데, 이런 목적으로 잠금(뮤텍스 또는 세마포어)들이 사용됩니다.
하지만 모든 공유 메모리 객체가 개별적인 잠금을 가지고 있지는 않습니다. 그렇게 구현이 된다면 너무 많은 잠금들이 뒤섞여서 사용되기 때문에 잠금의 관리가 더 복잡해질 수도 있기 때문입니다. 때로는 잠금의 경합이 매우 많이 발생하는 부분에서 하나의 잠금으로 여러 개의 메모리 구조체에 대한 동기화를 처리해 버림으로써 불필요하게 잠금 대기가 발생하는 경우도 많았습니다. 그 중에서 가장 대표적인 잠금이 kernel_mutex라는 뮤텍스입니다. kernel_mutex는 사실 딱 한마디의 용도를 설명할 수 있는 잠금이 아니었습니다. kernel_mutex는 MySQL 소스코드에서 딱 용도가 들어맞는 뮤텍스나 세마포어가 없을 때 사용되는 공통 뮤텍스 같은 존재였습니다. 이런 형식으로 kernel_mutex가 사용되다 보니 InnoDB 성능에서 왠만하면 kernel_mutex가 병목이 되고 확장성을 저해하는 요소였습니다.
MySQL 5.6의 InnoDB에서는 이렇게 범용으로 사용되던 kernel_mutex를 각 용도별로 더 세분화해서 별도의 잠금을 도입했습니다. 대표적으로 트랜잭션의 동시성 제어와 MVCC등의 메모리 구조체들은 모두 개별적인 읽고 쓰기 잠금으로 제어되도록 개선되었습니다.
6.3.1.4 멀티 스레드 기반의 언두 퍼지(Multi threaded purge)
InnoDB는 MVCC(Multi version concurrency control)와 롤백을 위해서 언두 영역을 별도로 관리하고 있습니다.
많은 클라이언트 커넥션에서 데이터를 변경하게 되면 언두 로그 영역에 수많은 변경 전 정보들이 쌓이게 되는데, 이렇게 쌓인 언두 로그를 언젠가는 삭제를 하고 빈 공간을 마련해야 이후의 변경 이력들을 계속 저장할 수 있게 됩니다. 이때 언두 로그를 삭제하는 작업을 언두 퍼지(Undo purge)라고 하는데, MySQL 5.1버전까지는 언두 퍼지 작업이 마스터 스레드라고 불리는 InnoDB의 메인 스레드에서 처리되었습니다. 그런데 InnoDB 마스터 스레드는 언두 퍼지 이외에도 많은 작업들을 수행하는 스레드여서 언두 퍼지가 제대로 이루어지지 못하거나 마스터 스레드의 다른 작업들이 지연되는 현상들이 발생했었습니다.
MySQL 5.5버전의 InnoDB에서는 언두 퍼지를 전담하는 별도의 스레드를 도입했는데, 이때까지는 언두 퍼지를 위해서 단 하나의 스레드만 사용할 수 있었습니다. MySQL서버의 innodb_purge_threads 시스템 설정변수로 언두 퍼지 스레드으 ㅣ개수를 설정할 수 있는데, MySQL 5.5까지는 이시스템 설정에 0 또는 1만 설정할 수 있었습니다. 이 변수가 0으로 설정되면 전용의 언두 퍼지 스레드를 사용하는 것이 아니라 MySQL 5.1과 같이 InnoDB마스터 스레드에서 언두를 퍼지하게 되고, 1로 설정하면 하나의 언두 퍼지 전용 스레드가 활성화되는 것입니다. 하지만 이 또한 충분하지 않아서 UPDATE가 많이 발생하는 MySQL 서버에서는 언두로그가 계속 늘어나기만 하는 경우도 있었습니다.
MySQL 5.6버전의 InnoDB에서는 innodb_purge_threads 시스템 변수에 1이상의 값을 설정하여 여러개의 언두 퍼지스레드를 동시에 사용할 수 있도록 개선되었습니다.
6.3.1.5 독립된 플러시 스레드
사용자가 DML 문장을 실행하면 변경된 데이터는 먼저 리두 로그에 기록되면서 디스크에 영구적으로 남게 됩니다. 그리고 InnoDB스토리지 엔진은 실제 테이블의 데이터를 메모리(InnoDB 버퍼 풀) 상에서만 변경하게 됩니다. 여기까지 완료되면 MySQL 서버는 클라이언트의 사용자에게 쿼리가 실행 완료되었다고 리턴을 합니다. 하지만 InnoDB 스토리지 엔진은 메모리(InnoDB 버퍼풀)상에만 변경되어 있는 이 데이터를 언젠가는 디스크에 영구적으로 기록해야 합니다. 이때 InnoDB 버퍼 풀에 변경된 데이터를 우리는 더티(Dirty)라고 하고, 더티 데이터를 디스크로 영구히 기록하는 작업을 플러시(Flush)라고 합니다.
MySQL 5.5버전의 InnoDB에서는 마스터 스레드가 다른 작업들을 수행하면서 더티 페이지를 디스크로 플러시하도록 되어 있었습니다. 물론 더티 페이지를 디스크로 플러시하는 알고리즘도 중요하지만 다른 작업들이 지연되어서 더티 페이지가 제때 디스크로 플러시되지 못한다면 더 큰 문제를 유발할 수 있습니다. 이런 문제를 보완하기 위해서 MySQL 5.6에서는 InnoDB 버퍼 풀의 더티 페이지를 디스크로 플러시하는 전용의 스레드를 도입한 것입니다.
6.3.1.6 가변 페이지 사이즈
MySQL 5.5버전까지 InnoDB의 페이지 사이즈는 16KB로 고정이었으며, 이를 조정하기 위해서는 MySQL 서버를 다시 컴파일해야 했습니다. 사이즈가 작은 레코드를 2~3건씩 읽어가는 쿼리가 아주 빈번하게 실행되는 시스템에서는 16KB페이지는 사실 많이 큰 편입니다. 실제 30~40바이트의 레코드를 읽기 위해 16KB페이지 단위로 디스크에서 읽고 그 데이터가 변경되면 16KB를 통째로 디스크에 플러시하는 것이 상당히 불합리할 수 있습니다.
MySQL 5.6의 InnoDB 스토리지 엔진에서는 데이터나 인덱스 페이지의 크기를 기존 16KB에서 4KB나 8KB로 조정할 수 있게 되었습니다. 프라이머리 키나 인덱스를 통해서 1~2건의 레코드를 읽는 단순한 형태의 쿼리들이 아주 빈번하게 실행되는 MySQL 서버라면 기존 16KB페이지보다는 4KB정도의 페이지 사이즈가 훨씬 더 효율적으로 작동할 것입니다. 또한 4KB 페이지 사이즈는 요즘 많이 사용되고 있는 SSD에서도 효율적으로 작동합니다. 일반적인 HDD에서는 읽어야 할 데이터의 위체어 디스크 헤더를 움직이는 시간이 많이 걸리는 반면 그 위치에서부터 연속적으로 데이터를 얼마나 많이 일그냐는 별로 중요하지 않습니다. 즉 특정 위치에서부터 연속적으로 읽어야 할 데이터가 4KB냐 16KB냐는 별로 성능에 영향을 미치지 않았었습니다. 하지만 SSD와 같이 디스크 헤더가 없고 읽어야 할 데이터의 위치를 찾는 기간이 걸리지 않는 시스템에서는 데이터를 읽어내고 전송하는 작업이 성능에 영향을 미치게 됩니다.
※ 이미 16KB 페이지 사이즈로 시스템 테이블스페이스가 만들어졌고, 16KB 사이즈의 사용자 테이블이 있는 경우에는 MySQL의 모든 데이터를 덤프받아서 다시 적재해야 합니다. mysqldump명령이나 SELECT INTO ... OUTFILE ... 과 같은 논리적 백업을 사용해야합니다. XtraBACKUP이나 ENTERPRISE BACKUP은 데이터파일을 그대로 복사하기 때문에 이 툴을 이용해서 복구한다면 그대로 16KB의 페이지를 사용하게될 것입니다.
6.3.1.7 테이블스페이스 복사(Transportable tablespace)
MySQL 5.5의 InnoDB에서도 테이블 스페이스를 복사할 수 있는 기능을 제공되고 있었습니다. 하지만 MySQL 5.5에서 제공된 기능은 단순히 자기 자신의 MySQL 서버로부터 백업된 테이블 스페이스 파일(*.ibd)만 복사할 수 있었습니다. InnoDB스토리지 엔진은 내부적으로 모든 InnoDB테이블마다 고유의 ID가 할당됩니다. 그런데 MySQL 5.5의 InnoDB에서는 ID가 일치하지 않으면 다른 서버에서 ibd파일을 복사해서 import하는 것이 불가능 했습니다.
MySQL 5.6의 InnoDB에서는 어렵지 않게 테이블 스페이스(*.ibd)파일만 가져오거나 복구할 수 있게 되었습니다.
테스트
-- DB 생성
mysql> create database IF NOT EXISTS employees;
Query OK, 1 row affected (0.17 sec)
mysql>
mysql> use employees;
Database changed
--테스트 테이블 생성
mysql> CREATE TABLE tb_trans(
fd1 int not null,
fd2 varchar(20),
primary key(fd1)
)ENGINE=InnoDB;
Query OK, 0 rows affected (0.34 sec)
--테스트 데이터 삽입
mysql> INSERT INTO tb_trans VALUES (9,'Matt');
Query OK, 1 row affected (0.03 sec)
mysql> select * from tb_trans;
+-----+------+
| fd1 | fd2 |
+-----+------+
| 9 | Matt |
+-----+------+
1 row in set (0.00 sec)
--EXPORT
mysql> FLUSH TABLES tb_trans FOR EXPORT;
Query OK, 0 rows affected (0.00 sec)
--물리적으로 파일 복사
[root@ysbae employees]# ls
tb_trans.cfg tb_trans.ibd
[root@ysbae employees]# cp tb_trans.* /tmp
--DISCARD (데이터파일을 삭제)
mysql> ALTER TABLE tb_trans DISCARD TABLESPACE;
Query OK, 0 rows affected (0.18 sec)
--파일 복사
[root@ysbae tmp]# cp /tmp/tb_trans.* .
--IMPORT
mysql> ALTER TABLE tb_trans IMPORT TABLESPACE;
Query OK, 0 rows affected (0.16 sec)
--확인
mysql> select * from tb_trans;
+-----+------+
| fd1 | fd2 |
+-----+------+
| 9 | Matt |
+-----+------+
1 row in set (0.00 sec)FLUSH TABLES ... FOR EXPORT가 실행되면 해당 테이블에 명시적인 잠금이 걸리게 되므로 다른 사용자는 그 테이블을 읽을 수는 있지만 절대 변경할 수는 없습니다. 만약 필요하다면 익스포트된 파일의 복사가 끝나는 대로 즉시 "UNLOCK TABLES" 명령으로 잠금을 해제해야합니다.
ALTER TABLE ... DISCARD TABLESPACE명령으로 기존 테이블이 가지고 있던 테이블 스페이스를 삭제할 수 있습니다.
6.3.1.8 독립된 언두 스페이스
MySQL 5.5버전까지의 InnoDB에서 언두 영역은 시스템 테이블스페이스의 일부 영역을 사용하고 있었습니다. 하지만 시스템 테이블스페이스는 인서트 버퍼나 DoubleWrite 버퍼 등의 데이터들도 모두 시스템 테이블스페이스를 사용하고 있습니다. 문제는 언두 영역은 주로 랜덤 I/O 기반으로 작동하고 DoubleWrite 버퍼는 시퀀셜 I/O기반으로 디스크에 기록된다는 것입니다. 이는 시스템 테이블스페이스가 저장될 디스크의 위치를 선정하기가 쉽지 않다는 것을 의미합니다.
MySQL 5.6버전의 InnoDB에서는 언두 영역을 시스템 테이블스페이스가 아닌 별도의 공간에 저장할 수 있도록 아래의 3개 시스템 설정 변수가 도입되었습니다.
innodb_undo_directory
InnoDB의 언두 로그가 저장되는 언두영역(Undo space,Undo tablespace)이 저장될 디렉토리를 지정합니다. 기본값은 "."인데 이는 MySQL 5.5버전에서와 같이 언두 영역이 시스템 테이블스페이스를 사용하는 것을 의미합니다.
innodb_undo_tablespace
언두 영역도 하나의 테이블처럼 생각할 수 있는데, 일반 테이블의 레코드를 파티션하듯이 언두 영역도 여러 개의 테이블 스페이스로 분리해서 생성할 수 있습니다. 최대 126개까지 생성할 수 있습니다. 만약 6으로 설정했다면 innodb_undo_directory설정 값에 지정된 언두 디렉토리에 undo라는 이름으로 시작하는 파일이 6개 생성되는 것입니다.
innodb_undo_logs
언두 세그먼트의 개수를 지정하는 시스템 설정으로 InnoDB에서는 최대 1023개의 쓰기 트랜잭션이 하나의 언두 세그먼트를 공유하면서 사용할 수 있습니다. 이 값은 최대 128까지 설정할 수 있습니다. 만약 20으로 설정한다면 최대 1023*20 = 20460개의 쓰기 트랜잭션이 실행될 수 있습니다.
언두영역을 시스템 테이블스페이스와 별도로 분리한다면 가능한 한 빠른 디스크로 구성된 볼륨이 위치시키는 것이 좋습니다.
6.3.1.9 읽기전용 트랜잭션 최적화
MySQL 5.5버전까지의 InnoDB에서 모든 쿼리에 대해 트랜잭션이 자동으로 시작되고 종료되는 형태로 처리되었습니다. 이는 Auto-Commit이 활성화된 경우에도 마찬가지였습니다. 그런데 사실 트랜잭션이 보장되기 위해서는 내부적으로 트랜잭션 ID를 발급하고 트랜잭션 유지에 대한 메모리 구조체를 매번 할당했어야 했습니다.
MySQL 5.6버전에는 다음 두가지 경우에 대해서는 읽기 전용 트랜잭션으로 처리할 수 있도록 개선되었습니다.
- START TRANSACTION READ ONLY로 시작된 트랜잭션
- AutoCommit이 활성화된 상태에서 SELECT 쿼리만 실행된 트랜잭션 (SELECT ... FOR UPDATE 제외)
InnoDB스토리지 엔진은 이 두 조건을 만족하는 트랜잭션에 대해서는 읽기 전용 트랜잭션이라는 것을 알아채고 트랜잭션 ID를 발급하지 않고 데이터 변경에 필요한 메모리 구조체들을 할당받지 않게 됩니다. 또한 트랜잭션 ID가 줄어들면 모든 커넥션에서 쿼리나 DML 문장을 실행하기 위해서 참조하고 검색해야 하는 메모리 구조체가 단순해지기 때문에 더 빠른 성능을 낼 수 있게 됩니다.
또한 읽기 전용 트랜잭션의 정보는 별도로 관리되기 때문에 SHOW ENGINE INNODB STATUS명령의 결과에 읽기 전용 트랜잭션 정보는 출력되지 않습니다.
MariaDB [employees]> SHOW ENGINE INNODB STATUS
-> ;
+--------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Type | Name | Status |
+--------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB | |
=====================================
2021-07-23 13:50:48 0x40b0 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 26 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 104404 srv_idle
srv_master_thread log flush and writes: 104404
----------
SEMAPHORES
----------
------------
TRANSACTIONS
------------
Trx id counter 644
Purge done for trx's n:o < 644 undo n:o < 0 state: running
History list length 49
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION (000001C5E5932108), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
Pending flushes (fsync) log: 0; buffer pool: 0
14230 OS file reads, 66 OS file writes, 60 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 432110428
Log flushed up to 432110428
Pages flushed up to 432110428
Last checkpoint at 432110416
0 pending log flushes, 0 pending chkp writes
38 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 2181038080
Dictionary memory allocated 13305816
Buffer pool size 129616
Free buffers 115271
Database pages 14345
Old database pages 5312
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 14206, created 139, written 26
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 14345, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 read views open inside InnoDB
Process ID=0, Main thread ID=0, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
|
+--------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)information_schema 데이터베이스의 INNODB_TRX 테이블을 통해서만 참조할 수 있습니다. trx_is_read_only라는 컬럼을 통해 구분할 수 있습니다.
MariaDB [employees]> desc information_schema.INNODB_TRX;
+----------------------------+----------------------------------------------------------------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+----------------------------------------------------------------------------+------+-----+---------------------+-------+
| trx_id | bigint(21) unsigned | NO | | 0 | |
| trx_state | varchar(13) | NO | | | |
| trx_started | datetime | NO | | 0000-00-00 00:00:00 | |
| trx_requested_lock_id | varchar(81) | YES | | NULL | |
| trx_wait_started | datetime | YES | | NULL | |
| trx_weight | bigint(21) unsigned | NO | | 0 | |
| trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | |
| trx_query | varchar(1024) | YES | | NULL | |
| trx_operation_state | varchar(64) | YES | | NULL | |
| trx_tables_in_use | bigint(21) unsigned | NO | | 0 | |
| trx_tables_locked | bigint(21) unsigned | NO | | 0 | |
| trx_lock_structs | bigint(21) unsigned | NO | | 0 | |
| trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | |
| trx_rows_locked | bigint(21) unsigned | NO | | 0 | |
| trx_rows_modified | bigint(21) unsigned | NO | | 0 | |
| trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | |
| trx_isolation_level | enum('READ UNCOMMITTED','READ COMMITTED','REPEATABLE READ','SERIALIZABLE') | NO | | NULL | |
| trx_unique_checks | int(1) | NO | | 0 | |
| trx_foreign_key_checks | int(1) | NO | | 0 | |
| trx_last_foreign_key_error | varchar(256) | YES | | NULL | |
| trx_is_read_only | int(1) | NO | | 0 | |
| trx_autocommit_non_locking | int(1) | NO | | 0 | |
+----------------------------+----------------------------------------------------------------------------+------+-----+---------------------+-------+
22 rows in set (0.042 sec)6.3.1.10 버퍼 풀 덤프 & 로드
MySQL 5.5버전의 InnoDB에서는 버퍼 풀의 모든 내용이 텅 비어 있는 상태로 시작하게 됩니다.
MariaDB10.0을 사용하는 경우에는 MySQL 5.6의 버퍼 풀 덤프 & 로딩 명령을 사용해야 합니다.
MySQL 5.6의 버퍼 풀 덤프 & 로딩
버퍼 풀 덤프 및 적재는 MySQL 서버의 시스템 설정 변수로 실행할 수 있는데, 다음과 같이 덤프와 적재에 관련된 4개의 시스템 설정 변수가 있습니다.
innodb_buffer_pool_dump_now
항상 OFF값이며 이 변수 값을 ON으로 설정하면 즉시 InnoDB버퍼 풀의 내용을 덤프합니다. 덤프 완료시 자동으로 OFF로 바뀝니다.
innodb_buffer_pool_load_now
항상 OFF값이며 이 변수 값을 ON으로 설정하면 innodb_buffer_pool_filename 설정변수에 지정된 파일을 읽어서 데이터와 인덱스 페이지들을 InnoDB 버퍼 풀로 적재합니다. 적재가 완료되면 OFF로 바뀝니다.
innodb_buffer_pool_dump_at_shutdown
ON으로 설정되어 있으면 MySQL 서버가 셧다운될 때 InnoDB버퍼풀의 내용을 파일로 덤프합니다.
innodb_buffer_pool_load_at_startup
ON으로 설정되어 있으면 서버가 시작될 때 innodb_buffer_pool_filename 에 지정된 파일을 읽어서 데이터와 인덱스 페이지들을 InnoDB 버퍼 풀로 적재합니다.
아래 두개의 파라미터를 이용하면 덤프나 적재가 얼마나 진행되었는지 상태를 확인할 수 있습니다.
MariaDB [employees]> show global status like 'Innodb_buffer_pool_dump_status';
+--------------------------------+--------------------------------------------------+
| Variable_name | Value |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status | Buffer pool(s) dump completed at 210722 10:20:03 |
+--------------------------------+--------------------------------------------------+
1 row in set (0.001 sec)
MariaDB [employees]> show status like 'Innodb_buffer_pool_load_status';
+--------------------------------+--------------------------------------------------+
| Variable_name | Value |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 210722 10:20:26 |
+--------------------------------+--------------------------------------------------+
1 row in set (0.001 sec)만약 시간이 많이 걸려서 버퍼 적재를 끝까지 기다릴 수 없다면 innodb_buffer_pool_load_abort 시스템 설정변수를 ON으로 설정해서 중간에 작업을 종료할 수 있습니다.
6.3.1.11 리두 로그 사이즈
InnoDB는 서버 크래시에 대한 데이터 안전을 보장하면서 성능을 향상시키기 위해 커밋된 트랜잭션 내용을 먼저 로그 파일로 기록하고 실제 데이터 파일의 변경은 나중에 모아서 배치 형태로 처리합니다. 리두 로그라고도 하며 WAL(Write Ahead Log)로그라고도 하는데, innoDb의 로그는 여러 개의 파일이 하나의 논리적 저장소처럼 순환되면서 사용됩니다.
innodb_log_file_size
각 로그 파일의 사이즈를 설정합니다. 이미 사용되고 있던 리두 로그 파일의 사이즈를 변경하기 위해서는 InnoDB 로그 파일의 모든 내용이 비워지도록 서버를 클린 셧다운 한 후에만 가능
innodb_log_files_in_group
로그 파일의 개수를 몇 개로 생성할지를 결정하는 변수입니다.
innodb_log_group_home_dir
InnoDB 리두 로그를 생성할 디렉토리를 설정하는 시스템 변수입니다. 특별히 명시되지 않으면 MySQL 서버의 데이터 디렉토리에 생성합니다.
6.3.1.12 리두 로그 크기 변경
MariaDB 5.5나 MySQL5.5에서 리두 로그 파일 크기를 변경하기 위해서는 아래와 같은 과정이 필요했습니다.
1.MariaDB서버에 로그인 후 SET GLOBAL innodb_fast_shutdown=0을 실행
2.MariaDB 서버 종료
3.MariaDB서버의 에러로그파일을 열어서 정상적으로 셧다운되었는지 확인(비정상적일 경우에는 사이즈 변경 하면 안 됨)
4.기존의 리두 로그 파일을 별도 디렉토리로 백업 후 기존파일 삭제
5. my.cnf 파일에서 innodb_log_file_size 사이즈 변경
6.MariaDB 서버 시작
7.에러 로그와 디렉토리에서 변경된 크기의 리두 로그 확인
MySQL 5.6에는 3가지 과장으로 변경되었습니다.
1.my.cnf에서 innodb_log_file_size를 변경
2.MariaDB 서버 재시작
3.에러 로그와 디렉토리에서 변경된 크기의 리두 로그 확인
변경전 50M
-rw-r----- 1 mysql mysql 50331648 Jul 22 22:06 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Jul 22 22:06 ib_logfile1
-rw-r----- 1 mysql mysql 50331648 Jan 13 2021 ib_logfile2
my.cnf파일
innodb_log_file_size=100M 추가
재기동
[root@ysbae ~]# systemctl stop mysql.server
[root@ysbae ~]# systemctl start mysql.server
변경후 100M
-rw-r----- 1 mysql mysql 104857600 Jul 23 01:18 ib_logfile0
-rw-r----- 1 mysql mysql 104857600 Jul 23 01:18 ib_logfile1
-rw-r----- 1 mysql mysql 104857600 Jul 23 01:18 ib_logfile26.3.1.13 데드락 이력
MySQL 5.5에서는 SHOW ENGINE INNODB STATUS 명령으로 DEADLOCK 섹션에서 볼 수 있었습니다 .하지만 여러번 발생하면 가장 최근의 이력만 남고 그전의 이력은 확인할 수 없었습니다.
MySQL 5.6에서는 innodb_print_all_deadlocks 파라미터를 이용해서 DEADLOCK발생 시 MySQL 서버 에러 로그파일에 기록할수 있게 되었습니다.
데드락 발생
삭제하고자 하는 행에 SHARE LOCK(공유 락 S)을 걸고 B세션에서 그 행에 대한 삭제를 시도합니다.삭제에는 Exclusive LOCK(X)가 필요하므로 대기에 빠지게 되고, A세션에서도 같은 행에 대해서 삭제를 시도하면 B세션에서 DEADLOCK이 발생됩니다.
--파라미터 변경
mysql> show variables like 'innodb_print_all_deadlocks';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)
mysql> set global innodb_print_all_deadlocks=on;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'innodb_print_all_deadlocks';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | ON |
+----------------------------+-------+
A세션
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from imptest where id=100 lock in share mode;
+------+------+
| id | n |
+------+------+
| 100 | BB |
+------+------+
1 row in set (0.00 sec)
B세션
mysql> delete from imptest where id=100;
A세션
mysql> delete from imptest where id=100;
Query OK, 1 row affected (0.00 sec)
B세션
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transactionmysql.log 확인
저의 경우에는 경로가 /var/log/mysql/mysql.log 에 있었습니다. my.cnf의 log-error 파라미터 값의 경로에 로그가 있으므로 확인하면 DEADLOCK 정보가 로그로 쌓인것을 확인할 수 있습니다
TRANSACTION 2754329, ACTIVE 13 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 11, OS thread handle 140195252131584, query id 96 localhost root updating
delete from imptest where id=100
RECORD LOCKS space id 263 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`imptest` trx id 2754329 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 6; hex 000000a4f402; asc ;;
1: len 6; hex 0000002a0711; asc * ;;
2: len 7; hex 010000018a0b72; asc r;;
3: len 4; hex 80000064; asc d;;
4: len 2; hex 4242; asc BB;;
RECORD LOCKS space id 263 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`imptest` trx id 2754329 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 6; hex 000000a4f402; asc ;;
1: len 6; hex 0000002a0711; asc * ;;
2: len 7; hex 010000018a0b72; asc r;;
3: len 4; hex 80000064; asc d;;
4: len 2; hex 4242; asc BB;;
TRANSACTION 2754330, ACTIVE 21 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 10, OS thread handle 140195251836672, query id 97 localhost root updating
delete from imptest where id=100
RECORD LOCKS space id 263 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`imptest` trx id 2754330 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 6; hex 000000a4f402; asc ;;
1: len 6; hex 0000002a0711; asc * ;;
2: len 7; hex 010000018a0b72; asc r;;
3: len 4; hex 80000064; asc d;;
4: len 2; hex 4242; asc BB;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 6; hex 000000a4f403; asc ;;
1: len 6; hex 00000029a90d; asc ) ;;
2: len 7; hex 01000001132aac; asc * ;;
3: len 4; hex 80000065; asc e;;
4: len 2; hex 5454; asc TT;;
RECORD LOCKS space id 263 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`imptest` trx id 2754330 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 6; hex 000000a4f402; asc ;;
1: len 6; hex 0000002a0711; asc * ;;
2: len 7; hex 010000018a0b72; asc r;;
3: len 4; hex 80000064; asc d;;
4: len 2; hex 4242; asc BB;;
'스터디 > Real MariaDB' 카테고리의 다른 글
| 06.스토리지 엔진 - Aria 스토리지 엔진 (0) | 2021.07.22 |
|---|---|
| 4.4 옵티마이저 힌트 (0) | 2021.06.14 |
| 4.3.10 Extra컬럼(2) (0) | 2021.06.04 |
| 4.3.10 Extra 컬럼(1) (0) | 2021.06.03 |
| 04.3 실행 계획 분석 (0) | 2021.06.02 |
댓글