Racker Hacker

If you haven’t downloaded the new Wordpress application for the iPhone yet, you might want to check it out. So far, it seems to work really well!

MySQLTuner 0.9.8 is now available for download and it is full of new changes! So far, MySQLTuner has been downloaded almost 24,000 times. Thanks for making it so popular.

Easy download!
You can download it simply by running wget mysqltuner.pl. You will automatically be redirected to the script.

Access servers remotely
If you have multiple servers that you need to tune and monitor, simply download MySQLTuner onto one machine. You can run it against other servers by specifying a hostname, port, and login credentials. Two new options are available: --host and --port.

Pass login credentials on the command line
Two new options are available: --user and --pass. You can pass a username and password on the command line to log in quickly. This can be especially helpful in conjunction with cron.

Manually set the amount of RAM and swap memory installed
I’ve received a few bug reports where users on certain virtual environments saw incorrect memory calculations when they ran MySQLTuner. You can now set the current amount of RAM and swap memory installed with --forcemem and --forceswap respectively. (Thanks to Jason Gill for the patch and the bug report!)

Checking for updates is now optional
Some operating system distributions and environments can’t allow for automatic update checking due to security concerns, so I’ve made update checks optional. The --skipversion option has been removed and it was replaced with --checkversion. As a side note, the only data that I collected was the version number being run and the IP from which the update originated. This data has only been used for aggregate statistical purposes.

As you might imagine, MySQLTuner 1.0 is just around the corner. I’ve been holding out for the MySQL 5.1 GA release, but I may release the script sooner. Don’t worry - as soon as MySQL 5.1 becomes a GA release, I’ll be hard at work to support any new optimization options which it provides.

As some of you may know, MySQLTuner is one of my favorite projects. It’s approaching the 1.0 release and I feel like celebrating. I thought about putting together some T-shirts (because as a nerd, I enjoy the occasional techy T-shirt), but I can’t come up with a good slogan.

I’ve heard of some pretty hilarious slogans for database-related activities, like “DBA’s know how to take dumps” and “don’t overflow my buffer”, but I’m trying to come up with some good ones for MySQLTuner. Whoever picks the slogan/joke that wins and goes on the shirt will get a free shirt from me. Yes, I did say free.

Wordpress 2.6 is now available, and I’ve just upgraded today. If you haven’t upgraded yet, download it and follow the instructions.

Nessus is one of those applications that makes me happy and drives me crazy at the same time. It does what I need it to, but it’s often hard to get it rolling when it needs to do something for me. When I run it, I run it in batch mode, which requires me to have a .nessusrc file. However, there is almost no documentation on how to create one of these files.

Luckily, a smart fellow by the name of George Theall created update-nessusrc. It’s a handy perl script that will take a basic .nessusrc file and do things with it based on the options you pass it. As I said before, I want every test enabled, so here’s the steps I performed:

First, I ran a batch scan to make a basic .nessurc file:

# nessus -xqV -T txt localhost 1241 username password targets.txt results.txt

The x skips the SSL certificate warning, q enables batch mode, V prints verbose status messages to the screen and -T txt makes the report come out in a text format.

Once it started, I pressed CTRL-C to stop it, and then I had a .nessusrc file ready to go. I downloaded update-nessusrc and ran it to enable all plugins:

# ./update-nessusrc-2.37 -ds -c "_all_" .nessusrc

The d enables debug mode (and saves the new .nessusrc to a new file name), the s prints a summary, and -c “_all_” tells the script to enable all plugin categories. You now have a .nessurc file for use with batch scans that will utilize all of the available plugins.

If you’re lazy, you can download my pre-made .nessusrc that I made today with Nessus 3.2.1.

Thanks to some work started by Ville Skyttä, MySQLTuner is now included in Fedora 9 repositories:

# cat /etc/fedora-release
Fedora release 9 (Sulphur)
# yum info mysqltuner
Loaded plugins: fastestmirror, priorities, refresh-packagekit
Loading mirror speeds from cached hostfile
* updates: mirrors.usc.edu
* fedora: mirror.unl.edu
* livna: mirrors.tummy.com
Available Packages
Name : mysqltuner
Arch : noarch
Version : 0.9.1
Release : 4
Size : 11 k
Repo : updates
Summary : MySQL high performance tuning script
URL : http://mysqltuner.com/
License : GPLv3+
Description: MySQLTuner is a MySQL high performance tuning script written in perl that will provide you with a snapshot of a MySQL server's health. Based
: on the statistics gathered, specific recommendations will be provided that will increase a MySQL server's efficiency and performance. The
: script gives you automated MySQL tuning that is on the level of what you would receive from a MySQL DBA.

In addition to Ville, I’d like to thank Jason Tibbitts for reviewing and approving the new package.

If you run a fairly busy and/or badly configured MySQL server, you may receive something like this when attempting to connect:

# mysql
ERROR 1040: Too many connections

MySQL is telling you that it is handling the maximum connections that you have configured it to handle. By default, MySQL will handle 100 connections simultaneously. This is very similar to the situation when Apache reaches the MaxClients setting. You won’t even be able to connect to MySQL to find out what is causing the connections to be used up, so you will be forced to restart the MySQL daemon to troubleshoot the issue.

What causes MySQL to run out of connections? Here’s a list of reasons that may cause MySQL to run out of available connections, listed in order of what you should check:

Bad MySQL configuration
Verify that you have set MySQL’s buffers and caches to appropriate levels for the type of data you’re storing and the types of queries that you are running. One quick way to check this information is via MySQLTuner. The script will tell you how well your server is performing along with the corrections you should make. Running the script only takes a few moments and it doesn’t require a DBA to decipher the results.

Data storage techniques
Remember that MySQL works best when moving vertically, not horizontally. If you have a table with 20 columns, breaking it into two tables with 10 columns each will improve performance. Even if you need to join the two tables together to get your data, it will still perform at a higher level. Also, use the right data types for the right data. If you’re storing an integer only, don’t use a CHAR or VARCHAR data type. If your integer will be small, then use something like a TINYINT or SMALLINT rather than INT. This means MySQL will use less memory, pull less data from the disk, and have higher performing joins.

Slow queries
These are generally pretty easy to fix. If you have queries that don’t use indexes, or if queries run slowly with indexes in place, you need to rethink how you’re pulling your data. Should your data be split into multiple tables? Are you pulling more data than you need? Keep these questions in mind, enable the slow query log, and re-work your queries to find where the bottlenecks occur.

Division of labor
Most people who use MySQL have a dynamic site written in a scripting language, like PHP, Perl or Python. It’s obvious that your server will need to do some work to parse the scripts, send data back to the client, and communicate with MySQL. If you find that your server is overworked, consider moving MySQL to its own dedicated hardware. Among many other things, this will reduce your disk I/O, allow you to better utilize memory, and it will help you when you need to scale even further. Be sure to keep your MySQL server close to your web servers, however, as increased latency will only make your performance problem first.

Right hardware
Do you have the right hardware for the job? Depending on your budget, you may need to make the move for hardware that gives you better I/O throughput and more useable cores. MySQL is a multi-threaded application, so it can utilize multiple cores to serve data quickly. Also, writing logs, reading tables, and adjusting indexes are disk-intensive tasks that need fast drives to perform well. When you look for a dedicated server for MySQL, be sure to choose multiple-core machines with low latency RAM, fast drives (SCSI/SAS), and a reliable network interface.

By reviewing these bottlenecks, you can reduce the load on your MySQL server without increasing your maximum connections. Simply increasing the maximum connections is a very bad idea. This can cause MySQL to consume unnecessary resources on your server and it may lead to an unstable system (crash!).

Thanks to some hard work from Oden Eriksson and Frederik Himpe, MySQLTuner 0.9.1 is available in a Mandriva package.

There was a bug in versions of Plesk prior to 8.3 where the AWStats statistics for the previous months were unavailable. It was a bug within Plesk’s AWStat’s implementation, and it was fixed in Plesk 8.3.

However, the fix only corrected the issue moving forward after the upgrade. There was no automated way to rebuild the previous months’ statistics, even though the AWStats data was right there on the disk!

I saw this blog post about the issue, and the fix is quite elegant:

Plesk 8.3 AWStats on Linux - Rebuilding Previous Month Statistics

As some of you might know, I interviewed for a position at Google in April of this year. It wasn’t a position that I sought out, but it all came about after I received an e-mail and phone call from a recruiter. Obviously, there’s some things I can’t talk about with regards to the interview process, but there’s quite a few things that can be said.

How it started
The initial recruiter that I spoke with was a very friendly fellow that didn’t seem too technical. He didn’t get into the job description much, but he was interested mostly in whether I wanted to relocate and what type of job I enjoy most. We ran through a few cursory technical questions and he tried to find out what my skill level was in certain areas. When it was all said and done, he said I’d be contacted from someone else at Google within a few weeks.

Two weeks later, I received some e-mails, went through [redacted] phone screens (with some pretty intelligent people), and learned more about the position. The folks from Google that I spoke with ranged from friendly and chatty to very direct and somewhat terse. Overall, I got the idea that they weren’t interested in running a quiz, but they wanted to know how deep my knowledge and understanding was with regards to critical topics relating to the position. I know this sounds vague, but it’s about as much as I can tell you.

The middle
I received a few more e-mails after the phone screens and my recruiter wanted to bring me out to California. Travel arrangements were made, I flew out to San Jose, and then drove the short drive to Mountain View. The city and the surrounding areas were a little different than I was used to. Most of the buildings and structures look as if they were built between 1960 and 1980 and they had a peculiar architecture. I stayed in the Hotel Avante (which was quite comfortable) and made the short drive to the Googleplex in the morning.

This was about the point where I slapped myself and said “Holy crap, I’m interviewing at GOOGLE!”

When I arrived, I went into the wrong buildings twice until I found the right one, but some Google employees finally pointed me in the right direction. I met with my recruiter, who was actually pretty entertaining, and he gave me a run down of how the day would go. I spent the morning interviewing, and then I joined a Google employee for lunch. He answered many of my questions about the cost of living, job benefits, and how he liked Google. When that was over, I went back to interviewing and was escorted out of the building at the end of the day.

Towards the end
I spoke with my recruiter a few more times after the interview for some basic paperwork-related issues, and he worked hard to keep me in the loop on my application status. There wasn’t much of a concern job-wise as I work for one of the best companies in my industry already. However, I was getting ready to move to a new home, so I let my recruiter know that I was in a bit of a time crunch.

You’ll probably want to know what happened next, but there’s not really anything that I’m allowed to say about it! What I can tell you is that I’m still with the best company in my industry, and I’m still enjoying it each day.

So I know what you’re probably thinking…

Why did you stay at Rackspace?
It’s easy to answer this question: I learn something new every day at Rackspace. Sometimes it’s something technical, and sometimes it’s something related to managing people or designing technology. The people that I share this learning opportunity with make it all worthwhile. I’ve never worked for a company where my managers cared so much about my personal and technical development. Also, I’ve never worked at a company where, as a manager, I’m encouraged to care for my own technicians’ personal and technical development.

If you have any more questions about why I love working at Rackspace, please let me know. I’ll be happy to fill you in.