Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1c25Ff-0002G9-RZ for pgsql-performance@arkaria.postgresql.org; Wed, 02 Nov 2016 23:47:28 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1c25Ff-0001eL-EN for pgsql-performance@arkaria.postgresql.org; Wed, 02 Nov 2016 23:47:27 +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_2) (envelope-from ) id 1c25FZ-0001Yj-08 for pgsql-performance@postgresql.org; Wed, 02 Nov 2016 23:47:21 +0000 Received: from mail-bn3nam01on0057.outbound.protection.outlook.com ([104.47.33.57] helo=NAM01-BN3-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1c25FA-0002p2-Qm for pgsql-performance@postgresql.org; Wed, 02 Nov 2016 23:47:20 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=BlueTreble.onmicrosoft.com; s=selector1-bluetreble-com; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version; bh=wzPXxuZt1q0b3Kk6eWVuatGJK/tIN/RLLGWvIuyVUYI=; b=ktL0y0QiCgUUnE9XbFww2apGjeJ6lKA4mImb/T6auTEwXa71iabowgvvWoKAOllFpOaa5iDjTA9T/DQqsVkLu8xZhMWLLncQZJP7pKj3+6h16ira1CGj65D0Lu33+m+Y/0YGZEQIpUAHzEY5SIruz3gAa9ZMtdre2Fz7olF5rzk= Authentication-Results: spf=none (sender IP is ) smtp.mailfrom=Jim.Nasby@bluetreble.com; Received: from decina.local (70.113.18.142) by CY4PR11MB1639.namprd11.prod.outlook.com (10.172.71.15) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.679.12; Wed, 2 Nov 2016 23:46:53 +0000 Subject: Re: Big Memory Boxes and pgtune To: "Joshua D. Drake" , "Warner, Gary, Jr" , postgres performance list References: <0cfccbc24b9e480a9452bf9ca79e96e4@UABEX4.ad.uab.edu> <44137c5c-2117-f497-7355-12ae06c1b299@commandprompt.com> From: Jim Nasby Message-ID: Date: Wed, 2 Nov 2016 18:46:47 -0500 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.11; rv:45.0) Gecko/20100101 Thunderbird/45.4.0 MIME-Version: 1.0 In-Reply-To: <44137c5c-2117-f497-7355-12ae06c1b299@commandprompt.com> Content-Type: text/plain; charset="windows-1252"; format=flowed Content-Transfer-Encoding: 7bit X-Originating-IP: [70.113.18.142] X-ClientProxiedBy: DM5PR18CA0038.namprd18.prod.outlook.com (10.173.208.24) To CY4PR11MB1639.namprd11.prod.outlook.com (10.172.71.15) X-MS-Office365-Filtering-Correlation-Id: 71521554-1bd6-4e85-d1db-08d4037a865c X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1639;2:6Z/vhE1qB+WeaKxIEt9edV33MEJ9IYkY7gNsGV1Q4P2IHKZdPhoWhFBN0iuKRF1UHS+XCJXaGfVhZpoRddGRg6FlR+tUmr9SeD8/Ld02llaUFZyp4WpwREnK1OjHTWoBz47N2EOSJWA/JC77V/aA8fwhoNUgcZssa26vLOq255THHZI4WE3t4wnfhig/5b8nC20y/1r44mC4HlGafym4HA==;3:LXWRqqy3gHfpznDspTNtEIMPC1sr8N8rikXUjocAVh2CrZ1JBBJmqvBRB1yNKSEQJuwxgRXeKl3PZ1A5QOxsVeqUdUpLW0RctmI6RYgqvE4KcX8SlWnaULcrFhUKj7Mzx5iGfTBmyT+Zg4hguuJTZg==;25:Z4h8bFZjfsUyclwoREcezkZ/xFHZ5bCvRJ4h10apOgr+rhWn5ga03slQQTDyDFeGe2+ZPn2fpX0difvQvb6yM+9rcvNy5UWuq91GanE2UZcJ+dLKOs3JUSXkf47EdFGFugBWY/aG4UmPUepjkCacpDey2J4VzQsr+Vmzs7T/it7ux4JW7oXvX9lXApk7kWzFD162P2/C0rVHHNvKGxWqJHvJUuhrGO9UanJGnj4TFzxdkZrIJaE8UC4dOy9Cc7uBUrFFOdFZ6GZwAzpioh6NMMY77rnvwgPnUt04DYlp+dzwFeH7Sff/zd5LaPW7arf3hpO3hS0bH3kSKh7Kgi/abFsmG94R640qsfeB26fcOTN7/WcyTbYu5JumdLcYXgnAU3QOO+O8AO4OF1aC7vIh1Qg8hPRbVYdSel68JT0IQqIgE9OLp/+My8Jv2Pcf6T84 X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:CY4PR11MB1639; X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1639;31:1nCVQiJ50802qm6TQTxB/h8F65vxW1Fxb0ZEUoWYdqtSl88s+lJ2B/+19/plTmVHYjYqhG0ECb4yy0Sdt0dV1RcA3DOEjZc1qE9NDkkGAuzT8aaodoEjGWZ/tuKPq4C6c1HOBTe0Sjla9L8MJyOIGziYAyQz98Zq6Gt2u5VF3JGOWKd282S4RlUliNAgFgqDyprZL9Lw6upgxlHtXgHPkJe65QRxCKyfQgojmnRSmQ5SQi5WxfZY69spS05ImHz7;4:yqRmw1UYDKb6VjsaW4doPOzHWs1vq1cjU0Q00vyL77P4UE9xL5/yCF5LNBF5f21G8nCrX4LP95MivvgmYpfhyXu74DXwW7CbaVV1egfacsXF4Dj+ViWctid7DSUKFSot/BdPptGpSiqrK8U0SGJEZab7WwHy8M/2zzYuYDlf7h+W7vrOMyBRcH49f2r2sgM1AezlXKA7N5vTJbTDXioDRtddGrDOMQzcuGqdU5RhptZc1m1RuPAmaMROEKDqBpzSTNaCiWaLnNlS5BEWjM2jlF09OsqCcoMQJ9D+57A09sW6jRxYHmmjUIGsKyEaVhbURc8hBB5EHCDdHwZv6ypy+FxQzko53TAK8Uho1lkKRAYbLC5cxBlBG9jYcydchV/C0E08baTl9tPNrtgV9DwzuspoiPnlPJGzBJ13E5QnXqwmTH0g4t+yrX9dpI6cgmHP X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0;PCL:0;RULEID:(6040176)(601004)(2401047)(8121501046)(5005006)(10201501046)(3002001)(6042046)(6043046);SRVR:CY4PR11MB1639;BCL:0;PCL:0;RULEID:;SRVR:CY4PR11MB1639; X-Forefront-PRVS: 0114FF88F6 X-Forefront-Antispam-Report: SFV:NSPM;SFS:(10009020)(4630300001)(6009001)(7916002)(189002)(199003)(377454003)(24454002)(19580395003)(8676002)(50986999)(6116002)(81166006)(2906002)(92566002)(2171001)(230700001)(31696002)(106356001)(81156014)(5001770100001)(2950100002)(189998001)(4001350100001)(586003)(15975445007)(77096005)(105586002)(7736002)(86362001)(65826007)(7846002)(3846002)(5660300001)(33646002)(64126003)(50466002)(107886002)(65956001)(68736007)(16601075003)(54356999)(42186005)(6666003)(31686004)(23746002)(47776003)(305945005)(36756003)(101416001)(83506001)(97736004)(76176999)(65806001)(66066001)(85282002);DIR:OUT;SFP:1101;SCL:1;SRVR:CY4PR11MB1639;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;CY4PR11MB1639;23:kVOZjE94wlQtXKQoGcYgqzDR9TtRTiRY1z8Jc?= =?Windows-1252?Q?birfylWkJKpgtmx+8JHGMI1BeIVF7+cMxfd/Co7ITJtcGv0Si03u2ZWb?= =?Windows-1252?Q?YpouTx0D4TS1MEMw4x2CZse+jKHlxBJyQA5kIYEzXv/4GYThKZY84GkG?= =?Windows-1252?Q?/Ia9JULGcQypDCsJJBULDyJNWuhtXFSyMmnNcHlLCdcBNY6KBGnZWuxg?= =?Windows-1252?Q?9p+yyWgIfUdn3783Njy1c9q8AQ5R6YdfEUGIa6yfNp6qyCBb7bzWMa4t?= =?Windows-1252?Q?IkaMulUusRvLhJmZk104ehdjXdxIxuIjDtRpEnoLFGxeQ4apTSuw2eIL?= =?Windows-1252?Q?bXg9YCEmkQ2ztjxNaM1hklDknCWcwvP/rq9Jptn73ioItZizJaSdMkF2?= =?Windows-1252?Q?XTdca5Rf3/tEzJ+VzDZ1amhXm0X91J3+RzCm3ankItLvlujknBrAKQdl?= =?Windows-1252?Q?/u+9CgVAZqnPWp7Dzep/SkOGlxHd6ER/lXgqNOYM0C0LLVQFC6CEzYlw?= =?Windows-1252?Q?l6kDvTtT6vJbABoPJos8IDOiY0CO6imRlANErJ8AmXlCtSEqtvo7WZxA?= =?Windows-1252?Q?WvyxZrLNTQP0G19ZSpKVV5AktPc476q3+DDU4BAlVs7QP2bEffvihabn?= =?Windows-1252?Q?YY5czYMK2MzihIFLG0jfGG0o97m60098yLX4cFIkosxE2w48LsoLPyvw?= =?Windows-1252?Q?cSz7qFD8ArrV/lsZ9NyYi5YhlUYlHem4QHkl59gcfe4wFA+9m87NNKF2?= =?Windows-1252?Q?lKv6PbsvTK93/FpuZP6GNF+RZDm+UTx5FtQDFVRoJV4HlaKGBTAcl/Ws?= =?Windows-1252?Q?tM4d5vKj+EJqWp2pzlmEs33iOO4L35Ss3zUqhnmQkcunAeCp8tdYMKEI?= =?Windows-1252?Q?I5wn5lMkxGYbhkyx/D5sUCt+OTXaqg9sk1cfz9IbW53eHFriF5xjxwUa?= =?Windows-1252?Q?sEwhtKWXtQgeRsrmn3iuhYxtb0z0F3c8ek15SgIClNwR65R/3mK3FveM?= =?Windows-1252?Q?NLwplK/+v5U5jIAhuIR37bJ4jxePktLu+a+36Lx8kmj16ja4V0xmdmSU?= =?Windows-1252?Q?T6O1vdmVeOhxC69aDo5h5AZ0KWKlrXJsylPtnKNcAunHqSZcMs52jykH?= =?Windows-1252?Q?OOXQUun+FU7V/IBzBYnllKvDXFegSXcrO37Civ7dVtJbrECRo4bMa+Ua?= =?Windows-1252?Q?rpTSFy4wGVeAJwsfeIam0eAmKwjomyeWC43FMWK0PgnjcdjrZ9kYwGPR?= =?Windows-1252?Q?NkM6VfNs4zU/EK8Ffz2GcQXkc7KBHGOGAHyhSsMPrqVW4JPJFbyr5VyT?= =?Windows-1252?Q?9MMv+AajzGdwelXCd1KavXBFHNtUZT5UJ7hYSfKHBVNwR36sdFbES4oC?= =?Windows-1252?Q?d8SXuZkS03cAXtFb0YulCXF+vivzXyOaXBOfxphux0xYNQEKMmyLgiXg?= =?Windows-1252?Q?HmUoKoWp/0tOZ+wPuqXZ8wGpcIh/AUnPLnycs47pHRensjSTY/ZqO2py?= =?Windows-1252?Q?T8CiZs=3D?= X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1639;6:MTrpNvuWxelHrnwRl95vrr4zM63G9jtCzM6FqovoXP5xDsZmMX3q7kuVSXj9rJZQlt/Ntw2f/s8oyWra3j7MMsouiYtOBD96F3mj2LvmS3e7bDGGlfZQ3OOrKxJhVRLRW2PoxjQwkTYSlp2VhgAVwqqV7dc6e1roW5MUO1nn8mydvmVRpvgaSto+OLsveQOlF5+VbbVTdYWnBGtPxOwZnSQHEfvlUp/+KNfQMzJYHnqP5dHckXu2w0fwkERwnEOo+84EGI0k2gQE7DKfScUICdqhdVA1aY7b9lWXq4zXU4OAwVMFit1TvCtST+sOOuZQCaYfoBq4ki+10krKcjjUWA==;5:+JPAYEpn/CwYJtbZEx3HGdyYzodi+IwFzSSU0lAbRBSyAn094nNGypbLhDUc9ENYqM+FYd3f5+xpJUns2hLX5UBNlG4bRelVg8CLY/FA95NoxJaCM2nQM5Sk4xGmuHGeg1tCRqW97dRn2+yBcd7Rjw==;24:13tjmPSq6oF1APg/+9/jTjymZpBgGoRHcGZdpDXPkRnX/RctJl06d/HAVJXyIb8iaCaYPn3diNHIr6bFARDwZ70aAI4fDUXRbT09T96r3ow= SpamDiagnosticOutput: 1:99 SpamDiagnosticMetadata: NSPM X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1639;7:CIfWwhZ90h7v1dN4LVWWL5DfKVbR86vqehdWUSsKI6LB78Oq9CNRlYwDXsuDWdhrJOl55gM7371aAFHao6DcVZUwVLjBxDCpdSIVU0STRE1e1MwzkZQIqKmwJbcuEXXlHk+zdgC/OYLC/Bksc+Ti9NoYzMVMPfBwC0Z/qz+0OAv73njccFtQ7KyYOmipAg2K+vrFGKS8MfCf60IdsAJHMzaIkqsH+QHgFl3MeUJ1KhDP+vpEb3F3Dt4NQsDKVbKAaIl3Wc1uC4O9xPvbwqa+bkiAESIFrMQFEfgMtJ6ocjW/3aeXpGyvEDbMteoYPlVxJ75yReReejc6KPEInFdXTf7yOiqIr/L23R7zzoeBLoI= X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 02 Nov 2016 23:46:53.8593 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: CY4PR11MB1639 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 10/28/16 2:33 PM, Joshua D. Drake wrote: > * A very high shared_buffers (in newer releases, it is not uncommon to > have many, many GB of) Keep in mind that you might get very poor results if shared_buffers is large, but not large enough to fit the entire database. In that case buffer replacement will be *extremely* expensive. Some operations will use a different buffer replacement strategy, so you might be OK if some of the database doesn't fit in shared buffers; that will depend a lot on your access patterns. -- 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 855-TREBLE2 (855-873-2532) mobile: 512-569-9461 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance