Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1f7xOa-0008P3-36 for pgsql-hackers@arkaria.postgresql.org; Mon, 16 Apr 2018 06:13:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1f7xOX-0005c6-ET for pgsql-hackers@arkaria.postgresql.org; Mon, 16 Apr 2018 06:13:41 +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.89) (envelope-from ) id 1f7xOX-0005bw-72 for pgsql-hackers@lists.postgresql.org; Mon, 16 Apr 2018 06:13:41 +0000 Received: from lungold.riddles.org.uk ([82.68.208.19]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1f7xOT-00043u-Us for pgsql-hackers@postgresql.org; Mon, 16 Apr 2018 06:13:40 +0000 Received: from [192.168.127.1] (port=29382 helo=caithnard.riddles.org.uk) by lungold.riddles.org.uk with esmtps (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256) (Exim 4.88 (FreeBSD)) (envelope-from ) id 1f7xON-000LWq-UW; Mon, 16 Apr 2018 06:13:31 +0000 Received: from [127.0.0.1] (port=51578 helo=caithnard.riddles.org.uk) by caithnard.riddles.org.uk with esmtp (Exim 4.89 (FreeBSD)) (envelope-from ) id 1f7xOM-00039Z-OZ; Mon, 16 Apr 2018 06:13:30 +0000 From: Andrew Gierth To: Thomas Munro Cc: Andres Freund , Robert Haas , Tobias Oberstein , "pgsql-hackers\@postgresql.org" Subject: Re: [HACKERS] lseek/read/write overhead becomes visible at scale .. In-Reply-To: (Thomas Munro's message of "Mon, 16 Apr 2018 17:40:35 +1200") Message-ID: <87604rg0n0.fsf@news-spur.riddles.org.uk> References: <20170124171841.2n2nzqkuetbivzta@alap3.anarazel.de> <20170124174158.nk6htg7jjmqia7hr@alap3.anarazel.de> <422b4e6c-b7f0-90e0-6f70-389b2d50a848@gmail.com> <20170124181121.pgk7kqfkq4dd3hpo@alap3.anarazel.de> <20170124190705.2ioaocx6wuhvvegn@alap3.anarazel.de> <20170125195238.2sjh477x2zvmjm5a@alap3.anarazel.de> <20170622165048.lwuje4f5wqw57mke@alap3.anarazel.de> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/25.1 (berkeley-unix) Date: Mon, 16 Apr 2018 07:13:30 +0100 MIME-Version: 1.0 Content-Type: text/plain List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk >>>>> "Thomas" == Thomas Munro writes: Thomas> * it's also been claimed that readahead heuristics are not Thomas> defeated on Linux or FreeBSD, which isn't too surprising Thomas> because you'd expect it to be about blocks being faulted in, Thomas> not syscalls I don't know about linux, but on FreeBSD, readahead/writebehind is tracked at the level of open files but implemented at the level of read/write clustering. I have patched kernels in the past to improve the performance in mixed read/write cases; pg would benefit on unpatched kernels from using separate file opens for backend reads and writes. (The typical bad scenario is doing a create index, or other seqscan that updates hint bits, on a freshly-restored table; the alternation of reading block N and writing block N-x destroys the readahead/writebehind since they use a common offset.) The code that detects sequential behavior can not distinguish between pread() and lseek+read, it looks only at the actual offset of the current request compared to the previous one for the same fp. Thomas> +1 for adopting pread()/pwrite() in PG12. ditto -- Andrew (irc:RhodiumToad)