线上的数据库遇到了这个问题,现在一点思路都没有:
mysql版本
mysql Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (x86_64) using readline 5.0
tablespace情况:
-rw-r--r-- 1 mysql mysql 10M Mar 14 14:00 ibdata1
-rw-r--r-- 1 mysql mysql 2.0G Mar 14 13:48 ibdata2
my.cnf相关配置:
innodb_data_file_path=ibdata1:10M;ibdata2:10M:autoextend
innodb_file_per_table=1java error:
insert和update的时候提示table is fullmysql error log:
110313 21:39:48InnoDB: Warning: cannot find a free slot for an undo log. Do you have too
InnoDB: many active transactions running concurrently?
mysql版本
mysql Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (x86_64) using readline 5.0
tablespace情况:
-rw-r--r-- 1 mysql mysql 10M Mar 14 14:00 ibdata1
-rw-r--r-- 1 mysql mysql 2.0G Mar 14 13:48 ibdata2
my.cnf相关配置:
innodb_data_file_path=ibdata1:10M;ibdata2:10M:autoextend
innodb_file_per_table=1java error:
insert和update的时候提示table is fullmysql error log:
110313 21:39:48InnoDB: Warning: cannot find a free slot for an undo log. Do you have too
InnoDB: many active transactions running concurrently?
改成 innodb_file_per_table
If the expanded slots are used: mysqld refuses to start and prints an error in the error log:InnoDB: Error: innodb_extra_undoslots option is disabled, but it was enabled before.
InnoDB: The datafile is not normal for mysqld and disabled innodb_extra_undoslots.
InnoDB: Enable innodb_extra_undoslots if it was enabled before, and
InnoDB: ### don't use this datafile with other mysqld or ibbackup! ###
InnoDB: Cannot continue operation for the safety. Calling exit(1). *
If the expanded slots are not used: mysqld starts and prints only a warning in the error log:InnoDB: Warning: innodb_extra_undoslots option is disabled, but it was enabled before.
InnoDB: But extended undo slots seem not used, so continue operation.System Variables
innodb_extra_undoslots
General Description:
Command Line Yes
Config File Yes
Variable Scope Global
Dynamic Variable No
Permitted Values:
Type BOOL
Default Value OFF
Range ON/OFF
innodb_data_file_path=ibdata1:10M;ibdata2:10M:autoextendibdata2 10M-->2G
应该有大事务开始却没有提交造成共享区的undo段暴涨,先重启让这些回滚回去,可能会很慢
innodb_data_file_path=ibdata1:10M;ibdata2:2048M;ibdata3:500M:autoextend为何要innodb_file_per_table, 这个可不是什么好参数。
If the expanded slots are used: mysqld refuses to start and prints an error in the error log:InnoDB: Error: innodb_extra_undoslots option is disabled, but it was enabled before.
InnoDB: The datafile is not normal for mysqld and disabled innodb_extra_undoslots.
InnoDB: Enable innodb_extra_undoslots if it was enabled before, and
InnoDB: ### don't use this datafile with other mysqld or ibbackup! ###
InnoDB: Cannot continue operation for the safety. Calling exit(1). *
If the expanded slots are not used: mysqld starts and prints only a warning in the error log:InnoDB: Warning: innodb_extra_undoslots option is disabled, but it was enabled before.
InnoDB: But extended undo slots seem not used, so continue operation.System Variables
innodb_extra_undoslots
General Description:
Command Line Yes
Config File Yes
Variable Scope Global
Dynamic Variable No
Permitted Values:
Type BOOL
Default Value OFF
Range ON/OFF
Units Unitless http://www.percona.com/docs/wiki ... ts?redirect=1InnoDB plugin 新特性测试 http://hi.baidu.com/unidba/blog/ ... a8dee708fa9331.html
alter table xxx engine=myisam用这两条命令,我看行。
-rw-r--r-- 1 mysql mysql 10M Mar 14 14:00 ibdata1
-rw-r--r-- 1 mysql mysql 2.0G Mar 14 13:48 ibdata2innodb_data_file_path=ibdata1:10M;ibdata2:10M:autoextend
innodb_file_per_table=1
看到这样的配置,无语了, linux x86的系统,你好意思这样配置2个tablespace都是10M?至少应该设置成50G,特别是innodb存储引擎。你的报错时因为批量操作或者并发的时候,共享表空间不够用了,我的建议是这样的,当然不一定是最佳的:1,将innodb_data_file_path=ibdata1:10M;ibdata2:10M:autoextend换成 innodb_data_file_path=ibdata1:50G;ibdata2:50G:autoextend2,然后将innodb_buffer_pool_size设置大一些,如果只采用InnoDB,可以把这个参数调大一点,大约内存的70%左右,当然,如果数据量不会暴增并且不是特别大,这个参数还是不要太大了,浪费空间。
buffer pool满了,里面有太多的东西还没commit, 或者说明你一个transaction的中数据太多了