James’ World

The MySQL Conference was this week in Santa Clara. It was a well-organized and educational event with everybody involved in the MySQL community showing up once again.

Executive Summary

The highlights were:

• after 2 years of effort, the performance schema foundation is available as a 5.5.x patch. With another year of effort, it could be useful.
• the various community forks (Percona/XtraDB, MariaDB, OurDelta) will merge in the next 3 months into a maintenance fork by Monty Program, since MP has the most original MySQL developers.
• the various MySQL vendors are soldiering along, all releasing new, improved versions of their hw and sw products this year.
• The largest independent MySQL-centric consulting companies are Percona with 32 staff, and Monty Program 40 with staff, with a target of 50 employees.
• the MySQL source code will have to be modified to make MySQL fast enough to keep up with Fusion IO SSD devices. Currently, better than SSD performance can be gained by installing enough RAM to fit the entire database in buffer pool.
• Drizzle development is going nicely, but note that it’s not backward compatible with MySQL. Drizzle is a 64-bit only fork of MySQL with emphasis on community code development, increasing performance and maintainability through a plug-in architecture and strict code cleanliness.

Monday Morning Tutorial

Using Partitioning in MySQL 5.1 and 5.5 with Giuseppe Maxia (Oracle)

- available in MySQL 5.1 and later only
- TO_DAYS and YEAR() special and recommended as they can prune partitions from lookups.
- when using TO_DAYS() as a partitoning function, the first partition matters. Use value less than zero for first partition to create NULL partition to double performance as a bug workaround.
- consider lock before inserting for all table types
- for performance, consider non-partitioned on masters, partitions on slaves.
- or different partition types

He also gave a nice tutorial on mysql sandboxes script.

Partition Limitations:

- cannot mix table types
- cannot make read-only

I talked to some advanced users, and none have found a practical use for partitions in their environment that was better than using regular table types for logging type applications.

This is due to the fact that partitions do not increase fault-tolerance, often don’t benchmark any faster, and have little in the way of administrative mgmt. support after partition creation.

Partitions can increase performance in applications where the index serves to stripe operations, but most people are just using dates for logging, with no practical benefit, as most operations fall into the current date partition.

Slides

Monday Afternoon Tutorial

Talked to Arjen Lenz and a friend at lunch.

- OpenQuery is suitable for affordable, long-term contract databae admin, not firefighting
- former partition tester and bugfixer
- replication bug with TCP errors, nagios plugin should compare both replication lag seconds and log position
- need SSL or heartbeat to detect/fix

memcached

- set all clients to same values
- use JSON or YAML, not Storable or Pickl

Tuesday

Performance Schema with Peter Gulutzan

- coded by Alff, but not GA yet
- PERFORMANCE_SCHEMA database optionally populated with events (mutex, lock, io) timing and count info
- allows simple SQL reporting of performance

EXPLAIN Demystified with Baron Schwartz (Percona Inc.)

- perennial nice EXPLAIN overview
- nice example of using mysql command prompt as pipeline for non-trivial processing

Introduction to InnoDB Monitoring System and Resource & Performance Tuning with Jimmy Yang

An Overview of Flash Storage for Databases with Vadim Tkach, Percona Inc.

- nice talk with useful performance graphs

Linux Performance Tuning and Stabilization Tips with Yoshinori Matsunobu, Sun Microsystems

- nice talk with detailed slide examples – he’s a hard worker
- he’s a fan of xfs, so some info not always useful for ext3. ie. deadline scheduler may be better on xfs, but it feels the same to me as cfq on ext3.

Wednesday

More Mastering the Art of Indexing with Yoshinori Matsunobu, Sun Microsystems

- second-part continuation of his talk from last year (!) Were you there?
- his understanding of the space requirements of blobs in Innodb is different than Peter Zaitsev’s.

Faster Than Alter – Less Downtime with Chris Schneider (Ning.com)

- Hipster presentation on doing practical DBA tasks
- likes doing dump and restore on Innodb tables, 30% faster afterwards on his tables.

InnoDB Architecture and Performance Optimization with Peter Zaitsev, Percona Inc.

- perennial comprehensive overview of Innodb
- talked about differences between Antelope and Barracuda file formats
lwn.net: A look at the MySQL forks

BOFs

O’Reilly failed to use meetup.com to promote the BOFs once again at this conference, so turnout was light to moderate as in past years.

Sphinx BOF hosted by Andrew Aksyonoff

I’ve been familiar with SphinxSearch for years and am a production user, so the general audience discussion was not interesting to me.

However, I had a chance to talk to Andrew about my take on the October Apache (Search!) Conference last October and suggested a few things:

• explain collections on the Sphinx homepage, since many users insist on this feature. The question, of course, is what does the term ‘collections’ mean to various people?
• make it possible for a non-technical end-user (like a marketing asst.) to highlight 10 items for feature on the first page of results
• Microsoft is EOLing FAST for linux users, so think about promotion to that segment, who is considering migration to Lucene mainly – because Lucene is free, and migration is the same cost to any other product.
• look at the myriad “value-added features” of commercial search engines, mostly related to adserver integration, and decide what can be supported.

MariaDB BOF hosted by Monty

Not much talk about MariaDB, but lots of drinking! (See Monty’s keynote for more detailed info.)

Conference Wrapup

Overall, another good MySQL conference. The organizers restored balance to the presentations, with a fair number of independent consultants and end-users doing talks. (Though I miss the awesome Percona Performance Conference from last year.)

BOFs should be promoted on meetup.com to double participation.

There should be a room with exotic hardware to demonstrate high performance MySQL and MySQL Cluster configurations – SANs, Infiniband, failover, etc.

The lunch food was quite good on all days, as noted by several people. (Important because the suburban venue is not within walking distance to outside restaurants.)