Data Migration Experience Summary--the data migration work of multi-table heterogeneous

As a result of the system revision, the last three months in the data migration work, due to the special business, the basic data migration can trample on the pits are trampled, decided to do a good summary.

Migration type--historical data with large changes in the structure of new and old system tables

I. Core ISSUES

1. The new cousin structure has changed enormously. The new table is based on deliver, and also involves a table of warehousing systems, 4 tables of the order system, and is migrated according to the new logical mapping relationship.
2. Incremental data migration. There must be new data at full data migration, which should be migrated in real time
3. Data performance, efficiency of the million-level considerations. Due to the importance of the order business, the QPS of data migration is very stressful for the database and requires constant test iterations to find a suitable migration efficiency and performance.
4. Old data format issues. 12, 13 of data because of a long history, there will be missing data, the problem of incomplete data, these problems should be in the process of fault tolerance.
5. It is difficult to check the data. Because the new table involves more than one old table and the data volume is very large, data reconciliation is difficult, and now write the script by week and according to a certain logic to check the data, while supplemented by the naked eye.

Second, the data migration process

Full-volume Synchronous Java program

Incremental Sync Otter + Extended Java program

Historical data needs to be synchronized to back-end backend and front-end Cobar libraries

1. Historical data is downloaded into multiple texts by year (a text of around 2g)

2. Multithreaded read text data

3. The processing type is mainly Insert Batch_insert Update, depending on the type, the thread calls different logical methods to process the data and write to the new table

Iii. Commencement of discussion

Expand the above questions and summarize the technical issues

1. Project Architecture Level

The migration project uses Java development, only as a migration, positioning lightweight, back-end db with JdbcTemplate, Spring management JavaBean is divided into three layers, a layer of manager mainly on the flow of data based on the type of distribution to different business logic to deal with a layer of business logic-- A layer of DAO that handles mapping relationships.

2.db related issues

Background db is a master multiple from which the master is used to accept write requests, both from read requests. Performance from library is much lower than the main library

2.1 Problems with calling interface fetching data

At the beginning of the work, because want to make the project very light, get old data in the form of interface, call Order interface, WMS interface to obtain the corresponding data. But the interface is very fragile, the statistics HTTP interface can only resist 500-1000 or so requests per second, large data volume under the interface quickly become a bottleneck. I tested it once, 100 million data takes 2 weeks to finish, so you have to discard the calling interface and read DB yourself to query.

2.2 Hot and cold storage problems

Most of the historical data in the cold storage, in the DB query is the need to check the hot library and then check the cold storage, and follow the corresponding logic to check the library.

2.3 Batch Insert master-slave delay problem

Start data processing One insert, so that 100 million data to produce 100 million insert statements. For the main library write is not affected, but from the library has a big problem, its performance and the main library is far-reaching, while the synchronization binlog will have a great delay, 100 million insert means 100 million times commited 100 million commits binlog synchronization will be 100 million network transmission, Inserts are also performed 100 million times from the library. This delay also creates a bigger problem because the main library has other business data written to it, no real-time synchronization from the library, and the customer service cannot find the order.

Later, the INSERT statement is changed to multiple value, each processing a batch of data, a commit commit. This improves insert insertion performance and resolves latency issues.

JdbcTemplate changes to the Updatebacth () method, but finds that the SQL statement is still an insert, which is how JDBC supports batch insert and requires a parameter in the JDBC connection jdbc:mysql ://.....&rewritebatchedstatements=true;

2.4 SQL Dead Connection

Multithreading read data, found that several threads have been blocked, after Jstack, found that MySQL sokcetavaiable, this error generally occurs in the connection front Cobar. This is because the JDBC connection does not set the connection timeout, to add Connecttime sockettime, this is the network time-out of the application connection database, if not set up will be waiting.

Setting Parameters: &failoverreadonly=false&connecttimeout=60000&sockettimeout=60000 failOverReadOnly The JDBC default timeout retry becomes read-only and must be prefixed with the failoverreadonly parameter

3. Program-Level optimization

3.1 Multi-threaded solutions for full-volume data

Single-process migration, 100 million data takes about 1 weeks, time and efficiency are too low, so think instead of multithreading. In fact, multi-threading is very simple, historical data is the form of text, only need to read the text one at a time, concurrent processing can be, while the use of countdownlatch to ensure that the thread is complete after the exit of the main program.

3.2 Multi-threaded solution for incremental data

Otter will order the consumption of binlog, the consumption process is very fast, but if the single process mode, the bottleneck will be stuck in my writer, so the program is multithreaded. Otter Read 2000 Binlog records each time, the main process once obtained full 2000, and then start multithreading to process these records concurrently. Also need to pay attention to the problem, it is possible that the same time period for a record has multiple changes, the need to take the last change, so I will the number of threads the most barrels, according to the remainder of order_id and bucket into the corresponding bucket, while the value is a map (order_id, Row object) , which ensures that each batch is up-to-date.

Extension of the 3.3 otter

Later I will write a blog post Analysis Otter, here first a brief introduction. Otter is an open source project of Ali, mainly solves the problem of real-time data backup in the remote computer room, its principle is to read the master's Binlog and then parse into the EventData object and write the new table by disguising itself as slave. It is only supported by default for one-to-one replication, and the table's field mapping logic is very simple to replicate. But Ali's project is strong, it provides users with an interface, after inheritance can follow their own logic to synchronize data. The inheriting class is Abstracteventprocessor, and this class accepts the EventData object (that is, Binlog), which has all the field values, SQL types, and so on for the table you are subscribing to. After inheritance, in this class of methods to introduce their own synchronization program, so that the otter subscription data will enter its own program.

4. Data Remediation Strategy

There are still some problems after data migration:

1. During the incremental process, the canal will retry the Binlog read due to network and other reasons, but it seems that the data at that point in time will still be lost.

2. During the migration process, there are some point-in-time errors that result in data loss or anomalies.

Because the results of the old and new tables are very different, the amount of data is too large, you can not simply use a SQL, this will drag the library. So I write the program week by week to check the data, mainly check the new table the old table does not have, these need to be deleted; There are no new tables for older tables, these need to be added; the old table status of the new table does not match those that need to be updated according to the old table.

Summary:

1. Performance--before the migration of MySQL performance, the size of the data does not have a certain understanding of the interface to obtain data after the use of Read Library multi-threading, one after the waste of more than half a month of time, then do the migration must consider good data volume, migration time, table results, MySQL performance.

2. Code writing-The program coupling is too serious, each additional type of operation in the late, the original code to add a lot of judgment, the next time to write must be interface-oriented programming, using inheritance and factory method (refer to the second Kill project); Jdbctample is very lightweight, more than a single SQL, the amount of code will still come up, The next time to do a good assessment, with mybatis; Check scripts and execution scripts too much, log printing too much, the next time you can use log4j to output multi-text, and script to write more elegant and concise.

3. Check the data--data migration is a dirty live dirty, this migration has more than half time in the data and the right, non-stop write SQL, constantly look at the calibration data. In the future, a similar migration must be planned first, how to check, for example, choose a few key fields to do hash.

Reference documents:

1. "Methods, steps and insights on data migration" http://m.blog.csdn.net/blog/baoqiangwang_11109/5492910

2. JDBC-driven rewritebatchedstatements parameter http://www.cnblogs.com/chenjianjx/archive/2012/08/14/2637914.html

3. In-depth understanding of JDBC Timeout settings http://www.importnew.com/2466.html

4. Otter Https://github.com/alibaba/otter