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 PDF

Info

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
Application number
CN202010250410.7A
Other languages
Chinese (zh)
Other versions
CN111475112A (en
Inventor
张一可
段利宁
张远斌
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Suzhou Inspur Intelligent Technology Co Ltd
Original Assignee
Suzhou Inspur Intelligent Technology Co Ltd
Priority date (The priority date 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 date listed.)
Filing date
Publication date
Application filed by Suzhou Inspur Intelligent Technology Co Ltd filed Critical Suzhou Inspur Intelligent Technology Co Ltd
Priority to CN202010250410.7A priority Critical patent/CN111475112B/en
Publication of CN111475112A publication Critical patent/CN111475112A/en
Application granted granted Critical
Publication of CN111475112B publication Critical patent/CN111475112B/en
Active legal-status Critical Current
Anticipated expiration legal-status Critical

Links

Images

Classifications

    • GPHYSICS
    • G06COMPUTING; CALCULATING OR COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F3/00Input 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/06Digital input from, or digital output to, record carriers, e.g. RAID, emulated record carriers or networked record carriers
    • G06F3/0601Interfaces specially adapted for storage systems
    • G06F3/0602Interfaces specially adapted for storage systems specifically adapted to achieve a particular effect
    • G06F3/061Improving I/O performance
    • GPHYSICS
    • G06COMPUTING; CALCULATING OR COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/21Design, administration or maintenance of databases
    • G06F16/217Database tuning
    • GPHYSICS
    • G06COMPUTING; CALCULATING OR COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F3/00Input 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/06Digital input from, or digital output to, record carriers, e.g. RAID, emulated record carriers or networked record carriers
    • G06F3/0601Interfaces specially adapted for storage systems
    • G06F3/0628Interfaces specially adapted for storage systems making use of a particular technique
    • G06F3/0629Configuration or reconfiguration of storage systems
    • G06F3/0631Configuration or reconfiguration of storage systems by allocating resources to storage systems
    • GPHYSICS
    • G06COMPUTING; CALCULATING OR COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F3/00Input 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/06Digital input from, or digital output to, record carriers, e.g. RAID, emulated record carriers or networked record carriers
    • G06F3/0601Interfaces specially adapted for storage systems
    • G06F3/0668Interfaces specially adapted for storage systems adopting a particular infrastructure
    • G06F3/0671In-line storage system
    • G06F3/0683Plurality of storage devices
    • G06F3/0688Non-volatile semiconductor memory arrays
    • GPHYSICS
    • G06COMPUTING; CALCULATING OR COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F3/00Input 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/06Digital input from, or digital output to, record carriers, e.g. RAID, emulated record carriers or networked record carriers
    • G06F3/0601Interfaces specially adapted for storage systems
    • G06F3/0668Interfaces specially adapted for storage systems adopting a particular infrastructure
    • G06F3/0671In-line storage system
    • G06F3/0683Plurality of storage devices
    • G06F3/0689Disk 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

Device for improving performance of Oracle database and data reading and writing method
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.
CN202010250410.7A 2020-04-01 2020-04-01 Device for improving performance of Oracle database and data reading and writing method Active CN111475112B (en)

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)

* Cited by examiner, † Cited by third party
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)

* Cited by examiner, † Cited by third party
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

Patent Citations (2)

* Cited by examiner, † Cited by third party
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