MySQL 5.7 설정 튜닝

 

 

 

 

 

 

MySQL 5.7에서 가쟝 중요한 4가지 변수가 있다.

 

[mysqld]

innodb_buffer_pool_size = 10240M (램의 50~70% 정도로 설정)

innodb_log_file_size = 256M

innodb_flush_log_at_trx_commit = 1 # may change to 2 or 0

innodb_flush_method = O_DIRECT

 

 

---

// performance_schema 데이터베이스 사용
USE performance_schema

// 현재 쓰레드(연결) 개수 확인
SELECT * FROM performance_schema.threads

// 현재까지 누적된 쿼리 패턴 통계 확인
SELECT * FROM performance_schema.events_statements_summary_by_digest

// 현재까지 누적된 쿼리 패턴 통계 초기화
TRUNCATE performance_schema.events_statements_summary_by_digest

// 현재 실행 중인 쿼리 확인
SELECT * FROM performance_schema.events_statements_current

// 최근 실행된 쿼리 이력 기능 활성화
UPDATE performance_schema.setup_consumers SET ENABLED = 'yes' WHERE NAME = 'events_statements_history'
UPDATE performance_schema.setup_consumers SET ENABLED = 'yes' WHERE NAME = 'events_statements_history_long'

// 최근 실행된 쿼리 이력 확인
SELECT * FROM performance_schema.events_statements_history

// 최근 실행된 10,000개 쿼리 이력 확인
SELECT * FROM performance_schema.events_statements_history_long

 

 

 

제공되는 정보를 적절히 가공하여 아래와 같이 모든 실행 쿼리 패턴 단위의 시간 통계 및 풀 테이블 스캔 여부를 확인할 수도 있다. 소요 시간과 관계된 컬럼들은 SEC_TO_TIME({COLUMN}/1000000000000)를 사용하면 한 눈에 확인이 가능하게 출력해준다.

 

 

---

SELECT DIGEST_TEXT AS query,
IF(SUM_NO_GOOD_INDEX_USED > 0 OR SUM_NO_INDEX_USED > 0, '*', '') AS full_scan,
COUNT_STAR AS exec_count,
SUM_ERRORS AS err_count,
SUM_WARNINGS AS warn_count,
SEC_TO_TIME(SUM_TIMER_WAIT/1000000000000) AS exec_time_total,
SEC_TO_TIME(MAX_TIMER_WAIT/1000000000000) AS exec_time_max,
SEC_TO_TIME(AVG_TIMER_WAIT/1000000000000) AS exec_time_avg_ms,
SUM_ROWS_SENT AS rows_sent,
ROUND(SUM_ROWS_SENT / COUNT_STAR) AS rows_sent_avg,
SUM_ROWS_EXAMINED AS rows_scanned,
DIGEST AS digest
FROM performance_schema.events_statements_summary_by_digest

ORDER BY SUM_TIMER_WAIT DES