MySQL 튜닝

MySQL / 튜닝

쿼리 분석 †

MySQL의 EXPLAIN을 철저 해설!

show profile에서 단계별 실행 시간을 본다.

서버 분석 

MySQL 튜닝?

↑

설정 값을 확인 

mysql> show variables;

통계를 가지고 

정량적 인 정보 수집의 추천

mysql> show global status; (5.0.2 이상의 경우)

mysql> show status; (5.0.2 미만의 경우)

테이블의 레코드 수, 데이터 크기, 인덱스 크기 등을 조사

select table_name, engine,

table_rows as tbl_rows,

avg_row_length as rlen,

floor ((data_length + index_length) / 1024 / 1024) as allMB,

floor ((data_length) / 1024 / 1024) as dMB,

floor ((index_length) / 1024 / 1024) as iMB

from information_schema.tables

where table_schema = database ()

order by (data_length + index_length) desc;

INFORMATION_SCHEMA 데이터베이스

슬로우 쿼리 로그를 확인 †

연결 상태 확인 †

$ mysqladmin -p extended-status | grep -E 'Max | Threads'

Enter password :

| Max_used_connections | 51 |

| Threads_cached | 4 |

| Threads_connected | 21 |

| Threads_created | 18015 |

| Threads_running | 11 |

↑

데이터 크기와 탑재 메모리 최적화 †

"데이터 크기 <메모리 용량"을 유의

디스크와 메모리의 읽기 및 쓰기 속도는 100 만배의 차이가있다

B 트리 인덱스의 검색 테이블 스캔시에 대량의 탐색이 발생. 이 디스크에서 할까 메모리에서 할 것인가에 상당한 차이가 난다

MySQL은 OS의 파일 캐시를 의지하는 전제로 만들어져있다

OS의 파일 캐시, MySQL 전용 버퍼에서 모두 좋은

데이터의 전체 용량이 메모리에 맞지 않는 경우는 인덱스만으로도 메모리에 맞게하기

↑

MySQL의 인덱스의 제한에주의

WHERE 절과 ORDER BY 절에 사용할 수있는 인덱스는 1 개까지

자세한 내용은 SELECT 문 및 기타 쿼리 최적화를 참조

조건에 잘 지정 항목에 집계가 필요한 것 (건수)은 테이블에 컬럼을 추가하여 그 컬럼을 보게하는

앱 개수 테이블 업데이트시에 항상 추가 한 컬럼의 값을 업데이트하도록

튜닝 

사용 메모리 조정 

MySQL 서버가 사용하는 메모리

파일 오픈 개수 조정 

고부하 때 다음 명령을 실행

$ mysqladmin -u root -p extended-status | grep Open

Enter password :

| Open_files | 515 |

| Open_streams | 0 |

| Open_tables | 256 |

| Opened_tables | 45281 |

Open_files, Open_tables의 수가 많은 경우 /etc/my.cnf에 다음을 조정. open_files 값에 비해 table_cache 값이 너무 큽니다 "Error in accept : Too many open files"에러가 발생 MySQL이 기능 부전에 빠질 있으므로 요주의!

[mysqld]

open_files = 2048

table_cache = 512

위의 수를 늘릴 때 "사용자 별 파일 디스크립터 수 '에도주의. 필요하다면 늘린다.

파일 디스크립터의 최대 수를 증가

【참고】

max_connections와 thread_cache 튜닝을 함

I / O 분산 †

데이터 디렉토리의 분산

디렉토리에 다른 파티션을 마운트

디렉토리를 심볼릭 링크로 데이터를 다른 파티션에 두는

MyISAM 파일은 데이터 파일 (MYD)과 인덱스 파일 (MYI)을 두는 디렉토리를 지정

InnoDB 전문 †

전체 테이블을 InnoDB 화

InnoDB와 MyISAM 어느 쪽이 빠른가?

InnoDB vs MyISAM vs Falcon benchmarks

철저 비교! MySQL 엔진

MyISAM에서 InnoDB로 전환 할 때의 주의점

MySQL 레퍼런스 매뉴얼 13.5.3.1. Per-Table 테이블 스페이스를 사용하는

innodb_file_per_table 옵션을 사용하여 1 InnoDB 테이블 = 1 파일에 마운트 및 심볼릭 링크로 분산시킨다.

InnoDB는 공유 테이블 스페이스 (ibdata1 etc)에 내부 데이터 디렉토리와 운도 로그두기 때문에, 항상 그것을 필요로한다. .ibd 파일만으로는 InnoDB는 작동하지 않습니다.

innodb_file_per_table 그런데 ibdata1이 이용된다

MySQL 레퍼런스 매뉴얼 7.5.13.3. InnoDB 테이블의 조각 모음

확장 된 계속 InnoDB의 데이터 파일의 크기를 줄이는 방법

↑

my.cnf †

↑

MyISAM / InnoDB 공통 †

table_open_cache

MySQL에서 테이블 열고 닫는 방법

연결 수가 200의 경우 최소 200 × N 테이블 캐시 크기가 필요

max_connections

서버가 허용하는 최대 동시 연결 수

query_cache_size (Global)

쿼리 결과 캐시. 같은 Query가 캐시에있는 경우 SQL을 실행하지 않고 캐시의 내용을 반환하기 때문에 크기가 큰 것이 좋다.

해당 테이블의 내용이 변경된 경우에는 캐시가 없기 때문에 캐싱으로 인한 문제가 발생할 수는 없다

sort_buffer_size (Thread)

정렬을 수행해야하는 스레드가이 크기의 버퍼를 할당

↑

MyISAM 관련 †

key_buffer_size (Global)

MyISAM 키 캐시 버퍼의 크기

MyISAM 만의 MySQL 전용 서버라면 RAM의 1/4 정도 할당하면된다

myisam_sort_buffer_size (Thread)

MyISAM 용

인덱싱시 (REPAIR TABLE, CREATE INDEX, ALTER TABLE)에 사용하는 버퍼

참고 : myisam_sort_buffer_size을 늘려두면 좋겠다

↑

InnoDB 관련 †

innodb_file_per_table

테이블 단위로 .ibd 파일을 작성

반드시! ! 지정

innodb_buffer_pool_size (Global)

InnoDB 테이블의 데이터와 인덱스를 저장하기위한 버퍼의 크기

사실 여기서 지정된 값의 5 ~ 10 % 정도 넉넉하게 메모리를 사용하므로주의가 필요

MySQL하고 InnoDB 전용 서버라면 RAM의 70-80 % 정도까지 올려도 좋다 든가 말해지고 있지만, 실제로한다고 떨어질 수 있으므로 50 %에 고정 해 놓은 것이 무난

innodb_log_file_size

데이터 쓰기 유익하게 큰 크기의 데이터 쓰기 성능에 영향을

큰 크기만큼 데이터의 복구 시간 필요

크기를 변경하는 경우 성공적 정지 후 생성 된 로그 파일 (기본값은 / var / lib / mysql / ib_logfile **)를 제거하고 mysql 서버를 시작하고 다시 필요

innodb_flush_method

MySQL innodb_flush_method = O_DIRECT 검토

기본 (fdatasync)에서 좋기 때문에 미지정으로 OK

↑

데이터 파일의 조정 †

확장 된 계속 InnoDB의 데이터 파일의 크기를 줄이는 방법

[MySQL 시계】 제 32 회 MySQL Enterprise 최초의 버전 업 및 데이터베이스 파일의 크기 조정

↑

증상과 대책 †

Copying to tmp table이 빈발

테이블이 MyISAM의 경우

myisam_sort_buffer_size (Thread) 확대

key_buffer_size (Global) 확대 (1 / 6 ~ 1 / 4)

테이블이 InnoDB의 경우

sort_buffer_size (Thread) 확대

↑

↑