Keeping It Simple

Blog

  • The Scaffold Project

    We have a water tank in our yard, to store and purify water from the well. It uses a filter that bubbles ozone through the water, which causes minerals to precipitate out, and disinfects the water. It’s a very cost-efffective way to have clean, clear water. It just means every month or so I have to clean the filter.

    Cleaning the filter has meant climbing up a ladder to get on top of the tank, open the hatch, pull out the filter on its chain, and hang the filter over the outside of the tank, where I can hose it off.

    My goal was to build a permanent scaffold so I could access the water tank filter without using the unsecured ladder.

    I designed a 3D model for the scaffold, showing a sturdy raised platform adjacent to the water tank, with a railing and a fixed ship’s ladder. This seemed within my skill to build.

    I bought lumber and hardware for the project.

    I got my friends Kevin and Jay to help. We had such a good time building my footbridge last summer that they wanted to construct something together again. This seemed like a good sized project, so we scheduled it when they would be out in California for this year’s summer vacation.

    The first step was to clear the ground where the scaffold would be built, and dig some holes for concrete footings. The posts would form a 4 ft square. The holes didn’t have to be terribly deep, just about 18 inches.

    Six bags of concrete seemed like enough to get started. I also got some pieces of #3 rebar to help hold the concrete together, and J-bolts to embed in the concrete so post bases could be fastened down.

    Then we mixed up the concrete in a tub and started shoveling it in. We used fast-setting concrete, which certainly does start hardening quickly!

    It turns out six bags only filled three of the holes, so we searched my garage and found some older bags of concrete. But they seemed like they had hardened. When you store concrete too long, the moisture in the air can cause them to cure, at least on the outside. But I found a YouTube video about how to loosen and use hardened bags of concrete. This worked! We were able to recover enough concrete to fill the fourth hole.

    Now we had four footings with threaded J-bolts set in them.

    The post bases were fastened to the J-bolts, using strings to make sure they were placed within a 48 inch square. I didn’t have a socket driver large enough for these nuts, so we ended up using a wrench to tighten them. It was a little awkward, but we only had to do four of these, so we decided to grin and bear it.

    Very quickly we positioned the 4×4 posts on top of the post bases. We made sure each post was plumb using a post level. Thanks to Jan for making a new piece of elastic for my post level, since the original rubber band that came with it had broken.

    In some cases, the post base was not quite level, so we shaved a bit off of the bottom of the post until it rested on all four corners.

    We braced the posts with 2×3 boards fastened to stakes in the ground, so they’d remain plumb while we attached the joists.

    We cut the tops of the posts so they were level, and fastened edge joists on top.

    The joists were held firmly in place with special hardware. The weight-bearing is directly onto the posts. The hardware just holds the corner together.

    Near the tank, the joists were cantilevered, so they get closer to the water tank. This required a different type of hardware.

    The whole structure was given a lot more rigidity by adding a diagonal member on each side. These made a huge difference. The frame was much more solid once these were attached.

    The trickiest cuts we made were bevel cuts on the ends of the joists, so the cantilevered end kind of followed the curve of the tank. We had to toenail some screws, because there isn’t enough space next to the tank to drive screws from the outside. We considered using joist hangers, but I only had joist hangers for perpendicular joists.

    The joists were 16″ on center to support the decking. We used joist hangers.

    After we put joist tape on the tops of the boards, we moved on to decking. I used an inexpensive composite decking board, whatever they had in stock at Home Depot. 2 1/2″ composite decking screws go right into this material, so we made quick progress.

    We found it helpful to use a light hammer to tap the point of a screw into the decking, so it would stay put as we used the impact driver.

    We got into a good rhythm, raising a decking board to be measured, then bringing it down to the sawhorse to be cut, then fastening the properly cut board in place.

    The Craftsman Portable Craft Center again proves its worth!

    As we added more decking, we could fully climb up and work on the platform to finish the rest of it. Here’s another angle, showing a bit of the joist tape.

    Finally we completed the platform! It took about 12 hours of work, over three days. Plenty of time around the project for vacationing.

    That was as far as we got while Kevin and Jay were visiting. I’ll continue to work on the railings and building a permanent ladder.

  • The Shed Porch Project

    We have a she-shed in our back yard. It was built in the 1980’s by my wife’s uncle for her aunt to use as a place to work on sewing projects. We’ve used it for storage of unused furniture, books, clothes, and dishware.

    The shed had a small porch in front of the door. It was evidently built from leftover lumber, which was untreated, unpainted, and without any foundation or posts. Just perched on top of a couple of loose cinder blocks.

    Each year as I mowed the tall grass around the shed, I saw that the porch looks a little more sad than the previous year. The nail heads had retreated a little further from the boards. The cinder blocks looked a little more jury-rigged. The screen door was scraping on the deck of the porch a little closer. One by one, the steps were rotting through.

    It was way past time to replace this porch!

    Tearout and Groundwork

    Removing the old porch only took a few minutes. The nails were barely holding it together anyway. I took the time to pull all the nails and put them in metal recycle. Some of the boards felt as light as styrofoam. I found more cinderblocks buried in the soil.

    I used CraigsList’s Free Stuff to give away the old wood. I don’t know what the person was going to use the boards for. Garden bed edging? Concrete forms? Even if it was for a bonfire, I didn’t mind. By giving away this old wood, I didn’t have to haul it to the dump.

    The shed siding behind the porch ledger was bare wood. I covered that up using some leftover house paint.

    The Foundation

    I decided to rebuild the porch in its original size: four feet by six feet, with a couple of steps on the left side. So I was just doing a “repair” without changing the design. I intended to make it a lot stronger than it was before, but otherwise the same.

    I had some surplus concrete deck blocks that had been loitering around our property for a long time. I finally got to use some of them. This was easier than pouring concrete piers or a slab.

    I used galvanized roofing nails to connect post bases to the wood on top of the deck blocks.

    The Ledger

    I wanted the ledger to be level, even though I knew the shed is actually far from level. So I put a couple of 12in stakes in the ground and balanced the board on the tops of the stakes. I could then tap the stakes by small increments until the board was level. I also made sure there was space between the ledger and the door trim to fit one of the decking boards.

    I fastened the ledger to the shed joists using six 4in GRK structural screws. These are plenty strong! They are rated at 840lbs. of shear strength each. A load that exceeded the capacity of six of these screws would be too heavy to store in this shed anyway.

    The Framing

    Now it was time to attach the sides of the frame. I clamped it to the ledger to make it flush. Then I used my stake-in-the-ground method to hold it level while I screwed it to the end of the ledger. Screws will hold the frame together over the years much better than nails.

    I cut short pieces of 4×4 for the posts, from some pressure-treated posts we had left over from previous projects. I cut the posts to the right length before putting them in place. I also cut a 5 degree slope on the top of the post to let water run off.

    I screwed the frame to the posts.

    I tied the frame together with the front 2×8 board. It fit perfectly between the side boards. The frame was still level and square. I balanced the board on two stakes again, while I fastened it to the side frame pieces.

    I cut posts like the others, placed them in the corners, and screwed into them from two sides. Maybe this was overkill?

    I added interior joists, spaced every 16in. I used some extra 2×8 lumber I had. I used clamps and a wooden stake to hold the joist flush with the rim joist, while I fastened it with more screws.

    DeWalt, if you want to send me any free swag for the product placement, I will not object.

    The frame of the porch was finished! I used a total of 44 structural screws. If the goal was to make the new porch stronger than the old porch, then mission accomplished!

    The Steps

    I’ve never made stair stringers, but the process seems pretty straightforward. This seemed like a good project to practice, since this porch only needed a few steps. I had some 2×12 boards long enough to make stringers left over from past projects.

    I tried making a diagram for the stringer using BlockLayer.com. But it was just as easy to make a template myself on some cardboard.

    I cut a 2×12 to make one stringer according to the diagram using the circular saw, and a little hand-sawing to finish the corners. Then I used the first stringer as the template for three more.

    I decided to use four 12in pavers as a footing for the stringers. Not as solid as it would have been if I poured a concrete pad, but a lot easier, and I tried to keep in mind this is just a shed, and what I was building was still much better than the old porch that was supported only by haphazard cinder blocks.

    Eventually I was able to fasten the stringers to the frame using joist hangers. I cheated the leftmost stringer away from the shed wall a couple of inches, so I could fasten both sides of its joist hanger to the frame.

    The stairs needed risers, so I cut some pieces of plywood and screwed them on. I also back-filled some soil around the pavers. This will help hold them in place as the soil compacts.

    Preparing the Decking

    It’s important to prepare new lumber to help it absorb the wood stain evenly. First, let the moisture and lumber treatments dry out for at least 30 days. Then, clean the wood with oxalic acid to open up the pores.

    I bought the decking boards two months earlier and sanded them. I guessed that two months was enough time, and the wood was dry enough to stain.

    I brushed the sawdust off of the decking planks and the stringers, then treated them with Behr All-in-One Wood and Deck Cleaner using a pump sprayer. Then after the wood dried for a day, I applied #1 Deck Premium Semi-Transparent Wood Stain for Decks, Fences, & Siding (Light Walnut). This was my first project involving stain, and I’m sure I did a pretty amateur job. But my primary goal was to protect the wood, not to make it pretty.

    Installing the Decking

    I covered the top of the joists and frame with 1.5in joist tape, to protect them from any standing water. This was probably overkill, because rain in California is so infrequent, but it’s good practice.

    To fasten the boards to the frame, I started from the stair stringer on the left side, and worked right. I looked around for an object about 1/8in thick that I could use to space the boards. I found a pair of leftover plastic things in our hardware drawer. I don’t remember what these were from, but they seem to fit, and they have an angle so they won’t fall through.

    I fastened all 18 of the boards, and discovered I made a mistake. I was two boards short on the end. I’ll have to order more boards, sand, and stain them. I left a scrap board over the gap at the end, so no one puts their foot in it, and the joist tape won’t peel up.

    Finally, there was easy access to enter the shed.

    Trim Boards

    I wanted to add some trim to cover the joists and the ends of the decking boards. I cut some pieces of 1×6 to the right size, then primed and painted them to match the Tudor Brown trim on the shed.

    Once the paint was dry, it was a quick task to fasten these trim boards to the porch.

    Finished Porch

    The new porch was solid and looked a lot better than the old one. I’m sure there are some things I could have done better, but it was just a shed, and I think this was a successful project.

    What I Learned

    • Decking boards are expensive no matter how one tries to economize. I thought I could reduce the cost by buying cheap pressure-treated wood, but by the time I added up all the costs and time for finishing the boards, I would have done better to just use Trex.
    • Buying cheap sight-unseen wood is a good way to get a bunch of unusable lumber with knots and even voids.
    • Digging and grading earth by hand is very time-consuming, tiring work.
    • It’s a lot harder than you might think to recycle leftover materials. They’re almost never exactly what you need for your new project, or else the materials are too old and degraded to use.

    Costs

    Below is a list of what I spent on this project, roughly in order of using the items.

    Items with “–” for the cost are materials that I had on hand already.

    ItemUnit CostQuantityTotal Cost incl. Tax
    Housepaint (primer and red)—*—
    Flat chip brush (for paint and wood preserver)$1.971$2.16
    Galvanized Steel 1/2in hardware cloth, 1/2in mesh, 2ft x 25ft$40.822$89.60
    Galvanized staples—*—
    Concrete pavers—8—
    Concrete deck blocks—4—
    Galvanized post base 4×4$7.294$32.00
    2×8 pressure-treated board, 12′ (cut in half)$13.001$14.27
    2×8 pressure-treated board, 8ft (cut in half)$19.371$21.26
    GRK structural screws, 5/16in x 4in (box of 45)$26.871$29.49
    4×4 pressure-treated posts, about 12in each—4—
    2×8 joists, 6ft—2—
    2×12 board, 36in (stringers)—4—
    Galvanized joist hanger, 2×6$1.483$4.84
    Galvanized joist hanger, 2×6 concealed$2.981$3.25
    Pressure-treated plywood, 6in x 4ft (stair risers)—3—
    Butyl joist tape 1-5/8in x 50ft (2 rolls)$24.631$26.85
    5/4in x 6in planks, 4ft (box of 3 boards)$43.996$263.94
    0.75in x 7.25in board, 8ft (for trim)$17.082$37.23
    Premium all-in-one wood cleaner, 1 gal$12.211$13.31
    Premium semi-transparent wood stain, 1gal$35.991$39.23
    Flat paint brush, 4in (for stain)$10.281$11.21
    Deck screws, #9, 2in (1lb box)$11.472$25.00
    Additional 5/4in x 6in planks, 4ft (box of 3 boards)$43.991$48.28
    TOTAL$661.92

    Tools I used that I already had on hand:

    • Impact driver
    • Circular saw
    • Belt sander
    • Speed square
    • Spirit level
    • Measuring tape
    • Plumb bob & string
    • Trigger clamps
    • Framing hammer
    • Sledge hammer (used for compacting soil)
    • Shovel, hoe, dirt rake
    • Saw horses
    • Wood stakes, 12in
    • Pry bar, nail puller
    • Work knife
    • Pencil
    • Wood preserver
    • Gloves, eye protection, ear protection
    • Miscellaneous galvanized nails, screws, and washers

  • The Footbridge Project

    I have a creek that runs through my yard. Most of the year it’s dry (we live in California), but it has a short span of time in the winter when water flows after the rains. Even when it’s dry, it is inconvenient to cross, because it’s between 3 – 4 feet deep, and the bank is irregular.

    The dark line is the creek.

    Actually I used to have a makeshift bridge over this creek. Years ago, I threw down some scrap lumber I had around. But one day when I came out into the yard, I saw the whole bridge was gone! It wasn’t secured down, so in theory it could have floated away when the water level swelled. Or else I guess someone passing by needed some lumber? Anyway, I need a new bridge.

    The former bridge, now missing.

    So I made a plan to build a new footbridge over it. This time I needed to build one properly with concrete moorings. I’m an amateur with construction projects, so this would be a learning experience. I hoped it would inspire me to try other projects.

    Preparing the site

    The first step was to clear the weeds and brambles that have grown in the creek. It had gotten quite overgrown. The thickest growth was next to my neighbor’s driveway. Some of the weeds could be pulled out by hand, but the raspberry brambles are thorny, and that wannabe tree has grown large enough that I had to use lopping shears and finally an electric saw.

    It took several weeks and truckloads hauled to the green waste at our county landfill.

    $15 dumping fee per load

    I chose a location to build the bridge. This spot is in the shade of a couple of trees, and the bank was nearly the same height on both sides.

    I admit working in the shade is not an insignificant factor!

    I left the Vinca Major ground cover intact, because it’s nice when it blooms, and also because it’s tougher than it looks, so tearing it out would take a lot of work.

    Measuring the bridge

    I ran some string to mark out the bridge. I thought I could use 12-foot joists, but I found that was too short. The bank of the creek is pretty crumbly, and I wanted the moorings to be well back from the edge. So I measured out a 16-foot length.

    The width of the bridge would be about 5 feet, because I had some Trex composite boards I had kept when we rebuilt our deck. I planned to make these the planks of the bridge, so I could avoid buying new material.

    Part of the creek bank is shored up with scrap concrete installed by a previous owner of the property. This was another reason to set the end of the bridge a few feet back from the edge, because I can’t dig through the concrete.

    I learned how to make square corners with the 3-4-5 method, and how to tie string to stakes using a Larkshead knot. I enjoyed watching a video Using String Like a Pro, by the Essential Craftsman.

    I placed one of the Trex boards on the ground for reference, to make sure I was measuring the right width for the bridge.

    How much water?

    The creek is dry most of the time, but during rain it does get water. In January 2023 we had one of the biggest rainstorms we’ve ever had in this county. I got a photo of the water when the creek was full, and I was confident that the underside of the bridge (indicated by the string level) would still be above the level of water, and the moorings at each end of the bridge will be secure.

    Building the moorings

    I planned to have three joists spanning the creek. Trex is not known for its bearing capacity, so I wanted a center joist as well as one on either side. To fix these joists to the ground securely, I would connect them to concrete moorings.

    I got steel Strong-Ties to fix the joists to the moorings. I wanted these to be as straight as I can make them, so I built a temporary frame matching the width of the bridge (i.e. 5 feet, the length of a Trex board). These allowed me to keep the Strong-Ties straight as I put them into the concrete. I used construction screws so I could remove these frame pieces from the Strong-Ties once the concrete sets.

    The next task was to dig holes to put the concrete moorings into.

    Setbacks and getting help

    While digging the holes, I gave myself a muscle strain in my back. I’m not used to this kind of work. It’s going to take several weeks to heal and strengthen my back.

    So I recruited a couple of friends who were visiting me this summer. I’ve known Jay and Kevin for over 30 years, since we were at university together. They asked if there’s anything I want to do during their visit. I suggested half-joking, “do you want to help me build a bridge?” They laughed but said, “yeah, well, okay!”

    Wow, did they help! They made sure I didn’t lift anything so I didn’t re-injure my back. I ended up becoming the foreman, and provided a detailed plan, the tools, and the materials. They said they were there to provide the hands and muscles. But they gave plenty of good input to make every step work well.

    Concrete moorings

    We dug six holes for cardboard sonotubes, which we carefully leveled at the strings I had stretched. We mixed nine 60lb. bags of concrete and filled the tubes. We also pushed pieces of #3 rebar into the concrete to help prevent cracking.

    Then we pushed my Strong-Ties on frames into the concrete. These will remain level and straight while the concrete sets.

    After the concrete set for 24 hours, we removed the frames from the Strong-Ties.

    Joists

    We ordered some lumber from Home Depot: six pressure-treated boards, 2×8 inches by 16 feet. We lifted the boards into position, two in each Strong-Tie.

    I had viewed many YouTube videos recommending to “crown” the boards, orienting any bend in the board up so it flattens out when there is weight on it. But these boards were remarkably straight already, so it didn’t matter much which way we oriented them.

    We also lined them up carefully and measured from corner to corner to make sure the layout of the boards was still square.

    To make sure the tops were flush before we screwed the boards together, we used clamps and held them against a piece of scrap 2×4. Also more clamps to hold the boards tightly together while we put screws in.

    The boards were to be bolted to the Strong-Ties. It was a bit of a challenge to drill straight holes accurately enough to emerge through the hole in the other side of the Strong-Tie, but with practice we got pretty good at it.

    We got through 10 out of 12 bolts using an impact driver with a socket, but then my cheap old 1/4″ socket adapter broke! No problem! We couldn’t let that stop us. We tightened the last two bolts the old-fashioned way with wrenches.

    We now had three strong joists that were level, square, flush on top, and definitely not going anywhere.

    Blocks

    It’s recommended to add some transverse pieces to provide stability and keep the joists from twisting or leaning. These are called blocks. We used a seventh 2×8 board, cutting it into pieces one by one. We aren’t precise architects, so despite trying to make the framing square, each block was slightly different in length. We screwed them into the joists toenail-style. These pieces won’t be load-bearing.

    The bridge was now quite stable. Kevin tested it by walking across it like a balance-beam.

    Joist tape

    It’s a good idea to protect horizontal wood from any potential rainwater pooling on it, so we got some special adhesive tape used for deck projects and stuck it down on top. The 3 inch tape worked well, and we got it down the first try by going slowly and carefully. One 50 foot roll of the tape was perfect for three joists, with minimal waste.

    We didn’t tape the blocks, because they should be under the deck boards, and I assume risk of rot isn’t as important since they aren’t load-bearing. Also because we ran out of tape.

    The shiny tape makes the joists look classy. It’s almost a shame it’ll be covered up by the boards.

    Deck boards

    Now finally we can put the boards on top. We had just enough Trex boards to cover the length if we added a 1/2″ space between each board. That’s recommended anyway for drainage and allowing for expansion (not so much for Trex, but still not a bad idea).

    We got very focused on the work during this phase, and didn’t take many photos, but my wife got one of us working.

    It really paid off that we made the Strong-Ties level, and made each pair of the joists flush. This made it easy to attach the deck boards to the joists. Our worst fear is that the middle joist might be slightly too high, and the boards wouldn’t sit flat or be stable. But in our case, it was perfect.

    After we finished, we were amazed that my impact driver put in 48 coated screws, 12 bolts, and 192 deck screws on a single battery charge. Good job!

    Finished product

    Here’s how the bridge looks now. Later I will make a bit of a ramp with gravel so it’s not such a high step up, but it works, and it’s strong and solid!

    Final thoughts

    This turned out to be a surprisingly great experience to build this bridge with my best friends. They are easy-going, patient, practical, communicate well, and they focus on getting things done. By the end, we were very proud of having such a good result, but the time we got to spend working together will be a great memory.

    What’s the next project?

  • Will Voyager 1 and 2 come back as V’Ger?

    In Star Trek: The Motion Picture (1979), V’Ger was an immense alien spacecraft that threatened Earth.

    V’Ger turned out to be evolved form of Voyager 6, a fictional unmanned probe that in our reality has (so far) never been launched (apologies if that spoils the movie for anyone, but was more than 40 years ago, so I think the moratorium on spoilers has expired).

    My theory is that V’Ger is a Mary Sue character, in a story written by the Voyager probe itself.

    Think about it: the probe is just a bespoke wide-spectrum camera and transmitter, coasting at 0.005% of the speed of light, barely making it outside its home solar system. But somehow it was found by a race of benevolent sentient machines, and they decided it was so impressive and worthy that they upgraded it to a planet-sized vessel armed with plasma weapons.

    The probe then becomes infinitely powerful and intelligent. It excels at tasks that should be impossible for it. It upstages the protagonists in the story. It even becomes the sexiest, most beautiful character in the story, with whom other characters are compelled to fall in love.

    V’Ger has no flaws, and therefore no character arc describing its internal growth from a flawed character to an improved character. It has all the hallmarks of a Mary Sue in bad fan fiction.

    So it’s unlikely that any of the Voyager probes from our Earth will return as V’Ger, unless Voyager transmits its amateur fiction back to us.

  • Maximum Memory Warnings Are Rubbish

    Many people use MySQLTuner, as a guide to MySQL Server configuration values and they are alarmed when they see things like this:

    [!!] Maximum reached memory usage: 86.6G (138.16% of installed RAM)
[!!] Maximum possible memory usage: 5085.9G (8110.25% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory

    You should be aware that the “maximum memory” warning from MySQLTuner is rubbish. It is a misleading and impractical estimate, because it has no real chance of happening.

    The memory usage they base their calculation on has to do with buffers that are allocated conditionally, depending on certain query behavior. Not all queries allocate all those buffers. Even if they do allocate the buffers, they may not allocate the full size of the buffer.

    The chance that a given query will allocate all the possible buffers at their maximum size is remotely small.

    In addition, there might be up to max_connections clients connected, but it’s typical in a running system that not all connections are executing a query at the same time. On every MySQL Server I’ve supported, even if there are hundreds of clients connected, most of them are idle (i.e. not running a query). I would expect the number of threads running a query at any given moment to be 10-20 at most. The others are connected, but if you view SHOW PROCESSLIST, they only show “Sleep” as their current state. These are clearly not using query-specific buffers.

    The possibility that all threads on a MySQL Server will allocate all the possible query buffers at their maximum size at the same time is so unlikely that you should treat it as complete fiction.

    Because of these mistakes in calculating the theoretical maximum memory usage, I ignore those warnings from automatic tuning tools like MySQLTuner. The estimate of “maximum memory” given by MySQLTuner has been many times the size of RAM on every database server I have administered. You don’t need to be alarmed by this.

    So how should one estimate memory usage?

    By observation!

    Monitor the actual memory usage of your mysqld process by using tools like top or ps. Ideally you would have a continuous monitoring system to make graphs over time, so you could observe the trend.

    This is much more accurate than relying on MySQL Tuner or any other estimate. Those estimates are not taking into account your database traffic or activity.

  • Beginner Web Programming Projects

    Can someone suggest a web application project for beginners? What would be a good project? Should I follow a how to make a tutorial or figure it out on my own?

    This is a frequent question from new programmers. I wrote a post in 2020 about this. Below is a reproduction of my post. It was in response to a question specifically about learning PHP, but similar advice would be true for other programming languages.

    The best way to learn PHP, or really any other language, is by practicing writing code. This is probably echoed by many of the other answers.

    • Pick a simple practice project, and implement it. Then pick another one. You don’t have to create a beautiful website to practice. Create an ugly website that serves as your sandbox for trying out code.
    • Learn from mistakes. Try something different.
    • Go outside your comfort zone. Find a new group of functions in the manual and try out each one. For example: Arrays or Strings or SPL.
    • Learn about PHP: The Right Way.
    • Learn about Laravel, the The PHP Framework For Web Artisans. Really. The days of arguing over the best PHP framework are over.
    • Learn about security (see OWASP).
    • Learn about testing code (PHPUnit, the PHP Testing Framework).
    • Subscribe to PHP blogs like Planet PHP. Attend PHP community users’ groups or PHP Conferences around the world.
    • Read other PHP code. Download WordPress or MediaWiki, both widely used and mature PHP projects, and study the code. What’s good about it? What’s lousy about it? There are bits of code in any project that are gems, and some that are garbage.

    Finally, there’s a secret way to learn any skill better than you can learn it on your own: teach it to someone else.

    As for a suggestion for a project to practice, I would recommend you develop a blog application similar to WordPress.

    1. It’s very simple at first: just a database table that stores blog posts by date, one PHP script to view them, and one PHP script to author them.
    2. Add authentication, so only the blog owner can write posts. Be sure to read You’re Probably Storing Passwords Incorrectly.
    3. Add tags, so the blog owner can categorize posts and users can view posts in that category. You’ll learn what a many-to-many relationship is.
    4. Add comments so readers can post too.
    5. Add captcha support or logins for users. Integrate with OpenID, Facebook, Google authentication APIs. Learn OAuth2.
    6. Add a text search function. Learn about a fulltext search engine like Sphinx Search.
    7. Add some kind of email notification or RSS feed, so users can be notified when a new post appears.
    8. Refactor your comments system to use Disqus, as a way to embed a web service into your application.
    9. Refactor the whole app to an MVC framework, to learn what it takes to do a major overhaul of your code without breaking things.
    10. Write unit tests for your classes that you wrote for the MVC implementation.

    I would recommend that you do not follow a tutorial, and instead try to design the application on your own. It will take longer, but I guarantee you’ll learn more about coding. Anyone can follow a tutorial and type in the steps, without knowing what’s happening. If you do not use a tutorial, you’ll have to think harder about how to solve the problems that come up. This is a necessary skill if you want to be a programmer.

    But of course you need to read documentation or how-to articles on some of the individual pieces I included in the list above.

  • Why is Software So Complex?

    Q: Why is most modern software so mindbogglingly complex, with multiple layers of abstraction stacked on each other? Why do they not make simple, efficient software like they used to earlier?

    This question was asked on Quora recently. I answered that it is due to a few major reasons:

    1. Code Maintenance

    There’s an old humor article that has circulated online for many years, titled, “If Architects Had to Work Like Programmers.” It’s written as if it’s a letter from a homebuyer who wants an architect to build a house. Here’s an excerpt:

    “Please design and build me a house. I am not sure of what I need, you should use your discretion. My house should have between two and forty-five bedrooms. Just make sure the plans are such that the bedrooms can be easily added or deleted. When you bring the blueprints to me, I will make the final decision of what I want. Also bring me the cost breakdown for each configuration so I can arbitrarily pick one.”

    This is humorous because it sounds like the way programmers are given their software requirements. Because software can be modified after it is created, the employer assumes it’s easy to do that, and that they don’t need to be specific about what they want.

    People in the software development field over many years have tried their best to accommodate this, by creating more and more abstractions so that the pieces of software can be altered, combined, upgraded, or swapped out more easily.

    The employer wants this because it enables them to get software on an affordable schedule, without forcing the employer to write detailed specifications that they probably don’t know in advance anyway.

    The programmer wants this because they want to remain employed.

    2. Code Reusability

    A good way to increase code quality without increasing the schedule is to write less bespoke code, but instead use more code that has been written and tested well prior to your project. We call these libraries, frameworks, templates, or code generators.

    You’ve probably used Lego toys, where you can build elaborate models using simple reusable bricks or other specialty pieces. You can build practically anything by using enough bricks. There are also some specialized shapes, and lots of guides showing you how to combine them to build the desired models.

    Image: Queen Mary model in Lego.

    It’s a similar concept to code reusability. Software development then becomes an activity of learning all the different pieces and ways to use them together.

    The code is reusable because of abstractions. Like the Lego pieces that use standard dimensions and connecting buttons so they can be fastened to other pieces.

    3. Features, Features, Features

    I once developed an app for a manager who was very decision-challenged. Every time I would ask him, “do you want the app to behave this way or that way?” I was often asking about two alternatives that were mutually exclusive. For example, do you want the report to arrange categories of data in rows or in columns?

    He would always answer, “both.” He didn’t know how to choose, and he was afraid of making the wrong choice. So he asked me to implement both alternatives, and make the software configurable. He wanted to keep his options to change his mind later as often as he wanted to.

    This at least doubled the work to implement the code, and doubled the testing needed to assure it works.

    But it was worse — every time he said, “both” this doubled the number of test cases, because I had to assure that a new feature worked with every combination of alternatives for the past features.

    Programmers can’t say “no” when their employers want some features. They can say, “okay, but here’s what it’ll cost in time and money, do you still want it?”

  • How do Software Engineers cope with stress?

    The typical sources of stress for software engineers are not caused by technology. They’re caused by managers and projects.

    Work tasks are not described clearly.

    If I’m given unclear work requirements, I ask for more details. I make it clear that I can’t give an estimate for the cost or the time of completion until I know the full scope of work. In a professional software engineering environment, a significant amount of time should be spent estimating the work based on complexity of the task. You can get yourself into a stressful obligation if you agree to a deadline before knowing the requirements of the project.

    Be careful about this. Managers often insist that the software engineer decide on the estimate much too early, and then they use that against the engineer later, saying it was an estimate that the engineer had made, so they can’t claim it was imposed on them.

    When you are asked for an estimate before you know the scope of work, remember to use this standard response: “I’ll have to get back to you.”

    Schedule is too aggressive. Deadlines are impossible.

    At one job I joined, I was hired to get a software project back on track after it had fallen behind schedule and the previous team lead had quit. I knew generally what the project was, but I didn’t know how much was done and how much still needed to be done. On my first day, a marketing person told me he wanted to present this software to customers at an annual conference, which was happening in two weeks. He wanted me to promise to finish the software by that time. I told him I would need two weeks just to learn the current state of the project and make an estimate for the completion. It was a little bit stressful to tell him no, but it would have been much worse to make a promise and then fail to deliver.

    Finishing on schedule requires long work hours and few breaks.

    Don’t fall into the trap of letting management bully you into working until you are exhausted. If you do that, you will make more mistakes, and your code will need to be scrapped and written over. You are not a machine — and even machines need time for maintenance, cleaning, repair, etc. If you keep yourself healthy and your mind fresh, you will be able to concentrate better and produce better quality work. You will have a better chance of finishing the work on time.

    New requirements are added late in the project, but the workers are held to the original schedule.

    When they want to add more features, tell them you’ll evaluate the new requirements to see if they can be added with minimal interruption to the schedule. Do your best and make an honest effort to do that. But sometimes the new feature is requires major changes to the current code design, which is partially implemented already.

    Approach the product manager and let them know this. Present to them the following options:

    • Postpone their new feature idea until “phase 2” (that is, a future revision of the software).
    • Cut some other requirements from the current project that are time-consuming and not yet implemented.
    • Make a compromise to reduce the complexity from one or more features, to make them take less time to implement.
    • Extend the project deadline to give enough time for the extra features.

    If they still demand that they want all the features and no change to the schedule, that’s not realistic. Politely tell them that they need to choose one of the options or they will be disappointed. This makes the tough choice their responsibility, which reduces your stress.

    Unscheduled work and alerts interrupt and spoil concentration.

    This is a great source of stress because it’s difficult to resume work that requires concentration after an interruption. This has been studied a lot. It’s not just the time it takes to do the unscheduled task. It also takes time to shift your focus between tasks. If this happens several times per day, you can lose all your productivity for the whole day.

    If software engineers are expected to be oncall or to help with unscheduled analysis or troubleshooting or technical support, then they should make it clear that any schedule estimates are in unpredictable. Your stress comes from uncertainty that you can be productive enough to meet your deadline. You can mitigate this stress by insisting that the deadline must be extended every time you are interrupted.

    Software engineers are expected to understand and be productive with any type of technology with no time for training.

    The best way to cope with this is to fib a little bit and add some time to every estimate, to allow for research, self-training, debugging, and getting answers from technical support. It’s unfortunately a reality that management can’t justify budget for training time, if they are already paying high salaries to software engineers. So you have to include the necessary training time with engineering estimates.

    One way to hide training as engineering is to schedule part of the project to implement a “prototype” or a “proof of concept.” These basically mean you’re going to be practicing, and the result will be an unoptimized implementation, intended to be scrapped and redone before the final deadline.

  • The Case Against The Case Against Auto Increment in MySQL

    In the Pythian blog today, John Schulz writes The Case Against Auto Increment In MySQL, but his blog contains some misunderstandings about MySQL, and makes some bad conclusions.

    The Concerns are Based on Bad Assumptions

    In his blog, Schulz describes several concerns about using auto-increment primary keys.

    Primary Key Access

    “…when access is made by a secondary index, first the secondary index B-Tree must be traversed and then the primary key index must be traversed.”

    This is true. If your query looks up rows by a secondary key, InnoDB does that lookup, finds the associated primary key value, then does another lookup of the primary key value in the clustered index (i.e. the table). But if your query looks up rows by the table’s primary key, the first step is skipped.

    But this has nothing to do with using an auto-inc mechanism to generate primary key values. The way secondary key lookups work is still true if we use another method to create primary key values, such as a UUID or a natural key. This is not an argument against auto-inc.

    A mitigating feature of InnoDB is that it caches frequently-requested values from secondary keys in the Adaptive Hash Index, which skips the double-lookup overhead. Depending on how likely your application requests the same values repeatedly from a secondary index, this can help.

    This concern is also irrelevant for queries that do lookup data by primary key. Whether you generate the primary key as auto-inc or not, it’s common for applications to search for data by primary key.

    Scalability of Auto-Inc Locks

    The blog claims:

    “When an insert is performed on a table with an auto increment key table level lock is placed on the table for inserts only until the transaction in which the insert is performed commits. When auto-commit is on, this lock lasts for a very short time. If the application is using manual commits, the lock will be maintained for a longer period.”

    This is simply incorrect. The auto-inc lock are not held by the transaction until it commits. That’s the behavior of row locks. An auto-inc lock is released immediately after a value is generated.

    See AUTO_INCREMENT Handling in InnoDB for a full explanation of this.

    You can demo this for yourself:

    1. Open two MySQL clients in separate windows (e.g. Terminal windows running the mysql CLI). In each window, begin a transaction, which disables autocommit.
    2. Insert into a table with an auto-inc primary key in the first window, but do not commit yet.
    3. Insert into the same table in the second window. Observe that the second insert succeeds immediately, with its own new auto-inc value, without waiting for the first session to commit.

    This demonstrates that an auto-inc lock is not held for the duration of a transaction.

    If your database has such a high rate of concurrent inserts that the auto-inc lock is a significant bottleneck, you need to split writes over multiple MySQL instances, or else consider using a different technology for data ingestion. For example, a message queue like RabbitMQ or ActiveMQ, or a data stream like Logstash or Kafka. Not every type of workload is best solved with an RDBMS.

    Key Conflicts After a Replication Failure

    The scenario is that an OS or hardware failure causes a MySQL replication master to crash before its binary logs have been copied fully to its replica, and then the applications begin writing new data to the replica.

    “In a situation like this failing over to the slave will result in new rows going into auto increment tables using the same increment values used by the previous master.”

    Yes, there’s a small chance that when using asynchronous replication, you might be unlucky enough to experience a catastrophic server failure in the split-second between a binary log write and the replica downloading that portion of the binary log.

    This is a legitimate concern, but it has nothing to do with auto-inc primary keys. You could have the same risk of creating duplicate values in any other column with a unique constraint. You could have a risk of orphaned rows due to referential integrity violations.

    This risk can be mitigated by using Semi-Synchronous Replication. With this option, no transaction can be committed on the master until at least one semi-sync replica has received the binary log for that transaction. Even if the master instance suffers a catastrophic power loss and goes down, you have assurance that every transaction committed was also received by at least one semi-sync replica instance.

    The above risk only occurs during OS or hardware crashes. See Crash-safe MySQL Replication: A Visual Guide for good advice about ensuring against data loss if the MySQL Server process aborts for some other reason.

    Key Duplication Among Shards

    This concern supposes that if you use a sharded architecture, splitting your data over multiple MySQL instances…

    “…you will quickly find that the unique keys you get from auto-increment aren’t unique anymore.”

    This supposes that a table on a given shard generates a series of monotonically increasing auto-inc values, not knowing that the same series of values are also being generated on its sister shards.

    The solution to this concern is to configure the shards to generate values offset from each other (this was quickly pointed out by Rick James in a comment on the blog).

    Set the MySQL option auto_increment_increment to the number of shards, and set auto_increment_offset to the respective shard number on each instance. With this arrangement, each shard won’t generate values generated by the other shards.

    The Proposed Solutions Have Their Own Problems

    Schulz recommends alternatives to using auto-incremented primary keys.

    Natural Key

    A natural key is one that is part of the business-related data you’re storing.

    “Examples of Natural keys are National Identity numbers, State or Province identity number, timestamp, postal address, phone number, email address etc.”

    There are problems with using a natural key as the primary key:

    • It might be hard to find a column or set of columns that is guaranteed to be unique and non-null, and is a candidate key for the table. For example, a national identity number isn’t a good choice, because a person who isn’t a citizen won’t have one.
    • Business requirements change regularly, so the columns that once were unique and non-null might not remain so.

    Natural keys are most useful in tables that seldom change, for example a lookup table.

    Natural Modified Key

    The suggestion is to add a timestamp or a counter column to a natural primary key for cases when natural primary key column can’t be assumed to be unique. By definition, this means the supposed natural key is not a candidate key for the table.

    It’s not unusual for a table to have no good choice of columns that can be assured to be unique. In these cases, a pseudokey based on an auto-inc mechanism is a standard solution.

    UUID

    The suggestion is to use a globally unique UUID as a primary key.

    “To save space and minimize the impact on index block consumption UUIDs should be stored as binary(16) values instead of the Char(36) form they are usually seen.”

    Even when stored as binary, a UUID requires more space than an INT (4 bytes) or BIGINT (8 bytes). Keep in mind that primary key values are internally appended to every secondary index, so it’s not merely double the space, it scales up with the number of indexes your tables have. This doesn’t sound like you’re saving space.

    “…they do not require table locks…”

    It’s worse than that. MySQL’s UUID() function is implemented with an internal global lock, instead of a table lock. It’s very brief of course, but in theory you can get contention. This contention might even be worse than the auto-inc table lock, because the same global lock is needed by all tables on the MySQL instance for which you generate a UUID.

    The fact that UUID doesn’t insert in key order is actually a big deal for insert performance under high load. This can be mitigated by reformatting the UUID as described by Karthik Appigatla in 2014, but this is not default behavior and it’s not widely used.

    Random insert order also leads to fragmentation in the clustered index and less locality of pages in the buffer pool. Michael Coburn showed this in an excellent blog post in 2015: Illustrating Primary Key models in InnoDB and their impact on disk usage.

    MySQL tables have no way to generate a UUID automatically. You would have to write a trigger to do this, or more application code. You would have to write a separate trigger for every table that uses a UUID. This is a lot more work than simply declaring your primary key column with the AUTO_INCREMENT option.

    UUIDs have their uses. They are most useful for distributed applications that need to generate globally unique values, without central coordination. Aside from that use, UUIDs are more trouble than they’re worth.

    Custom Key Generator

    The suggestion is to use some other software as a central generator for creating primary key values atomically and without duplicates. This can work, but it’s operational complexity and overhead to run another software service just for generating id values. It’s a single point of failure. How will you explain to your CIO that the database is running fine, but the applications still can’t load any data because the Snowflake server went down?

    Besides, other customer key generators are unlikely to have the same ACID reliability as InnoDB. If your key-generator service ever restarts, how do ensure it has not lost its place in the sequence of values it generates?

    Conclusion

    Like all software, using MySQL’s auto-increment feature requires some expertise and understanding to be used in the best way. Every feature has appropriate uses, as well as some edge cases where we should use a different mechanism.

    But it’s bad advice to conclude from this that we need to avoid using the feature altogether. For the majority of cases, it’s a simple, effective, and efficient solution.

  • The Private Option

    There’s a famous case of a fumbled rollout of a website: HealthCare.gov, the federal health insurance exchange used by independent insurance customers in about two-thirds of states in the USA.

    These days, the an updated version of HealthCare.gov functions fine, so you’re wondering what the hubbub was about when it was launched.

    Poor Debut

    Proponents said that a slow rollout is not unexpected. People who managed the health insurance exchange in Massachusetts that served as the model for the Affordable Care Act say that the same initial bugs and slow adoption affected their program too.

    The site has performance and scalability problems, has an overly complex user experience, and sometimes calculates wrong answers. The result is that of the 100,000 people who signed up for independent health insurance after October 1 2013, fewer than 27,000 used the federal exchange.

    Why Did it Fail?

    HealthCare.gov had a major obstacle: they had to handle several times the originally anticipated demand. The original plan was for each US state to implement their own health insurance exchange, to serve people in their respective state, and HealthCare.gov would handle those who couldn’t. It was assumed that only a small minority of states would rely on HealthCare.gov, and these would be the states with smaller populations. As it turned out, a majority of states refused to implement their own exchange web sites. In December 2012, when the states were required to have blueprints describing their solution, reportedly 25 states didn’t meet that deadline. 
    By the time of the rollout of the ACA, only 14 states were signing people up using their own state-run exchange, whereas the rest of the states–more than two-thirds–were relying on the federal exchange. These include some of the highest population states like Texas and Florida, and 20 states who had taken federal money to plan their state exchanges, but ultimately also relied on the federal exchange.
    The Private Option

    A few young programmers created an alternative web site they call HealthSherpa.com in their spare time, after the ACA debut on October 1 2013. Their web site is a prototype effort to make a more streamlined portal for people to find the health insurance plans they’re eligible for. It seems to work, and it’s very fast. It uses raw data that is accessible publicly from the federal government.

    It’s a valid question then: why didn’t the federal government—or any of the states—employ a small team of web experts to throw together such a site for a fraction of the cost?

    HealthSherpa.com doesn’t have all the functions that HealthCare.gov is supposed to. It doesn’t do credit checks, it doesn’t actually even sign anyone up for health care. It just allows consumers to find the data that pertains to them, and then it links to the websites for the respective insurance carriers. And HealthSherpa.com doesn’t create the data—it might be true that part of the effort behind HealthCare.gov has created the raw data that HealthSherpa.com uses.

    Also, HealthSherpa.com isn’t (yet) serving tens of millions of users, as HealthCare.gov is supposed to do. I work for Percona, a company that offers consulting and support for database operations, which is just one aspect of web site scalability. Scalability for a web site is complex, much more difficult than most people appreciate.

    But it’s worth noting that even with these limitations, there’s a pretty big difference between a three guys throwing together a working website in a few days, versus major federal IT contractor CGI Federal spending $174 million since they announced winning the contract in December 2011 (i.e. 22 months until their go-live deadline of October 1 2013), but they still failed to implement a site that could handle the demand.

    Conclusion

    So here’s some hindsight views on the HealthCare.gov project:

    • They should have anticipated the demand from all 50 states. This may have been over-engineering, since the intention was to serve only a minority. But they had no control over which states would agree to create their own exchanges, and every reason to think there would be political resistance to doing so.
    • They should have had a beta test period. No large-scale web site can handle the load of millions of users on its first day, not even sites implemented by major web experts like Google and Amazon. They restrict enrollment to a limited subset of their users, sometimes by invitation only. They leave enough time to work out the problems before going fully public.
    • They should have provided raw data only, not the whole web site. Let other entrepreneurs innovate the best way to search the data. Maybe someone would even create a Facebook game for selecting your insurance.
    • They should have set the deadline after scoping the project.