Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aHHk0-0007MA-Ko for pgsql-performance@arkaria.postgresql.org; Thu, 07 Jan 2016 21:05:04 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aHHjz-000442-N7 for pgsql-performance@arkaria.postgresql.org; Thu, 07 Jan 2016 21:05:03 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aHHjz-00043w-9x for pgsql-performance@postgresql.org; Thu, 07 Jan 2016 21:05:03 +0000 Received: from mail-by2on0062.outbound.protection.outlook.com ([207.46.100.62] helo=na01-by2-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aHHju-0005sO-4E for pgsql-performance@postgresql.org; Thu, 07 Jan 2016 21:05:02 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=BlueTreble.onmicrosoft.com; s=selector1-bluetreble-com; h=From:To:Date:Subject:Message-ID:Content-Type:MIME-Version; bh=9q8ZwkZ1LPNhLqywAHADMZhyL3AVsZc9X3fPU/QAhPs=; b=cdd/Q4DKu2cBvWX6+w7Y13+2oUvxVHFBSwZJVbHuMVPmehDjT8KSKepYZgfCLOykxCAZwJkhPZhZRTDbNgHo6dCXJ9epfI7PyaWRxQVwaXcnuWmluIiJ00oSIjtM7keRZw6LkGKoPfAQFPNYqO3SV4pUUtrRUm7OLYQontqoOZ8= Authentication-Results: spf=none (sender IP is ) smtp.mailfrom=Jim.Nasby@bluetreble.com; Received: from decina.local (70.113.16.71) by BLUPR11MB0674.namprd11.prod.outlook.com (10.163.216.144) with Microsoft SMTP Server (TLS) id 15.1.361.13; Thu, 7 Jan 2016 21:04:51 +0000 Subject: Re: Queries intermittently slow To: Andres Freund , Tom Lane References: <28587.1452097950@sss.pgh.pa.us> <568DE640.5070508@BlueTreble.com> <13261.1452179944@sss.pgh.pa.us> <9E456947-7273-4D62-A5A1-D66F24052A00@yahoo.com> <13016.1452191691@sss.pgh.pa.us> <20160107194759.GM7650@awork2.anarazel.de> CC: Rick Otten , "pgsql-performance@postgresql.org" From: Jim Nasby Message-ID: <568ED2E1.6060107@BlueTreble.com> Date: Thu, 7 Jan 2016 15:04:33 -0600 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:38.0) Gecko/20100101 Thunderbird/38.5.0 MIME-Version: 1.0 In-Reply-To: <20160107194759.GM7650@awork2.anarazel.de> Content-Type: text/plain; charset="windows-1252"; format=flowed Content-Transfer-Encoding: 7bit X-Originating-IP: [70.113.16.71] X-ClientProxiedBy: BY2PR05CA035.namprd05.prod.outlook.com (10.141.250.25) To BLUPR11MB0674.namprd11.prod.outlook.com (25.163.216.144) X-Microsoft-Exchange-Diagnostics: 1; BLUPR11MB0674; 2:Beax27tusAe13PlPgDlHjSyCN068Goco5zkY/Nb9ahqnGVgbMp2lPjuzf7YfPIOulPuPG4Lb/v7Mk6/QNxSEi+MVNhiGUHbFnN55uslc0UPrXCPzgL9Xn9c86d0UeG2jVqnlNWtIMWnwTSZ6P8jnCw==; 3:NHj/xAHjxWn2S1xlAmymhczHKMDA53Bzzg/LbolPnuRvSoJnsRUh6F49W3B8gDRQPvFlICcpiXUSjTFDD87T7WjYxk99N6DrRrfasNVQQqgzArQ9Vqgfo3qDcVwGKwdL; 25:adNRIzTFKKgGGf9b6SMeq8ySncydHPlhW0Nqg3j/uQfH1NbjGKT6j5iYgwkcW+bk0CmsrhPlJy2moU5568GZ2pEBR9ysJd7kRv4yDJorQgTH4C0ZS/GCwBezgtnbMOdE77T0YBJCC/HOu7tLMbbl58CDkEjOKqyxaiT0wavm7D70cN1EytPUzrEyMnbarNAB88/yxxEQft+GQeMhruN+i2TkHLp3h9ACl+fyShwJvIgdZjdbXR0to6PTevyfeHf0; 20:iR4PzC0KQmD9CV4Ux3SrezaWc5yXM/8QlLLX1QfOxUl7lovX5E7DTKitviuiyMl80G0WoyzBAFavMsnNFk0hEN9jrg8MX7B5aks9IMXZTc74sKHt48gvnCFMmNAePe7S/DU40i9DHwXkHyIe0BFO9h1pn+k5tyBQ6RH7R5PaGGI= X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:BLUPR11MB0674; X-MS-Office365-Filtering-Correlation-Id: 7befaa8a-a0a4-46c6-cbdc-08d317a62fbe X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0; PCL:0; RULEID:(601004)(2401047)(8121501046)(520078)(5005006)(10201501046)(3002001); SRVR:BLUPR11MB0674; BCL:0; PCL:0; RULEID:; SRVR:BLUPR11MB0674; X-Microsoft-Exchange-Diagnostics: 1; BLUPR11MB0674; 4:1ShkEVyLfAXwBlqco6MSSJ9dfWsGlBWwb/2emX3M/A/iKKJNqLRsbud6XrBM9cFxEFefzghQywSJMnBArS8VN+rPhR/GFfIN+GtoD+JZBq/+hPARweHm1OUCCyvjFaAIrbJWdjRQKGKMV8RebxVI6hLlRkzxqklQBQJzspkXg5MBd6tKs/8ZvpyNNZMk9EzAlkost55osG49a1/P9VqSuuLESoc01CnH2yMtwtVeFNLeOG2zEVQ28/EqtO7FfdkzYXfw/NZUU61+1ubRSsutaiIX1Hd98nsfvFRWaUANoePdSzE5YdZVkECZhV+0rg0Y3Q38PGtVSlaiSOIiiSrPTW7RK6/Z1HSTeijCaAKilQGnBtA0e52VpdMzJPLb3OHP X-Forefront-PRVS: 0814A2C7A3 X-Forefront-Antispam-Report: SFV:NSPM; SFS:(10009020)(6009001)(24454002)(377454003)(479174004)(189002)(199003)(377424004)(4326007)(106356001)(105586002)(122386002)(76176999)(15975445007)(1096002)(86362001)(54356999)(65816999)(80316001)(47776003)(87976001)(59896002)(40100003)(93886004)(87266999)(19580395003)(50986999)(66066001)(36756003)(65806001)(77096005)(33656002)(23746002)(230700001)(65956001)(5001770100001)(83506001)(64126003)(5004730100002)(189998001)(2950100001)(92566002)(4001350100001)(42186005)(97736004)(586003)(81156007)(101416001)(5008740100001)(6116002)(5001960100002)(50466002)(2906002)(3846002)(85282002)(336755003)(18886065003); DIR:OUT; SFP:1101; SCL:1; SRVR:BLUPR11MB0674; H:decina.local; FPR:; SPF:None; PTR:InfoNoRecords; MX:1; A:1; LANG:en; Received-SPF: None (protection.outlook.com: bluetreble.com does not designate permitted sender hosts) X-Microsoft-Exchange-Diagnostics: =?Windows-1252?Q?1; BLUPR11MB0674; 23:9A3fffRa17qVRX+AEUAYiiOIgcWEcPPlKGcEN?= =?Windows-1252?Q?/LoMCLI2CeN4vrSuyl7hCSa/SqhhU7pgPS1ZUjtBtJBCdUUWZAFrhH3M?= =?Windows-1252?Q?zuuSb5xjKqvHu7yvzQW5eqKr1ynStWyZx2cVsofMDdhkJR2SiMg+YnfZ?= =?Windows-1252?Q?OujO5qJY388Pwvsu8QX+5uqQcOv2kChPCu0aLgN/+P8PBhag+aN/RbXM?= =?Windows-1252?Q?qaCpX0oNV4yDgOoMJVNj0Gc59V1yEFvsNdpdwGrkp0FPgj+/nt0Gsmqe?= =?Windows-1252?Q?2nvm5YIKBGVoR29uyBkMgvt9yP+BP1Mu7N7AX7Eu0hDjeFolK56xm/LH?= =?Windows-1252?Q?51ywe8qm8iH4IiYmBd6Ye7nL4bz32J7rfbb1/N+HEascrAKybCur0o9T?= =?Windows-1252?Q?I8VVl/r89POcPjKXN8B4s7us6mYtSgMY9OXvVKR/VQ5JDOTcHK3Bi41s?= =?Windows-1252?Q?E48RGU12K8276yU0WQavCBWvUvzF1ftC6+yo3v83YfD38PhKBv6TTKPh?= =?Windows-1252?Q?s3SUS6ANmhCWjI3HQupYhv2UplbA+bh5aPGKrQACcbe0BKwOIG7Y+yJy?= =?Windows-1252?Q?u1it7RKY7ux/G1v2Uva2FQ/g3fREhGS0apw7z03L5fKOsAndvC+deZ1Z?= =?Windows-1252?Q?mRy1o85462Gs3HpBsj0Se+e7eemwuA9ThpkkaWIbGKl8PfqgVpUlKMOj?= =?Windows-1252?Q?1FUj32RGvkY6ACNTscbCDXFthLgrroCsLMJujF2Ce6ogy7igfRmevS7y?= =?Windows-1252?Q?0mB9wTWyqXYpdIcZe4YXcyL4KN4nI9b4h1pd6RLYMKNURP3s4Jfp6+Ic?= =?Windows-1252?Q?7ivzfIPaypkk9mEJCbg9h/aDukm1wpMK8pfMNTvKJbEJJmFA8AlBynhi?= =?Windows-1252?Q?TXByIzX4Tbckp1u+CMfrCvz/20f3z6EP1xbn6nClvHHRT4HCMprG6mN6?= =?Windows-1252?Q?9GLu5qHNhpB6UUPzZVcy+ghaAhpKM3DysPusUgvqvApDf3Y0iDFJ/3P4?= =?Windows-1252?Q?w2PReY89ve9LY2oHcSDm90cg/GL/tkI4LX5X7FneTfU7wO5Nm+pdctvC?= =?Windows-1252?Q?s8XU5dsWSEdGNjsiO/CIiloK9JqrKwbWosN2xtVZepAcfPksCgXvKbs/?= =?Windows-1252?Q?gB7zt/BhZqL8afkt+kgD79TJHY7ZzjWy1cZouc5zSFmhQNkLO6A3rpJt?= =?Windows-1252?Q?W/0B/5VQilNjn/aZBdbHf+j/Ku/7I92EK0GCSUXLz5eNwIdwbivz6/Aq?= =?Windows-1252?Q?/zduaGQaL/FeITse5SlXNSluqpLt4ymP61wWh+1saZdhujPf6XRqpQV2?= =?Windows-1252?Q?aDQEznfpBZRw3bsSQjCeyFo+L4Zb5Ha9o+7QyJ17704my6YX6TbRicnd?= =?Windows-1252?Q?XEPyt/ohxbi8b0+AIrwEyFFQqZyL60Pe6XMK0gaopa70p9h+hrOOYNiZ?= =?Windows-1252?Q?U2lWF6pfZrsNLAMb8FX73TG32k1xVd1vSSAv28thyphX4KZki86E+3gz?= =?Windows-1252?Q?/mVZOt9Rfa5eP5FelWTt1FHDg0bThlikjOFRouh5L1kVJ2Ueg=3D=3D?= X-Microsoft-Exchange-Diagnostics: 1; BLUPR11MB0674; 5:R0HUEWBGAcpMCHek92sjFN+1Ot4UoIFuZmd6NVDId9AeObh6uG0i/mfGdb01XLJf83P/gUG14/1Amk8lUIf8m6CpDYmA3e0PvSQL3zQzvjoGDPdkLydEAALXe0LC8qLBrbAgwRj52o43JFcVvqjicQ==; 24:LJaYFXFc0rZ3Hny0NLAQRfKF51dPM/k7vLs5CfRniX19Eso2o2H9T8XOoVVDvROVxfGhTS/0/pmSR1bxaN5ibrB5kiO02HM0ugynZH2wBWU= SpamDiagnosticOutput: 1:23 SpamDiagnosticMetadata: NSPM X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 07 Jan 2016 21:04:51.4289 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: BLUPR11MB0674 X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org On 1/7/16 1:47 PM, Andres Freund wrote: > On 2016-01-07 13:34:51 -0500, Tom Lane wrote: >> It's fairly well established that the implementation of transparent >> huge pages in Linux kernels from the 2.6-or-so era sucks, and you're >> best off turning it off if you care about consistency of performance. > > I think the feature wasn't introduced in original 2.6 kernels (3.2 or > so?), but red hat had backported it to their 2.6.32 kernel. > > >> I am not sure whether modern kernels have improved this area. > > I think the problem has largely been solved around 3.16. Around 4.1 I > could still reproduce problems, but the regressions were only in the > sub 10% range in my test workload. BTW, looks like Scott blogged about this along with some nice graphs: https://sdwr98.wordpress.com/2016/01/07/transparent-huge-pages-or-why-dbas-are-great/ -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance