(1) Query 단위 메모리 사용량 모니터링
① MySQL 5.7
memory 계측 Enable 설정 (5.7 버전은 메모리 계측 기본값이 Disable 입니다.)
shell> vi my.cnf
[mysqld]
performance-schema-instrument='memory/%=ON'
mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';
Query 단위 memory 사용량 조회
t.processlist_id,
t.thread_id,
t.processlist_time,
t.processlist_user,
t.processlist_host,
t.processlist_db,
m.current_allocated,
m.current_avg_alloc,
m.current_max_alloc,
t.processlist_info as Query
FROM
performance_schema.threads as t
JOIN
sys.memory_by_thread_by_current_bytes as m
where
t.thread_id = m.thread_id
and t.processlist_id!=connection_id()
and t.processlist_command='query'
and t.processlist_db NOT IN('mysql','performance_schema','sys')\G
*************
1. row *************
processlist_id: 67
thread_id: 102
processlist_time: 24
processlist_user: admin
processlist_host: 192.168.43.4
processlist_db: testdb
current_allocated:
1.72 MiB
current_avg_alloc:
1.20 KiB
current_max_alloc:
1.00 MiB
Query: INSERT INTO tbl (this_date)
VALUE (NOW()),(NOW()),(NOW()),(NOW()),(NOW()),(NOW())
*************
2. row *************
processlist_id: 143
thread_id: 178
processlist_time: 65
processlist_user: root
processlist_host: localhost
processlist_db: testdb
current_allocated:
156.25 KiB
current_avg_alloc:
6.25 KiB
current_max_alloc:
106.05 KiB
Query: INSERT INTO tbl (this_date)
VALUE (NOW()),(NOW()),(NOW()),(NOW()),(NOW()),(NOW())
2 rows in
set (0.06 sec)
② MySQL 8.0
8.0 버전부터 메모리 계측 기본값은 Enable 입니다.
Query 단위 memory 사용량 조회
t.processlist_id,
t.thread_id,
t.processlist_user,
t.processlist_host,
t.processlist_db,
t.processlist_command,
t.processlist_time,
t.processlist_state,
t.execution_engine,
m.current_allocated,
m.current_avg_alloc,
m.current_max_alloc,
m.total_allocated,
t.processlist_info
from
performance_schema.threads as t
join
sys.memory_by_thread_by_current_bytes as m
where
t.thread_id=m.thread_id
and t.processlist_id!=connection_id()
and t.processlist_command='query'
and t.processlist_db NOT IN('mysql','performance_schema','sys')\G
************* 1. row *************
processlist_id: 67
thread_id: 102
processlist_user: admin
processlist_host: 192.168.43.4
processlist_db: testdb
processlist_command: Query
processlist_time: 27
processlist_state: update
execution_engine: PRIMARY
current_allocated: 1.72 MiB
current_avg_alloc: 1.20 KiB
current_max_alloc: 1.00 MiB
total_allocated: 21.73 GiB
processlist_info: INSERT INTO tbl (this_date) VALUE (NOW()),(NOW()),(NOW()),(NOW()),(NOW()),(NOW())
************* 2. row *************
processlist_id: 143
thread_id: 178
processlist_user: root
processlist_host: localhost
processlist_db: testdb
processlist_command: Query
processlist_time: 23
processlist_state: update
execution_engine: PRIMARY
current_allocated: 157.75 KiB
current_avg_alloc: 4.93 KiB
current_max_alloc: 106.05 KiB
total_allocated: 11.07 GiB
processlist_info: INSERT INTO tbl (this_date) VALUE (NOW()),(NOW()),(NOW()),(NOW()),(NOW()),(NOW())
2 rows in
set (0.06 sec)