Write-heavy workloads can reach a situation where InnoDB runs out of usable space in its redo log files. When that happens, InnoDB does a lot of disk writes to create space and you can see a drop in server throughput for a few seconds. From InnoDB plugin 1.0.4 we have introduced the ‘innodb_adaptive_flushing’ method that uses a heurstic to try to flush enough pages in the background so that it is rare for the very active writing to happen. In this note I’ll try to explain how the heuristic works i.e.: what factors are taken into account when deciding how many dirty pages to flush from the buffer pool in the background. I’ll skip some details for the sake of clarity.

You may find InnoDB glossary useful to understand the terminology used in this note. adaptive_flushing is of consequence to you if your workload involves significant write activity. …

To speed up bulk loading of data, InnoDB implements an insert buffer, a special index in the InnoDB system tablespace that buffers modifications to secondary indexes when the leaf pages are not in the buffer pool. Batched merges from the insert buffer to the index pages result in less random access patterns than when updating the pages directly. This speeds up the operation on hard disks.

In MySQL 5.5, the insert buffer has been extended to a change buffer, which covers all modifications of secondary index leaf pages. This will improve the performance of bulk deletes and updates, transaction rollback and the purging of deleted records (reducing the “purge lag”).

To assess the benefits of the extended buffering, you may want to run benchmarks with the settings innodb_change_buffering=all, innodb_change_buffering=inserts, and innodb_change_buffering=none. Users of solid-state storage, where random reads are …

With all the changes surrounding Sun and MySQL joining Oracle, and InnoDB becoming part of the MySQL organization, let’s recap how and where to find information related to InnoDB, so first-time users can learn InnoDB from the ground up, and long-time users can keep up with the latest developments.

MySQL 5.1

In MySQL 5.1, the MySQL Reference Manual has information about the InnoDB storage engine in the Storage Engine chapter.

To take advantage of the latest InnoDB features and performance improvements in the 5.1 release, you’ll need to start using the InnoDB Plugin. The InnoDB Plugin went through a series of “Early Adopter” releases, reaching GA status with InnoDB 1.0.x. So this is the level of InnoDB that you should be using with 5.1, and it comes with 5.1, but requires action on your part to enable it. The InnoDB Plugin has its own manual that discuss features like fast index creation, table compression, …

This is a brief overview of the history of InnoDB with respect to the Version Control Systems (VCS) that were used for developing. It could be useful to people who want to trace back in time to find the origins of bugs or features.

Early days
InnoDB was born in the mid 1990s when Heikki Tuuri started developing this masterpiece. Heikki was a lone developer at that time and he did not use any VCS. There is no InnoDB revision history before 2001.

2001 – 2005
Then in 2001 InnoDB was imported into MySQL’s BitKeeper repository and development continued, recording the history there.

2005 – 2010
Later on in Oct 2005 when Oracle acquired Innobase, InnoDB developers had to move away from MySQL’s BitKeeper repository and Subversion was chosen for InnoDB development. The latest sources from BitKeeper were imported into SVN without preserving the history …

We are proud to announce the release of GreenSQL Pro and GreenSQL Light, our first commercial Unified Database Security solutions, designed to provide all organizations – from small and medium businesses all the way to large enterprises – robust database security at an affordable price.

“Commercial Unified Database Security solutions” is a mouthful. Let’s look at what that means.

For us, commercial has several meanings. First, we have designed GreenSQL Pro for commercial organizations; second, we charge a modest fee for it; and third, unlike our open source code, we take full responsibility for it.

How about unified? To be unified, something must first have parts. GreenSQL Pro and GreenSQL Light include many aspects of database security within them, all contributing to their primary mission: securing databases. We’ll be discussing some of those aspects below.

And of course, there’s database. …

MySQL Sunday is an open series and looks like a great start to the Oracle Open World conference.  Everyone is waiting to see if there are any announcements for MySQL at the conference from Oracle.  I like that they are starting with Edward Scriven and closing with Marten Mickos.  Hopefully there will be a lot of questions answered on stated directions and the MySQL 5.5 release and product updates

I am helping IOUG Organize a MySQL bootcamp at their Collaborate conference in Orlando. This is actually a great opportunity to reach out to a lot of Oracle talent looking for more information and training on MySQL. An IOUG Bootcamp is compiled from several 1 hour technical sessions starting from introductory level topics on day 1 to advanced topics the final day. The idea is this format will help those not familiar with MySQL, get a crash course in MySQL while also providing people with a wide range of targeted sessions that they can come in an out of as they see fit. It’s like the tag line of most Carnivals, Fun for all ages…

The reason I am posting is we are going to need lots of help from you. We are looking for speakers for the conference at the moment… you can submit your papers here: http://www.ioug.org/callforspeakers. Think of it like this, this is an …

JavaOne starts on Monday, but several key events are tomorrow, Sunday, including the keynote for OOW and the GlassFish Community Event and Party :-).

This will be a very busy week; below is a list of guides to (some) of the main events.

The JavaOne Conference Blog is the official source:

• Sunday Night Keynote Party @ the Zone
• Keynotes Set the Tone
• …

Here’s a little trivia that you might find helpful. Suppose that I have the following in the MySQL slow query log (abbreviated for clarity): # User@Host: root[root] @ localhost [127.0.0.1] # Time: 100919 17:58:52 # Query_time: 9.648427 Lock_time: 8.648039 select sleep(1) from t limit 1; To get this into the slow query log, I set the long_query_time to 0 and opened two sessions. In one session I ran LOCK TABLES t WRITE, and in the other I tried to select from that table.

In our work to improve MySQL scalability we tested many opportunities to scale the MySQL Server and the InnoDB storage engine. The InnoDB buffer pool was often one of the hottest contention points in the server. This is very natural since every access to a data page, UNDO page, index page uses the buffer pool and even more so when those pages are read from disk and written to disk.

As usual there were two ways to split the buffer pool mutex, one way is to split it functionally into different mutexes protecting different parts. There have been experiments in particular on splitting out the buffer pool page hash table, the flush list. Other parts that have been broken out in experiments are the LRU list, the free list and other data structures internally in the buffer pool. Additionally it is as usual possible to split the buffer pool into multiple buffer pools. Interestingly one can also combine using multiple buffer pools with …