[Pin to top] PostgreSQL introduction and analysis on Performance Improvement of PCIeSSD

In the relational database field, PostgreSQL is a very popular open source database software. Since its release in 1996, it has accumulated nearly 20 years of practical experience. Both PostgreSQL and the community ecosystem are very mature. Not only small and medium-sized enterprises, but many large industry customers will also use PostgreSQL to build their own database systems. This article

In the relational database field, PostgreSQL is a very popular open source database software. Since its release in 1996, it has accumulated nearly 20 years of practical experience. Both PostgreSQL and the community ecosystem are very mature. Not only small and medium-sized enterprises, but many large industry customers will also use PostgreSQL to build their own database systems. This article

In the relational database field, PostgreSQL is a very popular open source database software. Since its release in 1996, it has accumulated nearly 20 years of practical experience. Both PostgreSQL and the community ecosystem are very mature. Not only small and medium-sized enterprises, but many large industry customers will also use PostgreSQL to build their own database systems. This article mainly introduces PostgreSQL and compares the PCIe SSD (the PBlaze4 PCIe SSD of memblze is used in this article) and the SAS disk of hardware RAID aggregation, the performance of online transaction processing (OLTP) for PostgreSQL databases.

PostgreSQL and parameter configuration

The latest PostgreSQL is written based on the SQL2011 standard to implement ACID, multi-version concurrency control, full serial, and complex statement query functions. As an open-source database, PostgreSQL also has many plug-ins provided by third-party organizations. PostgreSQL also has great advantages in terms of expansion and database migration.

For OS support, PostgreSQL supports Linux, FreeBSD, OS X, Solaris, and Windows. In OS x, PostgreSQL is already the default database software (Mac OS X 10.7 Lion server or a later version ). Mainstream Linux versions also have corresponding PostgreSQL support packages.

Before the test, we will set the parameter full_page_write = on. the PostgreSQL server writes the entire page to WAL when writing the page for the first time after the checkpoint. This is because when the page is written to non-Easy loss media and the operating system crashes, some pages may only be written to the disk, resulting in a mix of New and Old data on the same page. If full_page_writes is disabled, the page cannot be completely restored due to incomplete information about the row changes stored in the WAL during crash recovery. The storage of the complete page content can ensure that the page can be correctly restored, but the disadvantage is that it increases the amount of data that must be written to WAL and more read/write operations.

Test Environment

BenchmarkSQL is an open-source TPCC benchmark application developed using Java. It meets the requirements of the TPCC industry benchmark Association. In this test, BenchmarkSQL Sets Up 1000 warehouses by simulating the operator, and simulates 5 businesses, including new orders, payment, order status, delivery, and inventory.

The overall test environment is as follows:

Server:Dell PowerEdge R730xd, 2 Intel XeonE5-2620 (6-core) v3 CPU, 32 GB DRAM

Storage device:1 x Memblaze 1.6 T PBlaze4 PCIe SSD

6*600 GB 15 K + 1*300 GB 15 K HDD

LSI SAS-3 3008 RAID Fury

Test tool:Benchmarksql 4.1.1, PostgreSQL TPC-C Testing Tool

Pgcluu 2.4, PostgreSQL performance monitoring and auditing tool

Software:PostgreSQL 9.2.14

CentOS 1, 7.1

Test prerequisites

Before testing, consider the following:

1. Make sure that the PBlaze4 partition is 4 kib aligned, and the partition offset starts from 1 MIB.

2. Enable the irqbalance service and set the CPU usage mode to the maximum performance mode.

3. Adjust the operating system kernel parameters to match PostgreSQL. Avoid using the OS switching function, and adjust the shared memory parameters/File System dirty page parameters. As follows:

Vm. swappiness = 0

Kernel. shmmax = 21474836480

Net. core. rmem_max = 4194304

Net. core. wmem_max = 4194304

Kernel. sem = 50100 64128000 50100 1280

Vm. dirty_background_bytes = 33554432

4. when XFS is used, PostgreSQL's default block size is 8KiB, but in the current Linux environment, running "mount xfs" will bring up the "function not implemented" error, therefore, the block size should be adjusted to 4KiB. The increase in the number of allocation groups means that more parallel blocks and index nodes can be allocated. The partition size of the nvme device is 1601 GB, And the agsize is set to 2 GB. Set inode64/nobarrier/nolargeio/allocsize = 16 m as the mounting option.

5. Set the PostgreSQL database group to use 3/4 of the server's physical memory, and set shared_buffer = 6 GB, Max connection = 600.

6. In the Benchmarksql tool, configure the 1000 repository and set up 128 terminals.

7. pre-process PBlaze4 and ensure that the available capacity of the file system is less than 50%.

Test Results

Performance of IOPS and bandwidth in PBlaze4 PCIe SSD and RAID HDD

Clearly shows the IOPS and data throughput traffic in different storage media. Compared with the test results, PBlaze4 PCIe SSD read/write IOPS is about 10 times the sum of raid hdd read/write IOPS, and PBlaze4 PCIe SSD read/write throughput is about 8 times the sum of raid hdd read/write throughput.

Average read/write Response Time for different storage media states

As shown in figure 2, PBlaze4 PCIe SSD maintains a stable response time throughout the test.

The above results show that memblze PBlaze4 PCIe SSD is outstanding in the online transaction processing test of the PostgreSQL database.

, PBlaze4 PCIe SSD power consumption and temperature during testing

As shown in 3, both power consumption and temperature indicators prove that PBlaze4 PCIe SSD has a stable delivery status.

, Total online transaction processing and new order indicators

4. The total number of transactions processed per minute (tpmC) includes five transaction models. The test results running on PBlaze4 PCIe SSD are much higher than those based on raid hdd.

Conclusion

This test clearly shows the excellent online transaction Test Results running on the memblze PBlaze4 PCIe SSD, and the power and temperature of the PBlaze4 PCIe SSD are stable during the test. For database systems with simple business types and TiB-level databases, using memblze PBlaze4 PCIe SSD is a feasible solution to replace traditional dedicated storage systems, improving performance while significantly reducing TCO.

Author

Wu Hao and AE of Memblaze product department. He has been engaged in storage-related system integration for a long time. His main research interests include logical volumes, traditional storage, and I/O Path Optimization of relational databases.