https://github.com/RodrigoViolante/my.cnf/blob/master/my.cnf_8GB

8기가 램에 맞는 Mysql config 셋팅 값

################################################################################

#DATE: 2011-02-02

#SITE: http://datastrangler.com

#DESCRIPTION: MySQL config 5.0.x, 5.1.x, 5.5.x 

#RAM: 8GB RAM dedicated server

#Connections: 1000 connections

################################################################################

[mysqld_safe]

nice = -15

[client]

socket = /var/lib/mysql/mysql.sock

default-character-set = utf8

[mysqld]

## Charset and Collation

character-set-server = utf8

collation-server = utf8_general_ci

## Files

back_log = 300

open-files-limit = 8192

open-files = 1024

port = 3306

socket = /var/lib/mysql/mysql.sock

pid-file = /var/lib/mysql/mysql.pid

skip-external-locking

skip-name-resolve

## Logging

datadir = /var/lib/mysql

relay_log = mysql-relay-bin

relay_log_index = mysql-relay-index

#log = mysql-gen.log

log_error = mysql-error.err

log_warnings

log_bin = mysql-bin

log_slow_queries = mysql-slow.log

#log_queries_not_using_indexes

long_query_time = 10 #default: 10

max_binlog_size = 256M #max size for binlog before rolling

expire_logs_days = 4 #binlog files older than this will be purged

## Per-Thread Buffers * (max_connections) = total per-thread mem usage

thread_stack = 256K  #default: 32bit: 192K, 64bit: 256K

sort_buffer_size = 512K  #default: 2M, larger may cause perf issues

read_buffer_size = 512K  #default: 128K, change in increments of 4K

read_rnd_buffer_size = 512K  #default: 256K

join_buffer_size = 512K  #default: 128K

binlog_cache_size = 64K  #default: 32K, size of buffer to hold TX queries

## total per-thread buffer memory usage: 4736000K = 4.625GB

## Query Cache

query_cache_size = 32M #global buffer

query_cache_limit = 512K #max query result size to put in cache

## Connections

max_connections = 2000 #multiplier for memory usage via per-thread buffers

max_connect_errors = 100 #default: 10

concurrent_insert = 2 #default: 1, 2: enable insert for all instances

connect_timeout = 30 #default -5.1.22: 5, +5.1.22: 10

max_allowed_packet = 32M #max size of incoming data to allow

## Default Table Settings

sql_mode = NO_AUTO_CREATE_USER

## Table and TMP settings

max_heap_table_size = 1G #recommend same size as tmp_table_size

bulk_insert_buffer_size = 1G #recommend same size as tmp_table_size

tmp_table_size                  = 1G    #recommend 1G min

#tmpdir                         = /data/mysql-tmp0:/data/mysql-tmp1 #Recommend using RAMDISK for tmpdir

## Table cache settings

#table_cache = 512 #5.0.x <default: 64>

#table_open_cache = 512 #5.1.x, 5.5.x <default: 64>

## Thread settings

thread_concurrency = 16  #recommend 2x CPU cores

thread_cache_size = 100 #recommend 5% of max_connections

## Replication

#read_only

#skip-slave-start

#slave-skip-errors = <default: none, recommend:1062>

#slave-net-timeout = <default: 3600>

#slave-load-tmpdir = <location of slave tmpdir>

#slave_transaction_retries = <default: 10>

#server-id                      = <unique value>

#replicate-same-server-id       = <default: 0, recommend: 0, !if log_slave_updates=1> 

#auto-increment-increment       = <default: none>

#auto-increment-offset          = <default: none>

#master-connect-retry           = <default: 60>

#log-slave-updates = <default: 0 disable>

#report-host = <master_server_ip>

#report-user = <replication_user>

#report-password                = <replication_user_pass>

#report-port = <default: 3306>

#replicate-do-db             =

#replicate-ignore-db =

#replicate-do-table = 

#relicate-ignore-table =

#replicate-rewrite-db =

#replicate-wild-do-table =

#replicate-wild-ignore-table =

## Replication Semi-Synchronous 5.5.x only, requires dynamic plugin loading ability 

#rpl_semi_sync_master_enabled = 1 #enable = 1, disable = 0

#rpl_semi_sync_master_timeout = 1000 #in milliseconds <default: 10000>, master only setting

## 5.1.x and 5.5.x replication related setting. 

#binlog_format = MIXED

## MyISAM Engine

key_buffer = 512K #global buffer

myisam_sort_buffer_size = 128M #index buffer size for creating/altering indexes

myisam_max_sort_file_size = 256M #max file size for tmp table when creating/alering indexes

myisam_repair_threads = 4 #thread quantity when running repairs

myisam_recover = BACKUP #repair mode, recommend BACKUP 

## InnoDB Plugin Dependent Settings

#ignore-builtin-innodb

#plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so

## InnoDB IO Capacity - 5.1.x plugin, 5.5.x

#innodb_io_capacity = 200

## InnoDB IO settings -  5.1.x only

#innodb_file_io_threads = 16

## InnoDB IO settings -  5.5.x and greater

#innodb_write_io_threads = 16

#innodb_read_io_threads = 16

## InnoDB Plugin Independent Settings

innodb_data_home_dir            = /var/lib/mysql

innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend

innodb_log_file_size = 256M #64G_RAM+ = 768, 24G_RAM+ = 512, 8G_RAM+ = 256, 2G_RAM+ = 128 

innodb_log_files_in_group = 4 #combined size of all logs <4GB. <2G_RAM = 2, >2G_RAM = 4

innodb_buffer_pool_size = 4G #global buffer

innodb_additional_mem_pool_size = 4M #global buffer

innodb_status_file   #extra reporting

innodb_file_per_table #enable always

innodb_flush_log_at_trx_commit = 2 #2/0 = perf, 1 = ACID

innodb_table_locks = 0 #preserve table locks

innodb_log_buffer_size = 128M #global buffer

innodb_lock_wait_timeout = 60

innodb_thread_concurrency = 16 #recommend 2x core quantity

innodb_commit_concurrency = 16 #recommend 4x num disks

#innodb_flush_method = O_DIRECT   #O_DIRECT = local/DAS, O_DSYNC = SAN/iSCSI

innodb_support_xa = 0   #recommend 0, disable xa to negate extra disk flush

skip-innodb-doublewrite

## Binlog sync settings

## XA transactions = 1, otherwise set to 0 for best performance

sync_binlog = 0

## TX Isolation

transaction-isolation = REPEATABLE-READ #REPEATABLE-READ req for ACID, SERIALIZABLE req XA

## Per-Thread Buffer memory utilization equation:

#(read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size) * max_connections

## Global Buffer memory utilization equation:

# innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + query_cache_size

[mysqldump]

quick

quote-names

max_allowed_packet = 128M