ben robison

MySQL Optimization

I’ve been doing some reading recently for a (long procrastinated) assignment at school. I’ve found some official looking materials as well as a recent Digg that led to some generally accepted industry expertise. I’ll give the links in a moment, but here’s the point of it all.

Low-hanging fruit
Don’t waste time trying to tune every query in your database. It can’t be done. Find the ones that take all the time and tune them. The slow queries log is a good place to start. My understanding is that you can set the parameters for the slow queries log in your my.cnf file.

log_slow_queries=/var/lib/mysql/slow­queries.log
long_query_time=2
log_long_format

EXPLAIN
Once you’ve found the queries that are taking a long time, you can use the EXPLAIN command (or the MySQL Query Browser graphical version) to see which parts of the query are taking the longest. It gives a lot of information about the query itself, but besides the times themselves the most useful information is related to the indexes. You can see how many records each portion has to crunch through, which indexes are available, and which indexes are being used. This leads to tip #3.

Index, Index, and….oh, Index.
Proper indexing is probably your best friend when it comes to increasing query performance. This has limited benefits for extremely dynamic databases with a high proportion of INSERTs and UPDATEs because the indexes will have to be rebuilt each time the table is changed, but where SELECTs are the bulk of the queries (most databases) indexing can contribute to huge increases in speed

More Info
http://dev.mysql.com/doc/refman/5.0/en/optimization.html
http://dev.mysql.com/doc/refman/5.0/en/explain.html
http://dev.mysql.com/tech-resources/presentations/presentation-oscon2000-20000719/index.html
http://jpipes.com/presentations/mysql_perf_tuning.pdf [pdf warning]
http://20bits.com/2007/04/10/10-tips-for-optimizing-mysql-queries-that-dont-suck/
http://immike.net/blog/2007/04/09/how-not-to-optimize-a-mysql-query/

TapeFailure and Compete’s Attention

Two quick tidbits I picked up in the news today.

TapeFailure
Apparently, there is a new product called TapeFailure that will show you video of what customers are doing on your website. It doesn’t capture all the information that other solutions provide, but somehow converts their browsing experience into a video that you can watch. Other metrics include percentage of pages scrolled, distance the mouse moved, average clicks per page, user sight focus.

I can actually see some real value in some of the metrics (user sight focus anyone) but I’d be very interested to see how something like that is measured and reportes. I think the video thing is really cool, but it’ll be a complete flop if only for one reason. What one single user does on a site is unimportant. Marketers care about trends of what thousands and millions of users are doing, and we’ve got better things to do with our time than watch a video of one person’s experience on our website. It might find some novelty points, but it’ll never catch on. If I’m wrong, then I’m wrong, but if I’m right, remember you heard it here first.

Attention
A group called Compete is now measuring a new metric that they’ve dubbed Attention. From the article…”it measures the total amount of time spent by US users on a website as a percentage of total time spent on the internet by all US users.” While Hitwise reports stats at the ISP level, Compete reports measurements by users that have their toolbar installed.

I won’t get into the problems of measuring the US population by the subset that use a particular browsing toolbar because this metric is not going to play that big of a role in the market. It will be useful for folks who want to pay for advertisements on other sites, they’ll know where to start to buy ads, but it also means that these companies now have a pretty good idea of how much they can charge for their adspace. It has little meaning for those who are trying to optimize their own sites to increase conversion.

MSN Messenger: Gotta love it

You know, regardless of your personal religious feelings and whether or not you believe in Microsoft, you’ve got to admit that they make a pretty good product.  I’m an Apple fanboy, but I do live in reality, and I can see that Windows helps make the world move along.  I can love my own operating system without hating all others.  There is room in my heart for those who choose to follow Satan’s Bill’s path.

That being said, every once in a while, I run across something that makes me wonder.  On my computer at work today, I was using Windows Live Messenger (formerly MSN Messenger) to talk to a friend when I saw this cleverly worded advertisement at the bottom of my Messenger window.

Is there really anything else to say?  Somebody in the marketing department had a bad day I guess.

Enterprise Java Tools

The Java Language presents many advantages for the enterprise-types.  My personal favorite language really shines on the web, but for the full architecture and support desired in enterprises, Java has a lot to offer (not the topic of this post).  Right now we’re going to look at 2 Java tools that have become commonplace in enterprise development: log4j and JMS.

log4j
log4j (non-capitalized on purpose) is an open-source project developed under the Apache Jakarta banner.  It was originally developed by Ceki Gülcü. It is built to be the best Java logger around, and the industry agrees that it is.  It’s used for debugging and logging and can provide detailed context for application failures.

It is built as a package that you can drop into your application and start making calls to the logger object.  log4j may provide the best or only solution for debugging (this is often case in distributed applications).

log4j enables logging at run-time without modifying the application’s compiled binary.  This means that you can enable debugging without suffering the heavy performance loss that you might expect by bloating your binaries with extra code.

Perhaps one of the most useful features in log4j is the idea of hierarchy and inheritance.  As you make calls to the logger object and log different kinds of events, you can have different outputs that listen to the logs and pass them on when certain criteria are met.  It’s sort of a subscriber provider model for things.  Potential subscribers would be loggers outputting information to the console, files, databses, SMTP servers, GUI components, etc.  These subscribers are called appenders.

This means that you could potentially have everything logged to a standard file, important things put into a different file, database, etc., and have critical errors emailed to someone that can fix them.  The only thing you’d need in your application code would be the one call to the logger object and everything else happens as the different loggers subscribe to the different events.  All the subscriptions to events are handled by external configuration files, so you don’t have to modify your application code in order to change the behavior of the loggers.

JMS
The Java Messaging Service is another logging solution.  My research on this topic introduced the phrase “asynchronous logging” for the first time although it sounds like log4j works on the same principle.  Reading about JMS also used the publisher/subscriber metaphor which I unfortunately already used, but these two facts should illustrate some of the similarities.

JMS actually supports to different types of logging.  The queuing model has a producer and only one consumer.  They are independent, so the consumer does not have to be running when the producer produces, and the producer does not have to be running when the consumer consumer.  This is a higher level of abstraction than log4j uses (according to my understanding).

The Publish/Subscribe model published messages in “topics.”  Zero or more subscribers can subscribe to a particular topic.  In this model the subscribers and the publisher are not even aware of the other’s existence.

Some enterprising individual has even built an Appender for log4j that plugs the JMS into log4j.  O’Reilly has also published a book on the JMS which in my book means it’s significantly more complex.  From my readings, log4j is also further abstracted out of the application than JMS.  It sounds like in order to change what your JMS publisher is doing, you’ll have to change code in your application and recompile.

Favorite Firefox Extensions

I’m always on the prowl for new Firefox extensions to make browsing easier for me, and thought I’d share some of my findings. I use Firefox primarily on my Mac, but on my work computer and home computer I’ve installed the very same plugins. It’s nice to have the same setup anywhere you go.

I’d be curious to know what Firefox extensions you all use and find invaluable on a daily basis. Please leave comments below. I use the default theme, it’s simple and clean, and I use the following extensions:

1. Noscript: This one takes a bit of getting used to, but it offers me incredible peace of mind when it comes to online security. Noscript blacklists all javascript from running unless you allow it. You can always allow specific sites, or enable the site only for a specific browsing session. I find that I’m a creature of habit, so I tend to visit the same sites most of the time. After about a week of tuning, I now browse anywhere I want without fear that Javascript is secretly stealing my identity.This required some adjustments in my browsing habits, and very occasionally, I stumble upon a site that just doesn’t seem to work properly. When that happens, I must either decide that I didn’t really want to see that page anyway, or fire up another browser.
2. Adblock Plus + Adblock Filterset G. Updater: Makes for a cleaner browsing experience without so many flashing banners. Adblock blocks adds based on specifc patterns while the Filterset updates every week or so and adds new patterns to block.
3. Fission: Shows a pages load progress in the address bar, Safari style. No practical application, but it’s lightweight and it looks nice.
4. Firebug: I recently switched to Firebug after using the Web Development Toolbar for a long time. While Firebug won’t change your screensize for you, I find it much more helpful for showing page hierarchies, javascript errors, css, etc. It will even show you the HTTP headers going back and forth (which is helpful for showing AJAX calls happening while you’re on a page. The interface is simple and clean. I love it.
5. del.icio.us: It took me a long time, but I jumped on the del.icio.us bandwagon not to long ago. This extension is invaluable for tagging, and quickly viewing my del.icio.us bookmarks.
6. GMail Manager: Recently upgraded from GMail Notifier to GMail Manager. I’m still in the trial period, but so far I like it. If you’ve got more than one GMail account that you need to check regularly, this is probably indispensable.
7. SwitchProxy Tool: For those times when my RSS feeds lead me to YouTube videos that I want to see, but can’t from my current location. Fire up my SSH connection my server at home, then reload the page with SwitchProxy. YouTube plays just fine from wherever I am.