CN111475112A - 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
CN111475112A
CN111475112A CN202010250410.7A CN202010250410A CN111475112A CN 111475112 A CN111475112 A CN 111475112A CN 202010250410 A CN202010250410 A CN 202010250410A CN 111475112 A CN111475112 A CN 111475112A
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.)
Granted
Application number
CN202010250410.7A
Other languages
Chinese (zh)
Other versions
CN111475112B (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

Landscapes

  • Engineering & Computer Science (AREA)
  • Theoretical Computer Science (AREA)
  • Physics & Mathematics (AREA)
  • General Engineering & Computer Science (AREA)
  • General Physics & Mathematics (AREA)
  • Human Computer Interaction (AREA)
  • Databases & Information Systems (AREA)
  • Data Mining & Analysis (AREA)
  • Information Retrieval, Db Structures And Fs Structures Therefor (AREA)

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
The Oracle database (named Oracle RDBMS, a relational database management system of Oracle corporation) system is a popular relational database management system at present due to good portability, convenient use and strong function, and is suitable for various large, medium, small and microcomputer environments.
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;
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.
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 a 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 failover groups.
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 on the nonvolatile memory and then written back on the HDD disk in a writeback mode;
caching Oracle hotspot data on 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 are in failure; 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 the performance of an Oracle database, where the Oracle database is configured on a server, and in order 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. Note that the CPU1 is connected to the HDD disk 2 via an SAS card 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 cache management module.
A memory block device 4 creation module is configured on the server, the nonvolatile memory 3 is set to be in 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 and an ndctl 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 non-volatile memory 3 device as a memory block device 4(pmem), and if the number of non-volatile memory devices is 12, then creating memory block devices 4 named pmem0, pmem1 … 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 region 11 are generated correspondingly: ipmctrate-good persistence MemoryType ═ AppDirectNotInterl improved
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, and 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 a bcache0, a bcache1, a bcache2, a 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 ~ 6 under CPU _0 and create block (bcache) devices 5 named bcache7 ~ 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 HDD disks 2 connected to two CPUs in each pair is the same. The OracleASM management software sets the two disk groups under each pair of CPUs as failover groups.
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 set of disk groups through an Oracle ASM 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;
caching Oracle hotspot data on a nonvolatile memory 3 persistently;
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 data is written and the IO time exceeds the write request time threshold, the data bypasses the nonvolatile memory 3 and is directly written into the HDD disk 2; 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 block on the nonvolatile memory 3 is improved.
In the specific embodiment, the method is implemented based on parameters of a bcache kernel module, and specifically the following parameters can be adjusted:
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 to 0, which means that all sequential data blocks need to pass through the 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 cache missing is avoided, all discrete reads and writes can be 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 (10)

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 created on the nonvolatile memory, and the memory block device is configured as a cache device corresponding to the HDD disk.
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 Oracle database as claimed in 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. The apparatus for improving performance of an Oracle database according to any one of claims 1 to 5, wherein the server is configured with Oracle ASM management software, and all the block devices are grouped into a set of disk groups for data striping storage.
7. The apparatus for improving performance of an Oracle database according to claim 6, wherein the server is provided with at least one pair of CPUs, and the number of HDD disks connected to two CPUs in each pair is the same; the Oracle ASM management software sets the two disk groups under each pair of CPUs as failover groups.
8. The apparatus for improving performance of Oracle database according to any one of claims 1-5, 7, wherein the CPU is connected to the HDD disk through an SAS card, wherein the SAS card is connected to a PCIe slot of the CPU.
9. A data read-write method based on any one of the devices 1-8 is 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 on 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.
10. A method for reading from and writing to data according to claim 9, 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 true CN111475112A (en) 2020-07-31
CN111475112B 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)

Cited By (1)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN115933994A (en) * 2023-01-09 2023-04-07 苏州浪潮智能科技有限公司 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

Cited By (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN115933994A (en) * 2023-01-09 2023-04-07 苏州浪潮智能科技有限公司 Data processing method and device, electronic equipment and storage medium
CN115933994B (en) * 2023-01-09 2023-07-14 苏州浪潮智能科技有限公司 Data processing method and device, electronic equipment and storage medium

Also Published As

Publication number Publication date
CN111475112B (en) 2023-03-14

Similar Documents

Publication Publication Date Title
US7730257B2 (en) Method and computer program product to increase I/O write performance in a redundant array
US7975168B2 (en) Storage system executing parallel correction write
US9442802B2 (en) Data access methods and storage subsystems thereof
US8024516B2 (en) Storage apparatus and data management method in the storage apparatus
US5586291A (en) Disk controller with volatile and non-volatile cache memories
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
US7831764B2 (en) Storage system having plural flash memory drives and method for controlling data storage
US5883909A (en) Method and apparatus for reducing data transfers across a memory bus of a disk array controller
JP3697149B2 (en) How to manage cache memory
US6243795B1 (en) Redundant, asymmetrically parallel disk cache for a data storage system
US6604171B1 (en) Managing a cache memory
US6961818B1 (en) Method, system and computer program product for managing data in a mirrored cache using an access balancing technique
US8862819B2 (en) Log structure array
JP2013156977A (en) Elastic cache of redundant cache data
US8195877B2 (en) Changing the redundancy protection for data associated with a file
TW201107981A (en) Method and apparatus for protecting the integrity of cached data in a direct-attached storage (DAS) system
US20080147970A1 (en) Data storage system having a global cache memory distributed among non-volatile memories within system disk drives
US8938641B2 (en) Method and apparatus for synchronizing storage volumes
CN106469119B (en) Data writing caching method and device based on NVDIMM
US10031689B2 (en) Stream management for storage devices
US7130973B1 (en) Method and apparatus to restore data redundancy and utilize spare storage spaces
JP2012507784A (en) Independent disk redundant array (RAID) write cache subassembly
CN111475112B (en) Device for improving performance of Oracle database and data reading and writing method
US6611897B2 (en) Method and apparatus for implementing redundancy on data stored in a disk array subsystem based on use frequency or importance of the data
US7143234B2 (en) Bios storage array

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