mysql参数： my.cnf

 

 基本配置

 

show variables like '%query_cache%';  

 thread_cache和table_cache详解 

 

query_cache_size 

query cache是一个众所周知的瓶颈，甚至在并发并不多时也如此。 最 好是一开始就停用，设置query_cache_size = 0，并利用其他方法加速查询：优化索引、增加拷贝分散负载或者启用额外的缓存（比如memcache或redis）。如果已经启用了query cache并且还没有发现任何问题，query cache可能有用。如果想停用它，那就得小心了。

当开启了 Query Cache 之后，尤其是当 query_cache_type 参数设置为 1（默认是OFF） 以后，MySQL 会对每个 SELECT 语句都进行 Query Cache 查找，查找操作虽然比较简单，但仍然也是要消耗一些 CPU 运算资源的。而由于 Query Cache 的失效机制的特性，可能由于表上的数据变化比较频繁，大量的 Query Cache 频繁的被失效，所以 Query Cache 的命中率就可能比较低下。所以有些场景下，Query Cache 不仅不能提高效率，反而可能造成负面影响。

默认值：

 

table_open_cache

MySQL每打开一个表，都会读入一些数据到table_open_cache缓存中，当MySQL在这个缓存中找不到相应信息时，才会去磁盘上读取。假定系统有200个并发连接，则需将此参数设置为200*N(N为每个连接所需的文件描述符数目)；当把table_open_cache设置为很大时，如果系统处理不了那么多文件描述符，那么就会出现客户端失效，连接不上。

 

thread_cache_size

thread_cache_size：Thread Cache 池中应该存放的连接线程数。当系统最初启动的时候，并不会马上就创建 thread_cache_size 所设置数目的连接线程存放在Thread Cache 池中，而是随着连接线程的创建及使用，慢慢的将用完的连接线程存入其中。当存放的连接线程达到 thread_cache_size 值之后，MySQL 就不会再续保存用完的连接线程了。如果我们的应用程序使用的短连接，Thread Cache 池的功效是最明显的。因为在短连接的数据库应用中，数据库连接的创建和销毁是非常频繁的，如果每次都需要让 MySQL 新建和销毁相应的连接线程，那么这个资源消耗实际上是非常大的，而当我们使用了 Thread Cache 之后，由于连接线程大部分都是在创建好了等待取用的状态，既不需要每次都重新创建，又不需要在使用完 之 后 销 毁 ， 所 以 可 以 节 省 下 大 量 的 系 统 资 源 。

所 以 在 短 连 接 的 应 用 系 统 中，thread_cache_size的值应该设置的相对大一些，不应该小于应用系统对数据库的实际并发请求数。

如果是查询的话，也要看是不是用了query_cache,query_cache增加cpu的消耗高
根据调查发现以上服务器线程缓存thread_cache_size没有进行设置，或者设置过小,这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能.通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。(?>表示要调整的值)   根据物理内存设置规则如下：

1G  ?> 8

2G  ?> 16

3G  ?> 32

>3G  ?> 64

 

查看thread_cache_size 设置： show variables like 'thread_cache_size';

mysql> show global status like 'Thread%'; 
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 10 |
| Threads_connected | 10    |
| Threads_created | 10    |
| Threads_running | 10    |
+-------------------+-------+
调整thread_cache_size后会发现Threads_created 数骤降。但数据库服务器其他资源使用情况并不一定会有明显的优化。具体还是使用的场景有关。

 

max_connections

 

max_connection值被设高了(例如1000或更高)之后一个主要缺陷是当服务器运行1000个或更高的活动事务时会变的没有响应。在应用程序里使用连接池或者在MySQL里使用进程池有助于解决这一问题。 

 

back_log

 

要求 mysql 能有的连接数量。当主要mysql线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间检查连接并且启动一个新线程。back_log指明在mysql暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，需要增加它，换句话说，该值对到来的tcp/ip连接的侦听队列的大小。

 

show variables like 'key_buffer_size';

设置MyISAM表引擎索引缓存的大小。若是32位平台，建议不超过2G；若是64位平台，建议不超过4G。

 

show variables like 'table_open_cache';

show global status like 'open%';

这个参数表示数据库用户打开表的缓存数量。每个连接进来，都会至少打开一个表缓存。与数据库的连接数有关。 如果状态变量Open_tables与Open_tables之间的比率过低，则代表Table Cache设置过小，经验值是80%左右。

 

show variables like 'read_buffer_size';

以顺序方式扫描表数据时候使用的Buffer在设置的时候尽量不要太高，可以尝试适当调大此参数看是否能改善全表扫描的性能。 这个参数只对MyISAM存储引擎使用

 

show variables like 'read_rnd_buffer_size';

进行随机扫描的时候使用的Buffer read_rnd_buffer_size适当调大，对提高ORDER BY操作的性能有一定的效果。 这个参数只对MyISAM存储引擎使用

 

innodb配置

innodb_file_per_table

这项设置告知InnoDB是否需要将所有表的数据和索引存放在共享表空间里（innodb_file_per_table = OFF）或者为每张表的数据单独放在一个.ibd文件（innodb_file_per_table = ON）。每张表一个文件允许你在drop、truncate或者rebuild表时回收磁盘空间。这对于一些高级特性也是有必要的，比如数据压缩。但是它不会带来任何性能收益。MySQL 5.6中，这个属性默认值是ON。

 

innodb_flush_method

这项配置决定了数据和日志写入硬盘的方式。一般来说，如果你有硬件RAID控制器，并且其独立缓存采用write-back机制，并有着电池断电保护，那么应该设置配置为O_DIRECT；否则，大多数情况下应将其设为fdatasync（默认值）。sysbench是一个可以帮助你决定这个选项的好工具。

 

show variables like 'Innodb_buffer_pool_size';

这个参数定义了InnoDB存储引擎的表数据和索引数据的最大内存缓冲区大小。 通过show status like 'Innodb_buffer_pool%'; 查看Innodb buffer pool的read命中率、是否有空闲空间。这个值越大越好，这能保证你在大多数的读取操作时使用的是内存而不是硬盘。典型的值是5-6GB(8GB内存)，20-25GB(32GB内存)，100-120GB(128GB内存)。

 

show variables like 'Innodb_log_buffer_size';

这个参数用来设置Innodb的LOG Buffer大小的，系统默认值为8MB。log buffer的主要作用就是缓冲log,提高IO的性能。建议8-16M，有高TPS（比如大于6k）的可以提高到32M，系统tps越高设置可以设置的越大

这项配置决定了为尚未执行的事务分配的缓存。但是如果事务中包含有二进制大对象或者大文本字段的话，看Innodb_log_waits状态变量，如果它不是0，增加innodb_log_buffer_size。

 

innodb的日志文件也是保存在磁盘中的，那写的速度也是相对慢的，innodb中使用了日志缓存来提高写的速度。innodb会将所有的日志首先写到日志缓存中，然后再通过后台的一个线程将这这些缓存刷新到磁盘的日志文件中。

 

show variables like 'Innodb_flush_log_at_trx_commit';

redo 的刷盘策略，控制什么时候才将缓存刷新到日志文件

0:Log Thread 每隔1s会将log buffer中的数据写入到文件，同时还会通过文件系统进行文件同步的flush操作，保证数据确实已经写入到磁盘上面的物理文件

1：每次事务的结束都会触发Log Thread将log buffer中的数据写入文件并通知文件系统同步文件

2：Log Thread会在每次事务的结束时将数据写入日志，这里的写入只是调用了文件系统的写入操作。文件系统何时会将缓存中的这个数据同步到物理磁盘文件Log Thread就完全不知道。

 默认值为1，表示InnoDB完全支持ACID特性。当关注点是数据安全的时候这个值是最合适的，比如在一个主节点上。但是对于磁盘（读写）速度较慢的系统，它会带来很巨大的开销，因为每次将改变flush到redo日志都需要额外的fsyncs。如果值为0速度就更快了，但在系统崩溃时可能丢失一些数据, 所以一遍只适用于备份节点。

 

innodb_flush_log_at_timeout 

< 5.6.6： 每隔一秒将redo log buffer中的数据刷新到磁盘

>= 5.6.6：每隔innodb_flush_log_at_timeout秒将数据刷新到磁盘中去

 

show variables like 'Innodb_log_file_size';

该参数含义是一个日志组中每个日志文件的大小，在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高，但是带来的副作用是，当系统灾难时恢复时间会加大（扫描恢复的时间越长）

这个值定义了日志文件的大小，innodb日志文件的作用是用来保存redo日志。一个事务对于数据或索引的修改往往对应到表空间中的随机的位置，因此当刷新这些修改到磁盘中就会引起随机的I/O，而随机的I/O往往比顺序的I/O更加昂贵的开销，因为随机的I/O需要更多的开销来定位到指定的位置。

 redo日志被用于确保写操作快速而可靠并且在崩溃时恢复。从MySQL 5.5之后，崩溃恢复的性能的到了很大提升，可以同时拥有较高的写入性能和崩溃恢复性能。在MySQL 5.6里可以被提高到4GB以上。如果应用程序需要频繁的写入数据，可以一开始就把它这是成4G。

 

sync_binlog

binlog 的刷盘策略,在做MySQL复制的时候二进制日志最重要的选项

=0：由os系统的刷新机制来控制，刷新数据到磁盘的频率

=1：每次commit刷新到磁盘

>1：每N次提交刷新到磁盘

 

实例：sync_binlog引起的一个IO问题 

 

其他配置

 

Mysql数据库写入数据速度优化

1）innodb_flush_log_at_trx_commit 默认值为1；设置为0，可以提高写入速度。 
值为0：提升写入速度，但是安全方面较差，mysql服务器宕机可能会造成数据丢失。

值为1：每一次事务提交或者事务外的指令都需要把日志写入硬盘，此过程消耗时间较长；

值为2：是每次操作不写入硬盘，而是写入系统缓存，日志仍然会每秒刷新硬盘；
2）innodb_autoextend_increment默认值为8M，调整为128M ；
此配置项作用主要是当tablespace 空间已经满了后，需要MySQL系统需要自动扩展多少空间，每次tablespace 扩展都会让各个SQL 处于等待状态。增加自动扩展Size可以减少tablespace自动扩展次数。 
3）innodb_log_buffer_size默认值为1M，调整为16M ；
此配置项作用设定innodb 数据库引擎写日志缓存区；将此缓存段增大可以减少数据库写数据文件次数。 
4）innodb_log_file_size默认值为 8M，调整为128M ；
此配置项作用设定innodb 数据库引擎UNDO日志的大小；从而减少数据库checkpoint操作。 

5）bulk_insert_buffer_size默认值为8M，调整为100M；

作用于使用 MyISAM存储引擎，用来缓存批量插入数据的时候临时缓存写入数据。

innodb_flush_log_at_trx_commit、innodb_log_buffer_size和 innodb_log_file_size 需要谨慎调整；因为涉及MySQL本身的容灾处理。