[펌] mysql 설정파일 my.cnf (Linux)

------------------------------------------------------------------------------------------------

[출처]

1) http://www.servergoogle.net/entry/mysql-%EC%84%A4%EC%A0%95%ED%8C%8C%EC%9D%BC-mycny

2) http://namineya.blogspot.com/2008/03/mysql-mycnf.html

------------------------------------------------------------------------------------------------

########################################################################### # MySQL 설정 File # 작성 김정균 <http://www.oops.org> ########################################################################### # # 이 파일에는 MySQL 의 전체적인 설정이 들어간다. [ client ] 설정은 각 유저 # 들의 홈디렉토리 ~user/.my.cnf 라는 파일로 만들어 설정을 할수 있다. 이 설 # 정 파일들의 퍼미션은 소유자 readonly 즉 600 을 유지해야 한다. # # 현설정 파일은 Plll 700 Mhz 에 RAM 512 M 를 기준으로 MySQL 전용 서버로 작 # 하는 경우를 기준으로 작성이 되어 있다. 각 시스템에 대한 설정은 아래의 파 # 일들을 참조 하도록 한다. # # PATH /usr/share/mysql # my-huge.cnf MySQL 전용으로 메모리가 1-2G 일 경우 # my-large.cnf MySQL 전용으로 메모리가 512 이상일 경우 # my-medium.cnf MySQL 전용으로 메모리가 32-64M 정도일 경우나 # 메모리가 128 이상이면서 다른 데몬과 같이 서비스 될 경우 # my-small.cnf MySQL 전용으로 메모리가 64M 이하일 경우 # # 메모리가 32M 보다 작을 경우에는 my.cnf 설정을 사용 하지 말고 디폴트 값을 # 사용하도록 한다. ########################################################################### # MySQL Clinet 설정 ########################################################################### [client] password = blahblah port = 3306 socket = /var/lib/mysql/mysql.sock default-character-set = euckr ########################################################################### # MySQL Server 설정 ########################################################################### # 다음 사항을 잘 고려하여 설정을 해야 한다. # key_buffer_size+(record_buffer+sort_buffer)*max_connections < 실제 메모리 양 # # key_buffer 는 실 메모리의 1/4 정도면 무난하다. # # 또한 table_cache 는 # (MaxFileOpen-MaxConnection-(temporary table 에 사용되는 파일핸들) ) / 2 # 을 고려하여 설정을 한다.(단 너무 크게 잡을 필요는 없으며 최대 동시 접속자 수의 1.5-2 배 정도라고 생각하면 된다. 즉 500 으로 지정이 되어 있으면 # DB 의 테 # 이블의 총수가 500개 까지는 모두 캐싱이 가능하다. 위의 공식은 # 최대값이라 생각을 하면 된다.) # [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock #skip-show-database skip-locking skip-name-resolve #skip-networking key_buffer = 512M max_allowed_packet = 4M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 thread_stack = 126976 max_connections = 200 join_buffer_size = 1M max_connect_errors = 1024 wait_timeout = 30 old_passwords = 0 #tmpdir = /dev/shm #log_slow_queries = /var/log/mysql/slow.log read_rnd_buffer_size = 8M thread_cache_size = 8 # sql cache 사용 # # query 캐쉬 사용 옵션 # 0 -> 사용안함 # 1 -> 사용함 # 2 -> 선택적 사용 # http://www.mysql.com/doc/en/Query_Cache_Configuration.html 참고 query_cache_type = 1 query_cache_size = 512M query_cache_limit = 2M # CPU 갯수 * 2 를 해서 설정을 한다. 1 개일 경우에는 설정 필요가 없다. thread_concurrency = 4 # Charset 설정 # # 기본 언어셋 설정은 /etc/sysconfig/mysql 의 default_char 을 함 character-set-client-handshake = 0 #character-set-server = euckr #default-collation = euckr_korean_ci #init_connect = 'set names euckr' # 메시지 언어 선택 language = /usr/share/mysql/korean/ # MySQL 의 리플리케이션 기능을 사용하기 위한 binary log 설정 #log-bin #server-id = 1 # InnoDB 테이블 설정 # # innodb 를 사용하지 않을 경우에는 아래를 설정한다. skip-innodb # innodb 를 위한 경로 설정 #innodb_data_home_dir = /var/lib/mysql/ #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend #innodb_log_group_home_dir = /var/log/mysql/ #innodb_log_arch_dir = /var/log/mysql/ # _buffer_pool_size 를 RAM 의 50-08% 정로도 설정한다. 하지만 이 경우 메모리 # 사용량을 너무 많이 잡아 먹을 수도 있으므로 어느정도 시스템 모니터링을 하도 # 록 한다. #innodb_buffer_pool_size = 384M #innodb_additional_mem_pool_size = 20M # _log_file_size 는 buffer pool size 의 25% 정도로 설정한다. #innodb_log_file_size = 100M #innodb_log_buffer_size = 8M #innodb_lock_wait_timeout = 50 # 0 => commit 시 로그 파일에 바로 기록 안함 # 1 => commit 시 로그 파일에 바로 기록 # 2 => commit 시 OS 레벨의 파일 캐쉬에 기록 (3.23.52 추가) #innodb_flush_log_at_trx_commit = 2 ########################################################################### # MySQL dump 설정 ########################################################################### [mysqldump] quick max_allowed_packet = 16M ########################################################################### # MySQL ########################################################################### [mysql] no-auto-rehash ########################################################################### # Isamchk ########################################################################### [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M ########################################################################### # Myisamchk ########################################################################### [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M ########################################################################### # Mysqlhotcopy ########################################################################### [mysqlhotcopy] interactive-timeout # Local variables: # tab-width: 4 # c-basic-offset: 4 # End: # vim600: noet sw=4 ts=4 fdm=marker # vim<600: noet sw=4 ts=4

 

---

 

 

 

=================================================================
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 값을 증가시켜라