MySQL参数调整
http://hi.baidu.com/thinkinginlamp/blog/item/c37ad407ef0d58cf7a89472f.html
当我们安装好MySQL后，首要的问题就是对MySQL的各种参数进行调整以适应我们的要求。其实，MySQL本身已经为我们提供了很多现成的模板，我们可以选择合适的直接使用，比如：我们在MySQL源代码里能找到的配置文件模板有如下几种：
my-small.cnf：
# This is for a system with little memory (<= 64M) where MySQL is only used
# from time to time and it's important that the mysqld daemon
# doesn't use much resources.
my-medium.cnf：
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
my-large.cnf：
# This is for a large system with memory = 512M where the system runs mainly
# MySQL.
my-huge.cnf：
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
my-innodb-heavy-4G.cnf：
# 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.
我们可以根据内存的大小选择相应的配置文件，复制为/etc/my.cnf，重启mysql服务即可。
不过MySQL参数那么多，很多时候我们还是要知道他们具体的含义才能根据实际问题做出具体的调整。
-----------------------------------
我们可以通过SHOW VARIABLES;来查看系统参数，通过SHOW STATUS;来判断系统状态。
-----------------------------------
先来看看table_cache参数对性能的影响。摘录my-innodb-heavy-4G.cnf中对它的描述：
# 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]
table_cache = 2048
比如：当系统比较繁忙的时候，我们show variables;查到table_cache的值，再show status;发现open_tables的值和table_cache差不多，而且opened_tables还一直再增加，就说明我们的 table_cache设置的太小了。
-----------------------------------
下面看看key_buffer_size参数对性能的影响。摘录my-innodb-heavy-4G.cnf中对它的描述：
# 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_size = 32M
比如当我们show status;的时候，发现key_reads / key_read_requests的值很小，比如：1/100，或者1/1000，就说明key_buffer_size设置是合理的。另 外，key_buffer_size选项只对myisam起作用，且大小不应该超过内存的30%，如果不使用myisam表类型，那么设置成8~64M就 差不多了。
-----------------------------------
下面看看query_cache_size参数对性能的影响。摘录my-innodb-heavy-4G.cnf中对它的描述：
# 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.
query_cache_size = 64M
比如当我们show status;的时候，如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，如果Qcache_hits的值也非常 大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；如果Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会 影响效率，那么可以考虑不用查询缓冲。此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。
与查询缓冲有关的参数还有query_cache_type、query_cache_limit、 query_cache_min_res_unit。query_cache_type指定是否使用查询缓冲，可以设置为0、1、2，该变量是 SESSION级的变量。query_cache_limit指定单个查询能够使用的缓冲区大小，缺省为1M。 query_cache_min_res_unit是在4.1版本以后引入的，它指定分配缓冲区空间的最小单位，缺省为4K。检查状态值 Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多，这就表明查询结果都比较小，此时需要减小 query_cache_min_res_unit
-----------------------------------
此外，针对InnoDB表类型，还有很多调节参数。摘录my-innodb-heavy-4G.cnf中对它的描述：
# *** 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.
#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_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.
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_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_data_home_dir = <directory>
# Number of IO threads to use for async IO operations. This value is
# hardcoded to 4 on Unix, but on Windows disk I/O may benefit from a
# larger number.
innodb_file_io_threads = 4
# 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_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_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.
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_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.
innodb_log_file_size = 256M
# Total number of files in the log group. A value of 2-3 is usually good
# enough.
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_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.
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_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_lock_wait_timeout = 120
其中，我觉得最重要的就是innodb_buffer_pool_size选项，应该尽可能设置大点，至少是内存的50%。其他参数的描述请参考上面文档内容。
