Can't get Dell PE T420 (Perc H710) perform better than a MacMini with PostgreSQL

It's months that I'm trying to solve a performance issue with PostgreSQL.

SYSTEM CONFIGURATION

Our deployment machine is a Dell PowerEdge T420 with a Perc H710 RAID controller configured in this way:

• VD0: two 15k SAS disks (ext4, OS partition, WAL partition, RAID1)

• VD1: ten 10k SAS disks (XFS, Postgres data partition, RAID5)

This system has the following configuration:

• Ubuntu 14.04.2 LTS (GNU/Linux 3.13.0-48-generic x86_64)

• 128GB RAM (DDR3, 8x16GB @1600Mhz)

• two Intel Xeon E5-2640 v2 @2Ghz

• Dell Perc H710 with 512MB RAM (Write cache: "WriteBack", Read cache: "ReadAhead", Disk cache: "disabled"):

• VD0 (OS and WAL partition): two 15k SAS disks (ext4, RAID1)

• VD1 (Postgres data partition): ten 10k SAS disks (XFS, RAID5)

• PostgreSQL 9.4 (updated to the latest available version)

• moved pg_stat_tmp to RAM disk

My personal low cost and low profile development machine is a MacMini configured in this way:

• OS X Server 10.7.5

• 8GB RAM (DDR3, 2x4GB @1333Mhz)

• one Intel i7 @2.2Ghz

• two Internal 500GB 7.2k SAS HDD (non RAID) for OS partition

• external Promise Pegasus R1 connected with Thunderbolt v1 (512MB RAM, four 1TB 7.2k SAS HDD 32MB cache, RAID5, Write cache: "WriteBack", Read cache: "ReadAhead", Disk cache: "enabled", NCQ: "enabled")

• PostgreSQL 9.0.13 (the original built-in shipped with OS X Server)

• moved pg_stat_tmp to RAM disk

So far I've made a lot of tuning adjustments to both machines, including kernel reccomended ones on the official Postgres doc site.

APPLICATION

The deployment machine runs a web platform which instructs Postgres to make big transactions over billion of records. It's a platform designed for one user because system resources have to be dedicated as much as possible to one single job due to data size (I don't like to call it big data because big data are in the order ob ten of billion).

ISSUEs

I've found the deployment machine to be a lot slower than the development machine. This is paradoxal because th

After asking on pgsql-performance list, Jeff Janes figured out that the cause was associated to the default collation used by Postgres (see this link for more informations). MacMini was using the much performing collation while Dell T420 was using the en/US collation.

T420 (Postgres 9.4.1)

List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 pen       | pen      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

MacMini (Postgres 9.0.13)

List of databases
       Name        |   Owner    | Encoding | Collation | Ctype |    Access privileges    
-------------------+------------+----------+-----------+-------+-------------------------
 caldav            | caldav     | UTF8     | C         | C     | 
 collab            | collab     | UTF8     | C         | C     | 
 device_management | _devicemgr | UTF8     | C         | C     | 
 pen               | pen        | UTF8     | C         | C     | 
 postgres          | _postgres  | UTF8     | C         | C     | 
 roundcubemail     | roundcube  | UTF8     | C         | C     | 
 template0         | _postgres  | UTF8     | C         | C     | =c/_postgres           +
                   |            |          |           |       | _postgres=CTc/_postgres
 template1         | _postgres  | UTF8     | C         | C     | =c/_postgres           +
                   |            |          |           |       | _postgres=CTc/_postgres
(8 rows)

After setting collation on T420 "C" the A transaction went from 195 seconds to 33 seconds against 40 seconds on Mac Mini; B type transaction went from 141 seconds to 78 seconds against 101 seconds on Mac Mini.
This is the best performance improvement after modifing BIOS settings. Many kernel adjustments didn't provide significant improvements.

So, running the following command will initialize a new database with collation C and encoding UTF8:

/usr/local/pgsql/bin/initdb -D /path/to/your/data --no-locale --encoding=UTF8

I hope this post will help other people in the future. After reading about a lot of digressions about kernel, virtual memory, RAID controllers, disk cache, WAL and other tech stuff I never found someone talking about collations.

StackExchange Database Administrators Q#96444, answer score: 2