MySQL CPU LOAD 부하가 너무 심합니다
본문
[mysqld]
symbolic_links=0
skip_name_resolve
skip_external_locking
performance_schema=ON
innodb_strict_mode=1
max_connections=500
thread_cache_size=50
tmp_table_size=32M
innodb_buffer_pool_size=20G
innodb_buffer_pool_instances=4
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=1
innodb_file_per_table=1
innodb_log_files_in_group=2
innodb_log_file_size=512M
innodb_sort_buffer_size=4M
innodb_stats_on_metadata=0
innodb_read_io_threads=64
innodb_write_io_threads=64
key_buffer_size=24M
interactive_timeout=180
wait_timeout=180
join_buffer_size=8M
read_buffer_size=3M
read_rnd_buffer_size=4M
sort_buffer_size=4M
table_definition_cache=40000
table_open_cache=40000
open_files_limit=60000
max_heap_table_size=128M
slow_query_log=ON
slow_query_log_file=/var/log/mariadb/slow_query.log
long_query_time=1
log_error=/var/log/mariadb/mariadb_error.log
query_cache_limit=2G
query_cache_size=10G
query_cache_type=ON
현재 mysql 설정상황입니다 여러모로 많이 검색해본 결과 my.cnf 문제는 없어보입니다
서버는 16코어 32기가램 이구요 동시접속자수 약 100명에 게시글이나 검색이 많이 이루어집니다
CPU점유율이 치솟는게 계속 유지가 되는것이 아닌 약 20~30초에 한번씩 쑥 올라왔다 잠깐 내려갔다가
조금있다가 또 올라왔다가 내려갔다가 그렇습니다
Slow Query 를 체크해보려고 로깅을 해봤으나 아무것도 로깅에 찍히지 않습니다
설정값을 보다싶이 1초 이상이구요 select sleep(2) 로 하면 로깅 들어오는걸 보면 로깅이 안되진 않습니다
우선 궁금한점이 많긴한데
query_cache_limit=2G
query_cache_size=10G
query_cache_type=ON
쿼리 캐시 사용시 사이트 속도가 전반적으로 나쁘지 않습니다 현재 이렇게 유지중인데
CPU부하는 지속적으로 2~600% 까지 계속 왔다갔다 하면서 일시적으로 로딩 딜레이가 발생합니다
쿼리 캐시를 끌 경우 부하률이 1000% 가 넘으면서 접속 로딩이 10초 이상 지연됩니다
쿼리 캐시를 끄면 아예 이용이 불가능한 상황입니다
테이블 컬럼들에 대해서 자주 사용하는 것들은 INDEX 도 처리를 해준 상태이며
NGINX, PHP-FPM 자체는 크게 부하률이 없고 설정도 잘 마치었다고 생각합니다
1. 어떻게 해야 mariadb 부하를 잡을 수 있을까요?
2. 쿼리캐시 사용시 일시적 끊김 및 부하는 있으나 (현재상태) 미사용시 아예 운영이 불가능할정도인데
이유가 뭔가요?
3. htop 을 보면 램 사용량이 현저히 적은데 이게 좋은건지 아님 설정이 잘못된건지 모르겟습니다
답변 3
1. 정확한 원인은 알수없지만 한번에 쿼리되는 output 데이터의 양을 확인해보세요.
row(각 컬럼 사이즈의 합) 크기 * 응답 rows 수 를 하면 대략적인 크기가 나옵니다.
애초에 아웃풋 데이터가 많아버리면 INDEX 가 가져오는 성능이 무의미할것 같습니다.
2. 아마도 쿼리 최적화가 더 필요한 상황이 아닐까 추측해봅니다. (쿼리문 자체든 I/O 양이든)
3. query_cache_limit=2G 설정한 만큼 잡히고 있는게 아닌가 싶네요
그리고 쿼리 캐시를 사용한다해도 대상 테이블에 DML(INSERT, UPDATE, DELETE) 관련 동작이 발생되면
기존 캐시는 지워지고 다시 캐시를 띄울겁니다.
만약 이와 같은 상황이 발생했다고 치면
DBMS 스레드 들이 공유자원인 쿼리캐시를 동시 접근, 락이 발생되면서 지연시간이 생기는 것일수도 있습니다.
DBMS 설정보다는 쿼리문과 어플리케이션 최적화쪽으로 집중해야 되지 않나 생각되며
general log 도 로깅이 가능한 환경이라면 한번 켜보시고 쿼리 종류를 수집해
전체적으로 검수해 보는 방법도 고민해보시기 바랍니다.
Slow query 옵션에
실행 시간 외에
Index를 안 쓰는 query도 출력하는
설정이 있습니다.
그리고 cpu 부하가 피크일 때
https://dev.mysql.com/doc/refman/8.0/en/show-processlist.html
상태를 한번 알아 보세요.
시스템엔지니어입장에서 말씀드리자면,
1. DB 는 cpu클럭빨 많이 받아요, 코어수 관계별로 없습니다 ( 웹은 관련 많고요 )
cpu 클럭 4Ghz 이상의 가능한, 신cpu 들어간 걸로 1차로, DB서버 분리 하시고요
2. 안그래도 부하인데 쿼리캐시 끄면 메모리에서 바로 응답못하고 디스크 읽어서 cpu에서 부하가 더 걸리는것처럼 보일거에요, 디스크쪽 지연있을때도 cpu 부하올라가는것처럼 나타납니다.
iotop 입력 얼마나 읽고쓰고 하는지랑, hdparm -t /dev/sda 등 읽기속도 몇메가 정도 나오는지 보세요~
3. 그건괜찮아요 그만큼 메모리를 많이사용할일이 없다는뜻입니다. my.cnf 도 괜찮게 설정한거같고요
Timing buffered disk reads: 2440 MB in 3.00 seconds = 812.89 MB/sec
hdparm 으로 찍었을때 속도입니다 DB서버 분리를 해야될까요?
램 사용량이 적은건 정상이라는 것이죠?
