The Challenges
I am aware that there are practices such as only adding database objects, i.e. tables and columns, never modifying or removing them
At one company I worked for, a rolling window of raw data equated to about 6 months of data and ate up 10 TB. The data was then processed into an RDBMS format, with some data portions hitting the cutting room floor. After, this dataset still weighed in at 6 TB of usable data which accounted for about 10 years of reportable data.
The point being that at scale, these kinds of practices (never modifying or removing data) simply isn't practical. Storage is expensive - probably the greatest compute expense of most companies. This provides several interesting challenges:
- Backups - the InnoDB plugins are great and all, but the backup times on that much data just isn't that practical.
- Restore times - for large datasets - especially if you need replication to catch up after a restore getting back to an operational state can take days or even weeks.
- Creating/seeding new instances - Often the work you may be doing in dev/test involves ETL (Extract, Transform and Load) jobs on your dataset. These jobs need to be validated using QA testing units, but this needs to be done in a non-destructive manner so that the original production dataset is preserved. While in a disaster, you may be willing to deal with a long restore time on the understanding that backups are an insurance policy (and the intent is to avoid disasters) the DevOps development workflow requires that, essentially, you be able to perform a restore or copy of your data on a regular basis (perhaps multiple times a day).
- Capacity - Slinging around that much data at the scale I just described can be very I/O intensive. Not only do you need to address the problems described in 1-3, but you need to do it in a way that doesn't cause outages or performance slowdowns to your production systems.
While the above considerations may not be a concern at smaller scales, at larger scales, these become huge problems to tackle. This means that it is extremely important that you define your requirements and forecast the size of your dataset.
Defining requirements
As a result of that excercise, you need to define several requirements:
- RTO - RTO or Restore Time Objective for backups is one of the most important drivers of database backup solutions. While at first it might not appear relevant to most of the other problems described, it becomes extremely relevant when you ask "What if I used my backup solution for creating or seeding new instances?" (I'll cover some techniques for doing that in the next section)
- RPO - RPO or Restore Point Objective for backups defines A) how far back you are able to restore to (minutes, hours, days, weeks, months, or years) B) The backup interval or frequency at different tiers and C) how granularly you can restore. For example, for E-mail databases, Message Level Backups - restoring a specific E-mail - are often sought. Similarly, you may find that data older than a few days is completely useless - so there is no point restoring back a year. It depends on the kind of data you are working with.
- Size of your dataset - This is important because for a 1MB database, your RTO may be achieved with most backup products and solutions. For a 10TB database however, you will find that a full, row level backup to LTO 3 tape probably won't achieve your RTO and could interfere with your RPO because backups begin exceeding your backup window. You can't exactly just do a mysqldump on this large of a dataset, but could probably get away with that on the 1MB database.
- Database consistency - A final thing that makes an immense difference in continuous deployment, site reliability, scalability and high-availability when working with databases is your need (or lack thereof) for consistency. There are three basic types: immediate consistency, Just-In-Time (JIT) consistency and eventual consistency.
In addition to the above major considerations, you also need to consider:
- Licensing and support requirements (open source or closed source; in house support, third party support or vendor support, and does this fit your budget?)
- Application/language requirements (the connectors for many databases can be important; is your app compiled? Do you have access to the source code? Can you recompile it, or is it provided by a vendor? Does it run on an interpreted language?)
- Political requirements (Does your organization only trust Oracle? Do they hate Oracle? Do they not trust MySql? How do you feel about MariaDB or Postgres? Do we require an EAL certified DB product due to contracts?)
- Database engine (innoDB? MyISAM? Blackhole? NDB Cluster? Spider?)
- Historical or compatibility requirements (we used PL/SQL for years and half our code is built into the oracle engine! How could we ever port over to MariaDB?!?)
All of these things (significantly) impact the tools available to you.
Some Options for in-house data management
Note: The following is in no way exhaustive, and other SE users should chime in with additional suggestions.
With the generic considerations out of the way, let me provide you with some techniques and technologies for addressing some of the above. First, ask yourself if you really need to use an RDBMS or if unstructured data with something like Hadoop, CouchDB, KV pairs, or even Object Oriented Storage (something like swift) is an option.
Second, consider looking into a cloud based solution. This outsources some of this headache and leaves the complicated problems to highly qualified (and paid) individuals. At scale however, you can find this really eats into your budget (cloud providers DO make a profit at this, and at a certain scale, you can just afford to employ these experts yourself) or if you are working under specific security or political requirements (read: we can't do clouds). If you need to run in-house, consider a hybrid NFS/Fibre Channel Filer. Most of these filers, such as those by NetApp, Pure Storage and Tegile support a delta-based snapshotting and cloning technique that can be very handy for A) taking backups, B) restoring backups and C) seeding new backups.
At this point, I need to note that I am not a backup and storage expert, so there some portions of this problem that I never quite was able to solve before I moved on to other problems (and greener pastures).
But that being said, some of these products allow you to take differential snapshots underneath your database. You will need to script out a full "lock tables with read lock" on one of your database instances (a read-only slave is recommended) and dump your binlog position or GTID for InnoDB based engines. But, when using these filers, once you script that, you will be able to use these snapshots to create new instances of your database. You will want to put binlogs on a separate partition and put only your database data on the other partition. With this, you will be able to clone these partitions (on NetApps, this is know as a "FlexClone") The only issue with this technique is that FlexClones cannot span datastores which means that all your I/O is still impacting the same datastore/volume and calculating the deltas on these volumes can cause some overhead in terms of CPU resources.
This is because for each block read, the filer must determine if the data resides in the frozen original snapshot, or in the delta. For volumes/stores with multiple snapshots, this might need to be checked multiple times. You can overcome this by refreshing the data (meaning, discard your snapshot and clone it again periodically - which might happen naturally and organically for a good continuous deployment environment) or by permanently splitting the volume (known as a "Flex Split" in NetApp terminology) which will take a moment to permanently resolve the deltas and create an entirely new and separate volume.
These delta clones have the added benefit of reducing your overall storage requirement - you can spawn several clones or instance of your production database to do your development, testing and validation. If you are only keeping one copy of your large dataset plus the (what are likely to be) small deltas, you reduce your overall storage cost and footprint.
The only trick here is that this may not constitute a full backup solution as the "backup" still resides on your filer. For this you may need to use something NetApp calls a Snap Mirror which will mirror data (using rsync-style technology) between filers and even datacenters, or use some type of integrated backup solution which can backup to tape one of your delta snapshots or a flex-clone.
This however has one major flaw: All of your data - dev, test and prod is still using I/O on the same filer and storage head. To work around this, consider creating a slave database instance on a second filer which can be the seeding point for you Test and/or dev filer, or consider using a load balancer/applcation delivery controller for your application layer to mirror production requests into your testing (and/or dev) environment(s). This has the added benefit of throwing prodcution traffic at your QA/Test environment before promoting deployments and code to production and allows you to test for issues that might not be immediately noticed otherwise. You can then check your logs for errors based on production traffic and user behavior in test.
This should allow you to use a few script to programatically spawn and destroy entire (and large) datasets for use with continuous deployment methodologies.
Scalability and High Availability
While you asked about continuous deployment, DevOps is conserned with more than just continuous deployment - so I am going to include some bits about redundancy, scalability and high availability.
I mentioned, JIT, immediate and eventual consistency. This is where varous RDBMS engines come in. Eventual consistency is relatively easy by simply configuring circular asynchronous replication. This can cause some collisions however *(what if your application layer updates data on one side of the cluster and on the other side of the cluster before replication is completed?) For immediate consistency, look at Galera cluster which will force synchronous replication, but causes scalability issues (how will you replicate to your Disaster Recovery site or load balance geographically without incurring significant latency due to propigation delay at the network layer?) You can also see if you can do synchronous replication within the datacenter and asynchronous replication between sites, but this seems the worst of both worlds.
Typically however, most people do not need fully synchronous replication - this is usually only needed for very specific (and exotic) high-write environments where multi-master is needed with table sharding. Most apps can deal with Just-In-Time consistency using a database proxy. For example, ScaleArc will monitor replication status and track where writes just went (to send subsequent read requests to that location until replication catches up) in order to provide Just-In-Time consistency and the appearance of database consistency. ScaleArc is compatible with Postgres, MySQL, MariaDB, Oracle and MSSQL and can use regular expressions to shard/partition your databases for applications that can't use shard keys. It also has a robust REST API for your configuration management software to interact with - and their support team is outstanding.
Similarly, you might wish to consider a free alternative, MaxScale developed by the MariaDB team for MariaDB. It lacks the GUI and some of the caching features of ScaleArc however.
Finally, MySQL fabric (and the in-RAM only MySQL Cluster - if you can afford that much RAM) are other potentials - especially with MySQL's new proxy. This can provide the scalability and redundancy component to your environment.
Postgres and Oracle (Eg, GoldenGate and RAC Cluster) should have the replication and sharding features you need, but ScaleArc will pair well if you need a proxy.
Ultimately, all these pieces and technologies add up to a highly flexible environment suitable for continuous deployment and development if you are unable to simply use a cloud based environment and let your cloud provider deal with the above problems for you.