my.cnf 설정파일

my.cnf 설정파일

=================================================================

my.cnf

=================================================================

-- 용도별 카피

my-small.cnf : 64M

my-medium.cnf : 128-256M

my-large.cnf : 512M

my-huge.cnf : 1~2G

[mysqld]

key_buffer

- 인덱스를 위한 버퍼 공간

- 키버퍼의 크기는 공유된 쓰레드의 크기이며 중복된 키를 자주 사용할 경우 속도증진

- show status 의 Key_blocks_used 를 체크

Key_blocks_used * 1024 의 2~3 배 ... (메모리충분할 경우)

Key_blocks_used * 1024 (보통)

ex) 아래와 같은 상태가 될때가 key_buffer 가 적당..

Key_reads / Key_read_request < 0.01

key_write / Key_write_requests = 1

max_connections

- show status 의 max_used_connections 를 체크 (최대값보다 10% 크게 설정)

table_cache

- MySQL 서버가 한번에 열수 있는 테이블의 개수설정

- show status 의 Opened_tables 값이 클 경우 table_cache 를 늘림

- max_connections 값이 100 일 경우 100 * n (조인해서 열수 있는 최대테이블개수)

ex) 테이블 20 , max_connections 100 이라면

table_cache = 512

sort_buffer / record_buffer

- max_used_connections 의 값에 따라 증가

- 한번에 많은 쓰레드가 동시에 붙을 경우는 증가

- max_used_connections 가 높은 경우

sort_buffer=6M

record_buffer=2M

(sort_buffer + record_buffer < 8M)

- 메모리가 4G 정도 된다면 sort_buffer 값을 32M 정도를 잡는 것이 좋다.

- order by , group by 절을 빠르게 하기 위해서 sort_buffer 값을 증가시킬수 있다.

- 많은 연속적인 테이블 스캔이 이루어진다면 record_buffer 값을 증가

thread_cache / thread_concurrency

- cpu 개수 * 2

-- 퀴리캐시 설정 방법

set-variable = query_cache_limit=1M

set-variable = query_cache_size=2M

set-variable = query_cache_type=1

-> 쿼리캐시 사용 안할려면 query_cache_size = 0 으로 설정.

-> query_cache_type

1) 0 : off 쿼리캐시 기능을 사용하지 않음

2) 1 : on , SELECT SQL_NO_CACHE 를 제외하고 쿼리캐시사용

3) 2 : DEMAND, SELECT SQL_CACHE 사용시만 쿼리캐시사용

-> 쿼리캐시 변수보기

show variables like 'query%';

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

| Variable_name | Value |

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

| query_cache_limit | 1048576 |

| query_cache_size | 33554432 |

| query_cache_type | ON |

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

-> 쿼리캐시 상태보기

show status like 'qcache%';

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

| Variable_name | Value |

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

| Qcache_queries_in_cache | 12780 | : 캐시에 등록된 쿼리수

| Qcache_inserts | 2084642 | : 캐시에 추가된 쿼리수

| Qcache_hits | 173194 | : 캐시에 있는 쿼리를 사용한 수

| Qcache_lowmem_prunes | 361897 |

| Qcache_not_cached | 23724 | : 쿼리를 캐시에 저장하지 않은 수

| Qcache_free_memory | 20055720 | : 캐시가 남은 공간

| Qcache_free_blocks | 6237 | : 쿼리캐시에서 남은 메모리 블록

| Qcache_total_blocks | 32000 | : 쿼리캐시가 사용하는 총 블록 수

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

-> FLUSH

FLUSH QUERY CACHE 쿼리 캐시를 재정렬하므로 메모리를 유용하게 사용하도록 해준다.

(단 쿼리캐시를 비우지는 않는다)

FLUSH TABLES 쿼리 캐시 버퍼를 비운다.

( Qcache_queries_in_cache = 0 , Qcache_table_blocks = 1

FLUSH QUERY CACHE 는 모든 쿼리 캐시를 삭제

-- 로그설정

log-isam=파일경로

log-slow-queries=파일경로

long_query_time=5

EX ) MySQL 메모리 사용량

innodb_buffer_pool_size

+ key_buffer

+ max_connections * (join_buffer + record_buffer + sort_buffer + thread_stack + tmp_table_size)

+ max_connections * 2MB

=================================================================

튜닝참조

=================================================================

1. Opened_tables가 크면 table_cache variable의 값이 너무 작은것일지도 모른다

2. key_reads가 크면 key_cach의 값이 너무 작은것일지도 모른다

3. cache hit rate은 key_reads/key_read_requests이다

4. Handler_read_rnd가 크면 MySQL의 모든 테이블을 스캔하는 많은 쿼리가 있다거나 key를 적절히 사용하지 않는 조인들이 있을지 모른다

5. Threads_created가 크면 thread_cache_size값을 증가시키기를 바랄수도 있다

6. Created_tmp_disk_tables이 크면 디스크대신 임시테이블메모리를 얻기위해 tmp_table_size값을 증가시키기를 원할 수있다

7. 기본적으로 support-files밑에 my-huge.cnf, my-large.cnf, my-medium.cnf, my-small.cnf 를 기본으로 my.cnf 로 바꾸어 사용하면서 조정한다.

- memory (>=256M)이고 많은 테이블이 있으며, 적당한 클라이언트수에서 최고 성능을 유지하기 위해

shell> safe_mysqld -O key_buffer=64M -O table_cache=256 -O sort_buffer=4M -O record_buffer=1M &

이러한 옵션으로 서버를 실행하는데, my-cnf에서 이를 수정하여 사용하면 될 것이다.

- 128M메모리에 테이블이 적지만, 정렬이 많을 때

shell> mysqld_safe -O key_buffer=16M -O sort_buffer=1M

- 메모리는 적지만 많은 연결이 있을 때

shell> mysqld_safe -O key_buffer=512k -O sort_buffer=100k -O record_buffer=100k &

또는

shell> mysqld_safe -O key_buffer=512k -O sort_buffer=16k -O table_cache=32 -O record_buffer=8k -O net_buffer=1K &

8. group by와 order by작업이 가지고 있는 메모리보다 훨씬 클 경우, 정렬 후 row 읽는 것을 빠르게 하기위해 record_buffer값을 증가시켜라