MySQL UC: Day 2

It's day 2 at the MySQL Users Conference.

LiveJournal's Backend: A History of Scaling

If my backend had a history of scaling, I'd be pretty upset.

I got here about 5 minutes late, and it was standing room only.

In short, they do a lot of caching on the front end. On the database side, they split databases across servers in some cases. They also do some master-slave replication. Another setup is to use InnoDB with the database on a remote filesystem, and if the master goes down, the backup master mounts the filesystem and recovers. They hate the Linux NFS implementation (probably with good reason), and so I suspect they use some type of NAS like iSCSI for this; it wasn't clear to me. They also do a lot of perl.

Other advice: Use InnoDB. Despite some early InnoDB problems, it works quite well for them. The exception to this is logging, in which case the advice is: Use MyISAM. It's not that InnoDB is bad, but that MyISAM is so good at appending rows.

Apparently InnoDB is about 15 years old, according to one of the presenters; it's just the MySQL support that is relatively new.

Flagship Features in MySQL 5.0

MySQL-5.0 (5.0.4 is the second beta, just released) has support for views, stored procedures, triggers, and an information schema. The most interesting thing about views is that they are updatable in most cases. With PostgreSQL, it appears you do not have directly updatable views; you have to write update rules.

Stored procedures follow the SQL 2003 standard. Apparently IBM DB2 is the only other DBMS which satisfies the standard.

The information schema looks like a database, but it is virtual. It's also an SQL standard for getting metadata about your tables. According to Monty, this is not currently implemented as a storage engine, as you might expected, because there some of the hooks that would be needed don't exist yet. I think he said this was one of Brian Aker's projects.

Lunch

At least O'Reilly feeds you well at these conferences. Day 1 was Asian-themed: Thai noodle salad, stir-fried vegetables, teriaki chicken, hot and sour soup, fried rice, spring rolls. Day 2 was Mexican-themed: Field green salad with jalapeno vinegrette dressing, fajita/taco fixings, black bean soup. OSCON 2002 (the last in San Diego) also had good lunches, as I recall. OSCON 2003 had a brown bag lunch, which was provided by Microsoft. My comment at the time was that Microsoft should get used to us eating their lunch.

PyCon 2005 furnished box lunches that, while quite good, many people had to sit in the hallways to eat. However, PyCon was only $250 for a three day conference, while the early-bird price for MySQL UC is $895 for the conference without tutorials. PyCon doesn't pay speakers, but then neither does MySQL UC, except for tutorials.

Distributed Transactions With MySQL XA

I had trouble finding the room and got there about 10 minutes late.

MySQL-5.0.4 can act as a transaction manager for performing distributed transactions. What this means is that there is a two-stage commit (prepare and commit), and once one of the servers in the transaction successfully prepares, it guarantees that it can commit or rollback. If the TM crashes, it can recover open transactions and either commit them or roll them back. This can take an indefinite amount of time, or there can be timeouts.

An example of how you could use this is writing to multiple databases -- perhaps multiple MySQL servers or MySQL and Oracle and ZODB, etc. -- and ensuring that all servers involved either commit entirely or rollback entirely.

MySQL Cluster Features and Roadmap

Clustering first showed up in early 4.1, though it had some serious limitations. Some of these limitations are removed in 4.1.10a, and more are removed in 5.0.4.

In the 4.1 series, if you do a SELECT with a WHERE clause, the MySQL server has to fetch all rows from the NDB nodes and then filter them based on the WHERE condition. In 5.0, the WHERE clause is evaluated by the NDB nodes, which can greatly reduce network traffic between the nodes and the server.

Support for BLOBs was added in 4.1.10a, but NDB is really not intended for large BLOBs. In 5.0.4 it is still an in-memory database, which is checkpointed to disk periodically.

In 4.1, NDB tables cannot use the query cache. The query cache is a function of the MySQL server, not the NDB nodes. In 5.0.4, the storage engine API has apparently been improved so that the NDB nodes can invalidate the cache in the MySQL server(s).

Replication does work with clustering, but it's not perfect. Since you can have multiple MySQL servers as a front-end to a cluster of NDB nodes, the server being replicated doesn't necessarily see all transactions, so they don't all get replicated. However, if you had a single read-write server and the rest read-only, you could replicate the data to another server which used a different cluster; the example given was for west and east coast clusters, with west replicating to east. This is in addition to the normal replication features of the cluster. If you want a disk analogy, this would be like RAID-110: A mirrored array of two RAID-10 (striped and mirrored) arrays.

Free as in Food and Beer

Post-sessions there was free food, beer, and wine: Bruschetta, stuffed mushrooms, tiny quiches, beef/chicken kebobs, cheese, bread. Decent beer: several varieties including Sam Adams.

Happy happy joy joy

I built a new kernel last night (gentoo-sources-2.6.11-r6) and as a result, when I resume after suspending to disk, my PCMCIA and USB work. This used to work and broke at some point. It's really nice to be able to suspend and resume and still have net and trackball afterwards. In my suspend script, I shutdown PCMCIA and USB and then remove the modules, and afterwards restart PCMCIA and USB. I don't know if that's still strictly necessary, but I don't feel like breaking it again just yet.