Skip to content
컴퓨터잡담
2010.04.13 17:12

MYSQL Qcache 값 조정~~~

조회 수 19530 추천 수 0 댓글 1
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄


MYSQL Qcache 값 조정~~~~



 질의 캐시 통계 출력

                
mysql> SHOW STATUS LIKE 'qcache%';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| Qcache_free_blocks      | 5216       |
| Qcache_free_memory      | 14640664   |
| Qcache_hits             | 2581646882 |
| Qcache_inserts          | 360210964  |
| Qcache_lowmem_prunes    | 281680433  |
| Qcache_not_cached       | 79740667   |
| Qcache_queries_in_cache | 16927      |
| Qcache_total_blocks     | 47042      |
+-------------------------+------------+
8 rows in set (0.00 sec)

각 항목을 분리하면 표 1과 같다.


표 1. MySQL 질의 캐시 변수
변수 이름설명
Qcache_free_blocks캐시에 있는 연속적인 메모리 블록 숫자. 높은 숫자는 단편화가 일어난 징표다. FLUSH QUERY CACHE는 캐시 조각을 모아 자유 블록 하나로 만든다.
Qcache_free_memory캐시에 있는 자유 메모리
Qcache_hits캐시에서 질의를 가져올 때마다 값이 증가한다.
Qcache_inserts질의가 들어올 때마다 증가한다. inserts를 hits로 나누면 비적중률을, 1에서 비적중률을 빼면 적중률을 구할 수 있다. 직전 에제에서 대략 질의 중 87%를 캐시에서 가져왔다.
Qcache_lowmem_prunes캐시를 위한 메모리가 부족해져 더 많은 질의를 위한 공간을 확보하기 위해 정리되어야 하는 횟수. 이 숫자를 계속해서 살펴보는데, 증가 추세에 있다면 단편화가 심각하거나 메모리가 부족하다는 징표다(위에서 언급한 free_blocks와 free_memory를 살펴본다).
Qcache_not_cached일반적으로 SELECT 구문이 아니기 때문에 캐시 후보에서 제외된 질의 숫자
Qcache_queries_in_cache현재 캐시되어 있는 질의 숫자(응답 숫자 포함)
Qcache_total_blocks캐시에 있는 블록 숫자

종종 이런 값의 변화 추이를 살펴보면 캐시를 효율적으로 사용하는지 파악하는 데 도움을 준다. FLUSH STATUS는 몇몇 카운터를 초기화하므로 서버가 동작 중에 있을 경우 도움이 된다.

모든 내용을 캐시하도록 과도하게 큰 캐시를 잡고 싶은 유혹이 든다. mysqld는 메모리 부족으로 인한 정리 작업과 같은 캐시 관리 작업도 해야 하므로, 여기에만 신경을 쓸 경우 서버가 꼼짝달싹하지 못한다. 일반적인 규칙을 설명하자면 FLUSH QUERY CACHE가 오래 걸린다면 캐시가 너무 큰 상황이다.




시스템 부하가 자원 부족으로 이어지지 않도록 mysqld에 몇 가지 제약을 가해야 한다. Listing 3은 my.cnf에서 몇 가지 중요한 자원 관련 설정을 보여준다.


Listing 3. MySQL 자원 설정
                
set-variable=max_connections=500
set-variable=wait_timeout=10
max_connect_errors = 100

최대 접속은 첫째 행에서 다룬다. 아파치가 사용하는 MaxClients와 같이, 서비스가 가능한 접속 수만 허용한다. 지금까지 서버가 처리한 최대 접속 수를 확인하려면 SHOW STATUS LIKE 'max_used_connections'명령을 내린다.

둘째 행은 mysqld가 10초 이상 쉬고 있는 접속을 끊어버리도록 만든다. LAMP 응용 프로그램에서 데이터베이스 접속은 일반적으로 웹 서버가 요청을 처리하는 동안에만 이뤄진다. 종종 부하가 걸린 상태에서 연결이 일시 정지된 상황에서 접속 테이블 공간을 차지하는 경우가 있다. 활성 사용자가 많거나 데이터베이스에 영속적인 접속이 이뤄지고 있다면, 이 값을 낮춰잡는 정책은 바람직하지 않다.

마지막 행은 안전 벨트다. 호스트에 서버 접속 관련 문제가 생겨 너무 많이 요청을 취소한다면, FLUSH HOSTS를 수행할 때까지 호스트는 잠겨버린다. 기본적으로 열 번 정도 실패하면 잠겨버리도록 설명한다. 이 값을 100으로 바꾸면 문제가 무엇이든 복구할 시간을 서버에 충분히 준다. 더 높은 값으로 설정하더라도 그다지 도움을 주지 않는 이유는 서버가 한 번에 100번 연결해도 실패한다면, 이후 계속 시도하더라도 연결에 성공할 가능성이 희박하기 때문이다.



버퍼와 캐시

MySQL은 100개가 넘는 조율 설정값을 지원한다. 하지만 천만다행으로 이 중에서 몇 가지만 알면 충분하다. 설정을 올바르게 하려면, SHOW STATUS 명령을 통해 상태 변수를 살펴보고, 이를 통해 mysqld가 원하는 방식으로 움직이는지 파악한다. 시스템에 존재하는 메모리 자원을 넘어서 버퍼와 캐시를 할당할 수 없기에 종종 조율 과정에서 타협이 필요하다.

MySQL 조율값은 mysqld 프로세스 전체나 개별 클라이언트 세션에 대해 설정이 가능하다.

서버 단위 설정

각 테이블은 디스크에 파일 형태로 저장되며, 테이블을 읽기 위해서는 파일을 열어야 한다. 파일 읽기 과정을 빠르게 하기 위해, mysqld는 /etc/mysqld.conf에 지정된 숫자(table_cache)만큼 열린 파일을 캐시한다. Listing 4는 열린 테이블에 대한 활동 상황을 출력하는 방법을 보여준다.


Listing 4. 열린 테이블 활동 상황 출력
                
mysql> SHOW STATUS LIKE 'open%tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 5000  |
| Opened_tables | 195   |
+---------------+-------+
2 rows in set (0.00 sec)

Listing 4는 현재 테이블 5000개가 열려있으며, 테이블 195개가 열려야 했음을 보여준다. 캐시에 유효한 파일 기술자가 없기 때문에 이런 현상이 일어난다(직전에 통계를 초기화했으므로 5000개 열린 테이블 중에 단지 195개만 열렸다고 기록이 남는다). SHOW STATUS 명령을 다시 실행할 때 Opened_tables가 급격하게 올라가면, 캐시 적중률이 떨어진 상황이다. table_cache 설정값보다 Open_tables 설정값이 훨씬 낮으면, 캐시를 너무 크게 잡았다(물론 여유있게 설정하는 방식은 나쁘지 않다). 예를 들어, table_cache = 5000으로 테이블 캐시 값을 조정한다.

테이블 캐시와 마찬가지로 스레드를 위한 캐시도 있다. mysqld는 접속을 받을 때 필요한 스레드를 만든다. 바쁜 서버에서 접속이 빠르게 연결되었다 끊어지면, 초기 접속 속력을 높아기 위해 나중에 사용할 요량으로 스레드를 캐시한다.

Listing 5는 충분한 스레드가 캐시되었는지 살펴보는 방법을 보여준다.


Listing 5. 스레드 사용량 통계 보기
                
mysql> SHOW STATUS LIKE 'threads%';
+-------------------+--------+
| Variable_name     | Value  |
+-------------------+--------+
| Threads_cached    | 27     |
| Threads_connected | 15     |
| Threads_created   | 838610 |
| Threads_running   | 3      |
+-------------------+--------+
4 rows in set (0.00 sec)

여기서 가장 중요한 값은 Threads_created로 mysqld가 새로운 스레드를 생성할 때마다 하나씩 증가한다. 연속적으로 SHOW STATUS 명령을 내릴 때, 이 숫자가 급격하게 올라가면 스레드 캐시 수치를 높여야 한다. 예를 들어, my.cnf에서 thread_cache = 40을 설정하면 된다.

키 버퍼는 MyISAM 테이블을 위한 색인 블록을 저장한다. 이상적으로 이런 블록에 대한 요청은 디스크가 아니라 메모리에서 일어나야 한다. Listing 6은 메모리와 디스크에서 얼마나 많은 블록을 읽는지 확인하는 방법을 보여준다.


Listing 6. 키 효율성 확인
                
mysql> show status like '%key_read%';
+-------------------+-----------+
| Variable_name     | Value     |
+-------------------+-----------+
| Key_read_requests | 163554268 |
| Key_reads         | 98247     |
+-------------------+-----------+
2 rows in set (0.00 sec)

Key_reads는 디스크에서 요청한 숫자이며, Key_read_requests는 전체 숫자다. Key_reads를 Key_read_requests로 나누면 비적중률이 나온다. Listing 6을 보면 1000개 요청 당 0.6개가 적중하지 않았다. 1000개 요청 당 1개 이상 적중하지 않는다면 키 버퍼를 늘려야 한다. 예를 들어, key_buffer = 384M를 지정하면 버퍼를 384MB로 늘인다.

임시 테이블은 GROUP BY 절과 같이 추가 처리가 필요할 때 임시로 자료를 저장할 곳으로, 좀 더 고급 질의에서 사용된다. 이상적으로 이런 테이블은 메모리에 생성하지만, 임시 테이블이 너무 커질 경우 디스크에 써야 한다. Listing 7은 임시 테이블 생성과 관련한 통계를 보여준다.


Listing 7. 임시 테이블 사용량 보기
                
mysql> SHOW STATUS LIKE 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 30660 |
| Created_tmp_files       | 2     |
| Created_tmp_tables      | 32912 |
+-------------------------+-------+
3 rows in set (0.00 sec)

임시 테이블을 사용하면 Created_tmp_tables가 증가한다. 디스크 기반 테이블을 사용하면 Created_tmp_disk_tables가 증가한다. 이 비율을 정확하고 빠르게 결정하지 못하는 이유는 질의에 의존하기 때문이다. 시차를 두고 Created_tmp_disk_tables를 관찰하면 생성된 디스크 테이블 비율을 알 수 있고, 설정 값이 유효한지 살펴볼 수 있다. tmp_table_size와 max_heap_table_size 둘 다 임시 테이블 최대 크기를 제어하므로, my.cnf에서 양쪽 설정을 모두 확인해야 한다.

세션 단위 설정

이어지는 설정은 세션 단위다. 이 값을 설정할 때 신경을 써야 하는 이유는 잠재적인 접속 숫자에 설정값이 곱해지므로 메모리 사용량이 늘어나기 때문이다. 코드에서 해당 세션 값을 변경하거나 my.cnf에서 모든 세션 값을 변경할 수 있다.

MySQL이 정렬 작업을 수행할 때, 디스크에서 읽는 열을 저장하기 위한 정렬 버퍼를 할당한다. 정렬할 자료 크기가 너무 크다면, 디스크에 임시 파일로 자료를 저장하고, 다시 한번 정렬해야 한다.sort_merge_passes 상태값이 높으면, 디스크 활동량이 많다는 증거다. Listing 8은 정렬 관련 상태 카운터 몇 가지를 보여준다.


Listing 8. 정렬 통계 보기
                
mysql> SHOW STATUS LIKE "sort%";
+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| Sort_merge_passes | 1       |
| Sort_range        | 79192   |
| Sort_rows         | 2066532 |
| Sort_scan         | 44006   |
+-------------------+---------+
4 rows in set (0.00 sec)

sort_merge_passes가 높다면, sort_buffer_size 쪽에 관심을 기울여야 한다. 예를 들어, sort_buffer_size = 4M를 지정하면, 정렬 버퍼를 4MB로 늘인다.

MySQL은 또한 테이블을 읽기 위한 메모리를 할당한다. 이상적으로 보면 색인은 필요한 열에서만 읽도록 충분한 정보를 제공하지만, (자료 특성 때문이나 설계 잘못으로 인해) 읽어야 할 테이블이 많은 질의도 있기 마련이다. 이런 행동 양식을 이해하려면, (색인으로 직접 접근하는 대신) 테이블에서 다음 열을 직접 읽어야 하는 숫자와 SELECT 문 개수를 알아야 한다. 이렇게 하려면 Listing 9에서 소개하는 명령을 내린다.


Listing 9. 테이블 탐색 비율 확인
                
mysql> SHOW STATUS LIKE "com_select";
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Com_select    | 318243 |
+---------------+--------+
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE "handler_read_rnd_next";
+-----------------------+-----------+
| Variable_name         | Value     |
+-----------------------+-----------+
| Handler_read_rnd_next | 165959471 |
+-----------------------+-----------+
1 row in set (0.00 sec)

Handler_read_rnd_next / Com_select는 테이블 탐색 비율을 보여주는데, Listing 9에서는 521:1이다. 4000이 넘어가면, read_buffer_size = 4M와 같이 read_buffer_size 값이 충분히 크게 설정되어 있는지 확인한다. 이 값이 8M 이상으로 커진다면, 개발자에게 질의 조율이 필요하다고 알려주자!







TAG •
로그인 후 댓글쓰기가 가능합니다.

?
  • ?
    시공포착 2010.06.17 19:44

    너무 잘읽었습니다. 제 블로그에 출처 명기하고 좀 퍼갑니다. 문제시 삭제하겠습니다.


  1. MYSQL 미 해결 과제 : Can't connect to MySQL server on 'localhost'(10055)

  2. php로 이미지를 mysql디비 저장하고 보여주는 소스

  3. mysql 날짜타입에 기본값으로 현재시간넣기

  4. mysql 재시작, 사용자 추가 명령어

  5. MySQL TABLE 손상시 대응방법(db 내용이 안 나올때)

  6. php 에서 mysql 제어하기

  7. mysql 접속에러시 재부팅 하는 배치파일

  8. MYSQL 최적화

  9. MYSQL Qcache 값 조정~~~

  10. mysqli로 DB 검색 조회

  11. 서버 최적화 시키기(APACHE, PHP, MYSQL, XE 압축&캐싱&버퍼)

  12. 우린친구블로그 서버 다운해결방법과 MYSQL 실행 안될 때 복구방법

  13. 램드라이브를 이용한 mysql 번개속도로 개선방법

  14. [mysql] Created tmp disk tables 디스크 쓰기방지 대안

  15. innodb_use_sys_malloc에 따라 The InnoDB memory heap is disabled mysql error 메시지

Board Pagination Prev 1 Next
/ 1

http://urin79.com

우린친구블로그

sketchbook5, 스케치북5

sketchbook5, 스케치북5

나눔글꼴 설치 안내


이 PC에는 나눔글꼴이 설치되어 있지 않습니다.

이 사이트를 나눔글꼴로 보기 위해서는
나눔글꼴을 설치해야 합니다.

설치 취소