MySQL配置优化选项

浏览数：39 / 时间：2015年06月12日

[mysqld]

port = 3306

默认为3306

user = mysql

datadir = /data/mysql

默认为/var/lib/mysql/

tmpdir = /dev/shm

默认为/tmp

slave-load-tmpdir = /tmp

官方文档中slave_load_tmpdir的解释：从服务器为复制LOAD DATA INFILE语句创建临时文件的目录名。当数据恢复后，mysql会自动删除对应的数据文件。

默认不进行主从的操作所以没有

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

默认值就为/var/lib/mysql/mysql.sock

如果是tar包安装的话就为/tmp/mysql.sock

key_buffer = 128M

指定索引缓冲区的大小，它决定索引处理的速度

建议key_buffer设置为物理内存的1/4(针对MyISAM引擎)，甚至是物理内存的25%，如果key_buffer_size设置太大，系统就会频繁的换页，降低系统性能。因为MySQL使用操作系统的缓存来缓存数据，所以我们得为系统留够足够的内存；在很多情况下数据要比索引大得多。

默认8M

max_allowed_packet = 128M

默认4M

table_open_cache = 2048

当某一连接访问一个表时，MySQL会检查当前已缓存表的数量。如果该表已经在缓存中打开，则会直接访问缓存中的表已加快查询速度；如果该表未被缓存，则会将当前的表添加进缓存并进行查询。

table_open_cache指定表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_open_cache的值。如果你发现open_tables等于table_open_cache，并且opened_tables在不断增长，那么你就需要增加table_open_cache的值了(上述状态值可甀SHOW STATUS LIKE ‘Open%tables’获得)。注意，不能盲目地把table_open_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。

Open_tables / Opened_tables >= 0.85

Open_tables / table_cache <= 0.95

默认2000

join_buffer_size = 128MJoin操作使用内存

联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享!

默认256KB

sort_buffer_size = 128M排序使用内存

查询排序时所能使用的缓冲区大小。注意：该参数对应的分配内存是每连接独占!如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 6 =

600MB。所以，对于内存在4GB左右的服务器推荐设置为6-8M。

默认256KB

read_buffer_size = 64M顺序读取数据缓冲区使用内存

读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享!

默认256KB

read_rnd_buffer_size = 64M随机读取数据缓冲区使用内存

和顺序读取相对应，当 MySQL 进行非顺序读取（随机读取）数据块的时候，会利用这个缓冲区暂存读取的数据。如根据索引信息读取表数据，根据排序

后的结果集与表进行Join等等。总的来说，就是当数据块的读取需要满足一定的顺序的情况下，MySQL 就需要产生随机读取，进而使用到read_rnd_buffer_size 参数所设置的内存缓冲区。

默认256KB

myisam_sort_buffer_size = 128M

在REPAIR TABLE过程中，或通过CREATE INDEX/ALTER TABLE为MyISAM表添加索引时为了对索引排序所能够使用的缓冲空间大小。最小值为4，32位系统上所能够使用的最大值为4294967295，即4G；64位系统上可以使用更大的空间。作用范围为全局或会话级别，可用于配置文件，属动态变量。

默认8M

query_cache_size = 128M

指定查询能够使用的缓冲区大小如果设置它为 0 ，查询缓冲将被禁止(缺省值为 0 )

Qcache_queries_in_cache 在缓存中已注册的查询数目

Qcache_inserts 被加入到缓存中的查询数目

Qcache_hits 缓存采样数数目

Qcache_lowmem_prunes 因为缺少内存而被从缓存中删除的查询数目

Qcache_not_cached 没有被缓存的查询数目 (不能被缓存的，或由于 QUERY_CACHE_TYPE)

Qcache_free_memory 查询缓存的空闲内存总数

Qcache_free_blocks 查询缓存中的空闲内存块的数目

Qcache_total_blocks 查询缓存中的块的总数目

Qcache_queries_in_cache 在缓存中已注册的查询数目

Qcache_inserts 被加入到缓存中的查询数目

Qcache_hits 缓存采样数数目

Qcache_lowmem_prunes 因为缺少内存而被从缓存中删除的查询数目

Qcache_not_cached 没有被缓存的查询数目 (不能被缓存的，或由于 QUERY_CACHE_TYPE)

Qcache_free_memory 查询缓存的空闲内存总数

Qcache_free_blocks 查询缓存中的空闲内存块的数目

Qcache_total_blocks 查询缓存中的块的总数目

query_cache_limit = 8M

指定单个查询能够使用的缓冲区大小，缺省为1M

max_tmp_tables = 512

单个客户端连接能打开的临时表数目

默认32

tmp_table_size = 128M

默认16M

max_heap_table_size = 128M

这个变量定义了用户可以创建的内存表(memory table)的大小.这个值用来计算内存表的最大行数值

默认16M

thread_cache = 32线程栈信息使用内存

主要用来存放每一个线程自身的标识信息，如线程id，线程运行时基本信息等等，我们可以通过 thread_stack 参数来设置为每一个线程栈分配多大的内

存。

默认13

thread_concurrency = 16

，错误设置了thread_concurrency的值, 会导致mysql不能充分利用多cpu(或多核), 出现同一时刻只能一个cpu(或核)在工作的情况。thread_concurrency应设为CPU核数的2倍. 比如有一个双核的CPU, 那么thread_concurrency的应该为4; 2个双核的cpu, thread_concurrency的值应为8.

默认为10

max_connect_errors = 99999999

max_connect_errors是一个MySQL中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况。max_connect_errors的值与性能并无太大关系。

默认100

wait_timeout = 300

默认28800

interactive_timeout = 300

在交互模式下，interactive_timeout取代wait_timeout。这样，如果有的客户端是交互模式方式连接mysql server。那么客户端的timeout受制于interactive_timeout。如果有的客户端是非交互模式，长连接mysql server。那么客户端的timeout受制于wait_timeout。(是否是交互模式的连接，由客户端决定)

默认28800

long_query_time = 3

慢查询区分多长时间为慢查询

back_log = 600

指定MySQL可能的连接数量。当MySQL主线程在很短的时间内接收到非常多的连接请求，该参数生效，主线程花费很短的时间检查连接并且启动一个新线程。 back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。如果系统在一个短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的TCP/IP连接的侦听队列的大小。不同的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。。对于Linux系统推荐设置为小于512的整数。

默认值为152

myisam_repair_threads = 1

默认就是1

myisam-recover = DEFAULT

自动检查和修复无法正确关闭MyISAM表

default表示每次访问表时会先判断是否需要修复，但是不会强制修复（仅仅尝试从key cache中修复），backup表示修复时会先将老的数据文件先做个备份，force表示强制修复。

默认为off也就是不开启此功能

expire_logs_days = 10

二进制日志保存的天数

默认是0用不过期

default_tmp_storage_engine = myisam

默认临时表存储引擎

默认为InnoDB的

###

plugin-load = thread_pool.so

thread pool包含数个thread groups,每个thread group管理一组客户端连接。当连接建立以后,thread pool以轮询的方式分配他们到thread group.

thread group的数量是通过thread_pool_size配置得到的,默认是16个,最大64个,最小1个。

每个thread group最大可以有4096个线程。

默认没有加载任何插件

collation_server = utf8_general_ci

默认latin1_swedish_ci

character_set_server = utf8

默认为latin1

character-set-client-handshake = false

skip-character-set-client-handshake = true

默认情况下是不开启这样的功能

innodb_lock_wait_timeout = 3600

指的是事务等待获取资源等待的最长时间，超过这个时间还未分配到资源则会返回应用失败；参数的时间单位是秒，最小可设置为1s(此时需要考虑应用端的频繁异常处理会消耗性能，不能设置过小)

默认50

group_concat_max_len = 4000

设定GROUP_CONCAT()函数返回值的最大长度，有效取值范围为4至“2^CPU字长”次方。作用范围为全局或会话级别，用于配置文件，属动态变量。于group by合并使用才会有效果就是将多个列合并的最大长度。

默认为1024。

#####

skip-slave-start

skip-slave-start 表示从mysql服务器启动时不启动同步线程，这就要在启动从服务器之后，手工启动同步线程，在mysql> 提示符下面运行“start slave”就可以

默认为空

skip-name-resolve

大家都知道，当mysql的client连过来的时候，服务器会主动去查client的域名，当你设置了正确的DNS服务器，而且此时DNS服务器无异常，那么你客户端连接mysql服务器就很快，如果此时你的DNS服务器有异常，那么连接mysql服务器就比较慢

默认为off

max_user_connections = 20000

某个用户的最大连接数

默认为0表示随便连只要服务器不挂

max_connections = 20000

整体的最大连接数

默认为151

read_only = 0

1. 只能对数据库进行读操作。

2. 对于拥有super权限的用户，可以ignore这个选项。

默认为off

log-slave-updates = 1

表示如果一个MASTER 挂掉的话，另外一个马上接管。

默认为空

log-slave-updates 告诉Slave在二进制记录中记录从主数据库同步到的更新数据。如果只启动log-bin 而没有启动log-slave-updates则Slave只记录针对自己数据库操作的更新。

默认为空

bulk_insert_buffer_size = 32M

和key_buffer_size一样，这个参数同样也仅作用于使用 MyISAM存储引擎，用来缓存批量插入数据的时候临时缓存写入数据。当我们使用如下几种数据写入语句的时候，会使用这个内存区域来缓存批量结构的数据以帮助批量写入数据文件：

insert … select …

insert … values (…) ,(…),(…)…

load data infile… into… (非空表)

默认8M

innodb_data_home_dir = /data/mysql

此参数指定创建InnoDB表空间的路径的公共部分,默认情况下，这是MySQL的默认数据，由MySQL参数datadir指定

默认要看参数datadir指定

#innodb_data_file_path = ibdata1:128M:autoextend

指定InnoDB的各个数据文件及其大小，文件多于一个时彼此间用分号隔开。数据文件路径可以为相对路径，其相对于innodb_data_home_dir变量所指向的目录；而文件大小的表示可以以K(KB)、M(MB)、G(GB)为单位，但这些文件的大小之和至少要达到10MB。在没有显式设定innodb_data_file_path变量的情况下，MySQL服务器会在数据目录中自动创建一个可自动增长、初始大小为10MB的名为ibdata1的数据文件。单个数据文件的大小上限取决于操作系统，这意味着可以使用操作系统所支持的最大单个文件大小以为其数据文件的体积上限。InnoDB还支持使用裸设备作为数据文件。作用范围为全局，可用于选项文件，属非动态变量。

存放数据文件内容

默认 ibdata1:12M:autoextend

innodb_log_group_home_dir = /data/mysql

此参数确定日志文件组中的文件的位置，日志组中文件的个数由innodb_log_files_in_group确定，此位置设置默认为MySQL的datadir

默认为MySQL的datadir指定

innodb_file_per_table = 1

可以修改InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间

设定InnoDB表是否使用每表表空间数据文件(以.ibd结尾)分别存储每个表的数据和索引。如果使用了每表表空间数据文件，其将不再使用系统表空间(即共享表空间)。InnoDB表的某些特性，如压缩表等仅对每表表空间生效。作用范围为全局，可用于选项文件，属动态变量。

默认为1

innodb_buffer_pool_size = 64G

这个参数主要作用是缓存innodb表的索引，数据，插入数据时的缓冲

专用mysql服务器设置的大小：操作系统内存的70%-80%最佳。

默认为128M

innodb_log_file_size = 128M

如果对 Innodb 数据表有大量的写入操作，那么选择合适的 innodb_log_file_size 值对提升MySQL性能很重要。然而设置太大了，就会增加恢复的时间，因此在MySQL崩溃或者突然断电等情况会令MySQL服务器花很长时间来恢复。通常依据服务器的大小(server size)设置为64M到512M。

innodb_log_buffer_size = 32M

这是 InnoDB 存储引擎的事务日志所使用的缓冲区。类似于 Binlog Buffer，InnoDB 在写事务日志的时候，为了提高性能，也是先将信息写入 Innofb Log Buffer 中，当满足 innodb_flush_log_trx_commit 参数所设置的相应条件(或者日志缓冲区写满)之后，才会将日志写到文件(或者同步到磁盘)中。

可以通过 innodb_log_buffer_size 参数设置其可以使用的最大内存空间。

默认8M

innodb_flush_log_at_trx_commit = 0

抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统挂了时才可能丢数据。

默认为1

innodb_flush_method = O_DIRECT

O_DSYNC|O_DIRECT

设置innodb_flush_method变量的值为O_DIRECT时，InnoDB使用O_DIRECT标志打开数据文件，而使用fsync()刷写数据和日志文件。O_DIRECT标志会导致操作系统既不缓存数据，也不预读数据，它完全禁止了操作系统的缓存并且使所有的读写动作直接至存储设备，避免了双缓冲。然而，其不能禁止硬件层面(如RAID卡)的缓存和预读功能，而且启用硬件层面的缓存和预读功能也是保证InnoDB使用了O_DIRECT标志时仍能保持良好性能的惟一途径。

O_DSYNC会产生双缓存

默认为空

server-id = 281653306

默认为空

log-bin = mysql-bin

默认为空

relay-log = relay-bin

默认为空

log-error = error.log

默认为空

slow-query-log=1

默认为空

slow-query-log-file = slow-queries.log

启动慢查询日志和慢查询日志记录位置

默认为空

[mysqld]

datadir=/data/mysql

tmpdir=/data/mysqltmp

默认为/tmp

plugin-load = thread_pool.so

# General server settings

# This setting ensures that temp tables are fast by being stored in memory. Add ENGINE = INNODB to create statements if a temp table

is too big. See Task 186143 for pros/cons.

#default_tmp_storage_engine = MEMORY

默认为innodb

InnoDB写入耗时大概是MyIsam和Memory的5倍左右，它的行锁机制必然决定了写入时的更多性能开销，而它的强项在于多线程的并发处理，而本测试未能体现其优势。

　　三种数据库引擎在SELECT性能上差不多，Memory稍占优，同样高并发下的比较有待进一步测试。

表受限于mysqld变量max_heap_table_size(默认16M)

default_time_zone = SYSTEM

默认时区为系统

event_scheduler = off

可以让你设置你的mysql数据库再某段时间执行你想要的动作这与视图是不相同的。

create event test1

on schedule every 1 day starts

‘2007-09-01 12:00:00‘

on completion not preserve

do insert into yyy values(‘hhh‘,‘uuu‘);

使用这个功能之前必须确保event_scheduler已开启，可执行

SET GLOBAL event_scheduler = 1;

默认为off

group_concat_max_len = 4000

# Matthew said that 5000 connections should be enough for a setup like ours for now.

max_connections = 5000

# Max heap and temp table size should always match. This setting keeps these tables from getting too big and using up all of memory.

max_heap_table_size = 64M

tmp_table_size = 64M

# Turning off the query cache gives a performance speed gain (a somewhat minor one). Since we don‘t use it, no reason to have it on.

query_cache_type = off

包括off，on，demand

默认off

query_cache_size = 0

默认为1M

skip_name_resolve = true

默认为off

# InnoDB engine settings

innodb_buffer_pool_instances = 8

通过以上测试结果可知，在测试数据为200Warehouse时，数据大小大约占innodb_buffer_pool_size的1/3。此时，innodb_buffer_pool_instances为2时，MySQL的性能相对较高，而随着innodb_buffer_pool_instances的增大，MySQL的性能反而有所降低。但总体来看，对数据库的性能影响不大。设定将InnoDB的buffer pool分隔为多少个区域。对于有着数GB空间的buffer pool来说，将其分隔为多个区域可以降低不同的线程对缓存页面的读写操作时资源争用系数，进行增强其并发能力。在buffer pool中，读取或存入页面时所选择的区域是基于hash算法随机进行的。每个buffer pool管理自己的空闲列表、列表刷写、LRU以及其它跟buffer pool相关的数据结构，并通过各自的互斥锁进行保护。此变量仅在变量innodb_buffer_pool_size的值大于1G时才能发挥功用，缓冲池的整体空间将由各buffer pool实例分割使用。出于最佳效用的目的，建议配合使用innodb_buffer_pool_instances和innodb_buffer_pool_size变量以使得每个buffer pool实例的都至少有1G的空间。作用范围为全局，可用于选项文件，属非动态变量。

默认为8

# This value should be 50%-80% of total system memory for a dedicated server

innodb_buffer_pool_size = 100GB

# These two settings help warm up the buffer pool, keeping server speed up after restarts. Increases shutdown and startup time.

innodb_buffer_pool_dump_at_shutdown = on

默认为off

innodb_buffer_pool_load_at_startup = on

默认为off

# This setting makes changes to data only get committed to disk (fsync) once per second, reducing disk I/O

innodb_flush_log_at_trx_commit = 0

# This setting skips unnecessary buffering when writing

innodb_flush_method = O_DIRECT