A crosstab query is a specific query used to create aggregate reports on two or more fields, it’s  a handy way to display summary information. At Open Query we have customers using that trick to display production schedules.

The summary table is generated from the database to extract the manufacturing date (mand), unit number (unitn), product area (pro_area), and ranking (rnk). Then we can start using that summary table to process our crosstab. The summary table looks like this:

From Sunday 11.4.2010 to Thursday 22.4.2010, I used my Twitter account @kajarno to send

• 26 Swedish tweets
• 41 German tweets
• 69 English tweets

(not counting direct replies) of which a clear majority (but not all) were of a volcanic nature.

Most Swedish readers can follow German and English too, and most German readers also follow English. Hence the large number of English tweets and the larger number of German tweets.

Here’s a subset of the English tweets. The dates and times are local (California 9 h different from Munich; Chicago 7 h different).

Thursday 15.4.2010

• 13:12 Kaj Arnö wonders whether LH459 will leave at all MUC-SFO tonight 21:00, and if so, which route #iceland #volcao #ash
• 20:18 Damn, flight was cancelled. #lh #iceland #volcano SFO-MUC LH459 …

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 …