replication on SWiK

MySQL Sandbox in Launchpad

Fri, 10 Oct 2008 12:04:27 -0700

Quick painless install of side MySQL server in isolation. MySQL Sandbox is a tool for installing one or more MySQL servers in isolation, without affecting other servers

MySQL: RENAME TABLE on Transactional Tables Can Jeopardize Slave Data

Thu, 09 Oct 2008 18:13:27 -0700

Do you have a master-slave MySQL set up? Ever do DDL changes on the master? You may be hit with a serious data integrity bug. Read on.

One of our clients does a regular rename tables on the master to keep the current table small and archive off old data. We’d occasionally be hit by a ‘duplicate key’ error on the slave on the current table and have to resolve it manually. Digging into the issue, I managed to replicate it on demand and filed bug 39675 with MySQL, which subsequently has been verified and slated for fix, though from what it seems only in version 6.0. The bug affects all versions of MySQL from 4.1 to 6.0.

In a nutshell, here is what happens. The rename tables command only checks for pending transactions or locks in the current session. If there is a pending transaction in another session on the table being renamed, the rename will succeed, but the order in which the transaction is written to the binlog will be different from the order in which the transactions were applied on the master. This means that the data on the slave will now be out of sync for this table.

Here’s a test-case:

(more…)

Three ways to know when a MySQL slave is about to start lagging

Thu, 09 Oct 2008 18:13:27 -0700

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 the back-side, when the slave is catching up after lagging behind, will become a gentler and gentler slope.

Two: deliberately make a slave fall behind, then see how fast it can catch up. This is sort of related to Method One. The goal here is to explicitly see how steep the backside of that slope is. If you stop a slave for an hour, then start it again and it catches up in one hour, it is running at 1/2 of its capacity. (In case that's confusing: if you stop it at noon and restart it at 1:00, and it's caught up again at 2:00, it played all statements from 12:00 to 2:00 in 1 hour, so it went at 2x speed.)

Three: measure it more scientifically. Use our patched server, which gives you a USER_STATISTICS table.

PLAIN TEXT

SQL:

mysql> SELECT * FROM INFORMATION_SCHEMA.USER_STATISTICS WHERE USER='#mysql_system#'\G
*************************** 1. row ***************************
USER: #mysql_system#
TOTAL_CONNECTIONS: 1
CONCURRENT_CONNECTIONS: 2
CONNECTED_TIME: 46188
BUSY_TIME: 719
ROWS_FETCHED: 0
ROWS_UPDATED: 1882292
SELECT_COMMANDS: 0
UPDATE_COMMANDS: 580431
OTHER_COMMANDS: 338857
COMMIT_TRANSACTIONS: 1016571
ROLLBACK_TRANSACTIONS: 0

You can compare the BUSY_TIME to one-half the CONNECTED_TIME (because there are two replication threads on the slave) to see how much of the time the slave thread was actively processing statements. If the slave threads are always running, you can just use the server's uptime instead.

[1] There are cases where this isn't true, especially if you're monitoring Seconds_behind_master instead of using mk-heartbeat, which is immune to this anomaly.

Entry posted by Baron Schwartz | 8 comments

Add to: | | | |

Setting up MySQL monitoring with Nagios

Thu, 09 Oct 2008 18:13:26 -0700

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 package upgrades were done on the machine I had the monitoring script running on, the mailer stopped working, and we found out the hard way about an alert situation with our replication.

Nagios

Nagios is something your systems/operations people are probably familiar with. In case you are the systems/ops person, it’s a 15-20 minute process to get nagios installed on a typical ubuntu machine. Make sure to set up nagios with the embedded perl interpreter in case you decide to make use of this feature in the future. Once you have it set up and monitoring, here’s how you can set up nagios to monitor your mysql databases:

You can grab some perl-based nagios plugins to monitor mysql here:

http://www.shinguz.ch/MySQL/nagios-mysql-plugins-0.3.tar.gz

Untar the plugins into /usr/local/nagios/libexec

If you want to check out how a particular plugin is executed, run it from the command line as follows:

vmware@vmware-desktop:~/dl/nagios-mysql-plugins-0.3$ perl check_repl_mysql_seconds_behind_master.pl --help

SYNOPSIS

check_repl_mysql_seconds_behind_master.pl flags

DESCRIPTION

Nagios plugin to check how many seconds slave is behind master...

FLAGS

help, ?      Print this help.
host, h      Host where database is located (default localhost)
password, p  Password of user root (default ><)
port         Port where database listens to (default 3306)
user, u      User for connecting to the database (default root)
critical|c   Seconds for critical level (default 600)
warning|w    Seconds for warning level (default 60)

PARAMETERS

none

As an example, let’s say we want to monitor the lag of a particular replicator. Nagios operates on thresholds, the first stage being a warning, the second being a critical alert.

Adding a Nagios Command

In your /usr/local/nagios/etc/objects/commands.cfg file, add the following “command” entry — you should see others like it. This specifies a type of command you will run on a given host:

# 'check_repl_mysql_seconds_behind_master' command definition
define command{
command_name    check_repl_mysql_seconds_behind_master
command_line    $USER1$/check_repl_mysql_seconds_behind_master.pl --host=$HOSTADDRESS$ --password=$ARG1
}

In this case we’re just using most of the default parameters, but if we wanted to specify a different critical or warning threshold, we could add it with –critical=$ARG2, etc.

Then, if you’re monitoring localhost (probably not in most cases) you would just add the following entry to /usr/local/nagios/etc/localhost.cfg:

define service{
use                             local-service         ; Name of service template to use
host_name                       localhost
service_description             MySQL Replication
check_command                   check_repl_mysql_seconds_behind_master!"<password>"
notifications_enabled           0
}

If you want to add another host to be monitored from this nagios instance, you can specify a new configration file, say, db01.cfg and write that to your etc/objects directory. Then add an entry to /usr/local/nagios/etc/nagios.cfg as follows, after making the appropriate adjustments to the hostname in the service:

cfg_file=/usr/local/nagios/etc/objects/db01.cfg

If everything has been configured correctly, you should be able to restart nagios with /etc/init.d/nagios restart, and then go into your nagios console and see the configuration entries for your new command and service, and also see if your slaves are far behind

Once you have one service added in this way, adding new ones becomes a lot easier. I hope to investigate some more of the performance monitoring features of the nagios mysql plugins, but i’ll leave that for a future blog post.

Does anybody really know what time it is?

Thu, 09 Oct 2008 18:13:26 -0700

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…)

Three ways to know when a MySQL slave is about to start lagging

Thu, 09 Oct 2008 18:11:53 -0700

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

Three: measure it more scientifically. Use our patched server, which gives you a USER_STATISTICS table.

PLAIN TEXT

SQL:

mysql> SELECT * FROM INFORMATION_SCHEMA.USER_STATISTICS WHERE USER='#mysql_system#'\G
*************************** 1. row ***************************
USER: #mysql_system#
TOTAL_CONNECTIONS: 1
CONCURRENT_CONNECTIONS: 2
CONNECTED_TIME: 46188
BUSY_TIME: 719
ROWS_FETCHED: 0
ROWS_UPDATED: 1882292
SELECT_COMMANDS: 0
UPDATE_COMMANDS: 580431
OTHER_COMMANDS: 338857
COMMIT_TRANSACTIONS: 1016571
ROLLBACK_TRANSACTIONS: 0

[1] There are cases where this isn't true, especially if you're monitoring Seconds_behind_master instead of using mk-heartbeat, which is immune to this anomaly.

Entry posted by Baron Schwartz | 8 comments

Add to: | | | |

PostgreSQL warm standby on ZFS crack - Esoteric Curio

Thu, 09 Oct 2008 17:59:08 -0700

Using PITR for PG replication

SILVERCODERS - Quality and Competence - SqlSync

Tue, 07 Oct 2008 09:48:12 -0700

The Proxy belt - Designing fail safe systems webinar

Tue, 07 Oct 2008 06:57:42 -0700

There is a free webinar today, October 7, at 10am PDT (1pm EDT, 7pm CEST, 5pm UTC), about Designing Fail-Safe Systems with MySQL Proxy.

It's an interesting session to attend, for several reasons.

Building a fail safe systems is the dream (and the nightmare) of every system architect. There are many solutions, not all of them are viable.
This solution was implemented by a user, Clint Alexander, Internet Technologist for the Army Game Project.

The interesting part is that this session is not a theoretical disquisition, but a detailed technical and practical description of how to achieve fail safe in a heavy duty web project.

In addition to the presenter, there will be several MySQL engineers as panelists, so you will be able to ask questions and get answers from the top experts.

And what is the Proxy belt? That's a teaser for you. It's an interesting technological concept that has been probably been done already, but this is the first time that someone has actually tried it out in practice. The name is a cute description of what has been implemented. You will get the real info and a catchy picture in the presentation.

Preparing for Open SQL Camp

Mon, 06 Oct 2008 22:01:42 -0700

The Open SQL Camp will take place in Charlottesville, VA, USA, on November 14, 15, and 16.
Attendees are requested to register in the event's Wiki, and if you are interested in presenting something, there is a mailing list to discuss your intended topics.

I have proposed a topic about the MySQL community driven replication monitoring project, a hackish solution to the old problem of replication monitoring, implemented entirely on the server side, without using system applications.

Remote replication using ZFS : Interconnectedness of all things

Mon, 06 Oct 2008 17:48:28 -0700

Home - Continuent

Sat, 04 Oct 2008 19:21:08 -0700

How to check MySQL replication integrity continually

Sat, 04 Oct 2008 12:08:17 -0700

I have recently added some features to Maatkit’s mk-table-checksum tool that can make it easy to checksum the relevant parts of your data more frequently (i.e. continually, but not continuously). This in turn makes it possible for you to find out much sooner if a slave becomes different from its master, and then you [...]

MySQL Replication Failures

Thu, 02 Oct 2008 09:28:23 -0700

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: (more…)

MySQL :: MySQL 5.0 Reference Manual :: 14 High Availability and Scalability

Wed, 01 Oct 2008 07:15:27 -0700

DRBD:What is DRBD

Tue, 30 Sep 2008 04:13:56 -0700

HOWTO Heartbeat and DRBD - Gentoo Linux Wiki

Mon, 29 Sep 2008 03:14:16 -0700

Will you use row-based replication by default?

Sun, 28 Sep 2008 16:14:21 -0700

MySQL 5.1 introduces row based replication, a way of replicating data that fixes many inconsistencies of the statement based replication, the standard method used by MySQL so far.

The good: row based replication solves some problems when replicating the result of non deterministic functions, such as UUID() or NOW().
The bad: row-based replication may break existing applications, where you count on the quirks of statement based replication to execute conditionally (updates base on @@server_id, for example), and may perform badly on updates applied to very large tables.

Currently, MySQL 5.1.28 has the mixed mode enabled by default. When in this mode, binary logging switches to row-based when there is a risk of breaking replication. Except that there are some cases left out, like Bug#39701 (load_file()).
Mixed mode is supposed to be the best of two worlds. You can run fast updates on large tables and not risking inconsistent results on non-deterministic functions. The recent addition to mysqlbinlog allows an easy decoding of the ugly looking row-based statements.
Would you switch to mixed mode or stay in statement mode? There are requests of reverting the default to statement based binlog format, to maintain compatibility with old applications.
Personally, I would go for the mixed mode. But it depends on your applications and your personal experience. What's your take?

ChironFS - A filesystem must go on, even if it is hurt

Sun, 28 Sep 2008 15:15:08 -0700

Zumastor Linux Storage Project

Sun, 28 Sep 2008 10:15:32 -0700

Virtual Machine Replication & Failover with VMWare Server & Debian Etch (4.0) | HowtoForge - Linux Howtos and Tutorials

Thu, 25 Sep 2008 07:16:12 -0700

DRBD:What is DRBD

Wed, 24 Sep 2008 14:14:25 -0700

AVS and ZFS, seamless? : The "Availability Suite"

Tue, 23 Sep 2008 11:11:29 -0700

Fighting MySQL Replication Lag

Tue, 23 Sep 2008 08:27:30 -0700

The problem of MySQL Replication unable to catch up is quite common in MySQL world and in fact I already wrote about it. There are many aspects of managing mysql replication lag such as using proper hardware and configuring it properly. In this post I will just look at couple of query design mistakes which result in low hanging fruit troubleshooting MySQL Replication Lag

First fact you absolutely need to remember is MySQL Replication is single threaded, which means if you have any long running write query it clogs replication stream and small and fast updates which go after it in MySQL binary log can't proceed. It is either more than than just about queries - if you're using explicit transactions all updates from the transactions are buffered together and when dumped to binary log as one big chunk which can't be interleaved by any other query execution. So if you have transaction containing millions of simple updates instead of one large update to help MySQL replication lag it is not going to work.

This brings us to rule number one - if you care about replication latency you must not have any long running updates. Queries or transactions containing multiple update queries which add up to long time. I would keep the maximum query length at about 1/5th of the maximum replication lag you're ready to tolerate. So if you want your replica to be no more than 1 minute behind keep the longest update query to 10 sec or so. This is of course rule of thumb depending on differences in master/slave configuration, their load and concurrency you may need to keep the ratio higher or allow a bit longer queries.

What should you do if you need to update a lot of rows ? Use Query Chopping - this can be running update/delete with LIMIT in the loop, controlling maximum amount of values per batch in multiple row insert statement or Fetching data you're planning to update/delete and having multiple queries to delete it (see example below)

This brings us to yet another rule for smart replication - do not make Slave to do more work than it needs to do. It is crippled by having to do all of this in single thread already - do not make it even harder. If there is considerable effort needed to select rows for modification - spread it out and have separate select and update queries. In such case slave will only need to run UPDATE
Example:

PLAIN TEXT

SQL:

UPDATE posts SET spam=1 WHERE body LIKE "%cheap rolex%";

This query will perform full table scan in MySQL 5.0 (even if there are no spam posts) which will load slave significantly. You can replace it with:

PLAIN TEXT

SQL:

SELECT id FROM posts WHERE body LIKE "%cheap rolex%";
UPDATE posts SET spam=1 WHERE id IN (list of ids)

If there could be many ids matched on the first place you should also use query chopping and run update in chunks if application allows it.

In MySQL 5.1 with row level replication you will not have selection process running on SLAVE but it will not do the chopping for you.

In general this trick does not only work well for full table scan updates but in general for cases when there are much more rows examined than modified.

The next common mistake is using INSERT ... SELECT - which is in similar to what I just described but can be much worse as SELECT may end up being extremely complicated query. It is best to avoid INSERT ... SELECT going through replication in 5.0 for many reasons (locking, long query time, waste of execution on slave). Piping data through application is the best solution in many cases and is quite easy - it is trivial to write the function which will take SELECT query and the table to which store its result set and use in your application in all cases when you need this functionality.

Finally you should not overload your replication - Quite typically I see replication lagging when batch jobs are running. These can load master significantly during their run time and make it impossible for slave to run the same load through single thread. The solution in many cases is to simply space it out and slow down your batch job (such as adding sleep calls) to ensure there is enough breathing room for replication thread.

You can also have controlled execution of batch job - this is when they will check slave lag every so often and pause if it becomes too large. This is a bit more complicated approach but it saves you from running around and adjusting your sleep behavior to keep the progress fast enough and at the same time keep replication from lagging.

In many bad replication lags I've seen simply following these simple rules would avoid a lot of problems and often save massive hardware purchases or development efforts based on assumption MySQL replication can't possibly keep up any more.

Entry posted by peter | 4 comments

Add to: | | | |

Fighting MySQL Replication Lag

Mon, 22 Sep 2008 22:24:33 -0700

PLAIN TEXT

SQL:

UPDATE posts SET spam=1 WHERE body LIKE "%cheap rolex%";

This query will perform full table scan in MySQL 5.0 (even if there are no spam posts) which will load slave significantly. You can replace it with:

PLAIN TEXT

SQL:

SELECT id FROM posts WHERE body LIKE "%cheap rolex%";
UPDATE posts SET spam=1 WHERE id IN (list of ids)

If there could be many ids matched on the first place you should also use query chopping and run update in chunks if application allows it.

In MySQL 5.1 with row level replication you will not have selection process running on SLAVE but it will not do the chopping for you.

In general this trick does not only work well for full table scan updates but in general for cases when there are much more rows examined than modified.

Entry posted by peter | 4 comments

Add to: | | | |

Tungsten Replicator

Sun, 21 Sep 2008 03:59:25 -0700

Tungsten Replicator 1.0 Alpha Is Released

Fri, 19 Sep 2008 15:07:35 -0700

The 1.0 Alpha of Tungsten Replicator is out. Actually it's been out since Tuesday but it's been a busy week. Binary downloads are available here.

The Alpha release offers basic statement replication for MySQL 5.0 on Linux, Solaris, MacOSX, and Windows platforms. The setup is very simple, and there are procedures for master failover as well as performing consistency checks. If you work at it, you'll find bugs. That's a promise, not a threat. Please log them in the project JIRA. We gladly accept feature requests, too.

Meanwhile, the beta version is in development. Among other nice features we will offer support for user-written SQL event extractors and appliers, MySQL row replication support, lots of testing, and much more.

DRBD - Wikipedia, the free encyclopedia

Thu, 18 Sep 2008 03:01:33 -0700

Tungsten Replicator

Wed, 17 Sep 2008 04:04:07 -0700

Bringing Open Source Replication to the Oracle World

Mon, 15 Sep 2008 19:03:35 -0700

Replication is one of the most useful but also also one of the most arcane database technologies. Every real database has it in some form. Despite ubiquity, replication is complex to use and in the case of commercial databases quite expensive to boot.

We aim to change that. On Tuesday we will be announcing replication support for Oracle. Oracle replication will be based on our open source Tungsten Replicator, which is currently available in an alpha version for MySQL. Our goal is to provide replication that is accessible and usable by a wide range of users, especially those running lower-cost Oracle editions.

It's not a coincidence that we chose to implement MySQL and Oracle replication at the same time. MySQL has revolutionized the simplicity and accessibility of databases in general and replication in particular. For example, MySQL users have created cost-effective read scaling solutions using master/slave replication for years. MySQL replication is not free of problems, but there is no question MySQL AB helped by the community got a lot of the basics really right.

On the other hand, Oracle replication products offer state-of-the-art solutions for availability, heterogeneous replication, application upgrade, and other problems, albeit for high-end users. For example, Oracle Streams and Golden Gate TDM offer very advanced solutions to the problem of data migration with minimal downtime. The big problem with these solutions is not capabilities but administrative complexity and cost.

Our initial cut at merging the two worlds is focused on creating a simple and usable database replication product that handles the following use cases for small to medium Oracle installations:

Basic data availability using extra copies of databases locally and off-site
Scaling reads using the MySQL read-scaling model
Performing zero-downtime upgrades and migrations using database replicas
Heterogeneous data migration between Oracle and MySQL as well as PostgreSQL (initially one-way only).

The big technical feature is that replication will work on all editions of Oracle, not just Enterprise Edition. We expect to help Oracle users build economical new systems on the scale-out model as well as off-load existing Oracle servers to avoid having to upgrade to more expensive licensing.

An early adopter version will be available toward the end of the month. The Oracle redo log extractor is commercial but all other capabilities are open source, so you can replicate from MySQL up to Oracle freely. We are now looking for some select users who can really help propel the software forward. If you would like to try out Oracle replication, contact me at Continuent.