Archive for 六月 2010

测试快速关闭innodb的方法

2010年06月20日, 11:31 下午
测试mysqlporformanceblog提供的减少关闭innodb时间的方法
经常发现一些MySQL镜像库的InnoDB的关闭时间会特别久，mysqlperformanceblog给出了一个不错的解决方案
InnoDB关闭的过程解释

MySQL官方手册介绍 影响到InnoDB关闭的参数有

innodb_fast_shutdown

如果你把这个参数设置为0，InnoDB在关闭之前做一个完全净化和一个插入缓冲合并。这些操作要花几分钟时间，在极端情况下要几个小时。

如果你设置这个参数为1，InnoDB在关闭之时跳过这些操作。 默认值为1。

如果你设置这个值为2 (在Netware无此值)， InnoDB将刷新它的日志然后冷关机，仿佛MySQL崩溃一样。已提交的事务不会被丢失，但在下一次启动之时会做一个崩溃恢复。


innodb_max_dirty_pages_pct

这是一个范围从0到100的整数。默认是90。InnoDB中的主线程试着从缓冲池写页面，使得脏页（没有被写的页面）的百分比不超过这个值。如果你有SUPER权限，这个百分比可以在服务器运行时按下面来改变：

SET GLOBAL innodb_max_dirty_pages_pct = value;


100423  0:10:18  InnoDB: Starting shutdown…
100423  0:10:20  InnoDB: Shutdown completed; log sequence number 0 4000976145
100423  0:10:20 [Note] /usr/sbin/mysqld: Shutdown complete


测试环境

1.在一个镜像库节点进行测试,innodb_buffer_pool_size=1G
2.在一个线上节点进行测试,碰巧这个节点要进行机架更换,所以可以趁机测试一把

innodb_buffer_pool_size=12G,线上环境和镜像环境还是有点不一样,没法让Innodb_buffer_pool_pages_dirty的值少于1000，虽然是大于1000，但关闭起来还是比较快速的

测试过程
正常关闭的normal-shutdown.sh


#!/bin/bash
#正常关闭innodb
log=”normal-shutdown.log”
start_time=`date`
echo “start time:”$start_time > $log
mysqladmin –defaults-file=db-32-1.cnf -uxxx -pxxx  ext  | grep dirty >> $log
mysqladmin –defaults-file=db-32-1.cnf -uxxx -pxxx shutdown
end_time=`date`
echo “end time:” $end_time >> $log

normal-shutdown.log 输出的日志如下

start time:2010年 06月 18日 星期五 17:21:00 CST
| Innodb_buffer_pool_pages_dirty    | 43492        |
end time: 2010年 06月 18日 星期五 17:23:10 CST

设置set global innodb_max_dirty_pages_pct =0的关闭方式

首先执行 mysql –defaults-file=db-31-2.cnf -uxxx -pxxx -e” set global innodb_max_dirty_pages_pct =0“
经过了大概3分钟后 Innodb_buffer_pool_pages_dirty的值少于1000
然后执行faster-shutdown.sh，生成faster-shutdown.log
#!/bin/bash
log=”faster-shutdown.log”
start_time=`date`
echo “faster shutdown start time:”$start_time >$log
mysqladmin –defaults-file=db-32-1.cnf -uxxx -pxxx  ext  | grep dirty >> $log
mysqladmin –defaults-file=db-32-1.cnf -uxxx -pxxx shutdown
end_time=`date`
echo “faster shutdown end time:” $end_time >>$log
faster-shutdown.log的内容
faster shutdown start time:2010年 06月 18日 星期五 17:47:08 CST
| Innodb_buffer_pool_pages_dirty    | 569        |
faster shutdown end time: 2010年 06月 18日 星期五 17:47:12 CST

关闭时间比较

正常关闭 快速关闭
Innodb_buffer_pool_pages_dirty 43492 569
关闭时间 2分10秒 4秒

结论

日常维护中,可以使用set global innodb_max_dirty_pages_pct =0,让Innodb_buffer_pool_pages_dirty 的数值为一个较小的值,这样就可以减少在关闭mysql服务器中耗费在innodb的时间
最终达到减少影响用户的时间
参考资料

InnoDB启动 http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html#innodb-start

Dirty pages, fast shutdown, and write combining http://www.xaprb.com/blog/2010/05/25/dirty-pages-fast-shutdown-and-write-combining/

Category: MySQL  |  2 条评论

MySQL5.1 InnoDB Plugin 启动信息详解 以及与MySQL5.0的InnoDB启动信息对比

2010年06月9日, 4:48 上午

经过一段时间的测试,准备把MySQL 5.1 InnoDB Plugin部署到线上，今天发现MySQL 5.0 InnoDB 和MySQL5.1 InnoDB Plugin启动是有点区别的，这里做一个整理

5.0MySQL 的版本信息

mysql> show variables like ’%version%’;

+————————-+—————————–+

| Variable_name           | Value                       |

+————————-+—————————–+

| protocol_version        | 10                          |

| version                 | 5.0.38-Debian_3netease5-log |

| version_comment         | Debian etch distribution    |

| version_compile_machine | x86_64                      |

| version_compile_os      | pc-linux-gnu                |

+————————-+—————————–+

5 rows in set (0.01 sec)

启动的日志信息

InnoDB: The first specified data file xxx//ibdata1 did not exist:

InnoDB: a new database to be created!

100609 19:18:50  InnoDB: Setting file xxx//ibdata1 size to 32 MB

InnoDB: Database physically writes the file full: wait…

100609 19:18:51  InnoDB: Log file xxx/ib_logfile0 did not exist: new to be created

InnoDB: Setting log file xxx/ib_logfile0 size to 128 MB

InnoDB: Database physically writes the file full: wait…

InnoDB: Progress in MB: 100

100609 19:18:53  InnoDB: Log file xxx/ib_logfile1 did not exist: new to be created

InnoDB: Setting log file xxxx/ib_logfile1 size to 128 MB

InnoDB: Database physically writes the file full: wait…

InnoDB: Progress in MB: 100

InnoDB: Doublewrite buffer not found: creating new

InnoDB: Doublewrite buffer created

InnoDB: Creating foreign key constraint system tables

InnoDB: Foreign key constraint system tables created

100609 19:18:57  InnoDB: Started; log sequence number 0 0

100609 19:18:57 [Note] /usr/sbin/mysqld: ready for connections.

Version: ’5.0.38-Debian_3netease5-log’  socket: xxx/mysqld.sock’  port: 4591  Debian etch distribution

MySQL 5.1 的版本信息

mysql>  show variables like ’%version%’;

+————————-+———————+

| Variable_name           | Value               |

+————————-+———————+

| innodb_version          | 1.0.6-unknown       | ##这个是InnoDB Plugin的新信息

| protocol_version        | 10                  |

| version                 | 5.1.45-log          |

| version_comment         | Source distribution |

| version_compile_machine | x86_64              |

| version_compile_os      | unknown-linux-gnu   |

+————————-+———————+

MySQL5.1 InnoDB Plugin启动日志信息，多了很多内容，下面逐一介绍

InnoDB: The InnoDB memory heap is disabled

InnoDB: Mutexes and rw_locks use GCC atomic builtins

InnoDB: The first specified data file ./ibdata1 did not exist:

InnoDB: a new database to be created!

100609 17:17:11  InnoDB: Setting file ./ibdata1 size to 512 MB

InnoDB: Database physically writes the file full: wait…

InnoDB: Progress in MB: 100 200 300 400 500

100609 17:17:15  InnoDB: Log file xxx/ib_logfile0 did not exist: new to be created

InnoDB: Setting log file  xxx/ib_logfile0 size to 512 MB

InnoDB: Database physically writes the file full: wait…

InnoDB: Progress in MB: 100 200 300 400 500

100609 17:17:21  InnoDB: Log file xxx/ib_logfile1 did not exist: new to be created

InnoDB: Setting log file xxxx/ib_logfile1 size to 512 MB

InnoDB: Database physically writes the file full: wait…

InnoDB: Progress in MB: 100 200 300 400 500

InnoDB: Doublewrite buffer not found: creating new

InnoDB: Doublewrite buffer created

InnoDB: Creating foreign key constraint system tables

InnoDB: Foreign key constraint system tables created

100609 17:17:26 InnoDB Plugin 1.0.6-unknown started; log sequence number 0

100609 17:17:28 [Note] Event Scheduler: Loaded 0 events

100609 17:17:28 [Note] /usr/xtradb/libexec/mysqld: ready for connections.

Version: ’5.1.45-log’  socket: ’xxx/mysqld.sock’  port: 4331  Source distribution

InnoDB: The InnoDB memory heap is disabled

是因为使用了操作系统的内存分配器,所以就禁用了InnoDB的内置内存分配器

mysql> show variables like ’%malloc%’;

+———————–+——-+

| Variable_name         | Value |

+———————–+——-+

| innodb_use_sys_malloc | ON    |

+———————–+——-+

1 row in set (0.00 sec)

而在5.0里面执行是为空结果的，关于这个innodb_use_sys_malloc，具体可以参阅InooDB的官方介绍

Mutexes and rw_locks use GCC atomic builtins

对比以往的版本,InnoDB Plugin 1.0.3之后的版本 使用了GCC atomic builtins来执行互斥和读写锁,性能会比以往使用的pthread_mutex_t要高效

参见 InnoDB Plugin: Enabling GCC atomic built-in functions for InnoDB rw-locks
InnoDB: The first specified data file ./ibdata1 did not exist:

InnoDB: a new database to be created!

第一次启动mysql，因为默认的是innodb引擎，所以会自动创建一个test

InnoDB: Doublewrite buffer not found: creating new

InnoDB: Doublewrite buffer created

什么是Doublewrite?

InnoDB将BP中的Dirty Page刷（flush）到磁盘上时，首先会将Page刷到InnoDB tablespace的一个区域中，我们称该区域为Double write Buffer。在向Double write Buffer写入成功后，再择机将数据拷贝到正在的数据文件对应的位置。

更多详细的信息可以看看performace blog 的对doublewrite的介绍

或者是苏普的对Doublewrite整理 InnoDB Double write

参考资料

InnoDB Plugin: Enabling GCC atomic built-in functions for InnoDB rw-locks

http://www.innodb.com/wp/support/tips/atomics/

InnoDB Plugin 1.0 for MySQL 5.1 User’s Guide

http://www.innodb.com/doc/innodb_plugin-1.0-doc-single/innodb-plugin.html#innodb-performance-use_sys_malloc

Innodb Double Write

http://www.mysqlperformanceblog.com/2006/08/04/innodb-double-write/#more-72

转载烦请保留原文连接 http://dbahacker.com/mysql/innodb-plugin-log-analyze

Category: MySQL  |  评论

MySQL Master-Slave的一个read log event 错误处理

2010年06月7日, 10:10 下午

Master-Slave的一个read log event 错误处理

现在简单介绍一下几个节点的情况
B服务器上面有一个B1节点，是MySQL5.0版本，普通InnoDB的表
C服务器上面有两个测试节点（C1，C2）,
它们是A服务器上面的A1节点的slave,C1，C2，都是使用XtraDB压缩功能的表

也就是 A为Master,有3个Slave(B1,C1,C2)

因为某些原因,C2的slave 复制出现了问题,而C1是没有问题的，当时候采取了以下措施
1.停止C1,C2的slave,使用mysqladmin shutdown来关闭C1,C2
2.删掉C2的数据目录等，只留下my.cnf
3.把C1的数据目录等都拷贝到C2目录下
4.启动C1,C2

这时候发现C2的mysqd.log 里面有了这样的内容

100608 12:44:14 [Note] Slave: received end packet from server, apparent master shutdown:
100608 12:44:14 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log ‘mysql-bin.007817′ at postion 86542498
100608 12:44:15 [Note] Slave I/O thread killed while reading event

同时在C2里面执行show slave status \G的时候,Seconds_behind_master = 会出现落后的秒数，但连续的执行show slave status 的时候就会看到有NULL的信息

mysqlperformanceblog有一篇文章是提到过是server-id的问题 http://www.mysqlperformanceblog.com/2008/06/04/confusing-mysql-replication-error-message/
但是我的3个slave的server-id都是不一样的，和master的server-id也是不一样的

最后的解决方法是尝试了一把把C2的server-id改成新的值，重启mysql,stop slave ;start slave;show slave status \G后发现复制正常了，不知道是否和我直接copy C1的数据库等文件过来是否有关

参考文献/文章

Confusing MySQL Replication Error Message http://www.mysqlperformanceblog.com/2008/06/04/confusing-mysql-replication-error-message/
Many Errors “Slave: received end packet from server” http://bugs.mysql.com/bug.php?id=9325

Category: MySQL  |  评论

[存储引擎基础知识]InnoDB与MyISAM的六大区别

2010年06月7日, 6:03 上午

这其实是09年总结的一篇文章，今天被一位朋友问到InnoDB有什么好处，一下子讲不清楚，现在把在自己另外一个博客的文章在这里重发一遍，主要是讲InnoDB和MyISAM的对比，从中可以看到InnoDB的很多好处，比如并发插入的时候行级锁等

本 文主要整理了Mysql 两大常用的存储引擎MyISAM，InnoDB的六大常见区别，来源于Mysql手册以及互联网的资料


InnoDB与Myisam的六大区别

MyISAM InnoDB
构 成上的区别： 每个MyISAM在磁盘上存储成三个文件。第一个 文件的名字以表的名字开始，扩展名指出文件类型。

.frm文件存储表定义。

数据文件的扩 展名为.MYD (MYData)。

索引文件的扩 展名是.MYI (MYIndex)。

 基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB 表的 大小只受限于操作系统文件的大小，一般为 2GB
事务处理上方面: MyISAM类型的表强调的是性能，其执行数 度比InnoDB类型更快，但是不提供事务支持 InnoDB提供事务支持事务，外部键等高级 数据库功能
SELECT UPDATE,INSERT，Delete操 作 如果执行大量的SELECT，MyISAM是更好的选择 1.如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表

2.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的 删除。

3.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用
对AUTO_INCREMENT的 操作

 每表一个AUTO_INCREMEN列的内部处理。

MyISAM为INSERT和UPDATE操 作自动更新这一列。这使得AUTO_INCREMENT列更快（至少10%）。在序列顶的值被删除之后就不 能再利用。(当AUTO_INCREMENT列被定义为多列索引的最后一列， 可以出现重使用从序列顶部删除的值的情况）。

AUTO_INCREMENT值可用ALTER TABLE或myisamch来重置

对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但 是在MyISAM表中，可以和其他字段一起建立联 合索引

更好和更快的auto_increment处理

 如果你为一个表指定AUTO_INCREMENT列，在数据词典里的InnoDB表句柄包含一个名为自动增长计数 器的计数器，它被用在为该列赋新值。

自动增长计数 器仅被存储在主内存中，而不是存在磁盘上

关于该计算器 的算法实现，请参考

AUTO_INCREMENT列 在InnoDB里 如何工作
表 的具体行数 select count(*) from table,MyISAM只要简单的读出保存好的行数，注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的 InnoDB 中不 保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行
锁 表锁 提供行锁(locking on row level)，提供与 Oracle 类型一致的不加锁读取(non-locking read in
SELECTs)，另外，InnoDB表的行锁也不是绝对的，如果在执 行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “%aaa%”
本文原出处为 http://www.dbahacker.com

转载烦请保留 链接
Category: MySQL  |  一条评论