CN109828983A - PG data base processing method, device, electronic equipment and storage medium - Google Patents
PG data base processing method, device, electronic equipment and storage medium Download PDFInfo
- Publication number
- CN109828983A CN109828983A CN201811537728.2A CN201811537728A CN109828983A CN 109828983 A CN109828983 A CN 109828983A CN 201811537728 A CN201811537728 A CN 201811537728A CN 109828983 A CN109828983 A CN 109828983A
- Authority
- CN
- China
- Prior art keywords
- database
- analysis
- sql statement
- sentence
- processing method
- 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.)
- Granted
Links
- 238000003672 processing method Methods 0.000 title claims abstract description 24
- 238000004458 analytical method Methods 0.000 claims abstract description 121
- 238000012545 processing Methods 0.000 claims abstract description 23
- 238000012546 transfer Methods 0.000 claims abstract description 19
- 238000000034 method Methods 0.000 claims description 26
- 230000002159 abnormal effect Effects 0.000 claims description 19
- 230000005540 biological transmission Effects 0.000 claims description 11
- 238000005457 optimization Methods 0.000 abstract description 3
- 230000006870 function Effects 0.000 description 16
- 238000004590 computer program Methods 0.000 description 13
- 230000008569 process Effects 0.000 description 9
- 230000008859 change Effects 0.000 description 7
- 238000010586 diagram Methods 0.000 description 5
- 238000012360 testing method Methods 0.000 description 4
- 238000001514 detection method Methods 0.000 description 3
- 238000009434 installation Methods 0.000 description 3
- 241001269238 Data Species 0.000 description 2
- 230000010365 information processing Effects 0.000 description 2
- 230000010354 integration Effects 0.000 description 2
- 230000003993 interaction Effects 0.000 description 2
- 230000002045 lasting effect Effects 0.000 description 2
- 238000012544 monitoring process Methods 0.000 description 2
- 230000008439 repair process Effects 0.000 description 2
- 238000012163 sequencing technique Methods 0.000 description 2
- 230000001960 triggered effect Effects 0.000 description 2
- 238000004364 calculation method Methods 0.000 description 1
- 235000013399 edible fruits Nutrition 0.000 description 1
- 230000002452 interceptive effect Effects 0.000 description 1
- 238000011835 investigation Methods 0.000 description 1
- 238000005192 partition Methods 0.000 description 1
- 238000000926 separation method Methods 0.000 description 1
- XLYOFNOQVPJJNP-UHFFFAOYSA-N water Substances O XLYOFNOQVPJJNP-UHFFFAOYSA-N 0.000 description 1
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The present invention provides a kind of PG data base processing method, device, electronic equipment and storage medium.The PG data base processing method includes: after receiving the instruction of PG database analysis, and Xiang Suoshu PG database sends connection request；After receiving the permission link order that the PG database returns, the connection with the PG database is established；It is instructed according to the PG database analysis, transfers basic data from the PG database；Logical operation is carried out to the basic data, obtains TOP SQL statement；Show the TOP SQL statement.The present invention realizes intelligent search in database O&M, also provides analysis foundation for developer, improve O&M efficiency, to carry out operation process optimization by the processing mode of automation.
Description
Technical field
The present invention relates to O&M technical field more particularly to a kind of PG data base processing method, device, electronic equipment and deposit
Storage media.
Background technique
Relational DBMS, such as MySQL save the data in different tables, rather than by all numbers
According to putting together, therefore data processing speed and flexibility can be improved.Relational DBMS, using the most frequently used
Database management language -- structured query language (Structured Query Language, abbreviation SQL) carry out database
Management.Relational DBMS is used widely in all trades and professions at present.Database function detection can help to use
Family understands the performance and investigation exception of database.
But in the prior art scheme, for the analysis of PG database topsql sentence, it usually needs manufacturer provides
AWR report, form are fixed, and flexibility, and the limitation of the experience level due to technical staff are lacked, the gained from same report
To conclusion be also different, conclusion can not be carried out effectively unified, brought inconvenience to relevant staff.
Summary of the invention
In view of the foregoing, it is necessary to a kind of PG data base processing method, device, electronic equipment and storage medium are provided,
By the processing mode of automation, intelligent search is realized in database O&M, also analysis foundation is provided for developer, mentions
High O&M efficiency, to carry out operation process optimization.
A kind of PG data base processing method, which comprises
After receiving the instruction of PG database analysis, Xiang Suoshu PG database sends connection request；
After receiving the permission link order that the PG database returns, the connection with the PG database is established；
It is instructed according to the PG database analysis, transfers basic data from the PG database；
Logical operation is carried out to the basic data, obtains TOP SQL statement；
Show the TOP SQL statement.
Preferred embodiment according to the present invention, it is described to include: to PG database transmission connection request
User name and password are sent to the PG database, for the PG database authentication.
Preferred embodiment according to the present invention, the foundation and the connection of the PG database include:
Obtain IP address and the port of the PG database；
The IP address and port are sent to configuration fastening means；
Based on the configuration fastening means, the connection with the PG database is established.
Preferred embodiment according to the present invention, the basic data include the combination of following one or more:
Central processor CPU consumption, execution efficiency execute the time, execute number.
Preferred embodiment according to the present invention, it is described that logical operation is carried out to the basic data, obtain TOP SQL statement packet
It includes:
The basic data is ranked up according to configuration rule；
The sentence of presetting digit capacity before obtaining, as the TOP SQL statement.
Preferred embodiment according to the present invention, when showing the TOP SQL statement, the method also includes:
When receiving the instruction for showing analysis result, the Sentence analysis list of configuration is obtained；
The TOP SQL statement is matched with the sentence in the Sentence analysis list, obtains matched sentence；
From the Sentence analysis list, analysis result corresponding with the matched sentence is obtained；
Show the analysis result.
Preferred embodiment according to the present invention, after showing the analysis result, the method also includes:
When the analysis is abnormal as the result is shown, the execution period corresponding to abnormal TOP SQL statement is obtained；
Obtain the execution journal in the execution period；
The execution journal is shown, so that user checks.
A kind of PG database processing device, described device include:
Transmission unit, for after receiving the instruction of PG database analysis, Xiang Suoshu PG database to send connection request；
Unit is established, for establishing and the PG number after receiving the permission link order that the PG database returns
According to the connection in library；
Unit is transferred, for instructing according to the PG database analysis, transfers basic data from the PG database；
Arithmetic element obtains TOP SQL statement for carrying out logical operation to the basic data；
Display unit, for showing the TOP SQL statement.
Preferred embodiment according to the present invention, the transmission unit are specifically used for:
User name and password are sent to the PG database, for the PG database authentication.
Preferred embodiment according to the present invention, the unit of establishing are specifically used for:
Obtain IP address and the port of the PG database；
The IP address and port are sent to configuration fastening means；
Based on the configuration fastening means, the connection with the PG database is established.
Preferred embodiment according to the present invention, the basic data include the combination of following one or more:
Central processor CPU consumption, execution efficiency execute the time, execute number.
Preferred embodiment according to the present invention, the arithmetic element are specifically used for:
The basic data is ranked up according to configuration rule；
The sentence of presetting digit capacity before obtaining, as the TOP SQL statement.
Preferred embodiment according to the present invention, described device further include:
Acquiring unit, for when receiving the instruction for showing analysis result, obtaining when showing the TOP SQL statement
Take the Sentence analysis list of configuration；
Matching unit is obtained for matching the TOP SQL statement with the sentence in the Sentence analysis list
Matched sentence；
The acquiring unit is also used to from the Sentence analysis list, is obtained and corresponding point of the matched sentence
Analyse result；
The display unit is also used to show the analysis result.
Preferred embodiment according to the present invention, the acquiring unit are also used to after showing the analysis result, when described point
When analysis is abnormal as the result is shown, the execution period corresponding to abnormal TOP SQL statement is obtained；
The acquiring unit is also used to obtain the execution journal in the execution period；
The display unit is also used to show the execution journal, so that user checks.
A kind of electronic equipment, the electronic equipment include:
Memory stores at least one instruction；And
Processor executes the instruction stored in the memory to realize the PG data base processing method.
A kind of computer readable storage medium is stored at least one instruction, institute in the computer readable storage medium
At least one instruction is stated to be executed by the processor in electronic equipment to realize the PG data base processing method.
As can be seen from the above technical solutions, the present invention can be after receiving the instruction of PG database analysis, Xiang Suoshu PG
Database sends connection request, and after receiving the permission link order that the PG database returns, resettles and the PG
The connection of database, to realize that identity identifies, it is ensured that the safety of connection is instructed according to the PG database analysis, is directed to
Property transfer basic data from the PG database, and logical operation is carried out to the basic data, obtains TOP SQL language
Sentence is used, the processing mode of automation with showing the TOP SQL statement for analysis, realizes that intelligence is searched in database O&M
Rope also provides analysis foundation for developer, improves O&M efficiency, to carry out operation process optimization.
Detailed description of the invention
Fig. 1 is the flow chart of the preferred embodiment of PG data base processing method of the present invention.
Fig. 2 is the functional block diagram of the preferred embodiment of PG database processing device of the present invention.
Fig. 3 is the structural schematic diagram of the electronic equipment for the preferred embodiment that the present invention realizes PG data base processing method.
Main element symbol description
| Electronic equipment | 1 |
| Memory | 12 |
| Processor | 13 |
| PG database processing device | 11 |
| Transmission unit | 110 |
| Establish unit | 111 |
| Transfer unit | 112 |
| Arithmetic element | 113 |
| Display unit | 114 |
| Acquiring unit | 115 |
| Matching unit | 116 |
Specific embodiment
To make the objectives, technical solutions, and advantages of the present invention clearer, right in the following with reference to the drawings and specific embodiments
The present invention is described in detail.
As shown in Figure 1, being the flow chart of the preferred embodiment of PG data base processing method of the present invention.According to different need
It asks, the sequence of step can change in the flow chart, and certain steps can be omitted.
The PG data base processing method is applied in one or more electronic equipment, and the electronic equipment is a kind of energy
It is enough according to the instruction for being previously set or store, the equipment of automatic progress numerical value calculating and/or information processing, hardware includes but not
It is limited to microprocessor, specific integrated circuit (Application Specific Integrated Circuit, ASIC), can compiles
Journey gate array (Field-Programmable Gate Array, FPGA), digital processing unit (Digital Signal
Processor, DSP), embedded device etc..
The electronic equipment can be any electronic product that human-computer interaction can be carried out with user, for example, personal meter
Calculation machine, tablet computer, smart phone, personal digital assistant (PersonalDigital Assistant, PDA), game machine, friendship
Mutual formula Web TV (Internet Protocol Television, IPTV), intellectual wearable device etc..
The electronic equipment can also include the network equipment and/or user equipment.Wherein, the network equipment includes, but
It is not limited to single network server, the server group of multiple network servers composition or based on cloud computing (Cloud
Computing the cloud being made of a large amount of hosts or network server).
Network locating for the electronic equipment include but is not limited to internet, wide area network, Metropolitan Area Network (MAN), local area network, it is virtual specially
With network (Virtual Private Network, VPN) etc..
S10, after receiving the instruction of PG database analysis, the electronic equipment sends connection to the PG database and asks
It asks.
In order to solve manual queries in the prior art, or the inconvenience to manufacturer's request data, the technical program provide
A kind of scheme for transferring TOP SQL statement from PG database automatically, while bringing convenience to user, also increases inquiry
Flexibility.
In at least one embodiment of the present invention, the PG database (PostgreSQL) is a kind of relevant database
Management system provides multi version parallel control, supports nearly all SQL (Structured Query Language, structure
Change query language) component (including subquery, affairs and user defined type and function), and boundless range can be obtained
(exploitation) language binding (including C, C++, Java, perl, tcl and python).
Specifically, the PG database is equipped with log sheet, records the accessing operation of all pairs of databases.For user terminal
Each accessing operation, database are corresponding with executive plan.The executive plan of database is that database server is executing inquiry language
When sentence, according to carrying into execution a plan for the determination such as search efficiency, time, it is made of several basic operations, such as: whole of traversal
Table utilizes index, the nested circulation of execution or Hash link etc..It executes i.e. exportable with the query statement pair after the executive plan
The query result answered.The log sheet will record each query statement and its corresponding executive plan, each executive plan it is every
One basic operation is all documented in log sheet, to can get the basic data of database by reading log sheet.
In at least one embodiment of the present invention, the PG database analysis instruction can be triggered by user, (such as:
One PG database analysis button is provided, starts testing process by triggering the PG database analysis button, wherein the PG
Database analysis button can be a preconfigured virtual push button, be also possible to an entity button, and the present invention is unlimited
System), it can also be executed automatically according to scheduled frequency.
Specifically, for the frequency of analysis, can be arranged according to demand, such as: it is executed in daily fixed time, or every
It is inferior that half a day executes one.In order to enable timing detects automatic execution, can be held by continuous integrating tool, such as Jenkins platform
Row.Jenkins is an open source software project, is a kind of continuous integrating tool based on Java exploitation, continues weight for monitoring
Multiple work, it is desirable to provide an easy-to-use software platform of opening makes the continuous integrating of software become possible.By described lasting
Integration tool calls analysis script, and the detection script self-timing is made to execute analysis task.
The analysis script can be used the language that can connect the PG database of any suitable, and from the PG data
The script of basic data is read in library, such as: Jmeter script, Shell script, Python script etc..
Preferably, the electronic equipment includes: to PG database transmission connection request
The electronic equipment sends user name and password to the PG database, for the PG database authentication.
Further, user name and password described in the PG database authentication, and do not have in the user name and password
When passing through verifying, Xiang Suoshu electronic equipment sends refusal link order and either passes through verifying in the user name and password
When, Xiang Suoshu electronic equipment, which is sent, allows link order.
S11, after receiving the permission link order that the PG database returns, the electronic equipment is established and the PG
The connection of database.
In at least one embodiment of the present invention, the electronic equipment can pass through ODBC (Open Database
Connectivity, open CNC) or JDBC (connection of Java DataBase Connectivity, java database)
Or any other suitable database connection accesses.Wherein JDBC is the application programming interfaces (API) of an object-oriented,
It may have access to types of databases by it.ODBC is Microsoft's open services architecture (WOSA, Windows Open Services
Architecture a component part in) in relation to database, it establishes one group of specification, and provides one group to database
The standard API (application programming interface) of access.Database link address can be JDBC link address or ODBC link address.
Setting to the analysis script further includes that the driver of the databases such as JDBC or ODBC connection is replicated in the analysis foot
In this script bank (such as the library lib).So as to after starting the permission link order that the PG database returns, establish with it is described
The connection of PG database.
Specifically, the electronic equipment is established and the connection of the PG database includes:
The electronic equipment obtains IP address and the port of the PG database, and the IP address and port are sent to and matched
Fastening means is set, and is based on the configuration fastening means, establishes the connection with the PG database.
Further, the configuration fastening means includes, but are not limited to above-described ODBC or JDBC or any other
Suitable database fastening means etc..
S12, the electronic equipment are instructed according to the PG database analysis, transfer basic number from the PG database
According to.
It at least one embodiment of the present invention, include that the user is customized in the PG database analysis instruction
Analyst coverage, the electronic equipment can transfer the basic data in the analyst coverage, and to the basic data into
Row analysis.
Specifically, the basic data includes, but are not limited to the combination of following one or more:
CPU (Central Processing Unit, central processing unit) consumption, execution efficiency execute time, execution time
Number etc..
Further, the electronic equipment can read the basic data from the log sheet of the PG database, also
Institute can be read from tracking file (SQL_TRACE), AWR (Automatic Workload Repository) snapshot document
Basic data is stated, the present invention does not limit the mode for transferring the basic data.
Further, SQL_TRACE file is mainly the thing tracking background process and consumer process and being done, wherein can
Obtain executive plan and relevant statistics.
Further, AWR is substantially the built-in tool of the PG database, AWR acquisition with it is performance-relevant
Statistical data, and performance metric is exported from those statistical data, to track potential problem.
S13, the electronic equipment carry out logical operation to the basic data, obtain TOP SQL statement.
In at least one embodiment of the present invention, the logical operation includes carrying out logic row to the basic data
Sequence.
In at least one embodiment of the present invention, the TOP SQL statement refers to according to certain rule, by all SQL
After sentence is ranked up, multiple sentences of front are come.For example according to number sequence is executed, sequence is logically read, according to CUP
Using time-sequencing etc., the meaning of TOP is exactly preceding several after taking sequence.
Specifically, the electronic equipment carries out logical operation to the basic data, and obtaining TOP SQL statement includes:
The basic data is ranked up by the electronic equipment according to configuration rule, and obtains the language of preceding presetting digit capacity
Sentence, as the TOP SQL statement.
Specifically, the configuration rule can be configured by the electronic equipment, described for different basic datas
Configuration rule is also different.
Further, the basic data of SQL statement include SQL statement the execution time, execute number, CPU consumption and its
Corresponding executive plan etc..Such as: it is ranked up according to CPU consumption, consumption ranking can be obtained in several SQL statements in front
As the TOP SQL statement；It is ranked up according to the execution time of the SQL statement, it may be determined that execute time longer SQL language
Sentence is used as the TOP SQL statement.
S14, the electronic equipment show the TOP SQL statement.
In at least one embodiment of the present invention, the electronic equipment shows the TOP SQL language in the form of statements
Sentence, and multiple function buttons can be shown simultaneously on showing interface, such as: historical data shows that button, data comparison are shown
Button etc., to provide more services for the user, makes the friendly for showing interface more for user selection
By force.
Preferably, when showing the TOP SQL statement, the method also includes:
When receiving the instruction for showing analysis result, the electronic equipment obtains the Sentence analysis list of configuration, by institute
State TOP SQL statement to be matched with the sentence in the Sentence analysis list, obtain matched sentence, the electronic equipment from
In the Sentence analysis list, analyze as a result, and show the analysis result corresponding with the matched sentence is obtained.
Specifically, store all TOP SQL statements in the Sentence analysis list, and with all TOP SQL languages
The corresponding analysis result of sentence.The analysis result can be configured by related personnel according to O&M experience.For example, for CPU
Consumption variation change rate reaches preset threshold and reaches the TOP SQL statement of predetermined threshold between executing, and related personnel is determined as
Abnormal TOP SQL statement, needs to optimize, and above-mentioned conclusion is maintained into the Sentence analysis list.
The electronic equipment provides a unified analysis as a result, avoiding due to technical staff's experience water for related personnel
Flat limitation obtains different conclusions from same portion TOP SQL statement, brings inconvenience to related personnel.
Specifically, the analysis result can also be shown in the form of statements, be checked convenient for testing staff's analysis.Described point
Analysis result can be incorporated into a report with the TOP SQL statement, convenient for being sent to specified developer, be opened described in auxiliary
Hair personnel carry out rectification and repair code, to improve the executive plan of SQL query statement.The report may include, but be not limited to count
According to the cognizance code (such as database ID etc.) in library, query statement cognizance code (such as query statement ID etc.), the rule violated
Etc. necessary information.
In at least one embodiment of the present invention, the electronic equipment can with customization be shown in the analysis result
A part, rest part can first hide, also, by the expansion button on the displaying interface, can trigger further exhibition
The step of showing details.Where the developer can directly know problem by the analysis result, so as to fast
Speed understands the performance and abnormal conditions of the PG database, to assist the developer to be further analyzed processing.
Preferably, after showing the analysis result, the method also includes:
When the analysis is abnormal as the result is shown, the electronic equipment obtains to be held corresponding to abnormal TOP SQL statement
Row period, and obtain the execution journal executed in the period, the electronic equipment shows the execution journal, for
Family is checked.
Specifically, the execution period that the electronic equipment Auto-matching is abnormal, and when the execution that exception will occur
Between all execution journal in section show on the displaying interface, have related personnel according to the execution journal
Targetedly analysis is handled.
Certainly, in other embodiments, the electronic equipment also can receive the period of user's input, and transfer
All execution journal in the period are convenient for the customized acquisition data of the user to meet the query demand of user, use
It experiences more preferably at family.
In conclusion the present invention can be after receiving the instruction of PG database analysis, Xiang Suoshu PG database sends connection and asks
It asks；After receiving the permission link order that the PG database returns, the connection with the PG database is established；According to described
The instruction of PG database analysis, transfers basic data from the PG database；Logical operation is carried out to the basic data, is obtained
TOP SQL statement；Show the TOP SQL statement.Therefore, the present invention can be transported by the processing mode of automation in database
Intelligent search is realized when dimension, also provides analysis foundation for developer, improves O&M efficiency, it is excellent to carry out operation process
Change.
As shown in Fig. 2, being the functional block diagram of the preferred embodiment of PG database processing device of the present invention.The PG data
Library processing unit 11 include transmission unit 110, establish unit 111, transfer unit 112, arithmetic element 113, display unit 114,
Acquiring unit 115 and matching unit 116.So-called module/the unit of the present invention refer to one kind can performed by processor 13, and
And the series of computation machine program segment of fixed function can be completed, storage is in memory 12.In the present embodiment, about each
Module/unit function will be described in detail in subsequent embodiment.
After receiving the instruction of PG database analysis, transmission unit 110 sends connection request to the PG database.
In order to solve manual queries in the prior art, or the inconvenience to manufacturer's request data, the technical program provide
A kind of scheme for transferring TOP SQL statement from PG database automatically, while bringing convenience to user, also increases inquiry
Flexibility.
In at least one embodiment of the present invention, the PG database (PostgreSQL) is a kind of relevant database
Management system provides multi version parallel control, supports nearly all SQL (Structured Query Language, structure
Change query language) component (including subquery, affairs and user defined type and function), and boundless range can be obtained
(exploitation) language binding (including C, C++, Java, perl, tcl and python).
Specifically, the PG database is equipped with log sheet, records the accessing operation of all pairs of databases.For user terminal
Each accessing operation, database are corresponding with executive plan.The executive plan of database is that database server is executing inquiry language
When sentence, according to carrying into execution a plan for the determination such as search efficiency, time, it is made of several basic operations, such as: whole of traversal
Table utilizes index, the nested circulation of execution or Hash link etc..It executes i.e. exportable with the query statement pair after the executive plan
The query result answered.The log sheet will record each query statement and its corresponding executive plan, each executive plan it is every
One basic operation is all documented in log sheet, to can get the basic data of database by reading log sheet.
In at least one embodiment of the present invention, the PG database analysis instruction can be triggered by user, (such as:
One PG database analysis button is provided, starts testing process by triggering the PG database analysis button, wherein the PG
Database analysis button can be a preconfigured virtual push button, be also possible to an entity button, and the present invention is unlimited
System), it can also be executed automatically according to scheduled frequency.
Specifically, for the frequency of analysis, can be arranged according to demand, such as: it is executed in daily fixed time, or every
It is inferior that half a day executes one.In order to enable timing detects automatic execution, can be held by continuous integrating tool, such as Jenkins platform
Row.Jenkins is an open source software project, is a kind of continuous integrating tool based on Java exploitation, continues weight for monitoring
Multiple work, it is desirable to provide an easy-to-use software platform of opening makes the continuous integrating of software become possible.By described lasting
Integration tool calls analysis script, and the detection script self-timing is made to execute analysis task.
The analysis script can be used the language that can connect the PG database of any suitable, and from the PG data
The script of basic data is read in library, such as: Jmeter script, Shell script, Python script etc..
Preferably, the transmission unit 110 includes: to PG database transmission connection request
The transmission unit 110 sends user name and password to the PG database, for the PG database authentication.
Further, user name and password described in the PG database authentication, and do not have in the user name and password
When passing through verifying, Xiang Suoshu electronic equipment sends refusal link order and either passes through verifying in the user name and password
When, Xiang Suoshu electronic equipment, which is sent, allows link order.
After receiving the permission link order that the PG database returns, establishes unit 111 and establish and the PG data
The connection in library.
In at least one embodiment of the present invention, the unit 111 of establishing can be by ODBC (Open Database
Connectivity, open CNC) or JDBC (connection of Java DataBase Connectivity, java database)
Or any other suitable database connection accesses.Wherein JDBC is the application programming interfaces (API) of an object-oriented,
It may have access to types of databases by it.ODBC is Microsoft's open services architecture (WOSA, Windows Open Services
Architecture a component part in) in relation to database, it establishes one group of specification, and provides one group to database
The standard API (application programming interface) of access.Database link address can be JDBC link address or ODBC link address.
Setting to the analysis script further includes that the driver of the databases such as JDBC or ODBC connection is replicated in the analysis foot
In this script bank (such as the library lib).So as to after starting the permission link order that the PG database returns, establish with it is described
The connection of PG database.
Specifically, described to establish that unit 111 is established and the connection of the PG database includes:
IP address and the port established unit 111 and obtain the PG database sends the IP address and port
To configuration fastening means, and it is based on the configuration fastening means, established and the connection of the PG database.
Further, the configuration fastening means includes, but are not limited to above-described ODBC or JDBC or any other
Suitable database fastening means etc..
It transfers unit 112 to be instructed according to the PG database analysis, transfers basic data from the PG database.
It at least one embodiment of the present invention, include that the user is customized in the PG database analysis instruction
Analyst coverage, the unit 112 of transferring can transfer the basic data in the analyst coverage, and to the basic data
It is analyzed.
Specifically, the basic data includes, but are not limited to the combination of following one or more:
CPU (Central Processing Unit, central processing unit) consumption, execution efficiency execute time, execution time
Number etc..
Further, the unit 112 of transferring can read the basic data from the log sheet of the PG database,
It can also be read from tracking file (SQL_TRACE), AWR (Automatic Workload Repository) snapshot document
The basic data, the present invention do not limit the mode for transferring the basic data.
Further, SQL_TRACE file is mainly the thing tracking background process and consumer process and being done, wherein can
Obtain executive plan and relevant statistics.
Further, AWR is substantially the built-in tool of the PG database, AWR acquisition with it is performance-relevant
Statistical data, and performance metric is exported from those statistical data, to track potential problem.
Arithmetic element 113 carries out logical operation to the basic data, obtains TOP SQL statement.
In at least one embodiment of the present invention, the logical operation includes carrying out logic row to the basic data
Sequence.
In at least one embodiment of the present invention, the TOP SQL statement refers to according to certain rule, by all SQL
After sentence is ranked up, multiple sentences of front are come.For example according to number sequence is executed, sequence is logically read, according to CUP
Using time-sequencing etc., the meaning of TOP is exactly preceding several after taking sequence.
Specifically, the arithmetic element 113 carries out logical operation to the basic data, and obtaining TOP SQL statement includes:
The basic data is ranked up by the arithmetic element 113 according to configuration rule, and obtains preceding presetting digit capacity
Sentence, as the TOP SQL statement.
Specifically, the configuration rule can be configured by the arithmetic element 113, for different basic datas,
The configuration rule is also different.
Further, the basic data of SQL statement include SQL statement the execution time, execute number, CPU consumption and its
Corresponding executive plan etc..Such as: it is ranked up according to CPU consumption, consumption ranking can be obtained in several SQL statements in front
As the TOP SQL statement；It is ranked up according to the execution time of the SQL statement, it may be determined that execute time longer SQL language
Sentence is used as the TOP SQL statement.
Display unit 114 shows the TOP SQL statement.
In at least one embodiment of the present invention, the display unit 114 shows the TOP SQL in the form of statements
Sentence, and multiple function buttons can be shown simultaneously on showing interface, such as: historical data shows button, data comparison exhibition
Show button etc., for user selection, to provide more services for the user, makes the friendly for showing interface
It is stronger.
Preferably, when showing the TOP SQL statement, the method also includes:
When receiving the instruction for showing analysis result, acquiring unit 115 obtains the Sentence analysis list of configuration, and matching is single
First 116 TOP SQL statements are matched with the sentence in the Sentence analysis list, obtain matched sentence, described to obtain
It takes unit 115 from the Sentence analysis list, obtains analyze as a result, the display unit corresponding with the matched sentence
114 show the analysis result.
Specifically, store all TOP SQL statements in the Sentence analysis list, and with all TOP SQL languages
The corresponding analysis result of sentence.The analysis result can be configured by related personnel according to O&M experience.For example, for CPU
Consumption variation change rate reaches preset threshold and reaches the TOP SQL statement of predetermined threshold between executing, and related personnel is determined as
Abnormal TOP SQL statement, needs to optimize, and above-mentioned conclusion is maintained into the Sentence analysis list.
The display unit 114 provides a unified analysis as a result, avoiding passing through due to technical staff for related personnel
Horizontal limitation is tested, different conclusions is obtained from same portion TOP SQL statement, brings inconvenience to related personnel.
Specifically, the analysis result can also be shown in the form of statements, be checked convenient for testing staff's analysis.Described point
Analysis result can be incorporated into a report with the TOP SQL statement, convenient for being sent to specified developer, be opened described in auxiliary
Hair personnel carry out rectification and repair code, to improve the executive plan of SQL query statement.The report may include, but be not limited to count
According to the cognizance code (such as database ID etc.) in library, query statement cognizance code (such as query statement ID etc.), the rule violated
Etc. necessary information.
In at least one embodiment of the present invention, the display unit 114 can with customization show the analysis knot
A part in fruit, rest part can first be hidden, also, by the expansion button on the displaying interface, can trigger into one
Step shows the step of details.Where the developer can directly know problem by the analysis result, so as to
The performance and abnormal conditions of the PG database are quickly understood, enough to assist the developer to be further analyzed place
Reason.
Preferably, after showing the analysis result, the method also includes:
When the analysis is abnormal as the result is shown, the acquiring unit 115 is obtained corresponding to abnormal TOP SQL statement
The period is executed, and obtains the execution journal executed in the period, the display unit 114 shows the execution journal,
So that user checks.
Specifically, the execution period that 115 Auto-matching of acquiring unit is abnormal, the display unit 114 will
All execution journal executed in the period being abnormal are shown on the displaying interface, allow related personnel according to institute
It states execution journal and carries out targeted analysis processing.
Certainly, in other embodiments, the acquiring unit 115 also can receive the period of user's input, and
All execution journal in the period are transferred, to meet the query demand of user, are convenient for the customized acquisition number of the user
According to user experience is more preferably.
In conclusion the present invention can be after receiving the instruction of PG database analysis, Xiang Suoshu PG database sends connection and asks
It asks；After receiving the permission link order that the PG database returns, the connection with the PG database is established；According to described
The instruction of PG database analysis, transfers basic data from the PG database；Logical operation is carried out to the basic data, is obtained
TOP SQL statement；Show the TOP SQL statement.Therefore, the present invention can be transported by the processing mode of automation in database
Intelligent search is realized when dimension, also provides analysis foundation for developer, improves O&M efficiency, it is excellent to carry out operation process
Change.
As shown in figure 3, being that the present invention realizes that the structure of electronic equipment of the preferred embodiment of PG data base processing method is shown
It is intended to.
The electronic equipment 1 be it is a kind of can according to the instruction for being previously set or store, automatic progress numerical value calculating and/or
The equipment of information processing, hardware include but is not limited to microprocessor, specific integrated circuit (Application Specific
Integrated Circuit, ASIC), programmable gate array (Field-Programmable Gate Array, FPGA), number
Word processing device (Digital Signal Processor, DSP), embedded device etc..
The electronic equipment 1, which can also be but not limited to any one, to pass through keyboard, mouse, remote controler, touching with user
The modes such as template or voice-operated device carry out the electronic product of human-computer interaction, for example, personal computer, tablet computer, smart phone,
Personal digital assistant (PersonalDigital Assistant, PDA), game machine, Interactive Internet TV (Internet
Protocol Television, IPTV), intellectual wearable device etc..
The electronic equipment 1 can also be that the calculating such as desktop PC, notebook, palm PC and cloud server are set
It is standby.
Network locating for the electronic equipment 1 include but is not limited to internet, wide area network, Metropolitan Area Network (MAN), local area network, it is virtual specially
With network (Virtual Private Network, VPN) etc..
In one embodiment of the invention, the electronic equipment 1 includes, but are not limited to memory 12, processor 13,
And it is stored in the computer program that can be run in the memory 12 and on the processor 13, such as PG database processing
Program.
It will be understood by those skilled in the art that the schematic diagram is only the example of electronic equipment 1, not structure paired electrons
The restriction of equipment 1 may include perhaps combining certain components or different components, example than illustrating more or fewer components
Such as described electronic equipment 1 can also include input-output equipment, network access equipment, bus.
Alleged processor 13 can be central processing unit (Central Processing Unit, CPU), can also be
Other general processors, digital signal processor (Digital Signal Processor, DSP), specific integrated circuit
(Application Specific Integrated Circuit, ASIC), ready-made programmable gate array (Field-
Programmable Gate Array, FPGA) either other programmable logic device, discrete gate or transistor logic,
Discrete hardware components etc..General processor can be microprocessor or the processor is also possible to any conventional processor
Deng the processor 13 is arithmetic core and the control centre of the electronic equipment 1, entire using various interfaces and connection
The various pieces of electronic equipment 1, and execute the operating system of the electronic equipment 1 and types of applications program, the program of installation
Code etc..
The processor 13 executes the operating system of the electronic equipment 1 and the types of applications program of installation.The place
Reason device 13 executes the application program to realize the step in above-mentioned each PG data base processing method embodiment, such as Fig. 1 institute
Step S10, S11, S12, S13, the S14 shown.
Alternatively, the processor 13 realizes each module in above-mentioned each Installation practice/mono- when executing the computer program
The function of member, such as: after receiving the instruction of PG database analysis, Xiang Suoshu PG database sends connection request；It is receiving
After the permission link order that the PG database returns, the connection with the PG database is established；According to the PG database point
Analysis instruction, transfers basic data from the PG database；Logical operation is carried out to the basic data, obtains TOP SQL language
Sentence；Show the TOP SQL statement.
Illustratively, the computer program can be divided into one or more module/units, one or more
A module/unit is stored in the memory 12, and is executed by the processor 13, to complete the present invention.It is one
Or multiple module/units can be the series of computation machine program instruction section that can complete specific function, the instruction segment is for retouching
State implementation procedure of the computer program in the electronic equipment 1.For example, the computer program can be divided into hair
Unit 110 is sent, unit 111 is established, transfers unit 112, arithmetic element 113, display unit 114, acquiring unit 115 and matching list
Member 116.
The memory 12 can be used for storing the computer program and/or module, the processor 13 by operation or
The computer program and/or module being stored in the memory 12 are executed, and calls the data being stored in memory 12,
Realize the various functions of the electronic equipment 1.The memory 12 can mainly include storing program area and storage data area,
In, storing program area can application program needed for storage program area, at least one function (such as sound-playing function, image
Playing function etc.) etc.；Storage data area, which can be stored, uses created data (such as audio data, phone directory according to mobile phone
Deng) etc..In addition, memory 12 may include high-speed random access memory, it can also include nonvolatile memory, such as firmly
Disk, memory, plug-in type hard disk, intelligent memory card (Smart Media Card, SMC), secure digital (Secure Digital,
SD) block, flash card (Flash Card), at least one disk memory, flush memory device or other volatile solid-states
Part.
The memory 12 can be the external memory and/or internal storage of electronic equipment 1.Further, described
Memory 12 can be the circuit with store function for not having physical form in integrated circuit, such as RAM (Random-Access
Memory, random access memory), FIFO (First In First Out) etc..Alternatively, the memory 12 is also possible to
Memory with physical form, such as memory bar, TF card (Trans-flash Card).
If the integrated module/unit of the electronic equipment 1 is realized in the form of SFU software functional unit and as independent
Product when selling or using, can store in a computer readable storage medium.Based on this understanding, the present invention is real
All or part of the process in existing above-described embodiment method, can also instruct relevant hardware come complete by computer program
At the computer program can be stored in a computer readable storage medium, which is being executed by processor
When, it can be achieved that the step of above-mentioned each embodiment of the method.
Wherein, the computer program includes computer program code, and the computer program code can be source code
Form, object identification code form, executable file or certain intermediate forms etc..The computer-readable medium may include: can
Carry any entity or device, recording medium, USB flash disk, mobile hard disk, magnetic disk, CD, computer of the computer program code
Memory, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access
Memory), electric carrier signal, telecommunication signal and software distribution medium etc..It should be noted that the computer-readable medium
The content for including can carry out increase and decrease appropriate according to the requirement made laws in jurisdiction with patent practice, such as in certain departments
Method administrative area does not include electric carrier signal and telecommunication signal according to legislation and patent practice, computer-readable medium.
In conjunction with Fig. 1, the memory 12 in the electronic equipment 1 stores multiple instruction to realize at a kind of PG database
The multiple instruction can be performed to realize in reason method, the processor 13: after receiving the instruction of PG database analysis, to institute
It states PG database and sends connection request；After receiving the permission link order that the PG database returns, establish and the PG
The connection of database；It is instructed according to the PG database analysis, transfers basic data from the PG database；To the basis
Data carry out logical operation, obtain TOP SQL statement；Show the TOP SQL statement.
Preferred embodiment according to the present invention, the processor 13 also execute multiple instruction and include:
User name and password are sent to the PG database, for the PG database authentication.
Preferred embodiment according to the present invention, the processor 13 also execute multiple instruction and include:
Obtain IP address and the port of the PG database；
The IP address and port are sent to configuration fastening means；
Based on the configuration fastening means, the connection with the PG database is established.
Preferred embodiment according to the present invention, the basic data include the combination of following one or more:
Central processor CPU consumption, execution efficiency execute the time, execute number.
Preferred embodiment according to the present invention, the processor 13 also execute multiple instruction and include:
The basic data is ranked up according to configuration rule；
The sentence of presetting digit capacity before obtaining, as the TOP SQL statement.
Preferred embodiment according to the present invention, the processor 13 also execute multiple instruction and include:
When receiving the instruction for showing analysis result, the Sentence analysis list of configuration is obtained；
The TOP SQL statement is matched with the sentence in the Sentence analysis list, obtains matched sentence；
From the Sentence analysis list, analysis result corresponding with the matched sentence is obtained；
Show the analysis result.
Preferred embodiment according to the present invention, the processor 13 also execute multiple instruction and include:
When the analysis is abnormal as the result is shown, the execution period corresponding to abnormal TOP SQL statement is obtained；
Obtain the execution journal in the execution period；
The execution journal is shown, so that user checks.
Specifically, the processor 13 can refer to the concrete methods of realizing of above-metioned instruction related in Fig. 1 corresponding embodiment
The description of step, this will not be repeated here.
In several embodiments provided by the present invention, it should be understood that disclosed system, device and method can be with
It realizes by another way.For example, the apparatus embodiments described above are merely exemplary, for example, the module
It divides, only a kind of logical function partition, there may be another division manner in actual implementation.
The module as illustrated by the separation member may or may not be physically separated, aobvious as module
The component shown may or may not be physical unit, it can and it is in one place, or may be distributed over multiple
In network unit.Some or all of the modules therein can be selected to realize the mesh of this embodiment scheme according to the actual needs
's.
It, can also be in addition, each functional module in each embodiment of the present invention can integrate in one processing unit
It is that each unit physically exists alone, can also be integrated in one unit with two or more units.Above-mentioned integrated list
Member both can take the form of hardware realization, can also realize in the form of hardware adds software function module.
It is obvious to a person skilled in the art that invention is not limited to the details of the above exemplary embodiments, Er Qie
In the case where without departing substantially from spirit or essential attributes of the invention, the present invention can be realized in other specific forms.
Therefore, in all respects, the present embodiments are to be considered as illustrative and not restrictive, this
The range of invention is indicated by the appended claims rather than the foregoing description, it is intended that the equivalent requirements of the claims will be fallen in
All changes in meaning and scope are included in the present invention.Any attached associated diagram label in claim should not be considered as limit
Claim involved in making.
Furthermore, it is to be understood that one word of " comprising " does not exclude other units or steps, odd number is not excluded for plural number.In system claims
The multiple units or device of statement can also be implemented through software or hardware by a unit or device.Second equal words are used
It indicates title, and does not indicate any particular order.
Finally it should be noted that the above examples are only used to illustrate the technical scheme of the present invention and are not limiting, although reference
Preferred embodiment describes the invention in detail, those skilled in the art should understand that, it can be to of the invention
Technical solution is modified or equivalent replacement, without departing from the spirit and scope of the technical solution of the present invention.
Claims (10)
1. a kind of PG data base processing method, which is characterized in that the described method includes:
After receiving the instruction of PG database analysis, Xiang Suoshu PG database sends connection request；
After receiving the permission link order that the PG database returns, the connection with the PG database is established；
It is instructed according to the PG database analysis, transfers basic data from the PG database；
Logical operation is carried out to the basic data, obtains TOP SQL statement；
Show the TOP SQL statement.
2. PG data base processing method as described in claim 1, which is characterized in that described send to the PG database connects
Request includes:
User name and password are sent to the PG database, for the PG database authentication.
3. PG data base processing method as described in claim 1, which is characterized in that the company of the foundation and the PG database
It connects and includes:
Obtain IP address and the port of the PG database；
The IP address and port are sent to configuration fastening means；
Based on the configuration fastening means, the connection with the PG database is established.
4. PG data base processing method as described in claim 1, which is characterized in that the basic data include it is following a kind of or
The a variety of combination of person:
Central processor CPU consumption, execution efficiency execute the time, execute number.
5. PG data base processing method as described in claim 1, which is characterized in that described to carry out logic to the basic data
Operation, obtaining TOP SQL statement includes:
The basic data is ranked up according to configuration rule；
The sentence of presetting digit capacity before obtaining, as the TOP SQL statement.
6. PG data base processing method as described in claim 1, which is characterized in that when showing the TOP SQL statement, institute
State method further include:
When receiving the instruction for showing analysis result, the Sentence analysis list of configuration is obtained；
The TOP SQL statement is matched with the sentence in the Sentence analysis list, obtains matched sentence；
From the Sentence analysis list, analysis result corresponding with the matched sentence is obtained；
Show the analysis result.
7. PG data base processing method as claimed in claim 6, which is characterized in that described after showing the analysis result
Method further include:
When the analysis is abnormal as the result is shown, the execution period corresponding to abnormal TOP SQL statement is obtained；
Obtain the execution journal in the execution period；
The execution journal is shown, so that user checks.
8. a kind of PG database processing device, which is characterized in that described device includes:
Transmission unit, for after receiving the instruction of PG database analysis, Xiang Suoshu PG database to send connection request；
Unit is established, for establishing and the PG database after receiving the permission link order that the PG database returns
Connection；
Unit is transferred, for instructing according to the PG database analysis, transfers basic data from the PG database；
Arithmetic element obtains TOP SQL statement for carrying out logical operation to the basic data；
Display unit, for showing the TOP SQL statement.
9. a kind of electronic equipment, which is characterized in that the electronic equipment includes:
Memory stores at least one instruction；And
Processor executes the instruction stored in the memory to realize PG number as claimed in any of claims 1 to 7 in one of claims
According to library processing method.
10. a kind of computer readable storage medium, it is characterised in that: be stored at least one in the computer readable storage medium
A instruction, at least one described instruction are executed by the processor in electronic equipment to realize such as any one of claim 1 to 7
The PG data base processing method.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811537728.2A CN109828983B (en) | 2018-12-15 | 2018-12-15 | PG database processing method, device, electronic equipment and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811537728.2A CN109828983B (en) | 2018-12-15 | 2018-12-15 | PG database processing method, device, electronic equipment and storage medium |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN109828983A true CN109828983A (en) | 2019-05-31 |
| CN109828983B CN109828983B (en) | 2024-05-07 |
Family
ID=66858864
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201811537728.2A Active CN109828983B (en) | 2018-12-15 | 2018-12-15 | PG database processing method, device, electronic equipment and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN109828983B (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110543405A (en) * | 2019-08-15 | 2019-12-06 | 平安普惠企业管理有限公司 | Log display method and device, storage medium and computer equipment |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20050050040A1 (en) * | 2003-08-29 | 2005-03-03 | Dietmar Theobald | Database access statement parser |
| CN1858738A (en) * | 2006-02-15 | 2006-11-08 | 华为技术有限公司 | Method and device for access data bank |
| CN102385628A (en) * | 2011-11-14 | 2012-03-21 | 北京锐安科技有限公司 | Java data base connectivity (JDBC)-based data distributed processing method |
| CN103258121A (en) * | 2013-04-25 | 2013-08-21 | 重庆图珀信息技术有限公司 | Clinical blood transfusion evaluating system and evaluating method |
| CN108733720A (en) * | 2017-04-24 | 2018-11-02 | 北京京东尚科信息技术有限公司 | The on-line testing method and check system of the SQL query of multiple database type |
-
2018
- 2018-12-15 CN CN201811537728.2A patent/CN109828983B/en active Active
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20050050040A1 (en) * | 2003-08-29 | 2005-03-03 | Dietmar Theobald | Database access statement parser |
| CN1858738A (en) * | 2006-02-15 | 2006-11-08 | 华为技术有限公司 | Method and device for access data bank |
| CN102385628A (en) * | 2011-11-14 | 2012-03-21 | 北京锐安科技有限公司 | Java data base connectivity (JDBC)-based data distributed processing method |
| CN103258121A (en) * | 2013-04-25 | 2013-08-21 | 重庆图珀信息技术有限公司 | Clinical blood transfusion evaluating system and evaluating method |
| CN108733720A (en) * | 2017-04-24 | 2018-11-02 | 北京京东尚科信息技术有限公司 | The on-line testing method and check system of the SQL query of multiple database type |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110543405A (en) * | 2019-08-15 | 2019-12-06 | 平安普惠企业管理有限公司 | Log display method and device, storage medium and computer equipment |
Also Published As
| Publication number | Publication date |
|---|---|
| CN109828983B (en) | 2024-05-07 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN109086409B (en) | Microservice data processing method and device, electronic equipment and computer readable medium | |
| US20200050968A1 (en) | Interactive interfaces for machine learning model evaluations | |
| JP5298117B2 (en) | Data merging in distributed computing | |
| US10229210B2 (en) | Search query task management for search system tuning | |
| JP5815563B2 (en) | Method and system for e-commerce transaction data accounting | |
| CN108536761A (en) | Report data querying method and server | |
| US20030088579A1 (en) | Collecting statistics in a database system | |
| CN107077476A (en) | Event is enriched for event handling using the big data of regime type | |
| CN101587491A (en) | Hybrid database system using runtime reconfigurable hardware | |
| CN109542966B (en) | Data fusion method and device, electronic equipment and computer readable medium | |
| Chen et al. | From tpc-c to big data benchmarks: A functional workload model | |
| US8533159B2 (en) | Processing materialized tables in a multi-tenant application system | |
| CN111666492A (en) | Information pushing method, device and equipment based on user behaviors and storage medium | |
| US20190050435A1 (en) | Object data association index system and methods for the construction and applications thereof | |
| Hesse et al. | ESPBench: The enterprise stream processing benchmark | |
| CN103177066A (en) | Analyzing and representing interpersonal relations | |
| CN110795478A (en) | Data warehouse updating method and device applied to financial business and electronic equipment | |
| CN110807016A (en) | Data warehouse construction method and device applied to financial business and electronic equipment | |
| CN112949269A (en) | Method, system, equipment and storage medium for generating visual data analysis report | |
| CN110347573A (en) | Application program analysis method, device, electronic equipment and computer-readable medium | |
| CN111125226B (en) | Configuration data acquisition method and device | |
| CN109828983A (en) | PG data base processing method, device, electronic equipment and storage medium | |
| CN111488386A (en) | Data query method and device | |
| CN115168474B (en) | Internet of things central station system building method based on big data model | |
| CN110874365A (en) | Information query method and related equipment thereof |
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 | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |