MySQL binlogs - Don't forget to do your homework!

Now that I'm back doing just database stuff, I've come to realize I've gotten a little sloppy about doing my homework.  Homework's never been my favorite thing in the world, but it often reduces stress when your under the gun during an outage or upgrade...

We had a MySQL database server that's been slow on DML changes, and based on the slowest statements being 'COMMIT', we had a good mind it was an issue with the binary logs, or the disk subsystem that MySQL was writing the binary logs to.

Investigation with iostat (iostat -xtc 10), we were able to narrow it down to a disk subsystem being fully utilized and queuing.  

Easy fix right?  Just change the location of the binlogs via the startup option in my.cnf and restart.

If I had done my homework, I would have known that restarting MySQL with a new location for binlogs would reset the binary log to mysql-bin.000001, and thus break replication to the slave... Oops.

No need to panic - just check the master's log position and run the following on the slave:

 mysql> change master to master_log_file=mysql-bin.000001, master_log_pos=123

Perfect - the slave caught up and was good to go - except that I neglected to recall the fact we had enabled the application and started writing to the master before I checked the log position....  Oops. (2)

Luckily, replication was up to date when we restarted, so I was able to use the handy dandy mysqldump utility to load all the transactions from the new binary log up to the point in which I checked the master:

mysqlbinlog --stop-position=123 mysql-bin.000001 | mysql   

Moral of the story: Doing your homework is usually a good thing! 

solving frequent replication distribution latency errors

We have many millions of commands a day passing through a 2-node peer-to-peer replication topology. At some point, replication lost its ability to keep up with distribution. After much research, we replicated the issue to Microsoft and were told that our distribution database was too big. They recommended that we set immediate_sync on the publication to false.

To me, it made no sense why this would help. We had replication distribution history latency set to 48 hours - wouldn't transactions be retained for that amount of time regardless of whether they'd been replicated or not?

Luckily, I found this MSDN blog post, which does a great job of explaining the interaction between min transaction history retention, max transaction history retention, and immediate_sync. Our plan now is to set min retention to 8 hours (we need a buffer that will allow us to initialize from backup), max retention to 36 hours, and immediate_sync to false. This will keep all transactions for 8 hours, but replicated transactions after that buffer will be deleted as soon as they're replicated to existing subscribers. Transactions will only be retained for 36 hours if replication gets seriously backed up again.

Lost and Found ?

Sometimes you just have to laugh at the crazy things that can kill a good evening.

I had this brilliant idea to change our replication setup on one of our Master-Master replication server setups this week. I got sick of having to restart MySQL every time we wanted to add a new database and have it included in the list of replicated databases - we were using replicate-do-db in our configs.

So it seems very straight forward to change to ignore-db or ignore-table (because of cross database updates).

After a few weeks in QA and a few weeks in staging - no problems, no issues, no complaints... let's go for it!


Yea, as soon as we deploy and restart MySQL to pickup the configs, replication fails and stops!

And of course to make it lots of fun, replication on a couple other servers failing at the same time for unrelated reasons, and then a migration of another application that night having issues...)

So look around a while, check the MySQL Monitor ... and, wala - lost+found - table does not exist errors!

Yea, ext3 rears it's ugly head again. I'm sure it's probably my responsibility to make sure that lost+found directories are clean up, etc, etc, but it sure made for a headache this week.

The fix (knock on wood) was straight forward - we just added ignore-table=lost%.%

Seemed to do the trick. Maybe we should check out ReiserFS or xfs or zfs.

Either way, if there's a way to break replication, I'm sure I'll find it... ;)


On that note - if you love the file system (linux based) that you are using for your MySQL servers, I'd love to hear your comments (good, bad, or ugly)

MySQL Replication and bad assumptions

Sometimes I amaze myself in my capacity to make assumptions about how things should work, especially when it comes to test plans... ( You know what happens when we assume, right? )

I had this great idea to setup a couple slaves off a master-master replication set something like this:

MASTER A  <-------------->  MASTER B
   |                          |
   |                          |
   |                          |
   V                          V
 SLAVE C                    SLAVE D

Makes sense, huh?

Then I'll just point our read / write app servers at MASTER A, and point half of our read / only app servers at SLAVE C, and half of our read only servers at SLAVE D. Sweet. Works like a charm.

Oops... Replication shows current everywhere, no alerts going off. But app servers pointed at SLAVE C show higher row counts than app servers on SLAVE D...

Yea, I never bothered to test that. That's what I get for making assumptions - if you could even call it that. When I thought things through, I quickly realized that there was no reason for MASTER B to send any of it's replicated updates on to SLAVE D, because I had told it not to write any of it's updates it received via replication to it's binary log... :)

All you replication experts out there could have probably told me that in your sleep, but of course, I never bothered to ask...

Just a couple lines in the config file could have saved me a few headaches, and a couple embarrassing apologies...

log-slave-updates
replicate-same-server-id = 0

Figures.. ;)

MySQL: replicate-* rules should be dynamically configurable

I wonder what the best way is to get a feature request more visibility (convert a feature request to an actual work item).

We use replicate-do-db on all our slave servers , so after many, many, restarts of our slave servers, I checked the bug list for any feature requests surrounding this, and about 6 months ago, one was opened:

replicate-* rules should be dynamically configurable

Sadly, there does not appear to be a work log for it, nor any targeted version. It seems to me that it's probably a fairly common use case...

I suppose adding or removing a db from the replicate list, might require restarting the slave processes that scan binlogs for transactions to apply, or at least, some way to notify the slave processes that additional databases need to be accounted for. Then we have to make sure that we start at the right point in the binlog, right? (what if the database as been active for a while, and then I try to dynamically add it to the replicate list)

There is a worklog item for having the master filter the database replication (to reduce network traffic), which has a proposed option of allowing this to be dynamic through the use of a CHANGE MASTER command. That might work for the dynamic replicate-* rules as well...

Maybe this is an opportunity to open my MySQL Internals book, and see if I can pull those C programming skills out of my cobweb filled dba brain...

Anyway - it would be interesting to know who all out there would like this as a feature...

Best Practices - Oops...

Yea, yea, yea... best practices.

I spent about a week troubleshooting issues on a MySQL 5.1 replication issue where certain transactions causing duplicate key on index errors stopped replication. Easy enough to fix, right?

 mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
 mysql> start slave;

Hmm... nice until it happens almost every minute...

So, after reading every link I could find on the internet, forum posts, upgrading to 5.1.24, I finally started to think I just didn't know what I was doing with replication.

Last ditch effort... ask dev and application engineers to help out...

"Oh... you mean one of the application servers is pointed at the slave server in read/write mode and is trying to insert data there as well?..."

:)

Yea, that's why it's a best practice to have your slave servers set READ ONLY .... duh...

Anyway, live and learn..

SQLServer: Image Replication - Oops

Yea, it's always something, huh?

Better mind your default configuration options with SQLServer 2005 if you plan on transactional replication with LOBs. It's nice that they only replicate the appropriate changes, but you'll get an error if you are using images with sizes > the default replication size allowed.

See the Microsoft article for more info.

Fun... :)

SQLServer: Transactional Replication Issues

After seeing a few friends at the SQLPass 2007 Summit in Colorado and discussing some of the finer 'features' of pier to pier transactional replication and large database systems (my friends work on the replication team at Microsoft), I quickly ran into one of my big grips with P2P replication and SQLServer 2005.

I had noted that being able to set up P2P from a 3rd party backup tool is essential with large databases (> 100GB) because creating a backup with SQLServer 2005 just takes way too long. The problem is, however, that SQLServer must know which LSN the backup was on when it was restored to the replicated server, so that any transactions that have occurred on the main database since the backup was taken can be correctly applied in the correct order.

The only way for SQLServer to get that is by reading the header in the backup file, which can't be done via a third party backup tool (SQLLightspeed, SQLBackup, etc, etc). My friends implied that if I knew the LSN, it could be done. I'm not quite there yet.

However, it wouldn't be that hard for Microsoft to allow a third party backup tool to call a procedure letting replication know what the current LSN is, thus fixing the problem. Hmm...

Maybe one day. For now, we'll have to deal with 5 hour backups and 2 hours restores in order to setup P2P transactional replication from backup, at least until SQLServer 2008 come around and do the backups for us with it's supposedly blazing fast compression, etc.

I'm not holding my breath....