Pythian Group

In October 2008, Baron posted How to Check MySQL Replication Integrity Continually. Here at Pythian we have developed a method based on that post, and added “verifying that masters and slaves are in sync” to our standard battery of tests.

We call it “Continual replication sync checking”. This article will explain how it works, how to test and make the procedure non-blocking, benchmarks from the real world, issues we encountered along the way, and finally Pythian’s procedure of setting up continual replication sync in a new environment.

At the 2010 MySQL User Conference & Expo, my co-worker Danil Zburivsky did a presentation about this, and you can get the slides in ODP (Open Office) format or watch the 46-minute video on YouTube.

How it works

On the master, mk-table-checksum is run. In order to make the checksum operation online, we use the modulo and offset features of mk-table-checksum to checksum only part of the data at a time. The checksum is run (from cron) on the master and replicates to the slave. The results are captured in a result table, and a separate process checks the result table and notifies us of any discrepancies.
Read the rest of this entry . . .

Here’s a sneak peek at a video matrix — this is all the videos that include Pythian Group employees at the MySQL conference. I hope to have all the rest of the videos processed and uploaded within 24 hours, with a matrix similar to the one below (but of course with many more sessions).

Last night at the Boston MySQL User Group I presented on how to get a consistent snapshot to build a slave, how to use mk-table-checksum to check for differences between masters and slaves on an ongoing basis, and how to use tools such as mk-table-sync and mysqldump to sync the data if there are any discrepancies.

The slides are online at http://technocation.org/files/doc/slave_sync.pdf.

The video can be watched on youtube at http://www.youtube.com/watch?v=Un0wqYKmbWY or directly in your browser with the embedded player below:

Read the rest of this entry . . .

Last week I had to confront one of those situations where you can’t really tell what is going on with a piece of software, and the final conclusion would sound completely crazy if postulated as the initial hypothesis. The regular MySQL commands and utilities fall short in these cases, so I had to resort to the three tools reviewed in this article.

The problem we were diagnosing was this: at some point in time, a number of queries that use to take less than one minute to execute, started to take between five to 15 minutes. We needed to get an insight into what was going on inside the MySQL server.

MySQL Tuner

At some point in a long diagnosis process, MySQL’s SHOW [GLOBAL] VARIABLES and SHOW [GLOBAL] STATUS are nothing more than a long list of numbers. Going through a team mate’s notes on another issue, I came across MySQL Tuner. This is an extremely simple tool that takes the information from the SHOW statements mentioned above and combines it in a useful way. Sample run:

Read the rest of this entry . . .

Over the weekend, I worked on a client’s two computers, trying to get a slave in sync with the master. It was during this time that I began thinking about:

a) how this never should have happened in the first place.

b) how “slave drift” could be kept from happening.

c) how this is probably keeping some businesses from using MySQL.

d) how MySQL DBAs must spend thousands of hours a year wasting time fixing replication issues.

I’ll be the first person to tell you that the replication under MySQL is pretty much dead-simple to set up. My only complaint is that it is annoying to type in the two-line “CHANGE MASTER” command to set up a new slave. Even so, it makes sense.

It is also very easy, however, for a slave to end up with different data than the master server has. This can be caused by replication bugs, hardware problems, or by using non-deterministic functions. Without proper permissions, a user/developer/DBA can log into the slave server and mess the data up that way. This last is a database administrator problem, but it affects replication. There are probably other issues that astute readers will point out.

I would like to point out one common issue that would probably be categorized as a replication bug. If the master crashes for whatever reason (say, a hosting company accidentally punches the power button on a master server) it will often cause corruption of the binary log. When the master comes back up, the slave cries about a non-existent binary log position.

Possible solutions: Read the rest of this entry . . .

Scenario: Someone wants to know which of the over 50 MySQL users have certain privileges.

There are many ways to solve this problem. Some of these scenarios are tedious and repetitious, others take no time at all.

The issue, of course, lies in what the “certain” privileges are. If it is “who has the SUPER privilege?” then a simple

SELECT user,host FROM mysql.user WHERE Super_priv='Y';

is sufficient. If it is “who has write access to the foo database”, you might write:

SELECT user,host FROM db WHERE Db='foo' AND Select_priv='Y';

but that only shows who explicitly has read permissions on that database; it does not include those who have global read permissions. The full query would be:
Read the rest of this entry . . .

The Maatkit toolkit is a real blessing for the MySQL DBA. And while its documentation is pretty good, in some cases it’s necessary to read carefully a second and third time to make sure you are not missing an important piece of information. In this article I will comment on mk-table-chksum and mk-table-sync. My comments are mostly aimed at those DBAs who are considering using these utilities with medium or larger-sized databases.

–replicate

This option allows you to store the checksum results on the master, in a table that will get replicated to the slaves. Although it might seem like overhead for a simple check, it really simplifies your life, especially when used in combination with mk-table-sync. I always use it, for the following reasons:

• You only need to run mk-table-chksum on the master.
• A simple query will tell you the slave status.
• When used with --chunksize, it divides the checking and synching into manageable portions.
• It’s the best way to get consistent checks between master and slaves.

I always define the table as an InnoDB table to improve concurrency and avoid deadlocks (see the tool documentation for details). This table can be created on any database. Just make sure that it is going to be replicated. Here’s an example of the table definition (straight from the documentation) with the InnoDB specification at the end:

Read the rest of this entry . . .

The “sla” in mysqlsla stands for “statement log analyzer”. This does a much better job than mysqldumpslow of analyzing your slow query log. In fact, you can sort by many different parameters — by sheer number of times the query shows up in the slow query log, by the total or average query time, by the lock time, etc. This is really good for weeding out pesky entries in the slow query log that you do not care about. In this case, our client was using log-queries-not-using-indexes, so there was a lot of junk in the slow query log as well (for instance, every time a mysqldump backup was run, the slow query log got plenty of entries). In this case, I’m using –slow to read the slow query log at the filename specified, –flat to flatten all the text to lowercase (basically case-insensitive matching) and –sort at to sort by “average time”.

> ./mysqlsla --flat --slow ~mysql/var/mysql-slow.log --sort at
Reading slow log '~mysql/var/mysql-slow.log'.
33274 total queries, 658 unique.
Sorting by 'at'.
__ 001 _______________________________________________________________________

Count         : 107 (0%)

Read the rest of this entry . . .

Today I’ve been checking out a new client environment. My mission is to figure out (cold) some of the characteristics of the queries being run, and particularly if they’re “good” or “bad”. In my arsenal of “tools I really want to check out” has been Maatkit‘s Query Profiler.

They’re very different tools. Maatkit’s query profiler profiles a batch of queries, without granularity (at least not the way I ran it) to see what query is doing what. So I ran this against a production machine:
(I ran the query profiler for a while and then hit “enter” (apparently after about 17 minutes))

> ./mk-query-profiler --external --host localhost --user <user> --password <password>
 Press <enter> when the external program is finished
 +----------------------------------------------------------+
 |                     1 (1028.2091 sec)                    |
 +----------------------------------------------------------+

__ Overall stats _______________________ Value _____________
 Total elapsed time                     1028.209
 Questions                               882

Read the rest of this entry . . .