Re: Sharding

Hi Mladen,

At its core, Sharding is basically a "Horizontal Scalability" play, while Oracle RAC is a "Vertical Scalability" play. While server technology keeps increasing in power and performance, there are high-scale cases where some issues come up. I have listed a few below:

• Connections - Even with connection proxying, there are cases where the sheer number of database connections required for massive web-scale applications cannot be sustained by a single RAC cluster. (In fact, when connections reach the 10's of thousands, the presence of a single entity such as a RAC system presents a very severe threat to availability)
• Memory - Related to # of connections, and to memory requirements for User PGA. Large RAM spread on multiple RAC nodes do have upper limits
• Interconnect - Even with RDS over IB, access to busy, hot-spot blocks such as Index leaf nodes or narrow lookup tables for these large # of connections can easily overwhelm the Interconnect. Note that it is sometimes the 200 byte interconnect messages required for grants and GES (rather than the data/index/control blocks) that can be the real killers of Interconnect
• CPU - While most large systems have enough CPU to cover the normal, run-the-business type workloads, surges caused by SQL plans going bad, even temporarily, or for surges at peak times can be enough to tip over even the largest, best servers. (And don't count out the overheads of CPU required for LMS/LCK0 type processed which run on RT)
• DDL on busy tables - Changes, even including innocuous, ongoing, expected activities such as partition maintenance can be devastating for hot tables/SQLs (Granted Oracle 12.2 is addressing a number of these issues, but still, executing DDL and changing objects is challenging)
• RAC Brownouts - Can be quite severe on systems with large SGAs (brought about by the need to freeze GES/GCS during RAC reconfiguration during node evictions/rejoins. Also, if you were naive enough to set RAC Re-mastering on large systems :-)
• Redo rate - Insert/Update intensive workloads can bump up against Redo I/O throughput and latency. Especially in cases where you need to transfer CURRENT blocks, Redo latency has a huge effect
• I/O Latency and throughput and SAN Storage limits - Related to above but includes Reads and DR Writer throughput
• Upgrades - All or Nothing model - you cannot perform a major version upgrade of a RAC to the next version without some downtime (There are techniques to reduce but not remove the downtime. If you want a rollback, then it makes sense to build an equivalent cluster, setup logical replication and switch to the new one while running the old in parallel)

Most workloads, however, do NOT fit the pattern above, so to be fair to the implied question, Sharding is not (or should not be!) positioned as a solution to the generic workload. You also sort of indicated that RAC does not lend itself to the Cloud (and I am assuming you are talking about Cloud vendors other than Oracle) and you are correct, so that is definitely a solve for such use cases.

However..... Sharding (in general, not specific to Oracle Sharding) does have its own caveats, some of them below:

• The most important point is that the use case HAS to fit the pattern of aligning behind ONE strong key, namely the Shard Key. In other words, data AND access needs to use a single key. For example, an Account or User ID along which all data is organized and this SINGLE ID is used for almost all queries.
• The requirement for a single key for alignment and access also means that the data model may need to understand and relax normalization. (And here is where things start to fall apart if you are not careful). And here is where I need to introduce the CAP Theorem, which in short says "You can either chose "C"onsistency OR "A"vailability in the presence of network "P"artitioning". In the "Shared Nothing" world (in other words, a network partitioned world), you will have to give up some consistency to provide availability, and THAT is the new paradigm that needs to be embraced by the Oracle use community that is used to 100% consistency
• Oracle Sharding provides ACID within a shard (a major difference with other sharded databases from the NoSQL world), but not across Shard (Cross-shard). However, Oracle Sharding does provide a number of other features that makes Sharding easier - including support for Cross-Shard queries and "Duplicated" tables, Shard Catalog, Routing layer (very important!), etc.
• Shard management and making your application understand Sharding is quite difficult if you are retrofitting an existing Oracle based application (assuming it aligns to the requirements above). Oracle Sharding has made it simpler
• Events such as Adding/Removing a Shard requires data rebalancing - You mentioned sharding using MongoDB. I am not sure if you experienced a resharding - that would be a very fun event :-)
• Joins across Shards - This is not possible (as I said earlier, the data model and understanding of consistency needs a major overhaul)
• Access via non sharded key (a.k.a. Secondary keys) will require running Cross-Shard, Scatter-Gather type queries (which introduces its own set of caveats)
• As a result of these caveats, SQL has to be relatively simple (implies that the data model is also relatively simple. For example, I would NOT attempt Sharding for the Oracle EBS database :-). This has some implications outside the DBA world:
  • Applications need to have a greater awareness of the data model and its restrictions. Consistency checks need to be built into the applications
  • Once you build an application (and you should have built the data model first!), extending it to support new access patterns is pretty difficult
• There are more considerations but I am running out of time and patience to list them :-)

Having said that, here are a few points FOR Sharding, and for Oracle Sharding:

• Sometimes, Sharding is the ONLY option left and can be done, even if it is Hard.
• "Web scale" workloads NEED to use some form of Sharding to work at scale. So most web-based entities have used custom or natively sharded database technologies such as NoSQL. (As an aside, Mongo Sharding is not easy - there are other technologies that are far easier for Sharding compared to MongoDB, but that is for an entirely different thread)
• Oracle Sharding has understood, and catered for some of the most difficult pieces, among others, Transpaent and easier Resharding capabilities, Application layer routing, Out of box support for Shard catalog, Duplicated tables, Replication (both physical via Active Data Guard and logical via GoldenGate), Native SQL friendly syntax for Sharding operations, plus some others...
• Sharding is more "Cloud friendly" (because of RAC availability in the non-Oracle cloud space)
• While Sharding is generally a "Consistent Hash" based sharding, you can also use custom List and Range based sharding. These are also known as "Swim lane" based separation
• And an important point for Oracle Sharding is that it provides multi-level sharding (similar to the two level partitioning) - List/Range and the top, and Consistent Hash at the bottom, in addition to the Consistent hashing as the default. I don't believe any of the NoSQL solutions do that.
• Again, some more points that escapes my mind at this time....

Having said all this, I speak as someone who has some experience in NoSQL for the past three years. If you are interested in what I had to say about Couchbase (available in the public forum) via a recorded talk at Couchbase Connect 2014 at https://www.youtube.com/watch?v=4Gc7lXotQ1E and a presentation on custom sharding at eBay at http://www.nocoug.org/download/2014-11/NoCOUG_201411_Kanagaraj_Sharding.pdf

And before I wind up this long email, let me also mention a new product that Oracle introduced (Beta) at OOW - Oracle Velocity Scale Database. This is a Multi-server Oracle TimesTen cluster. I.e. now you can (in the future), use a SQL based (purely) In-Memory scale-out solution. This product is still in Beta and needs a lot more work including Cluster to Cluster replication. However, it does hold some promise in the future. In my mind, it is Oracle's answer to the "NewSQL" paradigm.

And if it feels like I was selling Sharding - the answer is No: I do NOT work for Oracle (and have never worked for Oracle Corp. in the past)

And for my old friends on Oracle-L (for the past 20 years?) - I have never left Oracle-L, but simply lurked in this list :-)

Regards,

John Kanagaraj <><
http://www.linkedin.com/in/johnkanagaraj http://jkanagaraj.wordpress.com (Sorry - not an Oracle blog!) ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **

On Fri, Sep 23, 2016 at 11:37 PM, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> Oracle 12.2 will, if the "new features" articles are to be believed,
> introduce sharding. Sharding is a technique from the "shared nothing"
> world. The way this is implemented in MongoDB, which is the only reference
> implementation that I know of, is to have several different databases, each
> containing a portion of the data, and running a part of the SQL, which will
> later be combined into the result.
>
> However, this introduces the "shared nothing" clustering, sort of DPF in
> the DB2 world. How does that get along with the RAC philosophy? In addition
> to NUMA systems like Solaris T5, now we will have sharding, yet another
> technology that competes with RAC. I wonder about RAC and how is it
> positioned. Obviously, cloud doesn't lend itself to RAC. So, what is the
> future of the RAC? Are we looking at the end of the RAC era?
>
>
>
> --
> Mladen Gogala
> Oracle DBA
> Tel: (347) 321-1217
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l