my.cnf配置指南

常用跟踪命令 explain

# back_log 是操作系统在监听队列中所能保持的连接数,

# 队列保存了在MySQL连接管理器线程处理之前的连接.

# 如果你有非常高的连接率并且出现”connection refused” 报错,

# 你就应该增加此处的值.

# 检查你的操作系统文档来获取这个变量的最大值.

# 如果将back_log设定到比你操作系统限制更高的值,将会没有效果

back_log = 50

max_connections = 100

这个参数实际起作用的最大值（实际最大可连接数）为16384，即该参数最大值不能超过16384，即使超过也以16384为准；

增加max_connections参数的值，不会占用太多系统资源。系统资源（CPU、内存）的占用主要取决于查询的密度、效率等；

该参数设置过小的最明显特征是出现”Too many connections”错误；

show global status like 'Max_used_connections';

MySQL服务器过去的最大连接数是245，没有达到服务器连接数上限256，应该没有出现1040错误，比较理想的设置是：

Max_used_connections / max_connections * 100% ≈ 85%

最大连接数占上限连接数的85%左右，如果发现比例在10%以下，MySQL服务器连接上线就设置得过高了

max_connect_errors = 10

table_cach

table_cache是一个非常重要的MySQL性能参数，它在5.1.3之后的版本中叫做table_open_cache。table_cache主要用于设置table高速缓存的数量。由于每个客户端连接都会至少访问一个表，因此此参数的值与max_connections有关。

缓存机制

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

在执行缓存操作之前，table_cache用于限制缓存表的最大数目：如果当前已经缓存的表未达到table_cache，则会将新表添加进来；若已经达到此值，MySQL将根据缓存表的最后查询时间、查询率等规则释放之前的缓存。

show global status like 'open%_tables';

来查看这两个参数的值。其中Open_tables是当前正在打开表的数量，Opened_tables是所有已经打开表的数量。

如果Open_tables的值已经接近table_cache的值，且Opened_tables还在不断变大，则说明mysql正在将缓存的表释放以容纳新的表，此时可能需要加大table_cache的值。对于大多数情况，

比较适合的值：

Open_tables / Opened_tables >= 0.85

Open_tables / table_cache <= 0.95

max_allowed_packet = 16M

MySQL根据配置文件会限制Server接受的数据包大小。有时候大的插入和更新会受 max_allowed_packet 参数限制，导致写入或者更新失败。

show VARIABLES like '%max_allowed_packet%';查看当前配置的大小 ，换为M /2014/1024

binlog_cache_size = 1M

0.前提：

服务器配置了 log-bin

1.含义：

为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存。

2.作用：

提高记录bin-log的效率

3.属性：

参数：global

默认值：32768 -- 即 32K

范围：4096 .. 4294967295

如果你经常使用大的,多声明的事务,你可以增加此值来获取更大的性能.

根据参数：

show global status like 'bin%';

上述语句我们可以得到当前 数据库binlog_cache_size的使用情况

Binlog_cache_disk_use表示因为我们binlog_cache_size设计的内存不足导致缓存二进制日志用到了临时文件的次数

Binlog_cache_use 表示 用binlog_cache_size缓存的次数

当对应的Binlog_cache_disk_use 值比较大的时候 我们可以考虑适当的调高 binlog_cache_size 对应的值

binlog_stmt_cache_size管理语句缓存的大小。 发生事务时非事务语句的缓存的大小

max_heap_table_size = 64M

独立的内存表所允许的最大容量.此选项为了防止意外创建一个超大的内存表导致永尽所有的内存资源.

它规定了内部内存临时表的最大值，每个线程都要分配。（实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。）如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下

show variables like "tmpdir";

sort_buffer_size = 8M

排序缓冲被用来处理类似ORDER BY以及GROUP BY队列所引起的排序

# 如果排序后的数据无法放入排序缓冲,

# 一个用来替代的基于磁盘的合并分类会被使用

1。 Sort_Buffer_Size 是一个connection级参数，在每个connection第一次需要使用这个buffer的时候，一次性分配设置的内存。

2。 Sort_Buffer_Size 并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。

测试语句：

分别设置Sort_Buffer_Size 为 250K ，512K， 3M ，然后运行以下语句，查看运行时间。

1. sql_no_cache 防止query cache起效。

2. limit 1 为了减少排序占执行时间的比重，更多的体现内存分配带来的影响

3. 语句explain的结果是 filesort ， 以确保使用sort_buffe

mysqlslap -uroot -h127.0.0.1 -q ' select sql_no_cache * from sbtest order by pad limit 1' -c 100 --create-schema=test -i 10

join_buffer_size = 8M

# 此缓冲被使用来优化全联合(full JOINs 不带索引的联合).

# 类似的联合在极大多数情况下有非常糟糕的性能表现,

# 但是将此值设大能够减轻性能影响.

# 通过 “Select_full_join” 状态变量查看全联合的数量

最好是添加适当的索引而不是纯粹加大join_buffer_size

query_cache_size = 64M

# 查询缓冲常被用来缓冲 SELECT 的结果并且在下一次同样查询的时候不再执行直接返回结果.

# 打开查询缓冲可以极大的提高服务器速度, 如果你有大量的相同的查询并且很少修改表.

# 查看 “Qcache_lowmem_prunes” 状态变量来检查是否当前值对于你的负载来说是否足够高.

# 注意: 在你表经常变化的情况下或者如果你的查询原文每次都不同,

# 查询缓冲也许引起性能下降而不是性能提升.

SHOW STATUS LIKE 'qcache%';

SHOW VARIABLES LIKE '%query_cache%';

query_cache_limit = 2M

# 只有小于此设定值的结果才会被缓冲

# 此设置用来保护查询缓冲,防止一个极大的结果集将其他所有的查询结果都覆盖.

log_warnings

# 将警告打印输出到错误log文件. 如果你对于MySQL有任何问题

# 你应该打开警告log并且仔细审查错误日志,查出可能的原因.

log_error=/PATH/TO/ERROR_LOG_FILENAME

定义错误日志文件。作用范围为全局或会话级别，可用于配置文件，属非动态变量。

log_warnings=#

设定是否将警告信息记录进错误日志。默认设定为1，表示启用；可以将其设置为0以禁用；而其值为大于1的数值时表示将新发起连接时产生的“失败的连接”和“拒绝访问”类的错误信息也记录进错误日志。

一般查询日志：记录了所有与查询语句相关的信息，由于查询语句使用的比较频繁，因此，一般我们将此功能关闭。

slow_query_log={ON|OFF}

是否记录慢查询日志。

long_query_time=#

慢查询时间值 秒

slow_query_log_file=/PATH/TO/SOMEFILE

设定慢查询日志文件的名称 路径

＝＝MyISAM 相关选项＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

key_buffer_size = 32M

# 关键词缓冲的大小, 一般用来缓冲MyISAM表的索引块.

# 不要将其设置大于你可用内存的30%,

# 因为一部分内存同样被OS用来缓冲行数据

# 甚至在你并不使用MyISAM 表的情况下, 你也需要仍旧设置起 8-64M 内存由于它同样会被内部临时磁盘表使用.

read_buffer_size = 2M

# 用来做MyISAM表全表扫描的缓冲大小.

# 当全表扫描需要时,在对应线程中分配.

read_rnd_buffer_size = 16M

# 当在排序之后,从一个已经排序好的序列中读取行时,行数据将从这个缓冲中读取来防止磁盘寻道.

# 如果你增高此值,可以提高很多ORDER BY的性能.

# 当需要时由每个线程分配

myisam_max_sort_file_size = 10G

# MySQL重建索引时所允许的最大临时文件的大小 (当 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE).

# 如果文件大小比此值更大,索引会通过键值缓冲创建(更慢)

myisam_repair_threads = 1

# 如果一个表拥有超过一个索引, MyISAM 可以通过并行排序使用超过一个线程去修复他们.

# 这对于拥有多个CPU以及大量内存情况的用户,是一个很好的选择.

＝＝INNODB 相关选项＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

innodb_buffer_pool_size = 2G

# InnoDB使用一个缓冲池来保存索引和原始数据, 不像 MyISAM.

# 这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少.

# 在一个独立使用的数据库服务器上,你可以设置这个变量到服务器物理内存大小的80%

# 不要设置过大,否则,由于物理内存的竞争可能导致操作系统的换页颠簸.

# 注意在32位系统上你每个进程可能被限制在 2-3.5G 用户层面内存限制,

# 所以不要设置的太高.

innodb_force_recovery=1

# 如果你发现InnoDB表空间损坏, 设置此值为一个非零值可能帮助你导出你的表.

# 从1开始并且增加此值知道你能够成功的导出表.

innodb_log_buffer_size = 8M

# 用来缓冲日志数据的缓冲区的大小.

# 当此值快满时, InnoDB将必须刷新数据到磁盘上.

# 由于基本上每秒都会刷新一次,所以没有必要将此值设置的太大(甚至对于长事务而言)

innodb_log_file_size = 256M

# 在日志组中每个日志文件的大小

# 你应该设置日志文件总合大小到你缓冲池大小的25%~100%

# 来避免在日志文件覆写上不必要的缓冲池刷新行为.

# 不论如何, 请注意一个大的日志文件大小会增加恢复进程所需要的时间.

innodb_lock_wait_timeout = 120

# 在被回滚前,一个InnoDB的事务应该等待一个锁被批准多久.

# InnoDB在其拥有的锁表中自动检测事务死锁并且回滚事务.

# 如果你使用 LOCK TABLES 指令, 或者在同样事务中使用除了InnoDB以外的其他事务安全的存储引擎

# 那么一个死锁可能发生而InnoDB无法注意到.

# 这种情况下这个timeout值对于解决这种问题就非常有帮助.