You are here: Home / Archives for innodb plugin

Reasons to use MySQL 5.5 Presentation

by

I recently gave a presentation at the New York Effective MySQL Meetup on the new features of, and some of the compelling reasons to upgrade to MySQL 5.5. There are also a number of new MySQL variables that can have a dramatic effect on performance in a highly transactional environment, innodb_buffer_pool_instances and innodb_purge_threads are just two to consider.

For more information on all the new variables, status, reserved words and benchmarks of new features you can Download Presentation Slides.

Leveraging the InnoDB Plugin

by

Beginning with MySQL 5.1 as an additional plugin and included by default in MySQL 5.5 the InnoDB plugin includes many performance improvements. To leverage the support of new file formats however a very important setting is necessary.

#my.cnf
[mysqld]
innodb_file_per_table

The use of innodb_file_per_table with an existing system or during an upgrade to 5.1 or 5.5 requires a complete reload of your database to use effectively. In summary.

  • Backup all InnoDB tables via mysqldump
  • Drop InnoDB tables
  • Verify InnoDB not used
  • Stop MySQL
  • Enable innodb_file_per_table & simplified innodb_data_file_path (if applicable)
  • Remove ibdata? files
  • Start MySQL
  • Create Tables
  • Reload Data
  • Verify InnoDB Operation

      • The primary reason is we are moving from using a common tablespace to a tablespace per table. InnoDB wil not shrink the common tablespace so this process is necessary in order to purge the diskspace currently being used. You should also reduce your innodb_data_file_path options if specified. For example if currently set to :

      innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend

      I would suggest you change to

      innodb_data_file_path = ibdata1:100M:autoextend

      InnoDB still requires this common tablespace, however now each table has it’s own disk file the volume required is signficantly less.

Five reasons to upgrade to MySQL 5.5

by

Updated Nov 2011. Check out my Reasons to use MySQL 5.5 Presentation for more in-depth information about installing/configuring and using MySQL 5.5

I have been looking forward to the general availability (GA) release of MySQL 5.5 since is was publically announced in September that we would see this in 2010. While I already have a production client with 5.5.7rc, the badge of general availability is a great way to promote why environments should consider moving to using MySQL 5.5. Here is my quick short list of why I’d promote moving to MySQL 5.5.

1. Improved integration

The first significant improvement is that InnoDB is now again firmly a default included storage engine. The InnoDB plugin 1.1.x is now the builtin version of the engine, not a plugin version. Also the 1.1.x version has continued improvements over the 1.0.x version available as an included but not enabled plugin in current MySQL 5.1.x versions. Removing the complexity for end users over the choice of InnoDB and the necessary configuration changes is a great simplification. The introduction in the InnoDB plugin 1.0.x of a new file format (known as Barracuda) and additional new row formats such as dynamic and compressed should also be considered for improved I/O performance. Greater eduction and customer comparison results are still needed here.

2. Improved performance

“Improved scalability on multi-core CPUs” is the tag line Oracle touts and while the marketing graphs do show improved performance with huge numbers of threads, it is the continued work at addressing the support for large cores that is the key to have MySQL perform on newer H/W. MySQL was always known as the software that runs on low cost commodity H/W, however it also needs to work well on new hardware architectures.

InnoDB has many improvements, you can see a long list at 13.7.7. Performance and Scalability Enhancements. I have seen an upgrade from 5.0 to 5.5 significantly reduce contention of queries. The help of multiple buffer pool instances, multiple rollback segments, additional threads and functional mutexes have all reduced high throughput mutex contention.

3. Improved recoverability

While I have yet to use Semisynchronous replication in a production situation I see this as a continued progression to improving the recoverability and resilience of MySQL. This was work first seen by the Google Patch in MySQL 5.0 several years ago. The lack of a single unbreakable backup strategy is the number 1 overlooked feature in MySQL as reported by customer advisory groups in the past. The move to making InnoDB the default storage engine is another small step to improved recoverability in a default MySQL setting.

4. Improved instrumentation

A lack of detailed instrumentation rates number 2 on my list of areas of improvement needed with MySQL. With 5.5 there are more continued improvements. While some of these are very technical for high end analysis (e.g. the performance_schema), simple improvements in common instrumentation can help you identify triggers for poor performance. There is a lot more information available in the INFORMATION_SCHEMA. Here is a great example via SHOW PROCESSLIST that shows a 4 second lock in the Query Cache (QC) that until now was not possible to summarize easily what happens in the Query Cache black box.

... | Query   |    4 | Waiting for query cache lock | REPLACE INTO statistics_w SET IP = ...

Identification is the first necessary step to continued improvement.

My work with the PERFORMANCE_SCHEMA has been limited to experimenting such as described by Tracking IO with performance_schema however a recent discussion with Mark Leith highlighted that with the performance_schema it will be possible to determine the exact size of disk based temporary tables created by the MySQL kernel for example.

5. Improved development methodology

We have heard a lot of noise from a vocal few that Oracle will not do right by MySQL, the product, the community, the OEM providers. While I believe it is too early to tell the long term plans of MySQL under Oracle, I believe that action speaks louder than words. Oracle has made a number of commitments publically, Oracle are investing in resources to improve the development of the product and It’s clear the targeting of the Microsoft OS highlights MySQL has a future as a SQL Server competitor. Oracle brings a great wealth of experience, resources and processes and hopefully that will be invested into MySQL.

There is a good list of What Is New in MySQL 5.5 as part of the current development, features I’ve not mention include SIGNAL/RESIGNAL, change in the default storage engine, partitioning additions and pluggable MySQL authentication capability.

The MySQL documentation team also do a great job. An example of new work is a great comparison of Options and Variables from all 5.x versions shown on a single page for comparison.

The way forward with MySQL

Oracle as steward of MySQL is here to stay. While people still rumble about it, everybody has to get over it. For the end consumer little will change, for clients that use MySQL Enterprise Support that are affected by the changes in policy, many other viable support options exist. What is unknown and my single point of issue with the EU 10 point Statement is what will happen with OEM providers. This affects the financial viability of a number of providers and while I’m not directly involved I do not want to see this good work lost to the MySQL community.

Points of uncertainty include what features will be developed next, will they be pay only features, will they be available to enterprise customers and not community customers, what is the frequency of community releases etc. These still need good PR from Oracle for the MySQL community. For those concerned, Oracle has made continued investment in acquired RDBMS products including BerkeleyDB and InnoDB and RDB. Oracle RDB for example was acquired in 2004 and is still being actively developed and supported. As long as MySQL is profitable, we will see continued releases.

About the Author

Ronald Bradford is a well respected expert in the MySQL community. Ronald is internationally recognized as an Oracle ACE Director in MySQL, the highest industry recognition and is also a published author of Expert PHP and MySQL. He is available for MySQL consulting work now.

Couldn't load plugin named 'innodb'

by

As part of reviewing storage engines for my work on the upcoming Expert PHP and MySQL book, I finally had an excuse to try out the InnoDB Plugin for MySQL which is now conveniently included with MySQL 5.1 since 5.1.38.

Following the MySQL 5.1 Reference Manual instructions at 13.6. The InnoDB Storage Engine I included the bare minimum as documented to my my.cnf.

[mysqld]
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so ;innodb_trx=ha_innodb_plugin.so ;innodb_locks=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

However to my misfortune the following error occured.

091212 17:45:14 [ERROR] Can't open shared library '/home/rbradfor/mysql/mysql-5.1.41-linux-x86_64-glibc23/lib/plugin/ha_innodb_plugin.so ' (errno: 22 cannot open shared object file: No such file or directory)
091212 17:45:14 [ERROR] Couldn't load plugin named 'innodb' with soname 'ha_innodb_plugin.so '.

I double checked the files were indeed included in the binary release before undertaking a google search, to reveal the official Innodb Plugin Manual page but still not much to go on with the information ” Can’t open shared library library_name — Diagnose the cause from the following message details.”

Back to re-reading the error message, as referenced, I observed there is a trailing space ‘ ‘ ater the plugin name. Observing that I had indeed had this the my.cnf, the result of joining the lines from the MySQL manual which were broken down for presentation means (as noted).

Removed the space, and viola it works.

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_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     |
+---------------------+----------+--------------------+---------------------+---------+

I would question that this level of pedantic checking is unnecessary. A simple trim on semi-colon ‘;’ separated values could be an easy fix.