Enterprise level web and mapping applications, we are your able technology partners.

I just recently spent some time with a client investigating some performance issues with mysql subqueries.

The obviously contrived example below

Depending on data size the query can run very slow. When running two queries and pasting in the actual IDs the query runs extremely fast.

The good news it is looks like it is fixed in version 6.0 - looking forward to it becoming a production release....

Dual master active-passive is a good way to go for a highly available database server. I can offer the following tips for a more painless dual master experience:

1. Make sure all your tables are INNODB – Fixing a broken replication with any MyISAM tables will cause a good deal of pain – or limit you with unnecessary downtime from your production environment.
2. If possible, replicate from the beginning. Install your DB’s, configure for replication, create replication user acess rights, then restart your mysql instances. All databases and user rights from that point on will be replicated. If you can’t do this then take a snapshot of the ‘active’ master, and create your passive master from the snapshot. See below for obtaining a consistent snapshot.
3. Use log-slave-updates so that replicated data that is processed in master-master is also sent to any slaves. If you are replicating of either of your masters for backup, then set this on the master that is also replicating out to secondary slaves – or set on both so you have the freedom to change topologies.
4. Ensure that max_allowed_packet is consistent accross all master slaves. If you don’t you may experience the Slave IO thread simply stopping with no error message, and NULL seconds behind error
5. Use sync_binlog=1 so that transactions are written out to the bin logs on each write – relying on the O/S to flush the logs is not satisfactory and wont guard you in the event of a lost server or host. Make sure innodb_flush_log_at_trx_commit is set to 1 (the default)
6. If you are not replicating all your databases, be aware that the inserts into one database that is not replicated to a database that is replicated – INSERT into replicated_db_name.tablename will not replicate the insert. This can be used to your advantage in a dual replication set up if you need to fix replication errors – but i would still suggest the snapshot approach below
7. Reset slave is not an option – I could not see this as a way to fix a broken slave. You will end up loosing data. Best refer to the snapshot method
8. Nagios has some great mysql replication event handlers to warn you in advance of a broken replication setup. You will need this in your production environment – or some other method alerting you of a broken replication
9. If your replication is broken – make sure no database writes can make it to the passive database. If this happens you may be losing data – and at the least be in for a whole lot of hurt when putting it back together.
10. use show slave statusG. If you have errors, you can attempt to fix but in dual master remember that your changes will be replicated back to the other masters – and may cause conflicting errors. Use the snapshot method
11. Use the auto_increment_offset and auto_increment_increment to configure your primary key allocation method so odd primary keys are generated on one master, and even keys are generated on the other.
12. You can use mysql proxy or a IP load balancer to direct your SQL requests to the active master. If/when your server crashes, the passive master will be up to date and pick up to become the new active master – with no down time – yeah!

The Snapshot Script

export options=' --add-drop-database --triggers --routines --single-transaction --master-data=2 -u mysqluser -p '
export datestamp=`date +%d-%m-%Y`
mysqldump $options --databases database1 database2 database3 > snapshot_$datestamp.sql

The master-data=2 option will create the necessary information to point your new slave at the master and get replication running again – and place it commented out in the first few lines of the file. Use head -40 snapshot.sql to see what it is if your SQL file is large.

The single-transaction option will mean the the database snapshot will be consistent from beginning to end (Innodb only!).

You are pretty safe to run this on a live production server.

You will need to snapshot all the databases that you are replicating in the one command or the master log postion will not be consistent – it is global for all databases.

Once you have the snapshot. Issue a stop slave; on the passive master, load in the database, issue the change master command below, then issue a start slave;


CHANGE MASTER TO
MASTER_HOST='masterXXXXX',
MASTER_USER='mysql_repli_user',
MASTER_PASSWORD='xxxxxxxxx',
MASTER_LOG_FILE='mysql-bin.xxxxxxx',
MASTER_LOG_POS=xxxxxxxxx;