flyinweb's blog

一、XtraDB存储引擎

　　XtraDB是由Percona开发的一款MySQL数据库的高性能存储引擎，其目的是用来代替InnoDB存储引擎，可用于需要更高性能的环境。XtraDB可以看作是InnoDB存储引擎的增强版本，它在InnoDB上进行了大量的修改和patched，它完全兼容InnoDB，且提供了很多InnoDB不具备的有用的功能。

　　例如：在多核CPU上面的性能和伸缩性要更好；对于内存的分配和使用也要更好；也解除了InnoDB的很多限制；提供了比InnoDB更多的配置和性能监控参数。

　　Percona自己对新引擎的描述：“Percona XtraDB includes all of InnoDB's robust, reliable ACID-compliant design and advanced MVCC architecture, and builds on that solid foundation with more features, more tunability, more metrics, and more scalability. In particular, it is designed to scale better on many cores, to use memory more efficiently, and to be more convenient and useful. The new features are especially designed to alleviate some of InnoDB's limitations.”

　　对于高负载的MySQL应用来说，如果不需要MySQL官方技术支持的话，完全可以使用XtraDB来代替InnoDB存储引擎。此外，Percona还提供了打了补丁的MySQL版本，这些补丁对MySQL很多方面进行了改进，性能提高，增加更多监控参数等等。据说JavaEye的数据库就是使用了Percona的版本。

　　当前XtraDB的最新版本是1.0.4-8(release-8)

二、InnoDB的”替代品”：Percona XtraDB

XtraDB是Percona Team在InnoDB的基础上开发出来的一个更加强大的存储引擎，它百分百的兼容InnoDB，我们可以用XtraDB来替代为MySQL的发展做出历史性贡献的InnoDB，新的XtraDB引擎将具有更加强大的性能优势，以及良好的扩展性和一些新特性。

Percona于2008年12月16日正式对外公布Percona XtraDB存储引擎，当时版本为1.0.2-1。在09年有相继发布了release 3/4/5/8，目前最新的版本为Percona-XtraDB 1.0.4-8。

安装XtraDB存储引擎

XtraDB的安装有这么几种：一是现成的RPMS包安装，不过这些包仅限于x86_64位RedHat/CentOS 4/5系统下，而且MySQL的版本为5.1.30；还有就是通过整体编译MySQL源码包的安装,即将新的XtraDB引擎的代码整合到MySQL的源码中，进行重新编译。前面这两种都需要重新搭建MySQL数据库，例外还有一种，就是动态的将XtraDB加载进正在运行MySQL中，这个看上去很符合生产的要求。

下面我们就来介绍Linux系统下，源码包的安装方法，具体步骤如下：

1.下载MySQL的源码安装包。http://dev.mysql.com/downloads/mysql/5.1.html#source

2.下载Percona XtraDB的源码安装包。https://launchpad.net/percona-xtradb/+download

3.编译安装MySQL

shell>tar -xvf mysql-5.1.35.tar.gz
shell>cd mysql-5.1.35/storage
shell>tar -xvf percona-xtradb-1.0.4-8.tar.gz
shell>rm -rf innobase
shell>mv percona-xtradb-1.0.4-8 innobase
shell>cd ..
shell>./configure -prefix=/usr/local/mysql -with-plugins=innobase && make && make install
shell> cp support-files/my-medium.cnf /etc/my.cnf
shell> cd /usr/local/mysql
shell> chown -R mysql.mysql .
shell> bin/mysql_install_db -user=mysql
shell> chown -R root .
shell> chown -R mysql var
shell> bin/mysqld_safe -user=mysql &

4.验证XtraDB引擎的100%兼容Innodb。

shell> mysql -uroot -p < eiken_uat_backup.090803230101(将生产数据库导入新的MySQL)

mysql> show create table user_role\G  
*************************** 1. row ***************************  
Table: user_role  
Create Table: CREATE TABLE `user_role` (  
`user_name` varchar(100) NOT NULL,  
`role_name` varchar(25) NOT NULL,  
…omit….  
) ENGINE=InnoDB DEFAULT CHARSET=latin1  
1 row in set (0.00 sec)  

(经测试，所有应用一切正常。)

mysql> create table xtradb_tb(id int(3),engine_name char(120)) engine=innodb; （创建一个使用XtraDB引擎的表）  
Query OK, 0 rows affected (0.00 sec)  
mysql> show create table xtradb_tb\G  
*************************** 1. row ***************************  
Table: xtradb_tb  
Create Table: CREATE TABLE `xtradb_tb` (  
`id` int(3) DEFAULT NULL,  
`engine_name` char(120) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=latin1  
1 row in set (0.00 sec) 

（NOTE：如果你要使用XtraDB这个引擎，engine的值依然要设置成InnoDB。）

比较

最后，我们简要比较一下XtraDB与InnoDB之间都有哪些变化。分别连上配有不同引擎的数据库服务。比较如下：

1.INFORMATION_SCHEMA中的表数量发生明显变化。

XtraDB ：42个（增加了13项INNODB开头的表和一个XTRADB_ENHANCEMENTS表，改表中记录逐一介绍了新的XtraDB对InnoDB所做的各项改进）

InnoDB ：28个

2. SHOW INNODB STATUS命令的输出内容的增加。

其中，BUFFER POOL AND MEMORY的内容变得更加丰富。

同时，在输出中，我们还会发现，XtraDB在线程的投入上也有很大提高，在InnoDB中FILE I/O的线程仅为4个，而在XtraDB中你会发现为10个，分别是读、写的线程数都各自增加为4个，这对于性能会有很大提升。

 

三、InnoDB, InnoDB-plugin vs XtraDB on fast storage

To continue fun with FusionIO cards, I wanted to check how MySQL / InnoDB performs here. For benchmark I took MySQL 5.1.42 with built-in InnoDB, InnoDB-plugin 1.0.6, and XtraDB 1.0.6-9 ( InnoDB with Percona patches).
As benchmark engine I used tpcc-mysql with 1000 warehouses ( which gives around 90GB of data + indexes) on my workhourse Dell PowerEdge R900 ( details about box ).

On storage configuration: FusionIO 160GB SLC and 320GB MLC cards are configured in software RAID0 to store InnoDB datafiles. For InnoDB logs and system tablespace I used partition on regular RAID10 with regular hard drives, here I followed Yoshinori Matsunobu’s recommendations http://yoshinorimatsunobu.blogspot.com/2009/05/tables-on-ssd-redobinlogsystem.html and taking fact that FusionIO is not perfect for sequential writes http://www.mysqlperformanceblog.com/2010/01/11/fusionio-320gb-mlc-benchmarks/

Full results I put on page http://www.percona.com/docs/wiki/benchmark:fusionio:mysql:start, here are my thoughts and interesting facts.

First, chart with results for InnoDB vs InnoDB-plugin during runs (values are in new order transactions per minute, more is better) :

As you see InnoDB-plugin is doing much better here, it allows to utilize multiple IO threads,
which as we saw ( http://www.mysqlperformanceblog.com/2010/01/11/fusionio-320gb-mlc-benchmarks/ ) is necessary to get most throughput from FusionIO.

Also you may see from graph some waves for InnoDB-plugin. Here we observe innodb_adaptive_flushing in action (which is ON by default), and I think innodb_adaptive_flushing in InnoDB-plugin is not quite balanced, it may do overaggressive flushing, when it is not necessary.

But looking on CPU stats (see graph later), I guess InnoDB-plugin spends most time in buffer_pool mutex, contention here is not fully resolved yet in InnoDB-plugin.

Now, let’s take XtraDB. In additional to multiple IO threads, we have patch to decrease contention on buffer_pool mutex, plus separate purge thread. Also we use different adaptive_checkpoint algorithm.

The results are:

So I guess buffer_pool improvements play here for XtraDB, and looking on summary result:

• InnoDB 9439.316 NOTPM
• InnoDB-plugin-1.0.6 15299.333 NOTPM
• XtraDB-1.0.6-9 26160.551 NOTPM

InnoDB-plugin is 1.6x times better InnoDB, and XtraDB is 1.7x times better InnoDB-plugin.

Now on CPU usage and disk utilization.

Disk throughput:

CPU (user) usage:

Even with improvements, XtraDB performs less 150MB/s in disk writes (from benchmarks we
saw FusionIO can do much more) and with 45-50% of idle CPU.

I assume we still see significant contentions inside XtraDB, and there big room for improvements. As for InnoDB-plugin, I’d wish InnoDB team makes some actions on buffer_pool mutex problem.

Finally I wanted to check what if we put innodb transactional logs and system tablespace on FusionIO also, there is graph for that:

It is not so bad, with final result 23038.283 NOTPM, it is only about 12% worse than with logs on separate partition.

And to make reference point, I run the same but with all files on RAID10 with regular disks,
the graph is there:

with final result: 2873.783 NOTPM ( about 88% worse than all files on FusionIO)

To summarize

• MySQL InnoDB/InnoDB-plugin/XtraDB is not fully able to utilize throughput of FusionIO. XtraDB is doing better job with internal contention, but much more can be done.
• Still you can have very impressive performance improvement in IO-intensive or IO-bound workloads. You may want to use InnoDB-plugin or XtraDB to get better results.
• Putting logs on separate partition may be good idea, but only if you have possibility to do that. Making special setup for that may be not worth improvements