Category: Server Tuning

Today I was musing about how great \c is, and how it seems that many people are not aware of it, even though it’s in the banner of MySQL, and has been for a while.

So I decided to see what else I could find. I typed \h and found that a simple \W will show all warnings automatically after a query. That is great for the commandline interface! Now I just have to figure out if it’s possible to automatically do a \W (or “warnings”) whenever I log in with the commandline client.

So, how do I do it?

How do I enable comments to be posted immediately, but seem to avoid spam? Well, there are 2 features I employ. The first is that comments are moderated, but *only* if they contain more than 3 links. All the spam I get is a series of links, and there’s only been one time that a real message has been put in the moderation queue because it contains a series of links. And I have not yet had spam with 3 or fewer links, so it works out OK by me. I get an e-mail whenever a comment is in the moderation queue. About once a week, I go to the moderation panel and delete about 15 spam messages.

The second feature is that I have all comments e-mailed to me, so if a spam comment does get through, I see it right away.

This saves a lot of time — the real messages are sent to me, so I get feedback right away (as do other readers). The spam messages get sent to me in full, so I just glance at the message, delete it from my inbox, and deal with the moderation queue when I want to.

Oracle bought 2 companies which develop 2 open source storage engines that MySQL uses. Those storage engines are both transactional.

There’s the possibility Oracle does the stupid (from a PR perspective) move of making those databases proprietary. But it’s OPEN SOURCE right now. Which means that it’ll branch.

Worst case scenario? The developing stops here, and InnoDB and BDB never get any more functional than they currently are.

The “bad” scenario? MySQL is a company. With hired staff. They pay the hired staff to get familiar with the source code for those engines and continue to develop them. Hence the branch.

The likely scenario? 1) Oracle tries to have a pluggable storage engine architecture like MySQL. Oracle Express or Lite or whatever would be free with those engines, pay with the original. 2) Oracle tries to go on the good partnership of “we support MySQL by developing their architecture, we’re so nice, buy our product.”

Really, everyone is worried over nothing. The whole point of open source is that it’s open! Once you go open, you can close later on and people still have something to work with.

For the geeky folks running a non-windows operating system on a windows system, what are you using, why, and do you like it?

I just found colinux, which looks good, and I’m installing mysql on it now. I’ve used cygwin and it’s just a pig, and things don’t work so well there.

VMware costs $$, and also isn’t really great either (I’ve had stability problems). What do folks think about these three?

This looks neat, however they are charging a fee. Even if you’re already a Safari subscriber. . . that seems like the only bad part of the idea.

The following is a release from O’Reilly:

O’Reilly’s Safari Books Online has just announced a new service called Rough Cuts that gives you early access to content on cutting-edge technologies months before it’s published. Rough Cuts allows you to purchase work-in-progress manuscripts of selected titles. You’ll even have the chance to shape the final product by sending feedback to the author and editors. The beta version just debuted with four works-in-progress covering Ajax, Ruby, and Flickr. For more information, go to:

http://www.oreilly.com/roughcuts/

Titles now available:
Ajax Hacks: Rough Cuts Version
http://www.oreilly.com/catalog/ajaxhks/

Flickr Hacks: Rough Cuts Version
http://www.oreilly.com/catalog/flickrhks/

Ruby Cookbook: Rough Cuts Version
http://www.oreilly.com/catalog/rubyckbk/

Ruby on Rails: Up and Running: Rough Cuts Version
http://www.oreilly.com/catalog/rubyrails/

Rough Cuts FAQ
http://www.oreilly.com/roughcuts/faq.csp

Why didn’t I think of this? Tom Limoncelli was nice enough to create a livejournal feed for this site — http://www.livejournal.com/users/sheericom_feed/

Direct link to add as a ‘friend’ if you’re logged in — http://www.livejournal.com/friends/add.bml?user=sheericom_feed

Having a sysadmin background, I am fairly familiar with cron. I’m amazed at how many companies set up cron jobs and assume everything will just work. If root mail is not checked (or forwarded to someone who actually reads it), a cron job could fail and you would never know.

Having an automated task fail silently is a Bad Thing (TM). I take commands which should have no output normally (such as mysqldump > dumpfile.sql and mysql < maintenance.sql) and pipe the output into a script called mailif. The script will e-mail a specified recipient if standard input is not blank.

In plain English, if the automated task has ANY output or error code, it e-mails me.

One of my current tasks is going through all the database maintenance scripts and making sure it e-mails me if there's an error.

Standard cron entries have 6 fields:
minute 0-59
hour 0-23
day of the month 1-31
month 1-12
day of the week 0-7
command (the rest of the line)

A wildcard, *, is permitted. So you can easily have a command (or a script) run every minute, every hour, every day, every month, or every Monday. You can also do more complex routines like run a command when there's a "Friday the 13th during October."

So a backup script to dump tables in a database every day at 6 am would have the following crontab entry:
0 6 * * * mysqldump --all-databases > /var/log/mysql/dump/backup.sql | /usr/local/bin/mailif -t user@domain.com "output of mysqldump"

If the mysqldump fails, cron will send user@domain.com an e-mail with the subject of “output of mysqldump” and the body will be the output.

One thing I learned looking at the cron entries that were setup by someone else is that to set up a routine occuring every ten minutes, the minute field has */10 in it. Fascinating, given that I’d always written out a comma-separated list 0,10,20,30,40,50.

Right now, I’m craving the INFORMATION_SCHEMA database. Specifically, to compare tables for similarity. Sure, I can diff the results from SHOW CREATE TABLE, or mysqldump, but having the INFORMATION_SCHEMA will be much more handy.

Sigh.

So, yesterday, just one day after writing a short article about ENUM and SET and having it pointed out to me that data does not have a place in data structures (which is absolutely correct!), what did a developer want me to do at work but change an existing ENUM to add one item?

Well, having learned a bit, I suggested an alternative. The performance of our site depends on using ENUMs in the way I described, so we must continue to use them. Additionally, I was listening to Robin Schumacher making comments about how some folks “over-normalize” their data and need to do too many JOINs as a result.

Technically speaking, it’s not possible to “over-normalize” a data structure. But I’ll let that slide, because I know exactly what he meant.

At any rate, the alternative I suggested was to add all possible data points, not just the one, to the ENUM, thereby saving future ALTER TABLEs.

So a thank you to Kolbe, Beat and Arjen. I also would like to let people know that I am not perfect, and will attempt to understand mistakes I’ve made gracefully. I’m not perfect, that’s for sure, so do feel free to point out where I’m not correct.

So, I have not been able to find a MySQL magazine in print. Why is that? We’ve got great blogs out there, you might argue, and http://www.planetmysql.org is practically its own magazine, though not in print.

Still, it seems like it would be worth the effort. Thoughts? (http://mysql-journal.com does not seem to be working for me)