기초공부 - (14) innodb_buffer_pool_pages_data 값이 Total보다 크다면

innodb_buffer_pool_pages_data 또는 innodb_buffer_pool_bytes_data 를 통해 현재 메모리 사용량을 파악할 수가 있습니다.

그리고 우리는 상식적으로 innodb_buffer_pool_size에 설정된 값, 즉 메모리 전체 total 값보다는 작아야한다고 생각합니다.

그러나 total값을 초과하는 경우가 발생합니다.

 

결론부터 말하자면,

압축된 테이블을 읽는 경우, 압축된 데이터를 메모리로 가져와서 압축 해제 후에 작업이 이뤄진다고 합니다. 이럴경우 메모리에는 압축된 데이터와 압축해제 된 데이터가 동시에 존재한다고 하여 메모리가 초과가 되는 것으로 보입니다.

뒷부분에도 나오겠지만, innodb_buffer_pool_pages_data 보다는 innodb_buffer_pool_bytes_data가 좀 더 정확한 데이터로 보입니다.

 

압축된 테이블을 읽을 경우 innodb_buffer_pool_pages_data가 초과 하는지를 확인해보겠습니다.

 

1. 셋팅

(1) innodb_buffer_pool_size = 5M

 - 테스트 서버에서 많은 데이터량이 발생되지 않고 빠른 용량초과를 위해서 가장 낮게 size를 설정하였습니다.

 

(2) 일반 테이블과 compress 테이블 생성

mysql> create table test.temp_table ( a int, b varchar(300));
Query OK, 0 rows affected (0.01 sec)

mysql> create table test.temp_table_compress ( a int, b varchar(300)) row_format=compressed;
Query OK, 0 rows affected (0.01 sec)

 

(3) procedure 생성

mysql> delimiter $$
mysql> drop procedure if exists test.insertdata $$
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> create procedure test.insertdata(IN cnt int)
    -> begin
    ->     declare i int default 1;
    ->     while i <= cnt do
    ->         insert into test.temp_table values(i,concat('test',i));
    ->         set i = i + 1;
    ->     end while;
    -> end $$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

 

(4) 데이터 insert

 

(5)초기화를 위한 데몬 restart

 

2. 초기값 확인

mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_buffer_pool_size | 5242880 |
+-------------------------+---------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

mysql> show status like 'innodb_buffer_pool_%_data';
+-------------------------------+---------+
| Variable_name                 | Value   |
+-------------------------------+---------+
| Innodb_buffer_pool_pages_data | 318     |
| Innodb_buffer_pool_bytes_data | 5210112 |
+-------------------------------+---------+
2 rows in set (0.01 sec)

mysql> show status like 'innodb_buffer_pool_%_total';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| Innodb_buffer_pool_pages_total | 320   |
+--------------------------------+-------+
1 row in set (0.00 sec)

total mbytes=innodb_buffer_pool_pages_total*innodb_page_size/1024/1024

 

3. 일반 데이블 읽었을 때 

초기값과 차이가 없다.

 

4. 압축 테이블 읽었을 때

page_data 값은 증가하였으나, bytes_data는 변화가 없다.

설정 메모리값을 초과하였다.

 

5. 결론

여러 일반 테이블들을 반복적으로 조회해보았으나 메모리 사용량은 innodb_buffer_pool_size를 넘어서지 않았습니다.

그러나 압축 테이블을 조회한 이후는 innodb_buffer_pool_pages_data는 innodb_buffer_pool_size값을 초과하였습니다.

메모리 사용량이 100%를 넘어서다면 압축테이블이 있는지 한번 검토해보시길 바랍니다.

 

그리고 MySQL 8.0 부터 innodb_buffer_pool_bytes_data가 생겼습니다.

캡쳐한 내용처럼 pages_data는 total값을 초과하였지만, bytes_data는 초과하지 않았습니다. 상식적으로 볼때 설정된 메모리 값(Total)보다 증가 될 수는 없을 것으로 생각되어 innodb_buffer_pool_bytes_data 가 더 정확한 값이 아닐지 생각해봅니다. MySQL 5.x 버전에는 어쩔수 없겠지만, 8.0부터는 innodb_buffer_pool_bytes_data를 보는게 좋겠습니다.