s
mysql修改binlog保存的天数
https://blog.csdn.net/Hu_wen/article/details/80582013
查看binlog过期时间,设置的时间为90天,这个值默认是0天,也就是说不自动清理,可以根据生产情况修改,本例修改为7天
mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 90 |
+------------------+-------+
1 row in set (0.00 sec)
mysql> set global expire_logs_days=7;
Query OK, 0 rows affected (0.00 sec)
设置之后不会立即清除,触发条件是:
binlog大小超过max_binlog_size
手动执行flush logs
重新启动时(MySQL将会new一个新文件用于记录binlog)
我们执行flush logs;
mysql> flush logs;
Query OK, 0 rows affected, 64 warnings (0.16 sec
如果binlog非常多,不要轻易设置改参数,有可能导致io争用,这时候可以使用purge命令予以清除:
将bin.000055之前的binlog清掉:
mysql>purge binary logs to 'bin.000055';
将指定时间之前的binlog清掉:
mysql>purge binary logs before '2017-05-01 13:09:51';
问题1:
/dev/mapper/datavg-binloglv 20G 19G 0 100% /binlog /dev/mapper/datavg-datalv 29G 22G 5.3G 81% /mysql [root@sctssitdb18 ~]# cd /binlog/ [root@sctssitdb18 binlog]# ll total 19418016 drwx------ 2 mysql mysql 16384 Feb 7 15:03 lost+found -rw-r----- 1 mysql mysql 3217373871 Apr 29 11:29 mysql-bin.000005 -rw-r----- 1 mysql mysql 3150164739 Apr 29 12:21 mysql-bin.000006 -rw-r----- 1 mysql mysql 1931461984 Apr 29 12:29 mysql-bin.000007 -rw-r----- 1 mysql mysql 1783244637 Apr 29 12:30 mysql-bin.000008 -rw-r----- 1 mysql mysql 1366259526 Apr 29 12:41 mysql-bin.000009 -rw-r----- 1 mysql mysql 3108114143 May 5 17:55 mysql-bin.000010 -rw-r----- 1 mysql mysql 2151489577 May 5 18:21 mysql-bin.000011 -rw-r----- 1 mysql mysql 2682695927 May 6 09:17 mysql-bin.000012 -rw-r----- 1 mysql mysql 473747456 May 6 10:56 mysql-bin.000013 -rw-r----- 1 mysql mysql 279 May 6 09:17 mysql-bin.index
解决方案:删除非近期的mysql-bin.xxxx 文件即可,如有集群mysql最好用命令清理binlog文件,容易出现问题,万一要恢复需要用binlog。
mysql报错Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage
https://www.cnblogs.com/MYSQLZOUQI/p/6820688.html
Part1:max_binlog_cache_size = 4G ?
http://blog.51cto.com/suifu/1859252?zk1
浅析mysql内存参数之-- binlog_cache_size
https://blog.csdn.net/lxpbs8851/article/details/38455223
原my.cnf文件
内容:
55 # BINARY LOGGING #
56 log_bin = /mysql/binlog/mysql-bin
57 binlog_format = ROW
58 expire_logs_days = 14
59 sync_binlog = 1
60 binlog_cache_size = 1M
61 max_binlog_cache_size = 2G
数据库查询
mysql> show global status like 'bin%';
+----------------------------+------------------+
| Variable_name | Value |
+----------------------------+------------------+
| Binlog_snapshot_file | mysql-bin.000232 |
| Binlog_snapshot_position | 987410213 |
| Binlog_cache_disk_use | 15588 |
| Binlog_cache_use | 19931056 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 10 |
+----------------------------+------------------+
异常内容:
2018-08-04 07:00:00,001 INFO [stdout] (pool-5-thread-1) 07:00:00.001 [pool-5-thread-1] INFO c.s.p.s.service.TmpDateCleanJob - 定时JOB,清理perfStatisticsTemp#开始 2018-08-04 07:01:32,872 INFO [stdout] (pool-5-thread-1) 07:01:32.870 [pool-5-thread-1] ERROR o.s.s.s.TaskUtils$LoggingErrorHandler - Unexpected error occurred in scheduled task. 2018-08-04 07:01:32,872 INFO [stdout] (pool-5-thread-1) org.springframework.jdbc.UncategorizedSQLException: 2018-08-04 07:01:32,872 INFO [stdout] (pool-5-thread-1) ### Error updating database. Cause: java.sql.SQLException: Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again 2018-08-04 07:01:32,872 INFO [stdout] (pool-5-thread-1) ### The error may involve cn.s*****.ptsp.dao.mapper.PerfStatisticsMapper.deleteByPerfIds-Inline 2018-08-04 07:01:32,872 INFO [stdout] (pool-5-thread-1) ### The error occurred while setting parameters 2018-08-04 07:01:32,872 INFO [stdout] (pool-5-thread-1) ### SQL: DELETE FROM perf_statistics_temp WHERE perf_id IN ( ? ) 2018-08-04 07:01:32,872 INFO [stdout] (pool-5-thread-1) ### Cause: java.sql.SQLException: Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again 2018-08-04 07:01:32,872 INFO [stdout] (pool-5-thread-1) ; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1197]; Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again; nested exception is java.sql.SQLException: Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again 2018-08-04 07:01:32,872 INFO [stdout] (pool-5-thread-1) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84) 2018-08-04 07:01:32,872 INFO [stdout] (pool-5-thread-1) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) 2018-08-04 07:01:32,872 INFO [stdout] (pool-5-thread-1) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) 2018-08-04 07:01:32,872 INFO [stdout] (pool-5-thread-1) at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) 2018-08-04 07:01:32,872 INFO [stdout] (pool-5-thread-1) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:368) 2018-08-04 07:01:32,872 INFO [stdout] (pool-5-thread-1) at com.sun.proxy.$Proxy32.delete(Unknown Source) 2018-08-04 07:01:32,872 INFO [stdout] (pool-5-thread-1) at org.mybatis.spring.SqlSessionTemplate.delete(SqlSessionTemplate.java:268) 2018-08-04 07:01:32,872 INFO [stdout] (pool-5-thread-1) at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:58) 2018-08-04 07:01:32,872 INFO [stdout] (pool-5-thread-1) at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53) 2018-08-04 07:01:32,872 INFO [stdout] (pool-5-thread-1) at com.sun.proxy.$Proxy34.deleteByPerfIds(Unknown Source) 2018-08-04 07:01:32,872 INFO [stdout] (pool-5-thread-1) at cn.s*****.ptsp.service.service.TmpDateCleanJob.perfStatisticsTempCleaner(TmpDateCleanJob.java:55) 2018-08-04 07:01:32,872 INFO [stdout] (pool-5-thread-1) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 2018-08-04 07:01:32,872 INFO [stdout] (pool-5-thread-1) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 2018-08-04 07:01:32,872 INFO [stdout] (pool-5-thread-1) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 2018-08-04 07:01:32,872 INFO [stdout] (pool-5-thread-1) at java.lang.reflect.Method.invoke(Method.java:498) 2018-08-04 07:01:32,872 INFO [stdout] (pool-5-thread-1) at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:65) 2018-08-04 07:01:32,872 INFO [stdout] (pool-5-thread-1) at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54) 2018-08-04 07:01:32,873 INFO [stdout] (pool-5-thread-1) at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:81) 2018-08-04 07:01:32,873 INFO [stdout] (pool-5-thread-1) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) 2018-08-04 07:01:32,873 INFO [stdout] (pool-5-thread-1) at java.util.concurrent.FutureTask.run(FutureTask.java:266) 2018-08-04 07:01:32,873 INFO [stdout] (pool-5-thread-1) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) 2018-08-04 07:01:32,873 INFO [stdout] (pool-5-thread-1) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) 2018-08-04 07:01:32,873 INFO [stdout] (pool-5-thread-1) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) 2018-08-04 07:01:32,874 INFO [stdout] (pool-5-thread-1) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) 2018-08-04 07:01:32,874 INFO [stdout] (pool-5-thread-1) at java.lang.Thread.run(Thread.java:745) 2018-08-04 07:01:32,874 INFO [stdout] (pool-5-thread-1) Caused by: java.sql.SQLException: Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again 2018-08-04 07:01:32,874 INFO [stdout] (pool-5-thread-1) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965) 2018-08-04 07:01:32,874 INFO [stdout] (pool-5-thread-1) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) 2018-08-04 07:01:32,874 INFO [stdout] (pool-5-thread-1) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) 2018-08-04 07:01:32,874 INFO [stdout] (pool-5-thread-1) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527) 2018-08-04 07:01:32,884 INFO [stdout] (pool-5-thread-1) at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1283) 2018-08-04 07:01:32,884 INFO [stdout] (pool-5-thread-1) at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:783) 2018-08-04 07:01:32,884 INFO [stdout] (pool-5-thread-1) at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197) 2018-08-04 07:01:32,884 INFO [stdout] (pool-5-thread-1) at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:404) 2018-08-04 07:01:32,884 INFO [stdout] (pool-5-thread-1) at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:45) 2018-08-04 07:01:32,884 INFO [stdout] (pool-5-thread-1) at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:73) 2018-08-04 07:01:32,884 INFO [stdout] (pool-5-thread-1) at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49) 2018-08-04 07:01:32,884 INFO [stdout] (pool-5-thread-1) at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:115) 2018-08-04 07:01:32,884 INFO [stdout] (pool-5-thread-1) at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75) 2018-08-04 07:01:32,884 INFO [stdout] (pool-5-thread-1) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:170) 2018-08-04 07:01:32,884 INFO [stdout] (pool-5-thread-1) at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:185) 2018-08-04 07:01:32,884 INFO [stdout] (pool-5-thread-1) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 2018-08-04 07:01:32,884 INFO [stdout] (pool-5-thread-1) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 2018-08-04 07:01:32,884 INFO [stdout] (pool-5-thread-1) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 2018-08-04 07:01:32,884 INFO [stdout] (pool-5-thread-1) at java.lang.reflect.Method.invoke(Method.java:498) 2018-08-04 07:01:32,884 INFO [stdout] (pool-5-thread-1) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358) 2018-08-04 07:01:32,884 INFO [stdout] (pool-5-thread-1) ... 20 common frames omitted
解决方案一
Caused by: java.sql.SQLException: Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again
解决方案二
开发拆分事务
mysql5.6之my.cnf配置详解
https://blog.csdn.net/sbpgywgi/article/details/78561212
my.cnf [mysqld] # GENERAL # user = mysql port = 3306 default_storage_engine = InnoDB socket = /tmp/mysql.sock tmpdir = /dev/shm 放到共享内存里面,效率更高, 共享内存默认是物理内存的一半 character-set-server = gbk collation-server = gbk_chinese_ci (校验级,不建议设置,让系统自己选择) init-connection="set names gbk"; 如果指定非utf8 和laint 1的话 建议添加 init-connection="set names gbk"; 这个 新创建的连接客户端要设计他的字符集 http://imysql.com/tag/字符集 参考 http://imysql.cn/2013/10/29/misunderstand-about-charset-handshake.shtml 参考 | utf8_general_ci | utf8 | 33 | Yes | Yes | 1 | utf8默认的字符集编码 net_buffer_length = 1024K skip-name-resolve skip-external-locking ft_min_word_len = 4 event_scheduler = 0 tmpdir = /dev/shm 指向共享内存 如果内存比较小需要注意 skip-character-set-client-handshake 忽略客户端连接过来的字符集,使用服务器端指定的字符集 ## File back_log = 500 比如我现在有1000个连接过来 数据库现在只能连接100个进程,另外900缓存起来存到back_log中 open_files_limit = 65535 不超过内核的上限值就可以 ,但是不要设置太小 # MyISAM # key_buffer_size = 64M 只针对Myisam引擎以Innodb表为主 这个 就足够用了 如果以myisam表为主这个需要设置大些 #myisam_recover_options = FORCE,BACKUP myisam_sort_buffer_size = 32M myisam_repair_threads = 1 # SAFETY # max_allowed_packet = 128M 这个参数一般给默认值就可以了,设置导入导出环境这块设置不一样可能会报错。 skip_name_resolve #buffers & cache table_open_cache = 2048 table_definition_cache = 2048 max_heap_table_size = 96M tmp_table_size = 96M 如果排序多的话下面2个参数加大一些 sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 512 有点小可以加大一些 ,要根据我们的情况来顶,如果前端使用连接池512已经够用了,如果使用短链接需要加大 可以设置生1M query_cache_size = 0 =0 是关闭掉 query_cache_type = 0 query_cache_limit = 256K query_cache_min_res_unit = 512 thread_stack = 192K read_buffer_size = 2M read_rnd_buffer_size = 2M bulk_insert_buffer_size = 32M #connecter max_connections = 1100 数据库最大连接数 max_user_connections = 1000 用户最大连接数 max_connect_errors = 1000000 输入错误多少次 锁定用户 #timeout wait_timeout = 100 连接池一般是长连接,长连接 这个值就不要设置太小,太小的话超过一段时间就会自动踢掉了,可以根据 实际情况设置时间 interactive_timeout = 100 connect_timeout = 20 连接超时,设置太大会有问题,如果网络有问题就 slave-net-timeout = 20 这个可以设置小一点,太长会导致连接进程等待 transaction_isolation = READ-COMMITTED 事物隔离级别,交易类的用RR,如果代码曾有能力解决幻读用rc #performance_schema=0 # BINARY LOGGING # log_bin = /database/mysql/binlog/mysql-bin 日志存放位置 expire_logs_days = 6 日志存放天数 sync_binlog = 0 sync_binlog减小io开销使用 主从必须设置为1 好保证主从一致性, 设置为1 影响性能 ,设置为0 速度快数据不安全 可设置为0,1, 2, 3,5,,10 binlog_format = row max_binlog_size = 1024M # LOGGING # log_error = /database/mysql/logs/mysql-error.log 错误日志的位置 #log_queries_not_using_indexes = 1 slow_query_log = 1 slow_query_log_file = /database/mysql/logs/mysql-slow.log 慢日志的位置 long_query_time = 1 慢查询的时候 可以小一点 ,每个sql 最好不要超过20毫秒 log-output = file 控制慢日志输出的 file|table 可以文件,也可表 log_warnings = 2 log_warnings= 2,可以避免Aborted connection #thread pool thread_handling=pool-of-threads thread_pool_oversubscribe=3 意思是一个cpu核心同时可以处理多少个并发线程的链接 thread_pool_oversubscribe = cpu核心数 这个值不建议超过3 # INNODB # #innodb_adaptive_flushing_method = estimate innodb_adaptive_flushing = 1 顺序io 的参数 自适应刷新,开启比较合理 innodb_data_home_dir = /database/mysql/ibdata innodb_log_group_home_dir = /database/mysql/ibdata_log innodb_data_file_path = ibdata1:2048M:autoextend 共享表空间的大小 #innodb_adaptive_hash_index_partitions= 4 innodb_additional_mem_pool_size = 16M innodb_flush_method = O_DIRECT innodb_log_files_in_group = 2 innodb_log_file_size = 512M innodb_log_buffer_size = 16M innodb_flush_log_at_trx_commit = 1 innodb_flush_log_at_trx_commit = 0 非交易类,=1 交易类,保持一致性要设置成1 否则设置为0 innodb_file_per_table = 1 innodb_buffer_pool_size = 32G innodb_buffer_pool_instances = 8 比如场景为32 G 划分为4个相当于每个都有8个G子分区来进行管理,但是前提条件是内存一定要大于8G,也就是说切分完之后每个 都要大于1个G,大内存 高并发场景 设置要好点 innodb_blocking_buffer_pool_restore = ON innodb_buffer_pool_restore_at_startup = 300 innodb_merge_sort_block_size = 2M innodb_kill_idle_transaction = 300 查询或者dml语句超过一定时间内都会被kill掉 建议不设置 innodb_max_dirty_pages_pct = 65 innodb_io_capacity = 300 根据硬盘的iops来设置 ssd可以设置为2000普通的设置300-500 innodb_blocking_buffer_pool_restore = ON innodb_buffer_pool_restore_at_startup = 300 innodb_merge_sort_block_size = 2M https://bugs.launchpad.net/percona-server/+bug/1072538 innodb_thread_concurrency = 0 nnodb_thread_concurrency = 0 一般是设置0或者CPU*2 现在已经基本上不用太关心了5.6开始当做透明的就好了 innodb_read_io_threads = 12 innodb_write_io_threads = 16 innodb_file_format = Barracuda innodb_flush_neighbor_pages = 0 文件格式 innodb_change_buffering = all innodb_stats_on_metadata = 0 innodb_lock_wait_timeout = 30 等待锁的时间根据业务来判断 innodb_rollback_on_timeout innodb_sync_spin_loops = 100 innodb_spin_wait_delay = 30 innodb_support_xa = 0 innodb_concurrency_tickets = 500 默认值500 log_bin_trust_function_creators = 1 log_bin_trust_function_creators=1:触发器和存储过程时启用。 注释: 开启binlog时,是否允许创建存储程序 (除非有SUPER权限,或者指定DETERMINISTIC、READS SQL DATA、NO SQL) 可动态修改,属于一个功能性的东西。 innodb_purge_threads =1 purge线程 只能设置为1 5.7才可以有多个 innodb_purge_batch_size = 32 innodb_old_blocks_pct = 75 #Replication server-id = 113306 #log-slave-updates #skip_slave_start 看情况是否开启,如果作为中级节点必须开启否则不用开启,log-slave-updates 建议这个要开启 #read_only = 1 slave-skip-errors=1032 跳过指定错误 skip_slave_start = 1 开机自动启动同步,早起版本建议不要开启 innodb_max_dirty_pages_pct = 35 这个参数看环境 ,建议设置为50以下,毕竟数据库以读为主, 如果设置过多,刷新的时候会造成瓶颈 #relay log #relay-log = /database/mysql/binlog/slave-relay-bin #relay-log-index = /database/mysql/binlog/slave-relay-bin.index max-relay-log-size = 256M relay_log_purge = 1 #slave_parallel_workers = 8 并行的复制,多个数据库就要开启并行 my.cnf 里头,各个section什么意思 mysqld_safe 针对mysqld_safe这个进程或者程序一个配置的区间,这个区间只会被mysqld_safe读取 [mysql] 只针对 mysql 这个cli客户端工具 [client] 针对全部的client,例如mysqlmysqldumpmysqladmin都是 [mysqldump] 针对mysqldump专区 [mysqld] mysqld这个服务进程的专区 relay_log_recovery = 1 如果是slave 的话一定要加上 [mysql] prompt="\u@\h \D \R:\m:\s [\d]&get; " pager="less -i -n -S" no-auto-rehash tee="/home/mysql/query.log" 记录数据库的操作,加上no-auto-rehash 不扫描MySQL日志 innodb_adaptive_flushing_lwm -- 设置redo log flush低水位线,当需要flush的redo log超过这个低水位线时,立即强制启用adaptive flushing,即便没有设置使用adaptive flushing机制 innodb_buffer_pool_size innodb_additional_mem_pool_size 缓存数据字典的默认是8M 最好给16M或者32M innodb_log_buffer_size read_buffer_size -- 顺序读缓冲,提高顺序读效率 +read_rnd_buffer_size -- 随机读缓冲, +sort_buffer_size -- 排序缓冲,提高排序效率 +join_buffer_size -- 表连接缓冲,提高表连接效率 +binlog_cache_size -- 二进制日志缓冲,提高二进制日志写入效率 +tmp_table_size -- 内存临时表,提高临时表存储效率 +tmp_table_size -- 内存临时表,提高临时表存取效率(一般用于group by查询) +thread_cache_size -- 线程缓存, +net_buffer_length -- 线程持连接缓冲以及读取结果缓冲 +net_buffer_length -- 线程池连接缓冲以及读取结果缓冲 需要关注的是最大连接数 不要设置太大 tmp_table_size 不要设置太大 innodb_buffer_pool_size -- InnoDB高速缓冲,行数据、索引缓冲,以及事务锁、自适应哈希等 +innodb_additional_mem_pool_size -- InnoDB数据字典额外内存,缓存所有表数据字典 +innodb_log_buffer_size -- InnoDB REDO日志缓冲,提高REDO日志写入效率 +key_buffer_size -- MyISAM表索引高速缓冲,提高MyISAM表索引读写效率 +query_cache_size -- 查询高速缓存,缓存查询结果,提高反复查询返回效率 +key_buffer_size -- MyISAM表索引高速缓冲,提高MyISAM表索引读写效率(建议少用MyISAM) +query_cache_size -- 查询高速缓存,缓存查询结果,提高反复查询返回效率(建议关闭QC) +table_cahce -- 表空间文件描述符缓存,提高数据表打开效率 +table_definition_cache -- 表定义文件描述符缓存,提高数据表打开效率 MyISAM和InnoDB buffer区别 InnoDB既缓存索引,又缓存数据 MyISAM只缓存索引,不缓存数据 max_heap_table_size 负责设置MEMORY/HEAP表最大容量,不管其他执行SQL产生的临时表,如果内存不够用,则不允许写入新的数据,MEMORY/HEAP表也不会转成磁盘表,只会告警超限后拒绝写入 tmp_table_size 不负责限制 MEMORY/HEAP表最大容量,如果执行SQL产生临时表超过 tmp_table_size/max_heap_table_size,则会产生基于磁盘的MyISAM表 | default_tmp_storage_engine | InnoDB tmp_table_size 不负责限制 MEMORY/HEAP表最大容量,如果执行SQL产生临时表超过 tmp_table_size/max_heap_table_size,则会产生基于磁盘的MyISAM表(如果default_tmp_storage_engine选项没有设定为InnoDB的话) 关闭查询缓存: query_cache_type = 0 query_cache_size = 0 官方MySQL版本: Total memory allocated 1098907648; in additional pool allocated 0 Dictionary memory allocated 37754 Buffer pool size 65535 Free buffers 65283 Database pages 252 Old database pages 0 Modified db pages 0 Buffer pool size 大小16kb Buffer pool size 65535 * 16KB = 1G innodb_old_blocks_pct 旧的数据块的百分比 innodb_old_blocks_time 当buff_pool不够用的时候会根据时间讲旧的清除掉 新数据都是热数据过一段时间就放到存旧数据的里面,过段时间不用就清楚掉。 ======================================================= Percona分支版本: Total memory allocated 858521600 (818M); in additional pool allocated 0 Total memory allocated by read views 88 Internal hash tables (constant factor + variable factor) -- 内部用途哈希表缓冲 Adaptive hash index 13302240 (13281656 + 20584) -- 自适应哈希索引,约12.6M Page hash 830936 (buffer pool 0 only) -- data page哈希,约0.79MB,只缓存了buffer_pool_0这个instance,如果 innodb_buffer_pool_instances 设置大于0的话 Dictionary cache 3382197 (3321904 + 60293) -- 数据字典缓冲,约3.2MB File system 820024 (812272 + 7752) -- 文件子系统缓冲,约0.78MB Lock system 2311256 (2311256 + 0) -- 锁子系统缓冲,约2.2MB Recovery system 0 (0 + 0) -- 恢复子系统缓冲,目前是0 Dictionary memory allocated 60293 Buffer pool size 51199 Buffer pool size, bytes 838844416 Free buffers 50795 -- 空闲buffer,约793MB Database pages 403 -- 缓冲热点数据的buffer,约6KB Old database pages 0 -- 缓冲旧数据的buffer Modified db pages 0 -- 缓冲最近修改数据的buffer 叶金荣(4700963)21:18:01 默认128M(老版本默认8M,坑很多),以InnoDB表为主的话,设为物理内存50~70% [MySQL FAQ]系列 -- 数据不算大,备份却非常慢,http://imysql.com/2009/09/18/mysql-faq-why-backup-is-so-slow.html adaptive index hash, 自适应哈希索引,用来管理buffer pool的哈希索引 adaptive index hash, size= innodb_buffer_pool / 64,随着buffer的频繁更新,会随之上升 Lock system 2311256 (2311256 + 0) -- 锁子系统缓冲,约2.2MB 事件等待 当Innodb_buffer_pool_wait_free 就是事件等待 这个时候很有可能是内存不够 ,查看当系统的内存,如果有 剩余的内存就给buffer_pool多分配一些,如果剩余内存不多的情况下,建议添加内存或者迁移 | Innodb_buffer_pool_wait_free | 0 | | Innodb_log_waits | 0 | innodb_additional_mem_pool_size 数据字典以及内部数据结构缓存,表数量越多,相应的内存需要越大。 默认8M,通常设置为8~32M足够,一般建议设置为16M,如果确实不够用,那么会从系统中请求增加分配内存,并且错误日志中会提醒,目前至少还未发生过。 innodb_log_buffer_size show global status 查看 Innodb_log_waits 是否大于0,是的话,就需要提高 innodb_log_buffer_size,否则维持原样。 show global stauts 查看30~60秒钟 Innodb_os_log_written 的间隔差异值,即可计算出 innodb_log_buffer_size 设置多大合适。 默认8M,一般设置为16 ~ 64M足够了。 max_heap_table_size 负责设置MEMORY/HEAP表最大容量,不管其他执行SQL产生的临时表,如果内存不够用,则不允许写入新的数据,MEMORY/HEAP表也不会转成磁盘表,只会告警超限后拒绝写入 tmp_table_size 不负责限制 MEMORY/HEAP表最大容量,如果执行SQL产生临时表超过 tmp_table_size/max_heap_table_size,则会产生基于磁盘的MyISAM表(如果default_tmp_storage_engine选项没有设定为InnoDB的话) tmp_table_size -- 内存临时表,提高临时表存取效率(一般用于group by查询) ;max_heap_table_size 负责设置MEMORY/HEAP表最大容量,不管其他执行SQL产生的临时表,如果内存不够用,则不允许写入新的数据,MEMORY/HEAP表也不会转成磁盘表,只会告警超限后拒绝写入
end