MySQL Conference Day 3 Thoughts

Apr 16, 2008

Keynotes

The conference committee managed to get Rick Falkvinge of the Swedish Pirate Party to speak. I heard him speak at OSCON 2007. What I took away from his talk is copyright is evil. Copyright is the excuse industries (i.e. the music industry) are using as a tool to justify monitoring all of your communications. Not only do they want to monitor you, but prohibit certain kinds of communications. What it comes down to is your privacy vs. copyright. It’s scary stuff.

The second part of the keynote was a panel consisting of a representative from MySQL, Sun, flickr, FotoLog, Wikipedia, Facebook, and YouTube. They were discussing scaling at each of their sites. It was a great discussion. Informative and funny. Paul Tuckfield of YouTube had a great saying: “Replication is the answer. You just need to rephrase the question.” Farhan “Frank” Mashraqi of FotoLog made an interesting observation where Sun Sparc Niagara 1 servers make great master servers due to their high speed and Sun Sparc Niagara 2 servers make great slave servers due to their large concurrency.

Grand Tour of the information_schema

The information_schema database is a built-in database that contains metadata about data including tables, partitions, privileges, character sets, constraints, indexes, server settings, server status, and routines. This database is an alternative to MySQL’s proprietary SHOW commands.

I see a real utility being able to query the information_schema database to check server status. Another interesting use is to auto-generate schema documentation. I’m curious what kind of user metadata you can associate to objects.

Applied Partitioning and Scaling Your Database System

Phil Hildebrand gave his talk about the different ways of partitioning your data. The types are range, hash, key, and list. You can read more about partitioning types in MySQL’s documentation.

MySQL Performance Under a Microscope: The Tobias and Jay Show

This was an entertaining talk by MySQL’s Tobias Asplund and Jay Pipes. They showed the results of a few benchmarks comparing multiple ways to do something.

In the first test, they wanted to see what was the fastest way for getting the total count of records. They tried a handful of ways, but COUNT(*) when query caching was enabled was the fastest.

On of the other interesting tests they did was DATETIME vs. INT UNSIGNED for storing a date. The best method was to use an INT UNSIGNED and do the date to int conversion on the application tier. In PHP, use the strtotime() function.

The MySQL Query Cache

Query caching can bring huge performance gains to your web application. Baron Schwartz of Percona gave a talk describing why query cache rocks.

MySQL caches query results, not execution plans. It stores the results in a big hash table where the key is the query. They key is case-sensitive and whitespace-sensitive. Only SELECT statement results are cached since it doesn’t make a whole lot of sense to cache INSERT or UPDATE results. Only deterministic queries are cached. If the query contains a non-deterministic function call, such as a function that returns the current time, then it cannot cache the results.

You can display the query cache information by executing the following:

SHOW GLOBAL STATUS LIKE 'qcache%';
SHOW GLOBAL STATUS LIKE 'query_cache%';

The way the query cache memory is allocated can potentially cause fragmentation. You can get a feel for how bad it is by comparing the number of free blocks to the number of total blocks. If you are running out of free blocks, you either have filled your cache or you have bad fragmentation.

Grazr: Lessons Learned Building a Web 2.0 Application Using MySQL

The talk about Grazr was given by Patrick Galbraith and Michael Kowalchik. Patrick is one of the fellows that showed of some awesome memcached stuff at tutorial and the BOF. Grazr filters out feeds to only the information it thinks you’d be interested in. This was a pretty general discussion and they managed to get through their slides pretty quickly. Since the talk was winding down early, I headed over to Eli’s talk.

Help, My Website has been Hacked! Now What?

If you have a popular site, you are going get people attempt to hack your site. Eli White of digg talked about some of the ways your site can get hacked.

One thing he pointed out that I didn’t think about was you can’t just block someone’s IP address. If there is a proxy between the user and the web server, then IP address you get is the proxy’s, not the user’s. You need to check the x-forwarded-for HTTP header. If there are more than one proxies involved, the x-forwarded-for will contain a comma separated list of addresses.

I talked to Eli after his session and he recommended blocking the IPs on the firewall instead of the PHP code. This is means less load on the app server, but unless you have a fancy firewall, I would be curious to know how often a particular IP is trying to attack me.

Performing MySQL Backups Using LVM Snapshots

The last session of the day was by Lenz Grimmer of MySQL. LVM snapshots can be a great way to backup your databases, especially InnoDB. The basic procedure is:

  • flush tables
  • flush tables with read lock
  • lvcreate -s
  • show master/slave status
  • unlock tables
  • mount snapshot, perform backup
  • unmount and discard the snapshot

InnoDB ignores the “flush tables with read lock” step, but if you have any MyISAM tables, you’ll still need to do it. Flushing the tables does impact performance, especially while the snapshot is active. As soon as you mount the LVM partition snapshot, you can back it up and then unmount and discard the snapshot.

There is a Perl script called mylvmbackup which can help with these procedures.

An alternative to LVM snapshots for backups is to replicate to a slave server, stop the replication, perform the backup on the slave, then start replication again. The downside is it requires an extra machine as the slave in which MySQL can be stopped so that InnoDB tables can be properly flushed.

MySQL Quiz Show and Sun party

The quiz show is a absolute blast. The show is moderated by the infamous Jay Pipes. Facebook was kind enough to sponsor the quiz show this year. There was plenty of beer and popcorn to go around. People won a ton of books and Sheeri Kritzer Cabral won the grand prize: an Apple iPhone. Lucky!

Everybody ended up coming out of the wood work for the Sun after party. It was nice to finally get to meet Baron Schwartz. Everybody should go by his book! High Performance MySQL.

I also had a chat with Brian Moon of dealnews.com. He claims PHP can be made to work with the Apache worker MPM. Hmm, looks like I have a new project!


No Comments

No comments yet.

RSS feed for comments on this post. TrackBack URL

Sorry, the comment form is closed at this time.