Voice of the DBA

Author: way0utwest

Graphing Performance

We have a lot of different database platforms to choose from when building software. Most of us reading this are SQL Server users, and likely relationally biased. However, key-value stores, document databases, graph databases, and more are out there. If you work with developers that embrace change and new options, likely you’ve been asked about implementing some sort of NoSQL database instead of SQL Server for some project. Maybe you’ve even been asked to migrate away from SQL Server to an Open Source (OSS) NoSQL platform, with the lack of software cost being a factor.

I do think that there are some domains of problems that relational systems don’t handle well. Certainly at scales (data volume or rate), there are better ways to deal with some data sets in a less structured and tightly coupled way. We see that in the large scale web companies like Google, Twitter, Facebook, etc. If these companies had tried to build their entire system on a RDBMS platform, they would have struggled to grow, and maybe not even reached the size they are.

I’ve been reading and playing with the new graph capabilities of SQL Server 2017, trying to determine what I think of the concepts. Certainly large scale many-many relationships don’t seem to be a strength of relational databases and I’ve thought there are certain types of queries or data models that might be better handled by a graph database.

Then I ran across this report from a few researchers that examine how graph database compare to relational ones. After all, we’ve grown accustomed to using RDBMSs in many environments and situations. What better way to evaluate the performance of a specialized database than compare its performance in the problem domain its designed to solve to that of a general database platform.

The results are a little surprising. Even with a sub-optimal query language, I would have expected the graph database to perform better. Instead, relational seems to handle the reference graph workload better. Raw performance isn’t everything. Ease of development and ability to scale are important. There may be other considerations in your system as well, but I did find this to be an interesting paper.

We will see how the world of specialized databases handles real world workloads over time as more companies use them, but for now, I’d be skeptical of replacing an existing, working RDBMS with something unproven. I’d need to see a good POC that shows quite a bit of improvement across a variety of metrics, not just scalability.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.0MB) podcast or subscribe to the feed at iTunes and Libsyn.

August 28, 2017
The World of MySQL

I had two different people ping me on the same day with MySQL questions. One was a former co-worker, asking for some query help, and one was from the Boy Scouts of America, also asking for some query help for a few reports. Strange that both requests came on the same day.

I didn’t have a MySQL install on the latest incarnation of my desktop or laptop. I did pop MySQL Workbench on a laptop last year, playing with Azure deployments, but no server. I deployed a MySQL instance on an Azure VM, but I had trouble connecting. Either the Ubuntu instance didn’t have the MySQL ports open (should have in the template), or I typed the password wrong. Either way, I just decided to delete that resource group (I love resource groups) and download the MariaDB server.

It’s a standard download from Mariadb.org and it’s not too large. In fact, it took longer to download than install. The install was a standard msi installer, and ran in a minute or two.

From there, I started up MySQL Workbench, which I’d downloaded and installed. I entered my user and password, using the default ports for the server.

And things worked, as they should in 2017.

From there, I selected the data import and chose the file I’d been sent. It was (supposedly) a MariaDB dump, but listed as a .sql file. Looking through it, it has SQL and data inserts, so I ran it with the Data Import.

This ran for less than a minute and things were complete.

And sure enough, some MySQL syntax (googled to remember) works.

Now to debug some queries for a friend.

August 28, 2017
Responsible Log Growth with dbatools

I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.

I love dbatools, and I’ve been trying to explore the various cmdlets over time, both to practice my PoSh and see if there are easier ways of accomplishing some tasks.

Recently I was browsing the dbatools.io site to see what’s changed and noticed a new cmdlet, Expand-DbaLogResponsibly and just had to check this one out.

Often I’ve considered proactive space management a part of my DBA responsibilities that is core to a well run system. I don’t look at storage every day, but I usually try to look monthly, with an eye towards keeping about 3 months worth of data growth as a pad in most systems. That’s a general guideline that varies by system.

However, logs can be different. They are sized based on workload and backup schedule, which usually works. However, sometimes there are issues and I want to grow my log quickly. If you’ve read about growing log space (like this piece), you’ll realize that there are a variety of things to consider. And the rules change by version, or even patch level, of SQL Server. A lot to consider.

Fortunately, this cmdlet wraps those things up into a command. Just call it with the server, database, target size, and optionally log FileID. It will capture the rules for growth and take appropriate actions.

NOTE: This does not look at the current number of VLFs. You need to be sure you’ve managed your log file.

If you wonder how this works, or if the rules are appropriate for your system, check out the code at Github. I’m sure you’ll find that this is the easiest way to manage a log file.

August 25, 2017
The Career Bucket List

The Bucket List is a fun movie to watch, where two old men get a second chance at life and engage in some activities they’ve always wanted to enjoy. The idea of a bucket list isn’t new, and there are plenty of sites that can help you manage one. Brent Ozar has one on his site that he calls his Epic Life Quest, and I’ve enjoyed watching him tick off items. I got the idea of my own list nearly 20 years ago after hearing Ted Lesonis on the radio. He had a list of 101 things to tackled in life. Since he sold his stake in AOL, he’s had the resources to work on them. I have one, along with my wife, and we’ve worked through a few items over the years, though we were talking recently we need to revisit the list and perhaps revise things.

Why revise a bucket list? One of the things I’ve learned over time is that I change and the values, goals, and desires I once had will also change. The things I’ve had put on a list at 20 years old would have included owning a Ferrari, a Porsche, and a Lamborghini. At 40+, having owned a Porsche, the other things seem a little silly and vain, not to mention very impractical. At this point in my life, there are other things I find more interesting and captivating. The bucket list of today would be more experience based and likely contain more volunteer oriented goals.

I think my career has really changed as well, and as I move towards retirement, I don’t know if there are many more goals for me in this area. However, if I think back, I know I’d have had lots of them when I were younger. There are a few things I’d still like to accomplish, and hopefully I’ll find some time to work on them. For now, I wanted to ask you about your career.

What’s your career bucket list? Is there something you’d want to accomplish? Build some software? Work in an industry? Get a particular job, position, or work in a location? Maybe you’d like to become an advanced techie on some platform or using some technology. There might even be a goal you have to achieve some recognition for your skills.

This is a tough question to spring on you in an editorial, but leave a comment if something comes to mind. If it does, maybe you want to jot some notes down that might help you move towards that goal. Even if nothing strikes you, perhaps you want to bookmark this piece and spend some time in the coming weeks thinking about your career. Goals are always good, and they can help drive us in a direction that may ultimately provide some meaning or satisfaction that is lacking.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.2MB) podcast or subscribe to the feed at iTunes and Libsyn.

August 25, 2017