MySQL配置my.cnf调优项详解

Posted on 2013 年 5 月 19 日 by Jose
MySQL配置调优项详解

以下是一份机器内存:64GB RAM,最大连接数为2000,MySQL使用InnoDB为主的配置说明,
某些项的最优值请根据实际生产需要来调.
[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.sockdefault-character-set = utf8[mysqld]################################ Charset and Collation ################################character-set-server  = utf8collation-server      = utf8_general_ci############################### Basic Settings       ###############################user                  = mysqlpid-file              = /var/run/mysqld/mysqld.pidport		      = 3306socket		      = /var/run/mysqld/mysqld.sockbasedir               = /usr/local/mysqldatadir               = /db/data01tmpdir                = /tmp#tmpdir               = /db/tmp01:/db/tmp02:/db/tmp03 #Recommend using RAMDISK for tmpdirdefault-storage-engine = InnoDBskip-external-lockingskip-name-resolve## Table and TMP settingsmax_heap_table_size             = 1G    #recommend same size as tmp_table_sizetmp_table_size                  = 1G    #recommend 1G min## Default Table Settings#sql_mode             = NO_AUTO_CREATE_USER################################# Error Logs & Slow logs ################################### Log Errorslog_error             = /db/logs01/mysql-error.errlog_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: 151max_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: 400table_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: 400table_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=0query_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: 2Gread_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: 2Gread_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: 4Gjoin_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: 256Kthread_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-binlog_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 consistencybinlog_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: 32kbinlog_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: 1Gmax_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 threadrelay_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 .indexrelay-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: FALSElog-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 1sync_relay_log = 1sync_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 cachekey_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 : 1concurrent_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 tablesmyisam-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 enabledinnodb_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 filesinnodb_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 = 3innodb_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: 300innodb_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: fdatasyncinnodb_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: 1innodb_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: 4innodb_read_io_threads=32innodb_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: 200innodb_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 flushinnodb_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~256Minnodb_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 ~ 128Minnodb_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: 1innodb_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: 50innodb_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]quickmax_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 = 20Msort_buffer_size = 20Mread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]interactive-timeout