Pythian Group

OpenSQLCamp was a huge success! I took videos of most of the sessions (we only had 3 video cameras, and 4 rooms, and 2 sessions were not recorded). Unfortunately, I was busy doing administrative stuff for opensqlcamp for the opening keynote and first 15 minutes of the session organizing, and when I got to the planning board, it was already full….so I was not able to give a session.

• Comparing Non-Relational Databases: MongoDB, Tokyo Tyrant, CouchDB by Igal Koshevoy of Pragmaticraft
• Drizzle Client Rewrite – Clark Boylan leads the requirements and design discussion for rewriting the Drizzle Client
• Drizzle Plugin Hacking
• Drizzle is Not MySQL with Changes – Brian Aker gives a talk on Drizzle, and how Drizzle is not MySQL.
• Fractal Trees – by Bradley Kuszmaul of Tokutek
• Geographic Operators in SQL (PostGIS) by Webb Sprague
• Goal-Driven Performance – Peter Zaitsev speaks about how to achieve goal-driven performance.
• Graph Engine for MySQL – by Antony Curtis
• Intro to Cassandra
• An Intro to CouchDB: What caught Ubuntu’s eye by Mike Miller (Cloudant)
• Maria – MySQL founder Monty Widenius talks about Maria, and the past, present and future of MySQL.

Read the rest of this entry . . .

OpenSQLCamp was a huge success! Not many folks have blogged about what they learned there….if you missed it, all is not lost. We did take videos of most of the sessions (we only had 3 video cameras, and 4 rooms, and 2 sessions were not recorded).

All the videos have been processed, and I am working on uploading them to YouTube and filling in details for the video descriptions. Not all the videos are up right now….right now all the lightning talks are up.

• All the lightning talks belowin one continuous video (just over 54 minutes total)
• The Graph Engine (Antony Curtis)
• Cluster/J, a new set of Java APIs to MySQL Cluster 5.1 (John David Duncan)
• Sphinx, the fulltext storage engine (Peter Zaitsev)
• iiBench, the Indexed Insertion Benchmark (Bradley Kuszmaul of Tokutek)
• JJtree in Coco
• Integrating OSS with Windows (Tom Hanrahan of Microsoft)
• Trainwreck, an agent for MySQL replication (Domas Mituzas)
• Column Stores (David Lutz of Infobright)
• I Play With Data, about doing real statistical calculations of data, when SQL gets in the way (Joseph di Paolantonio)
• Your Guide to NoSQL (Brian Aker)
• PL/Parrot The call for PL/Parrot in Postgres is put out there (Jonathan “Duke” Leto)

Read the rest of this entry . . .

Last night at the Boston MySQL User Group I presented on how to get a consistent snapshot to build a slave, how to use mk-table-checksum to check for differences between masters and slaves on an ongoing basis, and how to use tools such as mk-table-sync and mysqldump to sync the data if there are any discrepancies.

The slides are online at http://technocation.org/files/doc/slave_sync.pdf.

The video can be watched on youtube at http://www.youtube.com/watch?v=Un0wqYKmbWY or directly in your browser with the embedded player below:

Read the rest of this entry . . .

In SQL Server Replication, a regular check is getting the number of undistributed commands for a transactional subscription. This can be done using the replication monitor (SQlmonitor.exe from the RUN prompt) or programmatically by running sp_replmonitorsubscriptionpendingcmds at the Distributor on the distribution database.

One issue with sp_replmonitorsubscriptionpendingcmds is that you will have to submit a few parameters without defaults (the required parameters are mentioned in the above link).

I wanted once to get status information about all subscriptions (Last logged message, status, agent, etc.), plus the number of undistributed commands for each subscription. For the replication info there is the MSdistribution_agents table, which is stored in the Distribution database and contains one row for each Distribution Agent. For the Agents’ history there is the MSdistribution_history table, which contains the history for Distribution agents, and is also stored in the Distribution database.

To return this, my idea was to get a list of publishers, publications, subscribers, and so on, into a temporary table, loop against the result, and run sp_replmonitorsubscriptionpendingcmds for each row, and then join against MSdistribution_history for the final result.

Here it is. All comments and additions are welcome.

Read the rest of this entry . . .

Slaves can be used for:

1. Horizontal read scalability — take the load off a master database by spreading reads to a replicated slave.

2. Disaster recovery — some disasters, such as a hardware failure, can be solved by having a slave ready to propagate to a master. This technique also works to make offline changes to a master/slave pair without having database downtime (see below).

3. Consistent Backups — without disrupting production usage, a slave can be used to take a consistent backup by stopping the database and copying the database files. This is a free and uncomplicated way to get consistent backups (innodb hot backup is not free, and using a snapshotting tool (such as LVM’s snapshot capability) can be complex. Not everyone wants to manage snapshots.)
   

Be careful when using a slave for more than one purpose. Using a slave for more than one purpose can be done, but carefully. For example:

If one slave instance serves the needs of 1 and 2 — Where do the reads go when the slave is promoted to a master (or taken offline for maintenance)?

If one slave instance serves the needs of 1 and 3 — can the read slave be down for the length of time it takes to take a backup? If not, this solution is not appropriate.

If one slave instance serves the needs of 2 and 3 — Where does the backup get taken from when the slave is promoted to master (or taken offline for maintenance)?

There are certainly workarounds. We either create a propagation procedure (whether for maintenance or in an emergency) or assess an existing one, to cover these cases to make sure any dual-use of a slave does not leave a client hanging. We regularly test whether slaves are in sync, to ensure accuracy no matter what purposes replication slaves are used for. We also go through actual slave-to-master propagations. This can be useful as a drill, and can also be useful to avoid downtime.

Avoiding Downtime with Replication

In many cases, using replication can avoid downtime during an offline database change (for example, an OPTIMIZE TABLE to defragment a table). To implement this, the slave is taken out of service, the change performed on the slave, the slave is put back into service and propagated to be the master. Then the previous master is taken offline, the change applied to the previous master, and the previous master can return to service (either as a slave of the current master, or by propagating the previous master to be the current master.

One very helpful use of the technique Sheeri described in Remote connections without leaving the mysql shell is making sure that replication is working properly.

According to the MySQL Reference Manual’s section on SHOW SLAVE STATUS Syntax, it shows information corresponding to the slave thread in the slave server. When replication is broken, however, or not working properly due to network issues between master and slave, this information may not be accurate. This has improved over recent releases, but it’s still not perfect.

The question, then, is: how to be 100% sure (or as close as you can get to 100%) that replication is running fine? The answer, as offered by Sheeri: use CONNECT.

Example

1. Log into the slave using the mysql client and issue SHOW SLAVE STATUS:

   Read the rest of this entry . . .

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 Read the rest of this entry . . .

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…. Read the rest of this entry . . .

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:

Read the rest of this entry . . .

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: Read the rest of this entry . . .