zorkian: Icon full of binary ones and zeros in no pattern. (Default)
I spent half of today working on rewriting my Perl show-top-queries script in Go and renamed it mysql-sniffer. It works now, so I'm happy to link it, in case anybody really wants to be a bleeding edge user. There are a few kinks to work out and options to add.


In short, the program uses pcap to sniff packets destined to MySQL. It extracts the queries from those packets and then does some canonicalization, producing output that should help you track down your most popular queries on the fly. Given that it uses pcap, you can turn it on and off with little impact to your running database.

On the big database at StumbleUpon, it parses 3000 queries per second and takes up 10% of one core on the machine. I can't show that output, but instead, I've got some data from Dreamwidth's master database.

Sample output. )

I'm not hosting compiled versions right now. If you want to play with it, you'll have to have a working Go setup (it's easy) and then check out the code. Yes, this means you'll need both Mercurial and Git on your machine. Sorry.
zorkian: Icon full of binary ones and zeros in no pattern. (Default)
Something I've now run into twice in my time at StumbleUpon, but doesn't seem to be noted anywhere online that I can find:

Short story: Removing the MySQL relay logs (and only the relay logs) is unsafe and will likely render your slave useless (missing data). This is true as of MySQL 5.0.

Now, let's talk about the longer version of the story...

A previous DBA who used to work here had written a script that took weekly snapshots of some databases for the purpose of creating development databases. The script, in pseudocode, did something like this:

stop mysqld instances
remove old /var/lib/mysql-dev
remove all relay logs from /var/lib/mysql
create lvm snapshot of /var/lib/mysql to /var/lib/mysql-dev
start up mysqld instances

The logic behind step 3 is: if we remove potentially many gigabytes of data before we snapshot, we save a bit of space. We aren't going to need those files on the development server later, and the main database can just re-download them from the master.

This reasoning is sound, but the reality is faulty and insidious.

The problem is that MySQL maintains its downloaded transaction state (aka, the status of the relay logs) in the master.info file, not in the relay-log.info file. If you remove *relay*, you get rid of all of the transactions MySQL thinks it has, but you don't actually tell MySQL that they're gone. If your slave was behind at all, the database now starts skipping ahead until it finds a valid transaction that it can start applying... you therefore lose all of the changes that were contained in un-executed portions of the relay log files that you removed.

If you want to prove this to yourself, you can temporarily issue a SLAVE STOP SQL_THREAD and then cat master.info several times. Watch the third line value increase -- that's the Read_Master_Log_Pos value from SHOW SLAVE STATUS. It continues to increment because MySQL is still writing data to the relay logs. If you then remove the relay logs, MySQL doesn't go back and re-download them. Ouch.

The only safe way to clone a slave is to either remove everything (bin logs, relay logs, all info files) and then re-slave it with CHANGE MASTER TO, or to copy over everything to the new location (and change the server id, of course).

This post brought to you by the "ah shit, really?" department.


zorkian: Icon full of binary ones and zeros in no pattern. (Default)
Mark Smith

April 2017

91011121314 15


RSS Atom

Most Popular Tags

Active Entries

Style Credit

Expand Cut Tags

No cut tags
Page generated Apr. 23rd, 2019 04:03 pm
Powered by Dreamwidth Studios