CN111104373A - Database performance optimization method - Google Patents
Database performance optimization method Download PDFInfo
- Publication number
- CN111104373A CN111104373A CN201911349433.7A CN201911349433A CN111104373A CN 111104373 A CN111104373 A CN 111104373A CN 201911349433 A CN201911349433 A CN 201911349433A CN 111104373 A CN111104373 A CN 111104373A
- Authority
- CN
- China
- Prior art keywords
- database
- file
- disk
- optimizing
- optimization 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
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/10—File systems; File servers
- G06F16/13—File access structures, e.g. distributed indices
- G06F16/134—Distributed indices
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/10—File systems; File servers
- G06F16/14—Details of searching files based on file metadata
- G06F16/144—Query formulation
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/10—File systems; File servers
- G06F16/16—File or folder operations, e.g. details of user interfaces specifically adapted to file systems
- G06F16/162—Delete operations
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Mathematical Physics (AREA)
- Library & Information Science (AREA)
- Human Computer Interaction (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention provides a database performance optimization method, which comprises the following steps: s1, optimizing a database file, and splitting the file; s2, optimizing query, unifying a client and a background read-write interface, providing a database module to close the interface, and adopting a pre-compiling mode for query operation; s3, optimizing file management, assigning indexes to sentences, and removing invalid indexes; and S4, optimizing reading and writing, namely adopting a WAL log mode, writing in a single thread and reading in multiple threads. The database performance optimization method can split the its.db file according to the disk and time period, which are totally zero, so that the query speed is increased, and the damage probability and the repair cost of the db file are reduced.
Description
Technical Field
The invention belongs to the field of embedded equipment big data, and particularly relates to a database performance optimization method.
Background
As a key link of an intelligent transportation scheme, the transportation host mainly takes the tasks of receiving snapshot records of the front-end camera, storing the snapshot records locally and uploading the snapshot records to a platform. At one intersection, 4 to 8 traffic cameras are accessed from one host, 3 to 5 vehicle-passing records are received in the peak time, the data volume exceeds 20 ten thousand every day, and the total data volume exceeds ten million in the state of full disks by matching with a plurality of large-capacity disks. The host equipment is mostly realized by adopting an embedded scheme, resources such as a cpu, an internal memory and the like are limited, the performance and the stability of the magnetic disk are reduced under the condition of long-time use, and under the condition, the equipment cannot meet the requirement of large-data-volume storage. Similarly, devices such as the face nvr and the face server have performance problems caused by too large snapshot amount of face pictures, which affects usability of users.
Disclosure of Invention
In view of this, the present invention provides a database performance optimization method to support ten million-level databases to read and write quickly and improve data stability under the condition of limited hardware resources.
In order to achieve the purpose, the technical scheme of the invention is realized as follows:
a database performance optimization method comprises the following steps:
s1, optimizing a database file, and splitting the file;
s2, optimizing query, unifying a client and a background read-write interface, providing a database module to close the interface, and adopting a pre-compiling mode for query operation;
s3, optimizing file management, assigning indexes to sentences, and removing invalid indexes;
and S4, optimizing reading and writing, namely adopting a WAL log mode, writing in a single thread and reading in multiple threads.
Further, the step S1 includes:
A1. splitting the database into a plurality of db files, naming the db files according to disks and serial numbers, and recording start and stop time of the db files through a tab _ cfg table;
A2. the integral point judges the current database, and a new database is built after the number of records and the number of days are limited;
A3. deleting the db file under the condition of meeting the data deletion condition and having no database connection;
A4. the database which is not operated in a certain time is connected and closed, so that the problem of excessive connection number is avoided;
A5. adjusting the main keys of the databases, wherein in order to ensure that the equipment is unique, each database file is allocated with id, the sub-database keys are increased by 0, and the (id <22) + keys are used as external main keys of the module;
A6. and detecting the size of the database file, setting an illegal value of 2G, and deleting and reconstructing if the illegal value exceeds the size.
Further, step S2 includes:
B1. database reading operation is concurrent, a database handle is registered once by the same thread, and the same handle is not used in multiple threads;
B2. the database reading operation provides two interfaces of sqlite3_ get _ table and sqlite3_ prepare;
B3. establishing connection for the database in each disk query time period, and respectively querying and combining query results;
B4. each database user reading operation thread is independent, required database connection is established, the database connection is normally opened in the same day, and the database connection is closed for a certain time without operation, so that excessive connection number is avoided;
B5. a unified shutdown interface for disk initialization, failures, etc. is provided.
Further, the step S3 includes:
C1. defaulting a first available disk as a database master disk, storing a config database file, and transferring the config file to an internal memory without changing the master disk when the format operation of the master disk is performed;
C2. judging the damage condition of the database by the database file operation error code, and performing background repair by a database backup or index reconstruction mode;
C3. and (5) carrying out damage inspection on the database in the early morning time period, and repairing.
C4. Adding a snapshot record reconstruction index for a certain disk on a certain day, creating a folder according to the day when storing a picture file, and requiring a kernel reconstruction index to support reconstruction according to the disk, the file type and the time period condition;
C5. retaining simple backup recovery logic for bk0, the current database is repaired by bk0, online files, and past data is restored by bk 0.
Further, the step S4 includes the following steps:
D1. each disk creates a database write thread and uniformly executes database write operation;
D2. the operation with low priority enters a sequence, and a record file is stored for power-off rewriting;
D3. taking a plurality of statements from the queue, packaging the statements into a transaction for writing, and inserting a high-priority operation into the current transaction to ensure timeliness;
D4. and write operation synchronous and asynchronous interfaces are provided, and the requirements of different scenes are met.
Compared with the prior art, the invention has the following advantages:
(1) the database performance optimization method provided by the invention divides the its.db file into parts according to the disk and the time period, improves the query speed, and reduces the damage probability and the repair cost of the db file.
(2) The database performance optimization method unifies the client and the background read-write interface, provides a database module closing interface, adopts a precompilation mode for query operation, and effectively controls the database operation quality.
(3) The database performance optimization method provided by the invention adopts single-thread writing and multi-thread reading, and long-time writing transactions are avoided.
Drawings
The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments of the invention and, together with the description, serve to explain the invention without limitation. In the drawings:
FIG. 1 is a schematic diagram illustrating a database query process according to an embodiment of the present invention;
FIG. 2 is a schematic diagram illustrating a database write flow according to an embodiment of the present invention;
FIG. 3 is a schematic diagram of a database file management process according to an embodiment of the present invention
Detailed Description
It should be noted that the embodiments and features of the embodiments of the present invention may be combined with each other without conflict.
In the description of the present invention, it is to be understood that the terms "central," "longitudinal," "lateral," "upper," "lower," "front," "rear," "left," "right," "vertical," "horizontal," "top," "bottom," "inner," "outer," and the like are used in the orientation or positional relationship indicated in the drawings, which are merely for convenience in describing the invention and to simplify the description, and are not intended to indicate or imply that the referenced device or element must have a particular orientation, be constructed and operated in a particular orientation, and are therefore not to be construed as limiting the invention. Furthermore, the terms "first", "second", etc. are used for descriptive purposes only and are not to be construed as indicating or implying relative importance or implicitly indicating the number of technical features indicated. Thus, a feature defined as "first," "second," etc. may explicitly or implicitly include one or more of that feature. In the description of the invention, the meaning of "a plurality" is two or more unless otherwise specified.
In the description of the invention, it is to be noted that, unless otherwise explicitly specified or limited, the terms "mounted", "connected" and "connected" are to be construed broadly, e.g. as being fixed or detachable or integrally connected; can be mechanically or electrically connected; they may be connected directly or indirectly through intervening media, or they may be interconnected between two elements. The specific meaning of the above terms in the creation of the present invention can be understood by those of ordinary skill in the art through specific situations.
The invention will be described in detail with reference to the following embodiments with reference to the attached drawings.
As shown in fig. 1 to 3, a method for optimizing database performance includes the following steps:
s1, optimizing a database file, and splitting the file;
s2, optimizing query, unifying a client and a background read-write interface, providing a database module to close the interface, and adopting a pre-compiling mode for query operation;
s3, optimizing file management, assigning indexes to sentences, and removing invalid indexes;
and S4, optimizing reading and writing, namely adopting a WAL log mode, writing in a single thread and reading in multiple threads.
The step S1 includes:
A1. splitting the database into a plurality of db files, naming the db files according to disks and serial numbers, and recording start and stop time of the db files through a tab _ cfg table;
A2. the integral point judges the current database, and a new database is built after the number of records and the number of days are limited;
A3. deleting the db file under the condition of meeting the data deletion condition and having no database connection;
A4. the database which is not operated in a certain time is connected and closed, so that the problem of excessive connection number is avoided;
A5. adjusting the main keys of the databases, wherein in order to ensure that the equipment is unique, each database file is allocated with id, the sub-database keys are increased by 0, and the (id <22) + keys are used as external main keys of the module;
A6. and detecting the size of the database file, setting an illegal value of 2G, and deleting and reconstructing if the illegal value exceeds the size.
As shown in fig. 1, step S2 includes:
B1. database reading operation is concurrent, a database handle is registered once by the same thread, and the same handle is not used in multiple threads;
B2. the database reading operation provides two interfaces of sqlite3_ get _ table and sqlite3_ prepare;
B3. establishing connection for the database in each disk query time period, and respectively querying and combining query results;
B4. each database user reading operation thread is independent, required database connection is established, the database connection is normally opened in the same day, and the database connection is closed for a certain time without operation, so that excessive connection number is avoided;
B5. a unified shutdown interface for disk initialization, failures, etc. is provided.
As shown in fig. 2, the step S3 includes:
C1. defaulting a first available disk as a database master disk, storing a config database file, and transferring the config file to an internal memory without changing the master disk when the format operation of the master disk is performed;
C2. judging the damage condition of the database by the database file operation error code, and performing background repair by a database backup or index reconstruction mode;
C3. and (5) carrying out damage inspection on the database in the early morning time period, and repairing.
C4. Adding a snapshot record reconstruction index for a certain disk on a certain day, creating a folder according to the day when storing a picture file, and requiring a kernel reconstruction index to support reconstruction according to the disk, the file type and the time period condition;
C5. retaining simple backup recovery logic for bk0, the current database is repaired by bk0, online files, and past data is restored by bk 0.
As shown in fig. 3, the step S4 includes the following steps:
D1. each disk creates a database write thread and uniformly executes database write operation;
D2. the operation with low priority enters a sequence, and a record file is stored for power-off rewriting;
D3. taking a plurality of statements from the queue, packaging the statements into a transaction for writing, and inserting a high-priority operation into the current transaction to ensure timeliness;
D4. provides synchronous and asynchronous interfaces for write operation and meets the requirements of different scenes
The above description is only for the purpose of illustrating the preferred embodiments of the present invention and should not be taken as limiting the invention, so that any modifications, equivalents, improvements and the like, which are within the spirit and principle of the present invention, should be included in the scope of the present invention.
Claims (5)
1. A database performance optimization method is characterized in that: the method comprises the following steps:
s1, optimizing a database file, and splitting the file;
s2, optimizing query, unifying a client and a background read-write interface, providing a database module to close the interface, and adopting a pre-compiling mode for query operation;
s3, optimizing file management, assigning indexes to sentences, and removing invalid indexes;
and S4, optimizing reading and writing, namely adopting a WAL log mode, writing in a single thread and reading in multiple threads.
2. The database performance optimization method of claim 1, wherein: the step S1 includes:
A1. splitting the database into a plurality of db files, naming the db files according to disks and serial numbers, and recording start and stop time of the db files through a tab _ cfg table;
A2. the integral point judges the current database, and a new database is built after the number of records and the number of days are limited;
A3. deleting the db file under the condition of meeting the data deletion condition and having no database connection;
A4. the database which is not operated in a certain time is connected and closed, so that the problem of excessive connection number is avoided;
A5. adjusting the main keys of the databases, wherein in order to ensure that the equipment is unique, each database file is allocated with id, the sub-database keys are increased by 0, and the (id <22) + keys are used as external main keys of the module;
A6. and detecting the size of the database file, setting an illegal value of 2G, and deleting and reconstructing if the illegal value exceeds the size.
3. The database performance optimization method of claim 1, wherein: step S2 includes:
B1. database reading operation is concurrent, a database handle is registered once by the same thread, and the same handle is not used in multiple threads;
B2. the database reading operation provides two interfaces of sqlite3_ get _ table and sqlite3_ prepare;
B3. establishing connection for the database in each disk query time period, and respectively querying and combining query results;
B4. each database user reading operation thread is independent, required database connection is established, the database connection is normally opened in the same day, and the database connection is closed for a certain time without operation, so that excessive connection number is avoided;
B5. a unified shutdown interface for disk initialization, failures, etc. is provided.
4. The database performance optimization method of claim 1, wherein: the step S3 includes:
C1. defaulting a first available disk as a database master disk, storing a config database file, and transferring the config file to an internal memory without changing the master disk when the format operation of the master disk is performed;
C2. judging the damage condition of the database by the database file operation error code, and performing background repair by a database backup or index reconstruction mode;
C3. and (5) carrying out damage inspection on the database in the early morning time period, and repairing.
C4. Adding a snapshot record reconstruction index for a certain disk on a certain day, creating a folder according to the day when storing a picture file, and requiring a kernel reconstruction index to support reconstruction according to the disk, the file type and the time period condition;
C5. retaining simple backup recovery logic for bk0, the current database is repaired by bk0, online files, and past data is restored by bk 0.
5. The database performance optimization method of claim 1, wherein: the step S4 includes the following steps:
D1. each disk creates a database write thread and uniformly executes database write operation;
D2. the operation with low priority enters a sequence, and a record file is stored for power-off rewriting;
D3. taking a plurality of statements from the queue, packaging the statements into a transaction for writing, and inserting a high-priority operation into the current transaction to ensure timeliness;
D4. and write operation synchronous and asynchronous interfaces are provided, and the requirements of different scenes are met.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201911349433.7A CN111104373B (en) | 2019-12-24 | 2019-12-24 | Database performance optimization method |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201911349433.7A CN111104373B (en) | 2019-12-24 | 2019-12-24 | Database performance optimization method |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN111104373A true CN111104373A (en) | 2020-05-05 |
| CN111104373B CN111104373B (en) | 2023-09-19 |
Family
ID=70424426
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201911349433.7A Active CN111104373B (en) | 2019-12-24 | 2019-12-24 | Database performance optimization method |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111104373B (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113641655A (en) * | 2021-08-17 | 2021-11-12 | 南京云信达科技有限公司 | Database performance optimization algorithm and device |
Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103049533A (en) * | 2012-12-23 | 2013-04-17 | 北京人大金仓信息技术股份有限公司 | Method for quickly loading data into database |
| WO2015100985A1 (en) * | 2013-12-30 | 2015-07-09 | 华为技术有限公司 | Method and database engine for recording transaction log |
| CN104809178A (en) * | 2015-04-15 | 2015-07-29 | 北京科电高技术公司 | Write-in method of key/value database memory log |
| CN108073696A (en) * | 2017-12-11 | 2018-05-25 | 厦门亿力吉奥信息科技有限公司 | GIS application processes based on distributed memory database |
| CN109635588A (en) * | 2018-12-20 | 2019-04-16 | 天津天地伟业信息系统集成有限公司 | A kind of document protection method based on Linux Virtual File System |
| CN109977334A (en) * | 2019-03-26 | 2019-07-05 | 浙江度衍信息技术有限公司 | Retrieval rate optimization method |
-
2019
- 2019-12-24 CN CN201911349433.7A patent/CN111104373B/en active Active
Patent Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103049533A (en) * | 2012-12-23 | 2013-04-17 | 北京人大金仓信息技术股份有限公司 | Method for quickly loading data into database |
| WO2015100985A1 (en) * | 2013-12-30 | 2015-07-09 | 华为技术有限公司 | Method and database engine for recording transaction log |
| CN104809178A (en) * | 2015-04-15 | 2015-07-29 | 北京科电高技术公司 | Write-in method of key/value database memory log |
| CN108073696A (en) * | 2017-12-11 | 2018-05-25 | 厦门亿力吉奥信息科技有限公司 | GIS application processes based on distributed memory database |
| CN109635588A (en) * | 2018-12-20 | 2019-04-16 | 天津天地伟业信息系统集成有限公司 | A kind of document protection method based on Linux Virtual File System |
| CN109977334A (en) * | 2019-03-26 | 2019-07-05 | 浙江度衍信息技术有限公司 | Retrieval rate optimization method |
Non-Patent Citations (1)
| Title |
|---|
| 周媛: "基于SaaS平台的数据库性能优化研究与设计" * |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113641655A (en) * | 2021-08-17 | 2021-11-12 | 南京云信达科技有限公司 | Database performance optimization algorithm and device |
Also Published As
| Publication number | Publication date |
|---|---|
| CN111104373B (en) | 2023-09-19 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US10657008B2 (en) | Managing a redundant computerized database using a replicated database cache | |
| US6983353B2 (en) | Method and apparatus for enhancing operations in disk array storage devices | |
| US7457921B2 (en) | Write barrier for data storage integrity | |
| US8074035B1 (en) | System and method for using multivolume snapshots for online data backup | |
| CN102696020B (en) | Based on the selectivity file system cache that configurable cache maps | |
| US7577800B2 (en) | Method for borrowing and returning physical volumes | |
| US7478216B2 (en) | Method, system, and article of manufacture for returning physical volumes | |
| US8635421B2 (en) | Snapshot based replication | |
| US20150193473A1 (en) | Database Storage System based on Optical Disk and Method Using the System | |
| US20080082525A1 (en) | File storage system, file storing method and file searching method therein | |
| US20140215127A1 (en) | Apparatus, system, and method for adaptive intent logging | |
| US6985996B1 (en) | Method and apparatus for relocating RAID meta data | |
| US8140886B2 (en) | Apparatus, system, and method for virtual storage access method volume data set recovery | |
| US20230185480A1 (en) | Ssd-based log data storage method and apparatus, device and medium | |
| US8984011B1 (en) | Page object caching for variably sized access control lists in data storage systems | |
| CN111104373A (en) | Database performance optimization method | |
| US6823348B2 (en) | File manager for storing several versions of a file | |
| US20210390121A1 (en) | Method and Apparatus for Hierarchical Generation of a Complex Object | |
| CN100369038C (en) | Method for implementing realtime database routine operation | |
| WO2004077219A2 (en) | System and method of mapping patterns of data, optimising disk read and write, verifying data integrity across clients and servers of different functionality having shared resources | |
| US11474981B2 (en) | Database dual-core storage system based on optical disk and method using the system | |
| US7797499B2 (en) | Apparatus, system, and method for uninterrupted storage configuration | |
| US11341159B2 (en) | In-stream data load in a replication environment | |
| US8805886B1 (en) | Recoverable single-phase logging | |
| US11720551B1 (en) | Method and system for streaming data from portable storage devices |
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 |