I spent Friday examining the systems for a more traditional consulting gig (in case you did not know, Pythian has offered more traditional consulting and emergency services in addition to our remote DBA offering for our entire 12 year history). It is a familiar story to many people — the database performance was unacceptable.
The company had a few log tables that had huge amounts of inserts
and foreign keys, so they used InnoDB. Unfortunately, they also
used a GUID as a primary key (which is getting more and more
common these days, but long primary keys with InnoDB slow down
INSERT, UPDATE and DELETE
commands a great deal) — varchar(32) and utf8.
That’s right — their primary key for many of these tables was 96 bytes long (32 characters * 3 bytes per character), and as an InnoDB table, the primary key is clustered with …
[Read more]