MySQL my.cnf 파일 설정

오라클의 PFILE 과 비슷한 설정 파일 이며 위치는 보통 /etc/my.cnf 파일에 위치한다. 

해당 옵션에 대한 설명 및 방법에 대해 설명한다.

# my.cnf ( innodb version)

#기본설정

### mysql db 설정 ###
### socket 위치설정 ###

socket=/wdb/mysql/mysql.sock

### mysql 구동 userid 설정 ###
user=mysql

### password() 함수 사용시 old_passwords 사용하도록 ###
old_passwords=1

### bin 로그 삭제 일 ###
expire-logs-days = 7

### 캐릭터 set 설정 하기 ###
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci

 

### network ###
connect_timeout          = 60                          # bad connection time
wait_timeout               = 28800                     # 커넥션이 자주 없을경우 해당 수치를 높여준다 현재는 8시간
max_connections          = 1024
max_allowed_packet      = 10M
max_connection_errors   = 1000 

#### limits ###
tmp_table_size        = 512M                   ### created_tmp_disk_tables 값의 증가에 따라서 값을 늘려주거나 함
max_heap_table_size   = 256M
table_cache           = 512

### logs ###
log_error                = /var/log/mysql/mysql-error.log         # 에러로그 남기기
slow_query_log_file      = /var/log/mysql/mysql-slow.log          # 슬로우 쿼리 남기기
slow_query_log           = 1
long_query_time          = 4                                      # 설정시간 이시간 이상된 로그 남기기
log                      = /usr/local/mysql/data/query.log        # 쿼리 로그 남기기
log-update               = update_logs                            # 업데이트 관련 로그
log                      = C:\Program Files\MySQL\MySQL Server 5.6\data

#### log-bin 관련  ###
log-bin=mysql-bin
#특정 데이터베이스 제외시키려면
binlog-ignore-db=somedatabase

 

###  필요 메모리 계산 #######################################################
#   innodb_buffer_pool_size (innodb)                                                                              
#+ key_buffer_size                                                                                                   
#+ max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)
#+ max_connections*2M
########################################################################

쿼리 : 메모리를 확인 해볼수 있는 쿼리 (실제로는 이렇게 하지 않음)
SELECT (@@query_cache_size + @@innodb_additional_mem_pool_size +
@@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@key_buffer_size +
@@max_connections * ( @@global.read_rnd_buffer_size + @@global.sort_buffer_size + @@binlog_cache_size +
@@global.join_buffer_size * IFNULL(@join_tables,1) + @@global.read_buffer_size *
IFNULL(@scan_tables, 1) + @@global.tmp_table_size * IFNULL(@tmp_tables, 1))) /
(1024 * 1024*1024)

 

## : 시퀀셜(순차) 스캔을 실행하는 스레드가 테이블 별로 할당하는 버퍼 크기(not shared)  ###

read_buffer_size = 512K                    # sequentail scan 인덱스를 사용하지 않는 테이블 스캔시 사용하는 메모리 영역
sort_buffer_size = 512K                    # group by order by  Sort_merge_passes , Sort_range, Sort_rows, Sort_Scan
join_buffer_size=  512K                     # minimum size 인덱스 조인, 범위 검색, 인덱스를 사용하지 않는 조인
read_rnd_buffer_size= 512K                 # 정렬후 읽기시 사용되는 메모리 order by 성능 향상 기대
#tmp_table_size                            # 메모리공간부족시 정렬시 사용공간 위 limmt 값에서 설정함
sql_buffer_result                          # SHOW PROCESSLIST state Sending data.일 경우 temporary 테이블에 데이터 저장시킴 다른 쓰레드가 접근 가능( not maria)
thread_cache_size                          # Threads_created / Connections default = 0 값이 1의 값을 넘을경우 값을 올려줌 정수 단위 값  1, 2 정도

### FILE SET (innodb) ###

# 로그 파일위치와 데이터 파일 위치 분리
#
# innodb 홈디렉토리 경로  ( 절대경로)
innodb_data_home_dir = /usr/local/mysql/data

# 데이터파일 옵션설정 파일명:초기용량:자동증가:최대사이즈 ( home_dir 상대 경로)
innodb_data_file_path=ibdata1:256M:autoextend:max:2000M

# 로그 파일
innodb_log_group_home_dir = /usr/local/mysql/data
# 로그 디렉토리 정보
innodb_log_arch_dir = /usr/local/mysql/data 
# 로그파일 그룹 갯수
innodb_log_files_in_group = 3

### INNODB SET  ###
default-storage-engine=              innodb            # Set default engine
innodb_buffer_pool_size=             5G                # Set buffer pool size to 50-80% of your computer's memory
innodb_additional_mem_pool_size=     20M               #Additional memory for InnoDB miscellaneous needs.
#Increase performance in optimize, backup, restore, compress and truncating a table ( 자신의 .ibd 파일을 사용해서 새로운 파일을 생성 myisam 방식)
innodb_file_per_table=                1
innodb_log_file_size=                1024M             # Set the log file size to about 25% of the buffer pool size
innodb_log_buffer_size=               8M               # blob 사용시 16M innodb_log_waits 가 증가시 늘려줌
innodb_flush_log_at_trx_commit=       1                # 1: commit 시만 flush  2:  1초마다 flush
innodb_thread_concurrency=            8                # cpu core 갯수 * 2
innodb_flush_method=                O_DIRECT           # This to avoid double buffering ( O_DSYNC 로그파일에 쓰고 datafile 에 flush  , O_DIRECT 파로 데이터 파일에 씀)
innodb_doublewrite = 0                                 # 페이지 기록시 Double write buffer에 memory 변경, 다음 datafile 변경
innodb_read_io_threads=               16               # MySQL 5.5 only  Default is 4
innodb_write_io_threads=              16               # MySQL 5.5 only  Default is 4
innodb_open_files=                     300              # default 300 innodb_file_per_table 옵션시 사용

#Default is 200. If SATA = 100. If SAS = 200. If RAID 5, 10 = 500 disk 7200RPM 이하면 100 IOPS(number of input/output operations per seccond)
innodb_io_capacity=                   200
innodb_lock_wait_timeout=             50               # rollback전에 deadlock까지 기다리는 timeout 시간(초)
# 아직 기록되지 않은 페이지의 페센트가 이값을 초과하지 않기 위해 버퍼플에 있는 페이지를 기록하고 쓴다.
innodb_max_dirty_page_pct =           90
# 하나의 버퍼풀에 여러개의 Thread가 동시 접근하면서 발생하는 경합을 줄이기 위해 작은 버퍼풀을 여러개 관리할 수 있도록 Multiple Buffer Pool
innodb_buffer_pool_instances =         6  
innodb_adaptive_hash_index  =         ON               # hash index on

bulk_insert_buffer_size = 64M  대용량 데이터 인서트시 사용
innodb_force_recovery = 0   # 1~6 단계 데이터 손상 복구옵션 단계 시작

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

[mysqldump]

socket      = /var/run/mysql/mysql.sock

quick                       덤프시 시간을 단축하기 위해 사용하는 옵션 

max_allowed_packet = 32M (mysqldump 시 이메모리값을 크게 함) --opt 옵션과 함께 사용하면 시간이 단축됨