Friday, November 6, 2009

Keeping a Slave Database in Sync

I haven't posted too many details of exactly how the WunderCounter is set up, but it uses MySQL replication. This means that there is always one master database server and one or more slave databases which download database updates from the master. The slave databases work much harder than the master and may occasionally require a reboot. Sometimes when this happens database tables crash and need to be repaired. This happened earlier today.

Usually the slave catches up to the master within a few minutes and the lag is barely noticed, but in this case, the crashed table had over 10 million rows. When you're dealing with that amount of data, MySQL isn't always able to repair the tables in a reasonable amount of time for a live site.

While I was dealing with this user log files appeared to be stuck and were not updating, which would make you think your tracking was not happening. What was actually happening was that your hits were being tracked by the master database, but not being updated on the slave database, which is the database which the reporting scripts connect to. The issue with the rogue table has now been fixed and the slave has caught up to the master. So, you can now view any of the hits which you were unable to view earlier. No data was lost, only delayed. :)

No comments: