Date: Sat, 10 Jun 2017 16:25:59 +0000 From: "Caza, Aaron" <Aaron.Caza@ca.weatherford.com> To: "freebsd-hackers@freebsd.org" <freebsd-hackers@freebsd.org> Subject: FreeBSD10 Stable + ZFS + PostgreSQL + SSD performance drop < 24 hours Message-ID: <79528bf7a85a47079756dc508130360b@DM2PR58MB013.032d.mgd.msft.net>
next in thread | raw e-mail | index | archive | help
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 Int= el 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 Post= greSQL 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 w= ork 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-st= ats -a, gstat, et cetera): I'm hoping some enlightened individual will be a= ble 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 n= ot read, copy, disclose or otherwise use this message. The sender disclaims= any liability for such unauthorized use. PLEASE NOTE that all incoming e-m= ails sent to Weatherford e-mail accounts will be archived and may be scanne= d by us and/or by external service providers to detect and prevent threats = to our systems, investigate illegal or inappropriate behavior, and/or elimi= nate unsolicited promotional e-mails (spam). This process could result in d= eletion 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 a= nd other personnel for review and appropriate action. If you have any conce= rns about this process, please contact us at dataprivacy@weatherford.com.
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?79528bf7a85a47079756dc508130360b>