Mysql의 InnoDB는 In-Memory 구조와 On-Disk 구조로 이루어져 있습니다.
In-Memory 구조
▶ Buffer Pool
InnoDB가 액세스할 때 테이블 과 인덱스 데이터를 캐시하는 메인 메모리영역입니다.
해당 영역은 메모리로부터 데이터를 직접 처리할 수 있게하여 처리속도를 향상시킵니다.
dedicated server의 경우, 물리 메모리의 최대 80%까지 버퍼풀이 할당됩니다.
효과적으로 읽기 작업을 하기 위해서 버퍼 풀은 여러 행을 보유할 수 있는 페이지들로 나눠집니다.
효과적으로 캐시 관리를 하기위해 버퍼 풀은 링크된 페이지들의 목록으로 구현됩니다.
LRU알고리즘으로 관리되어 자주 사용하지 않는 데이터는 age out(삭제) 됩니다.
- LRU 알고리즘
버퍼 풀은 LRU(leat recently used)알고리즘으로 관리됩니다. 버퍼 풀에서 새 페이지를 추가하기 위해 공간이 필요한 경우 제일 최근에 사용한 페이지가 제거되고 새 페이지가 목록의 중간에 추가됩니다. 이러한 중간 점(mid point) 삽입 전략은 리스트를 두개의 서브리스트로 취급합니다.
- 헤드(Head)부분 = 최근에 액세스한 새로운(Young) 페이지들의 서브리스트
- 꼬리(Tail)부분 = 최근에 "덜" 사용된 오래된 페이지들의 서브리스트
알고리즘은 자주 사용되는 페이지를 새 서브리스트 내에 유지합니다.
오래된 서브리스트는 덜 사용된 페이지(less frequently used)에 포함됩니다. 이 페이지들은 제거 후보입니다.
Default로 알고리즘은 다음과 같이 운영됩니다.
º 버퍼 풀의 3/8은 오래된 서브리스트로 관리됩니다.
º 리스트의 중간 점(midpoint)는 새 서브리스트의 꼬리(tail)과 오래된 서브리스트의 헤드(Head)의 경계입니다.
º InnoDB가 버퍼 풀 내의 페이지를 읽을 때, 중간 점(오래된 서브리스트의 헤드)에 페이지를 삽입합니다. 페이지는 SQL 쿼리같은 사용자가 시작한(user-initiated) 작업이나 InnoDB에 의해 자동으로 수행되는 미리 읽기(read-ahead)작업의 일부로 필요하므로 읽을 수있습니다.
º 오래된 서브리스트에 있는 페이지에 접근하면 페이지가 새로운(Young) 페이지가 되어 새로운 서브리스트의 헤드로 이동하게됩니다. 만약 페이지가 사용자가 시작한(user_initiated) 작업에 의해서 읽은 경우, 즉시 첫 번째 액세스가 발생하고 페이지는 새로운(Young) 페이지가 됩니다.
만약 페이지가 미리 읽기(read-ahead)작업 때문에 읽은 경우, 첫 번째 액세스는 즉시 발생하지 않으며 페이지가 제거되기 전에 발생하지 않을 수도 있습니다.
º 데이터베이스 운영 중에 퍼버 풀에있는 접근되지 않은 페이지는 리스트의 꼬리쪽으로 움직이는 age상태가 됩니다.
오래된 서브리스트와 새로운 서브리스트의 페이지는 다른 페이지가 새로 만들어 짐으로써 age가 됩니다.
오래된 서브리스트에 있는 페이지 또한 중간 점(midpoint)에 새로운 페이지가 삽입될 때 age됩니다.
결국, 사용되지 않은 채로 남아 있는 페이지는 오래된 서브리스트의 끝에 가게되고 삭제됩니다.
Default로 쿼리에서 읽은 페이지는 즉시 새로운 서브리시트로 이동하게되므로 버퍼 풀에서 더 오래 머무릅니다.
Table Scan, mysqldump작업,where 절 없는 select 문에 대해, 버퍼 풀 내의 많은 양의 데이터를 가져올 수 있고 새 데이터가 다시 사용되지 않더라도 동등한 양의 오래된 데이터를 삭제할 수 있습니다.
read-ahead 백그라운드 쓰레드에 의해 로드된 페이지와 오직 한번 새로운 리스트 헤드에 접근한 페이지도 비슷하게 운영됩니다.
이런 상황은 자주 사용되는 페이지를 오래된 서브리스트로 보내어 삭제될수도 있습니다.
/*+ 기본적으로 Oracle의 버퍼와 같은 원리로 작동됩니다. Oracle은 블록단위로 관리되고 Mysql은 Page단위로 관리가 됩니다. 자주 사용하는 Page는 계속해서 Buffer Pool에 남아있게되고 자주사용하지 않는 Page는 점점 밀려나다 Buffer Pool에서 삭제가 됩니다. 기본적인 사항은 이것이고, 위의 글은 Mysql만의 Buffer Pool 동작원리를 설명한 글인데 매뉴얼을 번역하고 제가 맞는 뜻인것 같은 표현으로 바꾸어 한글로 표현한 것이기 때문에 매끄럽지 않은 부분이 있을 수 있습니다.
*/
- Buffer Pool Configuration
성능 향상을 위해 버퍼 풀을 다양한 측면에서 구성할 수 있습니다.
º 이상적으로는 버퍼 풀의 크기는 크게 설정하여 서버의 다른 프로세스가 과도한 페이징 없이 실행되도록 충분히 메모리를 설정합니다. Buffer Pool이 크면 클수록 InnoDB는 in-Memory Database처럼 작동하고, subsequent read 중에 디스크로부터 데이터를 한번에 읽을 수 있습니다.
º 64bit 시스템(충분한 메모리가 있는)에서 버퍼 풀을 여러 부분으로 분할하여 메모리구조에 대한 경합을 최소화 할 수 있습니다.
º 자주 액세스하지 않는 데이터를 버퍼풀로 가져오는 조작으로 인한 갑작스러운 활동 급증에 관계없이 자주 액세스하는 데이터를 메모리에 보관할 수 있습니다.
º 페이지가 곧 필요할 것으로 예상하여 미리 읽기(read-ahead) 요청을 수행하여 페이지를 버퍼 풀로 비동기식으로 프리패치하는 시기와 방법을 제어할 수 있습니다.
º 백그라운드 플러시가 발생하는 시기와 플러시 속도가 워크로드에 따라 동적으로 조정되는지 여부를 제어할 수 있습니다.
º 서버가 다시 시작된 후 긴 워밍업 기간을 피하기 위해 InnoDB가 현재 버퍼 풀 상태를 유지하는 방법을 구성할 수 있습니다.
Show engine innodb status 명령어를 통해서 액세스할 수 있는 InnoDB 표준 모니터링 결과를 제공합니다.
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456
Dictionary memory allocated 387733
Buffer pool size 8192
Free buffers 7002
Database pages 1186
Old database pages 457
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1044, created 142, written 163
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: 1186, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0] mysql> show engine innodb status;
+--------+------+---------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- ---------------------------------------------------+
| Type | Name | Status |
+--------+------+---------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- ---------------------------------------------------+
| InnoDB | |
=====================================
2021-01-11 00:55:40 0x7fd4b052b700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 35 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1 srv_active, 0 srv_shutdown, 20704 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 0
OS WAIT ARRAY INFO: signal count 0
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 2731273
Purge done for trx's n:o < 2731270 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422026473037208, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422026473036352, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422026473035496, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1067 OS file reads, 205 OS file writes, 39 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
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 1439945713
Log buffer assigned up to 1439945713
Log buffer completed up to 1439945713
Log written up to 1439945713
Log flushed up to 1439945713
Added dirty pages up to 1439945713
Pages flushed up to 1439945713
Last checkpoint at 1439945713
13 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456
Dictionary memory allocated 387733
Buffer pool size 8192
Free buffers 7002
Database pages 1186
Old database pages 457
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1044, created 142, written 163
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: 1186, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=3782, Main thread ID=140550998009600 , 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 315, deleted 0, read 4611
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.00 sec)
해당 파라미터에 대해서 자세한 값을 알기위해서는 아래의 블로그에서 14.2 InnoDB Buffer Pool Metrics을 참고하시길 바랍니다.
myinfrabox.tistory.com/46?category=814732
[MySQL][InnoDB] 메모리구조
■ Bufferpool 버퍼 풀은 InnoDB가 액세스 할 때 테이블 및 인덱스 데이터를 캐시하는 메인 메모리의 영역입니다. 버퍼 풀은 자주 사용하는 데이터를 메모리에서 직접 처리 할 수있게하여 처리 속도
myinfrabox.tistory.com
▶ Change Buffer
변경 버퍼는 해당 페이지가 버퍼 풀에 없을 때 보조 인덱스(secondary index) 페이지의 변경사항을 캐시하는 특수한 데이터 구조입니다. 변경된 버퍼들, INSERT,UPDATE 또는 DELETE 작업 (DML)의 결과들을 나중에 다른 읽기 작업으로 인해 버퍼 풀안에 페이지가 로드될 때 병합됩니다.
클러스터드 인덱스(Clustered Index)와 다르게 보조 인덱스는 일반적으로 nonunique index입니다. 보조 인덱스의 insert작업은 비교적 임의의 순서로 수행됩니다. DELETE와 UPDATE는 인덱스 트리에 인접하지 않은 보조 인덱스 페이지에 영향을 줄 수 있습니다. 나중에 캐시된 변경사항을 병합할 때, 다른 작업에 의해 버퍼풀안의 페이지가 읽혀지고 있을 때 디스크로부터 보조 인덱스 페이지를 버퍼풀로 읽는데 필요한 실질적인 random access I/O를 피할 수 있습니다.
시스템이 대부분 idle 상태이거나 slow shutdown되고 있는 중에 일어나는 Purge 작업은 정기적으로 인덱스 페이지에서 디스크로 씁니다. Purge 작업은 각 값이 디스크에 직접 쓰여진 것 보다 일련의 인덱스값에 대한 디스크 블록을 더욱 효과적으로 쓸 수 있습니다.
영향을 받는 행과 업데이트 할 보조 인덱스가 많은 경우 변경 버퍼 병합작업에 몇시간이 걸릴 수 있습니다.
- Configuring Change Buffering
INSERT,UPDATE,DELETE 작업을 수행할 때 인덱스된 컬럼의 값(특히 보조 키값)이 정렬되지 않은 순서로 있는 경우, 보조인덱스를 최신상태로 유지하기 위해서는 상당한 I/O가 필요합니다.
변경버퍼는 관련된 페이지가 퍼버 풀에 없을 때 보조 인덱스 항목에 대한 변경사항을 캐시하므로 디스크에서 직접 페이지 읽기같은 비용이 많이 생기는 I/O를 피할 수 있습니다. 버퍼된 변경사항들은 페이지가 버퍼풀에 로드될 때 병합되고 업데이트된 페이지는 디스크로 flush 됩니다. InnoDB 메인 쓰레드는 서버가 거의 idle상태이거나 slow shutdown 중에 버퍼된 변경사항을 병합합니다.
디스크 읽기 및 쓰기 횟수가 줄어들기 때문에 변경버퍼 기능은 대량 삽입과 대량의 DML작업을 수행하는 애플리케이션과 같은 I/O bound작업에 효과적입니다. 하지만 변경 버퍼는 버퍼 풀의 일부를 차지하므로 데이터 페이지를 캐시하는데 사용 가능한 메모리가 줄어들게 됩니다. 작업 세트가 버퍼 풀에 거의 맞거나 테이블에 보조 인덱스가 비교적 적은 경우 변경버퍼를 비활성화 하는것이 유용할 수 있습니다.
default는
| innodb_change_buffering=all[ALL,NONE,INSERTS,CHANGES,PURGES] |
입니다.
set GLOBAL 옵션으로 변경하거나 my.cnf파일을 수정하여 변경할 수 있습니다. 설정을 변경하면 새 작업의 버퍼링에 영향을 줍니다. 기존 버퍼링된 항목의 병합에는 영향이 없습니다.
- Configuring the Change Buffer Maximum Size
innodb_change_buffer_max_size변수를 사용하여 변경 버퍼의 최대 크기를 버퍼 풀에 총 크기에 대한 백분율로 구할 수 있습니다. default는 25이며 50까지 설정가능합니다.
- Monitoring the Change Buffer
show engine INNODB statis\G; 명령어를 통해서 확인할 수 있습니다.
INSERT BUFFER AND ADAPTIVE HASH INDEX 라는 문구 아래에 있습니다.
mysql> show engine INNODB status\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2021-01-11 01:46:58 0x7fd4b052b700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 28 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1 srv_active, 0 srv_shutdown, 23781 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 0
OS WAIT ARRAY INFO: signal count 0
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 2731273
Purge done for trx's n:o < 2731270 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422026473037208, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422026473036352, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422026473035496, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1067 OS file reads, 205 OS file writes, 39 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
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 1439945713
Log buffer assigned up to 1439945713
Log buffer completed up to 1439945713
Log written up to 1439945713
Log flushed up to 1439945713
Added dirty pages up to 1439945713
Pages flushed up to 1439945713
Last checkpoint at 1439945713
13 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456
Dictionary memory allocated 387733
Buffer pool size 8192
Free buffers 7002
Database pages 1186
Old database pages 457
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1044, created 142, written 163
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: 1186, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=3782, Main thread ID=140550998009600 , 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 315, deleted 0, read 4611
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.00 sec)
ERROR:
No query specified
| mysql> select name,comment from INFORMATION_SCHEMA.INNODB_METRICS where NAME like '%ibuf%'\G; |
*************************** 1. row ***************************
name: buffer_page_read_index_ibuf_leaf
comment: Number of Insert Buffer Index Leaf Pages read
*************************** 2. row ***************************
name: buffer_page_read_index_ibuf_non_leaf
comment: Number of Insert Buffer Index Non-Leaf Pages read
*************************** 3. row ***************************
name: buffer_page_read_ibuf_free_list
comment: Number of Insert Buffer Free List Pages read
*************************** 4. row ***************************
name: buffer_page_read_ibuf_bitmap
comment: Number of Insert Buffer Bitmap Pages read
*************************** 5. row ***************************
name: buffer_page_written_index_ibuf_leaf
comment: Number of Insert Buffer Index Leaf Pages written
*************************** 6. row ***************************
name: buffer_page_written_index_ibuf_non_leaf
comment: Number of Insert Buffer Index Non-Leaf Pages written
*************************** 7. row ***************************
name: buffer_page_written_ibuf_free_list
comment: Number of Insert Buffer Free List Pages written
*************************** 8. row ***************************
name: buffer_page_written_ibuf_bitmap
comment: Number of Insert Buffer Bitmap Pages written
*************************** 9. row ***************************
name: ibuf_merges_insert
comment: Number of inserted records merged by change buffering
*************************** 10. row ***************************
name: ibuf_merges_delete_mark
comment: Number of deleted records merged by change buffering
*************************** 11. row ***************************
name: ibuf_merges_delete
comment: Number of purge records merged by change buffering
*************************** 12. row ***************************
name: ibuf_merges_discard_insert
comment: Number of insert merged operations discarded
*************************** 13. row ***************************
name: ibuf_merges_discard_delete_mark
comment: Number of deleted merged operations discarded
*************************** 14. row ***************************
name: ibuf_merges_discard_delete
comment: Number of purge merged operations discarded
*************************** 15. row ***************************
name: ibuf_merges
comment: Number of change buffer merges
*************************** 16. row ***************************
name: ibuf_size
comment: Change buffer size in pages
*************************** 17. row ***************************
name: innodb_ibuf_merge_usec
comment: Time (in microseconds) spent to process change buffer merge
INFORMATION_SCHEMA의 INNODB_BUFFER_PAGE 테이블을 통해서 대략적인 변경 버퍼 페이지의 수와 퍼센테이지를 확인할 수 있습니다.
mysql> SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
-> WHERE PAGE_TYPE LIKE 'IBUF%') AS change_buffer_pages,
-> (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE) AS total_pages,
-> (SELECT ((change_buffer_pages/total_pages)*100))
-> AS change_buffer_page_percentage;
+---------------------+-------------+-------------------------------+
| change_buffer_pages | total_pages | change_buffer_page_percentage |
+---------------------+-------------+-------------------------------+
| 2 | 8192 | 0.0244 |
+---------------------+-------------+-------------------------------+
1 row in set (0.08 sec)
PERFORMANCE_SCHEMA는 고급 성능 모니터링을 위한 변경 버퍼 뮤텍스 대기 정보를 제공합니다.
mysql> SELECT * FROM performance_schema.setup_instruments
-> WHERE NAME LIKE '%wait/synch/mutex/innodb/ibuf%';
+-------------------------------------------------------+---------+-------+------------+------------+---------------+
| NAME | ENABLED | TIMED | PROPERTIES | VOLATILITY | DOCUMENTATION |
+-------------------------------------------------------+---------+-------+------------+------------+---------------+
| wait/synch/mutex/innodb/ibuf_bitmap_mutex | NO | NO | | 0 | NULL |
| wait/synch/mutex/innodb/ibuf_mutex | NO | NO | | 0 | NULL |
| wait/synch/mutex/innodb/ibuf_pessimistic_insert_mutex | NO | NO | | 0 | NULL |
+-------------------------------------------------------+---------+-------+------------+------------+---------------+
3 rows in set (0.01 sec)
▶Adaptive Hash Index
적응형 해시 인덱스 기능을 통해 InnoDB는 In-memory database처럼 트랜잭션 기능이나 안정성을 희생하지 않고도 충분한 메모리 성능을 발휘할 수 있습니다.
아래의 변수에 의해서 활성화됩니다.
| innodb_adaptibe_hash_index |
▶Log Buffer
디스크의 로그 파일에 기록 될 데이터를 보관하는 메모리영역입니다. 로그 버퍼 사이즈는 innodb_log_buffer_size 변수로 정의됩니다. default는 16MB입니다.
로그버퍼의 내용은 주기적으로 디스크로 flush됩니다.
크기가 큰 로그 버퍼를 사용하면 트랜잭션이 커밋되기 전에 redo log 데이터를 디스크에 쓸 필요가 없이 큰 트랜잭션을 실행할 수 있습니다.
로그 버퍼의 내용을 디스크에 쓰고 flush하는 방법을 제어합니다.
| innodb_flush_log_at_trx_commit |
로그 flush 빈도를 제어합니다.
| innodb_flush_log_at_timeout |
출처 : myinfrabox.tistory.com/46?category=814732 , Mysql 8.0 Reference Manual
'다른 DBMS > MySQL&MariaDB' 카테고리의 다른 글
| InnoDB Doublewrite Buffer (0) | 2021.01.14 |
|---|---|
| InnoDB On-Disk Structures (0) | 2021.01.13 |
| Mysql 로그 종류 (0) | 2021.01.09 |
| InnoDB 소개 (0) | 2021.01.08 |
| mysqlslap (0) | 2021.01.06 |
댓글