A typical scenario is when a master has a few dozen slaves, and instead of dealing with all of them directly, uses four relay slaves, each one dealing with 6 slaves.
So, where's the trick? The trouble comes if you change replication format after you start the slave.
Example. One master (M), two relay slaves (R1, R2), with four slaves each …

Robert Treat and I had some back and forth emails a few weeks ago about explaining database replication to customers. Replication is totally cool but it is also completely confusing to a lot of people. The basic concepts are not widely understood. Plus PostgreSQL does not help by giving you a wide range of methods, often with poorly documented trade-offs.

Based on our conversation I put together a talk for PG West in Portland called Getting Smart about the New World of PostgreSQL Replication. It explains basic concepts and surveys five replication approaches. Press the title and you can possess the slides yourself.

Robert and I had talked about putting …

If you use MySQL Replication, 99% of the times your main concern is to keep the master and the slave fully consistent and in sync. Some applications require exactly the opposite.

Let's take, for example, some reporting solutions. You may want to run reports against a data mart (ok, I can see some BI experts here jumping off their chairs - please forgive the simplification for the sake of this example), that is a subset of the whole historical data within your data warehouse. So, you may have a master server containing only the last 3 months of your sales and a slave containing three years of sales. Sales managers can happily to see current year/previous year rolling comparisons using the slave server and at the same time they can run faster current month/previous month reports on their master.

There are many ways to prepare the data mart and the historical DB. The easiest thing to do is to remove the oldest rows from the …

Updated the release, sure it’s only been a matter of hours but I added the standard cnf files for 2,4,8 and 16GB server installations. Also added the rhcluster-wrapper script that can be used to run just about anything on the active database node when using mysql in an active/passive setup.

Download here:  http://code.google.com/p/monolith-toolkit/

Today I decided to package all of my various scripts together into a useful toolkit. Some are perl, some are shell script. These scripts are, in general, one off scripts that I wrote in order to get things done that weren’t available at the time. Now they’re nicely organized and will receive updates at the google code repo.

So far the toolkit includes the following scripts:

• mt-backup-parallel -> the parallel backup script I wrote about in my last post
• mt-check-replication -> script to report on replication status for slave servers
• mt-check-rhcluster-filesystems -> reports on redhat cluster filesystems (for mysql active/passive clustering)
• mt-connections-log -> logs connections to mysql to disk, reports on threshold overages
• mt-flush-tables-sequence -> runs through schema.tables to …

The rationale for this change is that MIXED mode as default may break existing applications, where statements like the following are used:

UPDATE t1 SET a=1 WHERE @@server_id = 1; …

Recently I had an interesting issue crop up. Due to an unfortunate migration incident in which involved master/master replication and not checking to see if replication was caught up, we ended up with an infinite replication loop of a number of SQL statements. awk helped immensely in the aftermath cleanup.

The basics of the replication infinite loop were (more…)

This is a post about SYSDATE() and NOW() and CURRENT_TIMESTAMP() functions in MySQL.

Firstly, note is that of these three, only CURRENT_TIMESTAMP() is part of the SQL Standard. NOW() happens to be an alias for CURRENT_TIMESTAMP() in MySQL.

Secondly, note that replication does not work well with non-deterministic functions. And “hey, what time is it?” is non-deterministic. Ask it twice, with a second apart between asking, and both times you get different results (with at least second precision).

You can start to see the problem here….but there’s more…. (more…)

It’s been a while since my last post! I have a few half-written entries, but I figured I just get one out there so I don’t appear to have dropped off the face of the planet

Being more of a DBA than a sysadmin myself, I’ve noticed that there is a surprising dearth of information regarding how to actually get nagios set up to monitor MySQL, especially if you’re not experienced with nagios.

In my own experience, MySQL monitoring often ends up being a homegrown thing, especially if you’re a small shop. It is definitely a good idea to consolidate all of your monitoring, DB-related or not, into one solution: roll-your-own monitoring solutions can be brittle and burn you in the end. One example that comes to mind, a few years back i had a quick perl-based script to monitor replication and set it to mail a few people based on some threshold. Took me 1 hour to write and worked well enough, except, six months later, when some …

The trouble with slave lag is that you often can't see it coming. Especially if the slave's load is pretty uniform, a slave that's at 90% of its capacity to keep up with the master can be indistinguishable from one that's at 5% of its capacity.

So how can you tell when your slave is nearing its capacity to keep up with the master? Here are three ways:

One: watch for spikes of lag. If you have Cacti (and these Cacti templates for MySQL) you can see this in the graphs. If the graphs start to get a little bumpy, you can assume that the iceberg is floating higher and higher in the water, so to speak. (Hopefully that's not too strange a metaphor.) As the slave's routine work gets closer and closer to its capacity, you'll see these spikes get bigger and "wider". The front-side of the spike will always be less than a 45-degree angle in ordinary operation[1] but …