mysql 参数调整_MySQL参数调整

当我们安装好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 =

# 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%。其他参数的描述请参考上面文档内容。