[펌] MySQL : 쿼리 성능 측정을 방해하는 요소를 제거하기

 

---

 출처

 : http://blog.naver.com/ez_/140113578383

---

 

MySQL 튜닝 시, 높은 응답성을 유지하기 위해 쿼리 캐시 크기의 조정,  최대로 열 수 있는 테이블 수의 조정, 인덱스를 효율적으로 쓰기 위한 key_buffer_size 값의 조정, 조인을 위한 버퍼의 크기 조정 등을 하곤 한다.

허나, 단일 쿼리의 성능을 측정하려고 할 때는 위의 기능(?) 들로 인해 정확한 성능을 측정하기가 어려울 때가 있는데, 이를 위해 다음과 같은 방법을 사용해봤다.

(1) SQL_NO_CACHE

SELECT 구문에 위의 키워드를 넣으면, 캐시의 내용에 영향을 받지 않는다. 허나, 뭔가 부족한 느낌...

(2) FLUSH QUERY CACHE

메모리 사용 효율을 높이기 위해, 쿼리 캐시 메모리를 defragment 한다.

실제로 쿼리 캐시를 없애진 않으므로 유의.

(3) RESET QUERY CACHE

쿼리 캐시 결과를 완전히 리셋한다.

(4) FLUSH TABLES

열려 있는 테이블을 모두 닫는다. 또한, RESET QUERY CACHE도 수행한다.

허나, 위의 방법들을 써도, MyISAM이 사용하는 인덱스를 위한 Key buffer는 리셋할 수가 없다.

이를 위해 아래의 꽁수를 사용한다.

SET GLOBAL key_buffer_set=8; /* 8 미만의 크기로 설정할 수는 없으니 최소값으로 설정 */

SET GLOBAL key_buffer_set=8*1024*1024; /* 원래 크기로 복원 */

그러나... 이 모든 것을 수행해도 결과가 다름을 확인했다.

이는 하드디스크 또는 다른 물리 장치들이 가지고 있는 펌웨어단에서의 버퍼 문제와, OS의 커널 레벨에서 다루는 디스크 캐시 때문인 것으로 생각된다. 이는 MySQL 서버를 재시작해도 피할 수 없다.

이러한 영향에서 벗어나기 위해서는, 아래의 방법을 사용한다.

 

1. MySQL 서버 종료
2. sync
3. purge
4. MySQL 서버 시작

 

sync는 아직 쓰지 않은 버퍼를 디스크에 쓰도록 만들며, purge는 커널에서 보관중인 디스크 캐시를 없앤다. ( 맥/리눅스에서 동작되며, 윈도우에서는 별도의 방법을 사용해야 한다. 다음 링크 참조 : http://technet.microsoft.com/en-us/sysinternals/bb897438.aspx )

이와 같은 방법은 MySQL뿐만 아니라 디스크 액세스와 관련된 모든 벤치마크에서 유용하리라 생각된다.

 

 

아래의 결과를 보면, 서버 재시작과 sync/purge 조합만이 유일한 해결책 임을 알 수 있다.

실행 환경 : Intel core 2 duo 2.4Ghz, 5400rpm HDD / Mac OS X 10.6.4 / MySQL 5.1.49

대상 데이터 : 2백만건, 약 8GB의 MyISAM 테이블

/* 최초 실행 */

mysql> select count(*) from board_myisam where d >= '2010-03-01' and d < '2010-05-01';

 

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

| count(*) |

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

|   333560 |

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

1 row in set (59.47 sec)

/* 쿼리 캐시의 영향 */

mysql> select count(*) from board_myisam where d >= '2010-03-01' and d < '2010-05-01';

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

| count(*) |

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

|   333560 |

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

1 row in set (0.22 sec)

/* 테이블을 닫고, 쿼리 캐시를 삭제 */

mysql> flush tables;

Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from board_myisam where d >= '2010-03-01' and d < '2010-05-01';

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

| count(*) |

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

|   333560 |

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

1 row in set (0.25 sec)

 

/* 테이블을 닫고, 쿼리 캐시를 삭제하며, 키 버퍼를 비움 */

 

mysql> flush tables;

Query OK, 0 rows affected (0.00 sec)

mysql> set global key_buffer_size=8;

Query OK, 0 rows affected (0.01 sec)

mysql> set global key_buffer_size=8*1024*1024;

Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from board_myisam where d >= '2010-03-01' and d < '2010-05-01';

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

| count(*) |

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

|   333560 |

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

1 row in set (0.26 sec)

 

/* 서버 재시작 */

 

 

 

mysql> select count(*) from board_myisam where d >= '2010-03-01' and d <= '2010-04-31';

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    1

Current database: test

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

| count(*) |

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

|   333560 |

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

1 row in set (0.30 sec)

 

 

/* 서버 종료, sync, purge이후 서버 재시작 */

 

mysql> select count(*) from board_myisam where d >= '2010-03-01' and d < '2010-05-01';

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    1

Current database: test

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

| count(*) |

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

|   333560 |

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

1 row in set (57.13 sec)

mysql> 

[출처] MySQL : 쿼리 성능 측정을 방해하는 요소를 제거하기|작성자 아미