1. 前言

oracle 收购 innobase 后，沉寂了将近2年，innodb开发团队一直是在修改bug等，也没见到什么动作。
前几天，他们终于宣布，发布最新的innodb plugin for MySQL 5.1，这个plugin其实等同于innodb引擎，只是可以plugin的方式安装，也可以自己编译。

innodb plugin 的一些主要新特性有：

• Fast index creation: add or drop indexes without copying the data
• Data compression: shrink tables, to significantly reduce storage and i/o
• New row format: fully off-page storage of long BLOB, TEXT, and VARCHAR columns
• File format management: protects upward and downward compatibility
• INFORMATION_SCHEMA tables: information about compression and locking
• Other changes for flexibility, ease of use and reliability
• Dynamic control of innodb_file_per_table
• TRUNCATE TABLE re-creates the *.ibd file to reclaim space
• “Strict mode” to prevent mistakes

其实第一个特性应该是：Fast index creation: add or drop (secondary) indexes without copying the data 才确切。

下载地址为：http://www.innodb.com/support/downloads/download-innodb-plugin，可以下载直接可用的 .so 文件，也可以下载源码自己编译，这里，我是自己下载源码回来编译。

2. 安装

[@s1.yejr.com ~]# tar zxf mysql-5.1.24-rc.tar.gz

[@s1.yejr.com ~]# tar zxf innodb_plugin-1.0.0-5.1.tar.gz

[@s1.yejr.com ~]# cd mysql-5.1.24-rc/storage/innobase

[@s1.yejr.com ~]# cp -rf ../../../innodb_plugin-1.0.0-5.1/* .

检查 innobase 目录下的 Makefile，原来的 MKDIR_P 有问题，需要修改 MKDIR_P 值

MKDIR_P = @MKDIR_P@

为

MKDIR_P = mkdir -p --

开始编译、安装

#设置一下 CFLAGS 和 CXXFLAGS，尤其要注意打开 HAVE_DLOPEN 选项
[@s1.yejr.com ~]# CFLAGS='-O2 -DHAVE_DLOPEN -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 \
-fexceptions -fstack-protector –param=ssp-buffer-size=4 -m64 -mtune=generic'

[@s1.yejr.com ~]# CXXFLAGS='-O2 -DHAVE_DLOPEN -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 \
-fexceptions -fstack-protector –param=ssp-buffer-size=4 -m64 -mtune=generic'

[@s1.yejr.com ~]# cd ../../

[@s1.yejr.com ~]# ./configure --prefix=/usr/local/mysql --with-extra-charsets=complex \
--enable-thread-safe-client --enable-local-infile --localstatedir=/home/mysql \
--libexecdir=/usr/local/mysql/bin/ --with-embedded-server --with-innodb \
--with-partition --without-plugin-archive --without-plugin-blackhole --with-big-tables \
--disable-shared --without-man --without-doc  --with-client-ldflags=-all-static \
--with-mysqld-ldflags=-all-static  --without-geometry --without-bench \
--without-test && make && make install-strip

安装完毕。

3. 配置

以下是 innodb 相关的几个主要配置：

transaction_isolation = READ-COMMITTED
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 12G
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table=1
innodb_rollback_on_timeout

其他的不再细说。

4. 测试

4.1 功能测试

测试在线增删索引：

mysql> select count(*) from sbtest;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.27 sec)

mysql> desc sbtest;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| k     | int(10) unsigned | NO   | MUL | 0       |                |
| c     | char(120)        | NO   |     |         |                |
| pad   | char(60)         | NO   |     |         |                |
+-------+------------------+------+-----+---------+----------------+

mysql> alter table sbtest add index (c , pad);
Query OK, 0 rows affected (16.96 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sbtest drop index c;
Query OK, 0 rows affected (0.52 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sbtest add index (k);
Query OK, 0 rows affected (4.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sbtest drop index k;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.2 性能测试

4.2.1 测试 4 个线程并发的情况

[@s1.yejr.com ~]# sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 \
--mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-db=test --num-threads=4 run

OLTP test statistics:
    queries performed:
        read:                            140000
        write:                           50000
        other:                           20000
        total:                           210000
    transactions:                        10000  (1189.56 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190000 (22601.68 per sec.)
    other operations:                    20000  (2379.12 per sec.)

Test execution summary:
    total time:                          8.4065s
    total number of events:              10000
    total time taken by event execution: 33.5053
    per-request statistics:
         min:                            0.0025s
         avg:                            0.0034s
         max:                            0.0150s
         approx.  95 percentile:         0.0044s

Threads fairness:
    events (avg/stddev):           2500.0000/96.33
    execution time (avg/stddev):   8.3763/0.00

 
4.2.2 测试 8 个线程并发的情况

[@s1.yejr.com ~]# sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 \
--mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-db=test --num-threads=8 run

OLTP test statistics:
    queries performed:
        read:                            140014
        write:                           50005
        other:                           20002
        total:                           210021
    transactions:                        10001  (1689.60 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190019 (32102.34 per sec.)
    other operations:                    20002  (3379.19 per sec.)

Test execution summary:
    total time:                          5.9192s
    total number of events:              10001
    total time taken by event execution: 47.1426
    per-request statistics:
         min:                            0.0031s
         avg:                            0.0047s
         max:                            0.0465s
         approx.  95 percentile:         0.0069s

Threads fairness:
    events (avg/stddev):           1250.1250/16.00
    execution time (avg/stddev):   5.8928/0.00

 
4.2.3 测试 16 个线程并发的情况

[@s1.yejr.com ~]# sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 \
--mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-db=test --num-threads=16 run

OLTP test statistics:
    queries performed:
        read:                            140000
        write:                           50000
        other:                           20000
        total:                           210000
    transactions:                        10000  (1401.51 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190000 (26628.65 per sec.)
    other operations:                    20000  (2803.02 per sec.)

Test execution summary:
    total time:                          7.1352s
    total number of events:              10000
    total time taken by event execution: 113.8354
    per-request statistics:
         min:                            0.0037s
         avg:                            0.0114s
         max:                            0.0467s
         approx.  95 percentile:         0.0174s

Threads fairness:
    events (avg/stddev):           625.0000/7.13
    execution time (avg/stddev):   7.1147/0.00

 
可以看到，并发线程设定为 8 的时候，测试结果最佳。

5. 和 mysql 5.0.45 对比一下，看看谁的性能更好点

5.1 功能测试

测试在线增删索引：

mysql> select count(*) from sbtest;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.27 sec)

mysql> desc sbtest;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| k     | int(10) unsigned | NO   | MUL | 0       |                |
| c     | char(120)        | NO   |     |         |                |
| pad   | char(60)         | NO   |     |         |                |
+-------+------------------+------+-----+---------+----------------+

mysql> alter table sbtest add index (c , pad);
Query OK, 1000000 rows affected (25.65 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> alter table sbtest drop index c;
Query OK, 1000000 rows affected (14.75 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> alter table sbtest add index (k);
Query OK, 1000000 rows affected (15.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sbtest drop index k;
Query OK, 1000000 rows affected (11.85 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

 
在增、删索引时，可以看到产生了临时文件，相当于完全重建了一次数据表。

-rw-rw----  1 mysql mysql        8632 Apr 23 16:44 #sql-62b_1.frm
-rw-rw----  1 mysql mysql    83886080 Apr 23 16:44 #sql-62b_1.ibd

而在 5.1.24rc + innodb plugin 的情况却只产生了类似 #sql-62b_1.frm 的临时文件，表记录排序则放在内存中完成；只有对数据表的主键(或作为 cluster index 的那个索引)进行修改时，才会重建整个表。

5.2 性能测试

5.2.1 测试 4 个线程并发的情况

[@s1.yejr.com ~]# sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 \
--mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-db=test --num-threads=4 run

OLTP test statistics:
    queries performed:
        read:                            140000
        write:                           50000
        other:                           20000
        total:                           210000
    transactions:                        10000  (1226.83 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190000 (23309.82 per sec.)
    other operations:                    20000  (2453.67 per sec.)

Test execution summary:
    total time:                          8.1511s
    total number of events:              10000
    total time taken by event execution: 32.4817
    per-request statistics:
         min:                            0.0024s
         avg:                            0.0032s
         max:                            0.0100s
         approx.  95 percentile:         0.0043s

Threads fairness:
    events (avg/stddev):           2500.0000/57.68
    execution time (avg/stddev):   8.1204/0.00

 
5.2.2 测试 8 个线程并发的情况

[@s1.yejr.com ~]# sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 \
--mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-db=test --num-threads=8 run

OLTP test statistics:
    queries performed:
        read:                            140000
        write:                           50000
        other:                           20000
        total:                           210000
    transactions:                        10000  (1774.08 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190000 (33707.44 per sec.)
    other operations:                    20000  (3548.15 per sec.)

Test execution summary:
    total time:                          5.6367s
    total number of events:              10000
    total time taken by event execution: 44.8875
    per-request statistics:
         min:                            0.0029s
         avg:                            0.0045s
         max:                            0.0162s
         approx.  95 percentile:         0.0066s

Threads fairness:
    events (avg/stddev):           1250.0000/12.97
    execution time (avg/stddev):   5.6109/0.00

 
5.2.3 测试 16 个线程并发的情况

[@s1.yejr.com ~]# sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 \
--mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-db=test --num-threads=16 run

OLTP test statistics:
    queries performed:
        read:                            140014
        write:                           50005
        other:                           20002
        total:                           210021
    transactions:                        10001  (1416.24 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190019 (26908.63 per sec.)
    other operations:                    20002  (2832.49 per sec.)

Test execution summary:
    total time:                          7.0616s
    total number of events:              10001
    total time taken by event execution: 112.6000
    per-request statistics:
         min:                            0.0035s
         avg:                            0.0113s
         max:                            0.0265s
         approx.  95 percentile:         0.0174s

Threads fairness:
    events (avg/stddev):           625.0625/8.90
    execution time (avg/stddev):   7.0375/0.00

可以看到，mysql 5.0.45 下的 innodb 会比 5.1.24rc 下的 innodb 总体性能稍微好一些。