Introduction
InnoDB is the leading transactional storage engine for MySQL. MySQL is the world’s most popular open source database. InnoDB is the only transactional storage engine that is used by millions of users worldwide and it is the key engine that is behind many popular web sites along with non-transactional engine MyISAM.
Innobase released a new plugin-1.0 during the MySQL user conference and expo 2008. This plugin allows number of new useful features like:
Faster secondary index creation
New row storage formats
Data compression
New Information schema tables
Truncate table space reclamation
Strict mode for error checking
New configuration parameters
Compatibility changes
The purpose of this article is to explain how to get started with all these new features and to explore InnoDB performance in further detail.
How to Install the InnoDB Plugin
The InnoDB Plugin is compatible with MySQL 5.1, and Innobase distributes the plugin in both source and binary form. You can build the plugin from source by replacing the files in /storage/innobase with the plugin source by either building it statically (configure options –with-innodb or –with-plugin-innodb) into the MySQL server (mysqld) or dynamically (by default). If you install the binary form of the plugin (ha_innodb.so or ha_innodb.dll), then you need to install the InnoDB Plugin and Information Schema tables before they can be used. For how to install the dynamic plugin, refer to the documentation.
Faster Secondary Index
Until now, to add or drop any index on an existing table, InnoDB would first create a temporary table with the new indexes and then copy the whole data to a temp table and rename the temp table with the original table (both .frm and .ibd) to re-create the clustered B-tree index structure. If the table had many rows (a typical case in a majority of the user base), then this process will take lot of time; sometimes even hours. That affects the overall performance of the server. This is also one of the main reasons a lot of people keep a maintenance server. They use it just for changing the indexes and/or schema and promote that as the main server without affecting performance.
The new InnoDB Plugin does not copy the data to create or drop a secondary index. That will really help to scale in the above cases. Thanks to the Innobase team, who saved lot of DBA’s time. Also, remember that even with the new plugin, if you want to add or drop a primary index, then it still follows the old fashioned approach of re-creating the table.
Consider the following table with 10,000,000 (10M) rows. Here is the performance of add/drop index in both 5.1.23 without plugin and 5.1.24 with plugin.
CREATE TABLE `sbtest` ( `id` int(10) unsigned NOT NULL, `k` int(10) unsigned NOT NULL DEFAULT ‘0′, `c` char(120) NOT NULL DEFAULT ”, `pad` char(60) NOT NULL DEFAULT ”, PRIMARY KEY (`id`), KEY `k` (`k`) ) ENGINE=InnoDB mysql> SHOW TABLE STATUS LIKE ’sbtest’\G *************************** 1. row *************************** Name: sbtest Engine: InnoDB Version: 10 Row_format: Compact Rows: 10000060 Avg_row_length: 224 Data_length: 2247098368 Max_data_length: 0 Index_length: 137035776 Data_free: 5632 Auto_increment: NULL Create_time: 2008-04-24 08:22:17 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment:
Without InnoDB plugin:
mysql> drop index k on sbtest; Query OK, 10000000 rows affected (2 min 4.88 sec) Records: 10000000 Duplicates: 0 Warnings: 0 mysql> create index k on sbtest(k); Query OK, 10000000 rows affected (2 min 38.85 sec) Records: 10000000 Duplicates: 0 Warnings: 0
With InnoDB plugin:
mysql> drop index k on sbtest; Query OK, 0 rows affected (0.74 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create index k on sbtest(k); Query OK, 0 rows affected (52.59 sec) Records: 0 Duplicates: 0 Warnings: 0
Drop index is really fast now compared with create index, because dropping a secondary index requires only data dictionary changes.. If you need to add or drop multiple indexes, then use a single statement to do that, so that the operations are grouped together. For example:
ALTER TABLE sbtest DROP INDEX index1, DROP INDEX index2;
ALTER TABLE sbtest ADD INDEX index1, ADD INDEX index2;
Row formats
InnoDB stores all data in 16K data pages comprising a B-tree index, called a clustered index or primary key index. For all variable length-columns (such as VARCHAR, TEXT, BLOB); InnoDB only stores the first 768 bytes in the index record within the B-tree node, and the rest in the overflow pages. This is termed as ‘COMPACT‘ or ‘REDUNDANT‘ row format and the file format is now named as ‘Antelope‘.
The InnoDB Plugin introduces two more new row-formats called ‘DYNAMIC‘ and ‘COMPRESSED‘ using the new named file format ‘Barracuda‘. The file format can be controlled by another new configuration parameter ‘innodb_file_format’. As opposed to Compact format, when a row does not fit entirely in the B-tree index node, with Dynamic or Compressed format InnoDB stores variable length column values fully in overflow pages, and the clustered index record contains only a 20-byte pointer to the overflow page. This can help performance since more rows fit per index page, making it more efficient (especially when you are not accessing the long column values).
Even with the new plugin, COMPACT is the default row format for backward compatibility. To create a table with a different row format, use ROW_FORMAT= as part of the create table statement or use alter table to change from one to another as shown in the example below:
mysql> set global innodb_file_format=‘Barracuda’; Query OK, 0 rows affected (0.00 sec) mysql> create table testformat(c1 int primary key)engine=innodb; Query OK, 0 rows affected (0.01 sec) mysql> show create table testformat; +————+——————————————————————————————————————+ | Table | Create Table | +————+——————————————————————————————————————+ | testformat | CREATE TABLE `testformat` ( `c1` int(11) NOT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +————+——————————————————————————————————————+ 1 row in set (0.00 sec) mysql> show table status like ‘testformat’; +————+——–+———+————+——+—————-+————-+—————–+————–+———–+—————-+———————+————-+————+——————-+———-+—————-+———+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +————+——–+———+————+——+—————-+————-+—————–+————–+———–+—————-+———————+————-+————+——————-+———-+—————-+———+ | testformat | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2008-04-26 16:34:51 | NULL | NULL | latin1_swedish_ci | NULL | | | +————+——–+———+————+——+—————-+————-+—————–+————–+———–+—————-+———————+————-+————+——————-+———-+—————-+———+ 1 row in set (0.00 sec) mysql> drop table testformat; Query OK, 0 rows affected (0.04 sec) mysql> create table testformat(c1 int primary key)engine=innodb row_format=Dynamic; Query OK, 0 rows affected (0.00 sec) mysql> show create table testformat; +————+————————————————————————————————————————————-+ | Table | Create Table | +————+————————————————————————————————————————————-+ | testformat | CREATE TABLE `testformat` ( `c1` int(11) NOT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC | +————+————————————————————————————————————————————-+ 1 row in set (0.00 sec) mysql> show table status like ‘testformat’; +————+——–+———+————+——+—————-+————-+—————–+————–+———–+—————-+———————+————-+————+——————-+———-+——————–+———+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +————+——–+———+————+——+—————-+————-+—————–+————–+———–+—————-+———————+————-+————+——————-+———-+——————–+———+ | testformat | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2008-04-26 16:34:13 | NULL | NULL | latin1_swedish_ci | NULL | row_format=DYNAMIC | | +————+——–+———+————+——+—————-+————-+—————–+————–+———–+—————-+———————+————-+————+——————-+———-+——————–+———+ 1 row in set (0.00 sec) mysql> alter table testformat row_format=Compressed; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table testformat; +————+—————————————————————————————————————————————-+ | Table | Create Table | +————+—————————————————————————————————————————————-+ | testformat | CREATE TABLE `testformat` ( `c1` int(11) NOT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED | +————+—————————————————————————————————————————————-+ 1 row in set (0.00 sec) mysql> show table status like ‘testformat’; +————+——–+———+————+——+—————-+————-+—————–+————–+———–+—————-+———————+————-+————+——————-+———-+———————–+———+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +————+——–+———+————+——+—————-+————-+—————–+————–+———–+—————-+———————+————-+————+——————-+———-+———————–+———+ | testformat | InnoDB | 10 | Compressed | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2008-04-26 16:37:23 | NULL | NULL | latin1_swedish_ci | NULL | row_format=COMPRESSED | | +————+——–+———+————+——+—————-+————-+—————–+————–+———–+—————-+———————+————-+————+——————-+———-+———————–+———+ 1 row in set (0.00 sec)
But one needs to have Barracuda as the file format in order to create Dynamic or Compressed row formats, else it will be defaulted to Compact with a warning as shown below:
mysql> set global innodb_file_format=‘antelope’; Query OK, 0 rows affected (0.00 sec) mysql> show variables like ‘%format%’; +———————+——————-+ | Variable_name | Value | +———————+——————-+ | innodb_file_format | Antelope | +———————+——————-+ mysql> create table t3(c1 int) engine=InnoDB ROW_FORMAT=Dynamic; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> show warnings; +———+——+——————————————————————–+ | Level | Code | Message | +———+——+——————————————————————–+ | Warning | 1478 | InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. | | Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT. | +———+——+——————————————————————–+ 2 rows in set (0.00 sec)
As pointed in the documentation, MySQL should consider changing the term ROW_FORMAT as TABLE_FORMAT as that would be more appropriate.
Data Compression
One of the best among the new features in the InnoDB Plugin is the data compression. Now InnoDB enables the data compression when one uses row format as ‘Compressed‘. InnoDB allows compression of 16K pages to 1K, 2K, 4K, 8K and 16K. This can be controlled by KEY_BLOCK_SIZE, which defaults to 8K. If you specify KEY_BLOCK_SIZE then it is implicitly compressed row format.
Here is a way to create a compressed table:
mysql> create table testformat(c1 int primary key, c2 varchar(20))engine=InnoDB ROW_FORMAT=Compressed; Query OK, 0 rows affected (0.01 sec) mysql> insert into testformat values(10,‘venu’),(20,‘anuganti’),(30,‘innodb’),(40,‘yahoo‘),(50,‘mysql’); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> show create table testformat; +————+————————————————————————————————————————————————————————-+ | Table | Create Table | +————+————————————————————————————————————————————————————————-+ | testformat | CREATE TABLE `testformat` ( `c1` int(11) NOT NULL, `c2` varchar(20) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED | +————+————————————————————————————————————————————————————————-+ 1 row in set (0.00 sec) mysql> show table status like ‘testformat’; +————+——–+———+————+——+—————-+————-+—————–+————–+———–+—————-+———————+————-+————+——————-+———-+———————–+———+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +————+——–+———+————+——+—————-+————-+—————–+————–+———–+—————-+———————+————-+————+——————-+———-+———————–+———+ | testformat | InnoDB | 10 | Compressed | 5 | 3276 | 16384 | 0 | 0 | 0 | NULL | 2008-04-26 17:05:17 | NULL | NULL | latin1_swedish_ci | NULL | row_format=COMPRESSED | | +————+——–+———+————+——+—————-+————-+—————–+————–+———–+—————-+———————+————-+————+——————-+———-+———————–+———+ 1 row in set (0.00 sec)
Here is the comparison of .ibd file sizes for both compressed and uncompressed formats for the above simple 4 row table.
[vanugant@test]$ ls -al /mysql/data/sbtest/ -rw-rw–– 1 mysql mysql 8582 Apr 26 17:12 testformat.frm -rw-rw–– 1 mysql mysql 65536 Apr 26 17:12 testformat.ibd and the same table without compression, looks like: [vanugant@test]$ sudo ls -al /mysql/data/test/ -rw-rw–– 1 mysql mysql 8582 Apr 26 17:10 testformat.frm -rw-rw–– 1 mysql mysql 98304 Apr 26 17:10 testformat.ibd
The above scenario is a simple case, but consider a table with many rows. Here is the .ibd file size for various row formats on a table with 10M rows.
|
Compact |
Compressed (8K) |
Compressed (4K) |
Compressed (2K) |
Dynamic |
|
2.3G |
1.2G |
592M |
324M |
2.3G |
As you can see from the above table, compression can save lots of disk space and reduce the number reads and writes needed to access the data. On an IO bound server, compression can be a key asset to improve performance.
Performance of Various Row Formats
Here is the performance of various row formats with threads ranging from 1 to 512 with concurrent reads and writes on the following table with 10M rows:
| sbtest | CREATE TABLE `sbtest` ( `id` int(10) unsigned NOT NULL, `k` int(10) unsigned NOT NULL DEFAULT ‘0′, `c` char(120) NOT NULL DEFAULT ”, `pad` char(60) NOT NULL DEFAULT ”, PRIMARY KEY (`id`), KEY `k` (`k`) ) ENGINE=InnoDB
For the complete benchmark results of various row formats and compression, check the following two blog posts from me on venublog.com:
Performance Key observations:
The load/insert time increases with smaller compressed page sizes: 8K, 4K, 2K, 1K.
When the whole data set can fit in memory; then compression will relatively slowdown the transaction rate
When the whole data set can not fit in memory, then compression seems to be a right choice (8K in particular)
Creation of a secondary index can still take lot of time when the compression is enabled. The 8K compression is much better and rather close to Compact/Dynamic; but 4K/2K seems to take ~6 times more time than 8K
The best compression in terms of performance is 8K; but it may vary based on the table structure and data and its distribution
4K compression in general seems to be slower when compared with 8K or any other row_format, and the InnoDB team is looking in to it.
Information Schema Tables
The InnoDB Plugin also added the following seven new Information Schema tables to provide more statistics about compression, transactions and locks.
INNODB_CMP
INNODB_CMP_RESET
INNODB_CMPMEM
INNODB_CMPMEM_RESET
INNODB_TRX
INNODB_LOCKS
INNODB_LOCK_WAITS
INNODB_LOCKS and INNODB_LOCK_WAITS schema tables are yet another cool feature that you will love. With these tables you can see what is happening inside the engine to know what is waiting on what. For example, here are the stats when one session selects a table for update and another session executes a update statement and gets into a wait state till the first one releases its lock (or until lock_wait_timeout)
mysql> select * from information_schema.innodb_lock_waits; +——————-+——————-+—————–+——————+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +——————-+——————-+—————–+——————+ | 1836AE5 | 1836AE5:27:3:3 | 1836AE4 | 1836AE4:27:3:3 | +——————-+——————-+—————–+——————+ 1 row in set (0.00 sec) mysql> select * from information_schema.innodb_trx; +———+———–+———————+———————–+———————+————+———————+————————————————+ | trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id| trx_query | +———+———–+———————+———————–+———————+————+———————+————————————————+ | 1836AE5 | LOCK WAIT | 2008-04-26 17:41:42 | 1836AE5:27:3:3 | 2008-04-26 17:41:42 | 2 | 13| update testformat set c2=‘updated’ where c1=20 | | 1836AE4 | RUNNING | 2008-04-26 17:40:55 | NULL | NULL | 2 | 11| NULL | +———+———–+———————+———————–+———————+————+———————+————————————————+ 2 rows in set (0.00 sec) mysql> select * from information_schema.innodb_locks; +—————-+————-+———–+———–+———————+————+————+———–+———-+———–+ | lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data | +—————-+————-+———–+———–+———————+————+————+———–+———-+———–+ | 1836AE5:27:3:3 | 1836AE5 | X | RECORD | `test`.`testformat` | `PRIMARY` | 27 | 3 | 3 | 20 | | 1836AE4:27:3:3 | 1836AE4 | X | RECORD | `test`.`testformat` | `PRIMARY` | 27 | 3 | 3 | 20 | +—————-+————-+———–+———–+———————+————+————+———–+———-+———–+ 2 rows in set (0.00 sec)
All of these information schema tables provide in-depth statistics on what is happening inside the engine.
Truncate Table Space
In addition, InnoDB has extended the TRUNCATE TABLE functionality; which now allows the space to be used by the operating system, by re-creating the .ibd file when innodb_file_per_table is set. Previously, the space was available only to the InnoDB engine. For example, here is an example from Linux, where truncate table with ~5G size immediately returns the space to OS:
[vanugant@deleted:compress compress-test]$ sudo ls -al /mysql/data/sbtest/ -rw-rw–– 1 mysql mysql 8632 Apr 28 20:28 sbtest.frm -rw-rw–– 1 mysql mysql 4844421120 Apr 28 20:35 sbtest.ibd [vanugant@deleted:compress compress-test]$ df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/sys-root 388G 20G 350G 6% / mysql> truncate table sbtest; Query OK, 20000105 rows affected (5.92 sec) mysql> exit Bye [vanugant@bm11:compress compress-test]$ df Filesystem Size Used Avail Use% Mounted on /dev/mapper/sys-root 388G 15G 354G 4% /
InnoDB Strict Mode
The InnoDB Plugin also supports a new configuration parameter called ‘innodb_strict_mode‘; by setting it to on or 1; all warnings (not really) will be treated as errors as shown below:
mysql> set global innodb_file_format=‘Antelope’; Query OK, 0 rows affected (0.00 sec) mysql> set session innodb_strict_mode=on; Query OK, 0 rows affected (0.00 sec) mysql> create table test(c1 int primary key) engine=InnoDB ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8; ERROR 1005 (HY000): Can‘t create table ‘test.test’ (errno: 1478) mysql> set session innodb_strict_mode=off; Query OK, 0 rows affected (0.00 sec) mysql> create table test(c1 int primary key) engine=InnoDB ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8; Query OK, 0 rows affected, 4 warnings (0.01 sec) mysql> show warnings; +———+——+———————————————————————–+ | Level | Code | Message | +———+——+———————————————————————–+ | Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. | | Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=8. | | Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. | | Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT. | +———+——+———————————————————————–+ 4 rows in set (0.00 sec)
New Configuration Parameters
Here is the list of all new configuration parameters introduced with the plugin:
innodb_strict_mode to treat all warnings as errors
innodb_file_format supports Barracuda to support compression and dynamic row formats, along with old Antelope format for compact row format.
innodb_file_per_table is now dynamic and one can set it through SET GLOBAL command
Compatibility Changes
One should aware of the following two compatibility changes in the new plugin:
fdatasync is no more supported as the innodb_flush_method from MySQL 5.1.24 onwards.
The tables which are created using compressed or dynamic row format using the Barracuda file format will not work with any older MySQL/InnoDB versions (version < 5.1.24)