[mariadb] 실행된 SQL 쿼리 이력 확인

목차

1. 실행중인 쿼리 확인 (show full processlist)

2. 실행했던 모든 쿼리 확인 (임시)(설정 필요)

3. 실행된 쿼리 이력 확인 + 로그파일 (영구)(설정 필요)

4. 실행 속도 느린 쿼리 확인

5. 쿼리 확인

6. 참조

 

 

1. 실행중인 쿼리 확인 (show full processlist)

-- engine 상태

show engine innodb status;

 

-- 현재 접속중인 유저 및 실행중인 쿼리

-- 기본

show full processlist;

-- 상세

select * from INFORMATION_SCHEMA.PROCESSLIST;

-- 해당 Thread 현황

select * from information_schema.INNODB_TRXG;

-- 마지막에 해당 thread에서 실행중(된) 쿼리 조회

SELECT esc.THREAD_ID, t.processlist_id, esc.SQL_TEXT
  FROM performance_schema.events_statements_current esc

  /**********************************************************************************

  processlist 에서 보여주는 mysql 이 만든 thread와

  performance_schema 에서 만든 thread 값이 달라

  threads 테이블을 참조해야 작업을 확인할 수 있다.

  why? performance_schema.threads 는 background thread의 값도 가지고 있기 때문이다.

  **********************************************************************************/

 JOIN performance_schema.threads t ON t.thread_id = esc.thread_id
 WHERE t.processlist_id = 56604
 ;

 

2. 실행했던 모든 쿼리 확인 (임시)(설정 필요)

mariadb 재실행시 설정이 풀린다.

1)  root 권한으로 mysql로 접속

mysql -u root
mysql -u root -p // 비밀번호 치고 들어가야 할 때

 

2) general log에 관한 설정 조회

show variables where Variable_name in ('version', 'log', 'general_log');
show variables like 'general%';

 

MariaDB [(none)]> show variables like 'general%';

+------------------+-------------------------+

| Variable_name    | Value                   |

+------------------+-------------------------+

| general_log      | OFF                      |

| general_log_file | general.log |

+------------------+-------------------------+

 

3) 설정

set global general_log = 1;
-- or
set GLOBAL general_log='ON';

 

3. 실행된 쿼리 이력 확인 + 로그파일 (영구)(설정 필요)

mysql 설정 파일 설정

1) MySQL의 설정 파일을 연다. (운영체제 마다 위치 상이)

ubuntu ex) mysql

/etc/my.cnf

ubuntu ex) mariadb

/etc/mysql/my.cnf
/etc/mysql/mariadb.cnf

 

2) general_log 와 general_log_file 을 설정하고 저장합니다.

database를 재기동 하면 쿼리의 로그가 기록됩니다.

# version에 따라서 다를 수 있음

# version에 따라 file로 내보내기만 될 수 있음

 

# maria

[mariadb]

#쿼리 로그가 출력되도록 설정

general_log
# log_output= FILE | TABLE
# file로 내보내기
log_output=FILE

#로그파일 경로 설정

general_log_file=/var/log/mariadb/general/mariadb_general.log

 

# mysql

[mysqld]

#쿼리 로그가 출력되도록 설정

general_log=ON
# log_output= FILE | TABLE
# file로 내보내기
log_output=FILE

#로그파일 경로 설정

general_log_file=/var/log/mysql/general/mysql_general.log

3) 재기동 (운영체제 마다 위치 상이)

#database 재기동

/etc/init.d/mysqld restart
/etc/init.d/mysql restart
service mysql restart

 

#권한없음 나오면 sudo

4) (file인 경우) mysql log 권한 설정 (log를 root에서 조회 하는걸 권장하지 않는다.)

# 폴더를 만들어 준 후 mysql에게만 권한을 주도록 하자.

chown -R mysql:mysql /var/log/mysql

 

# 위와 같이 권한을 설정하였을 경우 또는 etc..로 아래와 같은 문제가 생길 수 있다.

-bash: cd: mysql: Permission denied

sudo: cd: command not found

 

# mysql log 조회시 계정(권한을 준 계정)을 달리 들어가서 접속하면 보인다.

su - [계정명]
su - mysql

 

# Authentication failure Error

# root에 비밀번호가 설정이 안되었을 경우

sudo passwd root

 

4. 실행 속도 느린 쿼리 확인

MySQL의 설정 파일을 편집 명령어로 열고 slow_query_log 와 slow_query_log_file 을 설정하고 저장합니다.

MySQL을 재기동 하면 설정한 내용이 반영됩니다.

# 0:끄기,1:켜기
vim /etc/my.cnf slow_query_log=1

 

#로그파일 경로 설정

slow_query_log_file=/home/mysql/mysqld-slow.log

#5초이상 걸리는 쿼리

long_query_time=5

 

#MySQL 재기동

/etc/init.d/mysqld restart

이 설정으로 쿼리의 실행시간을 확인 할 수 있습니다.

long_query_time의 기본값은 10초입니다.

 

5. 쿼리 확인

파일 경로를 지정해놨으면 file 경로에 가서 보기!

table에 저장하면 해당 테이블(general_log, slow_log....)에서 볼 수 있다.

SELECT * FROM mysql.general_log;
SELECT * FROM mysql.slow_log;

 

6. 참조

-- 둘다 같음 서버 상태

SELECT * FROM information_schema.GLOBAL_STATUS;

show status;

-- maria db에 접속되어 있는 client 정보 리스트

SHOW PROCESSLIST;

 

-- Aborted_connects : MySQL 서버에 접속이 실패된 수

-- Max_used_connections : 최대로 동시에 접속한 수

-- Threads_connected : 현재 연결된 Thread 수

-- Connections : mysql서버에 연결시도한 수

show status like '%connect%';

-- Aborted_clients : 클라이언트 프로그램이 비 정상적으로 종료된 수

show status like '%clients%';

-- Threads_cached : Threads Cache의 Thread 수

-- Threads_connected : 현재 연결된 Thread 수

-- Threads_created : 접속을 위해 생성된 Thread 수

-- Threads_running : sleeping 되어 있지 않은 Thread 수

show status like '%thread%';

  

/*

커넥션을 모니터링 할 때 위에서 확인한 상태 값을 계산하여 튜닝 여부를 판단할 수 있다.

Cache Miss Rate(%) =  Threads_created / Connections * 100

Connection Miss Rate(%) = Aborted_connects / Connections * 100

Connection Usage(%) = Threads_connected / max_connections * 100

 

Connection Usage(%)가 100%라면 max_connections 수를 증가시켜야 한다.

Connection 수가 부족할 경우 Too Many Connection Error가 발생한다.

 

DB서버의 접속이 많은 경우 wait_timeout을 최대한 적게 설정하여 불필요한 연결을 빨리 정리하는 것이 좋다.

그러나 Connection Miss Rate(%)가 1% 이상이면 wait_timeout을 좀 더 길게 설정한다.

 

MySQL 서버는 외부로부터 접속 요청을 받을 경우 인증을 위해

IP 주소를 호스트네임으로 바꾸는 과정을 수행하여 접속 시에 불필요한 부하가 발생한다.

skip-name-resolve를 설정하고 접속시 IP기반으로 접속을 하게 되면

hostname lookup 과정을 생략하게 되어 좀 더 빠르게 접속할 수 있다.

*/

show variables like '%timeout';

 

출처: [mariadb] 실행된 SQL 쿼리 이력 확인 (ubuntu).html [365일 24시간 초보]