Mysql Slow Query log (부하 관리)

Mysql Database를 운영하다 보면 여러 가지 이유로 Application이 종료되거나

DB 처리가 되지않아서 에러가 생기는 경우가 많습니다

 

이러한 경우 문제가 되는 DB상의 처리 부분을 찾아야 하는데

유용하게 로그를 통해 부하가 걸리는 쿼리를 찾는 기능이 Slow Query Log 입니다

 

DB에 접근해서 쿼리문을 실행시킴에 있어서 어느 부분에서 지연이 발생하는지

로그를 통해 파악할 수 있으므로 해당 부분을 수정함으로써

DB의 부하를 관리하고 그로 인해 생길 수 있는 에러에 대비할 수 있습니다

 

대부분의 쿼리에서 어느 정도 지연은 문제가 되지 않으나 10초 이상의 지연은

발생하기 시작하면 연속적인 지연 문제로 커질 수 있기에

대부분 5~10초 이상의 Slow 쿼리를 찾아서 대비를 하는 편입니다

 

vim /etc/my.cnf

 

먼저 Slow Query Log를 설정하기 위해서 my.cnf 파일을 열어줍니다

 

# Enable the full query log. Every query (even ones with incorrect
# syntax) that the server receives will be logged. This is useful for
# debugging, it is usually disabled in production use.
#log

# Print warnings to the error log file.  If you have any problem with
# MariaDB you should enable logging of warnings and examine the error log
# for possible explanations.
log_warnings

# Log slow queries. Slow queries are queries which take more than the
# amount of time defined in "long_query_time" or which do not use
# indexes well, if log_short_format is not enabled. It is normally good idea
# to have this turned on if you frequently add new queries to the
# system.
slow_query_log

# All queries taking more than this amount of time (in seconds) will be
# trated as slow. Do not use "1" as a value here, as this will result in
# even very fast queries being logged from time to time (as MariaDB
# currently measures time with second accuracy only).
long_query_time = 2

# The directory used by MySQL for storing temporary files. For example,
# it is used to perform disk based large sorts, as well as for internal
# and explicit temporary tables. It might be good to put it on a
# swapfs/tmpfs filesystem, if you do not create very large temporary
# files. Alternatively you can put it on dedicated disk. You can
# specify multiple paths here by separating them by ";" - they will then
# be used in a round-robin fashion.
tmpdir = /data/mysql/mysql-tmp

 

우리가 고쳐야 할 부분은 중간에 있는 slow_query_log 부분입니다

위에 수정되지 않은 채 덩그러니 있는 slow_query_log를 다음과 같이 수정합니다

 

slow_query_log = 1
slow_query_log_file = /data/mysql/mysql_slow.log
long_query_time=10

 

첫 번째 slow_query_log = 1 은 ON 0은 OFF를 나타냅니다

두 번째 항목은 slow_query_log를 남길 dir 위치를 적어주면 됩니다

마지막 세 번째 줄이 중요한데 이 부분이 위에서 말했던 시간입니다

즉 1로 설정되면 1초 이상 지연이 생기는 쿼리를 로그로 남깁니다

 

5~10초 이상의 지연이 길게 발생하는 쿼리를 남기기 위해 이 부분은 10으로 설정했습니다

마지막으로 mysql을 재시작해줍니다

 

systemctl restart mysql

 

여기서 중요한 점 하나는 /data/mysql의 위치에서 mysql의 소유자가 mysql이어야 한다는 점입니다

root일 경우에는 slow_query_log가 생성되지 않고 기록도 되지 않으니 권한 설정 꼭 해주셔야 합니다

 

cd /data
chown mysql:mysql mysql