CN109491984B

CN109491984B - Hash packet data base fragment polling sorting method

Info

Publication number: CN109491984B
Application number: CN201811170220.3A
Authority: CN
Inventors: 杨建设; 桂侃; 徐汉东; 杨欣; 李康; 胡华青; 胡昌松; 赵飞; 陈�胜
Original assignee: Hubei Rural Credit Cooperatives Network Information Center
Current assignee: Hubei Rural Credit Cooperatives Network Information Center
Priority date: 2018-10-09
Filing date: 2018-10-09
Publication date: 2020-12-15
Anticipated expiration: 2038-10-09
Also published as: CN109491984A

Abstract

The invention discloses a hash grouping database defragmentation method. The method comprises the following steps: counting the database table objects; the index system obtained by the calculation of the mathematical model is sorted by the value of NPAGES; carrying out hash grouping according to an index system; step modular packaging is carried out on the target object; a single table is completely sorted and check points are packaged to form a table module; the flow progress is controlled using a dual poller. The method does not need manual operation, is fully automatically executed, and greatly saves the cost of human resources.

Description

Hash packet data base fragment polling sorting method

Technical Field

The invention belongs to the technical field of database management, and particularly relates to a defragmentation method of a hash grouping database.

Background

With the rapid development of information system construction, the data carrying and processing capacity of the system and the human resources of departments face huge challenges. On one hand, the influence on the back-end database is that the number of servers and the data volume of DB instances are multiplied, various service requirements and the instances are delivered quickly, and great challenges are brought to daily management of the database. On the other hand, with the progress of science and technology, data acquisition of various information systems is more intensive, the data volume of each service system is increased in a burst mode, data fragments are increased after the data volume is increased or changed to a certain extent, and data pages are discontinuous; the problems of inaccurate statistical information, low efficiency of executing the plan and the like are caused. The database self-contained automatic finishing function is less and less efficient and even fails.

Many databases have different technical abilities of using customers or outsourcers, most of the databases do not have professional DBA personnel, and the established specifications cannot be effectively implemented in each arrangement. Because the database table arrangement is incomplete, the fault and secondary problems caused by the manual database arrangement operation are endless. The database arrangement and maintenance requirements are high, and the manual operation risk of the database is uncontrollable.

As database data is increasing, traditional database defragmentation is employed. There are the following problems:

the traditional automatic defragmentation technology fails, specific time cannot be specified and the defragmentation progress cannot be controlled when a database-carried defragmentation program is used, and the automatic defragmentation function can hardly be triggered and cannot be manually intervened when a table runs for 24 hours. Or directly influence the operation of the service system after starting the automatic sorting.

And secondly, the shortage of human resources and the database maintenance personnel need to face hundreds of application systems with different service characteristics, and millions of tables can be only sorted by means of a passive fault repair mechanism. However, the problem of 'periodic' performance reduction of the service system caused by incomplete database arrangement inevitably emerges from the water surface gradually. The daily work of DBA becomes a case by case, and the technology is improved in a pure way, so that a vicious circle that the technology iteration is slow and the working efficiency is low is caused.

And thirdly, the risk of defragmentation operation is high, the manual execution of the traditional defragmentation method is carried out in a mode of manually sending commands or scripts, and the risk of misoperation is easily caused when manual operation is carried out in production for a long time.

Disclosure of Invention

Aiming at the defects or the improvement requirements of the prior art, the invention provides a fragmented polling sorting method for a hash grouping database, which aims to solve the problems of failure and secondary problems caused by higher authority of database sorting and maintenance and manual database sorting operation.

In order to achieve the above object, the present invention provides a defragmentation method of a hash packet database, comprising:

counting the database table objects; the index system obtained by the calculation of the mathematical model is sorted by the value of NPAGES;

carrying out hash grouping according to an index system;

step modular packaging is carried out on the target object; a single table is completely sorted and check points are packaged to form a table module;

the flow progress is controlled using a dual poller.

According to the embodiment of the invention, the database table object statistics comprise:

collecting relevant parameter values of all table objects of a database by using a sampling statistical information query method; sampling template is performed by adopting one percent of sampling standard;

the related database system parameter table comprises:

SYSCAT.TABLES；

SYSCAT.INDEXES；

the parameters involved include:

SYSCAT.TABLES.NPAGES；

SYSCAT.TABLES.NAME；

SYSCAT.TABLES.TYPE；

SYSCAT.TABLES.CARD；

SYSCAT.INDEXES.TABNAME；

SYSCAT.INDEXES.INDNAME；

sampling parameter arrays according to the parameters;

the index system obtained by the mathematical model calculation comprises:

confirming a related parameter array closest to the running time according to the fitting function, and performing time estimation sequencing;

the sampling fitting formula is set as follows:

{(x1i,x1i2,x2i,x2i2,x3,yi),i＝1,2,3…n}

x1 is the number of records of the table data size, x12 is the square number of the number of records of the table data size, x2 is the table size, x22 is the square number of the table size, and x3 is the number of fields;

fitting the sampled data to obtain:

the method comprises the following steps of (1) approximating a quadratic nonlinear function according to a scatter diagram of y and x1, y and x12, y and x2, y and x22 and y and x3, and setting a mathematical model estimation expression as follows:

y＝a+b1x1+b2x12+b3x2+b4x22+b5x3 ①

according to the principle of the least square method, the core objective is to minimize the sum of squared deviations θ between actual data yj and the calculated predicted value yi, and the following is set:

θ＝∑(yi-yj)2 ②

solving to obtain { (a, bi), i ═ 1,2,3,4,5} extreme points of a function with theta as a variable, and calculating to obtain a coefficient matrix; substituting the coefficient matrix into (i) to obtain a fitting model; the goodness of fit is close to 1 by calculation, and therefore, the index system is sorted by the value of NPAGES and is the closest to relative execution time sorting.

According to an embodiment of the present invention, the hash grouping according to the index system includes:

constructing a hash table for storing, querying, and locating hash ranges, each record containing the following data items: table names, record numbers, Npages and execution time, and performing hash table descending order arrangement by taking the Npages as key words;

setting a hash value according to an intermittent time window of an application system, performing execution time sequencing on the hash table, and positioning the position of the current hash value according to the hash value;

and carrying out data magnitude classification execution strategy according to the division of the hash value.

According to the embodiment of the present invention, the data magnitude classification execution strategy according to the division of the hash value is as follows:

grouping the Npages below 10W page in time less than the hash value, and executing the concurrent serial execution with the CPU thread number as the maximum value;

if the hash value is larger than the hash value, adopting independent grouping, and controlling the execution progress by using a table module;

the hash grouping is performed at a time below "hash value" and Npages greater than 10W.

According to an embodiment of the present invention, the step of modularly encapsulating the target object includes: standardizing a database fragment sorting step; and adding a check point and a termination judgment condition, and loading the poller to complete the packaging of the branch flow.

According to the embodiment of the invention, the step of defragmentation of the database is normalized by the following steps:

sampling one percent of a table statistical information query table, and sorting the table according to the latest statistical information; RSB for short;

performing surface fragment sorting; short for RG

Performing index defragmentation on the target table; short for RGI

Collecting the total statistical information of the target table and the index; abbreviated as RSA.

According to the embodiment of the invention, the solidification process of the package of the branch process completed by loading the check point and the termination judgment condition into the poller comprises the following steps:

step 1: checking the current system time, and judging whether to perform the next operation; if the time threshold is not reached, performing the operation in the step 2, if the time threshold is exceeded or equal to the time threshold, recording a check point, and waiting for the next window time to perform the step of recording the check point;

step 2: executing RSB operation and judging whether to perform the next operation; if the time threshold is not reached, performing the operation in the step 3, if the time threshold is exceeded or equal to the time threshold, recording a check point, and waiting for the next window time to perform the step of recording the check point;

and step 3: executing RG operation, judging whether to perform the next operation, if not, performing the operation in step 4, if the time threshold is exceeded or equal to the time threshold, recording a check point, and waiting for the time of the next window to perform the step of recording the check point;

and 4, step 4: and executing the RGI operation and judging whether to perform the next operation. If the time threshold is not reached, performing the operation in the step 5, if the time threshold is exceeded or equal to the time threshold, recording a check point, and waiting for the next window time to perform the step of recording the check point;

and 5: executing RSA operation;

step 6: finishing;

the steps 1 to 6 are table module operation flows.

According to the embodiment of the invention, the double pollers comprise a table module progress poller, and a table module grouping poller;

the meter module progress poller automatically reads module step information, records a progress value according to the last successful step in each grouping execution, and continues to perform unfinished operation in a subsequent module by reading the progress value the next day; when the progress value reaches the maximum, the progress value of the progress poller of the meter module is automatically reset;

and the table module grouping poller records the progress value according to the last successful step in each grouping execution, reads the progress value the next day and continues to perform unfinished operation in the subsequent module, and automatically resets the progress value of the table module grouping poller when the progress value reaches the maximum number.

In general, compared with the prior art, the above technical solution contemplated by the present invention can obtain the following beneficial effects due to the provision of the hash packet database defragmentation method:

(1) the database state is checked according to the preset threshold value, so that the stability and the safety of the application system are guaranteed, and high-overhead action can not be performed under the pressure-bearing condition of the database.

(2) The modular encapsulation ensures the integrity of the database defragmentation step.

(3) The database table is grouped by adopting a hash function, so that the performance of equipment is utilized to the maximum extent, and the operation execution efficiency is improved.

(4) Need not artifical the duty, full-automatic execution has greatly practiced thrift the human resource cost.

Drawings

FIG. 1 is a scatter plot of y versus x 1.

FIG. 2 is a scatter plot of y versus x 12.

FIG. 3 is a scatter plot of y versus x 2.

FIG. 4 is a scatter plot of y versus x 22.

FIG. 5 is a scatter plot of y versus x 3.

Fig. 6 is a diagram showing a hash algorithm evaluation method.

FIG. 7 is a flowchart of the table module operation.

FIG. 8 is a table module progress diagram.

FIG. 9 shows a table module grouping diagram of one.

FIG. 10 is a block diagram showing one.

FIG. 11 is a flow chart for database defragmentation.

Detailed Description

In order to make the objects, technical solutions and advantages of the present invention more apparent, the present invention is described in further detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the invention and are not intended to limit the invention. In addition, the technical features involved in the embodiments of the present invention described below may be combined with each other as long as they do not conflict with each other.

The defragmentation method of the hash packet data base is divided into four stages.

The first stage is as follows: and calculating a database table object statistic and index system.

The statistics of the database table objects uses a sampling statistical information query method to collect relevant parameter values of all table objects of the database, and in order to ensure that the interference of the production environment is reduced to the minimum, a sampling template is carried out by adopting one percent of sampling standards, and the lower the sampling template ratio is, the shorter the time cost is.

The parameter table related to the database system comprises:

SYSCAT.TABLES

SYSCAT.INDEXES。

parameters are involved:

SYSCAT.TABLES.NPAGES

SYSCAT.TABLES.NAME

SYSCAT.TABLES.TYPE

SYSCAT.TABLES.CARD

SYSCAT.INDEXES.TABNAME

SYSCAT.INDEXES.INDNAME。

and sampling parameter arrays according to the parameters.

And confirming the related parameter array closest to the running time according to the fitting function, and performing time estimation sequencing.

The sampling fitting formula is set as follows:

{(x1i,x1i 2,x2i,x2i2,x3,yi),i＝1,2,3…n}

x1 is the number of records of the table data amount, x12 is the number of squares of the number of records of the table data amount, x2 is the table size, x22 is the number of squares of the table size, x3 is the number of fields, and the predicted value shows that the number of fields of the table has a general goodness of fit, so that the number of squares is not fit.

Fitting the sampling data:

a scatter plot of y versus x1 as in FIG. 1;

a scatter plot of y versus x12 as in FIG. 2;

a scatter plot of y versus x2 as in FIG. 3;

a scatter plot of y versus x22 as in FIG. 4;

the scatter plot of y versus x3 is shown in FIG. 5.

According to the scatter diagram, the method is approximate to a quadratic nonlinear function, and the mathematical model estimation expression is set as follows:

y＝a+b1x1+b2x12+b3x2+b4x22+b5x3 ①

θ＝∑(yi-yj)2 ②

solving to obtain { (a, bi), i ═ 1,2,3,4,5} extreme points of a function with θ as a variable, and the matrix operation result is:

Variable	Coefficients
		a	29.73822363
b1	-1.344948548
		b2	0.003782627
b3	3.013930995
		b4	-0.008814949
b5	-0.108733778

the coefficient matrix is substituted into (i) to obtain the fitting model of the present example:

y＝29.7382-1.3449x1+0.0038x12+3.0139x2-0.0088x22-0.1087x3

other algorithm values:

the goodness of fit (R Square) is:

R2＝0.99238

the goodness of fit is very close to 1, which shows that the degree of variation of the independent variable x in explaining the dependent variable y is close to 100%, and the fitting effect of the dependent variable y is good. Therefore, the fitting model can accurately express the nonlinear relation between the running time (y) and the records (x1), the table size (x2) and the field number (x 3). Relative execution time can be reflected by the size of the table, i.e. the syscat. Sorting by the value of NPAGES is therefore closest to the relative execution time sorting.

And a second stage: hash grouping is performed.

(1) And constructing a hash table, namely constructing the hash table for storing, querying and locating the hash range.

Each record contains the following data items: table name, number of records, Npages, execution time.

And sorting the hash tables in descending order by taking Npages as a key word.

(2) And setting the hash value according to the intermittent time window of the application system.

And after the execution time of the hash table is sequenced, the position of the current hash value is positioned according to the hash value.

For example: the current system intermittent time is 60 minutes, and after the hash tables are sorted, the hash value is set to be 60 for positioning. The hash value is located between CUSM, TAXE.

(3) And carrying out data magnitude classification execution strategy according to the division of the hash value.

And grouping the data below the hash value time and the Npages below 10W pages, wherein the concurrent number takes the CPU thread number as the maximum value, and the concurrent serial execution is carried out.

Individual groupings are used for more than hash values, and the table module is used to control execution progress.

For example: let the number of CPU threads be 2, hash value x, run time t1, t2, t 3. The system design redundancy time is 30%.

Thread one calculation formula t1+ (tn-1) + (tn-2) < x 30%

Thread two calculates the formula t2+ (tn-1) + (tn-2) < x 30%.

The hash algorithm valuing method is shown in fig. 6.

In the stage, the application operation pause time is used for carrying out hash value taking, and time length splicing is carried out on the modules based on time sequencing.

And starting a parallel process number according to the CPU process number.

The method is executed by adopting a concurrent serial method. Therefore, the operation execution can be completed at the highest speed by utilizing the performance of the CPU to the maximum extent.

And a third stage: and step modular packaging is carried out on the target object.

(1) Standardizing a database defragmentation step:

one percent of table statistical information query table sampling is carried out, the defragmentation speed can be improved according to the latest statistical information collating table, runtables table BERNOULLI, RSB for short;

performing table defragmentation, wherein a Reorg table is called RG for short;

performing index defragmentation on a target table, wherein the Reorg index is called RGI for short;

collecting total statistical information of a target table and indexes, wherein runtables table and index all are called RSA for short;

and after the single table is completely sorted and the check points are packaged, the table module is called for short.

(2) Adding check points and termination judgment conditions, loading the polling device to complete the packaging of the branch flow, wherein the curing flow is as follows:

and 5: executing RSA operation;

step 6: finishing;

the steps 1 to 6 are table module operation flows, and a table module operation flow chart is shown in fig. 7.

A fourth stage: pollers and process progress control.

Reasonable control over the process progress is achieved using an adaptive dual-poller, which includes a table module progress poller, a table module grouping poller.

The progress poller of the meter module automatically reads the step information of the module, records the progress value according to the last successful step in each grouping execution, reads the progress value the next day and continues to perform unfinished operation in the subsequent module, when the progress value reaches the maximum, the progress value of the progress poller of the meter module is automatically reset, and the progress of the meter module is displayed as shown in figure 8.

And the table module grouping poller automatically reads table grouping information, generates a poller counter according to the grouping number, records the current running progress value, and reads the progress value the next day to continue the execution of the subsequent grouping operation. The table module group poller progress value is automatically reset when the progress value reaches a maximum number.

In the table module grouping, one or more table modules are loaded according to a hashing algorithm, and 4 table modules are grouped in an example in fig. 9.

And in the table module grouping poller, a polling identifier is automatically generated according to the grouping number.

For example: as shown in fig. 10, if the number of current packets is 20, the polling flag is reset to 1 when 20 is reached. The goal of polling the module group is achieved.

The flow of defragmentation of the database according to the four stages of the hash packet database defragmentation method is shown in fig. 11.

1. And checking the counter, sending a short message to inform the automatic task arranging content to inform a manager of the automatic task executing content and the executing time.

2. The application determines whether the current time and the exceptional time list time check are within the threshold, and skips execution if the current time is not suitable for database defragmentation.

3. And judging whether the current database related threshold process check passes or not, checking the number of database processes and the database table lock waiting condition, and performing database defragmentation if the current executed object has no lock waiting condition. Execution is skipped if the current database process threshold.

4. And traversing the sorting operation group, calculating an identification file counter and resetting the identification file. And accurately positioning the job groups needing to be executed, and carrying out multi-process distribution according to the number of CPUs (central processing units) of the database server.

5. The executed packets are selected for automatic defragmentation and wait for completion.

6. And acquiring an operation execution result, and sending a short message to report the execution condition.

The sending/receiving subsystem can carry out point-to-point communication according to a self-defined protocol, and can also carry out data forwarding through a special address resolution server.

It will be understood by those skilled in the art that the foregoing is only a preferred embodiment of the present invention, and is not intended to limit the invention, and that any modification, equivalent replacement, or improvement made within the spirit and principle of the present invention should be included in the scope of the present invention.

Claims

1. The defragmentation method of the hash packet data base is characterized by comprising the following steps:

database table object statistics: collecting relevant parameter values of all table objects of a database by using a sampling statistical information query method; sampling template is performed by adopting one percent of sampling standard;

the related database system parameter table comprises:

SYSCAT.TABLES；

SYSCAT.INDEXES；

the parameters involved include:

SYSCAT.TABLES.NPAGES；

SYSCAT.TABLES.NAME；

SYSCAT.TABLES.TYPE；

SYSCAT.TABLES.CARD；

SYSCAT.INDEXES.TABNAME；

SYSCAT.INDEXES.INDNAME；

sampling parameter arrays according to the parameters;

and calculating by a mathematical model to obtain an index system: the mathematical model is a fitting model, a relevant scatter diagram is obtained by fitting sampling data, and a computer number matrix is obtained according to the least square method principle to substitute the computer number matrix into the fitting model to calculate and confirm the value of the relevant parameters of the index system; confirming a related parameter array closest to the running time according to the fitting function, and performing time estimation sequencing;

sequencing the obtained NPAGES values of the index system;

carrying out hash grouping according to an index system;

performing step modular packaging on the target object: standardizing a database fragment sorting step; adding check points and termination judgment conditions, and loading the polling device to complete the packaging of the branch flow;

the step of standardizing the database defragmentation module comprises the following steps:

performing surface fragment sorting; RG for short;

performing index defragmentation on the target table; RGI for short;

collecting the total statistical information of the target table and the index; RSA for short;

a single table is completely sorted and check points are packaged to form a table module;

and controlling the process progress by using a double-poller, wherein the double-poller comprises a table module progress poller, and a table module grouping poller which uses an adaptive double-poller to realize reasonable control on the process progress.

2. The defrolling method for a hashed packet data base according to claim 1, wherein the performing hashed packets according to an index system comprises:

3. The defrolling method for a hashed packet data base according to claim 2, wherein the data level classification implementation policy based on the division of the hashed values is as follows:

4. The defragmentation method for a hashed database according to claim 1, wherein said adding a checkpoint and terminate determination condition into a solidification process in which the poller completes encapsulation of the branch process comprises:

and 4, step 4: executing RGI operation, judging whether to perform the next operation, if not, performing the operation in the step 5, if the time threshold is exceeded or equal to the time threshold, recording a check point, and waiting for the time of the next window to perform the step of recording the check point;

and 5: executing RSA operation;

step 6: finishing;

the steps 1 to 6 are table module operation flows.

5. The defragmentation round robin collating method of a hash grouping database according to claim 1, wherein said table module progress poller automatically reads module step information, records progress values according to the last successful step in each grouping execution, reads progress values the next day and continues to perform unfinished jobs in subsequent modules; when the progress value reaches the maximum, the progress value of the progress poller of the meter module is automatically reset;