최적화된 mysql configuration 값입니다.
[mysql]
default-character-set=utf8
socket=/tmp/mysql.sock
[mysqld]
#skip-name-resolve
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
server_id = 1
socket = /tmp/mysql.sock
log-bin=mysql-bin
max_connections=200
character-set-server=utf8
default-storage-engine=INNODB
max_allowed_packet=16M
sql_mode = "NO_ENGINE_SUBSTITUTION"
##
## 2) query log and slow query log
## ------------
##
log_warnings = 1
log-error = /usr/local/mysql/log/mysqld-error.log
#log_queries_not_using_indexes
slow_query_log = 1
slow_query_log_file = /usr/local/mysql/log/slow-query.log
long_query_time = 2
log_short_format = FALSE
log_output = FILE,TABLE
log_timestamps = SYSTEM
## General Log
general_log = 1
general_log_file = /usr/local/mysql/log/general.log
lower_case_table_names = 1
####################################################################
##
## 2-1. Replication Master or alone
## --------------------------------
##
## binary log
##
log-bin = /usr/local/mysql/mysql-bin/mysql-bin
binlog_format = ROW ## STATEMENT (default), ROW, MIXED
binlog_cache_size = 8M ## default 32768
expire_logs_days = 14
sync_binlog = 1 ## good performance but some dangerous
## PROCEDURE
log_bin_trust_function_creators = TRUE
#binlog-do-db = db_name
#binlog-ignore-db = mysql ## we replicate the `mysql' DB
##
## 2-2. Connections and charset
## ----------------
##
## backlog
##
back_log = 4096
## 0 no limit
##
max_connections = 1024
max_connect_errors = 9999999
##
## if webserver setting are:
## - lighttpd + mysql_pconnect => 60
## - any webserver + mysql_proxy => 60
## others, set to 5
##
## default 10 seconds
##
connect_timeout = 10
wait_timeout = 28800
interactive_timeout = 28800
lock_wait_timeout = 30 ## also innodb_lock_wait_timeout
max_allowed_packet = 128M
net_buffer_length = 16k ## default 16384
net_read_timeout = 15 ## default 30
net_retry_count = 10 ## default 10
net_write_timeout = 20 ## default 60
## charset
## http://dev.mysql.com/doc/refman/5.5/en/charset-connection.html
##
character-set-client-handshake = FALSE
skip-character-set-client-handshake
## euckr
##
#init_connect = 'SET NAMES euckr' ## same value character_set_client, character_set_results, character_set_connection
#init_connect = 'SET collation_connection=euckr_korean_ci'
character-set-server = utf8
collation-server = utf8_general_ci
## utf-8
##
init_connect = 'SET NAMES utf8'
init_connect = 'SET collation_connection = utf8_general_ci'
character-set-server = utf8
collation-server = utf8_general_ci
##
####################################################################
####################################################################
##
## 2-3. Threads
## ------------
##
thread_cache_size = 1024 ## do not set over 1024, but for event DB server 2048
#thread_concurrency = 16 ## CPU's*2
thread_stack = 512K ## default 256K
#thread_pool_size = 24 ## 16-32, http://dev.mysql.com/doc/refman/5.5/en/thread-pool-tuning.html
##
####################################################################
####################################################################
##
## 2-4. Query cache
## ----------------
##
query_cache_type = 1 ## must 1, off to `SET GLOBAL query_cache_type = 0'
query_cache_size = 64M ## guess 512M
query_cache_limit = 3M ## do not set to big(>=3MB)
key_cache_block_size = 4096 ## to OS block size
##
####################################################################
####################################################################
##
## 2-5. Buffer setting
## -------------------
##
table_open_cache = 8192 ## do not set over 8192, same as table-cache
max_heap_table_size = 32M
tmp_table_size = 32M ## refer Created_tmp_disk_tables / thread
read_buffer_size = 8M ## full table scans / thread
read_rnd_buffer_size = 8M ## ORDER BY / thread
join_buffer_size = 8M ## FULL-JOIN / thread
sort_buffer_size = 8M ## ORDER BY, GROUP BY / thread
##
####################################################################
####################################################################
##
## 2-7. InnoDB Specific options
## ----------------------------
##
innodb_fast_shutdown = 1
innodb_strict_mode = OFF
innodb_data_home_dir = /usr/local/mysql/innodb
innodb_data_file_path = ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:1G;ibdata7:200M:autoextend
## InnoDB log
##
innodb_log_group_home_dir = /usr/local/mysql/log
innodb_log_buffer_size = 32M ## refer Innodb_log_waits, Innodb_os_log_written
innodb_log_file_size = 1G ## less than innodb_buffer_pool_size * (0.1 ~ 0.2)
innodb_log_files_in_group = 3 ## number of ib_logfile# (lotation)
innodb_buffer_pool_size = 2G ## round(machine memory * 0.7)
innodb_buffer_pool_instances = 10 ## default 1
#innodb_additional_mem_pool_size = 64M
## [tuning point]
## http://dimitrik.free.fr/blog/archives/2012/04/mysql-performance-55-and-56labs-tpcclike.html
##
#innodb_checksums = 0
#innodb_doublewrite = 0
#innodb_support_xa = 0
innodb_thread_concurrency = 4 ## CPU's*2
## Maximum allowed percentage of dirty pages in the InnoDB buffer pool.
## If it is reached, InnoDB will start flushing them out agressively to
## not run out of clean pages at all. This is a soft limit, not
## guaranteed to be held.
##
innodb_max_dirty_pages_pct = 80
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 30
innodb_flush_method = O_DIRECT
## If you run into InnoDB tablespace corruption, setting this to a nonzero
## value will likely help you to dump your tables. Start from value 1 and
## increase it until you're able to dump the table successfully.
##
#innodb_force_recovery = 1
## [perf special]
## http://dimitrik.free.fr/blog/archives/2012/04/mysql-performance-55-and-56labs-tpcclike.html
##
#innodb_adaptive_flushing = 1
#innodb_read_io_threads = 16
#innodb_write_io_threads = 16
#innodb_io_capacity = 20000
#innodb_purge_threads = 1
# Number of IO threads to use for async IO operations. This value is
# hardcoded to 8 on Unix, but on Windows disk I/O may benefit from a
# larger number.
#
innodb_read_io_threads = 8
innodb_write_io_threads = 8
#innodb_io_capacity = 5000
## misc
##
innodb_file_per_table ## set 1 or empty (not 'ON')
innodb_open_files = 16384 ## to same table_open_cache
##
####################################################################
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
Data Engineer