Posts tagged ‘innodb’

InnoDB plugin 1.0手册翻译

之前和团队的同学一起翻译了部分InnoDB Plugin 1.0的手册,但是一直没有最终完稿。

利用周末的时间,我把剩下来的几章翻译完成并放到公司官方网站,希望大家一起来审阅一下。

这里也非常感谢彭立勋,胡中泉,陈良允同学。彭立勋主要翻译完成了1-7章,胡中泉,陈良允对文档进行了审阅。这些翻译文档也是取得了这些同学的同意才发表在公司博客网站的。

下面列出所有翻译文档的链接,请各位大牛批评指正(请直接在沃趣科技博客中评论):

第一章:InnoDB Plugin简介
第二章:在InnoDB存储引擎下快速创建索引
第三章:InnoDB 数据压缩
第四章:InnoDB文件格式管理
第五章:可变长度列(Variable-Length Columns)的存储
第六章:InnoDB的INFORMATION_SCHEMA数据表
第七章:性能优化及可扩展性的提高
第八章.灵活性、易用性、可靠性改进
第九章. 安装InnoDB Plugin
第十章.升级InnoDB Plugin
第十一章.从InnoDB Plugin降级
第十二章. InnoDB Plugin变更历史
附录A.第三方软件
附录 B.在5.1.30及之前版本MySQL使用InnoDB Plugin
附录 C. InnoDB Plugin 1.0参数修改列表

 

另外,由于本人时间有限,本来这些文档需要转换为docbook格式提交给Oracle官方文档的team的,如果哪位同学有兴趣可以一起参与进来。

exceeds the log group capacity

做数据订正,需要更新了一批表数据。
数据库是采用的分库结构,也就是说一个MySQL instance里面有多个分库(一共有8个MySQL instance),我的数据库instance里面是16个分库,现在需要更新每个分库里面的一张表(即16张表)的所有数据。
我是用脚本更新的,串行操作,每个分库执行:
update vas_eq_enquiries set ENABLE_VIEW=1;
结果就收到报警,应用部门也过来说他们很多语句执行不了,报错说lock timeout:
100506 19:51:16  InnoDB: ERROR: the age of the last checkpoint is 966364350,
InnoDB: which exceeds the log group capacity 966363956.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.
100506 19:51:32  InnoDB: ERROR: the age of the last checkpoint is 1028687928,
InnoDB: which exceeds the log group capacity 966363956.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.

认真看了一下错误描述,是说log group的容量不够了。MySQL的innodb引擎日志比较重要,所有数据变更都共享着这个日志undo log。看样子一下子更新的数据太多了,于是我修改脚本,每次间隔5s,10s,30s去操作每个分库,最后发现间隔30s就不会有问题了。
当然,这个30s只对我的这个情况有意义。我这里也查看了一下MySQL instance的logfile大小:
innodb_log_file_size = 512M
innodb_log_files_in_group = 2

也就是说,它只允许最大1G的变更。
查看了我变更的表vas_eq_enquiries,在各个分库上大小不一,25M~77M的都有,平均算50M,16个分库,那就有16*50=800M了,在加上数据库自己的变化量1G空间确实会捉襟见肘。

这个问题的最终解决方案有两个:
1、减少短时间内变更的数据量,避免更新造成log日志的容量不足。对于分库更新量的情况特别需要注意,
2、增加log日志的容量。log日志的容量,目前MySQL最大不允许超过4G。这里需要注意的是,修改log日志的容量必须要在MySQL将所有日志文件都应用完毕的情况下,先停机,然后将日志文件移到另外的目录备份,重启MySQL数据库。MySQL数据库启动以后,会自动生成新的log日志。否则可能会有如下的错误:
InnoDB: Error: log file /data/mysqldata/innodb_log/ib_logfile0 is of different size 0 1073741824 bytes
InnoDB: than specified in the .cnf file 0 1283457024 bytes!
100506 21:57:41 [ERROR] Plugin ‘InnoDB’ init function returned error.
100506 21:57:41 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
100506 21:57:41 [ERROR] Unknown/unsupported table type: INNODB
100506 21:57:41 [ERROR] Aborting

导致MySQL数据库启动不了。

顺便把MySQL这两个参数解释抄在下面:
innodb_log_file_size
The size in bytes of each log file in a log group. The combined size of log files must be less than 4GB. The default value is 5MB. Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But larger log files also mean that recovery is slower in case of a crash.

innodb_log_files_in_group
The number of log files in the log group. InnoDB writes to the files in a circular fashion. The default (and recommended) value is 2.

这里log日志的容量可能大家会觉得越大越好。innodb_log_file_size里面也说了这个值越大,把checkpoint从buffer pool中刷到磁盘的频率也越少,减少了很多磁盘I/O。同时,crash情况下需要恢复的时间也越多。

Baron Schwartz 在MySQL Performance Blog里面提到一种怎样设置innodb_log_file_size的方法。http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
他根据每分钟变化的数据量*60分钟,来粗略的得到一个innodb_log_file_size的值。
mysql> pager grep sequence
PAGER SET TO ‘grep sequence’
mysql> SHOW engine innodb STATUS\G SELECT sleep(60); SHOW engine innodb STATUS\G
Log sequence number 84 3836410803
1 row IN SET (0.06 sec)
1 row IN SET (1 min 0.00 sec)
Log sequence number 84 3838334638
1 row IN SET (0.05 sec)
然后:
mysql> SELECT (3838334638 – 3836410803) / 1024 / 1024 AS MB_per_min;
+————+
| MB_per_min
+————+
| 1.83471203
+————+

于是:1.8*60 约等于110M,那么就取128M把,如果innodb_log_files_in_group=2,那么innodb_log_file_size=128/2=64M。
这个当然取决于你这一分钟应用程序修改的量的多少,如果取的时间不对,那么这个值当然不精确。

3楼的3. Sheeri K. Cabral提出了他自己的方法:登录系统,直接看ib_logfile切换的速度。
/var/lib/mysql> ls -lrth ib_logfile*
-rw-r–r– 1 mysql mysql 400M Nov 22 09:00 ib_logfile1
-rw-r–r– 1 mysql mysql 400M Nov 23 10:51 ib_logfile0

(这里26个小时切换了一次)然后再根据你的要求:你需要logfile中保存多久的日志,来调整logfile的大小。当然,这个也不精确。取决于你什么时候去看这个文件,白天数据量变化多,晚上变化少,这个间隔时间就完全不同。

总的来说,需要注意这个innodb_log_file_size的大小设置,每个应用都有自己的特色,大小设置需要根据应用的不同来设置。

may you success.

索引问题导致mysql复制lock timeout

遇到一个非常郁闷的问题。MySQL复制备机SQL线程执行一个语句报错:
Error ‘Lock wait timeout exceeded; try restarting transaction’ on query. Default database: ‘brmms2’. Query: ‘UPDATE brmms_user SET gmt_modified = now() , last_login_ip = ‘58.20.77.164’ , last_login_time = now() WHERE member_id = ‘yanyazhang”
这个语句就算我单独提出来执行也会超时。show innodb status的状态如下:
root@localhost : (none) 08:12:46> show innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
100501 20:12:51 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 8 seconds
———-
SEMAPHORES
———-
OS WAIT ARRAY INFO: reservation count 22, signal count 22
Mutex spin waits 0, rounds 239, OS waits 8
RW-shared spins 20, OS waits 10; RW-excl spins 3, OS waits 3
————
TRANSACTIONS
————
Trx id counter 0 1835507099
Purge done for trx’s n:o < 0 1835505963 undo n:o < 0 0
History list length 44
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 0 0, not started, OS thread id 12
MySQL thread id 801, query id 3585 localhost root
show innodb status
—TRANSACTION 0 1835507098, ACTIVE 64 sec, OS thread id 17 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1216, 2 row lock(s)
MySQL thread id 1214, query id 3536 Updating
UPDATE brmms_user SET gmt_modified = now() , last_login_ip = ‘58.20.77.164’ , last_login_time = now() WHERE member_id = ‘yanyazhang’
——- TRX HAS BEEN WAITING 64 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10344 page no 3281 n bits 192 index PRIMARY of table brmms2.brmms_user trx id 0 1835507098 lock_mode X locks rec but not gap waiting
Record lock, heap no 121 PHYSICAL RECORD: n_fields 17; compact format; info bits 0
0: len 8; hex 0000000005fd14a7; asc ;; 1: len 6; hex 00006d6795f9; asc mg ;; 2: len 7; hex 00000000c30c2f; asc /;; 3: len 10; hex 79616e79617a68616e67; asc yanyazhang;; 4: len 4; hex 00000000; asc ;; 5: len 4; hex 00000000; asc ;; 6: len 4; hex 00000000; asc ;; 7: len 4; hex 00000000; asc ;; 8: len 3; hex 8fb48e; asc ;; 9: len 12; hex 35382e32302e37372e313634; asc 58.20.77.164;; 10: len 4; hex 4bc59f9c; asc K ;; 11: len 4; hex 4bc59f8f; asc K ;; 12: len 4; hex 4bc59f9c; asc K ;; 13: len 4; hex 80000000; asc ;; 14: len 4; hex 80000000; asc ;; 15: len 4; hex 80000000; asc ;; 16: SQL NULL;

——————
—TRANSACTION 0 1835505145, ACTIVE (PREPARED) 5527 sec, OS thread id 0
1 lock struct(s), heap size 368, 1 row lock(s), undo log entries 1
——–
FILE I/O
——–
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o’s: 0, sync i/o’s: 0
Pending flushes (fsync) log: 0; buffer pool: 0
16300 OS file reads, 336 OS file writes, 268 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
————————————-
INSERT BUFFER AND ADAPTIVE HASH INDEX
————————————-
Ibuf: size 1, free list len 104, seg size 106,
27 inserts, 27 merged recs, 27 merges
Hash table size 35401603, node heap has 7 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

LOG

Log sequence number 102 3346391724
Log flushed up to 102 3346391724
Last checkpoint at 102 3346391724
0 pending log writes, 0 pending chkp writes
198 log i/o’s done, 0.00 log i/o’s/second
———————-
BUFFER POOL AND MEMORY
———————-
Total memory allocated 18653842407; in additional pool allocated 16776704
Dictionary memory allocated 4059160
Buffer pool size 1048576
Free buffers 1034004
Database pages 14565
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 14565, created 0, written 146
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000
————–
ROW OPERATIONS
————–
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 9, state: waiting for server activity
Number of rows inserted 52, updated 36, deleted 3, read 364
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
—————————-
END OF INNODB MONITOR OUTPUT
============================

1 row in set, 1 warning (0.00 sec)
这里比较奇怪的地方在于:这个备机上就一个SQL线程在跑,其他的任何东西都没有跑,结果它还被锁超时杀掉了。上面的show innodb status显示
——————
—TRANSACTION 0 1835505145, ACTIVE (PREPARED) 5527 sec, OS thread id 0
1 lock struct(s), heap size 368, 1 row lock(s), undo log entries 1
这个线程是干吗的,难道是它lock住了update语句?

我先后试过如下的方法想去恢复:
1、重启数据库。SQL slave自己执行不过去。手工执行不过去。
2、重启数据库并修改为skip-slave-start模式,手工执行未果。
3、alter table brmms_user drop index idx_brmms_user_member_id, add index idx_brmms_user_Mem_id (member_id);报错lock timeout.
4、stop slave;想利用mysqldump -uroot -p –opt –add-drop-table –default-character-set=utf8 –master-data=2 –single-transaction –complete-insert –log-error=/tmp/mysqldump20100501.log brmms2 brmms_user>brmms_user.sql 或者mysqldump -uroot -p –default-character-set=utf8 brmms2 brmms_user>brmms_user.sql导出数据,然后重新导入。结果在mysqldump的时候就导不出来数据(能够导出表结构,错误日志里面也没有任何信息)
5、创建临时表tmp_pickup_brmms_user,然后insert into temp_brmms_user_pickup select * from brmms_user;。报错lock timeout.

最后通过select into outfile的方式解决了这个问题。步骤如下:
1、确认slave是停掉的。 并且修改SQL_LOG_BIN来避免下面的语句记录到binlog中(如果对应的主机没有这个问题,下面的这些操作完全没有必要通过replication传递到主机执行)。
stop slave;
SET SQL_LOG_BIN=0;
2、导出brmms_user表的所有数据。注意/tmp目录需要有足够的空间。
select * from brmms_user into outfile ‘/tmp/brmms_user.txt’;
3、创建临时表。该表的结构要求跟brmms_user表结构一样。
CREATE TABLE tmp_pickup_brmms_user ( … );
4、将brmms_user的数据导入到tmp_pickup_brmms_user。
load data local infile ‘/tmp/brmms_user.txt’ into table tmp_pickup_brmms_user;
5、改名。将brmms_user改到另外的名字,将临时表名字改为brmms_user
alter table brmms_user rename tmp_pickup_brmms_user_old;
alter table tmp_pickup_brmms_user rename brmms_user;
6、重新启动复制。
start slave;
7、收尾工作:必要的话,删除tmp_pickup_brmms_user_old表。SET SQL_LOG_BIN=1;来恢复session的binlog记录。
drop table tmp_pickup_brmms_user_old;
SET SQL_LOG_BIN=1;

may you success.

今天我们又检查了一下那个有问题的表。发现通过主键更新对应记录也是locktimeout。更新它之前和之后的主键都没有问题。突然回想起这台机器是通过另外一台机器的solaris的snapshot生产的快照,然后直接传到这台机器的。之后就直接启动起来用了。假设

这里在生成snapshot的时候,MySQL的数据(索引)在物理层面上是不一致的。这样的不一致是否是导致MySQL的这个问题的原因列。有待进一步的确认。

这里我们也知道了solaris的snapshot还是有风险的,如果以后还有备机的搭建,可以考虑先停止MySQL,然后才做snapshot。保证数据的物理一致性。

may you success