MySQL Performance Blog

As you probably know MySQL Replication (statement based) works by fetching statements from MASTERs binary log and executing them on the SLAVE. Since MySQL 4.0 this process is a bit more involved having events passing via relay logs on the Slave which also means there are two replication threads “IO Thread” and “SQL Thread” used in the process but idea remains the same.

For replication to work properly you need to have proper “snapshot” - consistent state of the database as it was on master and corresponding position in masters binary logs. There are multiple ways to get the “snapshot” - shutting down MySQL Server and copying data, using LVM, Using Innodb Hot Backup Tool, Using another Slave, using backup image etc but in all the cases you have to be sure the snapshot you’re dealing with corresponds to correct binary log position.
[read more...]

Sometimes MySQL Replication may run out of sync - because of its own buts or operational limitations or because of application mistake, such as writing to the slave when you should be only writing to the master. In any case you need slave to be synced with Master.

To discover the difference between Master and Slave you can use excellent Maatkit tool though if you you just need to resync small single table it may be easy to do it other way:
[read more...]

I already wrote about some MySQL Error Messages which are confusing, here is one more:

After setting up new slave Server I'm getting error log file flooded with messages like this and there is no hint in the message what would explain what is wrong.

In fact the issue in this case is (because of configuration error) two slave servers got the same server-id.

Seriously in this case Master clearly sees the problem in this case as there are 2 servers with same server-id connected and replicating so it should report it to the slave instead of sending end packet.

At very least it would be nice to include possible reason for this error message which MySQL already does in many other cases.

I've now filed it as a bug.

A while ago I wrote about how to make MySQL replication reliable, part of which is to eliminate temporary tables. The idea is this: if a slave is stopped (or crashed) while a temporary table is open and is then restarted, the temporary table doesn't exist anymore, and the slave will have problems trying to replay any further statements that refer to these tables. Thus, I claimed, there's no alternative but to eliminate temporary tables. This problem may not exist for row-based replication in MySQL 5.1 and later, but most installations I know of are using statement-based replication, even on MySQL 5.1

This is a contentious topic. People love their temporary tables and will ask hopefully "are you sure this isn't safe?" They'll propose all sorts of ways to mitigate the danger, and I've heard many of them. But I recently heard an angle on this I had not heard before.

[read more...]

Recently I had an interesting surprise with concurrent inserts into a MyISAM table. The inserts were not happening concurrently with SELECT statements; they were blocking and the process list was filling up with queries in Locked status.

My first thought was that the customer had deleted from the table, which leaves "holes" in the middle of it and prevents concurrent inserts. (You can configure the server to permit concurrent inserts even when there are holes, but it's disabled by default.) However, that turned out not to be the cause; the table was only inserted into (and selected from). Instead, the blocked statements were because of INSERT... SELECT statements that were running against the table, selecting data from it and inserting into another table.

Let's look at what happens here: suppose you have two tables tbl1 and tbl2 and concurrent inserts into tbl2 are running fine. If you now run the following query,

The concurrent inserts into tbl2 can block. This happens if you have the binary log enabled. If you think about it, this makes sense and is correct behavior. The statements have to be serialized for the binary log; otherwise replaying the binary log can result in a different order of execution.

The MySQL manual actually says this, but not in the clearest way. It just says

If you are using the binary log, concurrent inserts are converted to normal inserts for CREATE ... SELECT or INSERT ... SELECT statements.

If you use mysqladmin debug, you'll see an ordinary SELECT gets a lock on the table like this:

But on INSERT...SELECT, you'll see this:

That read lock is what's blocking the concurrent inserts from happening.

There's no solution to this, if you need the binary log enabled. (It needs to be enabled for replication.) There are workarounds, though. You can use the old trick of SELECT INTO OUTFILE followed by LOAD DATA INFILE. You can use InnoDB instead. Or you can do something more elaborate and application-specific, but that's a topic for another post.

Few months ago, I wrote about a faster way to do certain table modifications online. It works well when all you want is to remove auto_increment or change ENUM values. When it comes to changes that really require table to be rebuilt - adding/dropping columns or indexes, changing data type, converting data to different character set - MySQL master-master replication especially accompanied by MMM can be very handy to do the changes with virtually no downtime.
[read more...]

As you might know even if you're only using Innodb tables your replication is not completely crash safe - if Slave MySQL Server crashes/power goes down it is likely for relay logs to run out of sync (they are not synced to the disk) plus position on the master which slave remembers becomes stale.

During MySQL 4.0 and 4.1 series there was a great workaround if you're using only Innodb tables - Innodb when Innodb does crash recovery it would print position in master log files up to which replication was done:
[read more...]

To get this straight - I'm not a big fan of filtered or partial MySQL Replication (as of version MySQL 5.0) - there is enough gotchas with replication itself and getting things right with filtering can get quite bumpy road. In some applications however it is very helpful so lets see what one should do or should not do to make it work.

Experience shared here mainly applies to logical level replication. MySQL 5.1 can use row level replication which alleviates some of these issues but I guess it will still take some time before it becomes massively used.
[read more...]

Microslow patch is used by many DBAs and developers to accurately time their queries and to catch those which run less than a second as they can also be a performance killer for a busy application.

Recently I have started the development of an updated version of the patch. The basic idea is the same as for its predecessor - to get more information about query execution logged into slow log, however the new version is loaded with a set of cool new features.
[read more...]

The question I often get is how far MySQL may fall behind and how to keep replication from lagging.

The lag you will see will vary a lot from application to the application and from load to load. Plus what is the most important within same application the lag will likely have spikes - most of applications would have typical lag within few milliseconds while there will be rare cases when replication lags behind several seconds or even longer.
[read more...]