Sharding evaluation #2

Primary tabs

Hi all,

As I promised in my previous post Sharding evaluation #1 I continued investigating the impact f the number of shards.

To complete the overview I have also added the instances
on WIN (Server 2012 R2)  8 cores
- Cache for Windows (x86-64) 2016.2.2 - 12 GB global buffers
- IRIS for Windows (x86-64) 2018.1.1  - 400 MB global buffers, no sharding

on LINUX (Ubuntu 16.04 LTS)  2 cores
- IRIS for UNIX (Ubuntu Server LTS for x86-64) 2018.1.1   400MB global buffers
- no shards, 2 shards, 3 shards, 4 shards.

The table I used has 26.5 mio records and is identic on all instances with identic indexing

The queries:
 this was used with 2 different values resulting in  47000 and 19000 hits.
 I refer to it as simple queries S47 and S19

  AND <attribute> %INLIST ( SELECT $LISTFROMSTRING(LIST(<property2>)) FROM ... WHERE. .. %STARTSWITH  <value2>)
resultig in   ~3000 hits.
I refer to it as complex query CX3

  WHERE ... %STARTSWITH  <value1> and …. . .. %STARTSWITH  <value2>
resultig in   ~5500 hits.
I refer to it as complex query CJ5

This selection was closest to the real application. Others were also interesting in behavior but not so relevant to reality.
As expected and required all test queries delivered the same results across the different configurations

The baseline I refer to is Cache on WIN. Results are expressed by runtime aa percentage of this baseline.
For all values an average of the last 3 runs out of at least 5 was taken to exclude buffering impacts

  • IRIS on WIN no shard vs. Caché on WIN
    S47: 92%   S19: 98%   CX3: 87%   CJ5:  67%
  • IRIS on LNX no shard vs. Caché on WIN
    S47: 59%   S19: 56%   CX3: 50%   CJ5:  57%
  • IRIS on LNX 2 shards vs. Caché on WIN
    S47: 21%   S19: 23%   CX3: 38%   CJ5:  52%
  • IRIS on LNX 3 shards vs. Caché on WIN
    S47: 10%   S19: 14%   CX3: 27%   CJ5:  48%
  • IRIS on LNX 4 shards vs. Caché on WIN  
    S47:  7%   S19: 12%   CX3: 24%   CJ5:  44%

I think the results don't need much explanation:
- The more shards you have the faster your queries run
- The simpler the query the bigger the performance gain.
- SQL artwork could be counterproductive and might require sophisticated shard key design.

A different observation regarding initial load:
I missed measuring the load times exactly as this wasn't in focus at the beginning.
Anyhow the more shards you have to fill the longer it takes.
Out of stomach, I feel that loading of 4 shards took at least double the time of loading 2 shards
and loading 3 shards was somewhat in between. It didn't surprise me as moving that
amount of date across ECP just can be neither fast nor impressive. But as you see it pays off.

Based on this learning I see 2 options to keep the table up to date:
- using DSTIME class parameter to run an update in batch depending on your application.

HTH to decide on YOUR implementation of Sharding.




  • + 7
  • 1
  • 199
  • 6


Another great article, Thanks Robert!

I'm curious which mechanism you used for loading the data. SQL INSERTs indeed have to go through the data master when issued through an ObjectScript script, but when those are issued through JDBC, the JDBC driver will take care of automatically sending the INSERTs to the data shards directly and bypass the shard master for higher parallelism. We've seen that the maths (near-linear scaling) work out well there with in-house experiments.

InterSystems IRIS 2018.2 will also ship with a simple Java utility that bulk-loads data using the fastest mechanism for your target table. More on this in my GS talk "An Outlook on Scaling Out" next week ;-)

My data source was a table mapped from a non sharded namespace (copy from Caché)
So it just was 

INSERT INTO <table> VALUES (........)  SELECT .... FROM SOURCETABLE ........ 
Straight from Terminal prompt by do $system.SQL.Shell()   in order not to fall into a CSP timeout.

Robert, thank you for your writing.

As to IRIS Windows vs. Linux comparative performance, my guess is:

  • 400 MB was just enough for your queries; global buffer footprint can be checked with ^GLOBUFF utility after running the queries on freshly restarted IRIS
  • 2 cores was just enough as well: one for SQL processing, another for ECP
  • your boxes could be equipped with different CPUs; more frequency and/or more modern model/number can be of much importance
  • your boxes could have other harware differences, e.g. RAM type and frequency, bus frequency, etc.

In other words, in the same conditions InterSystems DBs should show the similar figures without regard of OS; therefore 50% difference means that "something happened". Although this guess is based on the experience of maintenance of dozens Caché instances, it would be a miracle if IRIS for Linux is able to over perform its Windows "sister ship" almost twice.

Alexey ;

When I started I had serious doubts about the first results related to WIN. 
My main target was to find out the impact of sharding between comparable systems (Linux)

The systems ran all on ESX, also the WIN box same for physical storage. So HW was definitely identic:
And the experience on WIN just happened as a by-product.
I tried to eliminate all possible differences except for global buffers Just 1 user, just terminal session,  

One difference: Ubuntu was set up by with a benchmark in mind

I found still another contributor to the dimension of the difference:

  • The original data on Caché where generated with a block fill of only 56% and almost no contiguous blocks
  • The sharded data showed a fill of 90% with large contiguous sections. and even high fill rate for indexes. 

Just to get a feeling I did an offline test. A simple Global MERGE reduced the block count to 65%.
That might be part of the mystery: Fewer blocks >>> less I/O >>> faster queries


That's advisable: the contribution of simple MERGE turned to be comparable with rather sophisticated sharding's one.