A database shard is a horizontal partition of data in a database or search engine. Each
individual partition is referred to as a shard or database shard. Each shard is held on a
separate database server instance, to spread load.
Some data within a database remains present in all shards, but some only appears in a
single shard. Each shard acts as the single source for this subset of data.
Database architecture Horizontal partitioning is a database design
principle whereby rows of a database table are held separately, rather than being split
into columns. Each partition forms part of a shard, which may in turn be located on a
separate database server or physical location. There are numerous advantages to this partitioning
approach. Since the tables are divided and distributed into multiple servers, the total
number of rows in each table in each database is reduced. This reduces index size, which
generally improves search performance. A database shard can be placed on separate hardware,
and multiple shards can be placed on multiple machines. This enables a distribution of the
database over a large number of machines, which means that the database performance
can be spread out over multiple machines, greatly improving performance. In addition,
if the database shard is based on some real-world segmentation of the data then it may be possible
to infer the appropriate shard membership easily and automatically, and query only the
relevant shard. Disadvantages include : A heavier reliance on the interconnect between
servers Increased latency when querying, especially
where more than one shard must be searched.Data or indexes are often only sharded one way,
so that some searches are optimal, and others are slow or impossible.
Issues of consistency and durability due to the more complex failure modes of a set of
servers, which often result in systems making no guarantees about cross-shard consistency
or durability. In practice, sharding is complex. Although
it has been done for a long time by hand-coding, this is often inflexible. There is a desire
to support sharding automatically, both in terms of adding code support for it, and for
identifying candidates to be sharded separately. Consistent hashing is one form of automatic
sharding to spread large loads across multiple smaller services and servers.
Where distributed computing is used to separate load between multiple servers, a shard approach
may also be useful. Shards compared to horizontal partitioning
Horizontal partitioning splits one or more tables by row, usually within a single instance
of a schema and a database server. It may offer an advantage by reducing index size
provided that there is some obvious, robust, implicit way to identify in which table a
particular row will be found, without first needing to search the index, e.g., the classic
example of the 'CustomersEast' and 'CustomersWest' tables, where their zip code already indicates
where they will be found. Sharding goes beyond this: it partitions the
problematic table(s) in the same way, but it does this across potentially multiple instances
of the schema. The obvious advantage would be that search load for the large partitioned
table can now be split across multiple servers, not just multiple indexes on the same logical
server. Splitting shards across multiple isolated
instances requires more than simple horizontal partitioning. The hoped-for gains in efficiency
would be lost, if querying the database required both instances to be queried, just to retrieve
a simple dimension table. Beyond partitioning, sharding thus splits large partitionable tables
across the servers, while smaller tables are replicated as complete units.
This is also why sharding is related to a shared nothing architecture—once sharded,
each shard can live in a totally separate logical schema instance / physical database
server / data center / continent. There is no ongoing need to retain shared access to
the other unpartitioned tables in other shards. This makes replication across multiple servers
easy. It is also useful for worldwide distribution of applications, where communications links
between data centers would otherwise be a bottleneck.
There is also a requirement for some notification and replication mechanism between schema instances,
so that the unpartitioned tables remain as closely synchronized as the application demands.
This is a complex choice in the architecture of sharded systems: approaches range from
making these effectively read-only, to dynamically replicated tables and many options in between.
Support for shards Apache HBase
HBase supports automatic sharding. Azure SQL Database Elastic Scale
Elastic Scale enables the data-tier of an application to scale out and in via industry-standard
sharding practices CUBRID
CUBRID supports sharding from version 9.0 dbShards
CodeFutures dbShards is a product dedicated to database shards.
ElasticSearch ElasticSearch enterprise search server provides
sharding capabilities. eXtreme Scale
eXtreme Scale is a cross-process in-memory key/value datastore. It uses sharding to achieve
scalability across processes for both data and MapReduce-style parallel processing.
Hibernate ORM Hibernate Shards provides support for shards,
although there has been little activity since 2007.
IBM Informix IBM supports sharding in Informix since version
12.1 xC1 as part of the MACH11 technology. Informix 12.10 xC2 added full compatibility
with MongoDB drivers, allowing the mix of regular relational tables with NoSQL collections,
still supporting sharding, failover and ACID properties.
MongoDB MongoDB supports sharding from version 1.6
MySQL Cluster Auto-Sharding: Database is automatically and
transparently partitioned across low cost commodity nodes, allowing scale-out of read
and write queries, without requiring changes to the application.
MySQL Fabric is part of MySQL utilities and includes support for sharding.
OrientDB OrientDB supports sharding from version 1.7
Plugin for Grails Grails supports sharding using the Grails
Sharding Plugin. Ruby ActiveRecord
Octopus works as a database sharding and replication extension for the ActiveRecord ORM.
ScaleBase's Data Traffic Manager ScaleBase's Data Traffic Manager is a software
product dedicated to automating MySQL database sharding without requiring changes to applications.
Shard Query Open Source parallel query engine for MySQL.
Solr Search Server Solr enterprise search server provides sharding
capabilities. Spanner
Spanner is Google's global scale distributed database that shards data across multiple
Paxos state machines to scale to "millions of machines across hundreds of datacenters
and trillions of database rows". SQLAlchemy ORM
SQLAlchemy is an object-relational mapper for the Python programming language that provides
sharding capabilities. SQL Azure
Microsoft supported sharding in SQL Azure through "Federations". In 2014, support for
the under-used Federations was dropped. Although Azure users were using sharding, they were
choosing to implement it through application-specific custom approaches, rather than the automatic
approach of Federations. Disadvantages of sharding
Sharding a database table before it has been optimized locally causes premature complexity.
Sharding should be used only when all other options for optimization are inadequate. The
introduced complexity of database sharding causes the following potential problems:
Increased complexity of SQL - Increased bugs because the developers have to write more
complicated SQL to handle sharding logic. Sharding introduces complexity - The sharding
software that partitions, balances, coordinates, and ensures integrity can fail.
Single point of failure - Corruption of one shard due to networksystems problems causes
failure of the entire table. Failover servers more complex - Failover servers
must themselves have copies of the fleets of database shards.
Backups more complex - Database backups of the individual shards must be coordinated
with the backups of the other shards. Operational complexity added - Adding/removing
indexes, adding/deleting columns, modifying the schema becomes much more difficult.
These historical complications of do-it-yourself sharding are now being addressed by independent
software vendors who provide autosharding solutions.
See also Shared nothing architecture
References
External links Informix JSON data sharding