CN111475112B - Device for improving performance of Oracle database and data reading and writing method - Google Patents

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

A device and data reading and writing method for improving Oracle database performance

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 method for reading and writing database data.

Background technique

Oracle database (also known as OracleRDBMS, is a relational database management system of Oracle Corporation) system is suitable for all kinds of large, medium, small and microcomputer environments due to its good portability, convenient use, and powerful functions, and has become a popular relational database management system. database management system. For OLTP-type business systems, the throughput (pressure-bearing capacity) of a system is closely related to the CPU consumption of requests, external interfaces, IO, and so on. The higher the CPU consumption of a single reqeust, the slower the external system interface and IO response speed, the lower the system throughput, and vice versa. With the improvement of CPU performance, the biggest performance bottleneck of Oracle database is IO performance. Only using HDD disks will seriously reduce the performance of the database. In order to solve this problem, the industry generally uses all-flash disk (SSD disk array) arrays to improve the Oracle database storage IO performance. As the amount of business data increases, it is forced to choose Larger-capacity SASSSD or NVMeSSD, followed by high cost and excess performance.

Contents of the invention

In order to solve the above problems, the present invention provides a device and a data reading and writing method for improving the performance of an Oracle database. Based on the non-volatile memory, a plurality of block devices of the non-volatile memory and HDD disks are constructed to improve the IO performance of the Oracle database.

The technical solution of the present invention is: a device for improving the performance of the Oracle database, the Oracle database is configured on the server,

At least one HDD disk and at least one non-volatile memory are connected to the CPU of the server; HDD disks correspond to the non-volatile memory one by one, and the number is the same;

An HDD disk and a non-volatile memory are configured as a block device;

A memory block device is created on the non-volatile 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, and the memory block device on the non-volatile memory is configured as a cache device, and the HDD disk is set as a back-end device, so that each HDD disk and its corresponding non-volatile memory Configured as individual block devices.

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 non-volatile memory is set to a region mode, and the region of the non-volatile memory is created as a memory block device.

Further, the memory block device creation module includes ipmctl tool and ndctl tool;

The ipmctl tool sets the non-volatile memory to the region mode; the ndctl tool creates the region of the non-volatile memory as a memory block device.

Furthermore, Oracle ASM management software is configured on the server, and all block devices are formed into a set of disk groups for data stripe storage.

Furthermore, at least one pair of CPUs is set on the server, and the two CPUs in each pair are connected to the same number of HDD disks; the Oracle ASM management software sets the two disk groups under each pair of CPUs as a failover group.

Further, the CPU is connected to the HDD disk through the SAS card, wherein the SAS card is connected to the PCIe slot of the CPU.

The technical solution of the present invention also includes a data reading and writing method based on the above-mentioned device, comprising the following steps:

When writing data, Oracle data is first written to the non-volatile memory, and then written back to the HDD disk in writeback mode;

Persistently cache Oracle hotspot data in non-volatile memory;

When reading data, first check whether the data is in the non-volatile memory, if so, read the data directly from the non-volatile memory, otherwise continue to read the data from the HDD disk.

Further, steps are also included:

Set the read request time threshold and write request time threshold;

When writing data, when the IO time exceeds the write request time threshold, bypass the non-volatile memory and directly write to the HDD disk;

When reading data, when the IO time exceeds the read request time threshold, the non-volatile memory is bypassed and the HDD disk is read directly.

The device and data reading and writing method for improving the performance of the Oracle database provided by the present invention are configured with the same number of non-volatile memory and HDD disks under the processor, and are constructed as multiple block devices of non-volatile memory and HDD disks. When writing data, Oracle data is first written to the non-volatile memory, and then written back to the HDD disk in writeback mode. When reading data, first check whether the data is in the non-volatile memory. Read data from the memory, otherwise continue to read data from the HDD disk, effectively improving the read and write capabilities of the database. In addition, the HDD of the present invention does not need to form a RAID array through a RAID disk array card, and only needs a single disk to be used, which reduces the performance loss caused by the RAID disk array verification; the failure of the non-volatile memory stick and the HDD disk does not affect the system Normal operation; the non-volatile memory and the HDD disk are under the same processor, and the data processing does not need to cross the CPU; this device can be used for any Oracle database all-in-one machine or Oracle database; and compared with the use of large-capacity all-flash Disk arrays greatly reduce server costs.

Description of drawings

Fig. 1 is a schematic structural diagram of Embodiment 1 of the present invention.

FIG. 2 is a schematic architecture diagram of a specific implementation manner of Embodiment 1 of the present invention.

Fig. 3 is a schematic flow chart of the method in Embodiment 2 of the present invention.

Detailed ways

The present invention will be described in detail below with reference to the accompanying drawings and specific embodiments. The following embodiments are explanations of the present invention, but the present invention is not limited to the following embodiments.

As shown in Figure 1, the present embodiment provides a device for improving the performance of the Oracle database, the Oracle database is configured on the server, in order to realize the device, at least one HDD disk 2 and at least one non-volatile memory 3 are connected to the CPU1 of the server ; The HDD disk 2 corresponds to the non-volatile memory 3 one by one, and the numbers are the same. A HDD disk 2 and a non-volatile memory 3 are configured as a block device 5 , wherein a memory block device 4 is created on the non-volatile 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 CPU1 is connected to HDD disk 2 through a SAS card, wherein the SAS card is connected to the PCIe slot of CPU1.

In this embodiment, the block device 5 configuration module is configured on the server, the memory block device 4 on the non-volatile memory 3 is configured as a cache device, and the HDD disk is set as a back-end device, so that each HDD disk and its corresponding The non-volatile memory is configured as individual block devices 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 non-volatile memory 3 is set to a region (region) mode, and the region of the non-volatile memory 3 is created as a memory block device 4. Specifically, the memory block device 4 creation module includes the ipmctl tool (Intel persistent memory control tool) and the ndctl tool (non-volatile memory devices non-volatile memory control tool); the ipmctl tool sets the non-volatile memory 3 to the region mode ; The ndctl tool creates a region of non-volatile memory 3 as a memory block device 4.

The server shown in Figure 2 is an example. The server has two CPUs. A SAS card is connected to each CPU of the server. Each SAS card is connected to 1-6 HDD disks 2. Each CPU is configured with HDD disks 2. An equal amount of non-volatile memory 3 devices.

Install the ipmctl and ndctl tools under the system to configure each non-volatile memory 3 device as a memory block device 4 (pmem). If the number of non-volatile memory devices is 12, create a system named pmem0 and pmem1 under the system ... memory block device 4 for pmem11.

Create steps:

Use ipmctl to set the type of each non-volatile memory 3 of the server to each non-volatile memory 3 as a region mode. If there are 12 non-volatile memories, region0 to region11 will be generated correspondingly: ipmctlcreate –goal PersistentMemoryType=AppDirectNotInterleaved

Use the ndctl tool to create a region of non-volatile memory as a pmem device:

ndctl create-namespace -region=region0

Install and activate the kernel bcache cache management module under the system. Through the management command of the bcache module, set each memory block device 4 as a front-end cache (caching) device, and each HDD disk array as a back-end (backing) device. Finally, block devices 5 such as bcache0, bcache1, bcache2, and bcache3 are generated.

Assuming that each CPU1 is configured with 6 HDD disks 2 and 6 non-volatile memory 3, then it is necessary to create a block (bcache) device 5 named bcache0~6 under CPU_0, and create a block (bcache) device 5 named bcache7~12 under CPU_1 Block (bcache) device5.

The command is as follows:

make-bcache --cache_replacement_policy=lru -C /dev/pmem0 -B /dev/sdb

Among them, --cache_replacement_policy is the cache algorithm, and lru is the algorithm that has not been used for the longest time, that is, the hot data is cached on the non-volatile memory 3.

The -C parameter specifies the non-volatile memory 3 device as a cache device,

The -B parameter specifies HDD disk 2 as the back-end storage device.

In this embodiment, Oracle ASM (Automatic Storage Management) management software is configured on the server, and all block devices 5 are formed into a set of disk groups for data striped storage. Evenly distribute Oracle data to multiple generated block devices5.

At the same time, this embodiment also uses the ASM multi-copy mode to solve the problem of fault tolerance of the non-volatile memory 3 and the HDD disk 2 . Specifically, at least one pair of CPU 1 is set on the server, and the two CPUs in each pair are connected to the same number of HDD disks 2 . The OracleASM management software sets the two disk groups under each pair of CPUs as a failover group.

Take the server connected to two CPUs as shown in Figure 2 as an example. A corresponding HDD disk 2 and a non-volatile memory 3 under each CPU1 form a piece of equipment 5. Through the OracleASM management tool, all the equipment under each CPU1 5 form a set of disk groups, and the redundancy type is double copy. Assume the disk group is named +DATA. The double copies of the disk group + DATA are located on different CPU sides, that is, the HDDs on the CPU_0 and CPU_1 sides are mirror groups of each other. That is, the two disk groups are mirror images of each other, and the disk group under CPU_1 is set as the failover group of the disk group under CPU_0 to ensure that failures of HDD disk 2 and non-volatile memory 3 at the cache layer do not affect the data of the data layer device.

Embodiment two

As shown in Figure 3, this embodiment provides a method for reading and writing data based on Embodiment 1, including the following steps:

When writing data, Oracle data is first written to non-volatile memory 3, and then written back to HDD disk 2 in writeback mode;

Persistently cache Oracle hotspot data on non-volatile memory 3;

When reading data, first check whether the data is on the non-volatile memory 3, if yes, then directly read the data from the non-volatile memory 3, otherwise continue to read data from the HDD disk 2.

Further, the read request time threshold and the write request time threshold can be set. When writing data, when the IO time exceeds the write request time threshold, the non-volatile memory 3 is bypassed and the HDD disk 2 is directly written; when the data is read, when When the IO time exceeds the read request time threshold, the non-volatile memory 3 is bypassed and the HDD disk 2 is read directly. Ensure that all discrete reads and writes will pass through the non-volatile memory 3, and improve the hit rate of data blocks on the non-volatile memory 3.

In a specific embodiment, the method is realized based on the parameters of the bcache kernel module, and the following parameters can be specifically adjusted:

Set the cache strategy to the writeback strategy, that is, the data flow first passes through the non-volatile memory 3, and the data on the non-volatile memory 3 is asynchronously written to the HDD disk 2;

Set the sequential IO cache policy parameter sequential_cutoff=0, which means that all sequential data blocks must pass through non-volatile memory 3;

Adjust the cache writeback ratio parameter writeback_percent, set the ratio of hot data reserved on non-volatile memory 3, and set a maximum of 40% of hot data reserved on non-volatile memory 3 according to needs;

Set the congested_read_threshold_us and congested_write_threshold_us parameters. By default, when the read request threshold is 2ms and the write request threshold is 20ms, if the IO time exceeds the threshold, the non-volatile memory 3 cache device will be bypassed, so as not to cause data cachemissing problems, ensuring all All discrete reads and writes will pass through the non-volatile memory 3, improving the hit rate of data blocks on the non-volatile memory 3.

The above disclosure is only a preferred embodiment of the present invention, but the present invention is not limited thereto, any non-creative changes that those skilled in the art can think of, and some improvements and modifications made without departing from the principle of the present invention , should 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.

Images