The MySQL Sandcastle
Developer sandboxes are crucial for quality software creation: a place to work in a locally destructive way, trying new avenues to problem solving at minimal risk. There are several different ways to put a sandbox together, but all essentially built around the concept of a scaled down copy of the target production environment.
In the case of internet applications this means an interface server (usually web), processing environment, and persistence layer (disk or database storage). Most simplistically this is just another subdirectory or virtual host directed to the developers' local copy of the operating code, where they get to make their changes without affecting or being affected by the work of others (shared processor and disk constraints aside, and out of scope for this discussion). The persistence layer, most commonly a database, has some additional constraints though: getting enough good sample data in place to be a good representation of real-world activity, both in terms of permutations and raw number of records, can be cost prohibitive either in documentation and setup (assembling the samples) or simply in the size of the data set.
Working with smaller data sets tends to help with the validation of structure, and doing it on scaled down hardware is often used as a way of simulating (poorly) the probable performance of proportionately larger data sets in a higher-class processing environment (and typically under greater load). There are some things that just can't be worked out on a reduced scale, however, especially related to index and query tuning.
A solution we've recently implemented is the MySQL Sandcastle - a more elaborate construct on top of the typical sandbox concepts that has some significant benefits for web app development.
We started with a rich staging environment: a copy of the production database, minimally sanitized (personally identifying and/or financially sensitive data obscured) and trimmed down only as appropriate (retaining 90 days worth of operational data from history tables) for the hosting environment. The sanitation makes it more like a bridge between test and literal stage, but gives us exactly what we need to work with regardless.
On top of that staging environment we're taking advantage of the MySQL Merge storage engine, which essentially marries multiple table definitions under a single abstracted table interface. This is similar to a unioned view, except that the structural checking is done during creation, the contents are fully malleable to all standard CRUD operations, and there's no need to spool data to temporary tables (which most databases do in some form or other for these kinds of operations) during complex queries. A default insert mode also tells the table which of the component tables to add records to (exclusively).
Per the illustration then, we have the Primary Staging Repository (PSR), plus 2 additional databases per sandbox (STAGE_[user] and STAGE_[user]_M) which round out the environment. The STAGE_[user] database replicates the structure of the PSR but starts out completely empty. STAGE_[user]_M shadows this structure, but swaps out the engine definition from MyISAM to Merge in order to combine user stage and PSR. In order to keep PSR clean for all developers to continue their work, each user is granted an un-privileged account with full access to their own sandbox databases and read-only to the large store (Merge table definitions must be created with full CRUD permissions to the underlying tables as well, so these are created by a privileged user prior to turning the reins over to the per-user accounts), then accesses the environment only through the ..._M instance.
Obviously this restricts activity: any attempt to update or remove records which exist only in PSR will likely produce errors, and at the very least will be ineffectual and quite probably anomalous. The most effective usage pattern is for unit tests and general developer activity to still recreate data they intend to directly modify, leaving the large store as a good general sample set for read activities (which accounts for ~60-80% of all development activity anyway). The benefits are pretty big: developers get cheap access to large swaths of regularly refreshed data without having to continually repopulate or propagate in their own environments, can work destructively without interference, and even test structural changes (simply excluding PSR from the Merge and redefining the table) to the database before finally recombining efforts in Stage (which does work destructively against the PSR) for integration testing before promoting to production.
There are some other disadvantages as well: it's possible for local insertions to the underlying tables to create keys in identical ranges which will appear to the client as duplicate primary keys (in violation of the very clear and appropriate designation of "Primary Key") in the merged data set. Setting new exclusive AUTO_INCREMENT ranges per table doesn't help yet either, due to a bug in the engine that treats it like a combinatory (multi-column) key definition, using MAX( PK ) + 1 instead of the defined range of the target table. Merge tables are also restricted to MyISAM table types, excluding the oft popular and appropriate InnoDB (and other) options. Treading carefully easily avoids these, but it's good to be aware of them.
A more complete workaround would be updateable views, using a combination of exclusive UNION (rather than implicit UNION ALL) selects and a series of triggers which not only manage insert activity, but replicate into the developer shadow sandbox the intended target record prior to modification in the case of insert. Establishing this kind of more extensive sandcastle pattern would be far more capable, should be scriptable without great effort, but still carry a small number of its own gotchas (most notably that un-altered trigger definitions on the underlying target tables with any dependence on external tables would be operating locally rather than through the same abstracted view, and insert triggers may fire at unexpected times). For now the limited Merge version is sufficient for our needs however, so we haven't gone so far as to even kick the tires on this possible approach.
I have a few more changes to make to the sandbox recreation script before it's ready for wider consumption, but it's not an especially arduous process for someone enterprising to reproduce on their own with little effort.
Enjoy!
The Long Journey
I've written a lot about my health over the past few years, and my efforts to try and do something about it. Each little clue gave me new hope, into which I threw my full energy. I took every inch and reveled in it, moving as far as I could before inevitably declining once again (increased effort met with increased resistance). These were genuine steps forward though, rather than false starts: I lost weight, gained focus, and started sleeping better, so each time I was really complaining from a new place.
The biggest gains came from discovering sleep apnea, candidiasis, and suspicions of mitochondrial myopathy. None of these are naturally indicative of McArdle's Disease (the eventual diagnosis) though, nor are they typically concomitant. They didn't fall into their place in the puzzle until the picture was already becoming clear, when it all suddenly came together.
I should start by explaining a little about the condition. Glucose is the primary source of energy for nearly everything in the human body, the whole process referred to as "glycolysis." Most cells, especially muscle cells, have an internal reserve of it in a compact polymer-like form called glycogen. When demand for energy increases, molecules are trimmed off the end of the chain and made available to the mitochondria to do their business of converting it into active energy (adenosine triphosphate, for those taking notes).
This store of energy is pretty high - enough for 12-20 hours worth of activity before needing to be replenished. That replenishment happens on a regular, ongoing basis, to keep stores topped off whenever spare glucose is available, and any surplus is either excreted or converted into starches and stashed elsewhere. Normally this is enough to keep the body readily burning glucose between meals without running low - an exception would be fasting (more than a day), starvation, or extremely high demand: marathon runners, for example, can completely deplete glycogen stores after about 20 miles of continuous running, a phenomenon they refer to as "hitting the wall." At that point continued activity requires use of the stored fats as an alternative fuel, and risks damage to those tissues unable to do so.
McArdle's refers to a defect in or absence of myophosphorylase, which is a fancy name for "the enzyme that breaks glucose off the glycogen chain." This deficiency means that the primary source of stored energy is either completely off limits or so impaired as to be unable to meet the body's demand. This results in dramatic exercise intolerance, and in some cases cramping or seizing of muscle fibers (which require energy both to contract and relax) in a disparate and uncoordinated state so severe that they can actually rupture (rhabdomyolysis - this will be on the test), spilling their proteins into the blood and straining renal functions. Alternatively, the secondary fuel source based on fatty acids can be engaged (lipolysis).
An interesting side note here is that this is the primary intent of low-carb diets: by lowering the intake of glucose (and things easily synthesized into it by the metabolism), glycogen stores are exhausted and the body has no choice but to turn to lipids in order to remain functional. It's a hack, but a potentially effective one - your mileage may vary though, and not every system can handle the kind of stress this creates.
To point, too heavy a reliance on lipolysis floods the body with its waste products and increases the acidity of the blood (ketoacidosis). Healthy folks don't normally get to this point - diabetics can be affected in pretty nasty ways though. Those with McArdle's suffer a similar fate since the reliance on it is more absolute, and constant: this happens on regular diets without regard for carbohydrate intake. I had hit on a form of metabolic acidosis in my investigation earlier but had come to it from the wrong side, thinking that it was an inhibition of the mitochondria in making use of available oxygen, when in fact the mitochondria work wonderfully and are simply making-do with limited materials on hand. The effect is the same though: fatigue, memory disruption, stupor, and eventual unconsciousness.