MySQL 성능을 죽이는 15가지 방법이라는 아주 재미있는 내용의 프리젠테이션...

MySQL 을 기반으로 한 다양한 웹 서비스들을 만들어가고 또 운영되는데 있어서
항상 MySQL 이라서 느려...MySQL 이니까 이 정도도 감지덕지... 라고 하지 말고
제대로 알고 제대로 쓸 수 있도록 해보자.
#2. Not using EXPLAIN
EXPLAIN 은 입력된 SQL 문장을 해석하여 옵티마이져의 실행 계획을 보여주는 명령어 이다.
입력된 SQL 을 해석한 뒤 테이블의 데이터/인덱스 등을 판단하여 어떻게 실행 할지 보여주게 되는데.

Extra 컬럼에 "Using Index" 라고 나온다면 제대로 돌아 가고 있다 라고 봐도 거~~의~~ 무방하다.

특히 MySQL 5.0 이전 버전에서는 단 하나의 Index 만을 이용 할 수 있었는데.
MySQL 5.0+ 버전에서는 Index Merge optimization 을 통해서 좀더 향상된 속도를 얻을 수 있다.
이는 곧 인덱스 수립 계획에도 큰 도움이 된다. ( 슬라이드 Page 13 참조 )


#3. Choosing the wrong date types
Index ( 혹은 데이터) 는 가급적이면 단일블럭(Single Block) 안에 들어갈 수 있게 하는 것이 좋다.
기본적으로 DBMS 는 데이터가 늘어가면서 I/O 가 많이 일어나고 그리고 성능에 매우 민감하게 영향을 미치게 된다.
특히 디스크 처럼 매우 느린장치(CPU 연산 과정에서 보자면  HDD 는 아직 한참 멀었다.)에 접근 횟수를 줄일 수 있다면 당연히 느려진다는 것이다.

반대로 말하자면 데이터를 가져올때 횟수를 최소화 할 수 있다면 곧 속도향상을 얻을 수 있다라는 이야기.

그렇기 때문에 Index (혹은 데이터) 를 저장함에 있어서 가급적이면 최소한의 컬럼을 유지하는 것이 I/O 횟수를 줄이는데 유리하다.
슬라이드에서는 IP 정보를 저장할때 Varchar 형을 사용하거나 UNSIGNED INT 형을 사용 하는 2가지 경우를 가지고 이야기를 한다.

일반적으로 Varchar 형을 이용하여 저장하는 경우가 많은데. INET_ATON()/INET_NTOA() 함수를 이용하여 변환하여 INT 형에 저장 할 수 있게 되는데.
저장하는 데이터 크기도 줄이고 또한 Index range scan 을 통한 성능 향상도 꾀 할 수 있다는 장점이 있다.

슬라이드에서는 설명하고 있지 않지만 굳이 음수형으로 데이터를 저장할 필요가 없다면 UNSIGNED INT 와 같이 선언을 하게 되면 SIGNED INT로 선언했을 때 보다 약 2배 가량 속도 향상을 얻을 수도 있다고 한다.
그러니 *반드시* 필요한 데이터형의 크기를 잘 정해서 설계하는 것이 중요하다.

#4. Using persistent connection in PHP
MySQL 연결 시간(Connection Time) 을 줄이기 위해 영구적인 연결(Persistent Connection) 을 이용하는 경우가 있다.

하지만 영구적인 연결(Persistent Connection) 인 경우 자원을 공유하지 않은 구조로 인하여
Apache 프로세서가 Zombie 상태에 빠지게 되면 그대로 자원을 낭비하게 된다.

애초에 MySQL 은 수명이 짧고 경량형 으로 디자인 되어있어서 Connection Time 은 Oracle 이나 PostgreSQL 에 비해서 10~100배 가량 빠르다고 하니 pconnon_* 계열 에 목 매지 맙시다.

#5. Using a heavy DB abstraction layer
이식성(Portability) 때문에  ADODB , MDB2  혹은 PearDB 같은 무거운(Heavy) 추상화 라이브러리(abstraction Library) 를 쓰는데
그것 보단 PDO 같은 경량형 라이브러리를 이용하는 것이 좋다.  거기에 Scale-out 을 위한 부분도 추가해주 면 좋다.

#6. Not understanding storage engines
MySQL 에서는 매우 다양한 DB Engine 을 가지고 있는데 각 Engine 의 특성에 따라서
장 단점이 있는데 이에 대한 이해를 통해 적절한 Storage Engine 을 활용하면 보다 나은 성능을 발 휘 할 수 있다.

반대로 그러한 이해가 없이 사용하면 오히려 성능저하의 원인이 되기도 한다.

예를 들어 웹 로그 기록 같이 한번 입력하면 변경은 이루어지지 않고 고속의 INSERT 성능이 필요한 경우엔 Archive Engine 을 쓰는 것이 낫다 더욱이 압축 효율 또한 우수하여 ( MyISAM 대비 6~8배 ) 디스크 관리에도 유리하다.

반대로 주간 베스트 와 같이 크지는 않지만 빠른 입출력이 필요한 경우 Memory Engine 을 사용하면 Disk based Engine 에 비하여
획기적으로 빠른 성능을 제공 할 수 있다.

이처럼 적절한 Engine 을 활용 하는 것이 보다 나은 성능을 보장해준다.
다시 한번 말하지만 InnoDB 는 어디에도 붙일 수 있지만 성능을 보장해주는 것은 아니다.


#7. Not understanding index layouts
INDEX 선정과 Storage Engine 을 선탁하는데 매우 중요한 부분이다.
모든 Engine 들 은 Data 와 Index 를 메모리(Memory) 와 디스크 ( Disk ) 에 저장 한다.

Clustered 방식은 Primary Key 를 기준으로 정렬된 Data 묶음 단위로 디스크에 저장을 하고
Non-Clustered 는 그렇지 않다.

Non-Clustered 의 경우에는 큰 영향은 없지만.
Clustered 의 경우 데이터를 검색 할 경우 Primary key 를 보고 실제 데이터로 이동하여 데이터를 검색 하기 때문에
데이터 조회시 가급적 PK 를 이용하여 데이터를 검색하는 것이 좋다.

그리고 Clustered  방식에서 생성되는 모든 Second Index 는 PK 를 참조하여 만드므로 PK 는 최대한 작게 만들어주는 것이 좋다.

#8. Not understanding how the query cache works
사용하는 Application 의 Read/Write 비율을 이해한 상황에서
CPU 사용율과 읽기 성능을 적절하게 조율해야 한다.

Query Cache 를 늘린다고 성능이 무작정 증가하지도 않는다.

Cache 검증하는데 매우 투박한 검증방식 을 채용했는데
이유는 Cache 된 데이터를 찾아서 저장하는데 너무 많은 CPU 자원을 사용을 방지 하기 위해서 이다.

이는 SELECT 할 때 Table 에 어떠한 변화가 생긴 경우 모든 Cache 정보들은 유효하지 않게 된다는 것이다.
결국 Table 에 매번 변화가 생길 경우 매번 Cache 갱신을 하게 되고
Query Cache 의 효율이 떨어지게 되는 것이다.

그래서 자주 업데이트 되는 컬럼들을 분리 할 필요가 있다.

#9. Using stored procedures improperly
모든 RDBMS 는 Connection thread 별로 컴파일(Compiled)된 Stored Procedure 의 실행 계획을 유지하게 되는데

이것은 PHP 로 만든 페이지에서 요청을 보내서 SP 를 이용하여 데이터를 가져온다면
매번 컴파일을 거친다는 것이고 이것은 매우 큰 낭비라는 것이다(7~8% 정도)

특별히 매우 복잡하고 요쳥 빈도수가 많지 않은 작업이거나...
한번에 매우 많은 횟수의 Query 를 요청하는 경우가 아니라면....말이다.

그냥 Prepared Statements 를 써라...

#10. Operating on an indexed column with a function
Index Column 을 펑션으로 변환해서 사용하게 되면 Index 를 사용할 수 없게 된다.
그러니 있는 그대로 Index Column 을 사용 할 수 있도록 하는 것이 중요하다.

#11. Having missing or useless indexes
Index 는 SELECT 할때 검색 속도 향상에 매우 도움이 크다.

하지만 사용하지 않는(혹은 빈도 수가 매우 적은) Index 는 INSERT/UPDATE/DELETE 할때 매우 부담이 된다.
필요 없는 Index 는 반드시 삭제해주는 것이 좋다.

혹시 대량의 데이터를 입력이 필요 할 경우 경우에 따라서는 INDEX 를 삭제한 뒤
입력을 완료하고 인덱스를 생성하는 것이 나을 것이다.

#12. Not being a join-fu master
최대한 단순하게
복잡한 SQL 문을 쪼개서 데이터 묶음(Sets)으로 생각하라.

반복된 작업(Subquery,loop) 가 아닌 데이터 묶음(Join)으로 생각 해야 한다.
데이터의 묶음으로 처리하기 때문에 보다 나은 성능을 얻을 수 있다.

( 46~48p 를 반드시 참조하라!)

#13. Not accounting for deep scans
정렬된 데이터(ordered set)에서 매우 깊숙한(Deep scan) 곳에 데이터의 일부를 가져오는 것은 비용이 매우 많이 든다.

보통 게시판의 Paging 을 하면서 LIMIT 를 이용하게 되는데

페이지 넘버가 커지면 커질 수록 느려지는 경험을 해봤을 것이다.

이유는  LIMIT 를 이용하여 목록을 가져 오는 경우
1. 데이터를 정렬하고
2. 검색에 필요한 위치(offset) 을 지정해서 목록을 가져 온다.

여기서 데이터를 가져오는 위치(offset) 이 크면 클 수록 그 만큼 scan 을 하는 비용이

Full scan 비용에 근접하게 된다.
이를 피하기 위해선 해당 정렬된 데이터(Ordered set)의 크기를 줄여서 가져오는 것이 보다 나은 성능을 보장 할 수 있다.

#14. Doing SELECT COUNT(*) without WHERE on an InnoDB table
InnoDB 는 SELECT count(*) 로 Table 전체 Row 개 수를 가져오는 작업은 매우 퍼포먼스가 느린 작업이다.


InnoDB 내부적으로 매우 복잡(?) 한 구조로 인하여 생긴 문제인데 ( MyISAM 에서는....괜찮았다...)

필요하다면 별도의 테이블을 생성하여 Table 마다 Trigger 를 작성하여 Table Row count 를 관리하도록 하는 것도 방법이 되겠다.

#15. Not profiling or benchmarking
시스템 성능의 Bottleneck 을 항상 관찰하고(Profiling)
어플리케이션의 성능이 어느정도 까지 버틸 수 있는지 얼만큼의 부하를 견딜 수 있는지 테스트(Benchmark) 해볼 필요가 있다.

(54~56p 참조)

#16. Not using AUTO_INCREMENT
MySQL 에서 AUTO_INCREMENT 는 매우 PK 에 최적화 되어있다.

복합적으로 진행 되는 매우 많은 INSERT 에도 고성능으로 처리가 가능하다.
(Lockless reading and appending)

#17. Not using ON DUPLICATE KEY UPDATE
Application 에서 해당 Row 가 존재하는지 보고 Update or INSERT 를 결정하는 경우가 있는데
DB Connection 을 오가면서 소모되는 비용을 줄 일 수 있다. ( 약 5~6% 정도 )
물론 많은 양의 데이터가 와도 가능하다.

http://joinfu.com/presentations/kill-mysql-performance/kill-mysql-performance.pdf
http://ppassa.wordpress.com/2011/05/15/mysql-optimization-with-indexing/