Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bhiQb-0008Dj-Bf for pgsql-performance@arkaria.postgresql.org; Wed, 07 Sep 2016 19:22:33 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bhiQa-0007D8-Uo for pgsql-performance@arkaria.postgresql.org; Wed, 07 Sep 2016 19:22:32 +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 1bhiQa-0007D0-9h for pgsql-performance@postgresql.org; Wed, 07 Sep 2016 19:22:32 +0000 Received: from mail-dm3nam03on0084.outbound.protection.outlook.com ([104.47.41.84] helo=NAM03-DM3-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.0:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1bhiQR-0007UD-Pm for pgsql-performance@postgresql.org; Wed, 07 Sep 2016 19:22:31 +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=Gr2V92DjiLIJ5eqM38w63D8lrfzH0pN8eCI21tni/pw=; b=NyjdH+FQXrbkM5+gvNnwAk/v6LIDUouOVtuiqmTZxdSJwwSd2MRI8Ap/wo54BaTJCNCzJyJpErz/+//bQ/YRmzNsct2lRr0AWzEG/DWBPOlfsY8Ya/zAE7UbDfuMK1Ui9UUK1w1hKu6Urq2WkB6mYnh9Kj/D5gqkklvK9x8dJ4w= Authentication-Results: spf=none (sender IP is ) smtp.mailfrom=Jim.Nasby@bluetreble.com; Received: from decina.local (70.113.16.71) by DM5PR11MB1644.namprd11.prod.outlook.com (10.172.38.15) with Microsoft SMTP Server (version=TLS1_0, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA_P384) id 15.1.609.9; Wed, 7 Sep 2016 19:22:19 +0000 Subject: Re: Postgres bulk insert/ETL performance on high speed servers - test results To: Mike Sofen , 'postgres performance list' References: <04b701d204c2$0bd7e330$2387a990$@runbox.com> <001401d206a8$9eda1350$dc8e39f0$@runbox.com> From: Jim Nasby Message-ID: Date: Wed, 7 Sep 2016 14:22:10 -0500 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.11; rv:45.0) Gecko/20100101 Thunderbird/45.2.0 MIME-Version: 1.0 In-Reply-To: <001401d206a8$9eda1350$dc8e39f0$@runbox.com> Content-Type: text/plain; charset="utf-8"; format=flowed Content-Transfer-Encoding: 8bit X-Originating-IP: [70.113.16.71] X-ClientProxiedBy: SN1PR01CA0034.prod.exchangelabs.com (10.165.224.44) To DM5PR11MB1644.namprd11.prod.outlook.com (10.172.38.15) X-MS-Office365-Filtering-Correlation-Id: b09758d9-58c6-416b-5305-08d3d7544999 X-Microsoft-Exchange-Diagnostics: 1;DM5PR11MB1644;2:9+NkRXhLakNW+G4t018A6o3tSoAaMNtOm58oHKhJ+8mwuGN7QvZJxf9ZtRv+3ZL8UNGIwUPZFD0uLtpX35nEaae2KMQWkfZc9dwYdRh0x7f+mDYMOo4KQ6iYbJczBBNLW24moDgu534mkLPp4iss/Vbyv2ylJYrN9vFInpeb8Sa/EHIKLjFLQhGRb1ZnXJhI;3:R0yoqNtSPSR1dwbhp2gKIly8KbHXbKi1JzzfBNWBtKX3ee5mqhAarcQVHexAMkkZHPmzQ7v2Z7g4rVs/6Ezmm8Q+JFSqwwUcL8wlb9vX81kE1JLn52Zo73DZ1tV0164I;25:s8Jiypmv1/d9602fr6Lb1urdpodQqqljCBv510qBVq83CmGyP+kDNZQ6EuaM+Kac2hHaTNq6QAyfNO1EP/LtASUCIpKmvPJxlNRUOoZl32lC+NmC+wPCtGWY5CYb2zeO4adS4lbZuDL+fvRcVZRRPIx8couxMpYQiEzCBQ10eiozoxebBcldhmOhuAihE/uz6TBKX9CSczKMXPdU/h7IR/ig6KPT6SvbigPZu5jpNf2FqzYF/PEfKpyV+McWJSOA/Tdf+fKPPdHdRvcLxqAEAswu2LojY6Wc/7JnfjBj8gC3xcg60bnvStqG7J3/ruGKHEoVwO9KgDfIFS8aSTrw03klX/nmnW2OsRkjzECHX/FWn540li7Ota+1iy1hrm83rW1QHdtew2FyWXn0msydc+x1+Jm5I0WqSsuljy0e0sY= X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:DM5PR11MB1644; X-Microsoft-Exchange-Diagnostics: 1;DM5PR11MB1644;31:0CuQwqZQ6K6gthdaU2dv0+lQJelFqv5H3iiXPzaegYclYnf3VrlrKVgWWKv1rGFxbz+Sgs51faMUxI1sutpJxRvWDy6doHhpvLRSXH+99mn4C7Iqj5WZ+F9hQzTqizWeHWkRtZ4zBu2rnkuxYyXYv2jFUsT25J4aP4S4LAXZJAz0EfJKI+Z6NNYpzFhobcYP46CB+6Vf4miTGYB7EhVwt7Jjul2sArMxuRqhwUyoY1A=;4:kes8OaHK68nRiYxkLNHpz5EHN0lwiPTj1URN8EduGrzsjGAxa8hWfNhX1o8gDlnv2EJBy0h7dQu4KpTsG7llzgTs856/ZPL0a8m2b9vY50SWcVW/zkbZ+1cv8R3fz3+JqJF9OGA4ggonmZbjBazb7cuTxJgsNcaTg3m6+cZ8TEI119SmTrWOwiGG6FNeIyUSejyKD1slrndB2M9JcZqOKs6SVB4a5i4dVXRfFcH7yhFxLHRjABVY0/iMQOuzxVxcYHB9hKP1izGFcawMNpds92T9OuVGk1UmAmeMcichyv52YdTpSdiksQloQgfRAy06OVi4DsAg53RRKY3WUDhi7Nfy5FtuVofrAuHl0kvf35LbMIAtXLwjgR98WreubUzBwMNRlm6DFbWHEZEMA/JJ1Aa7SPklSqALvP1/mEI/0q9XHU2k30KOE57+5BEk9QGSFBx8mWkNR2oQkrGGUEkC5w== X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:(20558992708506); X-Exchange-Antispam-Report-CFA-Test: BCL:0;PCL:0;RULEID:(6040176)(601004)(2401047)(8121501046)(5005006)(10201501046)(3002001)(6043046)(6042046);SRVR:DM5PR11MB1644;BCL:0;PCL:0;RULEID:;SRVR:DM5PR11MB1644; X-Forefront-PRVS: 0058ABBBC7 X-Forefront-Antispam-Report: SFV:NSPM;SFS:(10009020)(4630300001)(6009001)(7916002)(377454003)(24454002)(189002)(199003)(33646002)(3846002)(107886002)(92566002)(36756003)(93886004)(23676002)(189998001)(105586002)(6116002)(76176999)(47776003)(16601075003)(31696002)(65806001)(66066001)(65956001)(19580395003)(50466002)(65826007)(68736007)(2870700001)(586003)(31686004)(2950100001)(15975445007)(81156014)(8676002)(81166006)(106356001)(7846002)(77096005)(7736002)(64126003)(5660300001)(86362001)(305945005)(50986999)(54356999)(101416001)(42186005)(97736004)(5001770100001)(4001350100001)(83506001)(2906002)(85282002);DIR:OUT;SFP:1101;SCL:1;SRVR:DM5PR11MB1644;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: =?utf-8?B?MTtETTVQUjExTUIxNjQ0OzIzOlJzd0IzME5YS2MzWkFtWEFOY1BXN0RTOUtL?= =?utf-8?B?Z0FWbzg3a1FxRVRQdWRNVGJpeXJzZXBlM1dNU2NFK2l1V0h2V3FUaVNhYjNu?= =?utf-8?B?YkVIQ291L2g4UHBRY0VxcWI4VDd1dEZ4TU1kUk5LMFMwWWtMZmhoQ2htN0No?= =?utf-8?B?MmxSdTNjajREUUVCcFFMQzQrd0pvazRPU2w3c1ptRTR2WFpHTzZHdlNmbWRT?= =?utf-8?B?SWZQdERISmh3SGlsdzJ1eXpFTlRONDdEeTE5UEpkRThHYzhyS1lUbVJSNmJa?= =?utf-8?B?YlFGV2EzU0xuSVVFS0FPR1Z5cjJMcXJpSWUrbThhbktMeDZISmdOaFdXRVhp?= =?utf-8?B?MFdqNllaeG41WGVwZ0lkTEQrL05KZ05lWHpaVnhhaW9WcDU2SXRRK25HSDNO?= =?utf-8?B?TVJ3OFFpczZNQ3BmaVlKTnJXbG5WOE5Cbko0N0QrNmZPdDBhSTRPVVpNN1Jr?= =?utf-8?B?UDdqRkpaSGN3VUdQMWRBbEhXLzNOelNlMjlhdXFnU1FjcEJqUUxpK2RqQ0Ur?= =?utf-8?B?c3ppamMyRnhTWjdCVFZaQVg4dS9vV0c2aE9RTVZCMm9JSDIwTUJ4amlCS3gy?= =?utf-8?B?dXNBVVpPWlpEV0tUa2EwbkxuRHVvaklIZnZEaGd5UmdkUGIzOXBTdkoyRVlU?= =?utf-8?B?Wkp6UFNjVDNHR3hqU3ZnczR2WnZKSE9ZcjJxNlVUTmtiQ3Q2amorTG5lV2U2?= =?utf-8?B?RHR5SS9Gd1lubm5JdlgwOG1LRGJtVjlnRHNPWEpxSUJtNjZyZThjZE9JazV0?= =?utf-8?B?dm94bmJzNWRvRWJlVHZ4dllHZFZ2Y0RQUDFuQWdHRXRCbUEwckVPNUh2RXZC?= =?utf-8?B?QWhvb0pOWE56K1lvaVNockNZZXdGeVhkeU51bkdUYkN1OVJRMUFDZzFSYTdQ?= =?utf-8?B?bXhGdGpKR2p3cGRqV3dCZWd1aG1QamZwVXltQmxiTFFsaUhqR0F6Z2tEdzRz?= =?utf-8?B?NWVnOHhQbXdMK2hVb3VKTU10OUJScWpkN0lIZlZCQWlNVmhTUFo0VE5rWnJY?= =?utf-8?B?cFl0T01pTGY4K09qazdLWjdkK3NUL2laRm9kVEg2NVl1ZEhQa0RKZXJ3N0xq?= =?utf-8?B?dkR4SXpmUjUwTEltdFg2NVFmYnExUlZPRG1JQVlPM2dEMkF2ZEl1d3B2LzFP?= =?utf-8?B?RFE2b1Q2dU1JNEdlSjNMSS9DWGl1Ym1CdWdRNFphbWlCNkRHdmxBK1lkS3lU?= =?utf-8?B?R3d0WnpUL211NnIreE9oK0N5ZVROTTJGMVRVMnlsYXgzdDJtOHAxclN2TFRW?= =?utf-8?B?TXcxWFJiL2t6NHB6andxNHI2dXRCcnd0NHk0UzF0WjNHaW1pU0dFdGRXVG9o?= =?utf-8?B?ZGNqWkVTcFZjRit3OTJCZGtrczhHZEl5NFFSY0huY2h1djhSQ0ZLcjBPaXVF?= =?utf-8?B?QmJVTThNNWtwQldpVjV2ZUd1bmZFOXZ4WVA1SkZ3VXc1RWpuemV0WVdFbkY4?= =?utf-8?B?UjVHT0dRaThtZG5zRlBacVFuMm5uQ3Z6ZS80T3U1dGxXWk1hVjNpSDlVS3do?= =?utf-8?B?SWFIZkUvaEVXR1FBRDFGNnZUQU1kbDlnTE1USE15SU1tek03dDd5WStQUTB3?= =?utf-8?B?T1hJQlVOVERXRGNTTjVYcDdRM0hWRGR6NmxmR3hrOVJxYkVQZms0S1RrbWtI?= =?utf-8?B?NEw0Sk1jTktQaUsvb0xqL2hnMFdNcnpsQVB1MUZhaWVXSlcxZzU5ZGVMMnJn?= =?utf-8?B?aHBkeXhRbml0UWZuZXZac0pqNVMwTGhEejI2QkFkVTdPc1hYbUNBUndBMENV?= =?utf-8?B?MGExWUNwZVR2TU5CS0p6YzN4NU9EczZ4bjdtRGZ2Nm92T1ZIeWtzWkxXc2tR?= =?utf-8?B?SzE1RS9yb0h3WGlTRWlrL3MybHRxV003YXJueDhHMGlvUWc9PQ==?= X-Microsoft-Exchange-Diagnostics: 1;DM5PR11MB1644;6:DYCapOL1KzFDATPNsxVJ2JqAaMzxO4JQIFuFlniv1HQfjekmRyQt0AfQVSFs/c641ehZGp733ZolKCGesa0ADBLc52gXu0Mi3EhjmuXtHrvLY0mwGd3763LwuY2fKuEsBiu0iqnhFsOAz4P0aSNL/sn09IVFkegczb7CpL5EFjIFgFIqvsUaFEFi4hrogQ3BQM6RN20JGr8ntd0IRq2M8zrRIxoF+mAmvp65lo/GII1yRkVKxajn7g8MEVHLrZpMjCVtsQosMm+62hoW6eTGdYT6LMWQgJ/kcmU6iYuiWkiQU6UeQXPaYgLiZpgzQFvG;5:6fwT4rRN6YoQNBdBIpAXTqyBoSKByr3IowxqklK+8JA5hqU4tKoGVgNWyNOPNDsTEX69YL3ERQyuZcQTgsnjVz+eQUupjc+73cGF+x79OlZ9ZiaYXMxCV/NA0YfCMPD405q62JaWZapOGIUDaRVDog==;24:EO06rCWKzlbFSW9mWbNlmWjIKjy80wKrVfIB61XoE0kumMCRmyBouwG7h4oWPUpYHLAgUhfqWFNFzjhXQX9JA+Hr5mlXhxxX3mb1QprKj9U=;7:4rjRpkjI0zw9STiZwUndkJUN5x2ly6YU/8NX5ghhrSgY8l24sU0M3KBepeixehsmPBHlBTcqlEyDAMbjlT/EmBxX9WuHMhzruUN54Iq1ujchgHvbDBoI7Spv4tESxWVRkuZIr8Ua9sUtrBsOnK5AkDRl8nuexvZnowK1XdGdPMo0iOsP/qz03np9wm3xyCI6yV452dSnE77oYsOmaLyYzV5Lt0C8q/3EQSOgmMPvf/IzidlGNSPVZp1YpZJ5p5O4 SpamDiagnosticOutput: 1:99 SpamDiagnosticMetadata: NSPM X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 07 Sep 2016 19:22:19.7408 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: DM5PR11MB1644 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 9/4/16 7:34 AM, Mike Sofen wrote: > You raise a good point. However, other disk activities involving large > data (like backup/restore and pure large table copying), on both > platforms, do not seem to support that notion. I did have both our IT > department and Cisco turn on instrumentation for my last test, capturing > all aspects of both tests on both platforms, and I’m hoping to see the > results early next week and will reply again. Something important to remember about Postgres is that it makes virtually no efforts to optimize IO; it throws the entire problem in the OSes lap. So differences in OS config or in IO *latency* can have a massive impact on performance. Because of the sensitivity to IO latency, you can also end up with a workload that only reports say 60% IO utilization but is essentially IO bound (would be 100% IO utilization if enough read-ahead was happening). -- 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