my-innodb-heavy-4G.cnf

# 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() 函数调用的系统上有意义（例如 Sun Solaris）。

# 你应该对 thread_concurrency 尝试 CPU 数量的 2/4/6/... 倍。

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 结果并在稍后返回它们，不会再次实际执行相同的查询。

# 如果你有很多相同的查询并且很少改变表的话，查询缓存的启用将导致显著的速度改善。

# 请看“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.

#

# 只有缓存结果集是小于该限制的。

# 这可以保护一个非常大结果集的查询缓存覆盖所有其它查询结果。

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.

#

# 编制到全文检索索引的最小单词长度。

# 如果你需要检索更短的单词，你可能希望减小它。

# 注意，在你修改了该值以后，你需要重建你的 FULLINDEX 索引。

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() 函数调用，你可能想要启用该选项

#（运行 MySQL 以保持它锁定到内存，并在出现高内存压力时避免潜在的交换输出）。

# 这对性能是很有益的。

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

# specified differently during the CREATE TABLE statement.

# 如果在 CREATE TABLE 语句期间没有指定不同的，当创建一个新表时所使用的默认表类型

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 的内存，然而如果你使用的是你自己的堆栈 UDF 

# 函数或者你的系统针对某些操作需要更多堆栈，你可能需要设置该值为一个更高的值。

thread_stack = 192K

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

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

#

# 设置默认的事务隔离等级。

# 可用的级别有：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。

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.

#log_slave_updates

#

# 如果你正在使用连锁从服务器（A-〉B-〉C）进行复制，你需要在服务器 B 上启用该选项

# 它允许通过从服务器线程将日志记录到从服务器的二进制日志中来实现日志的更新。

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

#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

#

# 打印警告到错误日志文件。

# # 如果你有任何 MySQL 的问题，你应该启用警告日志并检查错误日志中可能的解释。

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

#

# 打印警告到错误日志文件。

# 如果你有任何 MySQL 的问题，你应该启用警告日志并检查错误日志中可能的解释。

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”值，因为这会导致甚至非常快的查询都会被不时地被记录

#（MySQL 当前的度量时间只精确到秒）。

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 之间的唯一服务器标识号。

# 该值对于主服务器和从服务器都是必须的。

# 如果“master-host”没有设置则默认为 1，但若是忽略，

# MySQL 将不会作为一个主服务器的功能。

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=<host>, MASTER_PORT=<port>,

#是主服务器的端口号（默认为 3306）。

#

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

# 基于这种因素，你可能想要离开下面未接触的行（已注释的）

# 并替代使用 CHANGE MASTER TO（请看上面）。

#

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

# 需要 2 到 2^32-1 之间的唯一 id（与主服务器不同）。

# (and different from the master)

# defaults to 2 if master-host is set

#如果“master-host”已被设置，默认设置为 2。

# but will not function as a slave if omitted

# 但若是忽略，将不会作为一个从服务器的功能。

#server-id = 2

#

# The replication master for this slave - required

# 针对该从服务器的复制主服务器 - 必须的。

#master-host = <hostname>

#

# The username the slave will use for authentication when connecting

# to the master - required

# 用户名，当连接到主服务器时，从服务器将用此来进行认证 - 必须的。

#master-user = <username>

#

# The password the slave will authenticate with when connecting to

# the master - required

# 密码，当连接到主服务器时，从服务器将用此来进行认证 - 必须的。

#master-password = <password>

#

        # The port the master is listening on.

# optional - defaults to 3306

# 端口，主服务器正在监听的。

# 可选的 - 默认为 3306。

#master-port = <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

# 让从服务器只读。

# 只有拥有 SUPER 特权的用户和复制从服务器线程能够修改它的数据。

# 你可以使用这个来确保不会有应用程序在无意中替代主服务器修改从服务器上的数据。

#

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

#

# 键缓冲区的大小，用来为 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 使用特殊的类似于树的缓存来让大批量插入（亦即 INSERT ... 

# SELECT、INSERT ... VALUES(...) 和 LOAD DATA INFILE）操作变得更快。

# 该变量限制每个线程的缓存树的字节大小。

# 将它设置为 0 将禁用该优化。

# 为了优化性能，不要将它设置得比“key_buffer_size”大。

# 当检测到大量的插入时，该缓冲区被分配。

bulk_insert_buffer_size = 64M

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

# large settings.

#

# 当 MySQL 需要通过 REPAIR、OPTIMIZE、ALTER 表语句重建索引，

# 以及 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).

#

# 当重建索引（在 REPAIR、ALTER TABLE 或 LOAD DATA INFILE 期间）时，

# MySQL 允许使用的临时文件的最大大小。

# 如果“file-size”比这个值大，索引将通过键缓存（更慢一些）创建。

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.

#

# 如果你有多个 CPU 和足够的内存，这是很有意义的。

myisam_repair_threads = 1

# Automatically check and repair not properly closed MyISAM tables.

# 自动地检查和修复没有正确关闭的 MyISAM 表。

myisam_recover

# *** INNODB Specific options ***

# *** INNODB 特定的选项 ***

#

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

#如果你有一个支持 InnoDB 启用的 MySQL 服务器，而你却并不计划使用它，请使用该选项

# 这可以保存一些内存和磁盘空间，并提高速度。

#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

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

# InnoDB，不像 MyISAM，使用一个缓冲池来缓存索引和行数据。

# 你将该值设得越大，在表中访问需要的数据时，磁盘 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 来使用原始的磁盘分区 - 请参考手册以获取更多有关这个的信。

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 数据目录。

#innodb_data_home_dir = <directory>

# 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 操作所使用的 IO 线程数。

# 该值在 Unix 系统上被硬编码为 4，但在 Windows 上，

# 磁盘 I/O 可能受益于一个更大的数字。

innodb_write_io_threads = 8

innodb_read_io_threads = 8

# If you run into InnoDB tablespace corruption, setting this to a nonzero

# 如果你遇到 InnoDB 表空间腐烂，设置该值为一个非零值，将很容易地帮助你导出你的表

# 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，InnoDB 在每次提交（提供完整的 ACID 行为）时刷新事务日志到磁盘。

# 值 0 表示日志只被写入到日志文件，并且日志文件大约每秒一次刷新到磁盘。

innodb_flush_log_at_trx_commit = 1

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

# 加速 InnoDB 的关闭。

# 这在关闭时将禁用 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 缓冲日志数据所使用的缓冲区大小。

# 一旦它满了，InnoDB 将刷新它到磁盘。

# 因为不管怎么它都是每秒刷新一次，所以没有必要让它变得很大（甚至是很长的事务）。

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.

# 一个日志组中每个日志文件的大小。

# 然而，注意，一个更大的日志文件大小将增加恢复处理所需的时间

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

# InnoDB 日志文件的位置。

# 默认为 MySQL 的数据目录。

# 你可能希望指定它到一个专用的硬盘或一个 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

# 如果它到达了，InnoDB 将开始积极地清理它们，以避免消耗完所有的干净页面。

# 这是一个软限制，不保证能够一直保持。

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

# InnoDB 对日志使用的刷新方法。

# 表空间总是使用双写刷新逻辑。

# 默认值为“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 在它自己的锁定表中自动地检测事务死锁，并回滚事务。

# 如果像这样，超时对于解决问题是很有用的。

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.

# 只允许 UPDATE 和 DELETE 使用键。

#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