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 …

Check out this SlideShare Presentation: Inno Db Internals Inno Db File Formats And Source Code StructureView more presentations from MySQLConference.

…

Check out this SlideShare Presentation: Mastering InnoDB DiagnosticsView more presentations from guest8212a5.

…

I’ve been working with an application which does a lot of CREATE and DROP table for Innodb tables and we’ve discovered DROP TABLE can take a lot of time and when it happens a lot of other threads stall in “Opening Tables” State. Also contrary to my initial suspect benchmarking create/drop table was CPU bound rather than IO bound.

I’ve got an oprofile run and saw the following:

15753796 56.0725  no-vmlinux               no-vmlinux               /no-vmlinux
11834143 42.1213  mysqld                   mysqld                   buf_LRU_invalidate_tablespace
168823    0.6009  mysql                    mysql                    completion_hash_update(hashtable*, char*, unsigned int, char*)
53667     0.1910  oprofiled                oprofiled                /usr/bin/oprofiled
42116     0.1499  mysqld                   mysqld                   buf_calc_page_new_checksum
32107     0.1143  mysqld                   mysqld …

MySQL 5.5 introduced a number of InnoDB startup options and system variables. I did a quick Google search for each parameter mentioned on the official page and was a little surprised by the results (full results at the bottom of this post). There are a few clear variables with exponentially lower results indicating one of [...]

InnoDB’s checkpoint algorithm is not well documented. It is too complex to explain in even a long blog post, because to understand checkpoints, you need to understand a lot of other things that InnoDB does. I hope that explaining how InnoDB does checkpoints in high-level terms, with simplifications, will be helpful. A lot of the simplifications are because I do not want to explain the complexities of how the simple rules can be tweaked for optimization purposes, while not violating the ACID guarantees they enforce.

A bit of background: Gray and Reuter’s classic text on transaction processing introduced two types of checkpoints beginning on page 605. There is a sharp checkpoint, and there is a fuzzy checkpoint.

A sharp checkpoint is accomplished by flushing all modified pages for committed transactions to disk, and …

The MySQL performance team in Oracle has recently completed a series of benchmarks comparing Read / Write and Read-Only performance of MySQL 5.5 with the InnoDB and MyISAM storage engines.

Compared to MyISAM, InnoDB delivered 35x higher throughput on the Read / Write test and 5x higher throughput on the Read-Only test, with 90% scalability across 36 CPU cores.

A full analysis of results and MySQL configuration parameters are documented in a new whitepaper


In addition to the benchmark, the new whitepaper, also includes:
- A discussion of the use-cases for each storage engine
- Best practices for users considering the migration of existing applications from MyISAM to …

The MySQL performance team in Oracle has recently completed a series of benchmarks comparing Read / Write and Read-Only performance of MySQL 5.5 with the InnoDB and MyISAM storage engines.

Compared to MyISAM, InnoDB delivered 35x higher throughput on the Read / Write test and 5x higher throughput on the Read-Only test, with 90% scalability across 36 CPU cores.

A full analysis of results and MySQL configuration parameters are documented in a new whitepaper


In addition to the benchmark, the new whitepaper, also includes:
- A discussion of the use-cases for each storage engine
- Best practices for users considering the migration of existing applications from MyISAM to …

Some people don't probably know, but there is a difference between how indexes work in MyISAM and how they work in InnoDB, particularly when talking from the point of view of performance enhancement. Now since, InnoDB is starting to be widely used, it is important we understand how indexing works in InnoDB. Hence, the reason for this post!

This is mostly a reflection on a limitation in InnoDB that, in my opinion, has persisted for too long. I founded while reviewing the Amarok media player. The player uses MySQL in the backend, embedded or regular server, so it makes for a great source of real life data.
The IssueBy default, Amarok uses MyISAM tables. This means that if it crashes or stops unexpectedly (a logout while playing music may cause this), the latest updates to the DB are all lost. So I've been looking into using InnoDB instead to avoid loosing my playlists or player statistics.
The ProblemThe limitation that bothers me is this one: "Index key prefixes can be up to 767 bytes" which has been in place for several years.
Take this Amarok table for example:
CREATE TABLE urls (
    …