CV Driven Development

Over recent years I've watched a worrying new trend emerge which I think of as CV Driven Development.

What I mean by this is that people are pushing forwards with new tech simply to get it on their CV. It doesn't seem to matter what their ability is or whether or not there is a need for a particular technology, but it's simply that they seem to believe that having something on their CV is the right thing to do. It is completely ignorant of the investment the company has already made, the other skill sets and time to get up to speed and the wider picture of what introducing that tech means.

In the most extreme examples, this can go so far that a new tech is pushed in for the wrong reasons to a major project and it can totally die on its arse. The basic bits that needed engineering are lost along the way simply to cram the new toy in. Having just spent 6 weeks re-doing 18 months worth of someone else's CV Driven Project, I have seen this first hand. Properly engineered, the re-work was comfortably 300x faster simply by doing things right.

Let's split this into two categories, contractors and employees.

When you hire a contractor, they are there to do a specific job for a period of time. They trade off some of the benefits an employee has (such as job security, sick pay etc) in return for a higher rate of pay. We use these folk to backfill a gap in skills. As a contractor, the more skills you can have the more employable you are. So adding new skills is a good thing right ? Well yes, but if it's not in your spare time and you aren't being employed to learn new skills then you are simply abusing the contract and spending your time on things that you weren't employed for. Generally I would expect a contractor to be an expert in a given set of skills and I wouldn't expect to pay them to learn something that will be used at their next clients.

If you are an employee then it's great to get some new skills and bring them to the role. Everyone should advance their skill set and the business benefits while you are employed. However, doing this simply for the sake of it isn't right. It is a fine line between being innovative and learning vs simply doing something to get it on your CV. I've seen a fair number of people pushing for new skills simply because it's something new and shiney and with complete disregard for the practicalities and costs of changing to the new tech.

I realise I sound like a right miserable old fart, and I probably am. However, you have a responsibility to your employer to do the right thing at the right cost in the right time. When you are doing that it is a good thing. When you are changing tech simply because it benefits your CV and doesn't benefit the business then there is a problem.

I have seen examples where people are requesting 6 or 7 figure budgets to change tech and the only real explanation is that the existing work is poor and things need re-building. I have to question how good something totally new can be when that person has helped build something they deem as poor with technology that they claim to be experts on.

It's equally hard to justify for the wider picture of an established business (i.e not the start up people want to be a part of but aren't brave/clever enough to be the entrepreneur). If I employ someone for a role supporting a multi million pound business I would expect a minimum of 4 years experience. It's hard to find that with a new tech that is 2 or 3 years old. It would have to be the guys right at the forefront from the start. Being blunt, that probably is right in Silicon Valley but in Manchester, much as I love the place, much less so.

In my experience the people pushing for tech simply to get it on their CV are doing it simply because they don't actually have sufficient technical skills in the first place. They really don't like this being pointed out as they think that they are better than this. Eventually things will catch up with them.

To balance this out, I've also seen some exceptionally innovative work done with new technology in recent years. This has been done for the enthusiasm for technology and the benefit of the business and it pays off very well. The individual improves and the business benefits.

Don't build a data platform that works

I mean that a little bit tongue in cheek, but if you build a data platform that simply solves the initial problem you are setting out to do, then you are doing it wrong. You will build an accidental platform.

If you look at the use case in front of you, and maybe a handful more then it is easy to get tempted by a technology choice or an approach that suits that limited scope. Somewhere down the road it has the potential to bite you in the arse because you didn't think far enough forward.

I'm definitely not saying that you should go hell for leather with a 'big design' up front, and I would suggest that that applies more to the schema or structure as opposed to a modular and scalable platform. What you need quite simply is options. You don't need to have figured out every answer, but you need to know if there is an answer to be found and that you have a rough idea on what to do. Make your life easy for yourself and figure some of this out in advance. When you need it, things become easier.

I've had occasions where I've been told we need to double our full setup and time was tight. I was ready for it as I'd built a modular system (and I don't simply mean bolting on a few more servers). I didn't have an actual plan, but I'd left myself options that made it easy to figure things out.

If you are architecting a system, you have a couple of responsibilities. Providing options (without spending too long figuring them out) is one of them. Providing a timely and financially justifiable solution are a couple more. You are being paid to be the expert and winging it is not something an expert should do.

If you go headlong into an approach without knowing how you are going to scale, what the costs are, how much kit and so on then you will very likely go wrong. When you have a quiet day, spend some time figuring this all out. One day it will come in handy.

If your answer is simply 'we can add more servers' then you haven't thought it through enough. What happens when I add more, how do we balance things when we do this. What effort is required. What do we impact when we do this ? etc...

I have a lot of respect for the developers that I have worked with over the years (and there are hundreds of them). However, there is a different mindset between a developer and the man who gets called in the early hours being told that 'the database is the problem'. (Hint, it probably isn't).

What you are missing is 'everything other than your immediate problem'. By that I mean everything else going on in the system that isn't directly related to the problems you are solving. Database platforms have many many people connecting to many many different things. Some things are done well. Most things are not.

For me, when building a data platform, I have to be 10 steps ahead of everyone else and have always needed to have thought ahead as many problems as I can do. It's my job to take away obstacles before you've run into them. It's a bit like a game of chess. When I give an answer, I've already thought about my moves weeks or months before so it is a qualified answer and not one made up on the spot. I've seen plenty do this and sometimes it works and sometimes it doesn't.

For me, being obsessed with the details comes with the territory. Seeing how ALL users interact is necessary. You have to get a feel for the system. It is almost like a living beast.

You have to know the characteristics (and all of them if you can). 

• Learn everything you can about the patterns of workload
• Learn what stats a metric can be at any point of the day
• Figure out which ones are important (this helps massively when diagnosing problems)
• Did you size and cost things properly ? Without size you cannot cost it nor know it works.
• How does it work when someone does something stupid ? They will.
• What breaks first ? (Second, Third and Fourth etc)
• What is giving us our worst workload ?
• How does this impact other workloads ?
• Does fixing one thing kill another ?
• How are you going to secure it ?
• What happens when the business changes direction ?
• Did you think about high availability, failover and recovery ?
• Do you know your RTO/RPO ?
• Can you actually achieve it ?
• Did you practice that ?
• Backups and maintenance ?
• Do the files actually restore properly ?
• Backups and maintenance when everyone is doing something stupid ?
• What about all of that when the caching tier is dead ? 
• You did realise that people aren't going to capacity plan didn't you ?
• Did you think through Dev, Test, QA, Sandbox ?
• How about poor releases and doing these at full tilt without downtime ?
• Did you actually do the maths or did you just guess ?
• Did you think about what the business is likely to do that they haven't thought of yet (like a massive import of data, or batch processing hundreds of millions of rows) ?
• and so on.....

Getting an answer for one use case is NOT building a robust platform.

So, don't build a platform that works. Build one that always works.

It is far more technical, far more difficult and requires a long term commitment and good forethought. I'm less interested in a solution that works (this is bread and butter stuff so should always be easy), but I want to know I can guarantee that we can run with a broken leg. Saying 'I don't know' isn't going to cut it when the site is down.

We had an occasion a while ago where our caching tier was in tatters. We managed to run without caching for 3 weeks. This is for a multi-million pound dot-com business. On the DB server side we handle 25k queries per second (with caching working). We could take the strain. We had options.

So when you think about building a platform, think well beyond what you think you need. It isn't easy, believe me. Even building my most recent platform with something I've worked with for nearly 20 years has been difficult. I'd say 12 months went into getting things workable. 2 years then went into making it right and 2 more years planning the next generation to the extent that I now only need to think about the schema and have a scalable platform to cope. So when you come to me and say 'we've read a blog' or 'we've been to a conference' and 'we think we can do it better next week', then I'm sorry to say you are in for a hell of a surprise. I don't mind you learning to swim in the deep end. It might do you some good, but don't think I haven't thought things through.

Creating a Google Earth KML file from SQL

Quite a simple post this one, but I wanted to create a Google Earth KML file from SQL so that I could plot the location of a load of customers.

The SQL for this is below :

USE blah
GO
DECLARE @kml XML
DECLARE @kmlout NVARCHAR(MAX)

SELECT @kml = CAST('<?xml version="1.0" encoding="utf-16" ?>' +
'<kml xmlns="http://www.opengis.net/kml/2.2">' +
'<Document>' +
' <name>BasicKML.kml</name>' +
' <open>1</open>' +
(
SELECT
  BusinessName AS 'Folder/name',
  0 AS 'Folder/open',
  BusinessName AS 'Folder/Placemark/name',
    '<br/>ID: ' + CAST(H.ID AS VARCHAR) +
    '<br/>Type: ' + 'BusinessName' +
    '<br/>City: ' + City +
    '<br/>Postcode: ' + PostCode +
    '<br/>' AS 'Folder/Placemark/description',
    CAST(Longitude AS VARCHAR)+ ',' + CAST(Latitude AS VARCHAR) AS 'Folder/Placemark/Point/coordinates'
    --'' AS 'Folder/Placemark/Icon/href'
FROM
  database.schemaname.Business
ORDER BY
  BusinessName
FOR XML PATH(''), ELEMENTS) + '</Document></kml>' AS XML)

SELECT @kml

The Big Data Gold Mine

Just mulling a few things over about Big Data and the rush over the last few years for people to claim that they have 'Big Data'. I think the message has been missed and is yet another bandwagon (for most people). The definition on Wikipedia is 'Big data is an all-encompassing term for any collection of data sets so large and complex that it becomes difficult to process using on-hand data management tools or traditional data processing applications'. For me, that explanation nails it.

What it doesn't say is that big data can be when you have done something stupid and created a lot of data and don't have the skills to work with it. It could well be that what you perceive as 'Big Data' is simply you don't have the accessible skills to hand. Just because you have the tools, doesn't mean you have the skills to use them.

I think it's fantastic that businesses are now talking a lot more about data. There is absolutely interesting stuff out there and yes there are some clever new tools to allow you to grapple with all of this, but most companies very likely don't have this problem or quantity of data. They likely have a lack of the skill sets within the business to get a grip on things (especially early on). If you are working at a company and genuinely have such a quantity and complexity then great, but not everyone will. Just because there is a desire to have a new approach on your CV doesn't mean you should ignore what you have and shun a more simplified approach. Your employer is paying you for the right answer for them, not what's best for you.

Some places will genuinely need things like complex event processing and so on. Some places might genuinely generate sensor data at such a rate you can't work with it using traditional methods. That's great, embrace it and share what you find as we can all learn a lot from it. We shouldn't however, let a desire to use these tools force a more complex solution in than was needed.

The really good data guys I know simply treat the vast majority of all this as 'Business As Usual'. We've had a lot of data for some time and there are plenty out there with the skills to work with it properly (and many many more who don't). Those guys look beyond the end of their noses and can see where you are going with your data and how this giant puzzle all fits together. If you find them moaning it's because they've probably seen something that you don't have as a task yet, but if you did, you might well change your mind on how you are approaching the problem. You can solve the first stage problem today but if you have to replace it tomorrow it is likely wasteful. Yes, sometimes you need to do this, but forcing this in badly can only lead to tears. It is simply a case of looking at the big picture.

If you miss the basics of a problem early on you can end up generating what you perceive as Big Data. If you don't model the data appropriately you get data bloat, but that isn't Big Data. Big Data has a value that differs from the more traditional data but it takes a lot of 'different' effort to get that value out.

Let's take a little step back for a minute and look at the value vs the effort.

I quite often watch programs on Gold Mining on the history channel. What you see is a huge amount of effort for something really small and valuable. If you went purely off weight you are going to process thousands of tons for a couple of ounces. Those couple of ounces are financially well worthwhile having and absolutely must pay for the effort to get there. The quantity of earth moved to get there is huge though and it's easy to go bankrupt.

However, don't assume that all of the dirt is gold and once you've got the gold out, it's only worthwhile keeping that dirt if you then decide to look in it for diamonds.

Big Data is very similar to gold mining in many ways. Many people are affected by the Gold Rush. Plenty fail. There is definitely gold in them there hills, but if you don't have the right equiment and team at the right time then you aren't going to be the one to make their fortune.

Mongo Concurrency Shoot out - A more detailed response

http://geekswithblogs.net/EltonStoneman/archive/2014/01/05/the-concurrency-shoot-out-sql-server-versus-mongodb-part-3.aspx

I couldn't post a proper response to the above link so I'm posting here and will post the link instead.

It took me a while before I stumbled back on this again. Plenty of tuning could be done on the SQL side. I've scripted up the most obvious below as there were some definite things that will improve performance. To get more out of it would need a little longer than I have, but there is plenty more to come out when you dig into what is actually being queried.

This is the pain in the arse with SQL if I'm honest. It's not obvious and doesn't tell you where you can improve or how. It'll allow you to do a lot (badly) but if you know what you are doing you can make it fly.

Always worth watching http://sqlbits.com/Sessions/Event9/Finding_the_Limits to see how much you can push SQL.

Anyway, some basic tweaks below.

USE master
GO
SET NOCOUNT ON
GO
sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'contained database authentication', 1
GO
RECONFIGURE WITH OVERRIDE
GO
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = 'MongoTest' ) 
  BEGIN
    BEGIN TRY   
      CREATE DATABASE 
           MongoTest CONTAINMENT = PARTIAL ON (NAME = MongoTest,            
           FILENAME = 'D:\MongoTest\MongoTest.mdf', SIZE = 1GB, FILEGROWTH = 256MB) --Made as small as possible simply for testing
           LOG ON (NAME = MongoTest_log, 
           FILENAME = 'D:\MongoTest\MongoTest_log.ldf', SIZE = 1GB, FILEGROWTH = 256MB)  
           
           ALTER DATABASE MongoTest ADD FILEGROUP FG_STANDARD
           PRINT 'Created FileGroup FG_Standard'

           ALTER DATABASE MongoTest ADD FILE (NAME = Standard_01, FILENAME = 'D:\MongoTest\Standard_01.ndf', SIZE = 1GB, FILEGROWTH = 256MB) TO FILEGROUP FG_Standard
           ALTER DATABASE MongoTest ADD FILE (NAME = Standard_02, FILENAME = 'D:\MongoTest\Standard_02.ndf', SIZE = 1GB, FILEGROWTH = 256MB) TO FILEGROUP FG_Standard
           ALTER DATABASE MongoTest ADD FILE (NAME = Standard_03, FILENAME = 'D:\MongoTest\Standard_03.ndf', SIZE = 1GB, FILEGROWTH = 256MB) TO FILEGROUP FG_Standard
           ALTER DATABASE MongoTest ADD FILE (NAME = Standard_04, FILENAME = 'D:\MongoTest\Standard_04.ndf', SIZE = 1GB, FILEGROWTH = 256MB) TO FILEGROUP FG_Standard
           PRINT 'Created 4 files for FG_Standard'
--Multiple files to help performance. Worth googling.
   
           ALTER DATABASE MongoTest MODIFY FILEGROUP FG_Standard DEFAULT  
    
      PRINT 'Database created: MongoTest'
    END TRY
    BEGIN CATCH
      PRINT '*** Database not created: MongoTest ***'
    END CATCH   
  END
ELSE 
  BEGIN 
    PRINT '*** Database already created: MongoTest ***'
  END
GO
USE MongoTest 
GO
EXEC sys.sp_changedbowner 'sa' 
GO
PRINT 'Database owner for MongoTest changed to sa'
GO

USE MongoTest
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.Tables WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'ParentEntities') 
  BEGIN
    CREATE TABLE dbo.ParentEntities ( 
      Id INT IDENTITY(1, 1) NOT NULL,
      CorrelationId NVARCHAR(50) NULL, --No idea where you are using this column
      Name NVARCHAR(255) NULL,
      CONSTRAINT PKN_ParentEntities PRIMARY KEY NONCLUSTERED (Id ASC)) --Again
      ON FG_Standard
      WITH (DATA_COMPRESSION = PAGE)
    CREATE UNIQUE CLUSTERED INDEX CIDX_ParentEntities --Again
      ON dbo.EntityEvents (Name ASC)
      WITH (DATA_COMPRESSION = PAGE) --Optional if on Enterprise Edition
      ON FG_Standard --Using different files for recovery reasons, my standard approach to scripting
    PRINT 'Created Table dbo.ParentEntities'
  END
ELSE
  BEGIN
    PRINT '*** Table dbo.ParentEntities already exists ***'
  END 
GO
--If you query by anything other than 'Name' in the above then you need to check the execution plan and build the query and index accordingly.

IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.Tables WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Entities') 
  BEGIN
    CREATE TABLE dbo.Entities ( --Table names shouldn't be plural
      Id INT IDENTITY(1, 1) NOT NULL, --Ideally a more descriptive name would be better.
      Name NVARCHAR(255) NULL, --Ideally you wouldn't use reserved words here but hey.
      ParentId INT NOT NULL,
      StatusCode INT NOT NULL,
      ParentEntity_Id INT NULL,
      CONSTRAINT PKN_Entities PRIMARY KEY NONCLUSTERED (MarketId ASC)) --Having the ID as a Clustered Index is likely pointless here.
      ON FG_Standard
      WITH (DATA_COMPRESSION = PAGE)
    CREATE UNIQUE CLUSTERED INDEX CIDX_Entities --Assuming you are most likely searching by name it makes more sense to have this as the clustered index
      -- so that we do a single pass through the b-tree index. The previous version lacked proper indexing.
      ON dbo.Entities (Name ASC)
      WITH (DATA_COMPRESSION = PAGE) --Optional if on Enterprise Edition
      ON FG_Standard --Using different files for recovery reasons, my standard approach to scripting
    ALTER TABLE dbo.Entities
      ADD CONSTRAINT FK_Entities_ParentEntity_Id
      FOREIGN KEY (ParentEntity_Id)
      REFERENCES dbo.ParentEntities (Id)
    PRINT 'Created Table dbo.Entities'
  END
ELSE
  BEGIN
    PRINT '*** Table dbo.Entities already exists ***'
  END 
GO
--If you query by anything other than 'Name' in the above then you need to check the execution plan and build the query and index accordingly.

IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.Tables WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'EntityEvents') 
  BEGIN
    CREATE TABLE dbo.EntityEvents ( 
      Id int IDENTITY(1, 1) NOT NULL, --again, better naming convention would help
      [Description] NVARCHAR(255) NULL, --as above
      EntityId INT NOT NULL, --In the first table this should also be called the same as it is so much easier to follow 
      CreatedAt DATETIME2 NOT NULL, --Changed to DateTime2 which is the better approach these days
      StatusCode INT NOT NULL,
      CONSTRAINT PKN_EntityEvents PRIMARY KEY NONCLUSTERED (Id ASC)) --Again
      ON FG_Standard
      WITH (DATA_COMPRESSION = PAGE)
    CREATE UNIQUE CLUSTERED INDEX CIDX_EntityEvents --Again...
      ON dbo.EntityEvents ([Description] ASC)
      WITH (DATA_COMPRESSION = PAGE) --Again
      ON FG_Standard --Again
    ALTER TABLE dbo.EntityEvents
      ADD CONSTRAINT FK_EntityEvents_EntityId
      FOREIGN KEY (EntityId)
      REFERENCES dbo.Entities (Id)
    PRINT 'Created Table dbo.EntityEvents'
  END
ELSE
  BEGIN
    PRINT '*** Table dbo.EntityEvents already exists ***'
  END 
GO

--If you query by anything other than 'Description' in the above then you need to check the execution plan and build the query and index accordingly.

--Things NOT looked at (not exhaustive)
--Partitioning - Enterprise only feature
--Execution plans - these are key to understanding the data
--Selectivity/Density
--Fill Factor (Mongo uses the term index padding but its the same thing effectively)
--Query Plan re-use - very important if you are using an ORM such as nHibernate
--How many files are actually needed for the DB
--What queries are to be run. I didn't dig into the code, purely the schema above
--What delays we can eradicate when this is run under load. This takes a little longer but is always worth doing (wait stats etc...)
--Locking, Blocking and Transaction Isolation level etc... Lets fix the basics first
--Any spills to Temp DB. This always kills performance.
--Whether or not the data resides in memory. If it doesn't on SQL then the comparison is really memory vs disk.
--There is likely plenty more. I gave this about 10 mins.

/*Rollback*/
/*
DROP TABLE dbo.EntityEvents
DROP TABLE dbo.Entities
DROP TABLE dbo.ParentEntities
*/

Same shit, different data store. B-Tree indexes

As you start to delve into the inner workings of various data stores you start to uncover the similarities between them.

So, say for example you want to know the difference between a document store and an RDBMS then there are plenty of places to tell you. But what happens when you look in a bit deeper ? 

Well, the truth is there are a lot of things the same, just named differently. Understanding one in depth can help to understand the other. So I'm going to look at the B-Tree index and how data is physically structured away from any schema.

The basic B-Tree is laid out as follows :

or

Think of it like an upside down tree and it's easier to suss. At the very top of the tree (the root) is the entry point. Values at the opposite end (the leaf) are the actual data. 

The B in B-Tree stands for balanced, and what this means is the leaf nodes are split pretty evenly. As more data gets added to the data store then the B-Tree is re-balanced automatically to give an even distribution (logically). It does this by moving data within the pages to another location. This (for the MS SQL guys) is called a page split. You can often find hundreds of these a second in busy systems. When data isn't on physically contiguous pages then this is physical fragmentation.

Physical fragmentation can be a bad thing (more so on mechanical storage) so this is why indexes need to get re-built so it can be accessed easily on the storage without too much effort.

To get to some data you need to start at the root node and work through to the leaf node. The more work you do here, the slower your query will be. If you touch lots of intermediate and leaf nodes then you are doing a lot more work in picking out the page you actually want. I like to think of this as trying to fall through the ugly tree without hitting too many branches. The more you hit, the uglier it gets.

How does this apply to various data stores ?

In MS SQL we often talk of a clustered index. This is the physical data and therefore the clustered index IS the data in the B-Tree. The order you get your data back in is defined by how this structure is built. If you build it in a specific way that the data doesn't need transforming in or out then you save on processing time and therefore make it quicker.

To get a single page, this is called a seek. When we deal with the clustered index or primary index we can get all of the data by knowing the key. If you don't have a clustered or primary index this is called a heap and is effectively like a pile of leaves that have been swept up in autumn.

If we move to say CouchBase, data is stored in buckets. These are the same as the leaf node. It just has a different name. Your KVP key is the clustered index by a different name. The value in the Key Value Pair can be a single value or the document. MongoDB uses a DocumentID which is a 12 byte number for each document. CouchBase can allow you to have composite values to form the key.

If you add what CouchBase and MongoDB call a Seconday Index then again it uses a B-Tree. In MS SQL this is called a non clustered index. This time around it doesn't hold all of the data. It contains the data for the index but if you need more, then you need the pointer it gives you so you can go back to the B-Tree index and do a second pass using that. This in MS SQL is called a Key Lookup or RID (row identifier) lookup. You need 2 passes through the index to get all of the data.

If you don't have the right index then you need to check each leaf node or bucket. This is called a scan as you have to check them all. Doing this burns processing time and needs more IO as you need to obtain every page, read it, decide if you need it and move on to the next page of data.

If I shard my data and need to select something through a mechanism that doesn't use the shard key (say I shard by hash and I query by range) then each shard must do the same work to find the data within the shard. If I add in large quantities of data and don't use the sharding key then I burn more CPU and therefore need to introduce a more scale out strategy to compensate. If I use large quantities of data then I need more RAM which is also helped with a scale out approach to the data. It gives me different complications by doing this so I need to consider the trade offs.

One of the best things about structuring data in MS SQL is that I can have my clustered or primary index as something different to the primary key. I can have an ID as a non clustered primary key, yet my clustered index can be over various other values (say a name for example). I can choose what suits my querying best so I only need to do a single pass through. I can't do this in MySQL. Nor can I that I am aware of in CouchBase or MongoDB for example as it would mean using the value part or the document as my primary index. When you really start looking at natural and composite keys and use this flexibility you can avoid a secondary pass and therefore gain speed.

If I now add in partitioning this is done at the root node. I can put everything A to F in one partition and G to Z in others. If I need value B then I can totally ignore the other partitions (partition elimination). Again this lightens the load if done correctly and means I only need some partitions in memory so have room for other things. I'm not aware of a way of doing this in a KVP or Doc store and it can't be done in MySQL either that I am aware of.

Learning to navigate this all properly is one of the keys to unlocking the performance in a data store.

There are other types (column store and BW-Tree for example), but if you get the hang of the B-Tree structure then it all starts to make more sense.

Big Data or JSON Meta Data Problem ?

I've been looking at a few things with MongoDB recently with some of the guys at work. It was brought to me as a 'Big Data Problem', but I think the problem lies in the meta data.

Having a detailed look into things made me think a bit more about the structure of data within a JSON/BSON document.

An example

{

    "CN" : "Something here to denote the name of the customer",

    "AB" : 27,

    "_id" : {

        "ST" : "MANCHESTER",

        "TS" : ISODate("2014-02-05T15:00:00.000Z"),

        "CI" : 158580,

        "XX" : 0

    }

}

Let's do the architect thing here and colour it in.

{

    "CN" : "Something here to denote the name of the customer",

    "AB" : 27,

    "_id" : {

        "ST" : "MANCHESTER",

        "TS" : ISODate("2014-02-05T15:00:00.000Z"),

        "CI" : 158580,

        "XX" : 0

    }

}

• The stuff in Yellow is your structural meta data for this document.
• The stuff in Red is data that is highly likely repeated in similar documents
• The stuff in Green is the useful data.

If I drop this into a Word or Google Doc and do a quick count I can see I have 190 characters with spaces and 152 without. The document breaks down as follows :

• Spaces = 38 characters (20%)
• Meta Data = 61 characters (32%)
• Repeatable Data = 59 characters (31%)
• Useful Data = 32 characters (17%)

If you modelled this with an RDBMS you could get this down to approx 20 characters.

One of the biggest arguments FOR a NOSQL option is the ability to be schema free as such. Well, if you are to go schema free then that's fine. If however, you are going to repeat that exact same structure in many millions of documents then it misses the point and you might as well have a more fixed structure.

I was chatting with a friend about this (he's a very good Dev btw) and he asked 'well, how does SQL handle this meta data ?'. It's a good question and I guess that most none data guys haven't really thought about it.

The answer is simple. It gets stored once. That's it. Once. We pay a storage cost that is the same if you have 1 row or 1 million rows.

With JSON, you store it in each and every single document. 

Let's play that out a little and put some costs against it.....

190 characters (or bytes if you prefer) and really only 32 useful ones. Let's say we create 20 million of these documents a day and keep a rolling 365 days.

That's 3.8 Billion Bytes in a day. Just over 3.5GB per day. Over a year that's going to be 1.2TB or thereabouts.

Now let's put a cost against that. Let's say £4K/year/TB so we get £4.8K in costs.

If you didn't have all of the extra 'stuff' that would have cost you £816.

Oops.

Lets say we have some (4 core) servers with 128GB of RAM and they cost £3K each for the hardware and £3K/year for hosting with a premium company. Let's just assume we need the same amount in memory and on disk so we don't lose it.

With the extra stuff we need 1,200TB / 128 * 2 (basic redundancy) = 19 servers (call it 20 for ease of use). I need £60K for hardware and £60K/year to run them.

My 3 year total cost is then £255K.

If I did the same with the useful data it works out as (1,200 / 100 * 17) = 204GB of data. So, 204 / 128 * 2 = 3.2 servers (lets call it 4). Cost wise that's £12K of servers and £12K/year of hosting + £816/year of storage. That's just under £25K all in.

£255K vs £51K for 3 years.

Half a rack of servers vs 10% of a rack.

Bloody Nora ! Sobering isn't it ?

Your Big Data problem is in the meta data needed to define the structure. You can't glean anything useful from the braces and colons used to define the structure.

Yes, but NOSQL stores are fast !

Erm....yes, you have just gone from 16 cores to 80 cores so it better had be.

Good job you ain't paying for them expensive SQL licences eh ? Erm...you are looking at about £5K/server/year for Mongo support so you've spent another £100K/year on them that we didn't account for. 

SQL wise, you are looking at $7K per core. So 16 cores = £67K for perpetual licences (and that's for Enterprise edition which performs way better than the Standard version people normally compare to). We don't have to pay for failover so we can halve that amount too.

So far we have a 3 year cost as follows :

JSON option = £555,000

None JSON option = £89,000

Difference = £466,000 cheaper by handling JUST the useful data.

None of this covers indexes and the space needed for them.

So....in conclusion, what you perceive as a Big Data problem might actually not be. IF you are going to go schema free then use it where a fixed schema really isn't the right fit. To be honest, if you are creating 20m documents a day and they all differ completely in structure then you do likely have other problems, but if they are the same then take a proper look at the data to see if you can work with it better.

Do you want to be the guy who asks for £466,000 that isn't going to give the business any value whatsoever ?

How to impress the DB guys with NoSQL

Lets step away from the technical aspect of SQL vs NoSQL for a minute. 

We know there are some core differences between an RDBMS approach and a document store approach (or KVP, Graph or Column Store). There is a way to get the more traditional data guys on-board.

This is totally one-sided by the way. There will be an equal argument from various Devs and if a response is well written, I promise I will add it. My hope is to provoke some thought.

Attitude

First up, don't seperate yourselves and then claim to be better. That's just never going to win any friends. OK OK so you don't like the DB guys and don't want them as friends, but hey, lets all play nicely. It might well be better or be a better fit but lets go over the basics first without looking purely at headline figures.

Don't forget that when something goes wrong code wise and calls the DB a few thousand times a second when it shouldn't, it's the DB guy who gets woken up at 3am to have a look. Even if you know it's not a DB issue, that guy still gets called for something he probably wasn't involved in. No wonder he gets pissed off and is grumpy and wants to protect his part of the estate. A little bit of being humble would go a long way on both sides. If the DB guy fucks up then the company stops earning and it's blatantly obvious. It's a lot of pressure. I've been there and it's tough. Rolling back DB changes needs a lot more thought. Building a system that won't lose data isn't easy.

Understanding the differences in approach

When coding, YOU tell the computer what to do and how to do it. With a DB you tell it WHAT you want and it figures that out for you. Don't forget that everyone else is competing for the same resource if your system is complicated. The DB engine will figure out things based on a least cost route. It's not always what you perceive to be best.

One of the biggest failings of RDBMS's is that they let you do stupid things and don't tell you if you asked in a crap way. It takes effort to get these things right. That's why things take time to develop. It takes more time to do DB development than pure coding. I've done both and it genuinely does. It pays off bigger if you get the data right too.

'Everyone can write a SELECT statement Ryan'. True, but not everyone can build a highly performing, highly available database. So let's not patronise each other or take cheap shots.

Backups

One of the fundamental things you do as a data professional is protect the data and make sure you can recover should something go wrong. It does go wrong and any long term data guy has been bitten. 

Do you want to be the guy sitting there without recoverable data saying 'erm...sorry' ? Hell no.

We genuinely care about what we are looking after. Any DB guy worth his salt will cover backups on his first hour of day one in a new job. Pretty much everything else can be worked on but without sorting this out then the rest is pointless.

Backups should be tested and proven. If you aren't doing this, you aren't doing it right. It should not be an afterthought. It gets interesting when you look at NoSQL solutions where you have to take a copy of the physical data from under the data store. How about across shards ? Can you get a consistent copy across shards without data loss and in a consistent state ? Not quite that easy is it ? OK so there is eventual consistency but is it really just consistently inconsistent ? It's worth thinking about how you do this right.

Performance

Yes, NoSQL solutions can be fast. However, in order to be fast, get the people who can make it fast. Don't just assume that out of the box it will be fast. The DB guys know a lot about making data go fast. They understand how to structure a b-tree to make it a simple pass instead of overcomplicating things (yes, NoSQL solutions such as MongoDB use b-tree's so a lot can be learnt). If you are going to choose a NoSQL solution then get the guy that knows how to make that work brilliantly. I can learn a lot from that guy.

I totally get that these solutions can be quick, but seriously here. Prove it. But work with me as we prove it together. When you genuinely blitz what we do you will raise eyebrows I promise. You will have our attention. Sure you can do something fast with MongoDB, Vertica or whatever but if you claim it's going to be fast, then make it properly quick. Do that under serious load with a lot of data and you will have my attention I promise. How fast is fast enough ?

There is no point in you buying a motorcycle if you don't know how to ride it.

Know that you aren't Amazon or Facebook

They have a whole bigger and scarier problem. You don't need their approach any more than I need a fleet of helicopters to travel to work. You can learn a lot from them but I bet you don't have their problems, resources or turnover.

Think of the bigger picture

If you come along with an interesting approach that covers a lot of the basic bases the DB guy will be impressed and helpful. Don't just look at one answer. The fastest way to the front door at work is to jump out of the window. It might not be the best approach though. If you apply the same thinking as the DB guys might then you might save yourself some pain.

The platform

For me I try to architect and build the platform for the biggest component piece that I have in front of me. Once that's done I will move on to the next one until it's right. For my current work it's a very busy OLTP approach and we have paid for MS SQL Enterprise. It would be irresponsible to not use that to it's fullest. Where it doesn't fit we will find the right approach, but if it works for 80% then I should spend 80% of my time there.

Understand we don't always suggest SQL

I get accused of this a LOT. I know it extremely well yet still have a long way to go. I fully appreciate that there are failings in SQL, don't overly like MySQL and think Oracle is too expensive. I know it can be seemingly overly complex and expensive but it is a superb product with a lot of time spent understanding it and making it better. I guess it's more of a reluctance based on seeing problems in other areas and I'm still looking for the one that totally blows my socks off. I get handed MongoDB as the answer a lot without being given the question it is supposed to answer. I genuinely like Neo4J. ElasticSearch (with Kibana) looks great where I know the problems we wanted to solve and it is the right fit. I'm all for it when it's right.

Whatever answer fits best should be based on the right answers for you. One size doesn't fit all, but the arguments distract from finding the right answers.

Work with us

I dealt with a Chief Architect several years ago who said 'stick around and you might learn something'. To me that summed up his arrogance. For me I would rather say 'work with me, learn from me and teach me'.

Thoughts on NoSQL

I've been thinking about NoSQL solutions a lot recently. It comes with the territory I guess. 

If you imagine any radical change as a swing, we've probably come to the extreme at this stage and are swinging back the other way somewhat. That is back towards RDBMS's. I'd imagine that over time things will settle part way between the two with the sucessful vendors being the ones that offer the most features of both. NoSQL options are adding in SQL interfaces and discovering the problems that RDBMS's have gone on to address over the years (indeed they are likely to make similar mistakes).

Google have certainly found that NoSQL solutions and the ideas of Eventual Consistency have caused the sufficient problems to move back to an RDBMS (but retaining the nice features that NoSQL offer)

http://www.theregister.co.uk/2013/08/30/google_f1_deepdive/

Google also use RSBMS's heavily (having recently decided to move to MariaDB from MySQL).

I started to dig into the NoSQL thing a bit more and from what I can see it mostly seems to stem back to a white paper from Amazon about scaling data at extreme scale. If I can find the link again I'll update this blog post.

The basic premise of the paper highlighted something that Ted Codd also pointed out back in the 1970's as he worked on the foundations for relational databases. This was that there is an overhead in checking constraints on data in a relational system. So think of there being overhead for foreign keys and you get the idea. It is absolutely true in that there has to be some delay while that work is done (we are talking very small fractions of time though depending on scale). Amazon's paper found that at 'extreme scale' this caused a problem for them with the expectations they had for their systems. 

Part of the problem that Amazon was aiming to solve was that they needed systems that would guarantee writes for the 99th percentile of their customers. In most cases you would typically look at the 95th percentile, so 99th is a whole different argument as it brings in most of the edge cases and that can change the game. 

Do this at 'extreme scale' (with very high concurrency) and it becomes a very difficult problem to resolve.

Amazon also aimed to address the 'delays' that you get from an RDBMS which is in the locking, logging and latching of the data which most developers perceive isn't fast enough for their needs. The best approach I've seen to improve on this is from VoltDB (another Michael Stonebraker product). It's early days for this and while they only offer support in office hours EST it will stay off my radar. They are both absolutely right in that there is time lost here. However, how much time and what impact that has for you is an interesting question. If we are talking the last few microseconds then yes it is valid, but if that doesn't really matter to you do you need to worry ? If you have delays introduced by poor coding etc... then you likely aren't at the stage where you need to worry here.

The NoSQL options here stop you worrying as it's generally a fire and forget approach. This is fine until something goes wrong. To help make it better (with say MongoDB) you can turn on journalling, but then you have re-introduced a basic concept you were trying to avoid. Hmmm...

Amazon were quite right in that the delays from a relational system would likley impact their expectations so they created their own approach which is where the NoSQL approaches stem from. It might be possible to argue that you could overcome a lot of the problems that both Amazon and Codd identified, but I'm sure they have all thought it through so lets give them credit and assume they were right.

We now move onto the 'joins are bad' argument. Feel free to read my other post on this, but as a basic statement I'd say that people misunderstanding how to work with data is the problem and that joins are perfectly fine for the vast majority. Those arguing against them are likely developers and I'd suggest they probably don't know RDBMS's enough to have a qualified discussion.

A lot of latency in databases comes from storage whichever option you choose. Storage desperately needed to move on and has done so now with NAND flash (even though it is a 25 year old idea). Moores law doesn't apply to spinning rust so it's hampered data as we've exploded the use of it.

What we need to remember is that Amazon was aiming to solve Amazon's problems. Not yours. They have offered DynamoDB to the public for a while but that's sensible to make revenue from it once they had implemented it for themselves first.

A number of other approaches have come along the way and offer solutions that allow you to move away from RDBMS's. Do you really need to solve the problems that Amazon needed to ?

Most of the NoSQL options appear to try to solve the problems that they understand from Amazon's findings. Most businesses don't need to scale to the extremes that Amazon is at. Graph databases might not be new, but Neo4J looks very interesting and I see good potential there in the right places. MongoDB is based on ideas from Lotus Notes which was awful IMHO (I spent 6 months as a Lotus Notes developer back in the day).

In terms of which platform to choose ? Well, it's an entirely different question if you are a start up or have an established platform. The true TCO can be way higher than expected if you swapped it all in (especially if you are looking at this for an Enterprise scale business). The costs look good for single servers, but try and see how much it costs for trying to put 1TB into RAM in a sharded setup. It ain't as cheap as you might think, especially when you consider support and redundancy or failover, dev, test, qa and so on. You can achieve this with lower costs if you trade off RAM for fast enough storage (say FusionIO) but you loose the scale out argument. Each approach is a balance of trade offs. Yes MS charge a lot and Oracle more. It's a stick I get beaten with most weeks and I wish it was better.

Developers often think that saving costs and time for development is the only consideration. It is very important yes, but if I save a few grand on Dev time but spend £3m in production then it's not an easy sell to me. It's simply because of different needs of the data that stops each side seeing the other's argument. (For the record, I've been a Dev for many years, then SDM, then DBA, then Data Architect and now Enterprise Data Architect who also owns a small software business).

The Agile movement has been incredibly worthwhile and beneficial but we might have gone a bit too far. In the lean world you only think about the next problem for development. However, from an enterprise architecture point of view I'm interested in making sure that when you come to the bigger problems we haven't taken the wrong direction earlier on. I'd love to hear a developer or SDM show me where the maths makes sense for the business whereby the changes we make are going to bring in a higher return and cover it's basic costs. It's the most basic concept of business and yet is often forgotten in the rush to develop the next feature. It'll get you somewhere down the road if you are lucky (and half decent) but at some point you have to turn that into profit.

Start with the basic maths. How big is the data. What will it take to run it. How fast do you need it. Can you afford to change it and if so which gives you the best return ? Don't just start coding. Measure twice, cut once my Dad always used to say.

So...right tool for the job yes (polyglot persistence), but a bit of common sense and some basic maths would go a long way on both sides. Having a platform that does 90% and then using something else for the edge cases is far more sensible. Remember that NoSQL stands for 'Not Only SQL' instead of 'Anything But SQL'. 

How do you justify an 'in-memory' datastore solution for everything ?

I've been doing some thinking on this over the years and again more recently.

Forget any arguments on the shape of the data you want to see (in and out). Forget which particular product is the preference and the maturity etc of that product.

It's some basic maths that sticks in my mind somewhat if you do everything as an 'in-memory' solution.

So....lets start with the basic maths.

I currently have a 1TB footprint of my data. I know I'm going to grow and if I give up a relational approach I'm likely to need some more space. Lets call it 2TB for the sake of argument. Hardly a massive data footprint as VLDB's are currently defined as starting at 1TB and data is growing a lot these days.

So, 2TB. In memory. That's a lot of memory.

I'm going to need 18 servers (with 60GB of RAM) per Terabyte. You can go for more memory if you like but you aren't helping your 'scale out not up argument'. It's an arbitrary number I suppose. I'll also suggest say £3k per server is likely.

I've solved having a solution like A-L and L-R and R-Z with my data split across nodes but I now have 18 single points of failure with data sharded across it. 

Hmmm....Best split the data so that it never resides on just one node (so lets say 2 nodes is acceptable). That means that I need twice the memory footprint I was originally thinking of.

I'm now looking at 18 servers per TB which gives me 36 servers and now I need x2 of that to give me redundancy. 72 servers so far. Keep with me.

OK, I'm going to throw some more things into the mix. Yep, a second data centre for DR. Bugger. Erm....hang on a minute. 144 servers now.

I might be able to split the estate over two data centres so I need to have a think about the best balance for the business there. For now, let's stick with the 144 servers (yes it helps my argument, but it is my argument so ner).

Right...we have everything we need now don't we ?

Erm....No

• Dev

• Test

• QA

• Sandbox

Ah.....

Right, so I can bin some of the redundancy and keep them simple.

Lets say 144 servers for Live, 36 for Dev, Test, QA and Sandbox each = 288 servers.

288 x £3,000 = £864,000

Awesome. We now have everything we need !

No

Did you think about support ? Come on, you are looking at putting this live and you really should think about it. If you are talking MongoDB you are probably looking at £5k per server - call it £3k if you can negotiate a good deal.

That's another 144 x £3,000 which = £432,000

Ah

So we need 288 servers and have spent £1,296,000

And NOW we now have everything we need !

No !

Seriously ?

Have you thought about monitoring ? BI ? Reporting ? and so on ?

Righty ho. Bet you didn't think about power, cooling and rack space did you ?

Or switches to make the 288 servers talk to each other.

Or cables ? (OK, I'm being somewhat pedantic).

Lets say you use 300 watts per server. That is 300 x 288 = 86,400 watts ! (360 Amps) 

I'm having a Doc Brown moment here if you can't tell.

At 42u per rack I'm going to need about 8 racks with some wriggle room. Assuming I can cool a rack full of pizza boxes with no gaps. Did you calculate the BTU's per hour that you need to cool ? No, I bet you didn't.

In case you haven't been to a data centre, that's a lot of floor space for 2TB.

What happens when you grow bigger than anticipated ?

Maybe, just maybe, you should pick your battles and work out what does need to be in memory (and realise that some solutions you already use are already using data in memory) and if you do genuinely need a very specific solution then go for it with the bits that will need it as opposed to everything at once.

For smaller data stores it does make sense, but there is a tipping point where you start to feel uncomfortable.

Maybe they only really make sense if they are cloud based and the hardware becomes someone else's problem. It does add other things to think about but I've leave it there for now.


Microservers or Blade Servers might be worth considering in order to reduce the physical footprint and possible cost (although you still have a lot to pay for)