1. 前言
oracle 收购 innobase 后，沉寂了将近2年，innodb开发团队一直是在修改bug等，也没见到什么动作。
前几天，他们终于宣布， 发布最新的innodb plugin for MySQL 5.1，这个plugin其实等同于innodb引擎，只是可以plugin的方式安装，也可以自己编译。
前几天，他们终于宣布， 发布最新的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 总体性能稍微好一些。
本文转自叶金荣51CTO博客，原文链接：http://blog.51cto.com/imysql/308626，如需转载请自行联系原作者
