This Banner is For Sale !!
Get your ad here for a week in 20$ only and get upto 15k traffic Daily!!!

F6 Automobile Technology’s Multimillion Rows of Data Sharding Strategy Based on Apache ShardingSphere

F6 Automobile Technology is an Web platform firm specializing in the informatization of the automotive aftermarket. It helps automotive restore corporations (shoppers) construct their sensible administration methods to digitally remodel the auto aftermarket.

The info of various auto restore corporations will definitely be remoted from one another, so theoretically the information may be saved in several tables of various databases. Nonetheless, fast-growing enterprises face growing information quantity challenges: typically complete information quantity in a single desk could method 10 million and even 100 million entries.

This subject positively challenges enterprise development. Furthermore, rising enterprises at the moment are additionally planning to separate their methods into many microservices primarily based on domains or enterprise sorts, and accordingly, completely different databases are vertically required for various enterprise instances.

Why Did We Want Information Sharding?

Relational databases are bottlenecks with regards to storage capability, connection depend, and processing capabilities.

First, we all the time prioritize database efficiency. When the information quantity of a single desk reaches tens of tens of millions, and there are a comparatively giant variety of question dimensions, system efficiency would nonetheless show unsatisfactory even when we added extra slave databases and optimize indexes. This meant it was time for us to contemplate information sharding.

The aim of information sharding is to scale back database load stress and question time. Moreover, since a single database typically has a restricted variety of connections, when Queries Per Second (QPS) indicator of the database is just too excessive, database sharding is actually wanted to share connection stress.

Second, to make sure availability was one other vital cause. If sadly, an accident happens in a single database, we’d probably lose all information and additional have an effect on all companies. Database sharding can reduce threat and the detrimental impression on enterprise companies. Usually, when the information quantity of a desk is bigger than 2GB or the variety of information rows is bigger than 10 million, to not point out that the information can also be rising quickly, we’d higher use information sharding.

What’s Information Sharding?

There are 4 frequent forms of information sharding within the trade:

Vertical desk sharding: break up massive tables into small ones, field-based, which suggests much less often used or comparatively lengthy fields are break up into prolonged tables.

Vertical database sharding: business-based database sharding is used to unravel efficiency bottlenecks of a single database.

Horizontal desk sharding: distribute tables’ information rows into completely different tables in keeping with some guidelines in an effort to lower the information quantity of single tables and optimize question efficiency. When it comes to the database layer, it nonetheless faces bottlenecks.

Horizontal information sharding: primarily based on horizontal desk sharding, distribute information into completely different databases to successfully enhance efficiency, decrease stress of stand-alone machine and single databases, and break the shackles of I/O, connections, and {hardware} sources.

Wonderful Information Sharding Options

1. Apache ShardingSphere-JDBC


  • Supported by an energetic open supply neighborhood. Now, Apache ShardingSphere 5.0 model has been launched and the event iteration pace is quick.
  • Confirmed efficacy by many profitable enterprise software instances: massive corporations comparable to JD Know-how and have utilized ShardingSphere.
  • Straightforward deployment: Sharding-JDBC may be rapidly built-in into any mission with out further companies deployed.
  • Wonderful compatibility: it may well path to a single information node and completely assist SQL.
  • Wonderful efficiency and low loss: check outcomes may be discovered on the Apache ShardingSphere web site.


Potential improve in operations and upkeep prices, and troublesome area adjustments and index creation after information sharding. To repair the difficulty, customers must deploy Sharding-Proxy that helps heterogeneous languages and is extra pleasant to DBAs.

To date, the mission doesn’t assist information shards dynamic migration but. Subsequently, function implementation is required.

2. MyCat


  • MyCat is a middleware positioned between purposes and databases to deal with information processing and interactions. It can’t be perceived throughout growth, and integrating MyCat doesn’t price a lot.
  • Use JDBC to attach databases comparable to Oracle, DB2, SQL Server, and MySQL.
  • Help a number of languages plus simple deployment and implementation throughout completely different platforms.
  • Excessive availability and auto-switch triggered by a crash.


  • Excessive operations and upkeep prices: to make use of MyCat, it’s required to configure a collection of parameters plus HA load balancer.
  • Customers should independently deploy the service, which can improve system dangers.

After all, there are related options comparable to Cobar, Zebra, MTDDL, and TiDB however truthfully, we didn’t spend a lot time researching different options, as a result of we determined to make use of ShardingSphere as we felt it meets the corporate’s wants.

F6 Car Know-how’s General Plan

Based mostly on our firm’s enterprise mannequin, we selected Shopper ID as Sharding Key to make sure that work order information of 1 consumer is saved in the identical single desk of the identical client-specific database. Subsequently, efficiency loss attributable to multi-table correlated queries is averted; plus later, even when multi-databases sharding is required, cross-database transactions and cross-database JOIN may be averted.

Amongst consumer ID databases, the kind BIGINT(20) applies UID (Distinctive Identification Quantity, or we name it “gene” ) to make sure potential database scaling sooner or later; the final two digits of a consumer ID are its UID, so in keeping with the double scaling rule, the utmost reaches 64 databases. The values of left bits can be utilized for desk sharding, which may be break up into 32 sharding tables.

Take 10545055917999668983 because the consumer ID instance and the foundations are proven as follows:

105450559179996689 83
Desk sharding uid worth % 32 database sharding uid worth % 1
Enter fullscreen mode

Exit fullscreen mode

The final two digits (i.e. 83) are used for database sharding, of which non permanent information is barely sharded into the library f6xxx, so the rest is 0. Later, growing information quantity may be expanded to a number of libraries. The remaining worth 105450559179996689 is used for desk sharding. At first time, it’s divided into 32 single tables so the modulo remainders correspond to the precise sharding desk subscripts are 0~31.

Image description

On condition that the enterprise system is rising and we undertake speedy iteration methodology to develop options step-by-step, we plan to shard tables first after which do database sharding.

Information sharding has an ideal impression on the system, so we’d like greyscale launch— if sadly, a problem happens, the system can rapidly begin roll-back, to make sure a functioning enterprise system. The implementation particulars are given beneath:

Desk Sharding

  1. Swap from JDBC to Sharding-JDBC to attach information sources
  2. Decouple the write databases, after which migrate codes
  3. Synchronize historic information and incremental information
  4. Swap sharding tables

Database Sharding

  1. Migrate the read-only databases
  2. Information migration
  3. Swap read-only databases
  4. Swap write-only databases

Desk Sharding Particulars

Variety of Sharding Tables

Within the trade, the information of a single desk ought to often be restricted to five million rows, and the variety of sharding tables needs to be an influence of two to make them scalable. The precise variety of sharding tables is calculated primarily based on enterprise growth pace and future information improve in addition to the long run information archiving plan. After sharding desk depend and sharding algorithms are outlined, it’s OK to evaluate the present information quantity in every sharding desk.


– Change the database & auto desk ID generator

After desk sharding, we might not use the auto database ID generator anymore, so we needed to discover a possible resolution. We had two plans:

Plan 1: Use different keys comparable to snowflake

Plan 2: Implement an incremental element (database or Redis) all by ourselves

Image description

After evaluating the 2 options and the enterprise situation, we determined to decide on Plan 2 and concurrently, offered a brand new complete table-level ID generator resolution.

– Test whether or not all requests are carried with shard keys

Now, the microservice visitors entrances embrace:

  • HTTP
  • Dubbo
  • XXLJOB scheduled job
  • Message Queue (MQ)

After desk sharding, to rapidly find information shards, all requests should carry their shard keys.

– Decoupling

  1. Decouple enterprise methods of every area and use interfaces to work together with learn and write information.

  2. Take away Direct Desk JOIN and use interfaces as an alternative.

The largest downside introduced by the decoupling is the distributed transaction downside: how to make sure information consistency. Normally, builders introduce distributed transaction elements to make sure transaction consistency or they use compensation or different mechanisms to make sure closing information consistency.

Grayscale Launch Plan

In an effort to guarantee fast roll-back when issues attributable to new function releases happen, all on-line modifications are launched step-by-step primarily based on shoppers. Our grayscale launch plan is proven as follows:

Plan 1: Preserve two units of Mapper interfaces: one makes use of Sharding-JDBC information sources to connect with databases whereas the opposite makes use of JDBC information sources to connect with databases. On the service layer, it’s vital to pick one of many two interfaces primarily based on the choice workflow diagram beneath:

Image description

Nonetheless, the answer causes one other downside: all codes visiting the Mapper layer have an if else department, leading to main enterprise code adjustments, potential code intrusion, and tougher code upkeep. Subsequently, we discovered one other resolution and we name it Plan 2.

Plan 2 — Adaptive Mapper Choice Plan: one set of Mapper interface is with two information sources and two units of implementations. Based mostly on the grayscale configuration, completely different consumer requests will undergo completely different Mapper implementations, and one service corresponds to 2 information sources and two units of transaction managers, and primarily based on the grayscale configuration, completely different shoppers’ requests go to completely different transaction managers. Accordingly, we leverage a number of Mapper scanners of MyBatis to generate a number of mapperInterfaces, and concurrently generate a mapperInterface for wrapping. The wrapper class helps hintManagerto robotically choose mappers; the transaction supervisor is much like wrapper class technology. The wrapper class helps hintManager to robotically choose numerous transaction managers to handle transactions. This resolution truly avoids intrusion as a result of for codes of the service layer, there is just one Mapper interface.

Image description

Information Supply Connection Swap

Apache ShardingSphere already lists some grammars at the moment not supported by Sharding-JDBC on its web site, however we nonetheless discovered the next problematic SQL statements that Sharding-JDBC parser can not deal with:

  • A subquery with out a shard key.
  • Not assistInsert statements whose values embrace forged ifnull now and different features.
  • By default, choose for replace goes to the slave database (the difficulty has fastened since 4.0.0.RC3).
  • Sharding-JDBC doesn’t assist the assertion ResultSet.first () of MySqlMapper with Optimistic Concurrency Management used to question imaginative and prescient.
  • No such assertion for batch updates.
  • Even when UNION ALL doesn’t assist the grayscale launch plan, we solely want to repeat a set of mapper.xml, and modify it primarily based on the syntax of Sharding-JDBC earlier than launch.

Image description

Historic Information Sync

DataX is Alibaba Group’s offline information synchronization instrument that may successfully sync heterogeneous information sources comparable to MySQL, Oracle, SqlServer, Postgre SQL, HDFS, Hive, ADS, HBase, TableStore(OTS), MaxCompute(ODPS) and DRDS.

The info synchronization framework DataX can summary the synchronization of various information sources as a Reader Plugin that reads information from the information supply, after which as a Author Plugin that writes information to the goal. In concept, the DataX framework can assist information synchronization of all information supply sorts. Moreover, the DataX plugin ecosystem can permit each newly-added information supply to instantly work together with the outdated information sources.

Image description

Confirm Information Synchronization

  • Use timed duties to match the variety of information of the unique desk and of the sharding desk.
  • Use timed duties to match the values of key fields.

Learn/write Splitting and Desk Sharding

Earlier than learn/write splitting, we would have liked to configure incremental information synchronization first.

-Incremental Information Synchronization

We used one other open supply distributed database sync mission named Otter to synchronize incremental information. Based mostly on database incremental log parsing, otter can synchronize information of MySQL/Oracle databases of the native pc room or the distant pc room. To make use of Otter, we would have liked to pay further consideration to the next ideas:

  • For MySQL databases, customers should have binlog enabled, and set its mode as ROW.
  • The person should have the question permission of binlog so they should apply for that as an otter person.
  • Now, the binlog of DMS database is saved for less than 3 days. In Otter, customers can outline the beginning place of binlog synchronization and the start line of incremental synchronization by themselves: first, choose slave-testDb on the SQL platform, and use the SQL assertion “present grasp standing” to question.

Observe: the execution outcomes of present grasp statusof grasp and slave could also be completely different, so should you set it, it’s good to get the execution results of the grasp database. We predict this perform is basically helpful as a result of when Otter’s information synchronization fails, we will reset factors and synchronize once more from the start.

  • When Otter is disabled, it’s going to robotically file the final level of synchronization, and proceed to synchronize information from this level subsequent time.
  • Otter permits builders to outline their very own processing course of. For instance, we will configure information routing guidelines and management the route of consumer information synchronization information from subtable to dad or mum desk or vice versa.
  • Disabling Otter won’t invalidate the cache outlined within the user-defined Otter processing course of. To repair it, the answer is to change the code remark and reserve it.

  • Learn/write Splitting Plan

Our greyscale change plan is proven beneath:

Image description

We selected the grayscale launch resolution, which suggests it was vital to make sure real-time information updates in each subtables and dad or mum tables. Subsequently, all information was synchronized in two instructions: for shoppers with grayscale launch being on, reads and writes went to subtables and the information was synchronized to dad or mum tables in actual time by way of Otter, whereas for shoppers with grayscale launch being off, reads and writes went to dad or mum tables and the information is synchronized to subtables in actual time by way of Otter.

Image description

Database Sharding Particulars


– Main Key
Main key needs to be auto increment sort (sharding tables needs to be international auto increment) or entry the one major key numbering service (server_id unbiased of DB). Desk auto increment major key technology or uuid_short generated key require switching.

– Storage Process, Capabilities, Set off and EVENT
Attempt to take away them first if current; in the event that they can’t be eliminated, create them prematurely in new databases.

– Information Synchronization
Information synchronization makes use of DTS or sqldump (historic information) + otter (incremental information) for synchronization.

– Database Change Process
To keep away from potential efficiency and compatibility issues, database change plan should comply with two criterion:

  • Greyscale switching: visitors is progressively switching to RDS (Alibaba Cloud Relational Database Service, a.ok.a. RDS), permitting obervance of database efficiency at any time.
  • Fast Rollback: reaching fast reversion when issues happen with little impression on person expertise.

Standing Quo: 4 software situations +one grasp db and two slave db

Image description

Step 1: add a brand new software occasion and change it to RDS, write into or go dms grasp database, and the information in dms grasp database might be synced to rds in actual time

Image description

Step 2:add three extra software situations, and reduce 50% of the information to put in writing into rds database

Image description

Step 3: take away the 4 unique occasion visitors, and browse them into rds situations whereas writing nonetheless goes into dms grasp database

Image description

Step 4: change the grasp database into rds, and rds information might be reversely synced to dms grasp database to make it simpler for the short rollback of the information

Image description

Step 5: Completion

Image description

Every step talked about above may be rapidly rolled again by way of visitors switching in order to make sure the provision and stability of system.

Sharding & Scaling

When the efficiency of a single database reaches a plateau, we will scale out the database by modifying sharding database routing algorithms and migrating information.

Image description

When the capability of a single desk reaches its most measurement, we will scale out the desk by modifying sharding desk routing algorithms and migrating information.

Image description


Q: Typically, Otter receives binlog information however the information can’t be discovered within the database?

A:To make our MySQL appropriate with replicas of different non-transactional engines, we added binlog on the server layer. Binlog can file all engine modification operations, so it may well assist replication perform for all engines. The issue is a possible inconsistency between redo log and binlog however MySQL makes use of its inner XA mechanism to repair the difficulty.

Step 1:Not carry out operations on MyISAM put together, write/sync redo log and binlog.

Step 2: First, write/sync Binlog after which MyISAM commit (commit in reminiscence).

After all, group commit has been added since model 5.6. The event improves I/O efficiency to some extent, but it surely doesn’t change the execution order.

After write/sync Binlog is finished, the binlog has been written, so MySQL considers that the transaction has been dedicated and persevered (now, the binlog is able to be despatched to subscribers). Even when a database crashes, the transaction can nonetheless be recovered appropriately after MySQL reboot. Nonetheless, earlier than this step, any operation failure could trigger transaction rollback.

MyISAM commit is centered on reminiscence commit comparable to killing locks, learn views associated to multiversion concurrency management launch. MySQL believes that no errors happen on this step — as soon as an error actually happens, the database will crash — MySQL itself can not deal with the crash. This step doesn’t have any logic that causes transaction rollback. When it comes to program operations, solely after this step is accomplished, the adjustments attributable to the transaction may be proven by way of the API or queries on the client-side.

The explanation why the issue could happen is that the binlog is distributed first, after which db commit is finished. We use question retries to repair this subject.

Q: In the case of multi-table queries, typically, why some tables can not get information?

A:The grasp/slave routing technique of Sharding-JDBC is proven beneath:

Grasp databases are chosen within the following situations:

  • SQL statements that embrace lock comparable to choose for replace ( of Model 4.0.0.RC3);
  • Not SELECT statements;
  • Threads which have already gone to grasp databases;
  • Codes specify the requests for grasp databases.

Algorithms used to select from a number of slave databases:

  • Polling Technique
  • Load Balancer Technique

The default is the polling technique. Nonetheless, one question could go to completely different slave databases, or it could go to the grasp library and slave databases, which happens when there may be time inconsistency between master-slave database latency or multi-slave latency.

Q: How can we take away community visitors?

  • http: use nginx to take away upstream;
  • dubbo: leverage its qos module to execute offline/on-line command;
  • xxljob: manually enter execution IP of the executor to specify situations;
  • MQ: use the API offered by Alibaba Cloud to allow or disable shopper bean.

Apache ShardingSphere Open Supply Mission Hyperlinks:

ShardingSphere Github
ShardingSphere Twitter
ShardingSphere Slack Channel

The Article was Inspired from tech community site.
Contact us if this is inspired from your article and we will give you credit for it for serving the community.

This Banner is For Sale !!
Get your ad here for a week in 20$ only and get upto 10k Tech related traffic daily !!!

Leave a Reply

Your email address will not be published. Required fields are marked *

Want to Contribute to us or want to have 15k+ Audience read your Article ? Or Just want to make a strong Backlink?