Category: Backup

How do you teach a thinking pattern? In my previous job I worked with a data analyst, who was really good at thinking about how our data correlates among itself. He was good at the data analysis, good at thinking up relationships, and good at coming up with complex comparisons.

However, he wrote some pretty funky SQL. We had him take a course in basics of MySQL, and from time to time I’d take a gander in the slow query logs and pick a few queries and point out the good and bad things. Most of these are optimization tips, such as “Indexes don’t apply to columns when you’re applying a function to that column”.

But it’s hard to try to think outside of your own box, and even running EXPLAIN on every query won’t necessarily tell you how you can fix a query. The problem is that there’s no way to know if you have optimized the query as much as possible. Even experienced DBAs

I was often frustrated when repeating concepts — though I always took a different approach, since I find that if I have to repeat myself it means that it didn’t sink in with the way I said it before. But I know many people, myself included, have managed to learn how to think differently.

The question, I guess, is how do you help someone else think differently?

http://www.oracle.com/openworld/2007/appreciation.html

Now, Billy Joel is one of my all-time favorite pop musicians. I saw him in concert and nosebleed seats at the Boston Garden cost me USD $100 per ticket, and I bought 4 tickets (my twin brother is a die-hard Billy Joel fan, they were a holiday surprise 2 years ago!)

Billy Joel regularly sells out sports arenas. I can only imagine how much Oracle paid to have a concert with him.

And don’t get me wrong, the rest of the list is also stellar. Which only adds to my disbelief.

MySQL shows customer appreciation by not grossly overcharging.

MySQL: Because you’re smart enough to buy your own damn concert tickets.

So, at midnight I got a call from customer service saying our site was slow. I narrowed it down to one of our auxiliary databases, that seems to have gotten wedged just about midnight. Normal queries that took less than 4 seconds started taking longer and longer, moving up to 5 seconds and past 30 seconds in the span of a minute or so.

In the moment, I thought killing off all the queries would be a good move. My kill script, which consists of:


for i in `/usr/bin/mysql -u user -pPass -e 'show full processlist' | grep appuser | cut -f1`
do
mysql -u user -pPass -e "kill $i"
done


This will attempt to kill any mysql connection owned by the appuser. I used it a few times, and it didn’t work. So I used a trick I learned when we bring our site down — sometimes there are straggling connections to mysql, so what I do is change the app user’s password by direct manipulation of the mysql.user table and flush privileges.

Within 10 seconds, all the connections from the appuser were gone, and when I put the correct password back and flushed privileges, everything came back normal. Queries started taking their usual amount of time.

Why is it that queries that refused to be killed by “kill”, and yet changing the password for the user they were running as killed them off? Some were running more than 45 seconds, in various states of “Sending data” and “closing tables”. Nothing was running for much longer than 60 seconds, so it doesn’t seem like there was a big query that was wedging things.

Oh, and what happened at midnight to cause this? No clue, the only thing we run at that time is a PURGE LOGS FROM MASTER, which we do every hour, as we fill up a 1.1G binary log file every 20 minutes or so. This database holds a particularly heavy write application and also runs reports, so we optimize the tables every week (wednesday at 2 am). I’ve put the optimization to daily, as when I ran it manually this morning it took about 20 seconds.

Anyone have an idea about why changing the password worked so quickly when kill did not?

aka, “Better late than never”…..

Back in March 2007, the Boston MySQL User Group (http://mysql.meetup.com/137) watched and heard Brian DeLacey give a tutorial of Ruby on Rails, including its interaction with MySQL using ActiveRecord.

I knew absolutely nothing about Ruby on Rails before attending the presentation, other than Ruby was a language and people were saying that Rails made for easy development. After the presentation, I knew enough to start coding!

Brian is an excellent speaker, and this presentation is long overdue. (I’d tried creating the video before, during and after the MySQL Users Conference back in April, and my application kept crashing. I guess it just needed a break, because I fired it up today and it seemed to save the movie OK. Please let me know if you watch the presentation and something seems wrong).

Enjoy!

Direct download link:
http://www.technocation.org/movies/mysql/2007_03_Rails_BostonUG.wmv

Ruby on Rails by Brian DeLacey

A sysadmin friend of mine was describing some DBA work he was doing, and wrote this:

I’m not much of a DBA, really – if it can’t be done through phpMyAdmin I’m not likely to be doing it.

This is in stark contrast to so many so-called DBAs who say “I’m a DBA….If it can’t be done through phpMyAdmin it must be Senior DBA work.”

I’ve used phpMyAdmin for MySQL administration, and there’s just something so nice about working on commandline.

Some have pointed out that the 12 Days of Scaleout campaign is a “cheap marketing tactic.”

Why, yes. It’s inexpensive as far as campaigns go. It’s definitely marketing. The grumbling seemed to be that there was no content on how the scaleout happened and worked for these companies.

We have to remember that not everyone is a geek. While we already know and love MySQL, there are people out there who only vaguely understand what a “database” is, much less have even heard of MySQL. Many laypeople I talk to haven’t heard of Oracle!

MySQL needs this kind of marketing. Perhaps it better belongs as an advertisement in a glossy magazine, but I see no problem with MySQL using what they own — lists, forums, PlanetMySQL, its own web page — to do cheap marketing. In fact, “cheap marketing” is one of the main reasons for having a website! The Log Buffers are a great way to give back to the community, but they’re also marketing for Pythian.

Heck, the people who post on Planet MySQL are marketing themselves — which may seem like a silly statement until you realize that there are a few folks who are independent consultants.

So, yes. I’m all for Planet MySQL being cheap marketing. I market my podcasts on the ‘planet, and I market my own skills. The next time I look for a job, I can point an employer to the fact that I’m at or near the top of the Planet MySQL top posters list.