Postgres over NFS

[fahadshery]

Hi,

I have been thinking to setup a Postgres database. I was thinking to create a ZFS dataset with 16K block size and tune some other params in Postgres.
I wish to run Postgres on a separate Ubuntu VM and thinking to mount the Postgres dataset from TrueNas via NFS.
This database will be write intensive because my application will be downloading data via APIs and storing in Postgres.

Are there any gotchas or is it a bad idea?

Thank you!

 

[Patrick M. Hausen]

Running any database over NFS is a bad idea. Postgres runs great on FreeBSD - use a jail instead.

 

[jgreco]

Running any database over NFS is a bad idea. Postgres runs great on FreeBSD - use a jail instead.

This is exactly correct.

Postgres compiles cleanly and neatly on FreeBSD, and by using a jail on a FreeNAS host, you combine the NAS's large amounts of memory along with fastest possible disk I/O, and you will get a MUCH more performant SQL server running it on the FreeNAS host than you would get on a NFS-mounted SQL server. It would be prudent to follow common ZFS rules for databases, including adding extra memory for the SQL server itself (if you don't have a ton), preferring mirror vdevs, and keeping pool occupancy lower than maybe 60%. Lots of common performance issues shared with block storage.

The path to success for block storage

It seems like I haven't written a sticky for awhile, but just in the last week I've had to cover this topic several times. ZFS does two different things very well. One is storage of large sequentially-written files, such as archives, logs, or data files, where the file does not have the middle...

www.truenas.com

 

[fahadshery]

Thank you @Patrick M. Hausen and @jgreco for your advice. I have setup the Postgres in the Jail.

 

[fahadshery]

Hi again @Patrick M. Hausen and @jgreco

I started benchmarking Postgres using NFS and in TrueNas jail and results are baffling me!
These results show everything using NFS.
I then created the TrueNas jail as you advised and the results are not even 50% and shows 32 cores being hammered 100% during benchmarking.

I moved the iocage storage from stripped mirror with 4 x SSD's and then moved it to 4 x spindles stopped mirror just like these results but the results are just appalling.

I am using phoronix benchmarking.

Here is one comparison:

Code:

PostgreSQL pgbench 14.0:
    pts/pgbench-1.11.0 [Scaling Factor: 1 - Clients: 50 - Mode: Read Only]
    Test 1 of 16
    Estimated Trial Run Count:    3
    Estimated Test Run-Time:      18 Minutes
    Estimated Time To Completion: 4 Hours, 43 Minutes [03:48 UTC Jan 10]
        Started Run 1 @ 23:05:47
        Started Run 2 @ 23:08:06
        Started Run 3 @ 23:10:24

    Scaling Factor: 1 - Clients: 50 - Mode: Read Only:
        53732.387014
        53456.242004
        52870.585255

    Average: 53353 TPS
    Deviation: 0.82%

    TPS > Higher Is Better
    HDD Fast10 with zil, truenas jail ............................... 53353  |=================================================
    HDD Fast10 with zil, 24G RAM, Sync Enabled, Atime off ........... 95592  |========================================================================================
    HDD Fast10 with zil, 8G RAM, Sync Enabled, Atime off ............ 99523  |============================================================================================
    SSD Fast10, 24G RAM, Sync Enabled, Atime off .................... 105429 |=================================================================================================
    HDD Fast10 with zil, 8G RAM, Sync Enabled, Atime off, 16K block . 108053 |===================================================================================================
    SSD Fast10, 8G RAM, Sync Enabled, Atime off, 16K block .......... 108653 |====================================================================================================


    Scaling Factor: 1 - Clients: 50 - Mode: Read Only - Average Latency:
        0.931
        0.935
        0.946

    Average: 0.937 ms
    Deviation: 0.83%

    ms < Lower Is Better
    HDD Fast10 with zil, truenas jail ............................... 0.937 |=====================================================================================================
    HDD Fast10 with zil, 24G RAM, Sync Enabled, Atime off ........... 0.523 |========================================================
    HDD Fast10 with zil, 8G RAM, Sync Enabled, Atime off ............ 0.502 |======================================================
    SSD Fast10, 24G RAM, Sync Enabled, Atime off .................... 0.474 |===================================================
    HDD Fast10 with zil, 8G RAM, Sync Enabled, Atime off, 16K block . 0.463 |==================================================
    SSD Fast10, 8G RAM, Sync Enabled, Atime off, 16K block .......... 0.460 |==================================================

PostgreSQL pgbench 14.0:
    pts/pgbench-1.11.0 [Scaling Factor: 1 - Clients: 100 - Mode: Read Only]
    Test 3 of 16
    Estimated Trial Run Count:    3
    Estimated Test Run-Time:      7 Minutes
    Estimated Time To Completion: 1 Hour, 34 Minutes [00:46 UTC Jan 10]
        Started Run 1 @ 23:12:48
        Started Run 2 @ 23:15:06
        Started Run 3 @ 23:17:24

    Scaling Factor: 1 - Clients: 100 - Mode: Read Only:
        54879.777409
        53789.915171
        53090.815527

    Average: 53920 TPS
    Deviation: 1.67%

    TPS > Higher Is Better
    HDD Fast10 with zil, truenas jail ............................... 53920  |=================================================
    HDD Fast10 with zil, 24G RAM, Sync Enabled, Atime off ........... 102739 |=============================================================================================
    HDD Fast10 with zil, 8G RAM, Sync Enabled, Atime off ............ 105109 |===============================================================================================
    SSD Fast10, 24G RAM, Sync Enabled, Atime off .................... 109128 |==================================================================================================
    HDD Fast10 with zil, 8G RAM, Sync Enabled, Atime off, 16K block . 110486 |====================================================================================================
    SSD Fast10, 8G RAM, Sync Enabled, Atime off, 16K block .......... 110865 |====================================================================================================


    Scaling Factor: 1 - Clients: 100 - Mode: Read Only - Average Latency:
        1.822
        1.859
        1.884

    Average: 1.855 ms
    Deviation: 1.68%

    ms < Lower Is Better
    HDD Fast10 with zil, truenas jail ............................... 1.855 |=====================================================================================================
    HDD Fast10 with zil, 24G RAM, Sync Enabled, Atime off ........... 0.975 |=====================================================
    HDD Fast10 with zil, 8G RAM, Sync Enabled, Atime off ............ 0.952 |====================================================
    SSD Fast10, 24G RAM, Sync Enabled, Atime off .................... 0.918 |==================================================
    HDD Fast10 with zil, 8G RAM, Sync Enabled, Atime off, 16K block . 0.906 |=================================================
    SSD Fast10, 8G RAM, Sync Enabled, Atime off, 16K block .......... 0.905 |=================================================

only the HDD Fast10 with zil, truenas jail is the local jail. All the other results are using NFS
Any ideas why there is a huge difference?

thanks

 

[fahadshery]

Here are the results with better visuals.

The only problem with the results that are effected are the Read Only and Average Latency.
Read/write operations are better than any other configuration

 

[jgreco]

results are not even 50% and shows 32 cores being hammered 100% during benchmarking.

Yikes. No idea. And unfortunately I'm too busy to try to replicate this, sorry. That is a completely unexpected result. If you can figure out the why, I am keenly interested in hearing what you discover.