MariaDB 튜닝하기

회사에 있을 땐 튜닝된 DB를 사용하다 개인 웹서버에 DB를 설치하고 나니 어디부터 손을 대야할지 막막했다.

구글해서 나온 설정값을 그냥 복붙해서 써도 될지 고민하다 구글을 뒤져보니 튜닝에 도움을 주는 프로그램이 있었다.

 

https://github.com/major/MySQLTuner-perl

major/MySQLTuner-perl

설치된 DB가 호환되는지는 Compatibility 항목에서 확인하자.

 

설치/사용 방법은 몹시 간단했다. 아래의 항목을 확인하자.

https://github.com/major/MySQLTuner-perl#optional-performance-schema-and-sysschema-installation-for-mariadb-10x

major/MySQLTuner-perl

 

우선 적당한 경로에서 스크립트를 다운로드 한다.

# cd /home/build
# wget http://mysqltuner.pl/ -O mysqltuner.pl
# wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
# wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv

 

my.cnf 또는 server.cnf에서 performance schema를 활성화 해주고

[mysqld]
performance_schema = on

 

퍼포먼스 모니터링을 위한 뷰를 생성해주는 스크립트를 내려받아 실행한다.

# curl "https://codeload.github.com/FromDual/mariadb-sys/zip/master" > mariadb-sys.zip
# unzip mariadb-sys.zip
# cd mariadb-sys-master/
# mysql -u root -p < ./sys_10.sql

 

그리고 튜너 스크립트에 실행 권한을 주고 실행한다.

# chmod +x mysqltuner.pl
# ./mysqltuner.pl --outputfile /home/build/result_mysqltuner.txt --buffers --dbstat --idxstat --sysstat --pfstat --tbstat --host localhost --port 20203

만약 ip와 port를 따로 설정해야 한다면 --host 와 --port 옵션을 추가한다.

프롬프트의 안내에 따라 root 계정으로 로그인하면 outputfile 에 설정한 경로에 결과 파일이 저장된다. 해당 파일을 열어보자

 

각 라인의 제일 앞엔 [] 로 쌓여진 부분이 있다.

[ -- ] : 단순한 infomation

[OK] : 현재 설정된 값이 적절하다는 의미

[ !! ] : 현재 설정된 값에 문제가 있다는 의미

 

눈여겨 볼 것은 !! 부분이다.

 

이 tuner 스크립트는 많이 유명하기 때문에 [ !! ] 다음에 오는 문구를 복사해서 그대로 구글에 검색해보면 각 항목에 대한 해결책이 제시되어 있다. 정 없다면 소스를 뒤져보자. 소스에 해결책이 나와있기도 하다.

 

아래는 개인 웹서버에 나온 문제점에 대한 수정 방안이다.

[!!] Max running total of the number of events is < 1M, please consider having a value greater than 1M

mysql이 아닌 CentOS 설정값의 문제에 대한 지적이다. 처음엔 mysql의 문제점인 줄 알고 구글에 검색했더니 별다른 내용이 없었다. 혹시나 싶어 소스를 뒤져보니

...
if ( `sysctl -n fs.aio-max-nr` < 1000000 ) { 
        badprint 
"Max running total of the number of events is < 1M, please consider having a value greater than 1M"; 
        push @generalrec, "setup Max running number events greater than 1M"; 
        push @adjvars, 
          'fs.aio-max-nr > 1M (echo 1048576 > /proc/sys/fs/aio-max-nr)'; 
    }

이런 내용이 나왔다. 'echo 1048576 > /proc/sys/fs/aio-max-nr' 를 그대로 실행해주었다.

 

/var/log/mysql/error.err contains 1 warning(s).

mysql error 로그에 warning 메세지가 포함되어있다. 에러 로그를 열어 어느 부분에서 warning이 생겼는지 파악하고, 문제가 있어 보인다면 해당 문제를 해결하자.

 

[!!] User 'root@%' does not specify hostname restrictions.

root 계정에 호스트 제한이 없다. 필요에 의해 만들어 둔 계정이기 때문에 그대로 두기로 했다.

 

...

쭈욱 아래로 내려보면 Recommendations 와 Adjust variables 부분이 나온다.

-------- Recommendations -----------
General recommendations:
  Control warning line(s) into /var/log/mysql/error.err file
  Restrict Host for 'root'@% to root@SpecificDNSorIp
  UPDATE mysql.user SET host ='SpecificDNSorIp' WHERE user='root' AND host ='%'; FLUSH PRIVILEGES;
  MySQL was started within the last 24 hours - recommendations may be inaccurate Reduce or eliminate unclosed connections and network issues

이미 수정을 해버려서 Adjust variables가 없다. 어쨌든 메모리나 캐시에 관한 부분이 여기 등장하므로 해당 부분을 참조해서 튜닝을 하자.