MySQL UC: Day 4

It's day 4 at the MySQL Users Conference. It's also the day I present.

Three Approaches to MySQL Applications

I went to this for one reason only: It was the presentation immediately preceding mine, so I was guaranteed to be on time and not have any surprises about the room.

The presenters were from Dell, and their demo app was a DVD sale system using MySQL running a (surprise!) Dell server (dual Xeons at 3.06 GHz). The three approaches were PHP, JSP, and ASP.net. I was really, really glad to see this as the presentation before mine, because the PHP database code was such a mess. In PHP, you have to piece together your own literal query, typically via string concatenation and $ substitution, and then call functions that are different for each database. If you want to even try to be database-independent, you need to add an abstration layer of some sort. I've done this before with PHP, implementing something like Python's DB-API, and having subclasses for both MySQL and PostgreSQL, but this was not done here. Worse, I am pretty sure that many of their PHP code examples were susceptible to SQL injection attacks. In short, it makes Python look damn good, which it is.

Python and MySQL

My presentation is now available on-line. I was happy with the way it turned out. We had 45 minutes, with the last five minutes for questions. I had 37 slides, and I managed to finish with seven minutes for questions, and ended up getting 10-15 minutes of questions. It was not a huge crowd -- less than 30 people I think -- but it was also the last day of the conference, with checkout time at noon, and I don't think I had anyone leave before the question session. People I talked with afterwards seemed to think it was useful, at least, including people that were already MySQLdb users, and the presentation was intended primarily for non-users.

Lunch

Make your own sandwich day.

Writing Storage Engines for MySQL

This was a more focused version of the "Tour of the Source" presentation, with more details on creating new column types and storage engines.

There are quite a few MySQL storage engines out there; in fact, a lot more than you might think:

• ISAM. This is the original MySQL storage engine, before there were storage engines, and in fact it is now removed from 5.0.
• MyISAM. This is the replacement for ISAM, around since 3.23. It's not transactional -- though it is planned to eventually make it transactional -- but very fast if you have to append new rows.
• MERGE. This is, in a way, a limited sort of view. You can define a MERGE table to be the UNION of several identical (schema) MyISAM tables. For example, if you are recording log data or audit trails, you could segment your data into seperate tables for each month, and then use a MERGE table to treat them like one.
• BDB. This is the Sleepycat Berkeley DB. BDB itself is not a relational database but a hashed-key database which is transactional.
• InnoDB. InnoDB has been around for a long time, supposedly more than 10 years. It provides Oracle-style multiversioned concurrency control.
• Archive. This is an engine that was developed by Yahoo! for logging. It supports SELECT and INSERT, and compresses records as they are inserted.
• CSV. This does what you would think: Each table is stored in a CSV (comma-separated value) file, which is suitable for importing into lots of programs, especially spreadsheets.
• FEDERATED. This engine actually acts as a proxy to another database server. At the moment, the remote server must be a MySQL server, but other servers are expected to be supported in the future. Transactions are not supported, but SELECT, UPDATE, DELETE, and INSERT are. In theory, it seems like transactions could be supported, and the documentation seems to hint that it could be at some point. Referential integrity can't be guaranteed, since the tables could change on the remote server without the local server being informed about it.
• MEMORY. Also known as HEAP tables, these only exist in RAM and are never written to disk. They are used internally sometimes for JOINs and views, but can be used for other temporary uses. The schema is persistent; only the data is volatile.
• BLACKHOLE. All SQL operations are supported, but they do nothing. Data written to BLACKHOLE tables is discarded; SELECT returns no rows. These tables are supposed to be good for replicating the schema but not the data.
• EXAMPLE. This doesn't do anything useful, except provide a skeleton example of how to make your own storage engine.

Brian said that it generally takes a few hours to write a specialized storage engine. Obviously if you need a real storage engine with persistent storage and transactions, this will take longer. However, you do not need to worry about details like parsing queries, or the query cache, since these are all managed higher up in the server. For now, you have to re-compile the server to add a new storage engine, but Brian says this will change in the future, and that the MySQL module configuration will look a lot like Apache.

Post-Conference

The conference was wrapped up by 5 p.m. PDT. Since I had some time on my hands, I checked the train schedules, and decided to make a quick trip to San Francisco. This involved taking the VTA train/trolley to Mountain View and catching Caltrain to San Francisco: $6 for both trains. Once there, I got on the 30 bus and went to Chinatown. Had dinner at Cafe Honolulu. Walked up-hill a block and rode the cable car down to Fisherman's Wharf. By this time, it was dark, but you still get a good view of the city. From Fisherman's Wharf, you can see the Golden Gate bridge.

I had figured out from the train schedules that the last Caltrain train I could take and still make the last VTA train out of Mountain View left at 10:07 pm PDT. My other options were: a) take the 12:07 a.m. PDT train to Mountain View and catch a taxi back; and b) take a train that left after 4 a.m. and wander around SF for six hours. In the end, I decided to take the cable car to the other end of the line. This put me close to 4th Street, which was one of the cross streets at the Caltrain station. I missed one bus and decided to walk there, which turned out to be a pretty good idea, since I got there with about 10 minutes to spare and was not passed by any more busses. I thought it would be about six blocks, but it was more like 1.3 miles. Then took Caltrain to Mountain View, and waited on the train about half an hour before it left. By this point I was nearly falling asleep, so skipping town early was probably the right choice. I figured out that to catch my 1:03 p.m. flight the next day, I should take the VTA train leaving around 10:30 a.m., and that would take me back to the San Jose airport. Got back to my hotel after midnight; set the alarm for 8:30 a.m. and sent to sleep.

8:30 a.m. arrives. Hit snooze.

8:37 a.m. or so arrives. Turned off alarm.

10:00 a.m. arrives. Now I am wide-awake. Pack in a frenzy. Checked out about 10:25 a.m. Made it to train station in time. Had to change trains and then catch the airport flyer bus to the airport, which turns out to be only about other mile away. Checked in. Got breakfast burrito at Señor Jalopeño. Ate a pastry I had gotten from Cafe Honolulu the night before in SF and put in my coat pocket, just in case. Flight to Atlanta and airport shuttle to Athens were uneventful, in comparison.