MySQL 5.1.24rc + innodb plugin尝鲜

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 总体性能稍微好一些。

评论

我想大家应该都看了下面这篇文章了:
这个Plugin还是很激动人心的
http://www.mysqlperformanceblog.com/2008/04/23/real-life-use-case-for-ba...

老叶,您好啊,我是鸡鸡哥,刚google了一下--with-partition,结果就到了你的网站,呵呵

请教一下,如果需要--with-partition ,是否必须下载那个plugin??
如果需要,那么带参数时我看到你这里有--with-partition --without-plugin-archive --without-plugin-blackhole --with-big-tables \
如果我使用--with-plugins=partition,是否就等同于您哪里的--with-partition选项

不过,我再congfigure的时候,带了--with-partition会报warring
我用的版本是5.1.50 source code,这个版本的configure帮助里面没有with-partition的编译选项,不知道为什么,谢谢

还有,你帮我解释一下这个编译选项,有点不明白,呵呵
--with-plugins=PLUGIN[[[,PLUGIN..]]]
Plugins to include in mysqld. (default is: none)
Must be a configuration name or a comma separated
list of plugins.
Available configurations are: none max max-no-ndb
all.
Available plugins are: partition daemon_example
ftexample archive blackhole csv example federated
heap ibmdb2i innobase innodb_plugin myisam myisammrg
ndbcluster.
--without-plugin-PLUGIN Disable the named plugin from being built.
Otherwise, for plugins which are not selected for
inclusion in mysqld will be built dynamically (if
supported)
--with-plugin-PLUGIN Forces the named plugin to be linked into mysqld
statically.

叶子,我加你友情链接了,有空回加一下呵
http://www.hardwork.cn
鸡鸡哥的聪明伶俐的儿子和如花似玉的老婆