Voice of the DBA

Author: way0utwest

  • How Do You Experiment?

    One of the things that DevOps asks software developers to do is experiment. Try new ideas out, get feedback quickly, and then choose how to grow or stop your experiment. This is great for features, and it works well for application software.

    The general flow for this is to talk to customers, and then decide what to build. In some sense, this can work, but as I heard at the DOES Summit recently, if Henry Ford had asked his early on customers what to build, they’d have asked for a faster horse.

    Customers are limited by their current experience. This includes not only end users, but for us database pros, the developers that build software. When they want to experiment, they often need some backing from the database to store information and query it.

    If we want to help enable experiments, and allow our software to evolve, there are two things we need to deal with in experiments. One is schema changes, either through new data buckets in tables, or programmable objects, such as views, functions, and procedures Adding these, or removing them when experiments aren’t useful, can be cumbersome and difficult. It’s amazing how quickly we create dependencies and how slow we are to remove them.

    The other area is in ensuring that we properly or appropriately, handle resource usage. Do we go back and tune queries, or restructure the way that we’ve indexed items to ensure that our system works optimally? Some tuning can be done early, and should be, but some requires some feedback to understand query patterns or data loads.

    Today, I’m wondering how, or if, you experiment in database work. What works for you, or what doesn’t? Or do you hate the idea of experiments in the database world and want more specification up front? Let me know with a comment.

    Steve Jones

    Note: Podcasts are suspended for a week as I deal with the PASS Summit.

  • Daily Coping 13 Nov 2020

    I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here.

    Today’s tip is to be creative. Cook, draw, write, paint, make, or inspire.

    I like creativity. It’s a part of my job and my life, as I often need to think about how to write. I’m also a little creative in my life, as I cook most of the meals, fix things, play guitar, and sometimes actually build something interesting.

    This week, I’ve started going back through my guitar courses and practicing. Today, to relax during the PASS Virtual Summit, I’ve kept a guitar handy and just mindlessly played some riffs over and over as I watched some talks.

    I’m also hoping to get “Lovely Day” good enough that I can play it tonight for my wife. She loves that song.

  • Extreme SQL

    I have made a career of working with SQL and databases. Usually I’ve looked for interesting companies and people, but I’ve avoided extreme situations. For me, that often is very large, or very real time environments. I once declined a job for a 13TB database on SQL Server 6.5. My suspicion is that job would have taken me away from my young children and wife far too often.

    Facebook has a lot of users, and a lot of queries they run. With over 1billion daily users and hundreds of TBs of daily uploads, they really need strong databases. While they have multiple databases, and that includes SQL ones, they have struggled with analytic queries in the past. They started using Presto as a solution, an open source query engine for running analytic queries against data in different storage locations like RDBMSes or in something like Hive/HDFS. This sounds like what Polybase does for SQL Server.

    The problem with any engine at Facebook’s scale is the load. While they like Presto, they needed to make it work better. They initially built a caching layer that required users to build ETL jobs to load data into SSDs attached to the Presto cluster. However, they outgrew this and ended up turning to a distributed file system called Alluxio.

    The article linked above talks a bit about how this works, and allows users to query petabytes of data. Most of us have users that often don’t qualify their queries completely, so we expect that some queries that might need to scan 100GB end up reading much more until the users tune them appropriately.

    The thing I found interesting in here is that some queries were taking up to 10s, which users found unacceptable. The move to Alluxio gave them a 30-50% boost, which doesn’t sound like a lot. 5-7s over 10 isn’t a great savings to me. The reduction in reads, is impressive, which is good, but I wonder to what expect there is some management and tuning needed here to ensure the cache works well.

    I have no desire to work on these extreme systems, but I am glad someone does. The lessons and tricks learned here often trickle down to improve the daily performance many of us see in our smaller systems. I think that the Hyperscale work Microsoft is doing, and the Big Data Clusters, are fascinating ways of organizing SQL Server based servers, and some of that tech will likely trickle down and help us continue to improve our smaller systems’ performance over time.

    Steve Jones

    Note: Podcasts are suspended for a week as I deal with the PASS Summit.

  • Daily Coping 12 Nov 2020

    I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here.

    Today’s tip is to give yourself a boost. Try a new way of being physically active.

    My weekly activities are usually yoga, weight lifting, and swimming. I may use the rowing machine or bike, but those are less frequent. Horseback riding rarely, but skiing is usually something I start thinking about this time of year as a 1-2x /wk activity.

    In trying something new, I decided to add in some walking. I stopped running a few years ago, but with being sick this fall, I need something that works me, but isn’t too taxing. Something I can also include the dogs in, so walking it is.

    I’m adding in a walk a week, or hoping to, and trying to get some different type of movement, especially as I hope to travel in 2021 somewhere and do more walking/hiking.