FreeBSD10 Stable + ZFS + PostgreSQL + SSD performance drop < 24 hours

[Caza, Aaron]

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.

Cheers,
Aaron
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.