Louis.Kim 의 제 3막

오랜만에 DB Guide net 에 접속했다.

그 중 Index 사용 여부에 대한 가이드 글을 읽고 느낀점에 대해서 정리해 보았다.

먼저 읽은 글의 본문은 아래와 같다.

이병국의 개발자를 위한 DB 이야기: 튜닝(31회)

개발자를 위한 DB 튜닝 실전(1편)

http://www.dbguide.net/knowledge.db?cmd=view&boardUid=192095&boardConfigUid=19&boardStep=&categoryUid=196

고작 1편보고 왜 작성하느냐...가 아니라 그동안 잠시 잊고 있었던 부분을 다시 짚는 부분과 함께,

예시 쿼리들을 직접 손으로 작성하면서 이렇게 변경해도 되는 구나 ....하기 위해서 작성하는 부분이다.

* 인덱스를 사용하는 것은 언제나 옳다???

- 답은 No 이다. 누구나 알겠지만 Full scan이 성능상 더 효과적인 경우가 있기 때문이다.

아래의 쿼리에 대해서 보자

위의 쿼리를 인덱스를 사용하면 엄청난 시간이 소요될 수 있다. 하지만 /*+ FULL(영업활동) */  힌트를 사용하여 Full Scan을 하면 더 적게 소요될 수 있다.

이 말은 즉 데이터를 scan 시 Block 단위로 읽게(I/O) 된다. Index 를 이용하게 되면 Random access 를 하게 된다. 하지만 full scan을 하게 되면 Seqential read 하게 된다. 물론 다 그런거는 아니겠지만 대부분 Random access 하게 되면 Single block I/O 가 되며, Seqential read 하게 되면 Multi block I/O를 하게 되어 훨씬 I/O양을 줄일 수 있는 효과를 가져올 수 있다.

물론 이 내용들은 머릿속에서 엇박자로 알고 있는 부분들이라 다시 정리하게 되는 것 같다. (이해는 가지만 또 설명하게 되면 어버버 되는 상황??ㅠ)

자세한 내용은 아래에서 읽으면서 정리하는 것을 추천한다.

- I／O 효율화 원리

http://wiki.gurubee.net/pages/viewpage.action?pageId=29065463

해당 내용을 이해한다면 왜 Index를 사용안하고 Full table scan 이 더 효율적인지 이해할 수 있을 것이다.

두번째 내용은 Group by 사용과 성능 이슈 (이 부분은 기존 dbguide.net 내용을 펌~~~)

업무를 진행하다 보면 group by를 많이 사용하게 된다...내 의지와 상관없이...통계 때문일 것이다.

여기서는 데이터 구간이 총 데이터의 1/100 , 10만건 이하이기에 인덱스를 활용해야 한다고 한다.

또한, 부서명은 order by 절에 영향을 주지 않으며, 부서 테이블은 오로지 부서명을 조회하는 용도로만 사용한다.

부서 테이블은 부서명 조회 용도이며 부서테이블과 관련한 조건절이 없다.

위와 같이 변경하면 부서 테이블에 대한 접근 빈도수를 최대한 줄일 수 있음.

Group by 절의 수행 이전에 부서 테이블을 접근하였으나 

개선된 쿼리는 group by 절의 수행 이후에 부서 테이블을 접근하므로 접근 빈도수가 대폭 줄어든 효과

Order by 절의 선행컬럼에 부서명이 있거나, 조건절에 부서 테이블의 컬럼이 있다면 Outer Join 방식으로 변경 불가능

아래와 같이 인라인 뷰 방식의 쿼리로 개선 가능

부서 테이블에 대한 접근 빈도수를 최대한 줄일 수 있었으며, Group by 절의 수행 이후에 부서 테이블을 접근하는 부분이라 접근 빈도수도 줄어드는 효과

하지만, 최종 집계된 부서의 개수가 부서코드 테이블의 전체 개수의 1/100 이상이라면 아래의 쿼리처럼 힌트절 조정을 통하여 성능 개선을 추가적으로 가능

첫번째 쿼리와 같이 Full 로 변경함으로써 성능 효과 개선 가능

만약 조건절의 판매일자 조회 구간이 한 달 이상이라면 이번에도 인덱스를 사용치 않는 방법으로 아래와 같이 힌트를 이용해 변경 가능

조회 구간이 한달 이상이라면 대규모의 집계 처리를 의미하므로 두 테이블 모두 인덱스를 타는 것보다는 타지 않는 것이 더 선능에 좋을 것.

Group by 절의 어떠한 형식이나 어떠한 조건에 따라서 성능 개선의 방법은 다양하게 다를 수 있다. 

2번째 내용들은 이해를 하기보다는 정리 수준이라 쿼리 개발 때 참고해서 설계하는 것이 좋을 듯 싶다.

MySQL 관련 Log 종류에 대해서 다시 정리해 보았다.

Binary Log 관련하여 추가 정리 및 Parameter 정리는 더 추가 해야겠다.

아래 내용들은 역활에 대해서 많이들 알고 있으며 평소에도 자주 보는 것들이다.

이것보다 더 자세한 Parameter 를 검색해서 정리하여 추가해 봐야겠다.

1. Error Log

  - log_error 파라미터에 경로 정의

  - 경로를 정의하지 않으면 daradir 파라미터에 설정된 경로에 *.err 로 생김

  - MySQL 이 시작하는 과정과 관련된 정보성 및 에러 메시지 저장

  - 마지막으로 종료할 때 비정상적으로 종료된 경우 나타나는 InnoDB의 트랜잭션 복구 메시지

  - innodb_force_recovery 파라미터를 0보다 큰 값으로 설정하고 재시작 추천

  - 쿼리 처리 도중에 발생하는 문제에 대한 에러 메시지

  - 비정상적으로 종료된 컨넥션 메시지(Aborted connection)

  - max_connect_errors 시스템 변수 값이 터무니 없이 낮을 경우 발생할 수도 있음

  - 네트워크 문제일 경우도 있음

  - InnoDB의 모니터링 명령이나 상태 조회 명령("Show Engine Innodb Status") 의 결과 메시지

  - MySQL의 종료 메시지

2. General Log file

- MySQL 서버에서 실행되는 쿼리를 기록

- 쿼리가 실행되기 전에 MySQL 이 쿼리 요청을 받으면 바로 기록하기 때문에 쿼리 실행 중 에러가 발생해도 로그파일에 기록

- 5.1.12 이전에는 "general-log" 이며, 이상에서는 "general_log_file" 로 파라미터 정의

- 5.1 이상에서는 쿼리 로그 파일이 아닌 테이블에 저장하도록 설정이 가능 -> log_output 파라미터에 의해 결정

- general log 영구 적용 

$ vi /etc/my.cnf

general_log = 1

general_log_file = /var/log/mysql_general.log

$ service mysqld restart

- mysql> set global general_log = off;

3. Slow Query log

- log_query_time 파라미터를 이용하여 초 단위로 설정 하여 이상의 시간이 소요된 쿼리가 모두 기록

- 쿼리가 정상적으로 실행이 완료 되어야 Slow Query log에 기록될 수 있음

  (정상적으로 실행이 완료됐고 실행하는 데 걸린 시간)

- slow 관련 설정은 Dynamic 으로 set global 명령으로 재시작 없이 수정 가능

mysql> show variables like 'slow%';

mysql> show variables like 'long%';

mysql> show variables like 'log%';

mysql> set global show_query_log = ON;

mysql> set global show_query_log = OFF;

mysql> set global long_query_time = 10;

  ex) 5.1 미만

   long_query_time = 1

      log_slow_queries = /var/log/mysql-slow.log

  ex) 5.1 이상

   log-output = File 또는 Table

   slow-query-log = 1

   long_query_time = 10

   slow_query_log_file = /var/log/mysql-slow.log

log-bin=/home/mysql_log/bin_log/bin # 빈로그 저장 설정 및 저장할 디렉토리 지정

binlog_cache_size = 2M # binlog cache 사이즈 

max_binlog_size = 50M # bin로그 최대 파일 사이즈 

expire_logs_days = 10 # 보관기간

- 인덱스를 사용하지 않는 쿼리 추출용 옵션 변수 on/off

mysql> log_queries_not_using_indexes = off

#==========================================================================================================================================

# Time : 110202 12:13:14 => 쿼리 종료 시간

# User@Host: root[root] @ localhost [] => 쿼리 실행한 사용자

# Query_time : 15.407663  => 수행시간 Lock_time : 0.000198  => Update문을 실행하기 위해 테이블 락을 기다렸다는 의미  Row_sent: 0  =>쿼리 결과의 몇건을 클라이언트로 보냈는지 표시 Rows_examined : 5  => 쿼리가 처리되기 위해 몇건의 데이터를 검색 했는지 카운트

Update tab set fd=100 where fd=10;

#==========================================================================================================================================

- MyISAM 이나 Memory Storage Engine 에서는 테이블 단위의 잠금을 사용하기에 select 쿼리도 1초 이상 소요될 가능성 있음.

- InnoDB도 select 쿼리에 대해 lock_time 이 0이 아니는 경우가 생기지만 MySQL Engine 레벨에서 설정한 테이블 잠금 때문일 가능성이 있음

- 사용자가 " Lock Table 테이블 " 명령으로 획득한 잠금에 의해서 생긴 슬로우 쿼리는 로그에 기록되지 않음

4. Binary Log와 Relay Log 

- 바이너리 로그파일은 마스터 MySQL 서버에 생성되고 릴레이 로그는 슬레이브 MySQL 서버에 생성된다는 것 말고는 바이너리 로그와 릴레이 로그 파일의 내용이나 포맷은 동일

- 바이너리 로그파일에는 Select 등의 문장은 포함되지 않고  DML 쿼리가 기록

- mysqlbinlog 를 이용하여 바이너리 파일을 텍스트로 변형 가능

ex) mysqlbinlog binlog.000012 > binlog.sql

ex) Slave와 Master 동기화 방법

- mysql> stop slave stuats;

mysql> change master to master_log_file = 'binlog.000013', master_log_pos = '443232';

mysql> start slave status;

[추가내용]

Login 통계 관련하여 설계를 진행 했다.

그 동안 Login / out 정보를 테이블로 각기 다르게 추가를 하였다.

그러다 보니 Login/out 정보를 제대로 관리가 되지 않았다.

그래서 아래와 같이 설계 하였다.

간단하다.

- Login 할 때는 Insert / out 할때는 Update를 진행 한다.

Status

이렇게 구성한 후 일일 평균 / 특정 구간 동안의 평균을 낸 쿼리를 다음과 같이 구성하였다.

위와 같이 구성하면 다음과 같은 실행 계획이 나온다.

Possible_keys 가 null 인 것을 보면 Index 를 사용하지 않는 것을 확인 할 수 있다.

여기서 빨간색으로 하이라이트 한 것을 확인 가능하다.

Index를 사용하지 못하는 경우가 몇가지가 있다.(가장 기본으로 알고 있어야 하는 것 중 하나로 난 생각한다)

그 중 한가지가 조건절을 함수로 변경하는 경우 Index를 사용 못하게 된다.

다음과 같이 변경해 보자

함수를 빼버렸다.

사실 나도 왜 저렇게 DATE_FORMAT 사용 했는지 모르겠다....습관도 아닐텐데...특정 날짜의 구간을 구하다 보니 반자동으로 DATE_FORMAT 를 사용했는 듯 싶다.

다음과 같이 확인해 볼 수 있다.

하지만 rows 수가 늘어난 것을 확인 가능하다......이건 아니잖아....ㅠ

나의 튜닝 목표 중 하나가 row 수를 줄이는 것이다.(랜덤 I/O 도 줄이는 것이다)

옵티마이저가 선택하는 것이니 이유가 있을 것이다.......OTL