my-innodb-heavy-4G.cnf

英文水平一般，难免有错误之处，之后会不断的进行参数测试，将尽力完善。
希望路人多多指点

#BEGIN CONFIG INFO

#DESCR: 4GB RAM, InnoDB only, ACID, few connections, heavy queries

#TYPE: SYSTEM

#END CONFIG INFO

#ACID: 原子性,一致性,隔离性,持久性

#

# This is a MySQL example config file for systems with 4GB of memory

# running mostly MySQL using InnoDB only tables and performing complex

# queries with few connections.

#

# MySQL programs look for option files in a set of

# locations which depend on the deployment platform.

# You can copy this option file to one of those

# locations. For information about these locations, see:

# http://dev.mysql.com/doc/mysql/en/option-files.html

#

# In this file, you can use all long options that a program supports.

# If you want to know which options a program supports, run the program

# with the "--help" option.

#

# More detailed information about the individual options can also be

# found in the manual.

#

#

# The following options will be read by MySQL client applications.

# Note that only client applications shipped by MySQL are guaranteed

# to read this section. If you want your own MySQL client program to

# honor these values, you need to specify it as an option during the

# MySQL client library initialization.

#

[client]

#user           = [your_name]

#password = [your_password]

#在这里定义用户名和密码后，可直接在命令行敲mysql，进入数据库

port  = 3306

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

#本机用socket套接字连接数据库。

# *** Application-specific options follow here ***

#

#

# The MySQL server

#

[mysqld]

# generic configuration options

port  = 3306

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

# back_log is the number of connections the operating system can keep in

# the listen queue, before the MySQL connection manager thread has

# processed them. If you have a very high connection rate and experience

# "connection refused" errors, you might need to increase this value.

# Check your OS documentation for the maximum value of this parameter.

# Attempting to set back_log higher than your operating system limit

# will have no effect.

# back_log可以把大量的系统连接放在监听队列里，在MySQL连接管理进程处理他们之前。

# 如果存在非常高的连接频率并且经历"connection refused"错误，需要增加这个值。

# 检查系统文档这个参数设置的最大值。

# 试图设置back_log的值，大于系统的限制值，是无效的。（因为操作系统对队列的大小是有限制的）

# 注：暂时我还没找到Linux操作系统，在哪里设置系统队列的大小限制。

# 将back_log设置一个非常大的值，然后查看variables，显示back_log=65535，

# 但是，sysctl -a|grep 65535，并没有这个值。下面这两个值是我猜测与之相关的。

#  # Controls the maximum size of a message, in bytes

#  kernel.msgmnb = 65536

#  # Controls the default maxmimum size of a mesage queue

#  kernel.msgmax = 65536

back_log = 50

 

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

# enhancement, if all processes that need to connect to mysqld run

# on the same host.  All interaction with mysqld must be made via Unix

# sockets or named pipes.

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

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

# 禁止TCP/IP连接，增加安全性。所有通信都要经过Unix sockets或者命名管道。

#skip-networking

 

# The maximum amount of concurrent sessions the MySQL server will

# allow. One of these connections will be reserved for a user with

# SUPER privileges to allow the administrator to login even if the

# connection limit has been reached.

# 限制连接MySQL的最大并发数量。即使达到了连接数限制，也会允许具有SUPER权限的用户连接。

max_connections = 100

 

# Maximum amount of errors allowed per host. If this limit is reached,

# the host will be blocked from connecting to the MySQL server until

# "FLUSH HOSTS" has been run or the server was restarted. Invalid

# passwords and other errors during the connect phase result in

# increasing this value. See the "Aborted_connects" status variable for

# global counter.

# 允许每台主机的最大错误数。如果达到范围极限，该主机将被阻塞MySQL的连接，除非运行

# "FLUSH HOSTS"，或者重启MySQL Server。在连接操作中，无效密码和其他错误都会导致

# 这个值的增加。可以查看"Aborted_connects"值的全局计数。

# mysql> show global status like 'Aborted_connects';

max_connect_errors = 10

 

# The number of open tables for all threads. Increasing this value

# increases the number of file descriptors that mysqld requires.

# Therefore you have to make sure to set the amount of open files

# allowed to at least 4096 in the variable "open-files-limit" in

# section [mysqld_safe]

# 所有线程打开表的数量。增加这个值，需要为mysqld进程增加文件描述符。

# 因此，需要在[mysqld_safe]中，至少将这个值设置为4096.

# 我猜测是不是需要将open-files-limit设置为table_open_cache的两倍。

# 在下面的[mysqld_safe]有open-files-limit的设置。

table_open_cache = 2048

 

# Enable external file level locking. Enabled file locking will have a

# negative impact on performance, so only use it in case you have

# multiple database instances running on the same files (note some

# restrictions still apply!) or if you use other software relying on

# locking MyISAM tables on file level.

# 启用外部文件所，将会对程序有负面的影响。如果你有多个数据库实例运行在同一个文件系统上

#（注意，一些应用将被限制），或者有其他软件需要在文件级别锁住MyISAM表

#external-locking

 

# The maximum size of a query packet the server can handle as well as

# maximum query size server can process (Important when working with

# large BLOBs).  enlarged dynamically, for each connection.

# 服务器能够处理的最大查询包（大的BLOB类型）可以为每个连接，动态增加该值。

max_allowed_packet = 16M

# The size of the cache to hold the SQL statements for the binary log

# during a transaction. If you often use big, multi-statement

# transactions you can increase this value to get more performance. All

# statements from transactions are buffered in the binary log cache and

# are being written to the binary log at once after the COMMIT.  If the

# transaction is larger than this value, temporary file on disk is used

# instead.  This buffer is allocated per connection on first update

# statement in transaction

# 在一个事物中，cache的大小保持二进制日志的SQL语句。如果经常用大量的事物语句，需要增加这个

# 值，以获得更好的性能。所有事物语句都被缓存到二进制日志缓存中，在执行COMMIT后，写入

# 二进制文件。如果一个事物比这个值要大，则用磁盘临时文件代替。

# 这个缓存值被分配给每个连接的第一次更新语句。我理解是，每个连接都会分配该缓存值。

binlog_cache_size = 1M

 

# Maximum allowed size for a single HEAP (in memory) table. This option

# is a protection against the accidential creation of a very large HEAP

# table which could otherwise use up all memory resources.

# 允许单一的HEAP（内存中）表的最大值。这个选项可以保护内存资源不被耗光。

max_heap_table_size = 64M

 

# Size of the buffer used for doing full table scans.

# Allocated per thread, if a full scan is needed.

# 该缓存大小用于做全表扫描

# 如果需要全表扫描，则会分配给每个线程。

read_buffer_size = 2M

 

# When reading rows in sorted order after a sort, the rows are read

# through this buffer to avoid disk seeks. You can improve ORDER BY

# performance a lot, if set this to a high value.

# Allocated per thread, when needed.

# 当行排序后，结果被缓存，可以避免扫描磁盘。如果设置一个较大的值，可以改善ORDER BY的性能。

# 当需要时，会分配给每个线程。

read_rnd_buffer_size = 16M

 

# Sort buffer is used to perform sorts for some ORDER BY and GROUP BY

# queries. If sorted data does not fit into the sort buffer, a disk

# based merge sort is used instead - See the "Sort_merge_passes"

# status variable. Allocated per thread if sort is needed.

# 分类缓存区被用于执行类似ORDER BY和GROUP BY的分类查询。如果分类数据没有放入sort_buffer，

# 就会放在磁盘中。

# show global status like 'Sort_merge_passes';

# 如果排序需要，会分配到每个线程。

sort_buffer_size = 8M

 

# This buffer is used for the optimization of full JOINs (JOINs without

# indexes). Such JOINs are very bad for performance in most cases

# anyway, but setting this variable to a large value reduces the

# performance impact. See the "Select_full_join" status variable for a

# count of full JOINs. Allocated per thread if full join is found

# 这个参数被用于优化JOINs（连接）。大多数情况下，这样的连接，对于性能是不利的，但是为

# 这个变量设置一个较大的值时，可以降低对性能的影响。

# show global status like 'Select_full_join';

join_buffer_size = 8M

 

# How many threads we should keep in a cache for reuse. When a client

# disconnects, the client's threads are put in the cache if there aren't

# more than thread_cache_size threads from before.  This greatly reduces

# the amount of thread creations needed if you have a lot of new

# connections. (Normally this doesn't give a notable performance

# improvement if you have a good thread implementation.)

# 我理解的意思如下，线程缓存，如果一个客户端断开连接，这个线程就会被放到thread_cache_size

# 中（缓冲池未满）。（通常情况下，这个值改善性能不大）

thread_cache_size = 8

 

# This permits the application to give the threads system a hint for the

# desired number of threads that should be run at the same time.  This

# value only makes sense on systems that support the thread_concurrency()

# function call (Sun Solaris, for example).

# You should try [number of CPUs]*(2..4) for thread_concurrency

# 线程并发数

thread_concurrency = 8

 

# Query cache is used to cache SELECT results and later return them

# without actual executing the same query once again. Having the query

# cache enabled may result in significant speed improvements, if your

# have a lot of identical queries and rarely changing tables. See the

# "Qcache_lowmem_prunes" status variable to check if the current value

# is high enough for your load.

# Note: In case your tables change very often or if your queries are

# textually different every time, the query cache may result in a

# slowdown instead of a performance improvement.

# 查询缓存用于缓存SELECT结果，再有相同的查询，则不会执行SELECT语句。

# ……如果大量的相同的查询并且很少改变表，将会大幅度提高性能。

# show status like 'Qcache_lowmem_prunes';

# 注意：如果表的改动非常频繁，或者每次查询都不同，查询缓存的结果会减慢系统性能。

query_cache_size = 64M

 

# Only cache result sets that are smaller than this limit. This is to

# protect the query cache of a very large result set overwriting all

# other query results.

# 我的理解，一个非常大的查询缓存结果最大限制是2M，这样可以避免一个非常大的缓存覆盖了

# 其他的缓存结果

query_cache_limit = 2M

 

# Minimum word length to be indexed by the full text search index.

# You might wish to decrease it if you need to search for shorter words.

# Note that you need to rebuild your FULLTEXT index, after you have

# modified this value.

# 编入全文索引的最小字长。如果需要搜索更短的字长，可以减小该值。

# 注意：在修改这个值后，需要重建全文索引。

#

ft_min_word_len = 4

 

# If your system supports the memlock() function call, you might want to

# enable this option while running MySQL to keep it locked in memory and

# to avoid potential swapping out in case of high memory pressure. Good

# for performance.

# 如果系统支持memlock()函数调用，在高内存使用下，可以启动该选项，获得良好的性能

#memlock

 

# Table type which is used by default when creating new tables, if not

# specified differently during the CREATE TABLE statement.

# 建表默认使用的存储引擎

default-storage-engine = MYISAM

 

# Thread stack size to use. This amount of memory is always reserved at

# connection time. MySQL itself usually needs no more than 64K of

# memory, while if you use your own stack hungry UDF functions or your

# OS requires more stack for some operations, you might need to set this

# to a higher value.

# 使用的线程堆栈大小。在连接的时候，内存的总数是保存的 。MySQL自己通常使用64K的内存。

# 当业务需要更多的堆栈时，需要设置更高的值。

# 还不知道有什么用处_20110515

thread_stack = 192K

 

# Set the default transaction isolation level. Levels available are:

# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE

# 设置默认的事物级别

transaction_isolation = REPEATABLE-READ

 

# Maximum size for internal (in-memory) temporary tables. If a table

# grows larger than this value, it is automatically converted to disk

# based table This limitation is for a single table. There can be many

# of them.

# 为临时表设置最大的内存。如果表的增长速度大于这个值，会自动刷新到磁盘。这个限制是针对单个表的。

tmp_table_size = 64M

 

 

# Enable binary logging. This is required for acting as a MASTER in a

# replication configuration. You also need the binary log if you need

# the ability to do point in time recovery from your latest backup.

# 启动二进制日志。在配置复制时，担任MASTER必须配置的。如果想针对时间点的数据恢复，也要打开二进制日志

log-bin=mysql-bin

 

# binary logging format - mixed recommended

# 二进制日志的格式-推荐为mixed

# STATEMENT格式，记录逻辑SQL语句

# ROW格式，不再记录简单的SQL语句，而是记录表的行更改情况。

# MIXED格式，混合模式，MySQL默认采用STATEMENT格式进行二进制日志文件的记录，以下情况使用ROW格式：

# 表的存储引擎为NDB

# 使用USER()等不确定函数

# 使用insert delay语句

# 使用用户自定义函数UDF

# 使用了临时表

binlog_format=mixed

 

# If you're using replication with chained slaves (A->B->C), you need to

# enable this option on server B. It enables logging of updates done by

# the slave thread into the slave's binary log.

# 如果复制结构采用了(A->B->C)，需要在B上启动这个参数。作用是，从MASTER获取的二进制日志也写入自己的二

# 进制日志文件中。

#log_slave_updates

 

# Enable the full query log. Every query (even ones with incorrect

# syntax) that the server receives will be logged. This is useful for

# debugging, it is usually disabled in production use.

# 启用完整查询日志。每个查询，甚至每个错误的语法都会记录。这个差用来debug。通常情况下禁用。

#log

 

# Print warnings to the error log file.  If you have any problem with

# MySQL you should enable logging of warnings and examine the error log

# for possible explanations.

# 把警告信息打印到错误日志中。

#log_warnings

 

# Log slow queries. Slow queries are queries which take more than the

# amount of time defined in "long_query_time" or which do not use

# indexes well, if log_short_format is not enabled. It is normally good idea

# to have this turned on if you frequently add new queries to the

# system.

# 慢查询日志。

slow_query_log

 

# All queries taking more than this amount of time (in seconds) will be

# trated as slow. Do not use "1" as a value here, as this will result in

# even very fast queries being logged from time to time (as MySQL

# currently measures time with second accuracy only).

# 大于这个查询时间的都会被记录。这里不能用1这个值，有时会记录一些查询很快的语句。

# 单位：秒

long_query_time = 2

 

# ***  Replication related settings

# 复制相关配置

# Unique server identification number between 1 and 2^32-1. This value

# is required for both master and slave hosts. It defaults to 1 if

# "master-host" is not set, but will MySQL will not function as a master

# if it is omitted.

# 主机号在1-2^32-1之间的数值

server-id = 1

 

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

#

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

# two methods :

#

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

#    the syntax is:

#

#    CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,

#    MASTER_USER=, MASTER_PASSWORD= ;

#

#    where you replace , , by quoted strings and

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

#

#    Example:

#

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

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

#

# OR

#

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

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

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

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

#    changes in this file to the variable values below will be ignored and

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

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

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

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

#    设置下面的变量，然后，事实上选择这个方法，在第一次开始复制的时候（一旦复制不成功，例如

#    写错了密码，slave连接失败），slave将创建一个master.info的文件，并且稍后的更改都会记录到

#    该文件中。在master.info中会忽略一些内容。

#    我的理解：如果在配置文件中my.cnf，设置了下面的这些变量，但是slave与master的连接失败，会把

#    信息记录到master.info文件中，如果想恢复复制，则需要shutdownslave，删除master.info并重启

#    slave。

#    可以用CHANGE MASTER TO替代上面的方法。（看方法1）

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

# (and different from the master)

# defaults to 2 if master-host is set

# but will not function as a slave if omitted

#server-id = 2

#

# The replication master for this slave - required

#master-host =

#

# The username the slave will use for authentication when connecting

# to the master - required

#master-user =

#

# The password the slave will authenticate with when connecting to

# the master - required

#master-password =

#

# The port the master is listening on.

# optional - defaults to 3306

#master-port =

# Make the slave read-only. Only users with the SUPER privilege and the

# replication slave thread will be able to modify data on it. You can

# use this to ensure that no applications will accidently modify data on

# the slave instead of the master

# slave设置read-only。仅具有SUPER权限的用户和复制进程可以修改数据。可以用这个参数，

# 保证应用不会在slave上修改数据。

#read_only

 

#*** MyISAM Specific options

#MyISAM特性选项

 

# Size of the Key Buffer, used to cache index blocks for MyISAM tables.

# Do not set it larger than 30% of your available memory, as some memory

# is also required by the OS to cache rows. Even if you're not using

# MyISAM tables, you should still set it to 8-64M as it will also be

# used for internal temporary disk tables.

# Key Buffer的大小，被用于MyISAM表的索引块缓存。

# 不要设置超过可用内存的30%，它需要用系统缓存。即使不用MyISAM表，也要设置该值8-64M，用于临时表。

key_buffer_size = 32M

 

# MyISAM uses special tree-like cache to make bulk inserts (that is,

# INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA

# INFILE) faster. This variable limits the size of the cache tree in

# bytes per thread. Setting it to 0 will disable this optimisation.  Do

# not set it larger than "key_buffer_size" for optimal performance.

# This buffer is allocated when a bulk insert is detected.

# MyISAM进行大量插入的缓存特性。

# 这个变量值是限制每个线程的。设置为0，会禁用这个优化。

# 不要大于key_buffer_size的设置值

bulk_insert_buffer_size = 64M

 

# This buffer is allocated when MySQL needs to rebuild the index in

# REPAIR, OPTIMIZE, ALTER table statements as well as in LOAD DATA INFILE

# into an empty table. It is allocated per thread so be careful with

# large settings.

# 这个缓存被分配，当MySQL在修复、优化、更改表信息，还有导入数据LOAD DATA INFILE

# 时，需要重建索引。

# 被分配给每个线程，要当心不要设置的太大。

myisam_sort_buffer_size = 128M

 

# The maximum size of the temporary file MySQL is allowed to use while

# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.

# If the file-size would be bigger than this, the index will be created

# through the key cache (which is slower).

# 当重建索引时，MySQL被允许用的最大的临时文件。

# 如果文件大小大于这个值，索引创建会比较慢。

myisam_max_sort_file_size = 10G

 

# If a table has more than one index, MyISAM can use more than one

# thread to repair them by sorting in parallel. This makes sense if you

# have multiple CPUs and plenty of memory.

# 如果一个表不止一条索引，MyISAM能够用不止一个的线程同时修复。

# 如果有多个CPU和足够的内存，这个值非常有意义。

myisam_repair_threads = 1

 

# Automatically check and repair not properly closed MyISAM tables.

# 如果没有正常关闭MyISAM引擎表，启动自动检查和修复。

myisam_recover

 

# *** INNODB Specific options ***

# Use this option if you have a MySQL server with InnoDB support enabled

# but you do not plan to use it. This will save memory and disk space

# and speed up some things.

# 让MySQL不加载innodb引擎，可以节省内存和磁盘空间，并提升一些速度。

#skip-innodb

 

# Additional memory pool that is used by InnoDB to store metadata

# information.  If InnoDB requires more memory for this purpose it will

# start to allocate it from the OS.  As this is fast enough on most

# recent operating systems, you normally do not need to change this

# value. SHOW INNODB STATUS will display the current amount used.

# innodb额外的内存池。如果InnoDB需求更多的内存，可以从系统分配。

# 一般不需要改变这个值

# SHOW INNODB STATUS，显示当前已用总数。

innodb_additional_mem_pool_size = 16M

 

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and

# row data. The bigger you set this the less disk I/O is needed to

# access data in tables. On a dedicated database server you may set this

# parameter up to 80% of the machine physical memory size. Do not set it

# too large, though, because competition of the physical memory may

# cause paging in the operating system.  Note that on 32bit systems you

# might be limited to 2-3.5G of user level memory per process, so do not

# set it too high.

# 用buffer pool缓存索引和行数据。设置较大的值，可以减少磁盘I/O。在专用的数据库服务器下，

# 需要设置成物理内存的80%大小。不要设置太大，以免因此与操作系统进行分页竞争。

# 注意，在32位系统中，每个进程被限制在2-3.5G，因此不要设置太高。

innodb_buffer_pool_size = 2G

 

# InnoDB stores data in one or more data files forming the tablespace.

# If you have a single logical drive for your data, a single

# autoextending file would be good enough. In other cases, a single file

# per device is often a good choice. You can configure InnoDB to use raw

# disk partitions as well - please refer to the manual for more info

# about this.

# 多个InnoDB数据文件，形成表空间。

# 在很多情况下，单独的文件驱动器通常是很好的选择。

innodb_data_file_path = ibdata1:10M:autoextend

 

# Set this option if you would like the InnoDB tablespace files to be

# stored in another location. By default this is the MySQL datadir.

# 设置InnoDB表空间文件的路径，默认是在MySQL datadir

#innodb_data_home_dir =

 

# 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.

#io线程数

innodb_write_io_threads = 8

innodb_read_io_threads = 8

 

# 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表空间损坏，给该变量设置一个非零的值，将有可能转储表。从1开始增加，

# 直到可以成功的转储表。

#innodb_force_recovery=1

 

# Number of threads allowed inside the InnoDB kernel. The optimal value

# depends highly on the application, hardware as well as the OS

# scheduler properties. A too high value may lead to thread thrashing.

# 设置InnoDB最大的线程并发数

innodb_thread_concurrency = 16

 

# If set to 1, InnoDB will flush (fsync) the transaction logs to the

# disk at each commit, which offers full ACID behavior. If you are

# willing to compromise this safety, and you are running small

# transactions, you may set this to 0 or 2 to reduce disk I/O to the

# logs. Value 0 means that the log is only written to the log file and

# the log file flushed to disk approximately once per second. Value 2

# means the log is written to the log file at each commit, but the log

# file is only flushed to disk approximately once per second.

# 如果设置为1，每次commit，InnoDB都会同步事务日志记录到磁盘。提供完整的ACID性能。

# 如果降低安全值，可以运行更小的事务级别。

# 可以设置0或者2，减小磁盘I/O。

# 设置为0的意思，仅仅写入日志文件，每秒钟一次刷新，将日志文件同步到磁盘。

# 设置为2的意思，每次commit都会写入日志文件，但是日志文件同步到磁盘，大约是每秒一次。

innodb_flush_log_at_trx_commit = 1

 

# Speed up InnoDB shutdown. This will disable InnoDB to do a full purge

# and insert buffer merge on shutdown. It may increase shutdown time a

# lot, but InnoDB will have to do it on the next startup instead.

# 加速InnoDB关闭。在关闭的时候，会禁用InnoDB的清理内存数据和合并插入缓存。

#innodb_fast_shutdown

 

# The size of the buffer InnoDB uses for buffering log data. As soon as

# it is full, InnoDB will have to flush it to disk. As it is flushed

# once per second anyway, it does not make sense to have it very large

# (even with long transactions).

# 日志缓冲大小。如果缓冲满了，就会刷新到磁盘。不必设置太大。

innodb_log_buffer_size = 8M

 

# Size of each log file in a log group. You should set the combined size

# of log files to about 25%-100% of your buffer pool size to avoid

# unneeded buffer pool flush activity on log file overwrite. However,

# note that a larger logfile size will increase the time needed for the

# recovery process.

# 在一个日志组中，每个redo-log的大小。结合buffer pool size设置其大小，25%-100%。

# 避免不需要的刷新，重写日志文件。注意：越大的日志文件恢复起来越慢。

innodb_log_file_size = 256M

 

# Total number of files in the log group. A value of 2-3 is usually good

# enough.

# 日志组数，通常设置为2-3个。

innodb_log_files_in_group = 3

 

# Location of the InnoDB log files. Default is the MySQL datadir. You

# may wish to point it to a dedicated hard drive or a RAID1 volume for

# improved performance

# 指定redo-log存放位置，默认是MySQL的datadir目录，可以将该日志文件指向一个专用的硬件

# 设备上，或者RAID1卷中，以提高性能。

#innodb_log_group_home_dir

 

# 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.

# 在buffer pool中，允许脏页的百分比。如果达到这个值，则启用刷新。

# 这是一个软限制，不保证按这个值来执行。

innodb_max_dirty_pages_pct = 90

 

# The flush method InnoDB will use for Log. The tablespace always uses

# doublewrite flush logic. The default value is "fdatasync", another

# option is "O_DSYNC".

# 日志的刷新方法。表空间一直用重写的刷新逻辑。默认值是 "fdatasync"，另外的参数是"O_DSYNC"

#innodb_flush_method=O_DSYNC

 

# How long an InnoDB transaction should wait for a lock to be granted

# before being rolled back. InnoDB automatically detects transaction

# deadlocks in its own lock table and rolls back the transaction. If you

# use the LOCK TABLES command, or other transaction-safe storage engines

# than InnoDB in the same transaction, then a deadlock may arise which

# InnoDB cannot notice. In cases like this the timeout is useful to

# resolve the situation.

# InnoDB为避免死锁的配置选项。

innodb_lock_wait_timeout = 120

 

[mysqldump]

# Do not buffer the whole result set in memory before writing it to

# file. Required for dumping very large tables

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

# Only allow UPDATEs and DELETEs that use keys.

#safe-updates

[myisamchk]

key_buffer_size = 512M

sort_buffer_size = 512M

read_buffer = 8M

write_buffer = 8M

[mysqlhotcopy]

interactive-timeout

[mysqld_safe]

# Increase the amount of open files allowed per process. Warning: Make

# sure you have set the global system limit high enough! The high value

# is required for a large number of opened tables

open-files-limit = 8192