Date: Tue, 13 Jun 2017 06:24:34 +0000 From: "Caza, Aaron" <Aaron.Caza@ca.weatherford.com> To: Slawa Olhovchenkov <slw@zxy.spb.ru> Cc: "freebsd-hackers@freebsd.org" <freebsd-hackers@freebsd.org> Subject: RE: [EXTERNAL] Re: FreeBSD10 Stable + ZFS + PostgreSQL + SSD performance drop < 24 hours Message-ID: <f3599715fd104d73acd0846ebc468315@DM2PR58MB013.032d.mgd.msft.net> References: <79528bf7a85a47079756dc508130360b@DM2PR58MB013.032d.mgd.msft.net> <20170610163642.GA18123@zxy.spb.ru>
next in thread | previous in thread | raw e-mail | index | archive | help
Slawa - thanks again for your suggestion but, unfortunately, same behavior: (Server is Intel Core i5-2500K w 16GB ram and 2x Intel 520 120GB SSDs): PostgreSQL test results immediately after reboot: Timing is on. count ---------- 21568508 (1 row) Time: 53855.000 ms test$ uptime 7:51PM up 2 mins, 1 user, load averages: 2.46, 0.88, 0.34 test$ uname -a FreeBSD xyz.com 10.3-STABLE FreeBSD 10.3-STABLE #0 r307264M: Mon Jun 12 17:= 48:24 MDT 2017 aaronc@WFT:XYZ amd64 PostgreSQL test results after ~5 hours: Timing is on. count ---------- 21568508 (1 row) Time: 737626.512 ms test$ uname -a FreeBSD xyz.com 10.3-STABLE FreeBSD 10.3-STABLE #0 r307264M: Mon Jun 12 17:= 48:24 MDT 2017 aaronc@WFT:XYZ amd64 test$ uptime 1:06AM up 5:16, 1 user, load averages: 2.21, 2.59, 2.04 Perhaps if I was on stable/11 this would be of some benefit. Regards, A -----Original Message----- From: Caza, Aaron Sent: Sunday, June 11, 2017 10:33 AM To: 'Slawa Olhovchenkov' Cc: freebsd-hackers@freebsd.org Subject: RE: [EXTERNAL] Re: FreeBSD10 Stable + ZFS + PostgreSQL + SSD perfo= rmance drop < 24 hours Thanks for the response. I happened across r307264; however, my understand= ing 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 FreeBSD1= 1.0-RELEASE-p1 amd64 system using 2x Intel 520 SSDs in ZFS mirrored pair wi= th 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@zxy.spb.ru] Sent: Saturday, June 10, 2017 10:37 AM To: Caza, Aaron Cc: freebsd-hackers@freebsd.org Subject: [EXTERNAL] Re: FreeBSD10 Stable + ZFS + PostgreSQL + SSD performan= ce 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 I= ntel 540 SSDs. This is using a FreeBSD 10 amd64 stable kernel back from Ja= n 2017. SSDs are basically 2 drives in a ZFS mirrored zpool. I'm using Po= stgreSQL 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 performan= ce 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 compil= e 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 an= d test took ~80 seconds after which I rebooted and re-ran test and was stil= l 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 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?f3599715fd104d73acd0846ebc468315>