Online service request processing method, device, electronic device and storage medium

Abstract

The embodiments of the present application provide an online business request processing method, device, electronic device and storage medium. By judging the business request, when the language to be operated on by the business request is not the database schema definition language, a change data capture method based on the binary log stream is used to execute the operation requested by the business request using a master-slave target structure. There is no need to establish a trigger, so no table lock caused by the trigger will be generated, which can improve the performance of the database; the number of triggers can be reduced, the occurrence of locks can be reduced, thereby reducing the impact on the business, and the situation where deadlocks are caused by conflicts between triggers and business practices can be reduced. In the case where the business request is a request to add a unique index to a table in the database schema definition language, a change data capture method based on the trigger is used to execute the operation requested by the business request using a master-slave target structure, and different modes can be selected according to the specific circumstances of the business request.

Description

Technical Field

[0001] The present application relates to the field of computer technology, and in particular to methods, devices, electronic devices, and storage media for processing online business requests. Background Art

[0002] Structured Query Language (SQL) is a special-purpose programming language, a database query and programming language used to access data and query, update, and manage relational database systems. The execution process of business requests in SQL can be as follows: Figure 1 As shown, a request based on structured query language is obtained; whether the request has passed the review, for example, whether the request is legal and whether it has relevant permissions, etc.; if the request has passed the review, whether to execute the request; whether the data of the requested operation is backed up; if it is backed up, the operation corresponding to the request is executed.

[0003] In related technologies, when using structured query language to process business requests, the pt-osc (pt-online-schema-change) mode is used to process business requests for tables whose operation objects are database schema definition languages. The execution process of the pt-osc mode can be as follows: Figure 2 As shown: first, create a new table with the same structure as the table being operated on (hereinafter referred to as the original table); modify the new table; create three types of triggers: add, update, and delete on the original table; copy the data of the original table to the new table, and map the operations in the original table (including add, update, and delete) to the new table through triggers; if the original table has foreign key constraints, process the foreign keys; replace the original table with the new table, for example, rename the original table to the old table and rename the new table to the original table. The entire process uses atomic operation mode and deletes the old table.

[0004] However, using the above method, when there are multiple business requests for the original table, each business request must set a trigger, which will generate a large number of table locks, thereby affecting database performance. In addition, conflicts between triggers and business practices will cause deadlock problems. When the number of triggers is large, the probability of deadlock will further increase. Summary of the Invention

[0005] The purpose of the embodiments of the present application is to provide a method, device, electronic device, and storage medium for processing online business requests to improve database performance and reduce deadlock situations. The specific technical solutions are as follows:

[0006] In a first aspect, an embodiment of the present application provides a method for processing an online service request, the method comprising:

[0007] During the online operation of the database server, when a service request for a database in the database server is obtained, determining whether the language to be operated for the service request is a database schema definition language;

[0008] When the language to be operated for the business request is not the language defined by the database schema, executing the operation requested by the business request using a master-slave target structure based on a change data capture method of a binary log stream;

[0009] In a case where the language to be operated on by the service request is a database schema definition language, determining whether the target to be operated on by the service request is a table;

[0010] In a case where the target to be operated on by the business request is a table, determining whether the operation type of the business request is to add a unique index;

[0011] In a case where the operation type of the business request is adding a unique index, the operation requested by the business request is executed using a master-slave target structure in a trigger-based change data capture manner.

[0012] In a possible implementation, when the language to be operated for the business request is not the database schema definition language, the step of executing the operation requested by the business request using a master-slave target structure based on a change data capture method of a binary log stream includes:

[0013] In a case where the language to be operated on by the business request is a data manipulation language, the operation requested by the business request is executed using a master-slave target structure based on a change data capture method of a binary log stream.

[0014] In a possible implementation, after the step of determining whether the target to be operated on by the service request is a table, the method further includes:

[0015] When the target to be operated on by the business request is not a table, the operation requested by the business request is executed using a master-slave target structure based on a change data capture method of a binary log stream.

[0016] In a possible implementation, when the target to be operated on by the business request is not a table, the step of executing the operation requested by the business request using a master-slave target structure based on a change data capture method of a binary log stream includes:

[0017] When the target to be operated on by the business request is a library, the operation requested by the business request is executed using a master-slave target structure based on a change data capture method of a binary log stream;

[0018] Alternatively, when the target to be operated on by the business request is a view operation, the operation requested by the business request is executed using a master-slave target structure based on a change data capture method of a binary log stream.

[0019] In a possible implementation, after determining whether the operation type of the service request is adding a unique index, the method further includes:

[0020] When the operation type of the business request is not an add unique index operation, the operation requested by the business request is executed using a master-slave target structure based on a change data capture method of a binary log stream.

[0021] In a possible implementation, when the operation type of the business request is adding a unique index, the step of executing the operation requested by the business request using a master-slave target structure based on a trigger-based change data capture method includes:

[0022] When the operation type of the business request is table creation and the newly created table includes a unique index, the operation requested by the business request is executed using a master-slave target structure based on a trigger-based change data capture method;

[0023] Alternatively, when the operation type of the business request is to add a unique index to an established table, the operation requested by the business request is executed using a master-slave target structure in a trigger-based change data capture manner.

[0024] In a possible implementation, the binary log stream-based change data capture method includes the following steps:

[0025] According to the business request, operate on the backup of the target to be operated, and obtain a binary log stream representing the changed data in the backup;

[0026] Copy the data in the target to be operated to the newly created target, and update the data in the newly created target according to the binary log stream;

[0027] The target to be operated is replaced by the newly created target.

[0028] In a second aspect, an embodiment of the present application provides an online service request processing device, the device comprising:

[0029] a module for determining a language to be operated, configured to determine, when a service request for a database in the database server is obtained during the online operation of the database server, whether the language to be operated for the service request is a database schema definition language;

[0030] a first execution module, configured to execute the operation requested by the business request using a master-slave target structure based on a change data capture method of a binary log stream when the language to be operated on by the business request is not the database schema definition language;

[0031] a target to be operated determining module, configured to determine whether the target to be operated targeted by the service request is a table when the language to be operated targeted by the service request is a database schema definition language;

[0032] An operation type determination module, configured to determine whether the operation type of the business request is to add a unique index when the target to be operated on by the business request is a table;

[0033] The second execution module is configured to execute the operation requested by the business request by using a master-slave target structure based on a trigger-based change data capture method when the operation type of the business request is adding a unique index.

[0034] In a possible implementation, the first execution module is specifically configured to: when the language to be operated on by the business request is a data manipulation language, execute the operation requested by the business request using a master-slave target structure based on a change data capture method of a binary log stream.

[0035] In a possible implementation, the first execution module is further configured to: when the target to be operated on by the business request is not a table, execute the operation requested by the business request using a master-slave target structure based on a change data capture method of a binary log stream.

[0036] In one possible implementation, the first execution module is specifically configured to: when the target to be operated on by the business request is a library, execute the operation requested by the business request using a master-slave target structure based on a change data capture method of a binary log stream; or, when the target to be operated on by the business request is a view, execute the operation requested by the business request using a master-slave target structure based on a change data capture method of a binary log stream.

[0037] In a possible implementation, the first execution module is further configured to: when the operation type of the business request is not an add unique index operation, execute the operation requested by the business request using a master-slave target structure based on a change data capture method of a binary log stream.

[0038] In one possible implementation, the second execution module is specifically configured to: when the operation type of the business request is table creation and the newly created table includes a unique index, execute the operation requested by the business request using a master-slave target structure based on a trigger-based change data capture method; or, when the operation type of the business request is adding a unique index to an established table, execute the operation requested by the business request using a trigger-based change data capture method.

[0039] In one possible implementation, the first execution module is specifically configured to: operate on a backup of a target to be operated according to the business request, and obtain a binary log stream representing changed data in the backup; copy data in the target to be operated to a newly created target, and update the data in the newly created target according to the binary log stream; and replace the target to be operated with the newly created target.

[0040] In a third aspect, an embodiment of the present application provides an electronic device, including a processor and a memory;

[0041] The memory is used to store computer programs;

[0042] The processor is used to implement any online service request processing method described in this application when executing the program stored in the memory.

[0043] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, it implements any online business request processing method described in the present application.

[0044] Beneficial effects of the embodiments of the present application:

[0045] The online business request processing method, device, electronic device and storage medium provided by the embodiments of the present application obtain online business requests; when a business request for a database in the database server is obtained during online operation, it is determined whether the language to be operated on by the business request is a database schema definition language; if the language to be operated on by the business request is not a database schema definition language, a master-slave target structure is used to execute the operation requested by the business request based on a change data capture method of a binary log stream; if the language to be operated on by the business request is a database schema definition language, it is determined whether the target to be operated on by the business request is a table; if the target to be operated on by the business request is a table, it is determined whether the operation type of the business request is to add a unique index; if the operation type of the business request is to add a unique index, a master-slave target structure is used to execute the operation requested by the business request based on a change data capture method of a trigger. By judging the business request, when the language to be operated on by the business request is not the database schema definition language, the change data capture method based on the binary log stream uses the master-slave target structure to execute the operation requested by the business request, without establishing a trigger, so no table lock caused by the trigger will be generated, which can improve the performance of the database; the number of triggers can be reduced, the occurrence of locks can be reduced, thereby reducing the impact on the business, and the situation where the trigger conflicts with the business practice and causes deadlock. In the case that the business request is a request to add a unique index to a table in the database schema definition language, the change data capture method based on the trigger uses the master-slave target structure to execute the operation requested by the business request, and different modes can be selected according to the specific circumstances of the business request. Of course, the implementation of any product or method of the present application does not necessarily require all the advantages described above to be achieved at the same time. BRIEF DESCRIPTION OF THE DRAWINGS

[0046] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without any creative work.

[0047] Figure 1 A schematic diagram of the execution process of a structured query language business request in the related art;

[0048] Figure 2 A schematic diagram of the execution process of the pt-osc mode in the related art;

[0049] Figure 3 This is a first schematic diagram of the online service request processing method according to an embodiment of the present application;

[0050] Figure 4 A schematic diagram of a change data capture method based on binary log stream according to an embodiment of the present application;

[0051] Figure 5 This is a second schematic diagram of the method for processing online business requests according to an embodiment of the present application;

[0052] Figure 6a This is a schematic diagram of database IO in pt-osc mode in an embodiment of the present application;

[0053] Figure 6b This is a schematic diagram of database IO in the gh-ost mode in an embodiment of the present application;

[0054] Figure 7a A schematic diagram of the database QPS in the MySQL raw mode in an embodiment of the present application;

[0055] Figure 7b A schematic diagram of the TPS of the database in the MySQL original mode in an embodiment of the present application;

[0056] Figure 8a A schematic diagram of the database QPS in the pt-osc mode in an embodiment of the present application;

[0057] Figure 8b A schematic diagram of a database TPS in the pt-osc mode in an embodiment of the present application;

[0058] Figure 8c A schematic diagram of the innodb_locks table in pt-osc mode in an embodiment of the present application;

[0059] Figure 9a This is a schematic diagram of the database QPS in the gh-osc mode in an embodiment of the present application;

[0060] Figure 9b A schematic diagram of a database TPS in the gh-osc mode in an embodiment of the present application;

[0061] Figure 10 A schematic diagram of an online service request processing device according to an embodiment of the present application;

[0062] Figure 11 A schematic diagram of an electronic device according to an embodiment of the present application. DETAILED DESCRIPTION

[0063] The following will be combined with the drawings in the embodiments of this application to clearly and completely describe the technical solutions in the embodiments of this application. Obviously, the embodiments described are only part of the embodiments of this application, not all of the embodiments. Based on the embodiments in this application, all other embodiments obtained by ordinary technicians in this field without making creative efforts are within the scope of protection of this application.

[0064] First, the terms in this application are explained:

[0065] DDL (Data Definition Language) is a language used to describe real-world entities to be stored in a database, that is, it is used to create various objects in the database - tables, views, indexes, synonyms, clusters, etc.

[0066] pt-osc is a table structure change tool provided in the Percona operation and maintenance toolkit. The full name of pt-osc is pt-online-schema-change. It can perform database schema definition language operations online without blocking read and write operations, thereby reducing the impact on business programs.

[0067] Inception / GoInception: A MySQL (a relational database management system) operation and maintenance tool that integrates auditing, execution, backup, and rollback statement generation. It returns audit results based on custom rules by parsing the syntax of the executed structured query language, and provides execution, backup, and rollback statement generation functions.

[0068] In the related art, the business request in the structured query language is executed through the pt-osc mode. The execution process of the pt-osc mode can be as follows: Figure 2 As shown, it includes: when operating on a table, creating a new table with the same structure as the original table; modifying the new table so that the number of rows and columns of the new table are the same as the original table; creating three types of triggers on the original table: "add", "update", and "delete"; copying the data of the original table to the new table, and mapping the operations in the original table to the new table through triggers; if the original table has foreign key constraints, processing the foreign keys; replacing the original table with the new table. The pt-osc mode has long-term usage experience and supports concurrent data manipulation languages, but the pt-osc mode has the following disadvantages: when the primary key of the original table adopts the auto-increment ID strategy, a large number of table locks will be generated when there are a lot of write requests, thereby affecting the performance of the database system; the overhead and risks brought by triggers, such as data manipulation language locks and deadlocks, adding unique indexes, losing duplicate data, and being unable to pause during execution.

[0069] In view of this, an embodiment of the present application provides an online business request processing method, including: during the online operation of the database server, when a business request for a database in the database server is obtained, determining whether the language to be operated for the business request is a database schema definition language; if the language to be operated for the business request is not a database schema definition language, based on the change data capture method of the binary log stream, using the master-slave target structure to execute the operation requested by the business request; if the language to be operated for the business request is a database schema definition language, determining whether the target to be operated for the business request is a table; if the target to be operated for the business request is a table, determining whether the operation type of the business request is to add a unique index; if the operation type of the business request is to add a unique index, based on the change data capture method of the trigger, using the master-slave target structure to execute the operation requested by the business request. In the embodiment of the present application, different modes are selected according to the actual situation of the business request. If the language to be operated for the business request is not a database schema definition language, based on the change data capture method of the binary log stream, using the master-slave target structure to execute the operation requested by the business request, without relying on triggers, it can improve the performance of the database and reduce deadlocks.

[0070] The following is a detailed description, see Figure 3 , Figure 3 A flowchart of a method for processing an online service request according to an embodiment of the present application is provided, including:

[0071] S301 , when a service request for a database in the database server is obtained during online operation of the database server, it is determined whether the language to be operated in the service request is a database schema definition language.

[0072] The online business request processing method of the embodiment of the present application can be implemented by an electronic device. In one example, the electronic device can be a database server, such as a database server based on MySQL. Online in the present application means that the database server provides database services online, that is, the database server can provide read and write services for the database to multiple users at the same time. For a business request for a database, a user can send a business request to a database server based on the World Wide Web, or a user can send a business request to a database server based on a local area network, etc., all of which are within the scope of protection of the present application. The operations requested by the business request may include update, delete and add, among which update and delete can be collectively referred to as modification. For example: a business request can be an operation on a library in a database schema definition language, which can specifically include a database creation operation for the database schema definition language, a database deletion operation for the database schema definition language, and a modification operation for a library in the database schema definition language; for example: a business request can be an operation on a table in a database schema definition language, which can specifically include a table creation operation for the database schema definition language, a table deletion operation for the database schema definition language, and a modification operation for a table in the database schema definition language, etc.

[0073] The language to be operated targeted by the business request may be a database schema definition language or a data manipulation language. After obtaining the business request, it is determined whether the language to be operated targeted by the business request is a database schema definition language.

[0074] S302 : When the language to be operated for the business request is not the database schema definition language, the operation requested by the business request is executed using a master-slave target structure based on a change data capture method of a binary log stream.

[0075] When the language to be operated on by the business request is not the database schema definition language, for example, when the language to be operated on by the business request is a data manipulation language, the operation requested by the business request is executed using a master-slave target structure based on a change data capture method based on a binary log stream.

[0076] Change data can be captured based on the binary log stream, and the data in the slave target can be updated according to the change data. The slave target can then be used to replace the master target, thereby executing the operation requested by the business request. In one possible implementation, the above-mentioned change data capture method based on the binary log stream and the steps of executing the operation requested by the business request using the master-slave target structure include:

[0077] Step 1: According to the business request, the backup of the target to be operated is operated, and a binary log stream representing the changed data in the backup is obtained.

[0078] The target to be operated can be a database, view or table, etc., and the binary log stream representing the changed data in the backup can specifically be a binlog (Binary Log, a type of binary log) log stream. The backup of the target to be operated can be a full data backup of the target to be operated, or it can be an incremental data backup of the target to be operated. In an example, before step one, a listening thread for the binlog log of the backup can be added to obtain the binlog log representing the changed data of the backup. The backup of the target to be operated can be pre-established or established after receiving a business request, both of which are within the scope of protection of this application.

[0079] Step 2: Copy the data in the target to be operated to the newly created target, and update the data in the newly created target according to the binary log stream.

[0080] The newly created target (equivalent to the slave target) has the same structure as the target to be operated on (equivalent to the master target) (the names may be different). Data from the target to be operated on is copied to the newly created target; data in the newly created target is updated based on the binary log stream. In one example, to increase efficiency, the copying of data from the target to be operated on and the updating of data in the newly created target based on the binary log stream can be performed in parallel.

[0081] Step 3: Replace the target to be operated with the newly created target.

[0082] In one example, the target to be operated is deleted, and the new target is named the same as the target to be operated, thereby replacing the directory to be operated with the new target.

[0083] The following is an example using the binary log stream as the binlog log. Figure 4 As shown: verify whether the database exists and test whether the database is connected; confirm whether the connection instance is correct; verify permissions; verify binlog logs, including row (referring to the row mode in structured query language) format verification and restart replication verification after modifying the binlog log format; check the storage engine, foreign keys, triggers, and row number estimation of the target to be operated; initialize the connection of streamer (a tool for listening to binlog logs in MySQL database) and add binlog log listening; initialize the connection of applier (a real-time synchronization tool for MySQL database and distributed file system data), create a new target and a log change record table. In an example, the log change record table is used to record binlog logs representing changed data; concurrently execute writing data in the target to be operated to the new target and updating the new target according to the binlog logs of the changed data; target switching, that is, replacing the original target to be operated with the new target.

[0084] Capture change data based on binary log streams. By simulating a slave database, obtain incremental changes from the row binlog log and then asynchronously apply them to the target to be operated. This is independent of triggers, has almost no impact on host performance, and can be paused during execution.

[0085] S303: When the language to be operated on by the service request is a database schema definition language, determine whether the target to be operated on by the service request is a table.

[0086] The target to be operated may be a database, a view or a table. When the language to be operated on by the business request is a database schema definition language, it is determined whether the target to be operated on by the business request is a table.

[0087] S304: When the target to be operated on by the business request is a table, determine whether the operation type of the business request is to add a unique index.

[0088] The operation type of a business request can be customized according to actual conditions. For example, the operation type of a business request can include "add", "update", "delete", etc., where "add" for a table can include "add unique index".

[0089] S305 , when the operation type of the business request is adding a unique index, based on the trigger-based change data capture method, the master-slave target structure is used to execute the operation requested by the business request.

[0090] The binary log stream-based change data capture method does not support tables with triggers or foreign key relationships, nor does it support adding unique indexes. Therefore, when adding a unique index to a table, the trigger-based change data capture method is required to capture the change data.

[0091] For example, when the above-mentioned business request is an operation to create a table in a database schema definition language and the newly created table includes a unique index, the operation requested by the above-mentioned business request is executed using a master-slave target structure based on a trigger-based change data capture method; or, when the above-mentioned business request is an operation to add a unique index to an already established table in a database schema definition language, the operation requested by the above-mentioned business request is executed using a trigger-based change data capture method.

[0092] In one example, a trigger-based change data capture method utilizes a master-slave target structure to execute the operations requested by the above business request, including: when operating on the original table (equivalent to the master target), creating a new table (equivalent to the slave target), and the structure of the new table is the same as that of the original table; modifying the new table so that the number of rows and columns of the new table are the same as those of the original table; creating three types of triggers: "add", "update", and "delete" on the original table; copying the data of the original table to the new table, and mapping the operations in the original table to the new table through triggers; if the original table has foreign key constraints, processing the foreign keys; and replacing the original table with the new table.

[0093] In an embodiment of the present application, by judging the business request, if the language to be operated on by the business request is not the database schema definition language, a change data capture method based on the binary log stream is used to execute the operation requested by the business request using a master-slave target structure, without establishing a trigger, so no table locks caused by the trigger are generated, which can improve the performance of the database; the number of triggers can be reduced, the occurrence of locks can be reduced, thereby reducing the impact on the business, and the situation where deadlocks are caused by conflicts between triggers and business practices can be reduced. If the business request is a request to add a unique index to a table in the database schema definition language, a change data capture method based on the trigger is used to execute the operation requested by the business request using a master-slave target structure, and different modes can be selected according to the specific circumstances of the business request.

[0094] In databases, such as MySQL, index types are generally divided into unique indexes and regular indexes. Operations that do not add unique indexes to tables include: operations on databases in the database schema definition language (including creation, modification, and deletion), operations on views in the database schema definition language, and operations on tables in the database schema definition language that do not add unique indexes (including operations on other data in the table besides unique indexes).

[0095] In one possible implementation, see Figure 5 After determining whether the target to be operated on by the business request is a table, the method further includes:

[0096] S306 , when the target to be operated on by the business request is not a table, the operation requested by the business request is performed using a master-slave target structure based on a change data capture method of a binary log stream.

[0097] Generally, the target to be operated on can be a database, view, or table. If the target to be operated on is not a table, the change data capture method based on the binary log stream is used. For example, if the target to be operated on is a database, the change data capture method based on the binary log stream is used to execute the requested operation using the master-slave target structure. Alternatively, if the target to be operated on is a view, the change data capture method based on the binary log stream is used to execute the requested operation using the master-slave target structure.

[0098] In one possible implementation, see Figure 5 After determining whether the operation type of the service request is to add a unique index, the method further includes:

[0099] S307: When the operation type of the business request is not an add unique index operation, the operation requested by the business request is executed using a master-slave target structure based on a change data capture method of a binary log stream.

[0100] If the business request is for an operation other than adding a unique index to a table, such as reading, updating, or deleting data other than the unique index in the table, or reading or deleting the unique index of the table, the operations requested by the business request can be performed using the master-slave target structure based on the change data capture method of the binary log stream.

[0101] In an embodiment of the present application, when the business request does not involve adding a unique index to a table in the database schema definition language, change data is captured based on the binary log stream without creating triggers. Therefore, no table locks caused by triggers are generated, which can improve database performance. The number of triggers can be reduced, thereby reducing the possibility of deadlocks caused by conflicts between triggers and business practices. Different modes can be selected based on the specific circumstances of the business request, reducing the occurrence of locks and thus minimizing the impact on the business.

[0102] To more clearly illustrate the technical effects of the embodiments of the present application, a specific example is provided below. In this embodiment, MySQL version 5.6, Sysbench (an open source multi-threaded performance testing tool) version 1.0.17, pt-osc version 2.2.17, and gh-ost version 1.0.48 are used for binary log stream-based change data capture.

[0103] 1. Idle state.

[0104] Sysbench creates a table with 10 million data points and a size of 3 GB.

[0105] (1) Testing process:

[0106] The pt-osc mode uses the pt-online-schema-change tool, and the gh-ost mode uses the gh-ost package to perform database table changes—adding columns.

[0107] In an example, the test command could be:

[0108] #time pt-online-schema-change --host=IP address --user=root --password=password --execute --alter "ADD COLUMN c1 INT" D=test, t=sbtest1

[0109] (Set up the pt-online-schema-change tool, set the host IP address, set the root user password, set the operation type: modify and add columns, set up testing)

[0110] #time gh-ost-user=root (root user operation time in gh-ost mode)

[0111] -password=password-host=IP address-allow-master-master (password and IP address to verify administrator privileges)

[0112] -allow-on-master (grant administrator privileges)

[0113] -database="test"-table="sbtest1"-alter="ADD COLUMN c1 INT"-execute

[0114] (Testing the database to modify and add columns)

[0115] (2) Monitoring graphics:

[0116] The number of database IO (Input / Output) in Pt-osc mode is as follows Figure 6a As shown, the database IO times in gh-ost mode are as follows Figure 6b As shown in the figure, the comparison shows that since the two modes synchronize existing data in the same way, the resulting IO load is basically the same.

[0117] 2. High load state

[0118] Sysbench creates a table with 1 million data volumes and starts 50 threads for concurrent reading and writing.

[0119] (1) Testing process:

[0120] Under a high-load Sysbench workload with 50 concurrent read and write threads, we used the pt-online-schema-change tool in pt-osc mode and the gh-ost package in gh-ost mode to perform database table changes, adding columns.

[0121] In an example, the test command could be:

[0122] sysbench--threads=50--rate=0--report-interval=1--percentile=99

[0123] (Sysbench starts 50 threads, reporting interval 1, percentile 99)

[0124] --events=0 --time=150 --db-ps-mode=auto --mysql-host=IP address

[0125] (Set the sports item to 0, set the time to 150, set the database model to automatically select, and set the MySQL host IP address)

[0126] --mysql-db=test--mysql-user=root--mysql-password=password / usr / share / sysbench / oltp_read_write.lua

[0127] (Set up MySQL database testing, set up MySQL database root user, set up root user password, set up multi-user online reading and writing in Sysbench)

[0128] --table_size=1000000run (run 1,000,000 tables)

[0129] pt-online-schema-change --host=IP address --user=root (set the host's online schema change plan and set the root user)

[0130] --password=password(password)

[0131] --max-lag=1000 --max-load="Threads_running=30000" --critical-load="Threads_running=30000" (sets the maximum lag to 1000, the maximum load to 30000 concurrent threads, and the critical load to 30000 concurrent threads)

[0132] --execute--alter"ADD COLUMN c1 INT"D=test,t=sbtest1

[0133] (Execute the test of modifying and adding columns)

[0134] gh-ost-user=root-password=password (verify the password of the root user in gh-ost mode)

[0135] -host=IP address-allow-master-master-allow-on-master-max-lag-millis=30000

[0136] (Verify the host address to allow administrator privileges on the host and set the maximum delay per millisecond to 30,000)

[0137] -ok-to-drop-table-database="test"-table="sbtest1"-alter="ADDCOLUMNc1 INT"-execute

[0138] (Execute tests to delete tables and modify and add columns to the database)

[0139] (2) Monitoring graphics:

[0140] The database QPS (Query per second) and TPS (Transactions Per Second) before executing each mode in MySQL are as follows: Figure 7a and Figure 7b As shown in the figure, the higher the QPS and TPS, the higher the performance of the storage system.

[0141] In pt-osc mode, the database QPS and TPS are as follows: Figure 8a and Figure 8b As shown, the innodb_locks table is as follows Figure 8c As shown, a large number of table locks are generated, which seriously affects the performance of the database under high load conditions.

[0142] In gh-ost mode, the database QPS and TPS are as follows: Figure 9a and Figure 9b As shown, the query and transaction processing speeds remain stable, with no significant fluctuations. Threads are not blocked, and no locks are observed. Under high load, gh-ost has virtually no impact on database performance.

[0143] The present application also provides an online service request processing device. Figure 10 , the device comprises:

[0144] The language to be operated determination module 11 is configured to determine whether the language to be operated for the business request is a database schema definition language when a business request for a database in the database server is obtained during the online operation of the database server;

[0145] A first execution module 12 is configured to execute the operation requested by the business request using a master-slave target structure based on a change data capture method of a binary log stream when the language to be operated on by the business request is not the database schema definition language;

[0146] The target to be operated determining module 13 is configured to determine whether the target to be operated on by the service request is a table when the voice to be operated on by the service request is a database schema definition language;

[0147] The operation type determination module 14 is configured to determine whether the operation type of the business request is to add a unique index when the target to be operated on by the business request is a table;

[0148] The second execution module 15 is configured to execute the operation requested by the business request using a master-slave target structure based on a trigger-based change data capture method when the operation type of the business request is adding a unique index.

[0149] In a possible implementation, the first execution module is specifically configured to: when the language to be operated on by the business request is a data manipulation language, execute the operation requested by the business request using a master-slave target structure based on a change data capture method of a binary log stream.

[0150] In a possible implementation, the first execution module is further configured to: when the target to be operated on by the business request is not a table, execute the operation requested by the business request using a master-slave target structure based on a change data capture method of a binary log stream.

[0151] In one possible implementation, the first execution module is specifically configured to: when the target to be operated on by the business request is a library, execute the operation requested by the business request using a master-slave target structure based on a change data capture method of a binary log stream; or when the target to be operated on by the business request is a view, execute the operation requested by the business request using a master-slave target structure based on a change data capture method of a binary log stream.

[0152] In a possible implementation, the first execution module is further configured to: when the operation type of the business request is not an operation of adding a unique index, execute the operation requested by the business request using a master-slave target structure based on a change data capture method of a binary log stream.

[0153] In one possible implementation, the second execution module is specifically used to: when the operation type of the business request is to create a table and the newly created table includes a unique index, based on the trigger-based change data capture method, use the master-slave target structure to execute the operation requested by the business request; or, when the operation type of the business request is to add a unique index to an established table, based on the trigger-based change data capture method, use the master-slave target structure to execute the operation requested by the business request.

[0154] In one possible implementation, the first execution module is specifically configured to: operate on a backup of the target to be operated according to the business request, and obtain a binary log stream representing the changed data in the backup; copy the data in the target to be operated to a newly created target, and update the data in the newly created target according to the binary log stream; and replace the target to be operated with the newly created target.

[0155] An embodiment of the present application further provides an electronic device, comprising: a processor and a memory;

[0156] The memory is used to store computer programs;

[0157] When the processor is used to execute the computer program stored in the memory, the processor implements the following steps:

[0158] During the online operation of the database server, when a business request for a database in the database server is obtained, it is determined whether the language to be operated for the business request is a database schema definition language;

[0159] If the language to be operated on by the business request is not the language defined by the database schema, the operation requested by the business request is performed using a master-slave target structure based on the change data capture method of the binary log stream;

[0160] In a case where the language to be operated on by the business request is a database schema definition language, determining whether the target to be operated on by the business request is a table;

[0161] In a case where the target to be operated on by the business request is a table, determining whether the operation type of the business request is to add a unique index;

[0162] In the case where the operation type of the business request is adding a unique index, the operation requested by the business request is executed using a master-slave target structure based on a trigger-based change data capture method.

[0163] Optional, see Figure 11 In addition to the above-mentioned processor 21 and memory 23, the electronic device of the embodiment of the present application also includes a communication interface 22 and a communication bus 24, wherein the processor 21, the communication interface 22, and the memory 23 communicate with each other through the communication bus 24.

[0164] Optionally, when the processor is used to execute the computer program stored in the memory, it can also implement any of the online service request processing methods mentioned above.

[0165] The communication bus mentioned in the electronic device mentioned above may be a PCI (Peripheral Component Interconnect) bus or an EISA (Extended Industry Standard Architecture) bus. This communication bus can be divided into an address bus, a data bus, a control bus, etc. For ease of illustration, the figure shows only one thick line, but this does not mean that there is only one bus or only one type of bus.

[0166] The communication interface is used for communication between the above electronic device and other devices.

[0167] The memory may include RAM (Random Access Memory) or NVM (Non-Volatile Memory), such as at least one disk storage. Optionally, the memory may also be at least one storage device located away from the aforementioned processor.

[0168] The above-mentioned processor can be a general-purpose processor, including a CPU (Central Processing Unit), an NP (Network Processor), etc.; it can also be a DSP (Digital Signal Processing), an ASIC (Application Specific Integrated Circuit), an FPGA (Field-Programmable Gate Array) or other programmable logic devices, discrete gate or transistor logic devices, and discrete hardware components.

[0169] An embodiment of the present application further provides a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, any of the above-mentioned online service request processing methods is implemented.

[0170] In another embodiment provided by the present application, a computer program product including instructions is further provided, which, when executed on a computer, enables the computer to execute any one of the online service request processing methods in the above embodiments.

[0171] In the above embodiments, it can be implemented in whole or in part by software, hardware, firmware or any combination thereof. When implemented using software, it can be implemented in whole or in part in the form of a computer program product. The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, the process or function described in the embodiment of the present application is generated in whole or in part. The computer can be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions can be stored in a computer-readable storage medium or transmitted from one computer-readable storage medium to another computer-readable storage medium. For example, the computer instructions can be transmitted from one website, computer, server or data center to another website, computer, server or data center via a wired (e.g., coaxial cable, optical fiber, digital subscriber line) or wireless (e.g., infrared, wireless, microwave, etc.) method. The computer-readable storage medium can be any available medium that a computer can access or a data storage device such as a server or data center that includes one or more available media integrated. The available medium can be a magnetic medium (e.g., a floppy disk, a hard disk, a tape), an optical medium (e.g., a DVD), or a semiconductor medium (e.g., a solid-state drive SolidState Disk (SSD)).

[0172] It should be noted that, in this article, the technical features in each optional solution can be combined to form a solution as long as there is no contradiction, and these solutions are all within the scope disclosed in this application. Relational terms such as first and second, etc. are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply that there is any such actual relationship or order between these entities or operations. Moreover, the term "comprise", "comprising" or any other variant thereof is intended to cover non-exclusive inclusion, so that the process, method, article or equipment including a series of elements not only include those elements, but also include other elements not clearly listed, or also include elements inherent to such process, method, article or equipment. In the absence of more restrictions, the elements limited by the sentence "comprising a..." do not exclude the presence of other identical elements in the process, method, article or equipment including the elements.

[0173] Each embodiment in this specification is described in a related manner. Similar portions between the various embodiments can be referenced to each other. Each embodiment focuses on the differences between the other embodiments. In particular, the embodiments of the apparatus, electronic device, computer program product, and storage medium are generally similar to the method embodiments, so their descriptions are relatively simple. For relevant portions, reference can be made to the descriptions of the method embodiments.

[0174] The above description is only a preferred embodiment of the present application and is not intended to limit the scope of protection of the present application. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principles of the present application are included in the scope of protection of the present application.

Claims

1. A method for processing online business requests, characterized in that: The method comprises: During the online operation of the database server, when a service request for a database in the database server is obtained, determining whether the language to be operated for the service request is a database schema definition language; When the language to be operated on by the business request is not the database schema definition language, executing the operation requested by the business request using a master-slave target structure based on a change data capture method of a binary log stream includes: when the language to be operated on by the business request is a data manipulation language, executing the operation requested by the business request using a master-slave target structure based on a change data capture method of a binary log stream; In a case where the language to be operated on by the business request is a database schema definition language, determining whether the target to be operated on by the business request is a table; When the target to be operated on by the business request is not a table, the operation requested by the business request is executed using a master-slave target structure based on a change data capture method of a binary log stream; In a case where the target to be operated on by the business request is a table, determining whether the operation type of the business request is to add a unique index; In a case where the operation type of the business request is to add a unique index, the operation requested by the business request is executed using a master-slave target structure in a trigger-based change data capture method; When the operation type of the business request is not an add unique index operation, the operation requested by the business request is executed using a master-slave target structure based on a change data capture method of a binary log stream; The change data capture method based on the binary log stream and the step of executing the operation requested by the business request using the master-slave target structure include: According to the business request, the backup of the target to be operated is operated, and a binary log stream representing the changed data in the backup is obtained, wherein a listening thread for the binlog log of the backup is added to obtain the binlog log representing the changed data of the backup; Copy the data in the target to be operated to the newly created target, and in parallel update the data in the newly created target according to the binary log stream; The target to be operated is deleted, and a new target is named with the name of the target to be operated, thereby replacing the target to be operated with the new target.

2. The method according to claim 1, characterized in that The step of executing the operation requested by the business request using a master-slave target structure based on a change data capture method of a binary log stream when the target to be operated on by the business request is not a table includes: When the target to be operated on by the business request is a library, the operation requested by the business request is executed using a master-slave target structure based on a change data capture method of a binary log stream; Alternatively, when the target to be operated on by the business request is a view operation, the operation requested by the business request is executed using a master-slave target structure based on a change data capture method of a binary log stream.

3. The method according to claim 1, characterized in that When the operation type of the business request is to add a unique index, the step of executing the operation requested by the business request by using a master-slave target structure based on a trigger-based change data capture method includes: When the operation type of the business request is table creation and the newly created table includes a unique index, the operation requested by the business request is executed using a master-slave target structure based on a trigger-based change data capture method; Alternatively, when the operation type of the business request is to add a unique index to an established table, the operation requested by the business request is executed using a master-slave target structure in a trigger-based change data capture manner.

4. An online service request processing device, characterized in that: The device comprises: a module for determining a language to be operated, configured to determine, when a service request for a database in the database server is obtained during the online operation of the database server, whether the language to be operated for the service request is a database schema definition language; a first execution module, configured to execute the operation requested by the business request using a master-slave target structure based on a change data capture method of a binary log stream when the language to be operated on by the business request is not the database schema definition language; a target-to-be-operated judgment module, configured to judge whether the target-to-be-operated targeted by the business request is a table when the language to be operated targeted by the business request is a database schema definition language; An operation type determination module, configured to determine whether the operation type of the business request is to add a unique index when the target to be operated on by the business request is a table; A second execution module is configured to, when the operation type of the business request is to add a unique index, execute the operation requested by the business request by using a master-slave target structure based on a trigger-based change data capture method; The first execution module is specifically configured to: operate on a backup of a target to be operated according to the business request, and obtain a binary log stream representing the changed data in the backup, wherein a listening thread for the binlog log of the backup is added to obtain the binlog log representing the changed data in the backup; copy the data in the target to be operated to a newly created target, and concurrently update the data in the newly created target according to the binary log stream; delete the target to be operated, and name the newly created target the same as the name of the target to be operated, thereby replacing the target to be operated with the newly created target; The first execution module is specifically configured to: when the language to be operated on by the business request is a data manipulation language, execute the operation requested by the business request using a master-slave target structure based on a change data capture method of a binary log stream; The first execution module is further configured to: when the target to be operated on by the business request is not a table, execute the operation requested by the business request using a master-slave target structure based on a change data capture method of a binary log stream; The first execution module is further used to: when the operation type of the business request is not an add unique index operation, based on the change data capture method of the binary log stream, use the master-slave target structure to execute the operation requested by the business request.

5. The device according to claim 4, characterized in that The first execution module is specifically configured to: when the target to be operated on by the business request is a library, execute the operation requested by the business request by using a master-slave target structure based on a change data capture method of a binary log stream; or, when the target to be operated on by the business request is a view, execute the operation requested by the business request by using a master-slave target structure based on a change data capture method of a binary log stream.

6. The device according to claim 4, characterized in that The second execution module is specifically used to: when the operation type of the business request is to create a table and the newly created table includes a unique index, based on the trigger-based change data capture method, use the master-slave target structure to execute the operation requested by the business request; or, when the operation type of the business request is to add a unique index to an established table, based on the trigger-based change data capture method, use the master-slave target structure to execute the operation requested by the business request.

7. An electronic device, characterized in that: including processor and memory; The memory is used to store computer programs; The processor is configured to implement the online service request processing method according to any one of claims 1 to 3 when executing the program stored in the memory.

8. A computer-readable storage medium, characterized in that The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the online service request processing method according to any one of claims 1 to 3 is implemented.

Images