[EXTERNAL] Re: FreeBSD10 Stable + ZFS + PostgreSQL + SSD performance drop < 24 hours

[Caza, Aaron]

Thanks for the response.  I happened across r307264; however, my understanding is that it applies to the FreeBSD 11 and I'm using FreeBSD 10.

Note that I have experienced the same performance degradation on a FreeBSD11.0-RELEASE-p1 amd64 system using 2x Intel 520 SSDs in ZFS mirrored pair with server an Intel Core i5-2500 with 16GB ram and running PostgreSQL 9.6.1.  There's no memory pressure in any of the systems I've tested on.

-----Original Message-----
From: Slawa Olhovchenkov [mailto:slw at zxy.spb.ru]
Sent: Saturday, June 10, 2017 10:37 AM
To: Caza, Aaron
Cc: freebsd-hackers at freebsd.org
Subject: [EXTERNAL] Re: FreeBSD10 Stable + ZFS + PostgreSQL + SSD performance drop < 24 hours

On Sat, Jun 10, 2017 at 04:25:59PM +0000, Caza, Aaron wrote:

> Gents,
>
> I'm experiencing an issue where iterating over a PostgreSQL table of ~21.5 million rows (select count(*)) goes from ~35 seconds to ~635 seconds on Intel 540 SSDs.  This is using a FreeBSD 10 amd64 stable kernel back from Jan 2017.  SSDs are basically 2 drives in a ZFS mirrored zpool.  I'm using PostgreSQL 9.5.7.
>
> I've tried:
>
> *       Using the FreeBSD10 amd64 stable kernel snapshot of May 25, 2017.
>
> *       Tested on half a dozen machines with different models of SSDs:
>
> o   Intel 510s (120GB) in ZFS mirrored pair
>
> o   Intel 520s (120GB) in ZFS mirrored pair
>
> o   Intel 540s (120GB) in ZFS mirrored pair
>
> o   Samsung 850 Pros (256GB) in ZFS mirrored pair
>
> *       Using bonnie++ to remove Postgres from the equation and performance does indeed drop.
>
> *       Rebooting server and immediately re-running test and performance is back to original.
>
> *       Tried using Karl Denninger's patch from PR187594 (which took some work to find a kernel that the FreeBSD10 patch would both apply and compile cleanly against).
>
> *       Tried disabling ZFS lz4 compression.
>
> *       Ran the same test on a FreeBSD9.0 amd64 system using PostgreSQL 9.1.3 with 2 Intel 520s in ZFS mirrored pair.  System had 165 days uptime and test took ~80 seconds after which I rebooted and re-ran test and was still at ~80 seconds (older processor and memory in this system).
>
> I realize that there's a whole lot of info I'm not including (dmesg, zfs-stats -a, gstat, et cetera): I'm hoping some enlightened individual will be able to point me to a solution with only the above to go on.

Just a random guess: can you try r307264 (I am mean regression in r307266)?

This message may contain confidential and privileged information. If it has been sent to you in error, please reply to advise the sender of the error and then immediately delete it. If you are not the intended recipient, do not read, copy, disclose or otherwise use this message. The sender disclaims any liability for such unauthorized use. PLEASE NOTE that all incoming e-mails sent to Weatherford e-mail accounts will be archived and may be scanned by us and/or by external service providers to detect and prevent threats to our systems, investigate illegal or inappropriate behavior, and/or eliminate unsolicited promotional e-mails (spam). This process could result in deletion of a legitimate e-mail before it is read by its intended recipient at our organization. Moreover, based on the scanning results, the full text of e-mails and attachments may be made available to Weatherford security and other personnel for review and appropriate action. If you have any concerns about this process, please contact us at dataprivacy at weatherford.com.