05.26.07
Posted in Technology at 5:04 pm by Stoner
MySQL is a great database. Packed with tons of features. Very easy to set up and get running. Easy to maintain. But it does have some serious drawbacks if you want to set up enterprise databases. I’m presenting my opinions about the MySQL 5.0 release of the database, based on my direct experiences with managing several medium-sized databases
Immature stored procedures
Beginning with MySQL 5, developers and DBAs now have access to triggers and stored procedures. These two key pieces of technology have been in other databases for years where they’ve had time to mature and grow with the product. MySQL’s implementation hasn’t and there will be growing pains to go along with it.
Backup and restore
Obviously, backing up and restoring a database is a critical business continuity function. The mysqldump utility is very flexible when it comes to backing up a MySQL database. You can be very picky when it comes to the database, the tables it backs up, even using a WHERE clause to get more fine-grained with the data. It generates straight SQL statements with some special MySQL extensions enbedded in comments. To restore, you simply pipe the dump file into the mysql client program and it’ll execute the SQL statements in order and rebuild the database/tables/rows. There are a couple problems with this approach:
- If you use the MyISAM storage engine you may encounter what I call “the back up lock up.” The MyISAM storage engine only supports locking at the table level. This means that when you make a backup, you lock all the tables for read access (so the dump is consistent across all tables being backed up) while mysqldump does it’s thing. This means your application could come to somewhat of a halt, depending on how well it handles not being able to write to the database or prolonged pauses. You can, of course, choose to not lock the tables, but then you have no guarantees that the data across tables is consistent. Fortunately, mysqldump is very fast. For small databases (a couple hundred megs or smaller) mysqldump will lock the tables for a minute or less. For a 5 gig database, expect to be locked for 5-10 minutes.
- Now that you have a backup, you may need to restore it from time to time, either on a developer/QA box to test new code, to verify the backup/restore process works (you *are* doing this, aren’t you?) or because a hard drive crashed and you need to restore the data on the production database. Again, the storage engine comes in to play. MyISAM tables can be written to very, very fast and a restore of hundreds of megs worth of data can be done in a matter of minutes. The InnoDB engine, with its transaction and foreign key support, takes quite a bit longer. A 5 gig dump file will take hours to restore.
The commercial database players have more sophisticated means of performing backups and restores that don’t suffer from these issues.
Relatively few who really understand how MySQL works
With the “big boys” in the database arena, there are a good number of highly qualified DBAs out there who know how to run the system and make it purr like a kitten. There are certification programs, conferences, books, people who “grew up” with particular databases, etc. With MySQL, you’ll find a lot of folks like myself - admins or developers who know how to set up and the basics of MySQL, but aren’t real DBAs. We got that title out of necessity (no one else available to do it.) For true enterprise implementations, you need someone who eats, sleeps, breathes MySQL 36 hours a day. MySQL AB does offer training so it’s only a matter of time before there is a large pool of MySQL DBAs out there.
Replication works to a point
MySQL has a simplistic replication feature that allows you to easily maintain copies of data on multiple database systems. In fact, its commonly used to build a “cluster” of read-only databases that an application queries from. The app will need to write to the master database, of course, but this gives you the ability to scale out your capacity to query data. The problem with replication is, when you have a database that gets a lot of INSERT/UPDATE/DELETE queries, you can find yourself in “replication lag,” where the replication slave databases fall behind the master. This happens because replication only uses 2 threads: one to read the data from the master and one to write it to the local data files. On the master, there are several threads writing to the data files but on the slave, there is only one (because replication works at a different level than regular database connections.) More processors won’t help, only faster processors and faster disk drives. You’re only other option is to replicate only a portion of the data but that may defeat the purpose.
Can MySQL be used on the enterprise? Absolutely. Should it be used in the enterprise? Absolutely. You need to examine where and how it will be used and plan accordingly. There are many big name companies out there using MySQL to run business-critical applications (Google uses it to run their company financial systems, Yahoo uses it a lot behind it’s web sites.)
You will get out of MySQL what you put into it. If you spend the money for a quality hardware platform and hire a high quality DBA, you can go far with MySQL. If you put it on generic hardware with little to no redundancy and let your overtaxed IT department (or a developer) manage it, it’s going to be a pain point for you. MySQL AB, the company behind MySQL, offers a commercial license and top notch support. While you’ll never “go it alone” due to the large community around MySQL (there are quite a few forums and web sites dedicated to MySQL out there,) having a phone number to call can be quite a time saver. And MySQL developers typically do the support for the commercial licensees, so it doesn’t get much better than that.
Permalink
05.24.07
Posted in Technology, Funny Stuff at 11:06 pm by Stoner
All Sys Admins start their day by reciting this prayer:
Our Server,
who art in the data center,
hallowed by thy CPU.
Thy computations done.
Thy process ended.
With results written to disk
and to tape.
Give this day our daily reports,
and give us not confusing errors,
but produce graphs and charts
for the PHBs I must endure.
Leads us not to core dumps,
but deliver us from lusers.
For thine is the Power Supply,
the RAM and Hard Disk, forever.
Amen.
Permalink
05.23.07
Posted in Technology, Hobbies at 5:01 pm by Stoner
One of my clients grows seemingly without bound. In the space of about 16 months, their Redhat Clustered MySQL database went from 2CPU/4GIG servers to 4CPU/8GIG servers to 8CPU/8GIG servers. We just put the dual quad-core Intel bad boys in last night. It pretty much went according to plan and the new cluster is humming along.
Also in that time frame, MySQL binary logging went from a few gigs a day to over 60 gigs a day (for those who don’t know, a binary log is a record of any change to the data in the database.) That’s a hell of a lot of inserts/updates/deletes. And that’s aggregated data, too!
I’m displeased with the current state of MySQL performance management tools available. Extracting the number of selects vs inserts vs updates vs deletes, something I assumed would be very basic and straight forward, doesn’t exist (that I’ve seen.) I figure I’ll have to break out some Perl/PHP and come up with some kind of trending/management tool. Oh joy.
I’ve also started a “smart” binary log purging script. When you use MySQL replication, you want to purge binary logs up to the log being used by the “slowest” replication slave. If you delete a binary log that’s being read by a slave (or is one of the next in line to be read) then replication will break and you have to either restore the binary log to manually update the slave or rebuild replication using a backup of the master.
Permalink
05.21.07
Posted in Technology, Life at 9:43 pm by Stoner
There’s an old axiom that goes something like:
“If you make something idiot-proof, they’ll come out with an improved idiot capable of breaking it”
That’s pretty much a Universal law. We’ve all witnessed it or been victims of it. Well, The Dept of Homeland Security commissioned Con Ed. and American Superconductor Corporation to build a power grid for New York’s financial district. Specs include resistance to weather and terrorist attacks.
The solution: liquid nitrogen cooled superconducting cables. Now, I’m all in favor of superconductors and liquid nitrogen. They’re awesome technologies. Using them in the transmission of power isn’t an application I would have thought of. Not because superconductors transmit electricity with very, very little resistance…but because of the economics of keeping the superconducting material at such a low temperature (-382 degrees Fahrenheit.) You need to keep liquid nitrogen cold and pump it through the wire. This is not a trivial task.
Even so, the benefits are tremendous, moving 10 times the power of traditional copper cables (depending on who you ask..the manufacturer says 100 times the power of copper.) That’s a very big bang for your buck.
My concerns are:
- rats - they chew through concrete and steel belted cables today…who’s to say they won’t make their way through these cables?
- tunnel/conduit collapse - will the cable survive a tunnel collapse or a crushed conduit (falling rock, subway train, etc.)?
- pump failure - if a liquid nitrogen pump fails, how long before the cable fails?
- inquisitive idiots - people explore…and people will explore areas with these new cables. Locks only keep out honest people. How long before someone decides to try and destroy one of these cables?
Honestly, I believe this new power system will fail in some capacity. Why? The two most devastating natural phenomenons: Mankind and Mother Nature.
Permalink
05.16.07
Posted in Technology, Linux at 11:59 am by Stoner
Redhat Cluster Services is very touchy. It expects everything to be set up properly or it’ll snap like a rubber band stretched too far. One of the things it expects is LSB-compliant init scripts.
Some init scripts do not implement these specs properly or completely. MySQL is a noted violator (both the scripts from MySQL and from Redhat - go figure.)
In particular, you need to be aware of two issues:
- does the script implement a ’status’ function?
- does the script return success when the service is already stopped?
Item #1 is critical to RHCS. It calls the init script once a minute, passing it the ’status’ parameter. If the script does not implement a status function, then RHCS will continuously bounce the service, thinking it has failed somehow.
Item #2 is a little more subtle. When a service is already stopped, stopping it again is of no real benefit/use and according to the LSB, it should be considered a successful action. If a stop-after-stop returns an error, then RHCS will assume something happened that cannot be resolved by failing over to another node and flags that service as failed, a situation that would require manual intervention to correct.
Since MySQL init scripts are notoriously broken, here’s what to look for. In the ’stop)’ branch of the script argument handling case statement (or in the ’stop()’ function if it’s used) there is usually an ‘if’ statement that checks for a MySQL pid file. Many times, it looks like this:
MYSQLPID=`cat "$mypidfile" 2>/dev/null `
if [ -n "$MYSQLPID" ]; then
Or even this:
if test -s "$pid_file"
then
Now, we need to check what happens if there is no pid file in the else clause. Here’s one example:
else
ret=0
action $"Stopping $prog: " /bin/true
fi
return $ret
Here’s another example:
else
echo "No mysqld pid file found. Looked for $pid_file."
fi
In the first example, the return code, zero, is stored in the ret variable. The action call prints a message to the screen and the /bin/true tells action to print a success message. This would be the preferred method of coding an init script.
In the second example, a statement is written to the screen. The return code is simply the exit status of the echo command, which is success (unless the echo command, itself, failed to write to standard out - something that isn’t very likely to happen.) While this example achieves the desired result, it also isn’t 100% LSB-compliant (since it doesn’t use logging functions for reporting the status) and should be avoided.
MySQL may not be the only init script to fail LSB-compliance. If you use RHCS for other services, you should check the init scripts to make sure they pass muster.
Permalink
05.14.07
Posted in Funny Stuff, Life at 3:07 pm by Stoner
It had to happen sooner or later. Jason Robert Stoner was busted in Florida for growing pot. Please hold the “is this one of your relatives” jokes. I’m not related to this moron, as far as I know.
This is just one more piece of wood for the fire. Do you know how many times cashiers/bartenders (and the like) ask me about my last name? Yes, it’s really my last name. No growing up in high school wasn’t weird or funny because there is actually a high percentage of people named Stoner in Southwestern Pennsylvania. In my high school class alone were two other people named Stoner. None of us were related in any way.
At least the article gives us a funny picture to look at. Damn does he look pathetic! I also chuckled when I read he’d be going to “Land O’Lakes jail.” Is that a veiled reference to anal lube? HA! 
Permalink
05.09.07
Posted in Political, Life at 8:53 pm by Stoner
A friend’s blog referenced this article on The Onion. Now, the article is satire but it strikes a particular and terrible cord: as we get older we become desensitized to violence and ineptitude. You get pounded day and night by the media spewing bad news and worse news. Murder rates, death tolls, domestic abuse, scandal, corruption…it gets to the point people don’t care - they simply want to get on with their lives. Then, when a particularly heinous crime is committed, they turn away, content to let someone else deal with it only no one deals with it.
As you get older, do you wonder why young people are so vocal and active? They haven’t built up their immune systems to the crap constantly shoveled at them. After so long, they throw in the towel and become “normal” people. I’m not talking about changing attention spans as in “what’s the crisis of the month?” - I’m talking about apathy towards everything outside of their immediate sphere (family, work and community.) A few folks are able to stay steadfast and stick with their activism. I applaud them.
Where this is particularly frightening is politics. Either GW is the most brilliant politician in US history or he really is *that* dumb. Clinton got impeached for oral sex. GW lied, lied and lied some more, got us involved in a war that has a cloudy outcome as far as democracy in Iraq and now he’s going to make a move to do the same in Iran. When most of the population suffers from the apathy I described, the political leaders will get away with whatever they want because they know the people won’t do anything to stop them.
What is it going to take to slap the stupid out of people and get them to act?
Permalink
05.07.07
Posted in Technology, Linux, Life, Family, Hobbies at 4:07 pm by Stoner
Well, maybe not Super Colossal but busy, none the less. On Saturday, I drove to the NOVALUG meeting to sit on a panel for a database discussion. I represented MySQL, Peter took Oracle and Angelo was PostgreSQL. We each did a 10 minute run-down of the major features of the database, then into a Q&A session. Or at least, that’s what we planned to do. Had to take a lot of time to define basics of databases, including terminology like “tables”, “columns” and “database.” I guess I assumed too much of the audience.
We had some good discussions around questions like “How do you size a database server for 500 gigs of data? For 1 terabyte of data? How do you detect and repair corruption? Can I have the database fire off an event when a piece of data changes” etc. Afterwards, a buddy of mine and I went to Old Dominion Brewery for lunch. I had my usual, Angus burger (medium rare) with fries and an Oak Barrel Stout (draft, not bottle.) If you can find Oak Barrel Stout, give it a shot - but be warned, they change the recipe slightly for the bottled version. OBS draft is a whole other world of goodness.
After I got home, I played World of Warcraft for a few hours then went to bed.
On Sunday, after dealing with a client melt-down, I grabbed my .45 and 410 shotgun and headed to Blue Ridge Arsenal to squeeze off a few hundred rounds with my friend. He had his .38 revolver and rented a 9mm pistol and we spent a good hour blowing holes in paper targets. I did manage to shoot myself in the finger….with a staple gun. I reached around the cardboard to put pressure on it so I could staple the target to it and CLICK. Damn staple went into the tip of my trigger finger. Afterwards, we went to Starbucks for some coffee and lengthly discussions on Brazilian strip clubs, Bruce Campbell’s appearances in the Spiderman trilogy, rednecks and other topics of interest.
When I got home, I rinsed the dirt off my car (you have to driver a half mile on the dirt road to get to my house.) Then I picked up a lot of branches and crap in my front lawn. I never raked the leaves from the fall and the wind storms of this spring blew a lot of dead branches down. Yard work is satisfying work…especially when it’s your own lawn. I have a huge pile of leaves to mulch up…and nothing that needs mulching so I’ll have to dump it in the woods out back. Oh well.
Permalink
05.04.07
Posted in Life at 4:29 pm by Stoner
I Netflixed Tideland about 2 months ago and it arrived in the mail last week. I didn’t have time to watch it until last night. All I can say is holyfuckingshit!
I’m a fan of Terry’s work like Monty Python, Brazil, Fisher King, etc. Terry does things…differently, we’ll say. He has a unique vision and when it hits you, it hits you like a runaway freight train. Tideland is no exception.
I’ve read several user reviews of Tideland and those people just don’t get it. They harp on the drug use and the kissing and stuff. The DVD I watched had an intro by Terry in which he said “this movie is seen through the eyes of a child.” Apparently, some people forgot what it is to be a child and were repulsed at what they saw. Oh well. Their loss, in my opinion.
The movie itself was excellently executed. I loved the visuals, the settings and the story line. The performances were all top notch with one exception. Brendan Fletcher’s portrayal of Dickens, the mentally handicapped brother of Dell, went beyond excellent. It’s hard enough to portray someone who is mentally handicapped in a way that’s accurate and believable (Forrest Gump - are you kidding me?!) but to portray them *and* give them a soul, to show you that there is a living, breathing, loving, caring, fearing person inside is exceptional. I think he stole the show in that movie.
Permalink
