Table of contents
Introduction
Have you ever heard of InnoDB plugin for MySQL 5.1 ? The one you use in MySQL 5.1 is built-in InnoDB so why switching ? If you refer to InnoDB plugin documentation you would find:
- Viewing the InnoDB Plugin version number
- 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
- Performance and scalability enhancements:
- Faster locking for improved scalability
- Using operating system memory allocators
- Controlling InnoDB insert buffering
- Controlling adaptive hash indexing
- Changes regarding thread concurrency
- Changes in read ahead algorithm
- Multiple background I/O threads
- Group Commit
- Controlling master thread I/O rate
- Controlling flushing rate of dirty pages
- Using a portable PAUSE to InnoDB spin loop
- Control Over Spin Lock Polling
- Changing defaults of parameters
- Making Buffer Cache Scan Resistant
- Improvements to Crash Recovery Performance
- Other changes for flexibility, ease of use and reliability:
- Dynamic control of system configuration parameters
- TRUNCATE TABLE reclaims space
- InnoDB “strict mode”
- Control over statistics estimation
- Better error handling when dropping indexes
- More compact output of SHOW ENGINE INNODB MUTEX
- More Read Ahead Statistics
Oracle claim they have improved the following:
- Scale MySQL performance to fully utilize the computing power of the most modern multi-core architectures.
- Improve manageability provides DBA/Developer with better control/monitoring of key InnoDB performance and tuning parameters.
- Increase MySQL uptime and reliability.
In short it’s all about better scaling performance on multi-core processor with big amount of memory, many distinct I/O controllers. Nowadays it is not uncommon to have a Xeon processor with 4 cores and 16Gb of memory (memory is cheap on x86 platform) to run your database.
In the referenced documents you will find interesting charts showing the improvement done.
Activation
To activate you must download it from web site, there is a compatibility matrix versus your MySQL database server release. What is important to remember is that for MySQL release > 5.1.41 latest InnoDB plugin release is coming with certified binaries and except activating it you have nothing to do. Starting with MySQL 5.4.2 (so all 5.5.x release) there is nothing to do, InnoDB that is coming by default in the server is what was previously called InnoDB plugin.
With 5.1.49sp1 certified binaries installed in /mysql/software/5.1.49sp1 basedir directory:
[root@server1 5.1.49sp1]# cd /mysql/software/5.1.49sp1/ [root@server1 5.1.49sp1]# ll lib/plugin/*innodb* -rw-r--r-- 1 mysql dba 7599770 Sep 29 03:41 lib/plugin/ha_innodb_plugin.a -rwxr-xr-x 1 mysql dba 1054 Sep 29 03:41 lib/plugin/ha_innodb_plugin.la lrwxrwxrwx 1 mysql dba 25 Feb 9 17:30 lib/plugin/ha_innodb_plugin.so -> ha_innodb_plugin.so.0.0.0 lrwxrwxrwx 1 mysql dba 25 Feb 9 17:30 lib/plugin/ha_innodb_plugin.so.0 -> ha_innodb_plugin.so.0.0.0 -rwxr-xr-x 1 mysql dba 5327190 Sep 29 03:41 lib/plugin/ha_innodb_plugin.so.0.0.0 |
Change you my.cnf file from:
# InnoDB innodb_additional_mem_pool_size = 16M innodb_data_home_dir = /mysql/data01/ innodb_data_file_path = ibdata1:10M:autoextend:max:1G innodb_log_group_home_dir = /mysql/data01/ innodb_buffer_pool_size = 100M innodb_log_file_size = 25M innodb_log_buffer_size = 8M innodb_autoextend_increment = 10 |
To:
# InnoDB loose_innodb_additional_mem_pool_size = 16M loose_innodb_data_home_dir = /mysql/data01/ loose_innodb_data_file_path = ibdata1:10M:autoextend:max:1G loose_innodb_log_group_home_dir = /mysql/data01/ loose_innodb_buffer_pool_size = 100M loose_innodb_log_file_size = 25M loose_innodb_log_buffer_size = 8M loose_innodb_autoextend_increment = 10 # innodb plugin ignore_builtin_innodb plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so innodb_file_format=barracuda innodb_strict_mode=1 |
Then at MySQL server level you should move from:
mysql> show plugins; +------------+----------+----------------+---------+---------+ | Name | Status | Type | Library | License | +------------+----------+----------------+---------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | +------------+----------+----------------+---------+---------+ 10 rows in set (0.00 sec) |
To:
mysql> show plugins; +---------------------+----------+--------------------+---------------------+---------+ | Name | Status | Type | Library | License | +---------------------+----------+--------------------+---------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | ha_innodb_plugin.so | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL | | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL | | INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so | GPL | +---------------------+----------+--------------------+---------------------+---------+ 17 rows in set (0.00 sec) |
You can get InnoDB Plugin version with:
mysql> SELECT @@innodb_version; +------------------+ | @@innodb_version | +------------------+ | 1.0.10 | +------------------+ 1 row in set (0.00 sec) |
And get list of new variables with:
mysql> show variables like '%inno%'; +---------------------------------+-------------------------------+ | Variable_name | Value | +---------------------------------+-------------------------------+ | have_innodb | YES | | ignore_builtin_innodb | ON | | innodb_adaptive_flushing | ON | | innodb_adaptive_hash_index | ON | | innodb_additional_mem_pool_size | 16777216 | | innodb_autoextend_increment | 10 | | innodb_autoinc_lock_mode | 1 | | innodb_buffer_pool_size | 104857600 | | innodb_change_buffering | inserts | | innodb_checksums | ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:10M:autoextend:max:1G | | innodb_data_home_dir | /mysql/data01/ | | innodb_doublewrite | ON | | innodb_fast_shutdown | 1 | | innodb_file_format | Barracuda | | innodb_file_format_check | Barracuda | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_io_capacity | 200 | | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 8388608 | | innodb_log_file_size | 26214400 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | /mysql/data01/ | | innodb_max_dirty_pages_pct | 75 | | innodb_max_purge_lag | 0 | | innodb_mirrored_log_groups | 1 | | innodb_old_blocks_pct | 37 | | innodb_old_blocks_time | 0 | | innodb_open_files | 300 | | innodb_read_ahead_threshold | 56 | | innodb_read_io_threads | 4 | | innodb_replication_delay | 0 | | innodb_rollback_on_timeout | OFF | | innodb_spin_wait_delay | 6 | | innodb_stats_on_metadata | ON | | innodb_stats_sample_pages | 8 | | innodb_strict_mode | ON | | innodb_support_xa | ON | | innodb_sync_spin_loops | 30 | | innodb_table_locks | ON | | innodb_thread_concurrency | 0 | | innodb_thread_sleep_delay | 10000 | | innodb_use_sys_malloc | ON | | innodb_version | 1.0.10 | | innodb_write_io_threads | 4 | +---------------------------------+-------------------------------+ 51 rows in set (0.00 sec) |
The one I rate quite interesting are:
- innodb_io_capacity
- innodb_read_io_threads
- innodb_write_io_threads
References
