CN107818093A - A kind of localization method, the apparatus and system of SQL scripts - Google Patents
A kind of localization method, the apparatus and system of SQL scripts Download PDFInfo
- Publication number
- CN107818093A CN107818093A CN201610816425.9A CN201610816425A CN107818093A CN 107818093 A CN107818093 A CN 107818093A CN 201610816425 A CN201610816425 A CN 201610816425A CN 107818093 A CN107818093 A CN 107818093A
- Authority
- CN
- China
- Prior art keywords
- sql scripts
- sql
- scripts
- cycle
- operational factor
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F11/00—Error detection; Error correction; Monitoring
- G06F11/30—Monitoring
- G06F11/3003—Monitoring arrangements specially adapted to the computing system or computing system component being monitored
- G06F11/302—Monitoring arrangements specially adapted to the computing system or computing system component being monitored where the computing system component is a software system
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
- G06F16/2438—Embedded query languages
Landscapes
- Engineering & Computer Science (AREA)
- Physics & Mathematics (AREA)
- Theoretical Computer Science (AREA)
- Mathematical Physics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computing Systems (AREA)
- Computational Linguistics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- Quality & Reliability (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
Embodiments of the invention provide a kind of localization method, the apparatus and system of SQL scripts, can relatively accurately orient the larger SQL scripts of performance consumption, improve the utilization rate of cpu resource and I/O resources in server.This method includes：Obtain operational factor, the consumed resource when operational factor is used to indicate to run the SQL scripts, N ＞ 1 caused by each SQL scripts in the N number of SQL scripts run in set period；According to the operational factor of each SQL scripts, resource consumption accounting of each SQL scripts in N number of SQL scripts in the set period is calculated；Resource consumption accounting is more than at least one SQL scripts of default threshold value, is defined as target SQL scripts.
Description
Technical field
The present invention relates to field of computer technology, more particularly to a kind of localization method, the apparatus and system of SQL scripts.
Background technology
SQL (Structured Query Language, SQL) script, refers to combined by SQL statement
The command set come, to realize certain query function, for example, nearest three months all clients for staying in Beijing of inquiry.
It may run tens of thousands of individual SQL scripts for many enterprises, in the database of its server, and some SQL pin
There may be the larger SQL statement of some performance consumptions in this, for example, in SQL scripts A, certain SQL statement has used entirely
The mode of table scan is searched, but in fact, can more be quickly obtained lookup result using the mode of index search, this
A little larger SQL statements of performance consumption not only reduce the execution efficiency of SQL scripts, can also take other SQL during longtime running
CPU (Central Processing Unit, central processing unit) resources and I/O (Input/Output, the input/defeated of script
Go out) resource, make the response speed of whole server reduce, or even machine phenomenon of delaying occur.
On the other hand, some database software provide some monitoring functions for being directed to SQL scripts for user, with Oracle numbers
Exemplified by library software, it can be user count and be shown in it is adjacent twice start shooting or the adjacent period shut down twice in,
CPU performs the accumulative duration taken of each SQL script, so as to obtain the accumulative ranking for taking duration of SQL scripts.
However, for some SQL script, CPU performs the accumulative duration taken of the SQL scripts, might not be with this
The execution efficiency of SQL scripts is inversely proportional, for example, SQL scripts B needs to run 5 hours daily, and SQL scripts C is only run daily
3 minutes, this was not meant to that SQL scripts B execution efficiency is less than SQL scripts C, that is to say, that when being taken by counting accumulative
Long method can accurately not reflect the execution efficiency of SQL scripts, and therefore, user is difficult to be accurately positioned by this method
Go out actual performance and consume larger SQL scripts.
The content of the invention
Embodiments of the invention provide a kind of localization method, the apparatus and system of SQL scripts, can relatively accurately position
Go out the larger SQL scripts of performance consumption, improve the utilization rate of cpu resource and I/O resources in server.
To reach above-mentioned purpose, embodiments of the invention adopt the following technical scheme that：
In a first aspect, embodiments of the invention provide a kind of localization method of SQL scripts, including：Obtain in set period
Each operational factor caused by SQL scripts in N number of SQL scripts of interior operation, the operational factor are used to indicate to run the SQL scripts
When consumed resource, N ＞ 1；According to the operational factor of each SQL scripts, calculate each SQL scripts in the set period and exist
Resource consumption accounting in N number of SQL scripts；If the first SQL scripts (the first SQL scripts are any one in N number of SQL scripts)
Resource consumption accounting is more than threshold value, then explanation is consumed when the first SQL scripts are performed in set period a large amount of where it
The resource of database server, i.e., it is likely to, containing the larger SQL statement of performance consumption, therefore, to incite somebody to action in the first SQL scripts
First SQL scripts are defined as target SQL scripts, so that developer optimizes to target SQL scripts in time, avoid this
Database server is excessively taken after target SQL script long-plays
The problem of cpu resource and I/O resources, so as to improve the utilization of cpu resource and I/O resources in database server
Rate.
In a kind of possible implementation, the set period includes the continuous M cycle of operation, M ＞ 1；Wherein, according to
The operational factor of each SQL scripts, calculates resource consumption of each SQL scripts in N number of SQL scripts in the set period
Accounting, including：For each cycle of operation, according to the operational factor of each SQL scripts in the cycle of operation, calculate in the operation
Cycle consumption accounting of each SQL scripts in N number of SQL scripts in cycle；For each SQL scripts, the SQL scripts are existed
The average value of M cycle consumption accounting in the M cycle of operation, the resource consumption accounting as the SQL scripts.
In a kind of possible implementation, the set period includes the continuous M cycle of operation, M ＞ 1；Wherein, obtain
Each operational factor caused by SQL scripts in the N number of SQL scripts run in set period, including：For each SQL scripts,
The SQL scripts caused M groups operational factor within the M cycle of operation is obtained respectively；Determine peeling off in the M group operational factors
Value；The outlier is removed from the M group operational factors, so as to which the larger operational factor of error be rejected, to improve in terms of subsequently
Calculate computational accuracy during resource consumption accounting.
In a kind of possible implementation, calculate in the set period each SQL scripts in N number of SQL scripts
In resource consumption accounting before, in addition to：Acquisition performs the database server of each SQL scripts in the set period
Resources occupation rate；Wherein, according to the operational factor of each SQL scripts, each SQL scripts are calculated in the set period in the N
Resource consumption accounting in individual SQL scripts, including：It is for each SQL scripts, the operational factor of the SQL scripts is N number of with this
The ratio of the operational factor sum of SQL scripts is as relative resource consumption accounting；By the relative resource consumption accounting and the resource
The product of occupancy, the resource consumption accounting as the SQL scripts.The resource consumption accounting being calculated using this method is one
Individual absolute value, i.e., the resource of each SQL scripts database server of actual consumption in set period, can cause exploit person
The resource consumption accounting of actual consumption when member intuitively knows each SQL scripts operation.
In a kind of possible implementation, resource consumption accounting is being more than at least one SQL pin of default threshold value
This, is defined as after target SQL scripts, in addition to：Obtain the history resource consumption accounting of target SQL scripts；According to the mesh
The history resource consumption accounting of SQL scripts is marked, the resource consumption of the database server of prediction operation target SQL scripts becomes
Gesture, if if predicting that target SQL scripts continue to run with, it will when exceed database server carrying energy
Power, database server is caused to break down, so as to carry out early warning for developer.
In a kind of possible implementation, each SQL scripts in the N number of SQL scripts run in set period are obtained
Caused operational factor, including：Produced when obtaining each SQL scripts operation that database server reports in each cycle of operation
Operational factor；According to the operational factor of each SQL scripts in each cycle of operation, each SQL pin in set period are calculated
Operational factor caused by this.
In a kind of possible implementation, the operational factor includes：Taken when performing each SQL scripts CPU it is total when
It is long；And/or perform the number that logic caused by each SQL scripts is read.When operational factor takes CPU's to perform SQL scripts
During total duration, the relative resource consumption accounting calculated can reflect Expenditure Levels of the SQL scripts to cpu resource；Work as operation
When parameter is performs the number of logic reading caused by SQL scripts, the relative resource consumption accounting calculated can reflect SQL pin
This Expenditure Levels to I/O resources.
Second aspect, embodiments of the invention provide a kind of positioner of SQL scripts, including：Acquiring unit, for obtaining
Each operational factor caused by SQL scripts in the N number of SQL scripts run in set period is taken, the operational factor is used to indicate
Run the consumed resource during SQL scripts, N ＞ 1；Computing unit, for the operational factor according to each SQL scripts, calculate
Resource consumption accounting of each SQL scripts in N number of SQL scripts in the set period；Positioning unit, for resource to be disappeared
At least one SQL scripts that accounting is more than default threshold value are consumed, are defined as target SQL scripts.
In a kind of possible implementation, the set period includes the continuous M cycle of operation, M ＞ 1；The calculating list
Member, it is specifically used for：For each cycle of operation, according to the operational factor of each SQL scripts in the cycle of operation, calculate in the fortune
Cycle consumption accounting of each SQL scripts in N number of SQL scripts in the row cycle；For each SQL scripts, by the SQL scripts
The average value of M cycle consumption accounting within the M cycle of operation, the resource consumption accounting as the SQL scripts.
In a kind of possible implementation, the set period includes the continuous M cycle of operation, M ＞ 1；The acquisition list
Member, it is specifically used for：For each SQL scripts, the SQL scripts caused M groups operation ginseng within the M cycle of operation is obtained respectively
Number；Determine the outlier in the M group operational factors；The outlier is removed from the M group operational factors, obtains the set period
Operational factor caused by interior each SQL scripts.
In a kind of possible implementation, the acquiring unit, it is additionally operable to acquisition and each SQL is performed in the set period
The resources occupation rate of the database server of script；The computing unit, is specifically used for：For each SQL scripts, by the SQL pin
The ratio of this operational factor and the operational factor sum of N number of SQL scripts is as relative resource consumption accounting；For each
SQL scripts, by the relative resource consumption accounting of the SQL scripts and the product of resources occupation rate, the resource as the SQL scripts disappears
Consume accounting.
In a kind of possible implementation, the acquiring unit, the history resource for being additionally operable to obtain target SQL scripts disappears
Consume accounting；The computing unit, is additionally operable to the history resource consumption accounting according to target SQL scripts, and prediction runs target SQL
The resource consumption trend of the database server of script.
In a kind of possible implementation, the set period includes the continuous M cycle of operation, M ＞ 1；The acquisition list
Member, specifically for obtaining the operation of database server each SQL scripts that each cycle of operation reports in the M cycle of operation
Caused operational factor；The computing unit, is specifically used for：For each SQL scripts, according to the SQL in each cycle of operation
The operational factor of script, calculate the operational factor caused by the SQL scripts in set period.
The third aspect, embodiments of the invention provide a kind of O＆M server, including：Processor, memory, bus and logical
Believe interface；The memory is used to store computer executed instructions, and the processor is connected with the memory by the bus, when the fortune
When tieing up server operation, computer executed instructions of the computing device memory storage, so that the O＆M server is held
The localization method of the above-mentioned any one SQL scripts of row.
Fourth aspect, embodiments of the invention provide a kind of alignment system of SQL scripts, including O＆M server, and
The database being connected with O＆M server, wherein, the alignment system of the SQL scripts, for monitoring and in maintenance service system
The SQL scripts of each database server operation.
5th aspect, the embodiments of the invention provide a kind of computer-readable storage medium, for saving as above-mentioned O＆M service
Computer software instructions used in device, it, which is included, is used to perform program of the above-mentioned aspect designed by the O＆M server.
In the present invention, the positioner of above-mentioned SQL scripts and the name of O＆M server do not form limit in itself to equipment
Fixed, in practical implementations, these equipment can occur with other titles.As long as the function of each equipment is similar with the present invention, i.e.,
Belong within the scope of the claims in the present invention and its equivalent technologies.
In addition, second aspect technique effect caused by any design method into the 5th aspect can be found in first aspect
Technique effect caused by middle different designs mode, here is omitted.
The aspects of the invention or other aspects can more straightforwards in the following description.
Brief description of the drawings
In order to illustrate more clearly about the embodiment of the present invention or technical scheme of the prior art, below will be to embodiment or existing
There is the required accompanying drawing used in technology description to be briefly described.
Fig. 1 is a kind of structural representation one of the alignment system of SQL scripts provided in an embodiment of the present invention；
Fig. 2 is a kind of structural representation two of the alignment system of SQL scripts provided in an embodiment of the present invention；
Fig. 3 is a kind of structural representation three of the alignment system of SQL scripts provided in an embodiment of the present invention；
Fig. 4 is a kind of structural representation of computer equipment provided in an embodiment of the present invention；
Fig. 5 is a kind of schematic flow sheet one of the localization method of SQL scripts provided in an embodiment of the present invention；
Fig. 6 is a kind of forecast consumption accounting provided in an embodiment of the present invention and the relation schematic diagram of time；
Fig. 7 is a kind of structural representation one of server provided in an embodiment of the present invention；
Fig. 8 is a kind of structural representation two of server provided in an embodiment of the present invention.
Embodiment
Below in conjunction with the accompanying drawing in the embodiment of the present invention, the technical scheme in the embodiment of the present invention is described, shown
So, described embodiment is only part of the embodiment of the present invention, rather than whole embodiments.
In addition, term " first ", " second " are only used for describing purpose, and it is not intended that instruction or hint relative importance
Or the implicit quantity for indicating indicated technical characteristic.Thus, define " first ", the feature of " second " can be expressed or
Implicitly include one or more this feature.In the description of the invention, unless otherwise indicated, " multiple " are meant that two
Individual or two or more.
Embodiments of the invention provide a kind of localization method of SQL scripts, and this method can be applicable to the positioning system of SQL scripts
In system, the system can be the operational systems such as DbaaS (Database-as-a-Service, database service).
It is exemplary, as shown in figure 1, the alignment system 100 of above-mentioned SQL scripts includes O＆M server 11, and with fortune
Tie up the connected database 12 of server 11.The alignment system 100 of the SQL scripts, for every in monitoring and maintenance service system 200
The SQL scripts that individual database server (DataBase Server, DB server) 21 is run.
Specifically, the O＆M server 11, which can gather each database server 21 in set period, runs SQL pin
The operational factor of this when, the consumed resource when operational factor is used to indicate to run the SQL scripts, for example, the SQL scripts
Number of run, run time etc..And then O＆M server 11 can calculate and specifying according to the operational factor of each SQL scripts
The resource consumption accounting of each SQL scripts in period, for example, the cpu resource accounting that each SQL scripts consumed in 1 hour, with
And I/O resource accountings for being consumed in 1 hour of each SQL scripts etc., so, when any SQL scripts, for example, the first SQL scripts
Resource consumption accounting when being more than threshold value, then illustrate that the first SQL scripts consume the service of mass data storehouse in set period
The resource of device 21, i.e., be likely in the first SQL scripts containing the larger SQL statement of performance consumption, therefore, can by this first
SQL scripts are defined as target SQL scripts, so that developer optimizes to target SQL scripts in time, avoid the target
The problem of cpu resource and I/O resources of database server 21 are excessively taken after SQL script long-plays.
In addition, the operational factor that O＆M server 11 can also collect different time is stored in database 12, and
The resource consumption accounting for each SQL scripts being calculated is stored in database 12 as history resource consumption accounting, with
It is easy to resource consumption trend of the follow-up O＆M server 11 according to these history resource consumption accounting forecast database servers 21.
It is exemplary, as shown in Fig. 2 can in operation system 200 installation agent (agent) in each DB servers 21
Software 13, and message server 14 is set between O＆M server 11 and DB servers 21, so, when O＆M server 11 needs
Each SQL scripts are obtained in set period during caused operational factor, collection can be sent to database server 21 and referred to
Order, so as to perform operational factor caused by each SQL scripts by the collection of agent software 13；Agent software 13 will collect
Operational factor is sent to message server 14, and message queue is provided with message server 14, and available for keeping in, each agency is soft
The operational factor that part 13 collects, and the operational factor is sent to O＆M server 11, obtain O＆M server 11 each
SQL scripts caused operational factor in set period, it can be seen that the alignment system 100 of SQL scripts passes through agent software 13
The running situation of SQL scripts in DB servers 21 is monitored, the shadow to realizing miscellaneous service process in operation system 200 can be avoided
Ring.
Further, as shown in figure 3, the alignment system 100 of above-mentioned SQL scripts can also include and the phase of O＆M server 11
Web server 15 even, portal (entrance) software is provided with web server 15, portal softwares can be used as whole SQL
The user interface of the alignment system 100 of script, the miscellaneous service for receiving developer is asked, so as to control O＆M server
11 realize the position fixing process of above-mentioned SQL scripts.
It should be noted that the O＆M server 11 in the alignment system 100 of above-mentioned SQL scripts can be one or more
It is individual, when O＆M server 11 is multiple, the position fixing process of above-mentioned SQL scripts can be realized in the form of server cluster, this
Inventive embodiments are not limited in any way to this.
As shown in figure 4, O＆M server 11, DB servers 21, message server 14 and web server in Fig. 1-3
15 can be realized in a manner of the computer equipment (or system) in Fig. 4.
Fig. 4 show computer equipment schematic diagram provided in an embodiment of the present invention.Computer equipment 500 includes at least one
Processor 501, communication bus 502, memory 503 and at least one communication interface 504.
Processor 501 can be a general central processor (CPU), microprocessor, ASIC
(application-specific integrated circuit, ASIC), or it is one or more for controlling the present invention program
The integrated circuit that program performs.
Communication bus 502 may include a path, and information is transmitted between said modules.The communication interface 504, using appoint
A kind of device of what transceiver, for other equipment or communication, such as Ethernet, wireless access network (RAN), wirelessly
LAN (Wireless Local Area Networks, WLAN) etc..
Memory 503 can be read-only storage (read-only memory, ROM) or can store static information and instruction
Other kinds of static storage device, random access memory (random access memory, RAM) or letter can be stored
Breath and other kinds of dynamic memory or the EEPROM (Electrically of instruction
Erasable Programmable Read-Only Memory, EEPROM), read-only optical disc (Compact Disc Read-
Only Memory, CD-ROM) or other optical disc storages, laser disc storage (including compression laser disc, laser disc, laser disc, digital universal
Laser disc, Blu-ray Disc etc.), magnetic disk storage medium or other magnetic storage apparatus or can be used in carrying or store with referring to
The desired program code of order or data structure form simultaneously can be by any other medium of computer access, but not limited to this.
Memory can be individually present, and be connected by bus with processor.Memory can also integrate with processor.
Wherein, the memory 503 is used to store the application code for performing the present invention program, and by processor 501
Performed to control.The processor 501 is used to perform the application code stored in the memory 503.
In the specific implementation, as a kind of embodiment, processor 501 can include one or more CPU, such as in Fig. 4
CPU0 and CPU1.
In the specific implementation, as a kind of embodiment, computer equipment 500 can include multiple processors, such as in Fig. 4
Processor 501 and processor 508.Each in these processors can be monokaryon (single-CPU) processor,
It can also be multinuclear (multi-CPU) processor.Here processor can refer to one or more equipment, circuit, and/or
Process cores for processing data (such as computer program instructions).
In the specific implementation, as a kind of embodiment, computer equipment 500 can also include output equipment 505 and input
Equipment 506.Output equipment 505 and processor 501 communicate, and can carry out display information in many ways.For example, output equipment 505
Can be liquid crystal display (liquid crystal display, LCD), Light-Emitting Diode (light emitting diode,
LED) display device, cathode-ray tube (cathode ray tube, CRT) display device, or projecting apparatus (projector) etc..
Input equipment 506 and processor 501 communicate, and can receive the input of user in many ways.For example, input equipment 506 can be with
It is mouse, keyboard, touch panel device or sensing equipment etc..
Above-mentioned computer equipment 500 can be an a general purpose computing device either dedicated computing machine equipment.
In the specific implementation, computer equipment 500 can be desktop computer, portable computer, the webserver, palm PC
(Personal Digital Assistant, PDA), cell phone, tablet personal computer, wireless terminal device, communication equipment, insertion
Formula equipment or the equipment for having similar structures in Fig. 4.The unlimited type for determining computer equipment 500 of the embodiment of the present invention.
As shown in figure 5, a kind of schematic flow sheet of the localization method of the SQL scripts provided for embodiments of the invention, should
Method includes：
101st, server obtains in the N number of SQL scripts run in set period and runs ginseng caused by each SQL scripts
Number, N ＞ 1.
Wherein, the consumed resource when operational factor is used to indicate to run each SQL scripts, for example, for each SQL
For script, the operational factor takes CPU total duration when can include performing the SQL scripts；And/or perform the SQL scripts
Caused logic reads the number of (logical reads) etc..
Certainly, the operational factor can also include：The execution time of the SQL scripts, perform and patrolled caused by the SQL scripts
The number for writing (logical writes) is collected, performs time of physical write (physical writes) caused by the SQL scripts
Number, at least one in the number of physical read (physical reads) caused by the SQL scripts is performed, people in the art
Member can be according to being actually needed or practical experience sets the particular content of operational factor, and the bright embodiment of this law do not make any limit to this
System.
Specifically, as shown in Figure 1-Figure 3, multiple SQL scripts are run in DB servers 21, DB servers 21 can be with the cycle
Property reports to O＆M server 11：Within this cycle of operation, the operation ginseng of each SQL scripts in all SQL scripts of operation
Number, for example, the cycle of operation is 1 minute, then, DB servers 21 are per minute can be reported to O＆M server 11 at this 1 point
The operational factor of each SQL scripts of operation in clock, for example, the total duration that SQL scripts 1 take CPU was performed in this 1 minute,
So, all operational factors received can be stored in database 12 by O＆M server 11, when O＆M server 11 needs
Obtain it is each caused by SQL scripts during operational factor in set period, can be according to each week described in database 12
The operational factor of different SQL scripts in phase, calculate each SQL scripts caused operational factor in set period.
For example, if set period is 8:00-8:10, the cycle of operation is 1 minute, then, O＆M server 11 can basis
8 described in database 12:00-8:The periodic duty parameter of totally 10 cycles of operation, cumulative calculation obtain this 10 fortune in 10
In the row cycle, in N number of SQL scripts of operation, operational factor caused by each SQL scripts.
Or, it is assumed that the set period includes M (M ＞ 1) the individual cycle of operation；So, for any one SQL script, fortune
Dimension server 11 can obtain within this M cycle of operation respectively, M group operational factors caused by the SQL scripts；And then O＆M
Server 11 can determine the outlier in this M group operational factor；And remove the outlier from this M group operational factor, from
And reject the larger operational factor of error, to improve computational accuracy during follow-up computing resource consumption accounting.
For example, being illustrated using the total duration for performing the occupancy of SQL scripts 1 CPU as operational factor, include in above-mentioned set period
In 10 cycles of operation, perform 10 groups of operational factors that SQL scripts 1 obtain and be followed successively by：10,12,11,8,14,2,13,9,8,
12, now, the normal range (NR) of this 10 groups of operational factors is calculated using Distance Theory, the normal range (NR)=average value ± definitely from
Difference.
Wherein, average value=(10+12+11+8+14+2+13+9+8+12)/10=9.9；
Each value and average=(0.1+ of the absolute value of average value difference in absolute deviation=this 10 groups of operational factors
2.1+1.1+1.9+4.1+7.9+3.1+1.9+2.1)/10=2.43
Therefore, normal range (NR)=9.9 ± 2.43=[7.47,12.33], then, can be by above-mentioned 10 groups of operational factors
In be not belonging to the operational factor of the normal range (NR) as outlier, i.e. outlier is 14,2 and 13, then, by the outlier from this
After being removed in M group operational factors, obtaining the caused operational factor in set period of SQL scripts 1 is respectively：10,12,11,8,
9,8,12.
102nd, server is according to the operational factors of each SQL scripts, calculates in set period each SQL scripts N number of
Resource consumption accounting in SQL scripts.
Exemplary, the method that the embodiment of the present invention provides following 3 kinds of computing resources consumption accounting：
Method 1
As shown in table 1, it is operational factor caused by 3 SQL scripts difference of operation altogether in set period, operation ginseng
Number takes CPU total duration (referred to as CPU total duration) when including performing SQL scripts, then, the resource consumption of SQL scripts 1
Accounting=execution SQL scripts 1 take CPU all SQL scripts occupancy CPU of total duration/execution total duration=50/ (50+20+
30)=50%, similar, the resource of resource consumption accounting=20/ (50+20+30)=20%, SQL scripts 3 of SQL scripts 2 disappears
Consume accounting=30/ (50+20+30)=30%.
Table 1
Method 2
Before step 102 is performed, O＆M server 11 can also obtain out of DB servers 21 that perform each SQL scripts
The resources occupation rate of the DB servers 21 above-mentioned set period Nei is taken, the resources occupation rate can be that CPU usage or I/O take
Rate.
So, can be by the SQL scripts when calculating resource consumption accounting of each SQL scripts in N number of SQL scripts
Operational factor and N number of SQL scripts operational factor sum ratio as relative resource consumption accounting；And then by Relative resource
Consume the product of accounting and above-mentioned resources occupation rate, the resource consumption accounting as the SQL scripts.
Optionally, in DB servers 21, in addition to operating system is installed, the number for performing SQL scripts can only be disposed
According to library software, for example, Oracle, MySQL or SQL Server etc., so, the resource that DB servers 21 take have 90% with
On consume when all performing SQL scripts, therefore, the above-mentioned resource consumption accounting that calculates can be approximately when performing SQL scripts
The resource of the actual consumption of DB servers 21.
Assuming that the resources occupation rate of the DB servers 21 got is 60%, still as shown in table 1, now, SQL scripts 1
Relative resource consumption accounting=execution SQL scripts 1 take CPU all SQL scripts of total duration/execution take CPU it is total when
Long=50/ (50+20+30)=50%, then, resource consumption accounting=50%*60%=30% of SQL scripts 1.
As can be seen that the resource consumption accounting that method 1 is calculated is a relative value, N number of SQL scripts are may be used to indicate
Between relative resource consumption situation, and the resource consumption accounting that method 2 is calculated is an absolute value, i.e., each SQL pin
The resource of this DB server 21 of actual consumption in set period, in order to which developer can definitely know each SQL
The script resource size actual when DB servers 21 are run.
Further, when operational factor takes CPU total duration to perform SQL scripts, the Relative resource calculated disappears
Consumption accounting can reflect Expenditure Levels of the SQL scripts to cpu resource, and now, above-mentioned resources occupation rate is specifically as follows CPU and accounted for
With rate, then, the resource consumption accounting calculated is the cpu resource accounting of corresponding SQL scripts actual consumption；When operation is joined
When number is performs the number of logic reading caused by SQL scripts, the relative resource consumption accounting calculated can reflect SQL scripts
To the Expenditure Levels of I/O resources, now, above-mentioned resources occupation rate is specifically as follows I/O occupancies, then, the resource calculated
Consumption accounting is the I/O resource accountings of corresponding SQL scripts actual consumption.
Method 3
When above-mentioned set period includes continuous M (M ＞ 1) the individual cycles of operation, for each cycle of operation, O＆M service
Device 11 can calculate each SQL scripts disappearing in N number of SQL scripts in the cycle of operation with according to the method described above 1 or method 2
Accounting (being referred to as cycle consumption accounting in the embodiment of the present invention) is consumed, obtains the M cycle consumption accounting in the M cycle of operation, this
Sample, for each SQL scripts, the average value of this M cycle consumption accounting can be accounted for as the resource consumption of the SQL scripts
Than.
Exemplary, as shown in table 2, above-mentioned set period includes continuous 6 cycles of operation, can be according to the method described above
The cycle consumption accounting of calculating SQL scripts 1, SQL scripts 2 and SQL scripts 3 within each cycle of operation respectively, and then will
Resource consumption accounting of the average value of the 6 cycles consumption accounting arrived as the SQL scripts.
Wherein, when calculating cycle consumes accounting, when operational factor includes outlier, such as cycle 3 and cycle in table 2
Shown in 6, in order to ensure the precision of the resource consumption accounting subsequently calculated, the row operational factor can be not considered, now, with
Resource consumption accounting of the average value of remaining 4 cycles consumption accounting as each SQL scripts.
Table 2
So far, can be calculated by above method 1- methods 3, server according to the operational factor of each SQL scripts
Resource consumption accounting of each SQL scripts in N number of SQL scripts in set period.
If the 103, the resource consumption accounting of the first SQL scripts is more than threshold value, server then determines that the first SQL scripts are target
SQL scripts.
Wherein, the first SQL scripts are one or more of N number of SQL scripts.
Specifically, by step 102, resource consumption accounting of each SQL scripts in set period can be obtained, that
, when the resource consumption accounting of some SQL script, such as the first SQL scripts is more than threshold value, then explanation is running the first SQL
The excessive resource of DB servers 21 is occupied during script, for example, the cpu resource of DB servers 21 or I/O resources, then, this
It is likely to, containing the larger SQL statement of performance consumption, therefore, the first SQL scripts can be defined as into target in one SQL scripts
SQL scripts, so that developer optimizes to target SQL scripts in time, after avoiding target SQL script long-plays
Excessively take the cpu resource and I/O resources of DB servers 21.
104th, server obtains the history resource consumption accounting of target SQL scripts.
105th, server is according to the history resource consumption accounting of target SQL scripts, the data of prediction operational objective SQL scripts
The resource consumption trend of storehouse server.
Optionally, after O＆M server 11 determines target SQL scripts, DB can also be taken to target SQL scripts
The resource of server 21 is predicted, if if predicting that target SQL scripts continue to run with, it will when is exceeding DB
The bearing capacity of server 21, DB servers 21 are caused to break down, so as to carry out early warning for developer.
Specifically, at step 104, server can obtain the history resource consumption accounting of target SQL scripts first,
That is resource consumption accounting of the target SQL scripts in each cycle of operation before current time.
And then server can utilize least square method scheduling algorithm according to the history resource consumption accounting of target SQL scripts
Model, forecast consumption accounting of the target SQL scripts when running in future is predicted, obtains forecast consumption accounting as shown in Figure 6
With the resource consumption trend of the relation of time, i.e. DB servers 21, on reaching certain when the resource consumption accounting of SQL scripts
During limit value, the bearing capacity of DB servers 21 can be exceeded, cause DB servers 21 to break down, it is therefore, pre- according to Fig. 6
Survey consumption accounting and the relation of time, the forecast consumption accounting that can predict target SQL scripts reach corresponding during higher limit
Time point, to prompt developer to be optimized before the time point to target SQL scripts, so as to improve DB servers 21
Resource utilization.
Above-described embodiment is mainly carried out from the angle of interaction between each network element to scheme provided in an embodiment of the present invention
Introduce.It is understood that each network element, such as O＆M server 11, DB servers 21 etc., in order to realize above-mentioned function, it is wrapped
The corresponding hardware configuration of each function of execution and/or software module are contained.Those skilled in the art should be readily appreciated that, tie
Close the embodiments described herein description each example unit and algorithm steps, the present invention can by hardware or hardware and in terms of
The combining form of calculation machine software is realized.Some functions is held in a manner of hardware or computer software driving hardware actually
OK, the application-specific and design constraint depending on technical scheme.Professional and technical personnel can come to each specific application
Described function is realized using distinct methods, but this realization is it is not considered that beyond the scope of this invention.
The embodiment of the present invention can carry out function mould according to above method example to O＆M server 11, DB servers 21 etc.
The division of block, for example, can correspond to each function divides each functional module, can also be by two or more function collection
In Cheng Yi processing module.Above-mentioned integrated module can both be realized in the form of hardware, can also use software function
The form of module is realized.It should be noted that the division in the embodiment of the present invention to module is schematical, only one kind is patrolled
Function division is collected, there can be other dividing mode when actually realizing.
In the case where dividing each functional module using corresponding each function, Fig. 7 shows involved in above-described embodiment
And O＆M server 11 a kind of possible structural representation, O＆M server 11 includes：Acquiring unit 31, computing unit 32
And positioning unit 33.Acquiring unit 31 is used to support O＆M server 11 to perform the process 101 and 104 in Fig. 5, computing unit
32 are used to support O＆M server 11 to perform the process 102 and 105 in Fig. 5；Positioning unit 33 is used to support O＆M server 11
Perform the process 103 in Fig. 5.Wherein, all related contents for each step that above method embodiment is related to can be quoted
The function description of corresponding function module, will not be repeated here.
In the case of using integrated unit, Fig. 8 shows O＆M server 11 involved in above-described embodiment
A kind of possible structural representation.O＆M server 11 includes：Processing module 1302 and communication module 1303.Processing module 1302
Management is controlled for the action to O＆M server 11, for example, processing module 1302 is used to support O＆M server 11 to hold
Process 101-105 in row Fig. 5, and/or other processes for techniques described herein.Communication module 1303 is used to support
Base station and the communication of other network entities, such as the communication between the functional module with being shown in Fig. 1-Fig. 3 or network entity.Fortune
Dimension server 11 can also include memory module 1301, for storing the program code and data of O＆M server 11.
Wherein, processing module 1302 can be processor or controller, such as can be central processing unit (Central
Processing Unit, CPU), general processor, digital signal processor (Digital Signal Processor, DSP),
Application specific integrated circuit (Application-Specific Integrated Circuit, ASIC), field programmable gate array
It is (Field Programmable Gate Array, FPGA) or other PLDs, transistor logic, hard
Part part or its any combination.What it can realize or perform with reference to described by the disclosure of invention various exemplary patrols
Collect square frame, module and circuit.The processor can also be the combination for realizing computing function, such as include one or more micro- places
Manage device combination, combination of DSP and microprocessor etc..Communication module 1303 can be transceiver, transmission circuit or communication interface
Deng.Memory module 1301 can be memory.
When processing module 1302 is processor, communication module 1303 is communication interface, when memory module 1301 is memory,
O＆M server 11 involved by the embodiment of the present invention can be the computer equipment 500 shown in Fig. 4, and the embodiment of the present invention is to this
Do not repeat.
Those skilled in the art are it will be appreciated that in said one or multiple examples, work(described in the invention
It is able to can be realized with hardware, software, firmware or their any combination.When implemented in software, can be by these functions
It is stored in computer-readable medium or is transmitted as one or more instructions on computer-readable medium or code.
Computer-readable medium includes computer-readable storage medium and communication media, and wherein communication media includes being easy to from a place to another
Any medium of one place transmission computer program.It is any that storage medium can be that universal or special computer can access
Usable medium.
Above-described embodiment, the purpose of the present invention, technical scheme and beneficial effect are carried out further
Describe in detail, should be understood that the embodiment that the foregoing is only the present invention, be not intended to limit the present invention
Protection domain, all any modification, equivalent substitution and improvements on the basis of technical scheme, done etc., all should
It is included within protection scope of the present invention.
Claims (15)
- A kind of 1. localization method of SQL SQL scripts, it is characterised in that including：Obtain operational factor, the operational factor caused by each SQL scripts in the N number of SQL scripts run in set period For indicating consumed resource when running the SQL scripts, N ＞ 1；According to the operational factor of each SQL scripts, each SQL scripts are calculated in the set period in N number of SQL scripts In resource consumption accounting；Resource consumption accounting is more than at least one SQL scripts of default threshold value, is defined as target SQL scripts.
- 2. according to the method for claim 1, it is characterised in that the set period includes the continuous M cycle of operation, M ＞ 1；Wherein, according to the operational factor of each SQL scripts, each SQL scripts are calculated in the set period described N number of Resource consumption accounting in SQL scripts, including：For each cycle of operation, according to the operational factor of each SQL scripts in the cycle of operation, calculate within the cycle of operation Cycle consumption accounting of each SQL scripts in N number of SQL scripts；For each SQL scripts, consumed to the average value of accounting M cycle of the SQL scripts within the M cycle of operation, Resource consumption accounting as the SQL scripts.
- 3. according to the method for claim 1, it is characterised in that the set period includes the continuous M cycle of operation, M ＞ 1；Wherein, each operational factor caused by SQL scripts in the N number of SQL scripts run in set period is obtained, including：For each SQL scripts, the SQL scripts caused M groups operational factor within the M cycle of operation is obtained respectively；Determine the outlier in the M groups operational factor；The outlier is removed from the M groups operational factor, obtains transporting caused by each SQL scripts in the set period Row parameter.
- 4. according to the method any one of claim 1-3, it is characterised in that each in the set period calculating Before resource consumption accounting of the SQL scripts in N number of SQL scripts, in addition to：Obtain the resources occupation rate for the database server that each SQL scripts are performed in the set period；Wherein, according to the operational factor of each SQL scripts, each SQL scripts are calculated in the set period described N number of Resource consumption accounting in SQL scripts, including：For each SQL scripts, by the ratio of the operational factor of the SQL scripts and the operational factor sum of N number of SQL scripts As relative resource consumption accounting；For each SQL scripts, by the relative resource consumption accounting of the SQL scripts and the product of resources occupation rate, as the SQL The resource consumption accounting of script.
- 5. according to the method any one of claim 1-4, it is characterised in that default resource consumption accounting is more than At least one SQL scripts of threshold value, are defined as after target SQL scripts, in addition to：Obtain the history resource consumption accounting of the target SQL scripts；According to the history resource consumption accounting of the target SQL scripts, prediction runs the database service of the target SQL scripts The resource consumption trend of device.
- 6. according to the method any one of claim 1-5, it is characterised in that the set period includes continuous M The cycle of operation, M ＞ 1；Wherein, each operational factor caused by SQL scripts in the N number of SQL scripts run in set period is obtained, including：Produced when obtaining the operation of database server each SQL scripts that each cycle of operation reports in the M cycle of operation Raw operational factor；For each SQL scripts, according to the operational factor of the SQL scripts in each cycle of operation, calculating should in set period Operational factor caused by SQL scripts.
- 7. according to the method any one of claim 1-6, it is characterised in that the operational factor includes：Perform each CPU total duration is taken during SQL scripts；And/or perform the number that logic caused by each SQL scripts is read.
- A kind of 8. positioner of SQL SQL scripts, it is characterised in that including：Acquiring unit, for obtaining operational factor caused by each SQL scripts in the N number of SQL scripts run in set period, The consumed resource when operational factor is used to indicate to run the SQL scripts, N ＞ 1；Computing unit, for the operational factor according to each SQL scripts, calculate each SQL scripts in the set period and exist Resource consumption accounting in N number of SQL scripts；Positioning unit, for resource consumption accounting to be more than at least one SQL scripts of default threshold value, it is defined as target SQL Script.
- 9. device according to claim 8, it is characterised in that the set period includes the continuous M cycle of operation, M ＞ 1；The computing unit, is specifically used for：For each cycle of operation, according to the operation of each SQL scripts in the cycle of operation Parameter, calculate cycle consumption accounting of each SQL scripts in N number of SQL scripts within the cycle of operation；For each SQL scripts, consumed to the average value of accounting, as the SQL scripts M cycle of the SQL scripts within the M cycle of operation Resource consumption accounting.
- 10. device according to claim 8, it is characterised in that the set period includes the continuous M cycle of operation, M ＞ 1；The acquiring unit, is specifically used for：For each SQL scripts, the SQL scripts are obtained respectively in the M cycle of operation M group operational factors caused by interior；Determine the outlier in the M groups operational factor；The outlier is run from the M groups and joined Removed in number, obtain each operational factor caused by SQL scripts in the set period.
- 11. according to the device any one of claim 8-10, it is characterised in thatThe acquiring unit, it is additionally operable to obtain the money for the database server that each SQL scripts are performed in the set period Source occupancy；The computing unit, is specifically used for：For each SQL scripts, by the operational factor of the SQL scripts and N number of SQL pin The ratio of this operational factor sum is as relative resource consumption accounting；For each SQL scripts, by the relative of the SQL scripts The product of resource consumption accounting and resources occupation rate, the resource consumption accounting as the SQL scripts.
- 12. according to the device any one of claim 8-11, it is characterised in thatThe acquiring unit, it is additionally operable to obtain the history resource consumption accounting of the target SQL scripts；The computing unit, is additionally operable to the history resource consumption accounting according to the target SQL scripts, and prediction runs the target The resource consumption trend of the database server of SQL scripts.
- 13. according to the device any one of claim 8-12, it is characterised in that the set period includes continuous M The individual cycle of operation, M ＞ 1；The acquiring unit, reported specifically for obtaining database server each cycle of operation in the M cycle of operation Each SQL scripts operation caused by operational factor；The computing unit, is specifically used for：For each SQL scripts, joined according to the operation of the SQL scripts in each cycle of operation Number, calculates the operational factor caused by the SQL scripts in set period.
- A kind of 14. O＆M server, it is characterised in that including：Processor, memory, bus and communication interface；The memory is used to store computer executed instructions, and the processor is connected with the memory by the bus, When the O＆M server is run, the computer executed instructions of memory storage described in the computing device, so that The O＆M server performs the positioning side of the SQL SQL scripts as described in any one in claim 1-7 Method.
- 15. a kind of alignment system of SQL SQL scripts, it is characterised in that including as claimed in claim 14 O＆M server, and the database being connected with the O＆M server,Wherein, the alignment system of the SQL scripts, for monitoring simultaneously each database server operation in maintenance service system SQL scripts.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201610816425.9A CN107818093A (en) | 2016-09-12 | 2016-09-12 | A kind of localization method, the apparatus and system of SQL scripts |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201610816425.9A CN107818093A (en) | 2016-09-12 | 2016-09-12 | A kind of localization method, the apparatus and system of SQL scripts |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN107818093A true CN107818093A (en) | 2018-03-20 |
Family
ID=61601188
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201610816425.9A Pending CN107818093A (en) | 2016-09-12 | 2016-09-12 | A kind of localization method, the apparatus and system of SQL scripts |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN107818093A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113515521A (en) * | 2021-04-26 | 2021-10-19 | 中国工商银行股份有限公司 | Index optimization method and device based on SQL (structured query language) inspection operation |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20120266026A1 (en) * | 2011-04-18 | 2012-10-18 | Ramya Malanai Chikkalingaiah | Detecting and diagnosing misbehaving applications in virtualized computing systems |
| CN103399851A (en) * | 2013-06-25 | 2013-11-20 | 携程计算机技术（上海）有限公司 | Method and system for analyzing and predicting performance of structured query language (SQL) scrip |
| CN104111876A (en) * | 2014-07-10 | 2014-10-22 | 深圳供电局有限公司 | Dynamic resource management device and method based on Oracle resource plan |
| CN104778185A (en) * | 2014-01-15 | 2015-07-15 | 中国移动通信集团北京有限公司 | Determination method for abnormal SQL (structured query language) statement and server |
-
2016
- 2016-09-12 CN CN201610816425.9A patent/CN107818093A/en active Pending
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20120266026A1 (en) * | 2011-04-18 | 2012-10-18 | Ramya Malanai Chikkalingaiah | Detecting and diagnosing misbehaving applications in virtualized computing systems |
| CN103399851A (en) * | 2013-06-25 | 2013-11-20 | 携程计算机技术（上海）有限公司 | Method and system for analyzing and predicting performance of structured query language (SQL) scrip |
| CN104778185A (en) * | 2014-01-15 | 2015-07-15 | 中国移动通信集团北京有限公司 | Determination method for abnormal SQL (structured query language) statement and server |
| CN104111876A (en) * | 2014-07-10 | 2014-10-22 | 深圳供电局有限公司 | Dynamic resource management device and method based on Oracle resource plan |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113515521A (en) * | 2021-04-26 | 2021-10-19 | 中国工商银行股份有限公司 | Index optimization method and device based on SQL (structured query language) inspection operation |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| Halfin et al. | Heavy-traffic limits for queues with many exponential servers | |
| Lin et al. | A cloud server energy consumption measurement system for heterogeneous cloud environments | |
| US8538484B2 (en) | Providing a user with feedback regarding power consumption in battery-operated electronic devices | |
| US20180060395A1 (en) | Selecting interruptible resources for query execution | |
| Yi et al. | Efficient compute-intensive job allocation in data centers via deep reinforcement learning | |
| US20120053925A1 (en) | Method and System for Computer Power and Resource Consumption Modeling | |
| CN109144658B (en) | Load balancing method and device for limited resources and electronic equipment | |
| US10423201B2 (en) | Method and apparatus for demand estimation for energy management of client systems | |
| CN113407649A (en) | Data warehouse modeling method and device, electronic equipment and storage medium | |
| Ma et al. | Review of power spatio-temporal big data technologies for mobile computing in smart grid | |
| CN115344207A (en) | Data processing method and device, electronic equipment and storage medium | |
| Shirzad et al. | Job failure prediction in Hadoop based on log file analysis | |
| Jiang et al. | Effective data management strategy and RDD weight cache replacement strategy in Spark | |
| Khan et al. | Advanced data analytics modeling for evidence-based data center energy management | |
| Sathyamoorthy et al. | Profiling energy efficiency and data communications for mobile internet of things | |
| CN114154962A (en) | Batch processing monitoring method, device and equipment | |
| CN107818093A (en) | A kind of localization method, the apparatus and system of SQL scripts | |
| Chen | Visual Design of Landscape Architecture Based on High‐Density Three‐Dimensional Internet of Things | |
| Sîrbu et al. | A data‐driven approach to modeling power consumption for a hybrid supercomputer | |
| CN115471215B (en) | Business process processing method and device | |
| CN114817408B (en) | Scheduling resource identification method and device, electronic equipment and storage medium | |
| Ju et al. | Innovation trend of edge computing technology based on patent perspective | |
| Forshaw | Operating policies for energy efficient large scale computing | |
| Guo et al. | Green data analytics of supercomputing from massive sensor networks: Does workload distribution matter? | |
| CN111143328A (en) | Agile business intelligent data construction method, system, equipment and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20180320 |
|
| RJ01 | Rejection of invention patent application after publication |