testing-04

MySQL

  • 起因

    昨天,在给一个数据表DDL操作时,InnoDB报了如下错误:

    100602 13:48:39 [ERROR] Index uk_usplugin_plugid of test/test_user_plugin_0002 has 2 columns unique inside InnoDB, but MySQL is asking statistics for 3 columns. Have you mixed up .frm files from different installations? See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html

    MySQL版本:MySQL5.1.45+InnoDB Plugin1.0.6

    当时的DDL:

    alter table test_user_plugin_0002 drop index USER_ID,add unique index uk_pluser_userid(USER_ID,PLUGIN_ID);

    追踪

    MySQL Bug System中看到,问题类似于Bug #44571中的Case2(Bug #47622)。问题的大概原因是,InnoDB的system table中的表信息和MySQL字典中的表信息不同步导致的。尝试了几次,无法重现错误。

    如何避开(workaround)

    可以通过下面的SQL来重建InnoDB的索引:

    alter table test_user_plugin_0002 engine=innodb

    Bug #47622看到5.1.46中貌似已经解决了这个问题,还可以尝试升级到5.1.46。

    另外,应该还可以尝试在DDL时打开old_alter_table来避免:(未验证)

    SET [GLOBAL] old_alter_table=ON
  • 较之MyISAM的一个很大的优势是,InnoDB会缓存数据块。如果系统中的数据量并不大,或者或者活跃数据量并不大时,InnoDB会将这些数据块全部缓存到Buffer Pool(内存)中,这样可以最大限度的提高提供的响应速度,特别是读取。当用户请求需要查询数据块时,InnoDB会首先在Buffer Pool中查找数据,如果Buffer Pool中没有该数据块时,InnoDB会从磁盘上Load对应的数据块,并通过LRU算法替代当前Buffer Pool的缓存块。

    1. Buffer Pool中LRU缺点、细节

    这样LRU算法的一个缺点是,如果有某一个查询做了一次全表扫描(例如备份,临时DDL等),都可能会导致整个Buffer Pool中LRU链表中的数据块都被替换了,甚至很多热点数据也会被替换,而这些新进的数据块可能在这一次查询之后就再也不会被读到了。我们也称这种情况为“Buffer Pool被污染”了。

    在InnoDB则引入了一些新的机制来避免这种情况。算法仍然是LRU算法,但是加上了中点策略(类似于MyISAM的key buffer中的midpoint strategy)。同时引入了参数innodb_old_blocks_time来控制Buffer Pool不被污染。

    LRU链表中的数据分为两部分:Sublist of new和Sublist of old。后者包含访问最近没有访问的数据块(链表越后面的数据块,最近越没有被访问)。默认情况,前者占63%(5/8),后者37%(3/8)。

    BufferPoolLRU (more…)

  • InnoDB Corruption

    ·

    以前没有遇到InnoDB文件损坏的情况,特此记录。从日志来看,损坏的应该是一个索引页,导致对应的数据表无法访问,也无法获取对应数据表中的数据,尝试check optimize也无法修复。最后设置innodb_force_recovery=1后启动数据库后,能够导出全部数据。

    1. 故障

    最近,在从一个Xtrabackup的备份中恢复数据后,发现MySQL数据库能正常启动,但是发现use dbname一下MySQL就会崩溃,看日志: (more…)

  • MySQL Optimize Table

    ·

    很难说Optimize Table到底能不能提高系统运行效率,但是有一点是肯定的:它能够帮我们回收更多的空间、减少“碎片”(defragment)

    1. 回收空间 Defragment

    在InnoDB的维护过程中,我们总会遇到磁盘耗尽、或者InnoDB Tablespaces用完的情况。这时候,在考虑扩容等方案之前,最好先使用Optimize Table试试。如果你的表大字段(Text Blob Varchar),并且更新、删除较频繁的话,Optimize之后可能会腾出大量的空间。 (more…)

  • InnoDB Plugin在快速DDL上做了一些改进,在做的实验中看到,创建secondary indexes时,大约快了20%。

    1. 原理

    在MySQL5.0里面,如果数据表的记录数很多,增加和删除索引是非常慢的。CREATE INDEX and DROP INDEX命令是通过先创建一个空的临时表,这个表就是你新增或删除索引后的结构,然后把原表中的全部记录都拷贝(插入)到新的临时表中,最后把原表删除,临时表重命名成原表。

    MySQL5.1的一些架构上的改变,可以简化上面的过程(不再需要逐行拷贝数据)。InnoDB Plugin利用这个改变,缩短了大多数情况下的索引变更时间。对InnoDB来说有两类索引:the clustered index and secondary indexes。因为InnoDB的主键是the clustered index,数据存放再此,所以,删除或者添加主键(the clustered index)逐行拷贝也是必须 (more…)

  • 这两天是O’Reilly MySQL Conference & Expo,期间有很多关于MySQL的相关话题的介绍,包括MySQL、PBXT、Drizzle、MariaDB、XtraDB等等,不过最抢风头的应该是InnoDB1.0.7GA(with MySQL-5.5)的新特性,回头想想,Oracle/InnoDB也酝酿了很久的吧。

    无论如何,这些新特性中最吸引我的是InnoDB的Faster Recovery。

    一般地,MySQL/InnoDB都是运行在普通的PC Server + Linux(Unix),虽然不期待小型机+AIX的高可用,但想尽一切办法缩短MySQL的不可用时间,仍然是DBA的目标。根据经验,主机OS崩溃、硬件故障,仍然是影响MySQL可用性的最主要因素,当这些故障(OS、硬件)恢复后,另一个非常耗时的恢复就是InnoDB自己的恢复时间。一般主机发生一次重启,正常大约<5分钟,而这时InnoDB恢复可能需要40分钟或者更久(这依赖于Buffer Pool、脏页面比例、TPS等因素)。 (more…)