CN111125053A - Method and equipment for optimizing database writing rate - Google Patents
Method and equipment for optimizing database writing rate Download PDFInfo
- Publication number
- CN111125053A CN111125053A CN201911050000.1A CN201911050000A CN111125053A CN 111125053 A CN111125053 A CN 111125053A CN 201911050000 A CN201911050000 A CN 201911050000A CN 111125053 A CN111125053 A CN 111125053A
- Authority
- CN
- China
- Prior art keywords
- user
- data
- database
- write
- writing
- 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
- 238000000034 method Methods 0.000 title claims abstract description 39
- 230000004044 response Effects 0.000 claims abstract description 27
- 230000008569 process Effects 0.000 description 9
- 238000004590 computer program Methods 0.000 description 6
- 238000005457 optimization Methods 0.000 description 5
- 238000013461 design Methods 0.000 description 4
- 230000002159 abnormal effect Effects 0.000 description 2
- 230000008859 change Effects 0.000 description 2
- 230000001419 dependent effect Effects 0.000 description 2
- 238000010586 diagram Methods 0.000 description 2
- 230000000694 effects Effects 0.000 description 2
- 230000006870 function Effects 0.000 description 2
- 238000012986 modification Methods 0.000 description 2
- 230000004048 modification Effects 0.000 description 2
- 230000009286 beneficial effect Effects 0.000 description 1
- 238000011161 development Methods 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
- 238000012360 testing method Methods 0.000 description 1
Images
Classifications
-
- 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/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- 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/21—Design, administration or maintenance of databases
- G06F16/217—Database tuning
-
- 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/22—Indexing; Data structures therefor; Storage structures
- G06F16/2228—Indexing structures
Abstract
The invention provides a method and equipment for optimizing database writing speed, wherein the method comprises the following steps: creating a table in a database; in response to receiving a request by a first user to write data to a table and no other users lock the table, allowing the first user to write data to the table and lock; in response to receiving a request of a second user for writing data into the table and the data volume written by the first user is larger than a threshold multiple of the data volume written by the second user, suspending the first user from writing the data and unlocking the data, and allowing the second user to write the data and lock the data; and in response to the second user finishing writing the data and unlocking, allowing the first user to continue writing the data. By using the method provided by the invention, the writing speed of the database can be accelerated, the user experience is improved, the performance of the database is greatly improved, the usability of products depending on the database is improved, and the product competitiveness is further improved.
Description
Technical Field
The field relates to the field of computers, and more particularly to a method and apparatus for optimizing database write rates.
Background
In the modern of rapid development of information, mass data are stored in a database, the query and write performance of the database is reduced along with the increase of the data scale, a user optimizes the database performance from the conventional software and hardware configuration in a common mode, the software configuration optimization mode comprises the steps of designing a reasonable table structure, providing a proper table index, using a proper storage engine for each table, designing reasonable table row data, and using a proper locking strategy to correctly set the size of a cache memory; the hardware level optimization mode comprises optimizing seek time and disk reading and writing, and the method is to distribute data to a plurality of disks and read the data from the plurality of disks in parallel, so that the configuration of a CPU and a memory is improved. The data base reading and writing performance can be improved to the maximum extent by reasonably setting the above way. However, each developer needs to know how to optimize the existing software hardware configuration and software configuration, and strictly adheres to the relevant configuration in the use process, and because of the diversification and complication of the use scenes and the diversification of the optimization method, the developer often cannot test each scene, and cannot realize the complete optimization of the software configuration. Optimizing the hardware configuration entails increased costs.
Disclosure of Invention
In view of this, an object of the embodiments of the present invention is to provide a method for optimizing a database write rate, which can accelerate a database write speed, improve user experience, greatly improve database performance, and improve usability of database-dependent products, thereby improving product competitiveness.
In view of the above object, an aspect of the embodiments of the present invention provides a method for optimizing a database write rate, comprising the steps of:
creating a table in a database;
in response to receiving a request by a first user to write data to a table and no other users lock the table, allowing the first user to write data to the table and lock;
in response to receiving a request of a second user for writing data into the table and the data volume written by the first user is larger than a threshold multiple of the data volume written by the second user, suspending the first user from writing the data and unlocking the data, and allowing the second user to write the data and lock the data;
and in response to the second user finishing writing the data and unlocking, allowing the first user to continue writing the data.
According to an embodiment of the present invention, further comprising: after creating the table in the database, it is checked whether the structure of the table is correct.
According to one embodiment of the invention, a warning is popped in response to the structure of the table being incorrect.
According to one embodiment of the invention, the amount of write data is proportional to the locking time.
According to one embodiment of the invention, the threshold multiple is 10 times.
In another aspect of the embodiments of the present invention, there is also provided an apparatus for optimizing a database write rate, including:
at least one processor; and
a memory storing program code executable by the processor, the program code when executed by the processor performing the steps of:
creating a table in a database;
in response to receiving a request by a first user to write data to a table and no other users lock the table, allowing the first user to write data to the table and lock;
in response to receiving a request of a second user for writing data into the table and the data volume written by the first user is larger than a threshold multiple of the data volume written by the second user, suspending the first user from writing the data and unlocking the data, and allowing the second user to write the data and lock the data;
and in response to the second user finishing writing the data and unlocking, allowing the first user to continue writing the data.
According to one embodiment of the invention, the steps further comprise: after creating the table in the database, it is checked whether the structure of the table is correct.
According to one embodiment of the invention, a warning is popped in response to the structure of the table being incorrect.
According to one embodiment of the invention, the amount of write data is proportional to the locking time.
According to one embodiment of the invention, the threshold multiple is 10 times.
The invention has the following beneficial technical effects: the method for optimizing the database writing speed provided by the embodiment of the invention comprises the steps of establishing a table in a database; in response to receiving a request by a first user to write data to a table and no other users lock the table, allowing the first user to write data to the table and lock; in response to receiving a request of a second user for writing data into the table and the data volume written by the first user is larger than a threshold multiple of the data volume written by the second user, suspending the first user from writing the data and unlocking the data, and allowing the second user to write the data and lock the data; the technical scheme that the second user finishes writing data and unlocks the data and the first user is allowed to continue writing data can be responded, the writing speed of the database can be increased, the user experience is improved, the performance of the database is greatly improved, the usability of the database-dependent product is improved, and the product competitiveness is further improved.
Drawings
In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings used in the description of the embodiments or the prior art will be briefly described below, and it is obvious that the drawings in the following description are only some embodiments of the present invention, and it is obvious for those skilled in the art that other embodiments can be obtained by using the drawings without creative efforts.
FIG. 1 is a schematic flow chart diagram of a method of optimizing database write rates according to one embodiment of the present invention.
Detailed Description
In order to make the objects, technical solutions and advantages of the present invention more apparent, the following embodiments of the present invention are described in further detail with reference to the accompanying drawings.
In view of the above objects, a first aspect of embodiments of the present invention proposes an embodiment of a method for optimizing a database write rate. Fig. 1 shows a schematic flow diagram of the method.
As shown in fig. 1, the method may include the steps of:
s1 creating a table in the database;
s2, in response to receiving the request of the first user to write data into the table and no other user locks the table, allowing the first user to write data into the table and lock;
s3, in response to the fact that a request for writing data into the table by a second user is received and the data volume written by the first user is larger than a threshold multiple of the data volume written by the second user, suspending the writing of the data by the first user and unlocking, and allowing the second user to write the data and lock;
s4 allows the first user to continue writing data in response to the second user having finished writing data and unlocked.
The invention discloses a method for improving database performance by optimizing software configuration, which comprises the following steps of adding SQL Tuning Module, wherein the SQL Tuning Module comprises four daemon processes: ProcessForTable, ProcessForTableContent, ProcessForPolicy, MonitorProcess. The ProcessForTable mainly monitors whether the structural design of the table is reasonable or not, whether the index is set or not, if the index is not reasonable, a warning is popped up, and a user can revise and submit again according to the warning. And the ProcessForPolicy monitors whether a proper locking strategy is used or not, whether a memory area for caching is correctly set or not, and if the locking strategy is not reasonable, the locking strategy of the table is optimized by adopting different algorithms. The MonitorProcess is used for detecting whether the three daemon processes are abnormal or not, and if the three daemon processes are abnormal, the assigned daemon processes are pulled up to ensure that each daemon process works normally. By optimizing the software configuration in a software mode, the error rate can be reduced, the performance of the database can be optimized more comprehensively, the manual participation is reduced, and the use difficulty of the database is reduced.
Through above technical scheme, can accelerate the write-in speed of database, promote user experience, greatly improve database performance, promote the ease of relying on database product, and then improve product competitiveness.
In a preferred embodiment of the present invention, the method further comprises: after creating the table in the database, it is checked whether the structure of the table is correct. The reasonably designed table structure can clearly record the object attributes and improve the efficiency of increasing, deleting, changing and searching data records.
In a preferred embodiment of the invention, a warning is popped in response to the table being incorrectly structured. Whether the structural design of the table is reasonable or not is mainly detected, whether the index is set or not is detected, if the index is unreasonable, a warning is popped up, and a user can revise and submit again according to the warning.
In a preferred embodiment of the invention, the amount of write data is proportional to the locking time.
If a user needs to write a large amount of data into a form, the form needs to be locked, operation change of other users is prevented, the situation that the user writes data is covered, if the data size is large, longer locking time is inevitably needed, a user behind the form needs to write a small amount of data into the form, if the user behind the form finishes writing the form, the user behind the form writes data again, experience feeling to the user behind the form is poor, the processForPolicy dynamically modifies the locking strategy, detects whether a memory area used for caching is correctly set or not, and if the locking strategy is unreasonable, the locking strategy of the form is optimized by adopting different algorithms. The write data volume is monitored, the write operation occupation time is estimated, data with short occupation time is preferentially distributed, and each user can be time-sliced, dynamically unlocked and locked aiming at the condition that a plurality of users operate a data table at the same time, so that each user is ensured to have time to write data.
In a preferred embodiment of the invention, the threshold multiple is 10 times. Other multiples can be adopted, and the specific needs are reasonably formulated according to the situation of the current data.
Through above technical scheme, through the mode optimization software configuration of software, can reduce the error rate, more comprehensive optimization database performance reduces artifical the participation, reduces the database and uses the degree of difficulty, can accelerate the write-in speed of database, promotes user experience, greatly improves database performance, promotes the ease for use of relying on the database product, and then improves product competitiveness.
It should be noted that, as will be understood by those skilled in the art, all or part of the processes in the methods of the above embodiments may be implemented by instructing relevant hardware through a computer program, and the above programs may be stored in a computer-readable storage medium, and when executed, the programs may include the processes of the embodiments of the methods as described above. The storage medium may be a magnetic disk, an optical disk, a Read-Only Memory (ROM), a Random Access Memory (RAM), or the like. The embodiments of the computer program may achieve the same or similar effects as any of the above-described method embodiments.
Furthermore, the method disclosed according to an embodiment of the present invention may also be implemented as a computer program executed by a CPU, and the computer program may be stored in a computer-readable storage medium. The computer program, when executed by the CPU, performs the above-described functions defined in the method disclosed in the embodiments of the present invention.
In view of the above object, a second aspect of the embodiments of the present invention proposes an apparatus for optimizing a database write rate, characterized by comprising:
at least one processor; and
a memory storing program code executable by the processor, the program code when executed by the processor performing the steps of:
creating a table in a database;
in response to receiving a request by a first user to write data to a table and no other users lock the table, allowing the first user to write data to the table and lock;
in response to receiving a request of a second user for writing data into the table and the data volume written by the first user is larger than a threshold multiple of the data volume written by the second user, suspending the first user from writing the data and unlocking the data, and allowing the second user to write the data and lock the data;
and in response to the second user finishing writing the data and unlocking, allowing the first user to continue writing the data.
In a preferred embodiment of the present invention, the steps further comprise: after creating the table in the database, it is checked whether the structure of the table is correct. The reasonably designed table structure can clearly record the object attributes and improve the efficiency of increasing, deleting, changing and searching data records.
In a preferred embodiment of the invention, a warning is popped in response to the structure of the table being incorrect. Whether the structural design of the table is reasonable or not is mainly detected, whether the index is set or not is detected, if the index is unreasonable, a warning is popped up, and a user can revise and submit again according to the warning.
In a preferred embodiment of the invention, the amount of write data is proportional to the locking time.
If a user needs to write a large amount of data into a form, the form needs to be locked, operation change of other users is prevented, the situation that the user writes data is covered, if the data size is large, longer locking time is inevitably needed, a user behind the form needs to write a small amount of data into the form, if the user behind the form finishes writing the form, the user behind the form writes data again, experience feeling to the user behind the form is poor, the processForPolicy dynamically modifies the locking strategy, detects whether a memory area used for caching is correctly set or not, and if the locking strategy is unreasonable, the locking strategy of the form is optimized by adopting different algorithms. The write data volume is monitored, the write operation occupation time is estimated, the data with short occupation time is preferentially distributed, each user can be time-sliced and dynamically unlocked and locked aiming at the condition that a plurality of users operate the data table simultaneously, and each user is ensured to have time to write data
In a preferred embodiment of the invention, the threshold multiple is 10 times. Other multiples can be adopted, and the specific needs are reasonably formulated according to the situation of the current data.
It should be particularly noted that the embodiment of the system described above employs the embodiment of the method described above to specifically describe the working process of each module, and those skilled in the art can easily think that the modules are applied to other embodiments of the method described above.
Further, the above-described method steps and system elements or modules may also be implemented using a controller and a computer-readable storage medium for storing a computer program for causing the controller to implement the functions of the above-described steps or elements or modules.
Those of skill would further appreciate that the various illustrative logical blocks, modules, circuits, and algorithm steps described in connection with the disclosure herein may be implemented as electronic hardware, computer software, or combinations of both. To clearly illustrate this interchangeability of hardware and software, various illustrative components, blocks, modules, circuits, and steps have been described above generally in terms of their functionality. Whether such functionality is implemented as software or hardware depends upon the particular application and design constraints imposed on the overall system. Skilled artisans may implement the described functionality in varying ways for each particular application, but such implementation decisions should not be interpreted as causing a departure from the scope of the disclosed embodiments of the present invention.
The embodiments described above, particularly any "preferred" embodiments, are possible examples of implementations and are presented merely to clearly understand the principles of the invention. Many variations and modifications may be made to the above-described embodiments without departing from the spirit and principles of the technology described herein. All such modifications are intended to be included within the scope of this disclosure and protected by the following claims.
Claims (10)
1. A method for optimizing database write rates, comprising the steps of:
creating a table in the database;
in response to receiving a request by a first user to write data to the table and no other users lock the table, allowing the first user to write data to the table and lock;
in response to receiving a request of a second user to write data into the table and the amount of data written by the first user is greater than a threshold multiple of the amount of data written by the second user, suspending the first user from writing data and unlocking, and allowing the second user to write data and lock;
and responding to the second user finishing writing data and unlocking, and allowing the first user to continue writing data.
2. The method of claim 1, further comprising: after creating a table in the database, it is checked whether the structure of the table is correct.
3. The method of claim 2, wherein a warning is popped in response to the structure of the table being incorrect.
4. The method of claim 1, wherein the amount of write data is proportional to the locking time.
5. The method of claim 1, wherein the threshold multiple is 10 times.
6. An apparatus for optimizing database write rates, the apparatus comprising:
at least one processor; and
a memory storing program code executable by the processor, the program code, when executed by the processor, performing the steps of:
creating a table in the database;
in response to receiving a request by a first user to write data to the table and no other users lock the table, allowing the first user to write data to the table and lock;
in response to receiving a request of a second user to write data into the table and the amount of data written by the first user is greater than a threshold multiple of the amount of data written by the second user, suspending the first user from writing data and unlocking, and allowing the second user to write data and lock;
and responding to the second user finishing writing data and unlocking, and allowing the first user to continue writing data.
7. The apparatus of claim 6, wherein the steps further comprise: after creating a table in the database, it is checked whether the structure of the table is correct.
8. The apparatus of claim 6, wherein a warning is popped in response to a structure of the table being incorrect.
9. The apparatus of claim 6, wherein the amount of write data is proportional to the locking time.
10. The apparatus of claim 6, wherein the threshold multiple is 10 times.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201911050000.1A CN111125053A (en) | 2019-10-31 | 2019-10-31 | Method and equipment for optimizing database writing rate |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201911050000.1A CN111125053A (en) | 2019-10-31 | 2019-10-31 | Method and equipment for optimizing database writing rate |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN111125053A true CN111125053A (en) | 2020-05-08 |
Family
ID=70495480
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201911050000.1A Pending CN111125053A (en) | 2019-10-31 | 2019-10-31 | Method and equipment for optimizing database writing rate |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111125053A (en) |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101714167A (en) * | 2009-11-03 | 2010-05-26 | 北京高信达网络科技有限公司 | Method and device for accessing monofile database |
| CN105183852A (en) * | 2015-09-08 | 2015-12-23 | 网易（杭州）网络有限公司 | Database migration method and device |
| CN106469150A (en) * | 2015-08-14 | 2017-03-01 | 阿里巴巴集团控股有限公司 | File read/write method, device and system |
| CN109558218A (en) * | 2018-12-04 | 2019-04-02 | 山东浪潮通软信息科技有限公司 | A kind of distributed service data lock implementation method based on Redis |
-
2019
- 2019-10-31 CN CN201911050000.1A patent/CN111125053A/en active Pending
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101714167A (en) * | 2009-11-03 | 2010-05-26 | 北京高信达网络科技有限公司 | Method and device for accessing monofile database |
| CN106469150A (en) * | 2015-08-14 | 2017-03-01 | 阿里巴巴集团控股有限公司 | File read/write method, device and system |
| CN105183852A (en) * | 2015-09-08 | 2015-12-23 | 网易（杭州）网络有限公司 | Database migration method and device |
| CN109558218A (en) * | 2018-12-04 | 2019-04-02 | 山东浪潮通软信息科技有限公司 | A kind of distributed service data lock implementation method based on Redis |
Non-Patent Citations (2)
| Title |
|---|
| HANDSOME: "抢占式短作业优先调度算法（SJF）", 《HTTPS://BLOG.CSDN.NET/QQ_37999723/ARTICLE/DETAILS/72835300》 * |
| YANCOLIN: "MySQL中的读锁和写锁（转）", 《HTTPS://WWW.JIANSHU.COM/P/3206EC85F056》 * |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN111338766B (en) | Transaction processing method and device, computer equipment and storage medium | |
| US6772155B1 (en) | Looking data in a database system | |
| CN111159252B (en) | Transaction execution method and device, computer equipment and storage medium | |
| CN108363806B (en) | Multi-version concurrency control method and device for database, server and storage medium | |
| CN101615203B (en) | Concurrency control method and device | |
| US11386065B2 (en) | Database concurrency control through hash-bucket latching | |
| CN107577678B (en) | Method, client and server for processing database transaction | |
| CN103544153B (en) | A kind of data-updating method based on data base and system | |
| US20070208787A1 (en) | Method and apparatus for reducing overhead of validating constraints in a database | |
| US20100169289A1 (en) | Two Phase Commit With Grid Elements | |
| CN110765143B (en) | Data processing method, device, server and storage medium | |
| CN109933606B (en) | Database modification method, device, equipment and storage medium | |
| CN110399333B (en) | Method, apparatus and computer program product for deleting snapshots | |
| CN109634974A (en) | A kind of data processing method, system and associated component | |
| JP6877435B2 (en) | Database operation method and equipment | |
| CN111737212A (en) | Method and equipment for improving performance of distributed file system | |
| CN111414392A (en) | Cache asynchronous refresh method, system and computer readable storage medium | |
| CN112214649A (en) | Distributed transaction solution system of temporal graph database | |
| CN115858467A (en) | File processing method and device for key value database, electronic equipment and medium | |
| US20100106744A1 (en) | Conflict prevention for peer-to-peer replication | |
| CN113342507A (en) | Distributed lock service implementation method and device and computer equipment | |
| US11003540B2 (en) | Method, server, and computer readable medium for index recovery using index redo log | |
| CN109710629B (en) | Data access method, device, server and storage medium | |
| KR20120082176A (en) | Data processing method of database management system and system thereof | |
| CN111125053A (en) | Method and equipment for optimizing database writing rate |
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: 20200508 |
|
| RJ01 | Rejection of invention patent application after publication |