[MySQL] my.cnf 최적화

MYSQL my.cnf 최적화

MySQL 성능 개선 파라미터

For better performance, we recommend replacing the existing MySQL parameters mentioned in startDB.bat/sh, available under <FirewallAnalyzerHome>\bin directory, with the following MySQL parameters changes for the corresponding RAM Size. Apart from MySQL parameter changes, you can also tune MySQL table sizes based on RAM availabililty to improve MySQL performance.

RAM Size	MySQL Parameters For  Windows Installation	MySQL Parameters For  Linux Installation
512 MB	Default configuration as given in startDB.bat	Default configuration as given in startDB.sh
1 GB	--innodb_buffer_pool_size=300M --key-buffer-size=150M --max_heap_table_size=150M --tmp_table_size=100M --table-cache=512	--innodb_buffer_pool_size=300M --key_buffer_size=150M --max_heap_table_size=150M --tmp_table_size=100M --table-cache=512
2 GB	--innodb_buffer_pool_size=900M --key-buffer-size=600M --max_heap_table_size=350M --tmp_table_size=100M --table-cache=512	--innodb_buffer_pool_size=900M --key_buffer_size=600M --max_heap_table_size=350M --tmp_table_size=100M --table-cache=512
3 GB	--innodb_buffer_pool_size=900M --key-buffer-size=600M --max_heap_table_size=350M --tmp_table_size=100M --table-cache=512	--innodb_buffer_pool_size=1400M --key_buffer_size=1000M --max_heap_table_size=350M --tmp_table_size=100M --table-cache=512
4 GB	--innodb_buffer_pool_size=900M --key-buffer-size=600M --max_heap_table_size=350M --tmp_table_size=100M --table-cache=512	--innodb_buffer_pool_size=1800M --key_buffer_size=1200M --max_heap_table_size=350M --tmp_table_size=100M --table-cache=512

 

# 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

 

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

# Don't listen on a TCP/IP port at all. This can be a security enhancement,

# if all processes that need to connect to mysqld run on the same host.

# All interaction with mysqld must be made via Unix sockets or named pipes.

# Note that using this option without enabling named pipes on Windows

# (via the "enable-named-pipe" option) will render mysqld useless!

#

#skip-networking

 

# Replication Master Server (default)

# binary logging is required for replication

 

# log-bin :

# 리플리케이션을 설정할때 활성화 시키는 곳?

# mysql 이 log-bin을 이용한다는 것을 알려준다.

log-bin

 

# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

 

# server-id :

# 리플리케이션 설정을 위해. id를 부여해 준다.

# slave 의 경우 .. 2 ~ .... 중복불가!

server-id      = 1

 

# binlog-do-db :

# 특정데이터베이스만 리플리케이션 기능을 사용할 수 있게 하는 옵션이다.

# 서버부담이 클수도 있으므로 인증 부분이 걸린 곳에 사용하면 좋을 것이다.

binlog-do-db   = V2PRJ

 

# Replication Slave (comment out master section to use this)

#

# To configure this host as a replication slave, you can choose between

# two methods :

#

# 1) Use the CHANGE MASTER TO command (fully described in our manual) -

#    the syntax is:

#

#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,

#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;

#

#    where you replace <host>, <user>, <password> by quoted strings and

#    <port> by the master's port number (3306 by default).

#

#    Example:

#

#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,

#    MASTER_USER='joe', MASTER_PASSWORD='secret';

#

# OR   

#

# 2) Set the variables below. However, in case you choose this method, then

#    start replication for the first time (even unsuccessfully, for example

#    if you mistyped the password in master-password and the slave fails to

#    connect), the slave will create a master.info file, and any later

#    change in this file to the variables' values below will be ignored and

#    overridden by the content of the master.info file, unless you shutdown

#    the slave server, delete master.info and restart the slaver server.

#    For that reason, you may want to leave the lines below untouched

#    (commented) and instead use CHANGE MASTER TO (see above)

#

# required unique id between 2 and 2^32 - 1

# (and different from the master)

# defaults to 2 if master-host is set

# but will not function as a slave if omitted

#server-id       = 2

#

# The replication master for this slave - required

#master-host     =   <hostname>

#

# The username the slave will use for authentication when connecting

# to the master - required

#master-user     =   <username>

#

# The password the slave will authenticate with when connecting to

# the master - required

#master-password =   <password>

#

# The port the master is listening on.

# optional - defaults to 3306

#master-port     =  <port>

#

# binary logging - not required for slaves, but recommended

#log-bin

 

# Point the following paths to different dedicated disks

#tmpdir        = /tmp/       

#log-update    = /path-to-dedicated-directory/hostname

 

# Uncomment the following if you are using BDB tables

#bdb_cache_size = 384M

#bdb_max_lock = 100000

 

# Uncomment the following if you are using InnoDB tables

#innodb_data_home_dir = /data/

#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend

#innodb_log_group_home_dir = /data/

#innodb_log_arch_dir = /data/

# You can set .._buffer_pool_size up to 50 - 80 %

# of RAM but beware of setting memory usage too high

#innodb_buffer_pool_size = 384M

#innodb_additional_mem_pool_size = 20M

# Set .._log_file_size to 25 % of buffer pool size

#innodb_log_file_size = 100M

#innodb_log_buffer_size = 8M

#innodb_flush_log_at_trx_commit = 1

#innodb_lock_wait_timeout = 50

 

[mysqldump]

quick

max_allowed_packet = 16M

 

[mysql]

no-auto-rehash

# Remove the next comment character if you are not familiar with SQL

#safe-updates

 

[isamchk]

key_buffer = 256M

sort_buffer_size = 256M

read_buffer = 2M

write_buffer = 2M

 

[myisamchk]

key_buffer = 256M

sort_buffer_size = 256M

read_buffer = 2M

write_buffer = 2M

 

[mysqlhotcopy]

interactive-timeout