PostgreSQL Introduction and performance improvement of PCIe SSD

In the relational database area, PostgreSQL is a very popular open source database software. Since its release in 1996, it has accumulated nearly 20 years of practical experience, both in PostgreSQL itself and in community ecology. Not only small and medium-sized enterprises, many large industry customers will also use PostgreSQL to build their own database system. This article focuses on PostgreSQL and compares the SAS disks in the PCIe SSD (this article uses Memblaze's PBlaze4 PCIe SSD) and hardware RAID aggregation. The performance of online transaction processing (OLTP) for the PostgreSQL database.

PostgreSQL and Parameter configuration

The latest PostgreSQL is written based on the SQL2011 standard, enabling acid, multi-version concurrency control, complete serialization, and complex statement queries. In addition, as an open source database, PostgreSQL also has many third-party organizations to provide plug-ins, in terms of expansion, database migration, etc., PostgreSQL also has a great advantage.

For operating system 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 higher version). The main Linux version also has a corresponding PostgreSQL support package.

Before testing, we put the parameter full_page_write=on,postgresql the server writes the entire page to the WAL when the first write to the page is after the checkpoint. This is done because when the page is written to non-volatile media and an operating system crash occurs, only some pages may be written to disk, resulting in a mix of old and new data in the same page. If Full_page_writes is turned off, the page cannot be fully recovered due to incomplete row changes stored in the Wal during the post-crash recovery. Storing the full page content ensures that the page can be restored correctly, but the disadvantage is that it increases the amount of data that must be written to the Wal, and more read and write operations.

Test environment Introduction

Benchmarksql is an open source application that uses Java-developed TPCC benchmarks to meet the requirements of the TPCC Industry Benchmarking Association. The benchmarksql in this test set up 1000 warehouses with simulation operators and simulated 5 businesses, namely new orders, payments, order status, shipping and inventory.

The overall test environment is as follows:

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

storage Devices: 1 x Memblaze 1.6T PBlaze4 PCIe SSD

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

LSI SAS-3 3008 RAID Fury

Test Tools: Benchmarksql 4.1.1,postgresql's tpc-c test tool

Pgcluu 2.4,postgresql performance monitoring and auditing tools

Software: PostgreSQL 9.2.14

CentOS 7.1

Test Prerequisites

Before testing, you need to consider the following actions:

1. Ensure that the PBLAZE4 partition has 4KiB alignment and the partition offset starts at 1MiB.

2. Open the Irqbalance service and set the CPU usage mode to maximum performance mode.

3. Adjust the operating system kernel parameters to match PostgreSQL. Avoid using the OS Swap feature and adjust shared memory parameters/File system dirty page parameters. As shown below:

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 using XFS, the default block size for PostgreSQL is 8KiB, but in the current Linux environment, running "Mount XFS" will eject the "feature not implemented" error, so the block size should be adjusted to 4KiB. An increase in the number of allocation groups means that more parallel blocks and index nodes can be allocated. The NVME device partition size is 1601GB and the agsize=2g is set. Set inode64/nobarrier/nolargeio/allocsize=16m as the Mount option.

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

6. Configure the 1000 warehouse in the Benchmarksql tool and set up 128 terminals.

7. Preprocess the PBlaze4 and ensure that the file system has less than 50% usable capacity.

Test results

Figure 1,iops and bandwidth performance under PBlaze4 PCIe SSD and RAID HDD

Figure 1 clearly shows the IOPS and data throughput traffic in the case of different storage media. Comparing the test results, the sum of PBlaze4 PCIe SSD Read and write ioPS is approximately 10 times times the sum of Read and write ioPS of the raid HDD, and the PBlaze4 PCIe SSD read and write throughput is approximately 8 times times the sum of the read and write throughput of the raid HDD.

Figure 2, read-write average response time in different storage media states

As shown in 2, the PBlaze4 PCIe SSD maintains a consistent response time throughout the testing process.

From the above results, Memblaze PBlaze4 PCIe SSD has performed well in the online transaction processing test of PostgreSQL database.

Figure 3,pblaze4 PCIe SSD power consumption and temperature during testing

3, both power and temperature indicators demonstrate a stable delivery status for the PBlaze4 PCIe SSD.

Figure 4, online transaction processing total transactions and new order metrics

As shown in 4, the total transactions per minute (TPMC) consists of 5 transaction models. Test results running on PBlaze4 PCIe SSDs are much higher than those based on raid HDD.

Conclusion

This test clearly shows the results of the excellent online transaction tests running on the Memblaze PBlaze4 PCIe SSD, and the power and temperature performance of the PBlaze4 PCIe SSD is stable during the testing process. For a simple, TIB-type database system, using the Memblaze PBlaze4 PCIe SSD is a viable alternative to traditional dedicated storage systems to improve performance while significantly reducing TCO.

Author of this article

Wuhau, Memblaze Product Department AE. Long-term storage-related system integration work, the main research interests include the main research of logical volumes, traditional storage and relational database IO path optimization.

PostgreSQL Introduction and performance improvement of PCIe SSD