08.17.07
Posted in Technology, MySQL at 2:32 pm by Stoner
MySQL has a unique feature whereby you can select the storage engine for each table. If you don’t know what a storage engine is, it is responsible for managing the physical data on disk - from data files and indexes to the memory buffers used to access the data and the locking needed to prevent multiple people from clobbering each other’s changes. This is both a boon and a bane for Systems Administrators and DBAs.
It’s a great feature because developers, Sys Admins and DBAs can select the engine with the properties they require. For example, the InnoDB engine supports ACID-compliant transactions, foreign keys and row-level locking. The MyISAM engine, while being lightning fast on reads and supporting concurrent inserts, does not have ACID-compliant transactions or foreign keys and implements table-level locking. Other engines are available, such as memory-based tables, clustered tables, federated tables (reference a table on server B from server A) and more. This level of flexibility can’t be found in any other database server.
This flexibility comes with a stiff price, though. If you mix engines in a single database then you must be aware of several limitations:
- Foreign keys support in the InnoDB engine does not extend to other engines, meaning you can’t have a InnoDB-based table define a foreign key on a MyISAM-based table.
- Likewise, transaction support in the InnoDB engine does not extend to tables defined using another engine.
- Some buffers are engine-specific so you may need more memory than if you used a single engine for all tables.
Permalink
07.13.07
Posted in Technology, Life, MySQL at 12:04 pm by Stoner
Ok…I’ve been crazy busy with job and home stuff. Doesn’t mean I stop thinking about stuff.
- Do we really need another text editor/Personal Information Manager/MP3 player? I monitor Gnomefiles and Portable Freeware RSS feeds and I’m constantly seeing these types of apps being created, promising to be a better than anything out there with whiz-bang features not seen anywhere else. Got news for you, you aint showing us nuttin’ new.
- The First Bank of Delaware needs to DIAF for offering loans at 99.25% interest. That’s right, 99.25%! It’s done through their CashCall marketing arm. Yes. The one offered up by has-been Gary Coleman. They’re preying on people who can’t manage their money.
- MySQL-Proxy is damn cool.
- MySQL-Proxy combined with memcached rocks da bomb!
- Managing hundreds of servers with Microsoft Office documents and Microsoft Sharepoint portal totally sucks nuts. I’d rather slash my wrists with a dull blade, then pour salt in the wounds.
Permalink
06.20.07
Posted in Technology, Life, MySQL at 11:09 pm by Stoner
Been a while since I wrote anything…not for lack of anything to write, just real busy. I’m finding myself having to force myself away from the computer to maintain my sanity.
On the home front, I’m going to be installing a ceiling fan in the only bedroom that doesn’t have one. I had to go buy a metal junction box because all the boxes in this house are the blue plastic ones. That’s fine and dandy but I’m not going to hang a ceiling fan from one. Don’t know if I’ll need it but I also bought a brace so I can mount the box to a rafter if it isn’t close enough to one already. After that, I’m going to start ripping down the ceiling in the basement in preparation for remodeling it. It’ll be a nice mess.
Then I’m off to California for a week - business related. I fly out Sunday morning at 7:30 and fly back Saturday.
Attended a MySQL webinar (man, do I hate that word) on replication and scaling out MySQL. I’m very familiar with replication but wanted to see what the MySQL folks are up to. Also got approval (and advance payment!!!) for MySQL training in Washington, D.C. in July. I don’t have to fork over a couple grand and submit an expense report after - which is nice.
Someone implemented an idea I had been working on - namely backing up MySQL binary logs on a MySQL replication master server. I had a shell of such a script written in Perl - this guy did it in bash. Six of one/half a dozen of the other - I can just reuse his code and not reinvent the wheel. Open Source is beautiful that way.
Found two scripts for making practical sense of the MySQL “show status” command. Problem is, they generate flat, unparsable text and don’t retain the info for later trending. Pity. I’ve started work on that project so hopefully I can start to get a better understanding of how our MySQL databases are performing over time.
Permalink
06.09.07
Posted in Uncategorized, Technology at 3:01 am by Stoner
It seems that every day, Microsoft gives stronger and stronger evidence that they are bad for business. Bug and security holes in products aside, they are professional double-talkers. They either outright lie or say one thing then spew something else shortly after. It’s disgusting. Here’s a couple examples.
Microsoft has, for a while, been saying that Linux and other Open Source Software projects infringe on many software patents. They kept building up, hinting about possible lawsuits, etc. Then they finally broke out and stated that Linux and OSS violates 235 of their patents. They even break them out into general categories like email-related or GUI-related. But, when pressed for details, Microsoft claimed they couldn’t specify which patents because it would be too cumbersome, administratively, to collect that information.
Umm. Hello? If you counted them, then you already collected them into some tabular format. Otherwise, how could you count them? This is the type of bullshit business practices I’m talking about. Here’s another one.
Jamie Cansdale, of London, was awarded a Microsoft Most Valuable Professional award for his work on a product that allows developers to better test the program code they write. The product integrated into Microsoft’s Visual Studio product and was available for all versions of Visual Studio. Later, Microsoft started threatening him with lawsuits because they didn’t want his product to be used with their “Express” version.
What the hell kind of message is that? “We’re awarding you one of our most prestigious honors. Oh, and by the way, we going to sue you for making it available for all versions of our product.”
So, ask yourself. Is this the type of company I want to do business with?
Permalink
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.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
« Previous entries · Next entries »
