Tag Archives: database clustering

Database Replication – What is it and why is it important?

Database replication is a service for shipping changes to your database, off to a copy housed on another server, potentially even in another data center.

Each change to the records of information in your database or groups of them are organized into transactions.  These each get unique identifiers or change numbers.  Those groupings of changes are logged to transaction logs which are then moved across to the sibling database server, and applied there.

Oracle offers this facility in the form of Standby Database aka Dataguard.  Although it can be messy to setup, it is fairly bulletproof.  If you don’t receive any errors in your logfiles, you can rest assured that the data on your main or master database server looks exactly the same as what’s on your secondary server.  With MySQL the situation can be a bit more complicated.  MySQL replication was built based on statements, not changed data.  So those same statements or instructions (SQL statements of DML & DDL) get rerun on the secondary database server.   Some of the problems associated with this are:

  1. MySQL standby transactions may execute at different times than master
  2. Serialization is important to maintain order.  Transactions need to get applied in the same order as on the primary.  However all tables & storage engines are not necessarily transactional in MySQL.  This can cause troubles, and potentially break replication on the slave side.
  3. MySQL replication can fail silently, that is data can drift out of sync with the master without your knowing.

Caveats aside, MySQL replication is powerful, and quite fast.  With the right caution, and correct tools, it can be as robust and reliable as Oracle’s own standby database technology.

  • Use checksum tools like Maatkit to verify & compare tables on primary & secondary servers
  • Use rigorous monitoring to watch error logs, and checksum logs hourly if necessary.
  • Use Maatkit sync tool to resync tables if they get out of whack.
  • Be especially vigilant if you’re taking backups off the secondary server

Replication can also be a powerful high availability tool in your arsenal.  Setup as a master-master cluster, you can keep your application pointed to the “active” side or role while performing blocking operations on the inactive database server.  Since master-master cluster also known as circular replication applies changes to both databases, you’ll need to stop replication during the blocking operation.  Once it is completed on the inactive side, redirect the webservers & application to the database where you completed the operation, then reenable replication and watch the databases catch up with each other!

Quora discussions – What is database replication?

NOSQL Database – What is it and why is it important?

NOSQL is a sort of all-encompassing term which includes very simple key/value databases like Memcache, along with more sophisticated non-relational databases such as Mongodb and Cassandra.

Relational databases have been around since the 70’s so they’re a very mature technology.  In general they support transactions allowing you to make changes to your data in discrete, controlled manner, they support constraints such as uniqueness, primary and foreign keys, and check constraints.  And furthermore they use SQL or so-called Simplified Query Language to access ie fetch data, and also modify data by inserting, updating or deleting records.

SQL though is by no means simple, and developers over the years have taken a disliking to it like the plague.  For good reason.  Furthermore RDBMS’ aka relational database management systems, don’t horizontally scale well at all.  To some degree you can get read-only scalability with replication, but with a lot of challenges.  But write-based scaling has been much tougher a problem to solve.  Even Oracle’s RAC (formerly Parallel Server) also known as Real Application Clusters, faces a lot of challenges keeping it’s internal caches in sync over special data interconnects.  The fact is changes to your data – whether it’s on your iphone, desktop addressbook or office directory, those changes take time to propagate to various systems.  Until that data is propagated, you’re looking at stale data.

Enter NOSQL databases like MongoDB which attempt to address some of these concerns.  For starters data is not read/written to the database using the old SQL language, but rather using an object-oriented method which is developers find very convenient and intuitive.  What’s more it supports a lot of different type of indexing for fast lookups of specific data later.

But NOSQL databases don’t just win fans among the development side of the house, but with Operations too, as it scales very well.  MongoDB for instance has clustering built-in, and promises an “eventually consistent” model to work against.

To be sure a lot of high-profile companies are using NOSQL databases, but in general they are in use for very specific needs.  What’s more it remains to be seen whether or not many of those databases as they grow in size, and the needs for which they are put stretch across more general applications, if they won’t need to be migrated to more traditional relational datastores later.

Sean Hull asks on Quora – What is NOSQL and why is it important?