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