Five days of uncertainty, trapped far away from home by an Icelandic volcano, provide plenty of time to think. Here is my attempt at drawing conclusions the day after returning home: partly on a personal level, partly on a general human level, partly on a societal level.

To recap what has happened: Ten minutes before the intended boarding time of LH459 from San Francisco home to Munich, I got an SMS about the flight being cancelled. This was Thursday 15.4.2010 at 20:20. Thereafter I lived in uncertainty in airport hotels, most of the time 40 km south of San Francisco, to finally arrive home five days later than plan on Wednesday 21.4.2010 at 13:00..

Personal conclusion

Conclusion 1: Uncertainty and losing freedom are hard to cope …

I (Peter Gulutzan) gave a Performance Schema talk at the O’Reilly MySQL User Conference are available at oreilly.com. My colleague Jimmy Yang gave a talk about InnoDB monitoring which was about 40% devoted to Performance Schema; his slides too are available at oreilly.com.
Edward Screven’s keynote talk also mentioned Performance Schema. I had a few minutes to show it during the “Demo of all the Big New Features” talk which I co-presented with Konstantin Osipov.

The rest of this blog is the text of the talk that I planned for the slides. Naturally it came out differently due to time constraints and questions.

User Conference Slides: …

What a busy week! Lots of MySQL 5.5 announcements that just happened to coincide with the MySQL Conference and Expo in Silicon Valley. Here are some highlights of the performance and scalability work that the InnoDB team was involved with.

A good prep for the week of news is the article Introduction to MySQL 5.5, which includes information about the major performance and scalability features. That article will lead you into the MySQL 5.5 manual for general features and the InnoDB 1.1 manual for performance & scalability info.

Then there were the conference presentations from InnoDB team members, which continued the twin themes of performance and scalability:

…

I forgot to include the titles for my talks.

The ignite talk Wednesday at 7pm is “What Is a Performance Model for SSDs?“

The ignite talk is a 5-minute talk at tonight’s Ignite MySQL session organized by Brian Aker. I’ll present some performance measurements on the Intel X25E SSD. The bottom line is that although I can get the 3,300 random 4KB writes per second, as the spec sheet advertises, I cannot seem to get more than about 11,000 reads per second, although the spec sheet says I should get 35,000.

The 1-hour talk Thursday at 10:50am is “How TokuDB Fractal Tree Indexes Work.“

This talk about Tokutek’s Fractal Trees. I’ll explain how Fractal Trees work, and show why they can get one to two orders of magnitude speedup on …

With the exception of Windows InnoDB has used ’simulated AIO’ on all other platforms to perform certain IO operations. The IO requests that have been performed in a ’simulated AIO’ way are the write requests and the readahead requests for the datafile pages. Let us first look at what does ’simulated AIO’ mean in this context. We call it ’simulated AIO’ because it appears asynchronous from the context of a query thread but from the OS perspective the IO calls are still synchronous. The query thread simply queues the request in an array and then returns to the normal working. One of the IO helper thread, which is a background thread, then takes the request from the queue and issues a synchronous IO call (pread/pwrite) meaning it blocks on the IO call. Once it returns from the pread/pwrite call, this helper thread then calls the IO completion routine on the block in question which includes doing a merge of buffered operations, if any, in …

Everybody at the MySQL Conference & Expo 2010 who is interested in MySQL Workbench, please feel free to join me for an in-depth presentation about the current MySQL Workbench 5.2 product and future plans. Looking forward to see you there!

Follow this link to the Conference Schedule for time and coordinates.

For everybody who could not joins us at the MySQL Conference & Expo this year I have uploaded the slides below. I included a lot of tips & tricks in the presentation. So even if you are not able to see the live demo, taking a look at the slides might still be a good idea.

MySQL_Conference2010_MySQL_Workbench_V3.pdf

Enjoy!

Edward Screven – Chief Corporate Architect of Oracle provided the opening keynote at the 2010 MySQL Users Conference.

Overall I was disappointed. The first half was more an Oracle Sales pitch, we had some product announcements, we had some 5.5 performance buzz. While a few numbers and features were indeed great to hear, there was a clear lack of information to the MySQL ecosystem including employees, alumni and various support services. I hope more is unveiled this week.

Some notes of the session.

• Oracle’s Strategy covers storage, servers, virtual machines, operating system, database, middleware, applications
• We build a complete technology stack that is “open” and “integrated” based on “open standards”
• products talk via open standards with the intention for customers to not feel locked in to any technology
…

One of the well known and much written about complaint regarding InnoDB recovery is that it does not scale well on high-end systems. Well, not any more. In InnoDB plugin 1.0.7 (which is GA) and plugin 1.1 (which is part of MySQL 5.5.4) this issue has been addressed. Two major improvements, apart from some other minor tweaks, have been made to the recovery code. In this post I’ll explain these issues and the our solution for these. First issue reported here is about available memory check eating up too much CPU. During recovery, the first phase, called redo scan phase, is where we read the redo logs from the disk and store them in a hash table. In the second phase, the redo application phase, these redo log entries are applied to the data pages. The hash table that stores the redo log entries grows in the buffer pool i.e.: memory for the entries is allocated in 16K blocks from the …

Performance Schema Support in InnoDB

With the plugin 1.1 release, InnoDB will have full support of Performance Schema, a new feature of MySQL 5.5 release. This allows a user to peak into some critical server synchronization events and obtain their usage statistics. On the other hand, in order to make a lot of sense of the instrumented result, you might need some understanding of InnoDB internals, especially in the area of synchronization with mutexes and rwlocks.

With this effort, the following four modules have been performance schema instrumented.

1. Mutex
2. RWLOCKs
3. File I/O
4. Thread

Almost all mutexes (42), rwlocks (10) and 6 types of threads are instrumented. Most mutex/rwlock instrumentations are turned on by default, a few of them are under special define. For File I/O, their statistics are categorized into Data, Log and Temp file I/O.

This blog is to give you a quick …

Background

The original motivation behind this patch was the infamous Bug#26590 – MySQL does not allow more than 1023 open transactions. Actually the 1024 limit has to do with the number of concurrent update transactions that can run within InnoDB. Where does this magic number come from ? 1024 is the total number of UNDO log list slots on one rollback segment header page. And in the past InnoDB created just one rollback segment header page during database creation. This rollback segment header page is anchored in the system header page, there is space there for 128 rollback segments but only one was being created and used resulting in the 1024 limit. Each slot in the rollback segment header array comprises of {space_id, page_no}, where both space_id and page_no are of type uint32_t . Currently the space id is “unused” and always …