mysql参数优化

修改配置文件--Linux
在 Linux 中，MySQL 对应的配置文件是 my.cnf , 我们在Linux终端输入如下命令 ：
mysql --help | grep my.cnf
可以查看 MySQL 所使用的 my.cnf 列表（优先级列表，先找到的配置文件生效），MySQL 会逐个目录寻找这个文件，直到找到为止！

skip-name-resolve
禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求。
back_log = 600
back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。如果期望在一个短时间内有很多连接，你需要增加它。也就是说，如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。
另外，这值（back_log）限于您的操作系统对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制（可以检查你的OS文档找出这个变量的最大值），试图设定back_log高于你的操作系统的限制将是无效的。

max_connections
SHOW VARIABLES LIKE 'max_connections'；显示设置的允许的最大连接数。
SHOW GLOBAL STATUS LIKE 'Max_used_connections'; 实际的连接数。
open_files_limit = 65535
MySQL打开的文件描述符限制，默认最小1024;
当open_files_limit没有被配置的时候，比较max_connections*5和ulimit -n的值，哪个大用哪个;
当open_file_limit被配置的时候，比较open_files_limit和max_connections*5的值，哪个大用哪个。
table_open_cache
Open_tables:当前打开表的缓存数；
Opened_tables:曾经打开表的缓存数，会一直累加；
show variables like 'table_cache%';
执行flush tables后，open_tables会清0，opened_tables则不会。因为flush_tables后，mysql会关闭打开的缓存表。
如果opened_tables每秒中打开的比较多，一般情况下，说明table_cahce太小了，但也存在另外一种情况：table cahce没有全部使用，临时表的  打开，也会导致opened_tables的增加。如果你发现open_tables等于table_open_cache，并且opened_tables在不断增长，那么你就需要增加table_open_cache的值了(上述状态值可甀SHOW STATUS LIKE ‘Open%tables’获得)。注意，不能盲目地把table_open_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。

max_allowed_packet
set global max_allowed_packet = 100*1024*1024；动态设置
[mysqld] 段下添加配置项 ：max_allowed_packet=100M

binlog_cache_size = 1M
一个事务，在没有提交的时候，产生的日志，记录到Cache中；等到事务提交需要提交的时候，则把日志持久化到磁盘。默认binlog_cache_size大小32K

max_heap_table_size = 8M
定义了用户可以创建的内存表(memory table)的大小。这个值用来计算内存表的最大行数值。这个变量支持动态改变SET @max_heap_table_size=100M，但是对于已经存在的内存表就没有什么用了，除非这个表被重新创建(create table)或者修改(alter table)或者truncate table。服务重启也会设置已经存在的内存表为全局max_heap_table_size的值。

tmp_table_size
它规定了内部内存临时表的最大值，每个线程都要分配。（实际起限制作用的是tmp_table_size和max_heap_table_size的较小值。）如果内存临时表超出了限制，mysql就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下（ show variables like "tmpdir";）。优化查询语句的时候，要避免使用临时表，如果实在避免不了的话，要保证这些临时表是存在内存中的。如果需要的话并且你有很多group by语句，并且你有很多内存，增大tmp_table_size(和max_heap_table_size)的值。
你可以比较内部基于磁盘的临时表的总数和创建在内存中的临时表的总数（Created_tmp_disk_tables和Created_tmp_tables），一般的比例关系是：Created_tmp_disk_tables/Created_tmp_tables<5%。

key_buffer_size
如果仅使用MyISAM存储引擎,设置 key_buffer_size 为可用内存的20%,(再加上设置 innodb_buffer_pool_size = 0 )；
SHOW GLOBAL STATUS LIKE 'Key%'; 执行后计算 Key_read_requests / Key_reads(一共有Key_read_requests个索引请求，一共发生了Key_reads次物理IO) 的值, 如果比值较大(比如大于10), 那么 key_buffer 就足够了.

read_buffer_size = 8M
MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能

read_rnd_buffer_size = 8M
MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，
MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大

sort_buffer_size = 8M
MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序字段。如果不能，可以尝试增加sort_buffer_size变量的大小。

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

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

query_cache_size = 8M
MYSQL的查询缓存用于缓存select查询结果，并在下次接收到同样的查询请求时，不再执行实际查询处理而直接返回结果，有这样的查询缓存能提高查询的速度，使查询性能得到优化，前提条件是你有大量的相同或相似的查询，而很少改变表里的数据，否则没有必要使用此功能。可以通过Qcache_lowmem_prunes变量的值来检查是否当前的值满足你目前系统的负载。注意：如果你查询的表更新比较频繁，而且很少有相同的查询，最好不要使用查询缓存。
show status like '%Qcache%';
Qcache_free_blocks:表示查询缓存中目前还有多少剩余的blocks，如果该值显示较大，则说明查询缓存中的内存碎片过多了，可能在一定的时间进行整理。
Qcache_free_memory:查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多了，还是不够用，DBA可以根据实际情况做出调整。
Qcache_hits:表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。
Qcache_inserts: 表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数，次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。
Qcache_lowmem_prunes:该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适当的调整缓存大小。
Qcache_not_cached: 表示因为query_cache_type的设置而没有被缓存的查询数量。
Qcache_queries_in_cache:当前缓存中缓存的查询数量。
Qcache_total_blocks:当前缓存的block数量

query_cache_limit = 2M
#指定单个查询能够使用的缓冲区大小，默认1M

innodb_file_per_table = 1

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

优点：
   每个表都有自已独立的表空间。
   每个表的数据和索引都会存在自已的表空间中。
   可以实现单表在不同的数据库中移动。
   空间可以回收（除drop table操作处，表空不能自已回收）
       1.Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。
       2.对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。
       3.对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。
缺点：
   单表增加过大，如超过100个G。
共享表空间在Insert操作上稍有优势。其它都没独立表空间表现好。当启用独立表空间时，请合理调整一 下：innodb_open_files 。InnoDB Hot Backup（冷备）的表空间cp不会面对很多无用的copy了。而且利用innodb hot backup及表空间的管理命令可以实现单现移动。
innodb_buffer_pool_size
InnoDB使用一个缓冲池来保存索引和原始数据, 不像MyISAM.
如果仅使用InnoDB存储引擎,设置 innodb_buffer_pool_size 为可用内存的 70%, (设置 key_buffer_size = 10M,很小但不是0.即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb使用后台线程处理数据页上的读写 I/O(输入输出)请求,根据你的 CPU 核数来更改,默认是4
注:这两个参数不支持动态改变,需要把该参数加入到my.cnf里，修改完后重启MySQL服务,允许值的范围从 1-64

innodb_thread_concurrency = 0
默认设置为 0,表示不限制并发数，这里推荐设置为0，更好去发挥CPU多核处理能力，提高并发量

innodb_purge_threads = 1
InnoDB中的清除操作是一类定期回收无用数据的操作。在之前的几个版本中，清除操作是主线程的一部分，这意味着运行时它可能会堵塞其它的数据库操作。
从MySQL5.5.X版本开始，该操作运行于独立的线程中,并支持更多的并发数。用户可通过设置innodb_purge_threads配置参数来选择清除操作是否使用单独线程,默认情况下参数设置为0(不使用单独线程),设置为 1 时表示使用单独的清除线程。建议为1

innodb_flush_log_at_trx_commit
默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。设成2对于很多运用，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。设为0的话，mysqld进程崩溃的时候，就会丢失最后1秒的事务。设为2,只有在操作系统崩溃或者断电的时候才会丢失最后1秒的数据。InnoDB在做恢复的时候会忽略这个值。
设为1当然是最安全的，但性能页是最差的（相对其他两个参数而言，但不是不能接受）。
如果对数据一致性和完整性要求不高，完全可以设为2.
如果只最求性能，例如高并发写的日志服务器，设为0来获得更高性能

innodb_log_buffer_size = 2M
此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，但意外的故障将会丢失数据。MySQL开发人员建议设置为1－8M之间.
一个大的log buffer 让一个大的事务运行不需要写日志到磁盘在事务提交前,因此,如果你有事务比如update,insert或者delete 很多的记录, 让log buffer 足够大来节约磁盘I/O. 

innodb_log_file_size
此参数确定数据日志文件的大小，更大的设置可以提高性能，但也会增加恢复故障数据库所需的时间.

innodb_log_files_in_group = 3
为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3

innodb_max_dirty_pages_pct = 90
innodb主线程刷新缓存池中的数据，使脏数据比例小于90%

innodb_lock_wait_timeout = 120
InnoDB事务在被回滚之前可以等待一个锁定的超时秒数。InnoDB在它自己的锁定表中自动检测事务死锁并且回滚事务。InnoDB用LOCK TABLES语句注意到锁定设置。默认值是50秒
需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

interactive_timeout = 28800
服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。默认值：28800秒（8小时）

wait_timeout = 28800
服务器关闭非交互连接之前等待活动的秒数。在线程启动时，根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值，
取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义)。参数默认值：28800秒（8小时）
MySQL服务器所支持的最大连接数是有上限的，因为每个连接的建立都会消耗内存，因此我们希望客户端在连接到MySQL Server处理完相应的操作后，应该断开连接并释放占用的内存。如果你的MySQL Server有大量的闲置连接，他们不仅会白白消耗内存，而且如果连接一直在累加而不断开，最终肯定会达到MySQL Server的连接上限数，这会报'too many connections'的错误。对于wait_timeout的值设定，应该根据系统的运行情况来判断。在系统运行一段时间后，可以通过show processlist命令查看当前系统的连接状态，如果发现有大量的sleep状态的连接进程，则说明该参数设置的过大，可以进行适当的调整小些。要同时设置interactive_timeout和wait_timeout才会生效。