经过一段时间的测试,准备把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
Innodb Double Write
http://www.mysqlperformanceblog.com/2006/08/04/innodb-double-write/#more-72