MySQL之my.cnf配置文件优化

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200

#   [client] port = 3306 socket = /tmp/mysql.sock default-character-set = utf8 [mysql] no-auto-rehash                                            #仅允许使用键值的updates和deletes [mysqld] port = 3306                                                #msyql服务器端口号 basedir = /usr/local/mysql                                 #mysql安装目录 datadir = /usr/local/mysql/data                            #mysql数据存放目录 #datadir = /data/mysql/data                                #同上 socket = /usr/local/mysql/data/mysql.sock                  #sock文件   #字符集与校对规则 character-set-server = utf8                                #默认字符集 collation-server = utf8_general_ci                         #设置校对规则   external-locking = FALSE                                   #避免外部锁定(减少出错几率，增加稳定性) skip-name-resolv                                           #禁止外部连接进行DNS解析 skip-slave-start                                           # 复制进程就不会随着数据库的启动而启动 http://blog.csdn.net/aeolus_pu/article/details/9419965  #master库binlog参数相关 server-id = 1                                              #主从复制时，ID不能相同 #binlog_format = mixed                                     #二进制日志格式（mixed、row、statement） binlog-cache-size = 32M                                    #设置二进制日志缓存大小 sync-binlog = 1                                            #每隔N秒将缓存中的二进制日志记录写回硬盘 max_binlog_cache_size = 8M                                 #最大的二进制Cache日志缓冲尺寸 max_binlog_size = 1G                                       #单个二进制日志文件的最大值，默认1G，最大1G log-bin-index = /usr/local/mysql/data/mysql-bin.index      #binlog索引文件位置 log-bin = /usr/local/mysql/data/mysql-bin                  #binlog日志存放目录 expire_logs_days = 90                                      #二进制日志文件过期时间 #slave数据库binlog参数 server-id = 10                                             #各数据库id不能相同 log_slave_updates = 1                                      #级联也使用 relay-log = /usr/lcoal/mysql/data/relay-bin                #relady目录 relay-log-info-file = /usr/local/mysql/data/relay-log.info #info目录 slave-skip-errors = 1007,1008,1032,1062                    #跳过主从复制时的错误 read-only = 1                                              #从服务器只读,SQL线程不影响，具有super，root用户不限制 master-connect-retry = 60                                  #主从复制丢失，重连间隔时间，默认60s #replicate-ignore-db = mysql                               #忽略mysql库不同步 replicate-wild-do-table=testdb1.% replicate-wild-do-table=testdb2.% replicate-wild-do-table=testdb3.% #master半同步开启参数 rpl_semi_sync_master_enabled = ON rpl_semi_sync_master_timeout = 10000 #rpl_semi_sync_master_wait_no_slave = ON #rpl_semi_sync_master_trace_level = 32 #slave半同步开启参数 rpl_semi_sync_slave_enabled = ON #rpl_semi_sync_slave_trace_level = 32   back_log = 1000                                          #指出在MySQL暂时停止响应新请求之前，短时间内的多少个请求 open_files_limit = 1024                                  #打开文件的最大个数，如果出现too mantopen files之类的就需要调整该值了   #连接相关 max_connections = 2000                                   #指定MySQL允许的最大连接进程数，show global variables like '%connections%'; http://elf8848.iteye.com/blog/1847445  max_user_connections = 2000                              #单用户最大的连接数，max_user_connections < 实例 max_user_connections < max_connections max_connect_errors = 100000                              #默认为10，设置每个主机的连接请求异常中断的最大次数，超过后会blocked，连接成功后初始0，出现错误后需要flush hosts   max_allowed_packet = 8M                                  #服务器一次能处理的最大的查询包的值 wait_timeout = 360                                       #指定一个请求的最大连接时间 interactive_timeout = 360                                #连接保持活动的时间 #访问日志 #general_log = on #general_log_file = /usr/local/mysql/data/mysql_access.log   #错误日志 log_error = /data/mysql/data/mysql_error.log #慢查询相关参数 slow_query_log = on                                    #开启慢查询 log-queries-not-using-indexes                          #记录所有没有使用到索引的查询语句 long_query_time = 2                                    #指定多少秒未返回结果的查询属于慢查询 min_examined_row_limit = 5                             #记录那些由于查找了多余5次而引发的慢查询 log-slow-admin-statements                              #记录那些慢的OPTIMIZE TABLE,ANALYZE TABLE和ALTER TABLE语句 log-slow-slave-statements                              #记录由slave所产生的慢查询 slow_query_log_file = /usr/local/mysql/data/slow.log   #指定慢查询日志文件路径 table_cache = 614                                      #表分配的内存，物理内存越大，设置就越大 table_open_cache = 512                                 #设置高速缓存表的数目 thread_cache_size = 64                                 #服务器线程缓存数，与内存大小有关(建议大于3G设置为64) thread_concurrency = 32                                #CPU核数的两倍 query_cache_size = 32M                                 #指定MySQL查询缓冲区的大小 query_cache_limit = 2M                                 #只有小于此设置值的结果才会被缓存 query_cache_min_res_unit = 2k                          #设置查询缓存分配内存的最小单位 key_buffer_size = 512M                                #指定用于索引的缓冲区大小，增加它可得到更好的索引处理性能 sort_buffer_size = 2M                                 #设置查询排序时所能使用的缓冲区大小，系统默认大小为2MB join_buffer_size = 1M                                 #联合查询操作所能使用的缓冲区大小 read_buffer_size = 4M                                 #读查询操作所能使用的缓冲区大小 read_rnd_buffer_size = 16M                            #设置进行随机读的时候所使用的缓冲区 thread_stack = 192K                                   #设置Mysql每个线程的堆栈大小，默认值足够大，可满足普通操作 bulk_insert_buffer_size = 8M                          #可以适当调整参数至16MB~32MB，建议8MB #myisam参数引擎相关 myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover                                       #自动检查和修复没有适当关闭的MyISAM表 key_buffer_size = 16M                                #myisam索引buffer，只有key没有data   transaction_isolation = READ-COMMITTED              #事务隔离级别 tmp_table_size = 64M                                #设置内存临时表最大值 max_heap_table_size = 64M                           #独立的内存表所允许的最大容量 #innodb引擎参数相关 default-storage-engine=InnoDB                      #默认表的类型为InnoDB innodb_old_blocks_time =1000                       #减小单次的大批量数据查询,默认为0，调整后性能提升80%                     http://www.cnblogs.com/cenalulu/archive/2012/10/10/2718585.html  innodb_flush_method = O_DIRECT                     #从innode刷新到磁盘，不经过系统write,fdatasync(默认)，O_DSYNC，O_DIRECT http://blog.csdn.net/jiao_fuyou/article/details/16113403  innodb_additional_mem_pool_size = 16M              #设置InnoDB存储的数据目录信息和其他内部数据结构的内存池大小 innodb_buffer_pool_size = 51G                      #InnoDB使用一个缓冲池来保存索引和原始数据，官方建议物理内存的80% innodb_data_file_path = ibdata1:128M:autoextend    #表空间 innodb_file_io_threads = 4                         #InnoDB中的文件I/O线程，通常设置为4，innodb除master线程外，还有insert buffer, log, read, write这4种线程，默认各有一个 innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_thread_concurrency = 8                      #服务器有几个CPU就设置为几，建议用默认设置，一般设为8 innodb_flush_log_at_trx_commit = 2                 #设置为0就等于innodb_log_buffer_size队列满后再统一存储，默认为1 innodb_log_buffer_size = 16M                       #默认为1MB，通常设置为6-8MB就足够 innodb_log_file_size = 512M                        #确定日志文件的大小，更大的设置可以提高性能，但也会增加恢复数据库的时间 innodb_log_files_in_group = 3                      #为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3 innodb_max_dirty_pages_pct = 90                    #InnoDB主线程刷新缓存池中的数据 innodb_lock_wait_timeout = 120                     #InnoDB事务被回滚之前可以等待一个锁定的超时秒数 innodb_file_per_table = 1                          #InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间,0关闭,1开启 innodb_autoextend_increment = 256                  #这个参数的作用是控制innodb 共享表空间文件自动扩展的大小 [mysqldump] quick max_allowed_packet = 64M [mysqld_safe] log-error = /usr/local/mysql/data/mysql.err pid-file = /usr/local/mysql/data/mysqld.pid   查询innodb分配资源 mysql> show engine innodb status; ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137363456; in additional pool allocated 0 Dictionary memory allocated 59957 Buffer pool size   8191 Free buffers       8028 Database pages     163 Old database pages 0 Modified db pages  0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 163, created 0, written 1 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 163, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0]       我们使用的是专用于MySQL的（5.5 Percona的）上运行CentOS的（不同口味），主要128GB的服务器。我们innodb_buffer_pool_size设置为104GB这些和他们也有/ tmp目录8GB的内存磁盘。他们被大量使用，但从未使用过任何的交换。当然vm.swappiness设置为1， 下面就是我们用较低层的服务器（包括128GB为简洁起见），他们又从来没有使用过任何掉期和运行大型数据库（2-3TB）： 128GB RAM：innodb_buffer_pool_size = 104GB 64GB RAM：innodb_buffer_pool_size = 56G 32GB RAM：innodb_buffer_pool_size = 28G   在大多数情况下，我们分配（N - 7G）* 0.9。所以对于一个64G的节点，我们最终分配给缓冲池内存〜51G 64GB RAM：innodb_buffer_pool_size = 51G   https://www.percona.com/blog/2015/06/02/80-ram-tune-innodb_buffer_pool_size/  http://osxr.org:8080/mysql/ident?_i=back_log&_remember=1      王导DBA MySQL优化 log_error = localhost3306.err sync_binlog=1 innodb_old_blocks_time =1000 innodb_flush_method = O_DIRECT back_log=1000 max_connections = 2000 max_user_connections=2000 min_examined_row_limit =5 skip-slave-start skip-name-resolve max_connect_errors = 100000 character-set-server=utf8 collation-server=utf8_bin binlog_cache_size=32M query_cache_limit = 2M tmp_table_size=256M max_heap_table_size=256M interactive_timeout=360 wait_timeout=360 log_slave_updates=1 expire_logs_days=60 binlog_format=mixed tmpdir=/dev/shm innodb_autoextend_increment = 256 innodb_buffer_pool_instances=8 innodb_additional_mem_pool_size=128M innodb_max_dirty_pages_pct=80 innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_log_file_size = 1G innodb_log_files_in_group = 2 innodb_flush_log_at_trx_commit = 2 innodb_file_per_table=1

这个参数最关键

innodb_flush_log_at_trx_commit = 2

一、参数解释

0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。

1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。

2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。

二、参数修改

找到mysql配置文件mysql.ini，修改成合适的值，然后重启mysql。

三、注意事项

 

当设置为0，该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。

当设置为1，该模式是最安全的，但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。。
当设置为2，该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。

四、其他相关

查找资料时候看到其他文章说innodb_flush_log_at_trx_commit和sync_binlog 两个参数是控制MySQL 磁盘写入策略以及数据安全性的关键参数，当两个参数都设置为1的时候写入性能最差，推荐做法是innodb_flush_log_at_trx_commit=2，sync_binlog=500 或1000