Mysql my.cnf 설정

● 설정 기본

하드웨어

: 대용량 InnoDB에서는 CPU보다 메모리가 성능에 더 영향을 미칩니다.

 빠른 입출력을 원한다면 SSD나 SAS를 사용하는게 유리 합니다.

 

● my.cnf

[code lang="bash"]
vi /etc/my.cnf
[/code]
[code lang="Apache"]
[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
# generic configuration options
port = 3306
socket = /tmp/mysql.sock

back_log = 100
max_connections = 500
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 16M
join_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
bulk_insert_buffer_size = 16M
thread_cache_size = 128
thread_concurrency = 16
query_cache_type = 0
default_storage_engine = innodb
thread_stack = 192K
lower_case_table_names = 1
max_heap_table_size = 128M
tmp_table_size = 128M
local_infile = 0
max_prepared_stmt_count = 256K
event_scheduler = ON
log_bin_trust_function_creators = 1
secure_auth = 1
skip_external_locking
skip_symbolic_links
#skip_name_resolve

## config server and data path
basedir = /usr/local/mysql
datadir = /data/mysql/mysql-data
tmpdir = /data/mysql/mysql-tmp
log_bin = /data/mysql/mysql-binlog/mysql-bin
relay_log = /data/mysql/mysql-binlog/mysql-relay
innodb_data_home_dir = /data/mysql/mysql-data
innodb_log_group_home_dir = /data/mysql/mysql-iblog

## config character set
##utf8
character_set_client_handshake = FALSE
character_set_server = utf8
collation_server = utf8_general_ci
init_connect = "SET collation_connection = utf8_general_ci"
init_connect = "SET NAMES utf8"

## bin log
binlog_format = row
binlog_cache_size = 4M

## Replication related settings
server_id = 1
expire_logs_days = 7
slave_net_timeout = 60
log_slave_updates
#read_only

## MyISAM Specific options
key_buffer_size = 32M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 16M
myisam_repair_threads = 1
myisam_recover = FORCE,BACKUP

# *** INNODB Specific options ***
innodb_additional_mem_pool_size = 16M

#innodb_buffer_pool_size  전체 메모리의 70~80%가 적당하다. 16G -> 12G , 32G -> 26G 정도를 설정한다.
innodb_buffer_pool_size = 2G

innodb_data_file_path = ibdata1:1G:autoextend
innodb_file_per_table = 1
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 8M

#innodb_log_file_size 16G이상에서는 256 미만은 128M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 120
innodb_support_xa = 0
innodb_file_io_threads = 8

 

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no_auto_rehash

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive_timeout

[mysqld_safe]
open_files_limit = 8192
[/code]

# ================================================================================
# key_buffer : 
# 인덱스를 위한 버퍼크기, 키 버퍼의 크기는 공유된쓰레드의 크기이며 중복된 키를 자주 
# 사용할 경우 속도를 높일 수 있고, 기준은 show status 명령을 했을 때 
# Key_blocks_used를 체크해서 key_buffer 사이즈를 줄이던가 늘리던가 해야 한다. 
# 일반적인 key_buffer 의 크기는 Key_blocks_used*1024 로 잡으면 된다.
# Key_reads 가 크다면, key_buffer_size 변수가 너무 작은 것이다. 
# 대용량 테이블일 경우 쿼리가 느리다면, 인덱스 버퍼 크기가 작아서 느릴 수 있으므로
# key_buffer 크기를 늘려 주어야 한다. 
# show status 명령에서 다음과 같이 계산되는 것이 key_buffer 설정이 적당한 것이다.
# Key_reads/Key_read_request < 0.01   [ 0.00112318293327 ]
# key_write/key_write_request = 1     [ 0.0763101585936 ]
# | Key_blocks_used          | 360325     |  > 368972800
# | Key_read_requests        | 386263882  |
# | Key_reads                | 433845     |
# | Key_write_requests       | 5685285    |

key_buffer = 384M

# ================================================================================
# max_allowed_packet :
# 클라이언트 통신에 대해 사용되는 버퍼가 커질수 있는 최대 크기, 이변수의 가장 큰 값은
# MySQL 4 이전은 16MB 가 될 수 있고 MySQL 4와 그 이후는 1GB까지 될 수 있다.
# 클라이언트가 커다란 BLOB나 TEXT값들을 전송하는 경우라면, 이 서버 변수를 늘릴 
# 필요가 있고 클라이언트 측에서도 이것을 늘려야 할 것이다. 
# shell>mysql --set-variable=max_allowed_packet=64M

max_allowed_packet = 1M

# ================================================================================
# table_cache : 
# MySQL 서버가 한번에 열수 있는 테이블의 개수 설정, 
# 기본값은 64개이다, Opened_tables 값이 크다면 table_cache 값이 너무 작은 것이다.
# max_connections 값과도 관계가 있는데, 만약 100이라면 table_cache는 100*n 으로 
# 설정해 주는것이 좋다.
# n은 조인해서 열수 있는 최대 테이블 개수이다. 
# ex ) 사용중인 테이블이 20개이고, max_connections=100이라면, table_cache 는 그의
# 5~6배인 table_cache=512 정도로 설정하는 것이 좋다.

table_cache = 512

# ================================================================================
# sort_buffer_size , read_buffer_size :
# 정렬을 위해 사용하는 버퍼 크기, ORDER BY 나 GROUP BY 절을 빠르게 하기 위해서는 
# 이 값을 증가시킨다.
# 4.0.3 이전에서는 
# sort_buffer_size => sort_buffer 
# read_buffer_size => record_buffer 
# 라는 변수로 쓰였다.
# max_used_connections에 따라서 증가시키는 것이 좋다. 한번에 많은 쓰레드가 동시에
# 붙을 경우는 증가시키는 것이 좋다. max_used_connections가 높을 경우
# sort_buffer=6M 정도로 설정하는 것이 좋고, record_buffer=2M 정도가 적당하다.
# sort_buffer와 record_buffer 를 합쳐서 8M를 넘지 않는 것이 좋다.
# (sort_buffer+record_buffer)*max_connections를 할 경우 ram을 다 차지할 수 있기
# 때문이다.
# 많은 연속적인 테이블 스캔이 이루어진다면 read_buffer_size 값을 증가시켜야 한다.

sort_buffer_size = 2M
read_buffer_size = 2M

 
# ================================================================================
# myisam_sort_buffer_size :
# MyISAM 테이블 타입에서 인덱스를 만들거나(create table ..), 인덱스로 
# 변경(alter table ...) 하거나, 복구( repair ...) 할 때 사용하는 버퍼크기
myisam_sort_buffer_size = 64M

thread_cache = 8

 

# 쿼리 캐시 버퍼 사이즈

query_cache_size = 32M

 

# Try number of CPU's*2 for thread_concurrency

# thread_concurrency : 솔라리스에서만 사용된다.

thread_concurrency = 8

 

# max_connections :
# show status 명령을 했을때 , max_used_connections을 보고 늘리던가, 줄이던가 해야 한다.
# 최대값보다 10% 정도 더 크게 잡는 것이 좋다.
max_connections=1000

 

# max_connect_error :

# 서버가 차단되기 전에 최대 연결 오류수

max_connect_error=10000

 

# max_delayed_threads : INSERT DELAYED 쿼리를 사용할 수 있는 최대 쓰레드 수

max_delayed_threads=1000

 

# tmp_table_size = 디스크에 쓰여진 후 임시 테이블의 최대 크기

tmp_table_size=64M

 

# key_buffer_size :

# 인덱스를 위한 버퍼 사이즈,  느리다면 크기를 늘리는 것이 좋으나

# OS의 Ram 크기를 고려하여 늘려야 한다. 인덱스 데이터 캐싱을 위한

# 메모리 제한이 key_buffer 이다.

# 인덱스 기반의 검색과 정렬은 이 변수의 값을 늘리면 인덱스들을 만들거나 수정하는

# 동작만큼 더 빨라진다. 키 퍼버가 클수록 MySQL이 메모리 안에서 키를 찾을 확률이

# 늘어나는데 그만큼 인덱스 처리에 필요한 디스크 접근 횟수를 줄여주게 된다.

# 3.23 버전 이전에는 key_buffer 로 불렸다. 3.23부터 두 이름 모두 인식한다.

key_buffer_size=369M

key_buffer =369M

 

출처 : code-factory.tistory.com/42?category=724516