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.

I hit on McArdle's as a possibility in my own studies just before I was recruited by Amazon, though there were a couple of things that didn't quite add up for me. The subjective descriptions of other sufferers sounded really really close, a better overlap for my experiences than anything else I'd encountered to that point. However, I didn't have the severe cramping, nor the myoglobinuria - the voiding of protein from the damaged muscle in such a way as to turn urine red or "rust colored." I grew up in the Pacific Northwest: rust, in that high-humidity environment, is a bright orange or red. I'd experienced darkened urine before (sorry to get visceral here), but it was more brown than anything else, was very infrequent, and not repeatable under controlled conditions. During a physical before relocating to Seattle I asked them to run a CPK, which looks for serum creatine kinase (the chemical marker of that damaged muscle) which is elevated in 90% of McArdle's sufferers: results came back normal and I scratched this one off the list.

Once in Seattle, apart from the family, I decided to give myself a break in some respects. Since I didn't have anything better to do (no family, trying to save money since I was living separately, and not particularly interesting) all I really did was work and sleep. I allowed myself to sleep whenever I wanted to, extended naps after work, sleeping-in on the weekends with a nap or two on Saturday and Sunday (each!), until eventually waking up from a nap didn't hurt - I didn't shudder or feel sick within a few seconds after starting to move, and then something even more surprising happened: I stopped needing the CPAP at night. The sleep apnea completely vanished for a time, as long as I was napping with such high frequency.

The results were amazing. I slept better, dreamt more frequently, and was even able to take longer walks. I lost about 10 pounds in a couple of months without changing my diet (I mean come on, I was eating like a bachelor - a bachelor who can cook, granted, but not especially health conscious and with a penchant toward mint ice cream in the evenings). After those couple of months the work schedule started to pick up and my resting decreased, the CPAP came back on, and exercise tolerance reined back in to the "inhibited" range. The stress was a lot higher too - being apart from my family for several months had sucked all the fun out of exploring a novel place. I felt a need to demonstrate to the powers that be that I was not taking any of it for granted, and dove into the work with all the vigor I possessed to make the most of the fortune bestowed upon me: man, did I ever work hard, even during the once-monthly weekend visit back to see my wife and daughters. We did everything we could to help sell the house make judicious use of our resources.

After three months my corporate housing benefit expired and I was forced to find a new apartment on my own dime. I narrowed it to a pair of places relatively close to work and made the move. Interestingly enough, one of the two could never correctly coordinate a showing: three different attempts always saw the super pulled away on some other issue or ill, and I was forced to take the other without ever earnestly evaluating the competition. The reason this is an interesting fact is because the one I got was up the steeper hill, a crucial contribution to the eventual diagnosis.

I ramped up my work even more. During the holidays things are busy in the retail world, and Amazon doubly so (on account of being good at what they do). My areas of responsibility overlapped with physical operations, so everything was closely monitored and actively engaged. I would work 9-10 hours at the office, walk the 1.1 miles back up the hill to the apartment (or in a real pinch take a shuttle bus - I was living across the street from one of Amazon's facilities, just not the one I was supposed to work at, and they have shuttle service between them), have my video chat with the family before the girls went to bed, and work another 6-8 hours on average. Saturdays I would put in only 4-5 hours, and try to rest on Sunday (except for the emergency processing which had to be attended to at 10 pm daily, no exceptions, and any pages I received). It shut me down: I suffered through it because I had to, but the limited treatment I had for the sleep apnea became ineffective and the afternoon meeting narcolepsy started creeping back up on me.

Clearly, this was unacceptable. I was trying to completely own the job and attendant performance expectations out of a desperate display of over-compensation for any perception of my own failings, and still demonstrate to the cosmos that the blessing of opportunity had not been misplaced. I would magnify what I'd been given, and failure was not an option: I re-opened an investigation into my health, starting over with new physicians and determination to finally see it through and regain some dignity. I was also armed with the recommendation from my last consultation in Utah that I follow-up with a neuromuscular specialist to see if they could find anything.

I got myself a referral and an appointment a month in the future. Tired of repeating myself and feeling like I was leaving out anything of relevance, I typed up my medical history and current state of symptoms and controlled conditions under which they were manifest and went into the consult armed with a fairly exhaustive twelve page document. Having previously personally ruled out McArdle's, I essentially wrote around it: made no mention of mitochondrial disorders, glycogen storage issues, anything of the like. I actually gave it a bent slightly toward some kind of transient ischemic event: the intense walk back to the apartment after a series of grueling days eventually resulted in some pretty nasty aches, but not until I took a break and rested. It was only well after the exertion that muscles seized up, and then after a power nap turned into a deep and exhausting pain that easily brought me to tears. On a scale of 1-10, with 1 being pain free and 10 being unconscious, this rates a 7; for comparison, a root canal tops out at 4 and deep lacerations a 6 at most (it'll send me in to shock, but tears are a distant thought). I took this to be a potential sign of ischemia (inhibition of blood flow, usually localized) due to the fact that deep tissue damage from suppressed blood supply doesn't happen in full force until that flow is restored (re-perfusion injury and apoptosis). The resulting darkened excretion following 12-24 hours later confirmed some kind of muscle wasting, but again given its brown color I associated it more with necrotic tissue than a possibility of the more traditionally red myoglobin.

At the appointment, after a very brief evaluation to make sure there was nothing glaringly pathological, the specialist looked over the history and immediately recommended McArdle's as though it was already on the tip of his tongue. I told him why I'd ruled it out, specifically noting the negative CPK test, but by then he'd closed the folder and was wearing that medical professional look of, "I'll listen to you, but I'm pretty sure I already know what's up and am not likely to take it under real consideration." He made no additional notes, and simply stated that he'd like to see me again after my consult with the neurologist (to see if it really all was in my head - though legitimately based on neurological function, rather than psychosis per se).

The neuropsychiatric evaluation was set for the middle of May. Only, I could swear the receptionist told me a corresponding date in March. I showed up on that date with all my paperwork filled out, ready to go, and was a little chagrined to learn I'd rearranged my schedule for something not due to happen for another four weeks. They offered to add me to the waiting list in case something earlier came up, which I accepted, and went back to work dejected.

Prompted by the prior meeting and hint toward McArdle's from the neuromuscular specialist I did some more research, and found the available materials evolved somewhat since my prior glance in this direction. Someone equated "rust" with "brown," a real forehead slapping moment of course - not only was the event predictable enough that I should have seen it and been able to disregard the minor deviation, but I've seen brown rust long enough I should have adapted to a broader definition anyway. Secondly, an explanation that the cramping was not necessarily present in all conditions, and frequently occurs after the exertion or even during rest. Following as many threads as I could I tied the alternate lipolysis metabolism to non-diabetic ketoacidosis, which rounded out the remainder of my symptoms. I had a fit for everything, there was no part of my condition not explained by the disorder, and nothing in the disorder at odds with my presentation. I didn't know what to do about it yet, but after 15 years of intensely searching I received a concrete corroboration and vindication: life really was hard, and I wasn't simply incapable of handling otherwise normal levels of stress.

One week after that appointment, as I was preparing for my monthly trip down to see the family, I had my regular meeting with the boss to sync up. This time, however, rather than the one-on-one format, our friendly neighborhood HR representative was there. As nice as they usually are there are times you don't want to see them; this was, in fact, one of those times. Business concluded, boxes were packed, and with an hour to go before I was already scheduled to head out to the airport I found myself an unemployed victim of a tightening financial reality. The timing could have been better, certainly - relaying the news to my family under those circumstances was bittersweet. I was due to see them, and to return to be with them more permanently, but with an uncertain future. Not only that, but the steps I was taking toward possible management and resolution of my health were going to have to be put on hold once again: even with a continuation of health coverage through the next month and opportunity for COBRA it would simply cost too much, and without new work lined up I couldn't risk the family finances.

While I was down visiting the family and celebrating my daughter's birthday, I received a phone call: it was the neuromuscular department letting me know they'd had a cancellation and would I be interested in moving my appointment up from May 12th to the coming Monday? Talk about providence! My return flight was on Sunday night, and I was planning on immediately breaking down the apartment and closing off accounts so I could have my belongings packed up and be back on my way to Utah as an officially binding move (cats and all) by Thursday. If I were to be able to get this evaluation at all, that would be the only way and the only day to do it - I very gratefully accepted the rescheduling and continued to enjoy my time at home.

I returned to Washington and went in for the six hour battery of tests designed to exercise and measure the functional capacity of the brain. During a break in the tests I had the opportunity to speak with the prior specialist, from the earlier consult - I let him know my findings, and we chatted at some length about possible avenues for management (and how to begin exercising in a legitimately beneficial way despite the condition, rather than as a grueling ordeal of determination). The tests concluded with favorable results, I finished packing the apartment, and made the long haul drive (14 hours straight) to be home genuinely and officially: for the first time in seven months I didn't have a looming clock ticking away until my next departure.

Further study and experimentation has led me to the following series of conclusions and strategies:

1. Most of the really nasty bits of McArdle's (the rhabdomyolysis, resulting myoglobinuria and possible renal failure) are only distantly present in my condition, which is certainly in my favor and gives me hope for decades to come.
   
2. The majority of my symptoms are the result of persistent lipolysis and metabolic ketoacidosis, so things are working pretty well and happen to have unpleasant side effects.
3. Minor snacking under ketoacidosis can in fact lead to a "sucrose induced second wind," but the body's already primed to store the excess since it's reacting as though starving (or otherwise resource impaired) and has slowly but constantly contributed to my weight gain over the years.
4. A carbohydrate (especially sucrose) rich environment contributed to the candida albicans overgrowth of yore, long since controlled.
5. Metabolic acidosis has a disproportionate effect on the diaphragm, even capable of inducing sleep apnea due to paralytic hypopnea (found some nice research papers on this).
6. Acidic (low pH) blood is filtered by the kidneys, which neutralize it using bicarbonate.
7. Most of the bicarbonate in the body is secreted by the acid pumps in the stomach, and an extra need for it does increase total pump output (acid reflux anyone?).
8. THEREFORE: adequate rest, which prevents the accumulation of metabolic toxins and/or provides opportunities for their filtration, OR a heightened filtration schedule, should be able to restore a more normal functional baseline. In practice, this means I either have to take it really easy (intense aerobic activities of my youth are all off the radar) or find some way of flushing out the kidneys, which I can do by massively upping my intake of water.

The recommended daily hydration regimen is 8 cups (1/2 gallon, or 64 ounces). Most people don't quite get this (and to be fair, it is a pretty arbitrary standard), and feel a little water-logged if they go for it. I've found experimentally that my ideal water throughput for symptomatic management ranges from 1.2 to 2 gallons per day, or two to four times the standard recommendation. Putting upwards of ten pounds of water through my body a day is not without its inconveniences, but the results are astounding: I have been able to consistently function, retain focus throughout the day, and consistently move without a broad distribution of muscles slowly constricting and seizing (I ignored a lot of this before, it had settled in so gradually). In those cases where the toxicity increases beyond my ability to handle it with water I can supplement with bicarbonate (sodium-bicarbonate in this case, or common baking soda - unfortunately you can't get bicarb without it being bound to another agent, and sodium in the related concentrations is the least threatening) to give my kidneys a boost. If that's still not enough it's time to take it easy and sleep it off.

And that's it - I can do 2 miles on the treadmill in the morning and become warm and flush in the face, with a good steady and heavy sweat instead of my previous experience of becoming pale, pupils dilating, and pulse threatening to become erratic as I felt increasingly weak and physically disconnected. I'm invigorated and capable, and with any luck will be able to set a good precedent for weight management. I've been doing this for a month now and am still amazed at the clarity of thought, presence of mind, and restored function of an engaged subconscious (especially for automatic mental reminders and short-term background processing). There are things I'll clearly never be able to do again, but I'm more OK with that now than ever before - the burden is easier to carry, and I'm relieved of the expectation of rising to a super-human standard.

None of this would have been possible except for:

1. Mental faculties adequate enough to both use as a means of trade in providing for self and family (and thus avoiding reliance on otherwise impossible physical performance), and sufficient to withstand the stupefying effects long enough to be effective in that employ.
   
2. An opportunity to both rest and subsequently push myself in opposite extremes: without the intensity of work and schedule I never would have been able to consistently reproduce the final few symptoms of the puzzle. The new job, attempted relocation, and first and second apartments created the perfect environments.
3. The urgency of investigation based on family need: I owed it to them not to screw things up, admitted I had a problem I couldn't solve on my own, and persisted in seeking out the care and advice I needed.
4. The inability of our beautiful home to sell despite our efforts to undercut the market.
5. That final appointment perfectly rescheduled (and only because I made a mistake that landed me on a waiting list) to coincide with my dramatically foreshortened stay in Washington. In fact, the foreshortening came on the heels of our most recent and drastic attempt to sell the house - almost as though we forced the hand of the cosmos, since we weren't really supposed to move but the evaluations still needed to happen.
6. All the little steps over the years that gave me hope.

In the end it seems as though the whole experience with Amazon was perfectly designed for the investigation into and subsequent resolution of health that was continuing its frustrating decline (every minor gain was eventually curtailed). It was very costly: the time apart from family is not something I would otherwise have chosen. That's being quickly made up for though - before the severance ran out I managed to land a good gig locally, with a favorable commute, frequent opportunities to work remotely (time with the family and care for my health), a nice level of influence and corresponding title, and chances for some stability. Certainly more humble in many ways than the work at Amazon, but arguably far better suited for both me and the family.

I'm being taken care of; it feels like stepping from shadow into warm sunshine. Health is finitely understood and controlled for the first time in my life. I have a much better idea of relative costs now and know what I will and will not do at the expense of closeness to my family, and most of it falls into the "will not" category. I learned a tremendous amount (the kind of education you really can't pay for) and am better prepared for the advancement of my career than ever before; and I can start living again.

Thank you.