Posts Tagged ‘innodb_io_capacity’

Added a Table of Contents

Wednesday, November 9th, 2011

Not a big deal, but I just added a “Table of Contents” page to my blog to make finding older articles much easier.

I noticed most of my posts are quite lengthy, and it can take a bit of searching/clicking to find an older entry. So unless you happen to recall the ‘month/year’ it was published, which I don’t even remember that, then hopefully this will help.

Really simple, and looks just like this:

My hopes are that this will aid in making some posts easier to find (such as ones about InnoDB Recovery, Recovery with an Individual .ibd, Proxy-related articles, Error-related articles, How-to posts, and so forth).

You can see the full “table of contents” here:

http://www.chriscalender.com/?page_id=399

Happy reading :)

 
 
 

IOPS, innodb_io_capacity, and the InnoDB Plugin

Tuesday, May 17th, 2011

In the InnoDB plugin, a new variable was added named innodb_io_capacity, which controls the maximum number of I/O operations per second that InnoDB will perform (which includes the flushing rate of dirty pages as well as the insert buffer (ibuf) batch size).

First off, let me just say this is a welcome addition (an addition provided by the Google Team, fwiw).

However, before this was configurable, the internal hard-coded value for this was 100. But when this became configurable, the default was increased to 200.

For many systems, this is not an issue (i.e., the overall system can perform 200 IOPS).

However, there are still many disks (which is often the bottleneck for IOPS) out there that are not quite at 200 IOPS. Here is a rough estimate based on some external resources which have already documented IOPS for various disk drives (*see links at end for further reference):

15k rpm: 180-210 IOPS
10k rpm: 130-150 IOPS
7200 rpm: 80-100 IOPS
5400 rpm: 50-80 IOPS

So if you are running with a single disk, no raid, and it’s a slower disk (say 5,400 rpm – 10,000 rpm range), then this default value of 200 might be too high for you.

In which case, reverting back to 100 should help (note you cannot reduce this lower than 100). The manual mentions reverting back to 100 to achieve the “old behavior”. However, it neglects to explicitly mention why this might be necessary.

Also, since this option controls the insert buffer batch size, the number of merges per background I/O loop also doubles (5% of innodb_io_capacity, or 5% of 100 in non-plugin).

What this means is that double the writes could be occurring, leaving you with a 1:1 insert to merge ratio, hence much more pages could be written to the secondary indexes, thus hurting performance (in this specific case obvious due to slave lag) and undermining the purpose of the insert buffer in the first place. See bug #48341 for more details on this specific case, as well as this following post from the Facebook Team.

And for those interested in even more details concerning innodb_io_capacity, James and Dimitri both have some excellent comments at the following 2 links, respectively:

http://bugs.mysql.com/bug.php?id=52393
http://dimitrik.free.fr/blog/archives/07-01-2010_07-31-2010.html

*Basis for IOPS general measurements (only to be used as general guidelines):

http://wiki.horde.org/HardwareRequirements
http://en.wikipedia.org/wiki/IOPS#Examples
http://storagearchitect.blogspot.com/2008/09/how-many-iops.html

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