We’re pleased to let you know that the 2nd episode of our “Meet The MySQL Experts” podcast series, where Oracle engineers share their expertise, is now available.

Wei-Chen Chiu interviews InnoDB team member Inaam Rana who comes back on the new InnoDB features and improvements delivered in MySQL 5.5 and in the first development milestone release of MySQL 5.6. Inaam also talks to us about the InnoDB features available in labs.mysql.com.

Enjoy!

MySQL Tuning

This is another section that is broader than one would first imagine.  There’s a reason that many large organizations employ dedicated database administrators.  That said, this doesn’t prevent the average sysadmin from making some changes to enhance performance on their database.

The easiest way to start on this is to utilize a script to automatically check your configuration options and make suggestions based on status variables MySQL sets.  I’ve had good luck with a script called mysqltuner.pl.  You can visit the project page at GitHub here: …

Yesterday I wrote about the new Full-text search for InnoDB. Some of you may have not noticed there is a package on Labs.MySQL.Com named mysql-5.6-labs-innodb-features that needs attention. Some of the features are already in the main MySQL source trunk and others will soon be there. Code to support solid state drives with 4K and 8K pages is there. Along with REDO logs up to 2TB in size. There is better thread scheduling and an option to pre-load the buffer pool for better performance after a restart. And UNDO logs can be in their own table space. And please let us know what you find in your testing so we can improve the product!

…

Last week was a banner week for MySQL at OSCON. We had many MySQL developers meeting with the MySQL community, conducting technical sessions, leading BOF sessions, working the exhibit hall, and confirming Oracle's leadership in the technical evolution of MySQL.  The highlight of the week was the unveiling of even more 5.6 early access InnoDB and Replication features that are now available for early adopters to download, evaluate and shape via labs.mysql.com.  

InnoDB is one of MySQL's "crown jewels" and beginning in 5.5 is now the default storage engine.  The following 5.6 feature improvements are in direct response to community and customer feedback and requests.  The new 5.6 early access features include:

• Full-text search
  
• REDO log files max size extended to 2 TB
• UNDO logs on their own tablespace …

This comment is in regard to this InnoDB blog page. Trying to post a message says you need to be logged in, but there’s no register/login page I could see…

Anyway the page talks about a new feature which allows you to save on shutdown an InnoDB buffer pool and to load this on startup, this ensuring that once loaded the database will perform with this “hot” cache.

That sounds interesting as I have seen on numerous occasions that if the buffer pool is not warm then performance can be a magnitude worse.

This looks like a very welcome feature. However, a couple of things are not clear to me.

1. Having some example benchmark times of using this process and comparing it to trying to warm up the buffer pool by hand would be useful.  While this may heavily dependent on database …

 Mark Callaghan at facebook tested the test release of MySQL 5.6.3 and he has found some performance improvement with InoDB feature. Read below...

Mark tried two of the previews for MySQL 5.6.3 at http://labs.mysql.com/. His first attempt with the multi-threaded slave preview was not successful. Parallel apply on the slave is serial when the master does not also run 5.6.3. He said (I hope this changes as a typical upgrade is first done on the slave.)

He was more successful with the InnoDB features preview. A few more mutex contention bottlenecks were removed in it and he wanted to compare the peak row update rate between it and MySQL 5.1.52. he configured InnoDB to use a buffer pool large enough to …

Note: this article was originally published on http://blogs.innodb.com on July 27, 2011 by Calvin Sun.

In April of 2011, InnoDB team published the early access of NoSQL to InnoDB with memcached, plus several new features as part of MySQL 5.6.2 milestone release. This week, we announced additional early access to new InnoDB features for the community to test, and provide feedback.

There are two release packages from InnoDB team on MySQL Labs: InnoDB full-text search, and InnoDB new features.

InnoDB Full-Text Search

MySQL 5.5 makes InnoDB the default …

In April of 2011, InnoDB team published the early access of NoSQL to InnoDB with memcached, plus several new features as part of MySQL 5.6.2 milestone release. This week, we announced additional early access to new InnoDB features for the community to test, and provide feedback.

There are two release packages from InnoDB team on MySQL Labs: InnoDB full-text search, and InnoDB new features.

InnoDB Full-Text Search

MySQL 5.5 makes InnoDB the default storage engine, so everyone can benefit from ACID-compliant transactions, referential integrity, crash recovery.  However, some users need InnoDB to have built-in full-text search, similar …

Another performance problem found by PoorMansProfiler
Innam rana said in his blog post on innodb blog:



InnoDB has an internal file system management module that primarily manages the space in the data files. One of the pain points was the coarse level of locking used when a data file has to be extended. More about this issue can be found here. In the latest labs release we have fixed this problem.
When we need to extend a data file inside InnoDB we write zero filled pages synchronously to the file. The user thread which is extending the data file holds fil_system::mutex during the whole …

Just in case you missed the press release:

Oracle Provides Early Access to MySQL 5.6 New Features

At OSCON (the O’Reilly Open Source Convention) in Oregon, Oracle announced that early access to new features of MySQL 5.6 is available for the community to test, deploy and provide feedback.  Read more here