This article is written by friend a fei, and the author agrees to send the original! Alfy Javaer, reprint please note the original source, thank you!

specifications

Such as order table and user table, which will have a future scale of hundreds of millions or even billions of billions of massive data tables, in the early stage of the project, in order to quickly go online, it is generally designed as a single table, without considering the sub-database and sub-table. With the development of the business, the single table capacity more than never even reached billion level, then depots table needs to be considered this problem, and migration of non-stop depots table, this should be the most basic needs depots table, after all, Internet project can’t hang a billboard “tonight 10:00 ~ 10:00 system downtime” the next day, this much low ah, When you talk to the interviewer about this plan, what does the interviewer think?

Using codis

The author has just encountered this problem and realized the non-stop sub-database and sub-table migration scheme based on some ideas of CODIS. Rebalance: CoDIS is not the focus of this article. Here’s what to learn from coDIS –rebalance:

When data access occurs during migration, the Proxy sends the SLOTSMGRTTAGSLOT migration command to Redis to forcibly migrate the Key to be accessed by the client immediately and then process the request from the client. (SLOTSMGRTTAGSLOT is customized by CODIS based on Redis)

Depots table

After understanding this scheme, it is easier to understand the non-stop sub-database sub-table migration. Next, the author introduces the implementation scheme of installed_APP table in detail. That is, the installed APP information table of the user;

1. Determine sharding Column

Determining sharding column is absolutely the most important part of the database and table. Sharding column directly determines whether the whole sub-database sub-table scheme can be successfully landed; The selection of an appropriate Sharding column can basically enable the majority of traffic interfaces related to this table to access the single table behind the sub-database and sub-table through this Sharding column, without the need for cross-database and cross-table. The most common Sharding column is user_id. The note also selects user_id;

2. Separate database and table scheme

After selecting the most appropriate Sharding column according to its own business, it is necessary to determine the database and table scheme. The author adopts a scheme combining active and passive migration:

  1. Active migration is a standalone program that iterates through the installed_APP table that requires a sub-table and migrates data to the target table behind the sub-table.

  2. Passive migration is when the business code associated with the Installed_APP table migrates its data to the corresponding table behind the sub-database sub-table.

These two scenarios are described in detail next;

2.1 Active Migration

Active migration is an independent external migration process, its purpose is to traverse the need installed_app depots table table, the data is copied to the depots in the target table after table, due to the migration of active and passive migration will run together, so you need to deal with the issue of migration of active and passive migration collision, the author of the active migration pseudo code is as follows:

Public void migrate(){long maxId = execute("select Max (ID) from installed_app"); long tempMinId = 0L; long stepSize = 1000; long tempMaxId = 0L; do{ try { tempMaxId = tempMinId + stepSize; // According to InnoDB index feature, where id>=? and id<? String scanSql = "select * from installed_app where ID >=#{tempMinId} and ID <#{tempMaxId}"; List<InstalledApp> installedApps = executeSql(scanSql); Iterator<InstalledApp> iterator = installedApps.iterator(); while (iterator.hasNext()) { InstalledApp installedApp = iterator.next(); // help GC iterator.remove(); long userId = installedApp.getUserId(); String status = executeRedis("get MigrateStatus:${userId}"); if ("COMPLETED".equals(status)) { // migration finish, nothing to do continue; } if (" upgrading ". Equals (status)) {// "upgrading", nothing to do continue; } // Get the lock before migration: set MigrateStatus:18 MIGRATING ex 3600 nx String result = executeRedis("set MigrateStatus:${userId} MIGRATING ex 86400 nx"); If ("OK".equals(result)) {if ("OK".equals(result)) { String SQL = "select * from installed_app where user_id=#{user_id}"; List<InstalledApp> userInstalledApps = executeSql(sql); ShardingInsertSql (userInstalledApps); ExecuteRedis ("setex MigrateStatus:${userId} 864000 COMPLETED"); } else {// If the lock is not acquired, the passive migration has acquired the lock, then the migration can be transferred to the passive migration. Logger. Info ("Migration conflict. userId = {}", userId); If (tempMaxId >= maxId) {maxId = execute("select Max (id) from installed_app"); } logger.info("Migration process id = {}", tempMaxId); }catch (Throwable e){// If there is any exception during execution (this exception can only be thrown by Redis and mysql), then exit, Fix the problem and migrate // and set tempMinId to logger.info("Migration Process ID ="+tempMaxId); The id of the last log record to prevent repeated migrations system.exit (0); } tempMinId += stepSize; }while (tempMaxId < maxId); }Copy the code

Here are a few things to note:

  1. The first step to query Max (id) is to minimize the number of queries for Max (ID). If the first query Max (ID) is 10000000, there is no need to query Max (id) again until the id traversed reaches 10000000.

  2. According to the id > =? and id
    =? Limit n or limit m, n traversal, because limit performance is mediocre and deteriorates as traversal progresses. The id > =? and id

  3. Iterator

    , remove the userId after each iteration, otherwise GC exception may occur, or even OOM may occur.

2.2 Passive Migration

Passive migration means that the migration logic is inserted before the normal service logic related to the Installed_APP table. Taking the APP installed by the new user as an example, its pseudocode is as follows:

// The passive migration method is common logic, so it is required to be called before all business logic related to the 'installed_app' table; public void migratePassive(long userId)throws Exception{ String status = executeRedis("get MigrateStatus:${userId}"); If ("COMPLETED". Equals (status)) {// The user data was migrated. nothing to do logger.info("user's installed app migration completed. user_id = {}", userId); }else if ("MIGRATING".equals(status)) {// "MIGRATING", wait until the migration is complete; To prevent dead loops, increase the maximum wait time logic do{thread.sleep (10); status = executeRedis("get MigrateStatus:${userId}"); }while ("COMPLETED".equals(status)); String result = executeRedis(" Set MigrateStatus:${userId} MIGRATING EX 86400 Nx "); If ("OK".equals(result)) {if ("OK".equals(result)) { String SQL = "select * from installed_app where user_id=#{user_id}"; List<InstalledApp> userInstalledApps = executeSql(sql); ShardingInsertSql (userInstalledApps); ExecuteRedis ("setex MigrateStatus:${userId} 864000 COMPLETED"); }else {// If the lock is not acquired, the lock should be acquired elsewhere first and is being migrated. Add services related to the 'Installed_APP' table. APPpublic void addInstalledApp(InstalledApp InstalledApp) throws new user Exception{// migratePassive(installedapp.getUserId ())); ShardingInsertSql (installedApp); // insert the user's installedApp information (installedApp) into the target table after the sub-table. }Copy the code

In either case, check MigrateStatus:${userId} in the cache:

  1. If the value is COMPLETED, it indicates that the migration is COMPLETED. In this case, move the request to the table next to the sub-database and sub-table for processing.

  2. If the value is MIGRATING, the migration process is in progress. You can cycle until the value is COMPLETED, and then transfer the requests to the tables following the database and tables for processing.

  3. Otherwise, the value is null, then try to acquire the lock and migrate the data. After the migration is COMPLETED, the cache value is updated to COMPLETED. Finally, the request is transferred to the table next to the sub-database and sub-table for processing.

3. Perfect scheme

After all data migration is complete, CRUD operations are determined based on MigrateStatus:${userId} in the cache. This step is unnecessary after data migration is complete. After all data migration is complete, the value of this switch will be sent in a similar way to TOPIC. After all services receive the TOPIC, the switch will be local cache. MigrateStatus:${userId} MigrateStatus:${userId} MigrateStatus:${userId}

4. Legacy work

After the migration is complete, the active migration program is offline and migratePassive() is removed from the passive migration program. In addition, some third-party database and table middleware, such as Sharding-JDBC, can be integrated. For details, see the Actual sharding-JDBC integration

review

Reviewing this scheme, the biggest disadvantage is that passive migration may take a long time if the total number of records for sharding columns (such as userId) is large and active migration is in progress.

However, according to DB performance, generally the batch insertion of 1000 data is at 10ms level, and the records of the same Sharding column belong to only one table after they are divided into tables in the database, without involving cross-table. Therefore, as long as there is no such abnormal Sharding column in the table to be migrated through SQL statistics before migration, it can be safely migrated;

When I migrated the Installed_APP table, users had no more than 200 apps at most, so there was no need to worry too much about performance issues caused by collisions. There is no universal plan, but there are suitable for their own plan;

If there are sharding columns with tens of thousands of records, these Sharding columns can be cached first, and the migration program will go online at night. The data of these cached Sharding columns can be migrated first, so as to reduce the experience of the migration program for these users as much as possible. Of course, you can use a better solution that you come up with.

If you feel that you have gained something after reading it, please click “like”, “follow” and add the official account “Ingenuity Zero” to check out more wonderful history!!