Alicloud DRDS usage drops

In 2017, the summary records of Alibaba cloud DRDS service were used. At that time, there was a large amount of data. If the database and table were not divided, the single vehicle table would exceed 100 million, which will continue to increase in the future. Therefore, when planning the system, it was considered that if there were hundreds of millions, MySQL could not support it at all, and the speed would be slow, This allows us to consider the solution when designing.

Another detail is that there are many fields in the vehicle. Users need to support multi condition query, and the combination of query conditions is irregular, which makes it difficult to solve the query problem directly by using the index at the database level. Therefore, it was planned to solve the problem in two steps:

  1. Upper sub warehouse sub table
  2. For conditional query, you do not need to query the database, but through Alibaba cloud's opensearch service, which is implemented based on luceue and elastic   search is similar. How to synchronize data from Mysql to opensearch does not need to be implemented at the business level, but by monitoring MySQL binlog and automatically synchronizing it to opensearch. This is a general underlying solution and does not need any processing at the business level.

Look at this database table. There are too many fields. The sql is as follows

/*------- CREATE SQL---------*/
CREATE TABLE `core_car` (
    `id` varchar(50)  NOT NULL COMMENT 'UUID,Primary key',
    `brandname` varchar(64)  NULL COMMENT 'Brand name, such as BMW',
    `seriesname` varchar(50)  NULL COMMENT 'Series name, such as 5 series and 3 Series',
    `modelName` varchar(50)  NULL,
    `modelDescr` varchar(300)  NULL COMMENT 'Description of vehicle configuration model, e.g. Roewe 550 1.8L Manual gear 2010 Qiyi version 5 seats  Market reference price: 121800 yuan, 2014 langhang 1.6 Hand in one Sports Edition',
    `img` varchar(2000)  NULL COMMENT 'The image path of the car is the file system path',
    `vehicleNumber` varchar(10)  NOT NULL COMMENT 'License plate number, globally unique',
    `vin` varchar(17)  NULL COMMENT 'Frame number',
    `engineNo` varchar(30)  NULL COMMENT 'Engine number',
    `exhaustScale` float(9,3)  NULL DEFAULT 0.000 COMMENT 'Vehicle displacement, in litres, for example: 1.6,1.8,2.0,2.4,3.0',
    `seatCount` tinyint UNSIGNED NULL DEFAULT 5 COMMENT 'Number of seats',
    `modelCode` varchar(20)  NULL COMMENT 'Model code, such as: RWAABD0029',
    `enrollDate` date NULL COMMENT 'The date on which the vehicle is licensed in the vehicle management office, accurate to, refers to the initial registration date of the driving license',
    `makeDate` date NULL COMMENT 'Date of transfer registration, format: mm / DD / yyyy, YYYY-MM-DD,If the vehicle has not been transferred, this field is empty',
    `startDate` date NULL COMMENT 'Starting date of vehicle insurance',
    `purchasePrice` float(11,3)  UNSIGNED NULL DEFAULT 0.000 COMMENT 'New car market reference price',
    `guohu` tinyint NULL DEFAULT 0 COMMENT 'Is it a transfer vehicle in the previous year',
    `owner` varchar(50)  NULL COMMENT 'Owner's name',
    `ownerId` varchar(25)  NULL COMMENT 'Owner's ID card',
    `certType` smallint NULL DEFAULT 1,
    `carLevel` tinyint NULL DEFAULT 1 COMMENT 'Owner's car level',
    `cityCode` smallint UNSIGNED NOT NULL DEFAULT 0 COMMENT 'City Code of the city where the vehicle is located',
    `ecdemicVehicleFlag` tinyint NULL COMMENT 'Foreign vehicle sign, 1 yes, 0 no',
    `vehicleModelId` int UNSIGNED NOT NULL DEFAULT 0,
    `createDate` datetime NULL COMMENT 'Time to add vehicle',
    `lastYearEndDate` datetime NULL COMMENT 'Last year's commercial insurance final insurance date',
    `lastYearCIEndDate` datetime NULL COMMENT 'Last year's compulsory insurance final insurance date',
    `lastYearICName` varchar(32)  NULL DEFAULT 'YYYY' COMMENT 'The name of the insurance company insured last year, such as China Life Insurance, CPIC, etc',
    `phone` varchar(20)  NULL COMMENT 'contact number',
    `merchantCode` int NULL DEFAULT 0 COMMENT 'Merchant id',
    `companyCar` tinyint NULL DEFAULT 0 COMMENT 'Is it a company vehicle',
    `comments` varchar(2000)  NULL COMMENT 'Vehicle remarks',
    `attachmentFlag` tinyint NULL DEFAULT 0 COMMENT 'Is there an attachment',
    `accidents` int NULL DEFAULT 0 COMMENT 'Number of commercial insurance occurrences in the previous year',
    `vehicleWeight` float(9,4)  NULL DEFAULT 0.0000 COMMENT 'Curb mass, ton',
    `licenseKindCode` varchar(4)  NULL COMMENT 'Number plate type',
    `oriCarKindName` varchar(4)  NULL COMMENT 'Traffic control vehicle type',
    `fuelType` varchar(4)  NULL COMMENT 'Fuel type',
    `useNature` varchar(4)  NULL COMMENT 'The nature of use, for example, 85 refers to household use and 83 refers to non business enterprise vehicles',
    `carKindCode` varchar(4)  NULL COMMENT 'Vehicle category code, e.g A0 Represents a passenger car, H0 Represents a truck',
    `platVehicleCode` varchar(100)  NULL COMMENT 'Industry model code',
    `platPublicMode` varchar(100)  NULL COMMENT 'Announcement model',
    `platModelName` varchar(100)  NULL COMMENT 'Vehicle model name',
    `subMerchantId` varchar(32)  NULL DEFAULT '-1' COMMENT 'Sub account ID',
    `taskRemind` tinyint NOT NULL DEFAULT 0 COMMENT 'Documentary or not',
    `address` varchar(100)  NULL COMMENT 'Owner's address, used for delivery',
    `appointmentDate` datetime NULL COMMENT 'time of appointment',
    `communicateFlag` tinyint NULL DEFAULT 0 ,
    `foursId` varchar(32)  NULL COMMENT 'Vehicle ownership 4 S store ID',
    `cpicLevel` smallint NOT NULL DEFAULT 0 COMMENT 'Classification and marking of Taibao vehicles',
    `region` varchar(16)  NULL COMMENT 'region',
    `sex` tinyint NOT NULL DEFAULT 0 COMMENT 'Gender',
    `ownerAge` smallint NOT NULL DEFAULT 0 COMMENT 'Owner age',
    PRIMARY KEY (`id`),
    KEY `auto_shard_key_merchantCode`(`merchantCode`) USING BTREE,
    KEY `core_car_idx1`(`merchantCode`,`lastYearEndDate`) USING BTREE,
    KEY `core_car_idx2`(`merchantCode`,`vehicleNumber`) USING BTREE,
    KEY `core_car_idx3`(`merchantCode`,`subMerchantId`,`communicateFlag`) USING BTREE,
    KEY `core_car_idx4`(`merchantCode`,`enrollDate`) USING BTREE,
    KEY `core_car_idx5`(`merchantCode`,`vin`) USING BTREE,
    KEY `core_car_idx6`(`merchantCode`,`id`) USING BTREE
) ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=Compact
dbpartition by hash(`merchantCode`)
tbpartition by hash(`merchantCode`) tbpartitions 20

The statement of creating a table focuses on the red part of the last two lines, which means that the database is divided according to the merchant to which the vehicle belongs. At the same time, after the database is divided, the table is divided into 20 parts. In this way, the total amount of data in a table will be completely reduced.

The core of this article is a note, because we put two RDS behind DRDS, but the RDS version is MySQL 5.6. Some of our functions require cross table transactions, that is, two or three tables will be updated simultaneously in a business logic method, which leads to cross RDS distributed transactions.

The DRDS table is stored in the first database on the first RDS if it is not divided into databases and tables

Therefore, it involves checking data: https://help.aliyun.com/document_detail/118061.html?spm=a2c4g.11186623.6.627.782951acM0b474

MySQL 5.6 we use does not support distributed transactions by default, and some codes need to be changed manually. For MySQL 5.6, because the implementation of MySQL XA protocol is not mature, DRDS independently implements 2PC transaction strategy for distributed transactions. In MySQL 5.7 and later versions, it is recommended that you use XA transaction strategy.

If a transaction may involve multiple data repositories, you need to declare the current transaction as a distributed transaction. If the transaction only involves a single data sub database, there is no need to start the distributed transaction, just like the MySQL stand-alone transaction, without additional operation

SET AUTOCOMMIT=0;
SET drds_transaction_policy = '2PC'; -- proposal MySQL 5.6 User use
.... -- business SQL
COMMIT; -- or ROLLBACK

JDBC code example:

conn.setAutoCommit(false);
try (Statement stmt = conn.createStatement()) {
stmt.execute("SET drds_transaction_policy = '2PC'");
}
// ... run business SQL
conn.commit(); // Or rollback()

However, we cannot manually write a line of code to send set DRDS every time_ transaction_ Policy = '2pc', so change the code as follows:

import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionDefinition;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class DrdsTransactionManager extends DataSourceTransactionManager {

    public DrdsTransactionManager(DataSource dataSource) {
        super(dataSource);
    }

    @Override
    protected void prepareTransactionalConnection(Connection con, TransactionDefinition     definition) throws SQLException {
        try (Statement stmt = con.createStatement()) {
            stmt.executeUpdate("SET drds_transaction_policy = '2PC'"); // Take 2PC as an example
        }
    }
}

<bean id="drdsTransactionManager" class="my.app.DrdsTransactionManager">
    <property name="dataSource" ref="yourDataSource" />
</bean>

Annotate the classes that need to open DRDS distributed transactions  @ Transactional("drdsTransactionManager")   Just.

Tags: Database

Posted on Wed, 08 Sep 2021 01:32:12 -0400 by rsmith