MySQL配置調(diào)優(yōu)項(xiàng)詳解

以下是一份機(jī)器內(nèi)存:64GB RAM,最大連接數(shù)為2000,MySQL使用InnoDB為主的配置說(shuō)明,
某些項(xiàng)的最優(yōu)值請(qǐng)根據(jù)實(shí)際生產(chǎn)需要來(lái)調(diào).

[root@centos190 conf]# cat my.cnf 
### MySQL config 5.0/5.1/5.5
### RAM: 64GB RAM dedicated server
### Connections: 2000

[mysqld_safe]
nice = -15

[client]
socket		          = /var/run/mysqld/mysqld.sock
default-character-set = utf8

[mysqld]
#############################
### Charset and Collation ###
#############################
character-set-server  = utf8
collation-server      = utf8_general_ci


############################
### Basic Settings       ###
############################
user                  = mysql
pid-file              = /var/run/mysqld/mysqld.pid
port		      = 3306
socket		      = /var/run/mysqld/mysqld.sock
basedir               = /usr/local/mysql
datadir               = /db/data01
tmpdir                = /tmp
#tmpdir               = /db/tmp01:/db/tmp02:/db/tmp03 #Recommend using RAMDISK for tmpdir

default-storage-engine = InnoDB

skip-external-locking
skip-name-resolve

## Table and TMP settings
max_heap_table_size             = 1G    #recommend same size as tmp_table_size
tmp_table_size                  = 1G    #recommend 1G min

## Default Table Settings
#sql_mode             = NO_AUTO_CREATE_USER

##############################
### Error Logs & Slow logs ###
##############################

## Log Errors
log_error             = /db/logs01/mysql-error.err
log_warnings # default: 1, Print out warnings such as Aborted connection... to the error log.

## Log general queries
#general_log           = 1
#general_log_file      = /db/logs01/mysql-gen.log 
#log-output            = file

## Log slow queries
#slow-query-log
#slow_query_log_file    = /db/logs01/mysql-slow.log
#log_queries_not_using_indexes

## It's worth noting that query execution time does not include the time taken to acquire
## table locks. If a query regularly runs slowly because of a high level of locking, it 
## will not be logged.The value can be specified to a resolution of microseconds.
## Default : 10 (s)
#long_query_time = 10

## Optionally, you can also restrict the slow query log to those queries that cause 
## more than a certain number of rows to be examined.
## This feature was introduced in MySQL 5.5.
#min_examined_row_limit = 500


###########################
### Connections         ###                                                                                                                                                    
###########################

## The number of outstanding connection requests MySQL can have. This comes into play 
## when the main MySQL thread gets very many connection requests in a very shot time. 
## It then takes some time (although very little) for the main thread 
## to check the connection and start a new thread.  You need to increase this 
## only if you expect a large number of connections in a short period of time.
## This value is the size of the listen queue for incoming TCP/IP connections.
## Your operating system has its own limit on the size of this queue
## Check your OS documentation for the maximum value for this variable. 
## back_log cannot be set higher than your operating system limit.
back_log = 300

## The maximum permitted number of simultaneous client connections.
## Without considering MyISAM buffers, each connection uses about  192KB of memory.
## You need to make sure that-(max_connections * 192KB) + innodb_buffer_pool_size is 
## less than your total amount of system memory, otherwise MySQL could start swapping.
## 2000 connections will use ~400MB of memory.
## Set max_connections as high as the theoretical maximum amount of connections 
## that your application can ever have.
## max_connections = pm.max_children * number of application servers
## Default: 151
max_connections=2000

## If more than this many successive connection requests from a host are interrupted 
## without a successful connection, the server blocks that host from further connections. 
## You can unblock blocked hosts by flushing the host cache. To do so, issue a FLUSH HOSTS 
## statement or execute a mysqladmin flush-hosts command. If a connection is established
## successfully within fewer than max_connect_errors attempts after a previous connection
## was interrupted, the error count for the host is cleared to zero. 
## However, once a host is blocked, flushing the host cache is the only way to unblock it.
max_connect_errors = 100

## The number of seconds that the mysqld server waits for a connect packet 
## before responding with Bad handshake.
## default: 5 (<= 5.1.22), 10 (>= 5.1.23)
connect_timeout    = 30

## The packet message buffer is initialized to net_buffer_length bytes, but can grow up 
## to max_allowed_packet bytes when needed. 
## This value by default is small, to catch large (possibly incorrect) packets. 
## You must increase this value if you are using large BLOB columns or long strings. 
## It should be as big as the largest BLOB you want to use. 
## The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; 
## nonmultiples are rounded down to the nearest multiple
##
max_allowed_packet = 32M   #max size of incoming data to allow


############################
### Table Cache Settings ###
############################

## This controls the maximum number of open tables the cache can hold for all threads.
## You can check whether you need to increase the table cache by checking the Opened_tables 
## status variable.If the value is very large or increases rapidly and
## you do not use FLUSH TABLES often, then you should increase this value. 
## Or compared with the number of currently open tables (server status 'Open_tables')
## The table_open_cache and max_connections system variables affect the maximum number of 
## files the server keeps open.
## Range: 64~ 524288, default: 400
table_open_cache = 2048

## The number of table definitions (from .frm files) that can be stored in the 
## definition cache. If you use a large number of tables, you can create a large
## table definition cache to speed up opening of tables. The table definition cache
## takes less space and does not use file descriptors, unlike the normal table cache. 
## However, table_definition_cache doesn't offer as great a performance increase
## as the standard table_cache. The recommended way to utilize it is as a secondary 
## caching mechanism when the table cache becomes full.
## Range: 400~524288, Default: 400
table_definition_cache = 400

########################
### File Descriptors ###
########################

## The number of files that the operating system permits mysqld to open. If you find MySQL 
## complaining about Too Many Open Files, raising this value should be your first avenue.
## The value is 0 on systems where MySQL cannot change the number of open files.
open_files_limit = 16384


######################
### Thread Cache ###
######################

## How many threads the server should cache for reuse. When a client disconnects, 
## the client's threads are put in the cache. if there are fewer than thread_cache_size 
## threads there. By examining the difference between the Connections and Threads_created 
## status variables,  you can see how efficient the thread cache is. 
## The cache miss ratio = Threads_created/Connections.
## default : 0 (effectively disabling this feature), recommend 5% of max_connections 
thread_cache_size = 100

## This variable is specific to Solaris systems.
## Range: 1~512, Default: 10, recommend 2x CPU cores
#thread_concurrency = 16


######################
### Query Cache    ###
######################

## Disable the query cache. Both of these must be set as 0 due to a bug in MySQL.
## The query cache adds a global lock and performs poorly with a non-trivial write-load.
query_cache_size=0
query_cache_type=0

#query_cache_size                = 64M   #global buffer
#query_cache_limit               = 512K  #max query result size to put in cache

###################################
### Per-Thread Buffers          ###
###################################

## The sort buffer is allocated on a per-client basis for any query that needs to perform
## a sort operation (that is, ORDER BY and GROUP BY operations).sort_buffer_size is not 
## specific to any storage engine and applies in a general manner for optimization.
## If you see many 'Sort_merge_passes' per second in the global server status output, 
## you can consider increasing this value. 
## On Linux, MySQL uses mmap() rather than malloc() for allocating sort buffer sizes larger than
## 256 KB, and this is somewhat slower.So, ideally you should keep the sort buffer 
## at 256 KB or less. There is a similar threshold at 2 MB. If you do require a value
## higher than 256 KB, you should also aim to keep it under 2 MB. 
## The maximum permissible setting for sort_buffer_size is 4GB. 
## Values larger than 4GB are permitted for 64-bit platforms.
sort_buffer_size = 2M

## The read buffer is used for queries that perform sequential scans of tables.
## Each thread that does a sequential scan for a MyISAM table allocates a buffer
## of this size (in bytes) for each table it scans. 
## If you do many sequential scans,  you might want to increase this value.
## default: 128K, change in increments of 4K, Maximum allowed: 2G
read_buffer_size = 2M

## The read_rnd cache is the counterpart to read_buffer used when reading sorted rows 
## (rather than sequential rows). 
## When reading rows from a MyISAM table in sorted order following a key-sorting
## operation, the rows are read through this buffer to avoid disk seeks.
## Setting the variable to a large value can improve ORDER BY performance by a lot. 
## However, this is a buffer allocated for each client, so you should
## not set the global variable to a large value. Instead, change this session variable
## only from within those clients that need to run large queries
## default: 256K, Maximum allowed: 2G
read_rnd_buffer_size = 2M

## The minimum size of the buffer that is used for plain index scans, range index scans,
## and joins that do not use indexes and thus perform full table scans.
## In most cases, better column indexing produces a greater performance increase than 
## raising this buffer. Care should be taken not to make the join buffer too big
## default: 128K, Maximum allowed: 4G
join_buffer_size = 2M


## Stack size for each thread.The default of 192KB (256KB for 64-bit systems) is large
## enough for normal operation. If the thread stack size is too small, it limits the
## complexity of the SQL statements that the server can handle, the recursion depth 
## of stored procedures, and other memory-consuming actions.
## default: 32bit: 192K, 64bit: 256K
thread_stack = 512k

#############################
### MySQL Master Settings ###
#############################

## This mandatory variable is a unique number for the server 
## within the current MySQL topology.
server_id = 1

## auto_increment_* setting For multi-master topology 
#replicate-same-server-id = 0
#auto_increment_increment= 2
#auto_increment_offset   = 1

## 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 , master only setting

## This value controls how the master will wait for a timeout from one or more slaves 
## before reverting to asynchronous replication.
#rpl_semi_sync_master_wait_no_slave = 1 # Default: ON

## This defines the level of debugging logging. The allowed values are
## 1 (general level logging), 16 (detailed level logging), 
## 32 (network wait logging), and 64 (function level logging).
#rpl_semi_sync_master_trace_level = 1


## This enables the binary log and is mandatory for replication on the master host. 
## This variable also defines the basename of the binary log files.
log_bin       = /db/logs01/mysql-bin
log_bin_index = /db/logs01/mysql-index

## This variable controls the type of binary logging.
## STATEMENT (the default): logs the actual SQL statement to the binary log
## ROW : log changed data blocks to the binary log
## MIXED: will choose the most applicable method for the given statement necessary 
## to ensure data consistency
binlog_format = MIXED

## This cache is used to hold changes that are to be written to the binary log
## during a transaction. Increasing this value for very large transactions can 
## possibly increase performance. The Binlog_cache_use and Binlog_cache_disk_use 
## status variables can be useful for tuning the size of this variable.
## Default:32k, 
binlog_cache_size = 10M

## Beginning with MySQL 5.5.9, this variable specifies the size of the cache 
## for the binary log to hold non-transactional statements during transactions
## on a per client basis. There may be a benefit total increasing this value 
## using large non-transactional statements. In MySQL 5.5.3 through 5.5.8, the size 
## for both caches is set using binlog_cache_size. This means that, in these MySQL 
## versions, the total memory used for these caches is double the value set for 
## binlog_cache_size. The Binlog_stmt_cache_use and Binlog_stmt_cache_disk_use 
## status variables can be useful for tuning the size of this variable.
## Default: 32k
binlog_stmt_cache_size = 10M

## This is the maximum size of the binary log file before a new file is created.
## The FLUSH BINARY LOGS command will also dynamically close the current 
## binary log and create a new file.
## Range: 4k~1G, Default: 1G
max_binlog_size = 256M

## This variable defines the number of days binary log files are retained. 
## Files older than the number of days are removed (similar in operation 
## to a PURGE MASTER LOGS command) when a new binary log file is created.
expire_logs_days = 30


## These variables on the master host limit which statements are logged to
## the binary log based on the specified database name, preceded by a USE qualifier. 
## Use of binlog_do_db and binlog_ingnore_db can make a binary log unusable 
## in a point in time recovery of a full primary database.  These options are also
## incomplete, as they require all SQL to be preceded by an applicable USE, and do not
## handle cross-schema joins as you would expect.
#binlog-do-db = book3
#binlog-ignore-db=mysql

############################
### MySQL Slave Settings ###
############################

## By default, when a slave server starts, an implied SLAVE START occurs. 
## With this variable specified, the slave is not automatically started and
## must be performed manually with START SLAVE.
#skip_slave_start

## The relay logs hold replicated database changes retrieved from the 
## master binary log and written with the I/O thread
relay_log = /db/logs01/mysql-relay-bin

## This variable defines the name of the relay log index that holds the names
## of all the relay logs available. The default filename is the relay_log variable
## value with the extension .index
relay-log-index= /db/logs01/mysql-relay-index

## auto_increment_* in multi-master replication
#replicate-same-server-id       =  
#auto-increment-increment       = 
#auto-increment-offset          = 


## When defined and binary logging is enabled on a slave, all replicated changes
## from the SQL thread are also written to the slave server binary log. This option
## is used to chain multiple nodes together through replication. For example, if you
## have three servers (A, B, and C) and want to connect them in a chain you would 
## use log_slave_updates on B. B would replicate from A, and C from B, forming a chain,
## (A -> B -> C). 
## Binary logging must be enabled on the slave for this variable to have any effect.
## Default: FALSE
log-slave-updates

## This variable defines that the slave will not accept DML or DDL statements other 
## than those applied by the replication slave SQL thread. The exception is a user 
## with SUPER privilege will override this setting.
## Default: False
#read_only

## This variable controls how the relay log files are purged. The default of 1
## specifies that the relay log files are removed when they are no longer needed 
## for applying replication events. A value of 0 retains the log files.
## Default: 1
#relay_log_purge = 1

## These variables control how frequently a file sync is performed on the 
## respective relay log and relay log info file. The number represents the name of 
## executed SQL statements to apply before action. 
## The default is 0; the safest durability setting is 1
sync_relay_log = 1
sync_relay_log_info = 1

## These variables are used to filter which recorded master binary log statements
## are applied on the slave. The replicate_do_db and replicate_ingnore_db can
## cause errors, as they require all SQL to be preceded by an applicable USE and
## do not handle cross-schema joins as you would expect.
#replicate-do-db                =
#replicate-ignore-db            =
#replicate-do-table             = 
#relicate-ignore-table          =
#replicate-rewrite-db           =
#replicate-wild-do-table        = b.%
#replicate-wild-ignore-table    = a.%

## Replication error codes can be skipped automatically when specified with
## this variable. It is rarely a good idea to specify a value for slave_skip_errors,
## because there is no accountability of the occurrences of these silent errors,
## which will generally lead to data drift and/or loss of data integrity.
## Error 1062 means Duplicate entry
## Error 1060 means Duplicate column name
## Error 1050 means Table already exists
## Error 1051 means Unknown table 
#slave-skip-errors = 1062,1050,1060,1052
#slave-skip-errors=1062,1053,1146,1051,1050

## Semisynchronous Replication settings on Slave (MySQL 5.5+)
## When this option set to ON, semisynchronous replication on the slave is possible
#rpl_semi_sync_slave_enabled = 1

## This defines the level of debugging logging. The allowed values are 1, 16, 32, and 64
#rpl_semi_sync_slave_trace_level = 16

#######################
### MyISAM Settings ###
#######################

## This is MyISAM key cache: a memory buffer used to hold frequently accessed 
## index (key) blocks. The key cache is used only for buffering indexes, and 
## MySQL still needs memory for the tables's contents. For a dedicated MySQL
## server, the general rule of thumb is to allocate 25%~50% of the total memory
## to the key cache. If the key cache is too large, the system may run out of 
## physical memory and start swapping.
## Key read miss ratio = Key_reads / Key_read_requests, 
## the ratio should normally be less than 0.01.
##
## Key write miss ration = Key_writes/Key_write_requests, 
## the ratio is usually near 1 if you are using mostly updates and deletes,
## but might be much smaller if you tend to do updates that affect many rows 
## at the same time or if you are using the DELAY_KEY_WRITE table option.
## The fraction of the key buffer in use can be determined using 
## key_buffer_size in conjunction with the Key_blocks_unused status variable 
## and the buffer block size, which is available from the key_cache_block_size
## system variable: 1 - ((Key_blocks_unused * key_cache_block_size) / key_buffer_size)
## Default: 8388608 (~8M), Max Limit: 4G for each key cache
key_buffer_size = 30M

## Multiple Key Caches
## 1. through mysql command line.
## mysql > SET GLOBAL hot_cache.key_buffer_size = 1024*1024* 16;
## mysql > select @@GLOBAL.hot_cache.key_buffer_size, @@GLOBAL.hot_cache.key_cache_block_size;
## mysql > CACHE INDEX t1, t2 IN hot_cache;
## mysql > LOAD INDEX INTO CACHE t1, t2 IGNORE_LEAVES;
##  
## 2. through conf file
## in .my.cnf:
#mycache1.key_buffer_size = 512M
#mycache2.key_buffer_size = 1G
#init_file=/var/lib/mysql/custom.sql
## custom.sql contents as follows:
##CACHE INDEX categories, comments IN mycache1
##CACHE INDEX userprofiles IN mycache2

## The size of the buffer that is allocated when preloading indexes.
## Range: 1k~1G, Default: 32k
#preload_buffer_size = 32k 

## The size in bytes of blocks in the key cache.
## Your motivation for changing these is to match the block size used by Linux 
## for disk I/O (not to be confused with the filesystem's block size). 
## On x86 Linux, use a value of 4 KB.
## show the disk I/O block size:  perl -e '$a=(stat ".")[11]; print $a'
## Range: 512~16384 (16k), Default: 1024 (1k)
#key_cache_block_size = 4k

## This specifies the percentage of the key cache to allocate to the warm list. 
## The default value, 100, effectively causes MIS(Midpoint Insertion Strategy) 
## to be disabled (because the hot list will be of zero size).When lowering this value, 
## remember that the warm list will be used more than the hot list, and the sizes
## of each should reflect this. Broadly speaking, a division limit of approximately
## 60 percent to 90 percent should be right in most cases.
#key_cache_division_limit = 100

## This controls how long an unused entry should stay in the hot list 
## before being moved back into the warm list.
## The default is 300 seconds, and permitted values range from 100 seconds upward
#key_cache_age_threshold=300

## Enabled by default, the concurrent_insert option enables INSERT statements
## to be executed on a table at the same time as SELECT queries are reading from it.
## This partly alleviates the table-level locking. (Although it does nothing 
## to help the table-level locking that occurs on UPDATE and DELETE queries.)
## 0: turns this option off
##
## 1 (the default) : enables concurrent inserts only when there are no deleted 
## rows in the middle of the table.
##
## 2: enables concurrent inserts even if such deleted rows exist. If any SELECT
## queries are running, the data will be inserted at the end of the table. Otherwise, 
## it will be inserted in the gap left by the deleted row (the default behavior).
## Default : 1
concurrent_insert  = 2

## If set to 1, all INSERT, UPDATE, DELETE, and LOCK TABLE WRITE statements wait until there is 
## no pending SELECT or LOCK TABLE READ on the affected table. 
## This affects only storage engines that use only table-level locking (such as MyISAM, MEMORY, 
## and MERGE).If you choose not to enable this option globally, it can still be set on a per-query 
## basis using the following syntax:
## update low_priority into ...
## Default : 0
# low_priority_updates = 0

## OFF : DELAY_KEY_WRITE is ignored.
## ON (the default): MySQL honors any DELAY_KEY_WRITE option specified in CREATE TABLE statements.
## ALL : All new opened tables are treated as if they were created with the DELAY_KEY_WRITE
## option enabled.
## When a MyISAM index/key is updated, the default behavior is to write the changes back to disk.
## For frequently modified tables,this can result in a lot of disk writes.By enabling 
## delay_key_write (either on a per table basis,or globally by specifying a value of ALL),
## changes to the indexes are buffered in memory, and not written out to disk until the table is closed. ## This greatly speeds up index updates. The downside is that if MySQL is not shut down cleanly,
## there is a good chance of index corruption (because the buffer will not have been flushed to disk). 
## You can fix this with myisamchk, but for big tables, it will take a while to run.
## Or if you use this feature, you should add automatic checking of all MyISAM tables by 
## starting the server with the --myisam-recover option (for example, --myisam-recover=BACKUP,FORCE).
## If you enable external locking with --external-locking, there is no protection 
## against index corruption for tables that use delayed key writes. 
## delay_key_write = 1

## The maximum size of the temporary file that MySQL is permitted to use while re-creating 
## a MyISAM index (during REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE).
## If the file size would be larger than this value, the index is created 
## using the key cache instead, which is slower. The value is given in bytes.
## The default value is 2GB. If MyISAM index files exceed this size and disk space is available, increasing the value may help performance. 
## The space must be available in the file system containing the directory where the original 
## index file is located
#myisam_max_sort_file_size = 10G. 

## The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE 
## or when creating indexes with CREATE INDEX or ALTER TABLE
## Default:8388608 (8M), Max Limit: 4G+
#myisam_sort_buffer_size = 128M

## MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, 
## INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. 
## This variable limits the size of the cache tree in bytes per thread. 
## Setting it to 0 disables this optimization (This is a limit per thread.)
## Default: 8M
#bulk_insert_buffer_size = 8M

## Set the mode for automatic recovery of crashed MyISAM tables
myisam-recover = BACKUP,FORCE

## Thread quantity when running repairs.
## If this value is greater than 1, MyISAM table indexes are created in parallel (each index in its own thread) during the Repair by sorting process.
## Default: 1
#myisam_repair_threads = 4


#######################
### InnoDB Settings ###
#######################


######################################
### InnoDB Table-space and logging ###
######################################

## The common part of the directory path for all InnoDB data files in the shared tablespace. 
## This setting does not affect the location of per-file tablespaces when innodb_file_per_table is enabled
innodb_data_home_dir = /usr/local/mysql/data

## The paths to individual data files and their sizes.
innodb_data_file_path=ibdata1:128M;ibdata2:10M:autoextend

## The directory path to the InnoDB redo log files
innodb_log_group_home_dir = /usr/local/mysql/data

## The number of log files in the log group, default: 2
## combined size of all logs <4GB. <16G_RAM = 2, >16G_RAM = 3
innodb_log_files_in_group = 2

## The size in bytes of each log file in a log group. The combined size of log files 
## must be less than 4GB. 
## Bigger log file size = less I/O used for writes, longer recovery time during a failure.
## Maximum = innodb_buffer_pool_size / innodb_log_files_in_group
## Calculation: Check mysql status 'Innodb_os_log_written' to calculate how many MB of data per minute are getting written to the file,
## and setting the value to large enough to hold one hour's worth of writes.
## Default:5M, on small buffer pool settings (under 4G), advise the same value as innodb_buffer_pool_size 
## 64G_RAM+ = 368, 24G_RAM+ = 256, 8G_RAM+ = 128, 2G_RAM+ = 64 
innodb_log_file_size = 368M

## If innodb_file_per_table is disabled (the default), InnoDB creates tables in the system tablespace. 
## If innodb_file_per_table is enabled, InnoDB creates each new table using its own .ibd file 
## for storing data and indexes, rather than in the system tablespace.
## It only impacts new tables and will not affect old "monolithic file" style InnoDB tables
## default: On (>= 5.5.0, <= 5.5.6), Off (>= 5.5.7)
innodb_file_per_table = 1

## InnoDB has two different versions: Antelope (the older version) and Barracuda (the newest).
## This applies only for tables that have their own tablespace, so for it to have an effect, innodb_file_per_table must be enabled.
## Tell InnoDB that we always want to use the Barracuda.
## Default:Barracuda (>= 5.5.0, <= 5.5.6), Antelope(>=5.5.7)
innodb_file_format=barracuda

## This specifies the maximum number of .ibd files that MySQL can keep open at one time.The file descriptors 
## used for .ibd files are for InnoDB tables only. They are independent of those specified by the --open-files-limit 
## server option, and do not affect the operation of the table cache.
## What is the right setting? check: sudo lsof | grep -c "\.ibd$" (myisam you should count the *.MYD)
## Minimum: 10, default: 300
innodb_open_files = 16384

############################
### InnoDB I/O resources ###
############################

## Tells the operating system that MySQL will be doing its own caching and that it should skip using the file system cache.
## Prevents double caching of the data (once inside of MySQL and again by the operating system.)
## Default: fdatasync
innodb_flush_method=O_DIRECT

## Set this to 1 on your master server for safest, ACID compliant operation (sync after every transaction, high I/O).
## Set this to 2 on your slave, which can cause up to a second of data loss after an operating system crash, but frees up I/O
## because it only fsyncs data to disk once per second.
## default: 1
innodb_flush_log_at_trx_commit=1

## If the value of this variable is greater than 0, the MySQL server synchronizes its binary log to disk 
## after every sync_binlog writes to the binary log. There is one write to the binary log per statement if autocommit is enabled, 
## and one write per transaction otherwise. A value of 1 is the safest choice, because in the event of a crash 
## you lose at most one statement or transaction from the binary log.However, it is also the slowest choice.
## Default: 0 (no synchronizing to disk). Set this to 1 on your master server.
sync_binlog=1

## Background Threads
## InnoDB uses background threads to prefetch and store data. The default is 4 threads, but should really be 4 * # of CPU cores.
## Each thread can handle up to 256 pending requests, and a maximum of 64 threads may be configured.
## Before rushing to increase these limits, remember that they will not increase bandwidth on individual disks; although they will 
## potentially help to alleviate bottlenecks in high-load environments, where data is spread across multiple disks.
## Then Pending reads and Pending writes columns of the InnoDB monitor's output can help you judge if the thread concurrency would benefit
## from being raised. Recalling that each thread can queue up to 256 requests, if you see more than 256 x [number of threads] pending reads or writes,
## this will clearly cause a bottleneck, and a gradual increase in the number of read/write threads would be beneficial.
## Range: 1 ~ 64, Default: 4
innodb_read_io_threads=32
innodb_write_io_threads=32

## innodb_io_capacity enables you to specify the number of I/O operations per second that the disk subsystem can handle.
## This should be set as the maximum amount of IOPS that your system has. It sets a max cap on how much I/O that InnoDB can use.
## IOPS available from Disk: 
## Drive Type              Value
## Enterprise SSD          50,000
## Single Consumer SSD     10,000
## 4 Drives in a RAID-10   5000
## Single 7200RPM Drive    200
##
## Range: 100~2**64-1, Default: 200
innodb_io_capacity = 5000

## Enables InnoDB support for two-phase commit in XA transactions, causing an extra disk flush for transaction preparation. 
## This setting is the default. The XA mechanism is used internally and is essential for any server that has its binary log 
## turned on and is accepting changes to its data from more than one thread. If you turn it off, transactions can be written 
## to the binary log in a different order from the one in which the live database is committing them. This can produce different 
## data when the binary log is replayed in disaster recovery or on a replication slave
## Recommend 0 on read-only slave, disable xa to negate extra disk flush
innodb_support_xa = 1

## By default, InnoDB stores all data twice, first to the doublewrite buffer, and then to the actual data files.
## For benchmarks or cases when top performance is needed rather than concern for data integrity or possible failures,
## doublewrite can be turn off.
#skip-innodb-doublewrite

## The number of background threads devoted to the InnoDB purge operation.
## 0 (the default): the purge operation is performed as part of the master thread.
## 1 : Running the purge operation in its own thread can reduce internal contention within InnoDB, improving scalability.
## In theory, a separate thread should improve performance. But in many situations, it simply shifts
## the bottleneck from one place (queue in the thread) to another (disk contention).
##innodb_purge_threads = 0

## read-ahead Requests
## In InnoDB, memory pages are grouped in extents, where an extent consists of 64 consecutive pages. If more than a certain number of pages
## from an extent exists in the buffer cache, MySQL preloads the remaining pages in the extent.
## This variable controls the number of sequential pages in an extent that must be accessed (and be in the buffer cache) to trigger a read-ahead 
## for the remaining pages. When the last page of a sequence falls at the end of an extent, InnoDB will also read in the whole of the next extent.
## Monitor via : mysql> show status like '%ahead%';
## or checking "Pages read ahead, evicted without access, Random read ahead" in BUFFER POOL AND MEMORY via "mysql> show engine innodb status \G"
## Range: 0 ~64, default: 56                                                                                                                                                   
#innodb_read_ahead_threshold = 56

## The main thread in InnoDB tries to write pages from the buffer pool so that the percentage of dirty (not yet written) pages will not exceed this value
## Range: 0~99, Default:75
#innodb_max_dirty_pages_pct = 90

## Adaptive Flushing
## With adaptive flushing, InnoDB attempts to calculate the rate at which flushing needs to occur, based on the number of dirty pages and
## the rate at which they have historically been flushed. This allows the master thread to perform flushing based on workload at a much more constant rate,
## eliminating I/O spikes in disk usage. Adjusting the flush rate dynamically is intended to avoid bursts of I/O activity.
## Default: ON
#innodb_adaptive_flushing = 1

## The maximum delay between polls for a spin lock.
## The os_waits column of the SHOW ENGINE INNODB MUTEX output shows the number of times that InnoDB failed to acquire a lock through polling, 
## and fell back on the operating system's thread sleeping. Rapidly increasing values here (remember that you're usually interested in the rate of increase,
## rather than the absolute figure) could signify that the mutex is causing a bottleneck, and it may be worth experimenting with 
## raising innodb_spin_wait_delay in the hope that less threads need to be sent to sleep.
## Range:0~4294967295(2**32-1), Default: 6
#innodb_spin_wait_delay = 6

###############################
### InnoDB Memory resources ###
###############################

## The size in bytes of the buffer that InnoDB uses to write to the log files on disk.
## If you have big transactions, making the log buffer larger saves disk I/O
## default:8M, General recomendations range: 8M~256M
innodb_log_buffer_size = 128M

## The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. 
## The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, 
## you may set this to up to 90% of the machine physical memory size
## When the size of the buffer pool is greater than 1GB, setting innodb_buffer_pool_instances to a value 
## greater than 1 can improve the scalability on a busy server.
## 64GB -> 57GB, 32GB -> 28GB, 16GB -> 14GB, 8GB -> 7GB.
## On a read-heavy workload, if you use iostat and see that you have a very high utilization or service time, 
## you can usually add more memory (and increase innodb_buffer_pool_size) to improve performance.
## On a write-heavy workload (i.e., MySQL Master), it's far less important.
## Default: 128MB
##
innodb_buffer_pool_size = 57000M

## InnoDB uses a modified LRU for the buffer pool, based on an MIS. With the InnoDB buffer pool, 
## the default division is for the cold list (containing less frequently accessed items) to occupy 37 percent
## of the pool size, with the hot list (frequently accessed items) taking the remaining space.
## For applications that occasionally access large tables, it often make sense to reduce innodb_old_blocks_pct,
## to prevent this less commonly accessed data from being cached so heavily. Conversely, for small,frequently accessed tables, 
## raising innodb_old_blocks_pct increases the likelihood that this data will be kept in memory for future use.
## Default: 37 (3/8 of the pool), Range: 5~95
#innodb_old_blocks_pct = 37

## As with other MIS algorithms, new pages are inserted at the top of the cold list, making them prime candidates for promotion to the hot list.
## innodb_old_blocks_time specifies how long in milliseconds (ms) a block inserted into the old sublist must stay there after its first access 
## before it can be moved to the new sublist. The default value is 0: A block inserted into the old sublist moves immediately to the new sublist
## the first time it is accessed, no matter how soon after insertion the access occurs.If the value is greater than 0, blocks remain
## in the old sublist until an access occurs at least that many ms after the first access.
## Monitor BUFFER POOL AND MEMORY via 'show engine innodb status'.
## 'youngs (not-youngs)/s' shows the rate (in seconds) at which pages in the cold list have or have not been promoted to the hot list.
## A low number of youngs/s shows that few pages are promoted to the hot list. In an application that regularly accesses the same data, 
## this would suggest that innodb_old_blocks_time be lowered. Conversely, a high number of youngs/s on applications that perform 
## frequent large scans would suggest that innodb_old_blocks_time be raised.
#innodb_old_blocks_time = 0

## The size in bytes of a memory pool InnoDB uses to store data dictionary information and other internal data structures.
## The more tables you have in your application, the more memory you need to allocate here. If InnoDB runs out of memory in this pool,
## it starts to allocate memory from the operating system and writes warning messages to the MySQL error log.
## Default: 8M, Typical setting: 16M ~ 128M
innodb_additional_mem_pool_size = 20M

## Using Multiple Buffer Pools,  New as of MySQL 5.5
## On busy systems with large buffer pools, there will typically be many threads accessing data simultaneously
## from the buffer pool, and this contention can be a bottleneck. Since MySQL 5.5, InnoDB enables multiple buffer pools to be created.
## Each is managed independently and maintains its own LRU and mutual exclusion (mutex).
## The innodb_buffer_pool_instances configuration option is used to control this and takes a value between 1 (the default) and 64. 
## Because the use of multiple pools is intended only for high-end systems, this option has no effect when innodb_buffer_pool_size is lower than 1 GB.
## The main benefit of changing this from the default value is to increase concurrency when using larger buffer pools
## that have a high rate of data being changed. MySQL recommends setting this to a value such that each buffer pool instance
## remains at a minimum size of 1 GB or more.
## Range: 1~64, Default: 1
innodb_buffer_pool_instances = 4

## Whether InnoDB performs change buffering, an optimization that delays write operations to secondary indexes 
## so that the I/O operations can be performed sequentially. The permitted values are: 
## none : do not buffer any operations
## inserts: Caches insert operations only
## deletes: Caches delete operations; strictly speaking, the writes that mark index records for later deletion during a purge operation
## changes: Caches both inserts and deletes
## purges: Caches purges only, the writes when deleted index entries are finally garbage-collected
## all: buffer insert, delete-marking, and purge operations(physical deletion). This is the default value.
#innodb_change_buffering = all

## Adaptive hashing is a feature of InnoDB designed to improve performance on machines with large amounts of physical memory.
## This value controlls whether the InnoDB adaptive hash index is enabled or disabled. The adaptive hash index feature is useful for some workloads, 
## and not for others; conduct benchmarks with it both enabled and disabled, using realistic workloads
## Default: ON
#innodb_adaptive_hash_index = 1

## Whether InnoDB uses the operating system memory allocator (ON) or its own (OFF).
## The default value is ON.
#innodb_use_sys_malloc = 1


##################################
### InnoDB Concurrency settings ###
##################################

## This limits the number of threads that InnoDB can perform concurrently at a given time. Once the number of threads reaches this limit, 
## additional threads are placed into a wait state within a FIFO queue for execution. Threads waiting for locks are not counted 
## in the number of concurrently executing threads. Setting it to 0 means 
## that it's infinite and is a good value for Percona 5.5.
## For non-Percona setups, a recommended value is 2 times the number of CPUs plus the number of disks.
## Range: 0~1000, Default: 0
#innodb_thread_concurrency = 0

## The number of threads that can commit at the same time. A value of 0 (the default) permits
## any number of transactions to commit simultaneously
## Default: 0
#innodb_commit_concurrency = 0

##################################
### InnoDB Timeout settings ###
##################################

## The timeout in seconds an InnoDB transaction waits for a row lock before giving up.
## When a lock wait timeout occurs, the current statement is rolled back (not the entire transaction).
## To have the entire transaction roll back, start the server with the --innodb_rollback_on_timeout option
## You might decrease this value for highly interactive applications or OLTP systems, 
## to display user feedback quickly or put the update into a queue for processing later. 
## You might increase this value for long-running back-end operations, such as a transform step 
## in a data warehouse that waits for other large insert or update operations to finish.
## innodb_lock_wait_timeout applies to InnoDB row locks only. The lock wait timeout value does
## not apply to deadlocks, because InnoDB detects them immediately and rolls back one of 
## the deadlocked transactions. 
## Default: 50
innodb_lock_wait_timeout = 50

## In MySQL 5.5, InnoDB rolls back only the last statement on a transaction timeout by default.
## If this configuration option is enabled, a transaction timeout causes InnoDB to abort and 
## roll back the entire transaction.
## Setting to 1 can avoid error 1052 in MySQL Slave.
## Default: 0
#innodb_rollback_on_timeout = 1


[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout