mysql_优化参数整理
1. 连接层
max_connections=1000 #*****最大并发连接数max_connect_errors=999999当某一客户端尝试连接MySQL服务器，但是累计失败（不管什么原因）设置的次数，则MySQL会无条件强制阻止此客户端连接。wait_timeout=600 #*****设置非交互连接（就是指那些连接池方式、非客户端方式连接的）的超时时间(单位秒，默认是28800，就是8小时)，超过这个时间，mysql服务器会主动切断那些已经连接的，但是状态是sleep的连接。interactive_wait_timeout=3600针对交互式连接。所谓的交互式连接，即在mysql_real_connect()函数中使用了CLIENT_INTERACTIVE选项。说得直白一点，通过mysql客户端连接数据库是交互式连接，通过jdbc连接数据库是非交互式连接。net_read_timeout = 120数据读取超时时间。在终止读之前，从一个连接获得数据而等待的时间秒数；当服务正在从客户端读取数据时，net_read_timeout控制何时超时。即客户端执行数据读取，等待多少秒仍未执行成功时自动断开连接。 net_write_timeout = 120数据库写超时时间。和net_read_timeout意义类似，在终止写之前，等待多少秒把block写到连接；当服务正在写数据到客户端时，net_write_timeout控制何时超时。max_allowed_packet= 32M #*****限制server接受的数据包大小,最大1G2.server层
**********************安全更新模式**************************sql_safe_updates =1 # *****在mysql中，如果在update和delete没有加上where条件，数据将会全部修改。开启安全模式后,当没有加where条件或where条件中列（column）没有索引可用,则拒绝修改*************************慢日志****************************slow_query_log =ON开启慢日志slow_query_log_file =/data/3307/slow.log # *****慢日志存储路径long_query_time =1 # *****运行时间为多长则判定为慢,实际生产中一般设置在0.01~0.1之间(单位秒)log_queries_not_using_indexes =ON # *****开启记录没有使用索引的语句log_throttle_queries_not_using_indexes = 10 # *****因为没有使用同样索引而导致慢的语句只记录10条***********************会话级别缓冲区****************************************不可设置过大,高并发时会占用很多内存******************sort_buffer_size = 1M系统中对数据进行排序的时候用到的Buffer,储存行指针join_buffer_size = 1M系统中对数据进行join的时候用到的Bufferread_buffer= 1MMySQL读入缓冲区大小。对表进行顺序扫描的请求所分配的一个读入缓冲区.read_rnd_buffer_size = 1M储存排序的数据,配合sort_buffer使用,MRR优化器算法就是在read_rnd_buffer中进行工作的.tmp_table_ = 16Mmax_heap_table_size = 16M两个参数共同影响了sql执行时生成临时数据表的大小max_execution_time = 10000用来控制select语句的最大执行时间，如果超过这个时间,就终止该查询。单位是毫秒，可以动态修改，分为session和global两种级别，如果设置为0的话，则证明不设限制。*************************锁等待****************************lock_wait_timeout = 60 # *****当其他的SQL语句已经持有了锁，当前SQL语句需要等待持有锁的SQL语句执行完毕释放锁之后才能执行。当申请的锁等待时间超过参数的设定值时，系统会报LOCK_WAIT_TIMEOUT的错误。*************************库表大小写敏感****************************lower_case_table_names =1 # ***** 默认0，大小写敏感。设置1，大小写不敏感。创建的表，数据库都是以小写形式存放在磁盘上，对于sql语句都是转换为小写对表和DB进行查找。设置2，创建的表和DB依据语句上格式存放，凡是查找都是转换为小写进行。*************************线程相关****************************thread_cache_size =64 每建立一个连接，都需要一个线程来与之匹配。thread_cache_size用来缓存空闲的线程，以至不被销毁，如果线程缓存中有空闲线程，这时候建立新连接，MYSQL就会很快的响应连接请求。show global 里关于线程缓存有如下几个状态变量，这里作简单解释一下。Threads_cached :代表当前此时此刻线程缓存中有多少空闲线程。Threads_connected :代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。Threads_created :代表从最近一次服务启动，已创建线程的数量。Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态，这里相对应的线程也是sleep状态。最好将thread_cache_size设置成与threads_connected一样。不过很少有情况将threads_cache_size设置成比200还大的数。log_timestamps =SYSTEM # *****控制 error log、slow_log、genera log，等等记录日志的显示时间参数可以被设置的值有：UTC 和 SYSTEM，默认使用 UTC,这样会使得日志中记录的时间比中国这边的慢了8个小时，导致查看日志不方便。它还支持动态设置，不过建议大家在配置文件中就写上，以免重启之后造成不必要的麻烦。init_connect ="set names utf8" # *****event_scheduler =OFFmysql中的定时任务secure-file-priv =/tmp # *****设置文件导出路径binlog_expire_logs_seconds =2592000 # *****binlog失效日期参数为 binlog_expire_logs_seconds，单位秒,默认30天过期。sync_binlog =1 # *****sync_binlog=0，当事物提交之后，mysql不做fsync之类的刷新binlog_cache中的信息到磁盘，而让filesystem自行决定什么时候来同步，或者cache满了之后再同步。sync_binlog=n，每进行n次事物提交后，mysql将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。为了确保安全性，我们可以将sync_binlog设置为1，为了保证最价性能，我们可以将sync_binlog设置为0，log-bin =/data/3307/mysql-bin二进制日志路径及文件名前缀log-bin-index =/data/3307/mysql-bin.index二进制日志索引文件max_binlog_size =500M二进制日志达到设定值时滚动生成新日志,flush logs可以手动滚动binlog_format =ROW二进制日志的格式STATEMENT模式（SBR）语句模式每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)ROW模式（RBR）不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。③ MIXED模式（MBR）以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。3.存储引擎层
transaction-isolation ="READ-COMMITTED" # *****隔离级别设置,隔离效果依次提高,并发性能依次下降read-uncommitted：读未提交，会出现脏读；read-committed：读提交，杜绝脏读，但不可重复读；repeatable-read：可重复读，不允许脏读、不可重复读，但会出现幻读；serializable：串行化，以上都不会出现。innodb_data_home_dir =/xxx共享表空间路径innodb_log_group_home_dir =/xxx日志组的路径,默认在数据目录下innodb_log_file_size =2048Mredo日志的大小innodb_log_files_in_group =3redo日志的数量innodb_flush_log_at_trx_commit =1 # *****双1中的第二个10：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。innodb_flush_method =O_DIRECT # *****fdatasync模式(默认)：写数据时，write这一步将数据文件和redo log 写入到操作系统缓冲OS buffer中就会返回完成，真正完成是flush操作，buffer pool 和log buffer把文件和日志交给OS buffer去flush更新到磁盘。O_DSYNC模式：写日志操作是在write这步完成，而数据文件的写入是在flush这步通过fsync完成O_DIRECT模式：数据文件的写入操作是直接从mysql innodb buffer到磁盘的，并不用通过操作系统的缓冲，而真正的完成也是在flush这步,日志还是要经过OS缓冲
innodb_io_capacity =1000 # *****控制当刷新脏数据时MySQL每秒执行的IO量,参数默认是200，可以动态调整.可以设置为设备能达到的最大IOPS的50%innodb_io_capacity_max =4000 在压力下，控制当刷新脏数据时MySQL每秒执行的IO量,可以设置为设备能达到的最大IOPSinnodb_buffer_pool_size =64G # *****InnoDB缓冲池,用于多个目的：* 数据缓存 – 这绝对是它的最重要的目的* 索引缓存 – 这使用是的同一个缓冲池* 缓冲 – 更改的数据(通常称为脏数据)在被刷新到硬盘之前先存放到缓冲* 存储内部结构 – 一些结构如自适应哈希索引或者行锁也都存储在InnoDB缓冲池如果是单纯的数据库服务器,可以将其设置为物理内存的80%以下.innodb_buffer_pool_instances =4 # *****可以开启多个内存缓冲池，把需要缓冲的数据hash到不同的缓冲池中.将 buffer pool 分成几个区，每个区用独立的锁保护，这样就减少了访问 buffer pool 时需要上锁的粒度，以提高性能。innodb_log_buffer_size =64M # *****mysql事务日志文件（ib_logfile0）的大小；设置的太小：当一个日志文件写满后，innodb会自动切换到另外一个日志文件，而且会触发数据库的检查点（Checkpoint），这会导致innodb缓存脏页的小批量刷新，会明显降低innodb的性能。由于日志切换更频繁，也就直接导致更多的BUFFER FLUSH，由于日志切换的时候是不能BUFFER FLUSH的， BUFFER写不下去，导致没有多余的buffer 写redo， 那么整个MYSQL就HANG住，还有一种情况是如果有一个大的事务，把所有的日志文件写满了，还没有写完，这样就会导致日志不能切换（因为实例恢复还需要，不能被循环复写）这样mysql就hang住了。可以根据文件修改时间来判断日志文件的旋转频率，旋转频率太频繁，说明日志文件太小了。设置的太大：设置很大以后减少了checkpoint，并且由于redo log是顺序I/O，大大提高了I/O性能。但是如果数据库意外出现了问题，比如意外宕机，那么需要重放日志并且恢复已经提交的事务（也就是实例恢复中的前滚, 利用redo重演变化来恢复buffer cache中的数据），如果日志很大，那么将会导致恢复时间很长。甚至到我们不能接受的程度。innodb_max_dirty_pages_pct =85 # *****控制buffer pool中脏页的百分比，当脏页数量占比超过这个参数设置的值时，InnoDB会启动刷脏页的操作。默认值为 75innodb_lock_wait_timeout =10 # *****事务锁等待超时时间,单位秒innodb_open_files =63000 # *****指定InnoDB一次可以保持打开的.ibd文件的最大数目。最小值是10。默认值300。innodb_page_cleaners =4页面清除程序线程数,如果超过缓冲池实例数，则innodb_page_cleaners将自动设置为与innodb_buffer_pool_instances相同的值innodb_sort_buffer_size =64M在创建InnoDB索引时用于指定对数据排序的排序缓冲区的大小。利用这块内存把数据读进来进行内部排序然后写入磁盘。这个参数只会在创建索引的过程中被使用，不会用在后面的维护操作；在索引创建完毕后innodb_sort_buffer会被释放。innodb_print_all_deadlocks =1 #记录死锁日志到error.loginnodb_rollback_on_timeout =ON事务超时后回滚整个事务还是上一条语句innodb_deadlock_detect =ON死锁检测4. 主从复制相关
*************************relay_log******************************relay_log =/opt/log/mysql/blog/relay定义relay_log的位置和名称，如果值为空，则默认位置在数据文件的目录relay_log_index =/opt/log/mysql/blog/relay.index定义relay_log的位置和名称max_relay_log_size =500M标记relay log 允许的最大值，如果该值为0，则为默认值max_binlog_size(1G)relay_log_recovery =ON当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。默认情况下该功能是关闭的，将relay_log_recovery的值设置为 1时，可在slave从库上开启该功能，建议开启。**********************半同步**************************rpl_semi_sync_master_enabled =ON控制是否在主库上启用半同步复制,默认值是0（关闭）rpl_semi_sync_master_timeout =1000一个以毫秒为单位的值，用于控制主服务器等待来自从服务器的确认提交并恢复到异步复制的时间，超过这个值就是超时。 默认值是10000（10秒）。超时之后，就从半同步复制，返回到异步复制。rpl_semi_sync_master_trace_level =32日志打印参数，默认即可rpl_semi_sync_master_wait_for_slave_count =1必须等待至少多少个slave的ack信息，才可以提交rpl_semi_sync_master_wait_no_slave =ON在没有slave时，是否选择等待rpl_semi_sync_master_wait_point =AFTER_SYNC控制等待ack的逻辑处于整个事务提交过程的哪个阶段，目前支持两种模式AFTER_SYNC ##增强半同步AFTER_COMMIT ##普通半同步rpl_semi_sync_slave_enabled =ONslave半同步的开关rpl_semi_sync_slave_trace_level =32salve节点半同步日志相关binlog_group_commit_sync_delay =1表示binlog提交后等待延迟多少时间再同步到磁盘，默认0，单位微妙,不延迟。设置延迟可以让多个事务同一时刻提交，提高binlog组提交的并发数和效率，提高slave的吞吐量。binlog_group_commit_sync_no_delay_count =1000表示等待延迟提交的最大事务数，如果上面参数的时间没到，但事务数到了，则直接同步到磁盘。若binlog_group_commit_sync_delay没有开启，则该参数也不会开启。gtid_mode =ONenforce_gtid_consistency =ONskip-slave-start =1gtid开关,以上三个参数同时开启猜可以#read_only =ON#super_read_only =ON只读模式,从库参数,防止从库误写入log_slave_updates =ON1、从库只开启log-bin功能，不添加log-slave-updates参数，从库从主库复制的数据不会写入log-bin日志文件里。2、直接向从库写入数据时，是会写入log-bin日志的。3、开启log-slave-updates参数后，从库从主库复制的数据会写入log-bin日志文件里。这也是该参数的功能。server_id =2330602report_host =xxxx向主库报告从库的ipreport_port =3306向主库报告从库的端口slave_parallel_type =LOGICAL_CLOCKDATABASE（默认值，基于库的并行复制方式）LOGICAL_CLOCK（基于组提交的并行复制方式）slave_parallel_workers =4并发sql线程数量master_info_repository =TABLErelay_log_info_repository =TABLE多源复制从库需要开启的参数 赞 (0)