Racker Hacker

After running the idea by some of my fellow technical folks, I’ve considered making an array of screencasts aimed to prepare people for the MySQL DBA exam. I haven’t decided to make them free or charge for them as of yet, but if I did charge a fee, it would be much less than getting the training from MySQL ($2,499USD in most locations).

So, I have two questions for the general techy community:

1. What tools/applications would you recommend on a Mac for making high-quality screencasts that have a professional feel?

2. Would you pay for these screencasts (if they are really good), and if so, how much would you want to spend in total to get all of the screencasts for the DBA certification?

Feel free to add comments to this post, or you’re welcome to drop me an e-mail at major at mhtx dot net. Your feedback is greatly appreciated!

I finally remembered this book when someone asked me about how to get started with PHP and MySQL development. If you get the chance, get a copy of this book:

PHP and MySQL Web Development by Luke Welling, Laura Thomson
Barnes & Noble: http://snurl.com/265xp

Why do I like this book so much?

• Teaching by application - The book teaches fundamentals by showing how to apply techniques to an active website. There’s not a ton of theory to wade through, and you feel like you’re learning the material faster.
• Intertwined strategies - You learn how to get PHP working with MySQL, and then you learn how to optimize your code. It’s important to know which work is best done by PHP and which is best done by MySQL. This book teaches both.
• Lots of examples - The CD-ROM comes with tons of code examples that actually relate to something you can use.

I’d be happy to loan my copy, but I’ve loaned it out and it never returned.

I’ve just upgraded RackerHacker to Wordpress 2.5.1. If you haven’t upgraded your own blog installation yet, I’d recommend doing so soon!

Download It
Upgrade It

Call me weird - but I like to know where people host their sites. When I get a link to a nice-looking site, I’ll get there and think, “Hmm, I wonder where they host.”

Part of this curiosity probably stems from being a server administrator. I’ve used seven different dedicated server providers before along with 5 different VPS providers. There’s quite a few shared hosters thrown into the mix as well. I like to know which companies that people use when they have a site that is tremendously vital to their everyday lives.

Without further ado, here’s some of the politicians’ hosters that I’ve found (in alphabetical order):

Bob Barr - Rackspace (67.192.188.81)
Hillary Clinton - Rackspace (72.32.103.48)
John Edwards - Internap (70.42.42.155)
Rudy Giuliani - Core NAP (64.20.231.77)
Mike Gravel - Media Temple (64.13.237.176)
Mike Huckabee - HostMySite (208.112.83.62)
Alan Keyes - ThePlanet (74.52.145.91)
John McCain - Smartech Corporation (64.203.107.147)
Ralph Nader - FutureQuest (69.5.9.21)
Barack Obama - Pair Networks (66.39.143.229)
Ron Paul - Rackspace (74.205.22.51)
Bill Richardson - Engine Yard (65.74.179.43)
Mitt Romney - Rackspace (72.32.175.83)

It’s always been a bit of a challenge to disable TRACE and TRACK methods with Plesk. The only available options were to create a ton of vhost.conf files or adjust the httpd.include files and prevent modifications with chattr (which is a bad idea on many levels).

Luckily, Parallels has made things easier with a new knowledge base article.

Before getting started, it’s important to understand why MySQL uses locks. In short - MySQL uses locks to prevent multiple clients from corrupting data due to simultaneous writes while also protecting clients from reading partially-written data.

Some of you may be thinking, “Okay, this makes sense.” If that’s you, skip the next two paragraphs. If not, keep reading.

Analogies can help understand topics like these. Here’s one that I came up with during a training class. Consider two people sitting in front of a notepad on a table. Let’s say that a sentence like “The quick brown fox jumps over the lazy dog” is already written on the notepad. If both people want to read the sentence simultaneously, they can do so without getting in each other’s way. A third or fourth person could show up and they could all read it at the same time.

Well, let’s say one of the people at the table is writing a screenplay for Cujo, and they want to change “lazy” to “crazy”. That person erases the “l” in “lazy” and then adds a “cr” to the front to spell “crazy”. So if the other person is reading the sentence while the first person is writing, they will see “lazy” turn into “azy”, then “c_azy”, and then finally, “crazy”. This isn’t a big issue in real life, but on the database level, this could be dangerous. If the person who was reading the sentence showed up during the middle of the letter changes, they would think that the dog was “azy”, and they’d walk away wondering what the adjective “azy” means. To get around this, MySQL uses locking to block clients from reading data while it’s being written and it blocks clients from writing data simultaneously.

Now that we’re all familiar with what locks are, and why MySQL uses them, let’s talk about some ways to reduce the delays caused by locking. Here’s some situations you might be running up against:

Writes are delayed because reads have locked the tables
This is the most common occurrence from the servers that I have seen. When you run a SHOW PROCESSLIST, you may see a few reads at the top of the queue that are in the status of “Copying to tmp table” and/or “Sending data”. On optimized servers running optimized queries, these should clear out quickly. If you’re finding that they are not clearing out quickly, try the following:

• Use EXPLAIN on your queries to be sure that they are optimized
• Add indexes to tables that you query often
• Reduce the amount of rows that are being returned per query
• Upgrade the networking equipment between web and database servers (if applicable)
• Consider faster hardware with larger amounts of RAM
• Use MySQLTuner to check your current server’s configuration for issues
• Consider moving to InnoDB to utilize row-based locking

Reads and writes are delayed because writes have locked the tables
Situations like these are a little different. There’s two main factors to consider here: either MySQL cannot write data to the disk fast enough, or your write queries (or tables) are not optimized. If you suspect a hardware issue, check your iowait with sar and see if it stays at about 10-20% or higher during the day. If it does, slow hardware may be the culprit. Try moving to SCSI disks and be sure to use RAID 5 or 10 for additional reliability and speed. SAN or DAS units may also help due to higher throughput and more disk spindles.

If you already have state-of-the-art hardware, be sure that your tables and queries are optimized. Run OPTIMIZE TABLES regularly if your data changes often to defragment the tables and clear out any holes from removed or updated data. Slow UPDATE queries suggest that you are updating too many rows, or you may be using a column in the WHERE clause that is not indexed. If you do a large amount of INSERT queries, use this syntax to enter multiple rows simultaneously:

INSERT INTO table (col1,col2) VALUES ('a','1'), ('b','2'), ('c','3');

This syntax tells MySQL to hold off on updating indexes until the entire query is complete. If you are updating a very large amount of rows, and you need to use multiple queries to avoid reaching the max_allowed_packet directive, you can do something like this:

ALTER TABLE table DISABLE KEYS;
INSERT INTO table (col1,col2) VALUES ('a','1'), ('b','2'), ('c','3');
~~~ many more inserts ~~~
ALTER TABLE table ENABLE KEYS;

This forces MySQL to not calculate any new index information until you re-enable the keys or run OPTIMIZE TABLE. If all of this does not help, consider using InnoDB as your storage engine. You can benefit from the row-level locking, which reduces locking in mixed read/write scenarios. In addition, InnoDB is able to write data much more efficiently than MyISAM.

If you’re working in Plesk and you receive this error:

mchk: Unable to initialize quota settings for someuser@somedomain.com

Run this command to fix the issue, but be patient:

find /var/qmail/mailnames -type d -name '.*' ! -name '.spamassassin' -ls -exec touch '{}'/maildirfolder \; -exec chown popuser:popuser '{}'/maildirfolder \;

Thanks to Mike Jackson for this one.

MySQLTuner v0.9.0 is now available. There is a bug fix and also a new feature!

Fixed a bug in the enumeration/sizing of tables in MySQL 5
In MySQL 5 on some distributions, a NULL is returned for the storage engine and data length. Luuk Vosslamber quickly e-mailed me about the bug yesterday and it has been fixed.

MySQLTuner version checking
MySQLTuner will now check to see if a new version is available when the script runs. You can disable the check with the --skipversion option if you do not wish to perform the check.

The version check does not submit any information about the server, the MySQL installation, or any of your MySQL data. It simply queries a page on mysqltuner.com with the version number of your currently running script. Based on the value returned by the page, MySQLTuner will alert you if a new version is available.

To download the new version right now, please go to the project page and use the download links.

MySQLTuner v0.8.9 is now available.  There are a few bug fixes, performance improvements, and readability adjustments.

Table enumeration and sizing can now be skipped
I’ve received reports that MySQLTuner will stall while enumerating tables on servers that contain a lot of tables or a lot of large tables.  You can now use the --skipsize option to skip over the table enumeration process and let the script finish.  However, you will not be able to receive some recommendations since the script was unable to determine which storage engines are enabled.

New table enumeration and sizing method for MySQL 5
The script now uses information_schema to enumerate tables and their sizes for MySQL 5 servers.  This has provided a drastic improvement in performance.

Readability improvements
The recommendations for query_cache_limit and max_heap_table_size/tmp_table_size have been improved.

As always, I welcome your suggestions, bug reports, and questions!  Please feel free to drop a comment on this blog posting or send me an e-mail (it’s in the script).  Also, don’t forget to sign up for the MySQLTuner version announcement list.

DISCLAIMER: Okay, technical folks - I’m doing this as a favor to the general community of people that aren’t very technical, but they need to know some tips for ridding themselves of a technical person that is harming their business. If you look at it this way, there’s a 50/50 chance that this article might get you hired instead of fired.

No one has every really asked me “hey, if I want to fire my technical guy and get a new one, how do I do it?” So, how can I answer this question with any authority? Simple. I used to run my own company doing technical work for homes and businesses, I was hired and fired by people (much more hiring than firing), and I’ve learned a lot from being “the tech guy”. Also, from working at Rackspace, and a previous job, I’ve seen many situations in which a company lets their technical person go without any plan in place. You never realize how valuable your IT staff are until they’re not in the office, and your e-mail server falls apart.

Firing your technician

I’ll start with how to fire your current technical person. It should go without saying, but be sure that you’re firing this person for a substantial and legal reason. If you’re firing this person for something trivial or petty, stop right here and re-evaluate. But, if this is the kind of person that ignores your phone calls, takes down services to increase job security, or prints pornography on the office laserjet, then it’s time for them to go.

First, create a plan. How much does this technical person know about the company that could be detrimental if they were fired abruptly? You’ll need to consider things like their access to your buildings, computers, corporate credit cards, cars, and colocated/dedicated servers. Take an inventory of the access that they have, and also how they access these items. For example, if they have multiple user accounts on your computer network, then make sure that all of those accounts are accounted for. If you have secret passwords with any of your service providers, be sure those are documented as well.

If you don’t know some of these items, but your technical person does, you might want to get this information from them in a careful manner. I’d recommend against going in and saying something like “we need to inventory all of our user accounts before you’re canned”. You need to find a plausible excuse for you to have a list of this information, and it needs to be something that the technical person won’t argue with. Some good ones I’ve heard are PCI, SOX or SAS/70 compliance. Let your employee know that compliance with these standards requires that you keep all of the access to all of these services in a safe place.

By the time you reach this stage, you really should have a new technician in mind. Interview them after work, or at night time so that the current technical person doesn’t become suspicious. I’ll talk more about how to find a new technical person a little later.

At this point, if you can trust your technical person, you should have a proper list of their entry points into your infrastructure. It’s more likely that you don’t trust this person at this point since you’re firing them after all. Some might argue with me here, I’d recommend bringing in some other technical person that you undoubtedly trust. The reason for this is that your technical person may have given you a partial list, or they may have left “backdoors” so that they can access your infrastructure after they leave. A trusted tech can review your company for any possible issues and can give you a heads up if they find any red flags. Of course, if your current technical person has set up traps to know when someone logs in, then you may have blown your cover entirely. I would certainly hope that your situation wouldn’t end this badly.

Now that you have a complete list of everything to which your former tech had access, you have an idea of what will be involved in changing everything over. Most people like to fire employees on Fridays to reduce the chance of violence or uncomfortable moments, so here’s my recommendation. If anything financial needs to be taken care of, get it done late on Thursday or early on Friday. Then, on Friday, set a time with your current technical person to have a short meeting. Coordinate this time with your trusted technical person so they can begin changing passwords on accounts which the current tech has access to. Change the most sensitive passwords first, like the passwords on database servers. Also, change the root passwords as a high priority, but make sure you eventually change them all since you can be bitten by sudo or SSH keys.

When your current technical person exits the meeting, you’re covered. If the meeting goes well, and the current technical person is amicable, then you’re going to be covered since their access is revoked. If the meeting goes badly, your still covered in case they try to do something nasty. Their access to your network and corporate infrastructure should be eliminated or minimized before they can do anything destructive.

Hiring your technician

Luckily, hiring a new technical person is a bit easier than firing one. However, if you do a bad job on the hiring, you’ll be referring to the beginning of this article fairly soon.

The best way to find a new technical person is via recommendations from another person. They’ve probably had interactions with the tech, and they can give you an idea of their technical prowess and social skills (yes, these are important). If you can’t find any techs through recommendations, you can always check big job sites like Monster, LinkedIn, or Dice. Whichever route you choose, be sure to meet the technician in person. Don’t hire someone based on the initials after their name, their previous job experience, or how they sound over the phone. Your technical person is like a central pillar in your organization, and this needs to be a responsible, sensible, and practical person.

Once you’ve found one or more technicians that you’d like to hire, you need to test them just a little. I’d recommend contacting them late in the evening (8-10PM) or early/late on the weekend. See how receptive and cordial they are at these times, because when something explodes later, you’ll probably be calling them after business hours. You don’t want to pick up the phone at 4AM on Saturday when your Exchange server dies only to hear your tech tell you that he’ll be in on Monday to fix it for you, and that it can wait until then. When you talk to the technician on the phone, ask them to do something that forces them to go use the computer. For example, send them an e-mail for something reasonable that they need to respond to. Or, tell them that you discovered some neat product or service, and you want to know if they could start working at your company and maintain that product or service. If they respond quickly and they don’t give you the vibe that you’ve just inconvenienced them horribly, then that’s a good sign that you’ve found a worthy technician.

It’s up to you when you bring them on at your company. Some people might want to hold off until the current technician is out of the way, but some might want to bring the technician in a little early to help with the cleanup of the last technician. Either way is good in my opinion. However, I would recommend against having both of them employed at your company simultaneously. If your old technician is upset about something, that could rub off on the new guy, and you may be returning to the top part of this article sooner rather than later.

Also, don’t expect the new technician to be knee-deep in your problems immediately. They will need some time to figure out your network, review your vital services, and get an idea how everything works together. If you have the giant list your previous tech made, be sure to furnish it to the new technician so they have an idea of where to go to fix a certain problem.

I certainly hope this article helps! If you have any questions, drop me a comment and I’ll be happy to give additional recommendations.