[MySQL] Memory 모니터링

■ MySQL 메모리 사용률 모니터링에 필요한 기본 내용

1. MySQL은 하나의 프로세스로 동작.

      세션은 thread로 확장되고 시스템 자원을 할당.

   MySQL의 Memory 부분은 Connection 수의 영향을 받는 부분이며 항상 주의를 요함.

2. InnoDB의 기본 페이지 크기는 16KB

   컴파일을 통하여 페이지 변경 가능

3. 메모리 파리미터는 세션 파라미터와 글로벌(서버)파라미터로 구분

    서버 메모리외에 세션별로 할당되는 메모리가 있기 때문에 컨넥션이 만으면 그만큼 메모리 사용량이 높아짐.

    메모리 크기를 잘 산정해야 함

 

■ 기본 메모리 설정

1. 성능에 가장 영향을 미치는 buffer pool 크기

    시스템 전체의 50% ~ 60%로 잡는다.

파리미터명 : innodb_buffer_pool_size

 

 

■ 메모리 사용량 모니터링 방법

1. Buffer Pool에서 사용되는 Page수.

select count(*) from information_schema.innodb_buffer_page;

Output:
+----------+
| count(*) |
+----------+
| 262142   |
+----------+

 

2.버퍼풀에 어떤 페이지들이 사용되고 있는지 그룹별로 조회

부하가 굉장히 심함. 사용자 접속이 적은 시간에 조회. 혹은 업무에 영향을 받는 시간이 아닐때 사용.

select
page_type as Page_Type,
sum(data_size)/1024/1024 as Size_in_MB
from information_schema.innodb_buffer_page
group by page_type
order by Size_in_MB desc;

Result:
+-------------------+--------------+
| Page_Type         | Size_in_MB   |
+-------------------+--------------+
| INDEX             | 158.66378689 |
| UNKNOWN           | 0.00000000   |
| TRX_SYSTEM        | 0.00000000   |
| SYSTEM            | 0.00000000   |
| FILE_SPACE_HEADER | 0.00000000   |
| IBUF_BITMAP       | 0.00000000   |
| EXTENT_DESCRIPTOR | 0.00000000   |
| ALLOCATED         | 0.00000000   |
| INODE             | 0.00000000   |
| BLOB              | 0.00000000   |
| UNDO_LOG          | 0.00000000   |
| IBUF_FREE_LIST    | 0.00000000   |
| IBUF_INDEX        | 0.00000000   |
+-------------------+--------------+

 

 

 

 

3. 각 인덱스마다 사용되는 버퍼풀 크기

select
table_name as Table_Name, index_name as Index_Name,
count(*) as Page_Count, sum(data_size)/1024/1024 as Size_in_MB
from information_schema.innodb_buffer_page
group by table_name, index_name
order by Size_in_MB desc;

Result:
+-------------------------------------+-----------------------+------------+-------------+
| Table_Name                          | Index_Name            | Page_Count | Size_in_MB  |
+-------------------------------------+-----------------------+------------+-------------+
| `SYS_TABLES`                        | CLUST_IND             |       1423 | 20.48999214 |
| `sbtest`.`sbtest1`                  | PRIMARY               |       1374 | 19.85344791 |
| `sbtest`.`sbtest5`                  | PRIMARY               |       1374 | 19.85344791 |
| `sbtest`.`sbtest3`                  | PRIMARY               |       1374 | 19.85344791 |
| `sbtest`.`sbtest8`                  | PRIMARY               |       1374 | 19.85344791 |
| `sbtest`.`sbtest4`                  | PRIMARY               |       1374 | 19.85344791 |
| `sbtest`.`sbtest6`                  | PRIMARY               |       1374 | 19.85344791 |
| `sbtest`.`sbtest9`                  | PRIMARY               |       1374 | 19.85344791 |
| `sbtest`.`sbtest7`                  | PRIMARY               |       1374 | 19.85344791 |
| `sbtest`.`sbtest10`                 | PRIMARY               |       1374 | 19.85344791 |
| `sbtest`.`sbtest2`                  | PRIMARY               |       1374 | 19.85344791 |
| `sbtest`.`sbtest4`                  | k_4                   |        226 |  2.02505684 |
| `sbtest`.`sbtest3`                  | k_3                   |        226 |  2.01777077 |
| `sbtest`.`sbtest6`                  | k_6                   |        226 |  2.01542759 |
| `sbtest`.`sbtest9`                  | k_9                   |        225 |  2.00539780 |
| `sbtest`.`sbtest5`                  | k_5                   |        226 |  2.00282669 |
| `sbtest`.`sbtest2`                  | k_2                   |        226 |  2.00012016 |
| `sbtest`.`sbtest7`                  | k_7                   |        226 |  1.99518108 |
| `sbtest`.`sbtest8`                  | k_8                   |        226 |  1.98054695 |
| `sbtest`.`sbtest1`                  | k_1                   |        216 |  1.92529964 |
| `sbtest`.`sbtest10`                 | k_10                  |        203 |  1.86111736 |
| `SYS_COLUMNS`                       | CLUST_IND             |          1 |  0.01065063 |
| `mysql`.`innodb_index_stats`        | PRIMARY               |          1 |  0.00634384 |
| `SYS_INDEXES`                       | CLUST_IND             |          1 |  0.00341129 |
| `SYS_FIELDS`                        | CLUST_IND             |          1 |  0.00262165 |
| `SYS_DATAFILES`                     | SYS_DATAFILES_SPACE   |          1 |  0.00146294 |
| `SYS_TABLESPACES`                   | SYS_TABLESPACES_SPACE |          1 |  0.00143433 |
| `SYS_TABLES`                        | ID_IND                |          1 |  0.00111580 |
| `mysql`.`innodb_table_stats`        | PRIMARY               |          1 |  0.00070381 |
| `mysql`.`server_cost`               | PRIMARY               |          1 |  0.00026608 |
| `mysql`.`engine_cost`               | PRIMARY               |          1 |  0.00010681 |
| `SYS_FOREIGN`                       | FOR_IND               |          1 |  0.00000000 |
| `mysql`.`slave_worker_info`         | PRIMARY               |          1 |  0.00000000 |
| `SYS_FOREIGN`                       | REF_IND               |          1 |  0.00000000 |
| `mysql`.`servers`                   | PRIMARY               |          1 |  0.00000000 |
| `mysql`.`time_zone_name`            | PRIMARY               |          1 |  0.00000000 |
| NULL                                | NULL                  |     113644 |  0.00000000 |
| `mysql`.`time_zone_transition_type` | PRIMARY               |          1 |  0.00000000 |
| `mysql`.`time_zone_transition`      | PRIMARY               |          1 |  0.00000000 |
| `mysql`.`slave_master_info`         | PRIMARY               |          1 |  0.00000000 |
| `mysql`.`plugin`                    | PRIMARY               |          1 |  0.00000000 |
| `mysql`.`slave_relay_log_info`      | PRIMARY               |          1 |  0.00000000 |
| `mysql`.`time_zone_leap_second`     | PRIMARY               |          1 |  0.00000000 |
| `mysql`.`gtid_executed`             | PRIMARY               |          1 |  0.00000000 |
| `mysql`.`time_zone`                 | PRIMARY               |          1 |  0.00000000 |
+-------------------------------------+-----------------------+------------+-------------+

 

4. sys schema를 이용하여 모니터링 하는방법

SELECT substring_index(`x$memory_global_by_current_bytes`.`event_name`,'/',2) AS `code_area`,
sys.`format_bytes`(sum(`x$memory_global_by_current_bytes`.`current_alloc`)) AS `current_alloc`
FROM `sys`.`x$memory_global_by_current_bytes`
GROUP BY substring_index(`x$memory_global_by_current_bytes`.`event_name`,'/',2)
ORDER BY sum(`x$memory_global_by_current_bytes`.`current_alloc`) DESC;
+---------------------------+---------------+
|     code_area             | current_alloc |
+---------------------------+---------------+
| memory/innodb             | 843.24 MiB    |
| memory/performance_schema | 81.29 MiB     |
| memory/mysys              | 8.20 MiB      |
| memory/sql                | 2.47 MiB      |
| memory/memory             | 174.01 KiB    |
| memory/myisam             | 46.53 KiB     |
| memory/blackhole          | 512 bytes     |
| memory/federated          | 512 bytes     |
| memory/csv                | 512 bytes     |
| memory/vio                | 496 bytes     |
+---------------------------+---------------+

 

■ 총 메모리 할당량

인터넷 검색시 대부분 메모리 사용량이라고 하지만 정확하게는 메모리 할당량이라고 해야 정확한 내용입니다.

파라미터에 할당한 메모리 크기이고 그 크기들을 더한것이기 때문입니다.

참고로 실제 사용량은 이 할당량보다 작을 수 있습니다. DB서버에서 사용하는 만큼 메모리를 할당하기 때문입니다.

 

- MySQL 5.6에서 조회

SET @kilo_bytes = 1024;
SET @mega_bytes = @kilo_bytes * 1024;
SET @giga_bytes = @mega_bytes * 1024;

SELECT
( @@key_buffer_size
+ @@query_cache_size
+ @@innodb_buffer_pool_size + @@innodb_additional_mem_pool_size
+ @@innodb_log_buffer_size + @@binlog_cache_size
+ @@max_connections * (
@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@tmp_table_size
+ @@join_buffer_size +  @@thread_stack + @@net_buffer_size + @@max_allowed_packet
)) / @giga_bytes AS MAX_MEMORY_GB;

 

- MySQL 5.7 조회

(innodb_additional_mem_pool_size 파라미터 제거됨)
SET @kilo_bytes = 1024;
SET @mega_bytes = @kilo_bytes * 1024;
SET @giga_bytes = @mega_bytes * 1024;

SELECT
( @@key_buffer_size
+ @@innodb_buffer_pool_size
+ @@innodb_log_buffer_size + @@binlog_cache_size
+ @@max_connections * (
@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@tmp_table_size
+ @@join_buffer_size + @@thread_stack + @@net_buffer_length + @@max_allowed_packet
)) / @giga_bytes AS MAX_MEMORY_GB;

참고로 tmp_table_size, max_heap_table_size 중 작은게 할당이 됩니다. 특별히 설정하지 않으셨다면 2개의 값은 같습니다. 그러므로 위의 수식을 사용해 계산하시면 됩니다.

 

※도움이 되셨다면 광고클릭 한번 부탁드립니다.※