mysql参数优化建议

要想提高mysql的性能，首先就是必须对mysql的配置参数进行了解，在了解了mysql的配置参数后，根据自己的项目需要以及运行环境，再做出相应的调整，那么以下这些参数是经过对mysql的官方配置参数说明、网上资料查询，以及自己的各种实验得出的个人结论。

skip-external-locking：跳过外部锁定。要明白这个参数，必须先了解external-locking（外部锁定，作用是为MYISAM数据表在多进程【多个服务公用同一个数据库目录】访问下锁定），大多数情况下，我们的mysql服务都是单进程服务的，从mysql官网上看，skip-external-locking参数默认情况下是ON的，

mysql> show variables like '%skip%';

+------------------------+-------+

| Variable_name          | Value |

| skip_external_locking  | ON    |

在配置文件[mysqld]下开启这个参数OK。

key_buffer_size = 256M：为MYISAM数据表开启供线程共享的索引缓存。我们的项目中数据表基本上用的是INNODB引擎，所以这个参数暂时不进行调整，有需要的可以参考

http://database.51cto.com/art/201010/229939.htm

max_allowed_packet = 16M：服务端最大允许接收的数据包大小。在没有调整该配置项的时候，服务端默认是4M。当然这个参数和mysql（默认16M）和mysqldump（默认为24M，我已经调整为16M）中的数据包大小有关系，一般情况下1M就可以，官方建议如果使用了blog或者更大的字符串时进行该参数的调整，一般情况下，数据库会被初始化为net_buffer_length（最小1024byte，最大是1M，默认是16KB）的大小。

table_open_cache = 512：所有线程打开表的数目（默认设置大小为1000）。如果opened_tables很大并且不经常使用flush tables，官方建议我们增加该参数的大小。这个值并不是越大越好，需要根据实际情况下open_tables和opened_tables的综合进行调整，详细可见

http://www.cnblogs.com/suredandan/p/4010931.html

sort_buffer_size = 512K：需要排序会话的缓存大小，是针对每一个connection的，这个值也不会越大越好，默认大小是256kb，过大的配置会消耗更多的内存。我个人还没有测试，可以查看

http://bbs.chinaunix.net/thread-1805254-1-1.html

read_buffer_size = 512K：为需要全表扫描的MYISAM数据表线程指定缓存，也是针对每个connection的，这个参数暂时我也不需要太关注。

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, which

defaults to 131072. The value of this variable should be a multiple of 4KB. If it is set to a value that is not

a multiple of 4KB, its value will be rounded down to the nearest multiple of 4KB.

This option is also used in the following context for all search engines:

• For caching the indexes in a temporary file (not a temporary table), when sorting rows for ORDER BY.

• For bulk insert into partitions.

• For caching results of nested queries.

and in one other storage engine-specific way: to determine the memory block size for MEMORY tables.

The maximum permissible setting for read_buffer_size is 2GB.

For more information about memory use during different operations, see Section 8.11.4.1, “How MySQL

Uses Memory”.

read_rnd_buffer_size = 1M：首先，该变量可以被任何存储引擎使用，当从一个已经排序的键值表中读取行时，会先从该缓冲区中获取而不再从磁盘上获取。默认为256K。

This variable is used for reads from MyISAM tables, and, for any storage engine, for Multi-Range Read

optimization.

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. See Section 8.2.1.15, “ORDER BY Optimization”. Setting

the variable to a large value can improve ORDER BY performance by a lot. However, this is a buffer

Server System Variables

627

allocated for each client, so you should not set the global variable to a large value. Instead, change the

session variable only from within those clients that need to run large queries.

The maximum permissible setting for read_rnd_buffer_size is 2GB.

另外可参见

http://bbs.chinaunix.net/forum.php?mod=viewthread&tid=3642777&highlight=

thread_cache_size = 18：有多少线程供服务缓存使用。

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. Requests for threads

are satisfied by reusing threads taken from the cache if possible, and only when the cache is empty is

a new thread created. This variable can be increased to improve performance if you have a lot of new

connections. Normally, this does not provide a notable performance improvement if you have a good

thread implementation. However, if your server sees hundreds of connections per second you should

normally set thread_cache_size high enough so that most new connections use cached threads. By

examining the difference between the Connections and Threads_created status variables, you can

see how efficient the thread cache is. For details, see Section 5.1.6, “Server Status Variables”.

The default value is based on the following formula, capped to a limit of 100:

8 + (max_connections / 100)

This variable has no effect for the embedded server (libmysqld) and as of MySQL 5.7.2 is no longer

visible within the embedded server.

query_cache_size= 8M：分配给查询缓存的内存大小。要配合query_cache_type使用，默认是不开启的。只从该参数的表面介绍来看，似乎值设置的越大，带来的效果会更好，但是请注意，查询缓存的工作原理，一个select语句过来后，数据库将查询结果缓存到cache中，等同样的select查询过来后，如果这段时间内该查询结果没有发生变化时，数据库将cache中将缓存结果返回，那么假如查询的相关数据表增删改特别多的话，数据表变更的这段时间内，要将cache失效，然后再更新数据，对于增删改来说，花费的时间就很多了，所以要有所权衡，这个参数我会在将来进行相关测试数据整理。

By default, the query cache is

disabled. This is achieved using a default value of 1M, with a default for query_cache_type of

0. (To reduce overhead significantly if you set the size to 0, you should also start the server with

query_cache_type=0.

The permissible values are multiples of 1024; other values are rounded down to the nearest multiple.

Note that query_cache_size bytes of memory are allocated even if query_cache_type is set to 0.

See Section 8.9.3.3, “Query Cache Configuration”, for more information.

The query cache needs a minimum size of about 40KB to allocate its structures. (The exact size

depends on system architecture.) If you set the value of query_cache_size too small, a warning will

occur, as described in Section 8.9.3.3, “Query Cache Configuration”.

query_cache_type = 1：1表示缓存所有查询，2表示缓存select sql_cache的查询，看如下内容（最近在网上看到该参数最好设置为0，也就是说关闭缓存，其性能在实际情况下很糟糕）。

0 or OFF Do not cache results in or retrieve results from the query cache. Note that

this does not deallocate the query cache buffer. To do that, you should set

query_cache_size to 0.

1 or ON Cache all cacheable query results except for those that begin with SELECT

SQL_NO_CACHE.

2 or DEMAND Cache results only for cacheable queries that begin with SELECT SQL_CACHE.

set global max_connections = 500：注意这个是通过命令行设置最大连接数，不是配置在配置文件的，因为我在配置文件里面尝试修改，重启mysql服务后并没有起效，通过该参数设置以后，重启服务后，依然没有起效，如果有朋友知道这个原因的话，请告知。如果说你的项目使用的是spring的连接池的时候，我认为spring个connection就对应的这个连接。根据你项目的需求而定。

log-bin=mysql-bin：开启二进制日志，并且日志的名称会按照mysql-bin***之类的依次生成。但是我一直有一些疑问，就是这个二进制日志是否是innodb的事务日志，是传统所说的事务日志吗？

binlog_format=mixed：二进制日志的格式为mixed，该中模式是statement和row模式的结合体，注意查看我同事写的

http://www.xx566.com/detail/177.html

这篇文章，里面讲解了我们项目在二进制日志设置上遇到的问题和解决办法，如果遇到类似的问题后，会有所帮助。

In MySQL 5.7, the default format is STATEMENT.

You must have the SUPER privilege to set either the global or session binlog_format value.

The rules governing when changes to this variable take effect and how long the effect lasts are the same

as for other MySQL server system variables. See Section 13.7.4, “SET Syntax”, for more information.

When MIXED is specified, statement-based replication is used, except for cases where only row-based

replication is guaranteed to lead to proper results. For example, this happens when statements contain

user-defined functions (UDF) or the UUID() function. An exception to this rule is that MIXED always

uses statement-based replication for stored functions and triggers.

innodb_buffer_pool_size = 512M：innodb缓存表和索引的内存空间，官网建议我们设置为物理内存的50-80%，但是请注意是专注于数据服务的机器，如果你的操作系统包含数据库和其他服务，请考虑其他服务占用的内存，对于Linux来说，这个参数会占用swap文件的大小（14-17项在centOs的环境下，会导致mysql无法启动,没有弄明白为什么，请注意）。

The size in bytes of the buffer pool, the memory area where InnoDB caches table and index data.

The default value is 128MB. The maximum value depends on the CPU architecture; the maximum

is 4294967295 (232-1) on 32-bit systems and 18446744073709551615 (264-1) on 64-bit systems.

On 32-bit systems, the CPU architecture and operating system may impose a lower practical

maximum size than the stated maximum. 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.

The larger you set the innodb_buffer_pool_size value, the less disk I/O is needed to access the

same data in tables more than once. On a dedicated database server, you might set this to up to 80% of

the machine physical memory size.

innodb_additional_mem_pool_size = 20M：用来设置innodb存储数据目录信息和其他内部数据结构的内存池大小。

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 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. The default value is 8MB.

This variable relates to the InnoDB internal memory allocator, which is unused if

innodb_use_sys_malloc is enabled.

innodb_log_file_size = 128M：用来设置每个日志文件在内存池中的大小，但是请注意，其他资料说“一旦设置了该参数，你就需要把原来的ib_logfile备份删除掉，否则mysql服务在重启的时候会出错"，我个人还没有进行测试。

The size in bytes of each log file in a log group. The combined size of log files

(innodb_log_file_size * innodb_log_files_in_group) cannot exceed a maximum value that

is slightly less than 512GB. A pair of 255 GB log files, for example, would allow you to approach the limit

InnoDB System Variables

1945

but not exceed it. The default value is 48MB. Sensible values range from 1MB to 1/N-th of the size of

the buffer pool, where N is the number of log files in the group. The larger the value, the less checkpoint

flush activity is needed in the buffer pool, saving disk I/O. Larger log files also make crash recovery

slower, although improvements to recovery performance in MySQL 5.5 and higher make the log file size

less of a consideration. For general I/O tuning advice, see Section 8.5.7, “Optimizing InnoDB Disk I/O”.

innodb_log_buffer_size = 16M：为innodb向磁盘刷新日志文件的缓存大小，可以相对应减少磁盘的IO。

The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value is

8MB. A large log buffer enables large transactions to run without a need to write the log to disk before

the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, making

the log buffer larger saves disk I/O. For general I/O tuning advice, see Section 8.5.7, “Optimizing InnoDB

Disk I/O”.

innodb_flush_log_at_trx_commit = 2：设置二进制日志刷新磁盘的时间点。可参照

http://blog.csdn.net/qing_gee/article/details/42551179

。

innodb_lock_wait_timeout = 20：事务等待超时的时间，默认为50秒。

The length of time in seconds an InnoDB transaction waits for a row lock before giving up. The

default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB

1941

transaction waits at most this many seconds for write access to the row before issuing the following

error:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

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. See also Section 14.19.4, “InnoDB Error Handling”.