mysql学习：配置参数优化说明

hao0089

　　–http://blog.csdn.net/nightelve/article/details/17393631
　　经典面试题：http://blog.csdn.net/u013252072/article/details/52912385
　　####:for innodb
　　default_storage_engine=innodb
　　default_tmp_storage_engine=innodb
　　innodb_data_file_path=ibdata1:2048M:autoextend
　　innodb_temp_data_file_path=ibtmp1:12M:autoextend
　　innodb_log_group_home_dir=/mydata
　　innodb_log_files_in_group=3
　　innodb_log_file_size=500M
　　innodb_undo_directory = /mydata
　　innodb_undo_tablespaces = 3
　　innodb_undo_logs = 1
　　innodb_file_format=Barracuda
　　innodb_file_per_table=on
　　innodb_page_size=16k
　　innodb_thread_concurrency=0
　　innodb_read_io_threads=24
　　innodb_write_io_threads=40
　　innodb_purge_threads=4
　　innodb_print_all_deadlocks=on
　　innodb_deadlock_detect=on
　　innodb_lock_wait_timeout=50
　　innodb_spin_wait_delay=6
　　innodb_autoinc_lock_mode=2
　　innodb_stats_persistent=on
　　innodb_stats_persistent_sample_pages=20
　　innodb_adaptive_hash_index=on
　　innodb_change_buffering=all
　　innodb_change_buffer_max_size=25
　　innodb_flush_neighbors=1
　　innodb_flush_method=O_DIRECT
　　innodb_doublewrite=on
　　innodb_log_buffer_size=16M
　　innodb_flush_log_at_timeout=1
　　innodb_flush_log_at_trx_commit=1
　　innodb_buffer_pool_size=60G
　　autocommit=1
　　根据实际情况把具体值改动一下。
　　-
　　innodb_additional_mem_pool_size = 16M
　　参数默认值：1048576（1M）
　　这个参数用来设置 InnoDB 存储的数据目录信息和其它内部数据结构的内存池大小，类似于Oracle的library cache。这不是一个强制参数，可以被突破。
　　innodb_buffer_pool_size = 2048M
　　参数默认值：8388608（8M）
　　这对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默认的 key_buffer_size 设置下运行的可以，然而Innodb在默认的
　　innodb_buffer_pool_size 设置下却跟蜗牛似的。由于Innodb把数据和索引都缓存起来，无需留给操作系统太多的内存，因此如果只需要用Innodb的
　　话则可以设置它高达 70-80% 的可用内存。
　　这是你安装完InnoDB后第一个应该设置的选项。缓冲池是数据和索引缓存的地方：这个值越大越好，这能保证你在大多数的读取操作时使用的是内存而
　　不是硬盘。典型的值是5-6GB(8GB内存)，20-25GB(32GB内存)，100-120GB(128GB内存)。
　　innodb_data_file_path = ibdata1:1024M:autoextend
　　参数默认值：ibdata1:10M:autoextend
　　表空间文件 重要数据
　　innodb_file_io_threads = 4
　　参数默认值：4
　　文件IO的线程数，一般为 4，但是在 Windows 下，可以设置得较大。
　　innodb_thread_concurrency = 8
　　参数默认值：8
　　服务器有几个CPU就设置为几，建议用默认设置，一般为8.
　　innodb_flush_log_at_trx_commit = 0
　　参数默认值：1
　　当 innodb_flush_log_at_trx_commit 取值为 0 的时候，log buffer 会 每秒写入到日志文件并刷写（flush）到磁盘。但每次事务提交不会有任何影响，
　　也就是 log buffer 的刷写操作和事务提交操作没有关系。在这种情况下，MySQL性能最好，但如果 mysqld 进程崩溃，通常会导致最后 1s 的日志丢失。
　　当取值为 1 时，每次事务提交时，log buffer 会被写入到日志文件并刷写到磁盘。这也是默认值。这是最安全的配置，但由于每次事务都需要进行磁盘I/O，所以也最慢。
　　当取值为 2 时，每次事务提交会写入日志文件，但并不会立即刷写到磁盘，日志文件会每秒刷写一次到磁盘。这时如果 mysqld 进程崩溃，由于日志已经
　　写入到系统缓存，所以并不会丢失数据；在操作系统崩溃的情况下，通常会导致最后 1s 的日志丢失。
　　innodb_log_buffer_size = 4M
　　参数默认值：1048576
　　这项配置决定了为尚未执行的事务分配的缓存。其默认值（1MB）一般来说已经够用了，但是如果你的事务中包含有二进制大对象或者大文本字段的话，这
　　点缓存很快就会被填满并触发额外的I/O操作。看看Innodb_log_waits状态变量，如果它不是0，增加innodb_log_buffer_size。
　　innodb_log_file_size = 128M
　　参数默认值：5242880
　　这是redo日志的大小。redo日志被用于确保写操作快速而可靠并且在崩溃时恢复。一直到MySQL 5.1，它都难于调整，因为一方面你想让它更大来提高性能，
　　另一方面你想让它更小来使得崩溃后更快恢复。幸运的是从MySQL 5.5之后，崩溃恢复的性能的到了很大提升，这样你就可以同时拥有较高的写入性能和崩
　　溃恢复性能了。一直到MySQL 5.5，redo日志的总尺寸被限定在4GB(默认可以有2个log文件)。这在MySQL 5.6里被提高。
　　一开始就把innodb_log_file_size设置成512M(这样有1GB的redo日志)会使你有充裕的写操作空间。如果你知道你的应用程序需要频繁的写入数据并且你使
　　用的时MySQL 5.6，你可以一开始就把它这是成4G。
　　innodb_log_files_in_group = 3
　　参数默认值：2
　　为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3M
　　innodb_lock_wait_timeout = 50
　　参数默认值：50
　　InnoDB 有其内置的死锁检测机制，能导致未完成的事务回滚。但是，如果结合InnoDB使用MyISAM的lock tables 语句或第三方事务引擎,则InnoDB无法识别
　　死锁。为消除这种可能性，可以将innodb_lock_wait_timeout设置为一个整数值，指示 MySQL在允许其他事务修改那些最终受事务回滚的数据之前要等待多长时间(秒数)
　　innodb_file_per_table = 1
　　参数默认值：0
　　这项设置告知InnoDB是否需要将所有表的数据和索引存放在共享表空间里（innodb_file_per_table = OFF） 或者为每张表的数据单独放在一个.ibd文件
　　（innodb_file_per_table = ON）。每张表一个文件允许你在drop、truncate或者rebuild表时回收磁盘空间。这对于一些高级特性也是有必要的，比如数
　　据压缩。但是它不会带来任何性能收益。你不想让每张表一个文件的主要场景是：有非常多的表（比如10k+）。
　　MySQL 5.6中，这个属性默认值是ON，因此大部分情况下你什么都不需要做。对于之前的版本你必须在加载数据之前将这个属性设置为ON，因为它只对新创建的表有影响。
　　sync_binlog = 0
　　参数默认值：0
　　MySQL 的二进制日志（binary log）同步到磁盘的频率。MySQL server 在 binary log 每写入 sync_binlog 次后，刷写到磁盘。
　　如果 autocommit 开启，每个语句都写一次 binary log，否则每次事务写一次。默认值是 0，不主动同步，而依赖操作系统本身不定期把文件内容 flush
　　到磁盘。设为 1 最安全，在每个语句或事务后同步一次 binary log，即使在崩溃时也最多丢失一个语句或事务的日志，但因此也最慢。
　　大多数情况下，对数据的一致性并没有很严格的要求，所以并不会把 sync_binlog 配置成 1. 为了追求高并发，提升性能，可以设置为 100 或直接用 0.
　　而和 innodb_flush_log_at_trx_commit 一样，对于支付服务这样的应用，还是比较推荐 sync_binlog = 1.
　　max_connections = 200
　　参数默认值：151
　　如果你经常看到‘Too many connections’错误，是因为max_connections的值太低了。这非常常见因为应用程序没有正确的关闭数据库连接，你需要比默
　　认的151连接数更大的值。max_connection值被设高了(例如1000或更高)之后一个主要缺陷是当服务器运行1000个或更高的活动事务时会变的没有响应。在
　　应用程序里使用连接池或者在MySQL里使用进程池有助于解决这一问题。
　　interactive_timeout：
　　参数默认值：28800秒（8小时）
　　参数含义：（交互式）服务器在客户端连接处于非活动状态时等待interactive_timeout秒后关闭连接。
　　wait_timeout:
　　参数默认值：28800秒（8小时）
　　参数含义：（非交互式）服务器在客户端连接处于非活动状态时等待wait_timeout秒后关闭连接。
　　MySQL服务器所支持的最大连接数是有上限的，因为每个连接的建立都会消耗内存，因此我们希望客户端在连接到MySQL Server处理完相应的操作后，应该断
　　开连接并释放占用的内存。如果你的MySQL Server有大量的闲置连接，他们不仅会白白消耗内存，而且如果连接一直在累加而不断开，最终肯定会达到MySQL
　　Server的连接上限数，这会报'too many connections'的错误。对于wait_timeout的值设定，应该根据系统的运行情况来判断。在系统运行一段时间后，可
　　以通过show processlist命令查看当前系统的连接状态，如果发现有大量的sleep状态的连接进程，则说明该参数设置的过大，可以进行适当的调整小些。
　　注：在配置文件中要同时设置interactive_timeout和wait_timeout才会生效。
　　wait_timeout=100
　　interactive_timeout=100
　　back_log = 600
　　参数默认值：50
　　在MYSQL暂时停止响应新请求之前，短时间内的多少个请求可以被存在堆栈中。如果系统在短时间内有很多连接，则需要增大该参数的值，该参数值指定到来
　　的TCP/IP连接的监听队列的大小。默认值50。
　　max_allowed_packet = 32M
　　参数默认值：1048576
　　设置在网络传输中一次消息传输量的最大值。系统默认值 为1MB，最大值是1GB，必须设置1024的倍数。
　　join_buffer_size = 2M
　　参数默认值：
　　用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
　　thread_cache_size = 300
　　参数默认值：
　　如果我们在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁
　　（前提是缓存数未达上限）。Threads_created表示创建过的线程数，如果发现Threads_created值过大的话，表明 MySQL服务器一直在创建线程，这也是
　　比较耗资源，可以适当增加配置文件中thread_cache_size值，
　　thread_concurrency = 8
　　参数默认值：
　　设置thread_concurrency的值的正确与否, 对mysql的性能影响很大, 在多个cpu(或多核)的情况下，错误设置了thread_concurrency的值, 会导致mysql不
　　能充分利用多cpu(或多核), 出现同一时刻只能一个cpu(或核)在工作的情况。thread_concurrency应设为CPU核数的2倍. 比如有一个双核的CPU, 那么
　　thread_concurrency的应该为4; 2个双核的cpu, thread_concurrency的值应为8
　　#属重点优化参数
　　query_cache_size = 64M
　　参数默认值：0
　　对于使用MySQL的用户，对于这个变量大家一定不会陌生。前几年的MyISAM引擎优化中，这个参数也是一个重要的优化参数。但随着发展，这个参数也爆
　　露出来一些问题。机器的内存越来越大，人们也都习惯性的把以前有用的参数分配的值越来越大。这个参数加大后也引发了一系列问题。我们首先分析一
　　下 query_cache_size的工作原理：一个SELECT查询在DB中工作后，DB会把该语句缓存下来，当同样的一个SQL再次来到DB里调用时，DB在该表没发生变化
　　的情况下把结果从缓存中返回给Client。这里有一个关建点，就是DB在利用Query_cache工作时，要求该语句涉及的表在这段时间内没有发生变更。那如
　　果该表在发生变更时，Query_cache里的数据又怎么处理呢？首先要把Query_cache和该表相关的语句全部置为失效，然后在写入更新。那么如果Query_cache
　　非常大，该表的查询结构又比较多，查询语句失效也慢，一个更新或是Insert就会很慢，这样看到的就是Update或是Insert怎么这么慢了。所以在数据库写
　　入量或是更新量也比较大的系统，该参数不适合分配过大。而且在高并发，写入量大的系统，建议把该功能禁掉。
　　#重点优化参数（主库 增删改-MyISAM）
　　transaction_isolation = READ-COMMITTED
　　参数默认值：
　　设定默认的事务隔离级别.可用的级别如下:
　　READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
　　1.READ UNCOMMITTED-读未提交2.READ COMMITTE-读已提交3.REPEATABLE READ -可重复读4.SERIALIZABLE -串行
　　key_buffer_size = 2048M
　　参数默认值：
　　#批定用于索引的缓冲区大小，增加它可以得到更好的索引处理性能，对于内存在4GB左右的服务器来说，该参数可设置为256MB或384MB。
　　下面是常规
　　expire_logs_days = 30
　　参数默认值：0
　　来指定超过多少天的二进制日志将被自动清除。
　　server-id = 1
　　open_files_limit    = 10240