CN111475112B - Device for improving performance of Oracle database and data reading and writing method - Google Patents
Device for improving performance of Oracle database and data reading and writing method Download PDFInfo
- Publication number
- CN111475112B CN111475112B CN202010250410.7A CN202010250410A CN111475112B CN 111475112 B CN111475112 B CN 111475112B CN 202010250410 A CN202010250410 A CN 202010250410A CN 111475112 B CN111475112 B CN 111475112B
- Authority
- CN
- China
- Prior art keywords
- data
- nonvolatile memory
- hdd
- disk
- block device
- 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.)
- Active
Links
- 238000000034 method Methods 0.000 title claims abstract description 13
- 230000015654 memory Effects 0.000 claims abstract description 112
- 238000010586 diagram Methods 0.000 description 2
- PUAQLLVFLMYYJJ-UHFFFAOYSA-N 2-aminopropiophenone Chemical compound CC(N)C(=O)C1=CC=CC=C1 PUAQLLVFLMYYJJ-UHFFFAOYSA-N 0.000 description 1
- 230000009977 dual effect Effects 0.000 description 1
- 230000006870 function Effects 0.000 description 1
- 238000012986 modification Methods 0.000 description 1
- 230000004048 modification Effects 0.000 description 1
- 230000002688 persistence Effects 0.000 description 1
- 230000002085 persistent effect Effects 0.000 description 1
- 230000004044 response Effects 0.000 description 1
- 238000012795 verification Methods 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F3/00—Input arrangements for transferring data to be processed into a form capable of being handled by the computer; Output arrangements for transferring data from processing unit to output unit, e.g. interface arrangements
- G06F3/06—Digital input from, or digital output to, record carriers, e.g. RAID, emulated record carriers or networked record carriers
- G06F3/0601—Interfaces specially adapted for storage systems
- G06F3/0602—Interfaces specially adapted for storage systems specifically adapted to achieve a particular effect
- G06F3/061—Improving I/O performance
-
- 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
- G06F3/00—Input arrangements for transferring data to be processed into a form capable of being handled by the computer; Output arrangements for transferring data from processing unit to output unit, e.g. interface arrangements
- G06F3/06—Digital input from, or digital output to, record carriers, e.g. RAID, emulated record carriers or networked record carriers
- G06F3/0601—Interfaces specially adapted for storage systems
- G06F3/0628—Interfaces specially adapted for storage systems making use of a particular technique
- G06F3/0629—Configuration or reconfiguration of storage systems
- G06F3/0631—Configuration or reconfiguration of storage systems by allocating resources to storage systems
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F3/00—Input arrangements for transferring data to be processed into a form capable of being handled by the computer; Output arrangements for transferring data from processing unit to output unit, e.g. interface arrangements
- G06F3/06—Digital input from, or digital output to, record carriers, e.g. RAID, emulated record carriers or networked record carriers
- G06F3/0601—Interfaces specially adapted for storage systems
- G06F3/0668—Interfaces specially adapted for storage systems adopting a particular infrastructure
- G06F3/0671—In-line storage system
- G06F3/0683—Plurality of storage devices
- G06F3/0688—Non-volatile semiconductor memory arrays
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F3/00—Input arrangements for transferring data to be processed into a form capable of being handled by the computer; Output arrangements for transferring data from processing unit to output unit, e.g. interface arrangements
- G06F3/06—Digital input from, or digital output to, record carriers, e.g. RAID, emulated record carriers or networked record carriers
- G06F3/0601—Interfaces specially adapted for storage systems
- G06F3/0668—Interfaces specially adapted for storage systems adopting a particular infrastructure
- G06F3/0671—In-line storage system
- G06F3/0683—Plurality of storage devices
- G06F3/0689—Disk arrays, e.g. RAID, JBOD
Abstract
The invention discloses a device for improving the performance of an Oracle database and a data reading and writing method, wherein the Oracle database is configured on a server, and a CPU (central processing unit) of the server is connected with at least one HDD (hard disk drive) disk and at least one nonvolatile memory; the HDD disks correspond to the nonvolatile memories one by one, and the quantity of the HDD disks is the same; an HDD disk and a nonvolatile memory are configured as a block device; a memory block device is created on the nonvolatile memory, and the memory block device is configured as a cache device corresponding to the HDD disk. When writing data, oracle data is firstly written on the nonvolatile memory and then written back on the HDD disk in a writeback mode; when reading data, firstly detecting whether the data is on the nonvolatile memory, if so, directly reading the data from the nonvolatile memory, otherwise, continuously reading the data from the HDD magnetic disk. The invention can effectively improve the read-write capability of the database and reduce the cost of the server.
Description
Technical Field
The invention relates to the field of Oracle databases, in particular to a device for improving the performance of an Oracle database and a database data reading and writing method.
Background
An Oracle database (also known as Oracle rdbms, a relational database management system of Oracle corporation) system is a popular relational database management system at present because of its good portability, convenient use and strong function, and is suitable for various large, medium, small and microcomputer environments. For an OLTP type business system, the throughput (pressure bearing capacity) of a system is closely related to the consumption of CPU by requests, external interfaces, IO, and so on. The higher the consumption of a single reqeust on the CPU, the slower the response speed of an external system interface and IO is, the lower the throughput capacity of the system is, and the higher the throughput capacity of the system is. With the increase of CPU performance, the biggest performance bottleneck of Oracle database is IO performance. In order to solve the problem, a full flash disk (SSD disk array) array is generally adopted in the industry to improve the storage IO performance of the Oracle database, and as the data volume of a service is larger and larger, a larger capacity of the sassd or nvmesdd is forced to be selected, which causes a high cost problem and a performance surplus problem.
Disclosure of Invention
In order to solve the problems, the invention provides a device for improving the performance of an Oracle database and a data reading and writing method.
The technical scheme of the invention is as follows: an apparatus for improving performance of Oracle database, wherein the Oracle database is configured on a server,
the CPU of the server is connected with at least one HDD disk and at least one nonvolatile memory; the HDD disks correspond to the nonvolatile memories one by one, and the quantity of the HDD disks is the same;
a HDD disk and a nonvolatile memory are configured as a block device;
a memory block device is created on the nonvolatile memory, and the memory block device is configured as a cache device corresponding to the HDD disk.
Further, a block device configuration module is configured on the server, the memory block devices on the nonvolatile memory are configured as cache devices, and the HDD disks are set as backend devices, so that each HDD disk and the corresponding nonvolatile memory thereof are configured as each block device.
Further, the block device configuration module is a bcache cache management module.
Further, a memory block device creation module is configured on the server, the nonvolatile memory is set to be in a region mode, and the region of the nonvolatile memory is created as the memory block device.
Further, the memory block device creation module comprises an ipmctl tool and an ndctl tool;
the ipmctl tool sets the nonvolatile memory to be in a region mode; the ndctl tool creates a region of non-volatile memory as a memory block device.
Furthermore, oracle ASM management software is configured on the server, all the block devices are combined into a group of disk groups, and data striping storage is performed.
Furthermore, the server is provided with at least one pair of CPUs, and the number of the HDD disks connected with the two CPUs in each pair is the same; the Oracle ASM management software sets the two disk groups under each pair of CPUs as a failover group.
Further, the CPU is connected with the HDD disk through an SAS card, wherein the SAS card is connected to a PCIe slot of the CPU.
The technical scheme of the invention also comprises a data reading and writing method based on the device, which comprises the following steps:
when writing data, oracle data is firstly written to the nonvolatile memory and then written back to the HDD disk in a writeback mode;
caching Oracle hotspot data in a nonvolatile memory persistently;
when reading data, firstly detecting whether the data is on the nonvolatile memory, if so, directly reading the data from the nonvolatile memory, otherwise, continuously reading the data from the HDD magnetic disk.
Further, the method also comprises the following steps:
setting a read request time threshold and a write request time threshold;
during data writing, when the IO time exceeds a write request time threshold, bypassing the nonvolatile memory and directly writing the data into the HDD disk;
when reading data, when the IO time exceeds the read request time threshold, bypassing the nonvolatile memory and directly reading the HDD disk.
The device for improving the performance of the Oracle database and the data reading and writing method are characterized in that the same number of nonvolatile memories and HDD disks are configured under a processor to construct a plurality of block devices of the nonvolatile memories and the HDD disks, oracle data is firstly written on the nonvolatile memories during data writing and then written back on the HDD disks in a writeback mode, whether the data is on the nonvolatile memories during data reading is firstly detected, if yes, the data is directly read from the nonvolatile memories, otherwise, the data is continuously read from the HDD disks, and the reading and writing capability of the database is effectively improved. In addition, the HDD does not need to form an RAID array by an RAID disk array card, only needs a single disk to be used, and reduces the performance loss caused by the verification of the RAID disk array; the normal operation of the system is not influenced when the nonvolatile memory bank and the HDD disk fail; the nonvolatile memory and the HDD disk are both arranged under one processor, and the data processing does not need to cross the CPU; the device can be used for any Oracle database all-in-one machine or Oracle database; and compared with the adoption of a large-capacity full-flash disk array, the cost of the server is greatly reduced.
Drawings
FIG. 1 is a schematic structural diagram of an embodiment of the present invention.
Fig. 2 is a schematic architecture diagram of a specific implementation of an embodiment of the present invention.
FIG. 3 is a flowchart illustrating a second method according to an embodiment of the present invention.
Detailed Description
The present invention will be described in detail below with reference to the accompanying drawings by way of specific examples, which are illustrative of the present invention and are not limited to the following embodiments.
As shown in fig. 1, this embodiment provides a device for improving performance of an Oracle database, where the Oracle database is configured on a server, and to implement the device, a CPU1 of the server is connected to at least one HDD disk 2 and at least one nonvolatile memory 3; the HDD disks 2 correspond to the nonvolatile memories 3 one to one, and the number thereof is the same. One HDD disk 2 and one nonvolatile memory 3 are configured as one block device 5, where a memory block device 4 is created on the nonvolatile memory 3, and the memory block device 4 is configured as a cache device corresponding to the HDD disk 2. It should be noted that the CPU1 is connected to the HDD disk 2 through an SAS card, wherein the SAS card is connected to a PCIe slot of the CPU 1.
In this embodiment, a configuration module of a block device 5 is configured on the server, and the memory block device 4 on the nonvolatile memory 3 is configured as a cache device, and the HDD disks are set as backend devices, so that each HDD disk and the corresponding nonvolatile memory thereof are configured as each block device 5. Specifically, the block device 5 configuration module is a bcache (system kernel) cache management module.
A memory block device 4 creation module is configured on the server, the nonvolatile memory 3 is set to a region mode, and the region of the nonvolatile memory 3 is created as the memory block device 4. Specifically, the memory block device 4 creation module includes an ipmctl tool (Intel persistent memory control tool) and an ndctl tool (non-volatile memory control tool); the ipmctl tool sets the nonvolatile memory 3 to be in a region mode; the ndctl tool creates a region of the non-volatile memory 3 as the memory block device 4.
As shown in fig. 2, for example, the server has two CPUs, a SAS card is connected under each CPU of the server, 1-6 HDD disks 2 are connected on each SAS card, and nonvolatile memory 3 devices with the same number as the HDD disks 2 are configured under each CPU.
Installing ipmctl and ndctl tools under the system configures each nonvolatile memory 3 device as a memory block device 4 (pmem), and if the number of nonvolatile memory devices is 12, creating a memory block device 4 named pmem0, pmem1 \8230, pmem11 under the system.
A creating step:
using ipmctl to set the type of each nonvolatile memory 3 of the server to be a region mode for each nonvolatile memory 3, if there are 12 nonvolatile memories, regions 0 to 11 are generated correspondingly: ipmctl create-good persistence MemoryType = AppDirectNotInterleaved
A region of non-volatile memory is created as a pmem device using the ndctl tool:
ndctl create-namespace -region=region0
an activated kernel bcache cache management module is installed under a system, through a management command of the bcache module, each 1 memory block device 4 is set as a front-end cache (caching) device, each 1 HDD disk array is set as a back-end (backing) device, and finally a block device 5 such as bcache0, bcache1, bcache2, bcache3 and the like is generated.
Assuming that 6 HDD disks 2 and 6 nonvolatile memories 3 are configured under each CPU1, it is necessary to create block (bcache) devices 5 named bcache0 to 6 under CPU _0 and create block (bcache) devices 5 named bcache7 to 12 under CPU _ 1.
The commands are as follows:
make-bcache --cache_replacement_policy=lru -C /dev/pmem0 -B /dev/sdb
wherein — cache _ replacement _ policy is a caching algorithm, and lru is a least recently used algorithm, that is, hot spot data is cached in the nonvolatile memory 3.
the-C parameter specifies that the non-volatile memory 3 device is a caching device,
the B parameter specifies the HDD disk 2 as a back end storage device.
In this embodiment, the server is configured with an Oracle ASM (Automatic Storage Management) Management software, and all the block devices 5 are configured into a set of disk groups for data striping Storage. The Oracle data is evenly distributed over the generated plurality of block devices 5.
Meanwhile, the embodiment also adopts an ASM multi-copy mode to solve the fault tolerance problem of the nonvolatile memory 3 and the HDD 2. Specifically, the server is provided with at least one pair of CPUs 1, and the number of the HDD disks 2 connected with two CPUs in each pair is the same. The Oracle ASM management software sets the two disk groups under each pair of CPUs as a failover group.
Taking the example that the server shown in fig. 2 is connected with two CPUs 1, a corresponding HDD disk 2 and a nonvolatile memory 3 under each CPU1 form 1 device 5, and all the block devices 5 under each CPU1 form a group of disk groups by an OracleASM management tool, where the redundancy type is a double copy. Assume the disk group name + DATA. The HDDs of the disk group + DATA dual copies located on different CPU sides, i.e., CPU _0 and CPU _1 sides, are mirror groups of each other. That is, 2 disk groups are in mirror image relationship with each other, and the disk group under CPU _1 is set as the failover group of the disk group under CPU _0, so that it is ensured that the data of the data layer device is not affected by the failure of the cache layer of the HDD disk 2 and the nonvolatile memory 3.
Example two
As shown in fig. 3, the present embodiment provides a data reading and writing method based on the first embodiment, including the following steps:
when writing data, oracle data is firstly written on the nonvolatile memory 3 and then written back on the HDD disk 2 in a writeback mode;
oracle hotspot data is persistently cached in a nonvolatile memory 3;
when reading data, firstly detecting whether the data is on the nonvolatile memory 3, if so, directly reading the data from the nonvolatile memory 3, otherwise, continuously reading the data from the HDD magnetic disk 2.
Further, a read request time threshold and a write request time threshold may be set, and when writing data, when the IO time exceeds the write request time threshold, the data is directly written into the HDD disk 2 by bypassing the nonvolatile memory 3; when reading data, when the IO time exceeds the read request time threshold, the HDD disk 2 is directly read by bypassing the nonvolatile memory 3. All discrete reads and writes are guaranteed to pass through the nonvolatile memory 3, and the hit rate of the data blocks on the nonvolatile memory 3 is improved.
In a specific embodiment, the method is implemented based on parameters of a bcache kernel module, and specifically can adjust the following parameters:
setting a cache policy as a writeback policy, namely that a data stream firstly passes through the nonvolatile memory 3, and data in the nonvolatile memory 3 is asynchronously written into the HDD disk 2;
setting a sequential IO cache policy parameter sequential _ cutoff =0, which means that all sequential data blocks pass through a nonvolatile memory 3;
adjusting a cache write-back proportion parameter, writeback _ percentage, setting the proportion of reserving the hot spot data on the nonvolatile memory 3, and setting that 40% of the hot spot data is reserved on the nonvolatile memory 3 to the maximum extent according to requirements;
setting constrained _ read _ threshold _ us and constrained _ write _ threshold _ us parameters, wherein the default condition is that the read request threshold is 2ms, the write request threshold is 20ms, and if the IO time exceeds the threshold, the cache device of the nonvolatile memory 3 is bypassed, so that the problem of data caching is not caused, all discrete reads and writes are ensured to pass through the nonvolatile memory 3, and the hit rate of a data block on the nonvolatile memory 3 is improved.
The above disclosure is only for the preferred embodiments of the present invention, but the present invention is not limited thereto, and any non-inventive changes that can be made by those skilled in the art and several modifications and amendments made without departing from the principle of the present invention shall fall within the protection scope of the present invention.
Claims (7)
1. A device for improving the performance of Oracle database, the Oracle database is configured on the server, characterized in that,
the CPU of the server is connected with at least one HDD disk and at least one nonvolatile memory; the HDD disks correspond to the nonvolatile memories one by one, and the quantity of the HDD disks is the same;
an HDD disk and a nonvolatile memory are configured as a block device;
a memory block device is established on the nonvolatile memory, and the memory block device is configured as a cache device corresponding to the HDD;
the server is provided with Oracle ASM management software, all the block devices under each CPU form a group of disk groups, and data striping storage is carried out;
the server is provided with at least one pair of CPUs, and the number of the HDD disks connected with the two CPUs in each pair is the same; the two disk groups under each pair of CPUs are in a mirror image relationship with each other, and the two disk groups under each pair of CPUs are set as a failover group by the Oracle ASM management software;
the CPU is connected with the HDD magnetic disk through the SAS card, wherein the SAS card is connected to the PCIe slot of the CPU.
2. The apparatus according to claim 1, wherein the server is configured with a block device configuration module, the memory block device on the nonvolatile memory is configured as a cache device, and the HDD disk is set as a backend device, so that each HDD disk and the corresponding nonvolatile memory thereof are configured as each block device.
3. The apparatus for improving performance of an Oracle database according to claim 2, wherein the block device configuration module is a bcache cache management module.
4. The apparatus according to claim 3, wherein the server is configured with a memory block device creation module, and sets the nonvolatile memory to a region mode, and creates a region of the nonvolatile memory as a memory block device.
5. The apparatus for improving performance of an Oracle database according to claim 4, wherein the memory block device creation module includes an ipmctl tool and an ndctl tool;
the ipmctl tool sets the nonvolatile memory to be in a region mode; the ndctl tool creates a region of non-volatile memory as a memory block device.
6. A data reading and writing method based on the device of any one of claims 1-5, characterized by comprising the following steps:
when writing data, oracle data is firstly written on the nonvolatile memory and then written back on the HDD disk in a writeback mode;
caching Oracle hotspot data in a nonvolatile memory persistently;
when reading data, firstly detecting whether the data is on the nonvolatile memory, if so, directly reading the data from the nonvolatile memory, otherwise, continuously reading the data from the HDD magnetic disk.
7. A method for reading and writing data according to claim 6, further comprising the steps of:
setting a read request time threshold and a write request time threshold;
during data writing, when the IO time exceeds a write request time threshold, bypassing the nonvolatile memory and directly writing the data into the HDD disk;
when reading data, when the IO time exceeds the read request time threshold, bypassing the nonvolatile memory and directly reading the HDD disk.
Priority Applications (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
CN202010250410.7A CN111475112B (en) | 2020-04-01 | 2020-04-01 | Device for improving performance of Oracle database and data reading and writing method |
Applications Claiming Priority (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
CN202010250410.7A CN111475112B (en) | 2020-04-01 | 2020-04-01 | Device for improving performance of Oracle database and data reading and writing method |
Publications (2)
Publication Number | Publication Date |
---|---|
CN111475112A CN111475112A (en) | 2020-07-31 |
CN111475112B true CN111475112B (en) | 2023-03-14 |
Family
ID=71750396
Family Applications (1)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
CN202010250410.7A Active CN111475112B (en) | 2020-04-01 | 2020-04-01 | Device for improving performance of Oracle database and data reading and writing method |
Country Status (1)
Country | Link |
---|---|
CN (1) | CN111475112B (en) |
Families Citing this family (1)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN115933994B (en) * | 2023-01-09 | 2023-07-14 | 苏州浪潮智能科技有限公司 | Data processing method and device, electronic equipment and storage medium |
Citations (2)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN110007870A (en) * | 2019-04-12 | 2019-07-12 | 苏州浪潮智能科技有限公司 | A kind of storage equipment write request processing method and relevant apparatus |
CN110502188A (en) * | 2019-08-01 | 2019-11-26 | 苏州浪潮智能科技有限公司 | A kind of date storage method and device based on data base read-write performance |
-
2020
- 2020-04-01 CN CN202010250410.7A patent/CN111475112B/en active Active
Patent Citations (2)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN110007870A (en) * | 2019-04-12 | 2019-07-12 | 苏州浪潮智能科技有限公司 | A kind of storage equipment write request processing method and relevant apparatus |
CN110502188A (en) * | 2019-08-01 | 2019-11-26 | 苏州浪潮智能科技有限公司 | A kind of date storage method and device based on data base read-write performance |
Also Published As
Publication number | Publication date |
---|---|
CN111475112A (en) | 2020-07-31 |
Similar Documents
Publication | Publication Date | Title |
---|---|---|
US10152254B1 (en) | Distributing mapped raid disk extents when proactively copying from an EOL disk | |
US9442802B2 (en) | Data access methods and storage subsystems thereof | |
US7730257B2 (en) | Method and computer program product to increase I/O write performance in a redundant array | |
US8024516B2 (en) | Storage apparatus and data management method in the storage apparatus | |
US7975168B2 (en) | Storage system executing parallel correction write | |
US5586291A (en) | Disk controller with volatile and non-volatile cache memories | |
US6467022B1 (en) | Extending adapter memory with solid state disks in JBOD and RAID environments | |
US9146688B2 (en) | Advanced groomer for storage array | |
US7831764B2 (en) | Storage system having plural flash memory drives and method for controlling data storage | |
JP3697149B2 (en) | How to manage cache memory | |
US8307159B2 (en) | System and method for providing performance-enhanced rebuild of a solid-state drive (SSD) in a solid-state drive hard disk drive (SSD HDD) redundant array of inexpensive disks 1 (RAID 1) pair | |
US20100100664A1 (en) | Storage system | |
US20090210620A1 (en) | Method to handle demand based dynamic cache allocation between SSD and RAID cache | |
US20100274964A1 (en) | Storage system for controlling disk cache | |
US6961818B1 (en) | Method, system and computer program product for managing data in a mirrored cache using an access balancing technique | |
JP2013156977A (en) | Elastic cache of redundant cache data | |
WO2011061801A1 (en) | Computer system and load equalization control method for the same | |
US8762636B2 (en) | Data storage system having a global cache memory distributed among non-volatile memories within system disk drives | |
WO2021242317A1 (en) | Zns parity swapping to dram | |
US9921913B2 (en) | Flushing host cache data before rebuilding degraded redundant virtual disk | |
US10152242B1 (en) | Host based hints | |
US10031689B2 (en) | Stream management for storage devices | |
CN111475112B (en) | Device for improving performance of Oracle database and data reading and writing method | |
CN111857540A (en) | Data access method, device and computer program product | |
JP2015052853A (en) | Storage controller, storage control method, and program |
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 |