Posts Tagged ‘innodb_adaptive_flushing’

Ease of Switching to the InnoDB Plugin and the Numerous Benefits

Thursday, July 1st, 2010

In my last post, I discussed how to troubleshoot InnoDB locks using the new InnoDB Plugin’s new Information Schema tables.

However, that got me to thinking about how many MySQL 5.1 users who have still not switched to use the plugin as opposed to the built-in version of InnoDB.

There are many advantages to using the plugin as opposed to the built-in version (aside from just the new I_S tables, and more importantly, numerous performance enhancements), and it’s breeze to set up, so I wanted to provide a quick start guide to using the new InnoDB plugin.

Note that the InnoDB plugin is now GA (as of plugin version 1.0.7) and as of 5.1.38 it is included with the MySQL downloads (it just needs to be enabled).

If you’re running pre-5.1.38, then you may want to consider upgrading to a newer MySQL anyways (5.1.48 is the latest as of this posting). But if not, then you’ll need to download the plugin and extract it.

But if you’re using 5.1.38 or newer, then just take the following steps:

1. Update your my.cnf (or my.ini) by adding the following 2 lines (note the dll should already exist in BASEDIRlibplugin):

ignore_builtin_innodb
plugin-load=innodb=ha_innodb_plugin.dll;innodb_trx=ha_innodb_plugin.dll;
                innodb_locks=ha_innodb_plugin.dll;innodb_lock_waits=ha_innodb_plugin.dll;
                innodb_cmp=ha_innodb_plugin.dll;innodb_cmp_reset=ha_innodb_plugin.dll;
                innodb_cmpmem=ha_innodb_plugin.dll;innodb_cmpmem_reset=ha_innodb_plugin.dll

2. Restart mysqld

3. Now you can check some various commands to verify it’s enabled.

I’ll post the BEFORE and AFTER, so you can see the differences:

SHOW GLOBAL VARIABLES LIKE ‘%innodb%’ Output:

BEFORE Plugin:

mysql> show global variables like '%innodb%';
+-----------------------------------------+------------------------+
| Variable_name                           | Value                  |
+-----------------------------------------+------------------------+
| have_innodb                             | YES                    |
| ignore_builtin_innodb                   | OFF                    |
| innodb_adaptive_hash_index              | ON                     |
| innodb_additional_mem_pool_size         | 2097152                |
| innodb_autoextend_increment             | 8                      |
| innodb_autoinc_lock_mode                | 1                      |
| innodb_buffer_pool_size                 | 49283072               |
| innodb_checksums                        | ON                     |
| innodb_commit_concurrency               | 0                      |
| innodb_concurrency_tickets              | 500                    |
| innodb_data_file_path                   | ibdata1:10M:autoextend |
| innodb_data_home_dir                    |                        |
| innodb_doublewrite                      | ON                     |
| innodb_fast_shutdown                    | 1                      |
| innodb_file_io_threads                  | 4                      |
| innodb_file_per_table                   | ON                     |
| innodb_flush_log_at_trx_commit          | 1                      |
| innodb_flush_method                     |                        |
| innodb_force_recovery                   | 0                      |
| innodb_lock_wait_timeout                | 50                     |
| innodb_locks_unsafe_for_binlog          | OFF                    |
| innodb_log_buffer_size                  | 1048576                |
| innodb_log_file_size                    | 25165824               |
| innodb_log_files_in_group               | 2                      |
| innodb_log_group_home_dir               | .                     |
| innodb_max_dirty_pages_pct              | 90                     |
| innodb_max_purge_lag                    | 0                      |
| innodb_mirrored_log_groups              | 1                      |
| innodb_open_files                       | 300                    |
| innodb_rollback_on_timeout              | OFF                    |
| innodb_stats_on_metadata                | ON                     |
| innodb_support_xa                       | ON                     |
| innodb_sync_spin_loops                  | 20                     |
| innodb_table_locks                      | ON                     |
| innodb_thread_concurrency               | 8                      |
| innodb_thread_sleep_delay               | 10000                  |
| innodb_use_legacy_cardinality_algorithm | ON                     |
+-----------------------------------------+------------------------+
37 rows in set (0.00 sec)

AFTER Plugin:

mysql> show global variables like '%innodb%';
+---------------------------------+------------------------+
| Variable_name                   | Value                  |
+---------------------------------+------------------------+
| have_innodb                     | YES                    |
| ignore_builtin_innodb           | ON                     |
| innodb_adaptive_flushing        | ON                     |
| innodb_adaptive_hash_index      | ON                     |
| innodb_additional_mem_pool_size | 2097152                |
| innodb_autoextend_increment     | 8                      |
| innodb_autoinc_lock_mode        | 1                      |
| innodb_buffer_pool_size         | 49283072               |
| innodb_change_buffering         | inserts                |
| innodb_checksums                | ON                     |
| innodb_commit_concurrency       | 0                      |
| innodb_concurrency_tickets      | 500                    |
| innodb_data_file_path           | ibdata1:10M:autoextend |
| innodb_data_home_dir            |                        |
| innodb_doublewrite              | ON                     |
| innodb_fast_shutdown            | 1                      |
| innodb_file_format              | Antelope               |
| innodb_file_format_check        | Antelope               |
| innodb_file_per_table           | ON                     |
| 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          | 1048576                |
| innodb_log_file_size            | 25165824               |
| innodb_log_files_in_group       | 2                      |
| innodb_log_group_home_dir       | .                     |
| 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              | OFF                    |
| innodb_support_xa               | ON                     |
| innodb_sync_spin_loops          | 30                     |
| innodb_table_locks              | ON                     |
| innodb_thread_concurrency       | 8                      |
| innodb_thread_sleep_delay       | 10000                  |
| innodb_use_sys_malloc           | ON                     |
| innodb_version                  | 1.0.9                  |
| innodb_write_io_threads         | 4                      |
+---------------------------------+------------------------+
51 rows in set (0.00 sec)

In the above, with the plugin, you can see the value of ignore_builtin_innodb is now “ON”.

Also, the innodb_file_format and innodb_file_format_check is “Antelope” for this particular version of the plugin.

There are also a number of new variables, such as innodb_adaptive_flushing, innodb_change_buffering, innodb_file_format, innodb_file_format_check, innodb_io_capacity, innodb_old_blocks_pct, innodb_old_blocks_time, innodb_read_ahead_threshold, innodb_read_io_threads, innodb_replication_delay, innodb_spin_wait_delay, innodb_stats_sample_pages, innodb_stats_sample_pages, innodb_strict_mode, innodb_use_sys_malloc, innodb_version, and innodb_write_io_threads.

The only two missing from the new plugin are innodb_file_io_threads and innodb_use_legacy_cardinality_algorithm.

SELECT * FROM PLUGINS Output:

BEFORE Plugin:

mysql> use information_schema;
Database changed
mysql> select * from plugins;
+-------------+----------------+---------------+----------------+---------------------+----------------+------------------------+--------------------------------------------+--------------------------------------------------------------------------+----------------+
| PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_TYPE    | PLUGIN_TYPE_VERSION | PLUGIN_LIBRARY | PLUGIN_LIBRARY_VERSION | PLUGIN_AUTHOR                              | PLUGIN_DESCRIPTION                                                       | PLUGIN_LICENSE |
+-------------+----------------+---------------+----------------+---------------------+----------------+------------------------+--------------------------------------------+--------------------------------------------------------------------------+----------------+
| binlog      | 1.0            | ACTIVE        | STORAGE ENGINE | 50148.0             | NULL           | NULL                   | MySQL AB                                   | This is a pseudo storage engine to represent the binlog in a transaction | GPL            |
| ARCHIVE     | 3.0            | ACTIVE        | STORAGE ENGINE | 50148.0             | NULL           | NULL                   | Brian Aker, MySQL AB                       | Archive storage engine                                                   | GPL            |
| BLACKHOLE   | 1.0            | ACTIVE        | STORAGE ENGINE | 50148.0             | NULL           | NULL                   | MySQL AB                                   | /dev/null storage engine (anything you write to it disappears)           | GPL            |
| CSV         | 1.0            | ACTIVE        | STORAGE ENGINE | 50148.0             | NULL           | NULL                   | Brian Aker, MySQL AB                       | CSV storage engine                                                       | GPL            |
| FEDERATED   | 1.0            | ACTIVE        | STORAGE ENGINE | 50148.0             | NULL           | NULL                   | Patrick Galbraith and Brian Aker, MySQL AB | Federated MySQL storage engine                                           | GPL            |
| MEMORY      | 1.0            | ACTIVE        | STORAGE ENGINE | 50148.0             | NULL           | NULL                   | MySQL AB                                   | Hash based, stored in memory, useful for temporary tables                | GPL            |
| InnoDB      | 1.0            | ACTIVE        | STORAGE ENGINE | 50148.0             | NULL           | NULL                   | Innobase OY                                | Supports transactions, row-level locking, and foreign keys               | GPL            |
| MyISAM      | 1.0            | ACTIVE        | STORAGE ENGINE | 50148.0             | NULL           | NULL                   | MySQL AB                                   | Default engine as of MySQL 3.23 with great performance                   | GPL            |
| MRG_MYISAM  | 1.0            | ACTIVE        | STORAGE ENGINE | 50148.0             | NULL           | NULL                   | MySQL AB                                   | Collection of identical MyISAM tables                                    | GPL            |
| partition   | 1.0            | ACTIVE        | STORAGE ENGINE | 50148.0             | NULL           | NULL                   | Mikael Ronstrom, MySQL AB                  | Partition Storage Engine Helper                                          | GPL            |
+-------------+----------------+---------------+----------------+---------------------+----------------+------------------------+--------------------------------------------+--------------------------------------------------------------------------+----------------+
10 rows in set (0.01 sec)

AFTER Plugin:

mysql> use information_schema;
Database changed
mysql> select * from plugins;
+---------------------+----------------+---------------+--------------------+---------------------+----------------------+------------------------+--------------------------------------------+--------------------------------------------------------------------------+----------------+
| PLUGIN_NAME         | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_TYPE        | PLUGIN_TYPE_VERSION | PLUGIN_LIBRARY       | PLUGIN_LIBRARY_VERSION | PLUGIN_AUTHOR                              | PLUGIN_DESCRIPTION                                                       | PLUGIN_LICENSE |
+---------------------+----------------+---------------+--------------------+---------------------+----------------------+------------------------+--------------------------------------------+--------------------------------------------------------------------------+----------------+
| binlog              | 1.0            | ACTIVE        | STORAGE ENGINE     | 50148.0             | NULL                 | NULL                   | MySQL AB                                   | This is a pseudo storage engine to represent the binlog in a transaction | GPL            |
| ARCHIVE             | 3.0            | ACTIVE        | STORAGE ENGINE     | 50148.0             | NULL                 | NULL                   | Brian Aker, MySQL AB                       | Archive storage engine                                                   | GPL            |
| BLACKHOLE           | 1.0            | ACTIVE        | STORAGE ENGINE     | 50148.0             | NULL                 | NULL                   | MySQL AB                                   | /dev/null storage engine (anything you write to it disappears)           | GPL            |
| CSV                 | 1.0            | ACTIVE        | STORAGE ENGINE     | 50148.0             | NULL                 | NULL                   | Brian Aker, MySQL AB                       | CSV storage engine                                                       | GPL            |
| FEDERATED           | 1.0            | ACTIVE        | STORAGE ENGINE     | 50148.0             | NULL                 | NULL                   | Patrick Galbraith and Brian Aker, MySQL AB | Federated MySQL storage engine                                           | GPL            |
| MEMORY              | 1.0            | ACTIVE        | STORAGE ENGINE     | 50148.0             | NULL                 | NULL                   | MySQL AB                                   | Hash based, stored in memory, useful for temporary tables                | GPL            |
| MyISAM              | 1.0            | ACTIVE        | STORAGE ENGINE     | 50148.0             | NULL                 | NULL                   | MySQL AB                                   | Default engine as of MySQL 3.23 with great performance                   | GPL            |
| MRG_MYISAM          | 1.0            | ACTIVE        | STORAGE ENGINE     | 50148.0             | NULL                 | NULL                   | MySQL AB                                   | Collection of identical MyISAM tables                                    | GPL            |
| partition           | 1.0            | ACTIVE        | STORAGE ENGINE     | 50148.0             | NULL                 | NULL                   | Mikael Ronstrom, MySQL AB                  | Partition Storage Engine Helper                                          | GPL            |
| InnoDB              | 1.0            | ACTIVE        | STORAGE ENGINE     | 50148.0             | ha_innodb_plugin.dll | 1.0                    | Innobase Oy                                | Supports transactions, row-level locking, and foreign keys               | GPL            |
| INNODB_TRX          | 1.0            | ACTIVE        | INFORMATION SCHEMA | 50148.0             | ha_innodb_plugin.dll | 1.0                    | Innobase Oy                                | InnoDB transactions                                                      | GPL            |
| INNODB_LOCKS        | 1.0            | ACTIVE        | INFORMATION SCHEMA | 50148.0             | ha_innodb_plugin.dll | 1.0                    | Innobase Oy                                | InnoDB conflicting locks                                                 | GPL            |
| INNODB_LOCK_WAITS   | 1.0            | ACTIVE        | INFORMATION SCHEMA | 50148.0             | ha_innodb_plugin.dll | 1.0                    | Innobase Oy                                | InnoDB which lock is blocking which                                      | GPL            |
| INNODB_CMP          | 1.0            | ACTIVE        | INFORMATION SCHEMA | 50148.0             | ha_innodb_plugin.dll | 1.0                    | Innobase Oy                                | Statistics for the InnoDB compression                                    | GPL            |
| INNODB_CMP_RESET    | 1.0            | ACTIVE        | INFORMATION SCHEMA | 50148.0             | ha_innodb_plugin.dll | 1.0                    | Innobase Oy                                | Statistics for the InnoDB compression; reset cumulated counts            | GPL            |
| INNODB_CMPMEM       | 1.0            | ACTIVE        | INFORMATION SCHEMA | 50148.0             | ha_innodb_plugin.dll | 1.0                    | Innobase Oy                                | Statistics for the InnoDB compressed buffer pool                         | GPL            |
| INNODB_CMPMEM_RESET | 1.0            | ACTIVE        | INFORMATION SCHEMA | 50148.0             | ha_innodb_plugin.dll | 1.0                    | Innobase Oy                                | Statistics for the InnoDB compressed buffer pool; reset cumulated counts | GPL            |
+---------------------+----------------+---------------+--------------------+---------------------+----------------------+------------------------+--------------------------------------------+--------------------------------------------------------------------------+----------------+
17 rows in set (0.01 sec)

In the above output, you can see the values for the InnoDB Plugin have changed. For instance, the PLUGIN_LIBRARY now shows ha_innodb_plugin.dll. Additionally, you can see the new INFORMATION SCHEMA plugins listed, which are INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS, INNODB_CMP, INNODB_CMP_RESET, INNODB_CMPMEM, INNODB_CMPMEM_RESET.

Lastly, I’ll just post the output from SHOW ENGINE INNODB STATUS from the new Plugin only. There’s new information provided here as well (see “Pages made young 0, not young 0″ amongst numerous others).

mysql> show engine innodb statusG
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
100622 19:22:06 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 39 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1 1_second, 1 sleeps, 0 10_second, 2 background, 2 flush
srv_master_thread log flush and writes: 1
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3, signal count 3
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 3, OS waits 3; RW-excl spins 0, OS waits 0
Spin rounds per wait: 0.00 mutex, 30.00 RW-shared, 0.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 20E00
Purge done for trx's n:o < 20C18 undo n:o < 0
History list length 11
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started, OS thread id 8812
MySQL thread id 1, query id 7 localhost 127.0.0.1 root
show engine innodb status
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
27 OS file reads, 7 OS file writes, 7 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 2174, seg size 2176,
0 inserts, 0 merged recs, 0 merges
Hash table size 195193, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 745473833
Log flushed up to   745473833
Last checkpoint at  745473833
0 pending log writes, 0 pending chkp writes
10 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 50184192; in additional pool allocated 0
Dictionary memory allocated 20888
Buffer pool size   3008
Free buffers       2992
Database pages     16
Old database pages 0
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 16, created 0, written 1
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s
LRU len: 16, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 9800, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)

So in conclusion, it's very easy to enable the InnoDB Plugin (add 2 lines to your my.cnf file and restart). So if you're running 5.1, you should definitely consider enablig the InnoDB plugin if you haven't already, and you will gain the following numerous benefits (posted from the manual):

  • 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

http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-plugin-introduction-features.html


Click here to give this article a "Thumbs Up" on Planet MySQL !

  


Period Panties by Period Panteez Menstrual Underwear Menstruation PMS Panty