Re: Speedup of relation deletes during recovery

On Tue, Apr 16, 2019 at 10:48 AM Jamison, Kirk wrote: > > Hello Fujii-san, > > On April 18, 2018, Fujii Masao wrote: > > > On Fri, Mar 30, 2018 at 12:18 PM, Tsunakawa, Takayuki > > wrote: > >> Furthermore, TRUNCATE has a similar and worse issue. While DROP TABLE > >> scans the shared buffers

RE: Speedup of relation deletes during recovery

Hello Fujii-san, On April 18, 2018, Fujii Masao wrote: > On Fri, Mar 30, 2018 at 12:18 PM, Tsunakawa, Takayuki > wrote: >> Furthermore, TRUNCATE has a similar and worse issue. While DROP TABLE >> scans the shared buffers once for each table, TRUNCATE does that for >> each fork, resulting

Re: Speedup of relation deletes during recovery

On Thu, Jul 05, 2018 at 03:10:33AM +0900, Fujii Masao wrote: > On Tue, Jul 3, 2018 at 11:28 AM, Michael Paquier wrote: > Thanks for the review! So, committed. Thanks. >> (please indent!). > > Hmm.. I failed to find indent issue in my patch... But anyway > future execution of pgindent will fix

Re: Speedup of relation deletes during recovery

On Tue, Jul 3, 2018 at 11:28 AM, Michael Paquier wrote: > On Tue, Jul 03, 2018 at 04:13:15AM +0900, Fujii Masao wrote: >> OK, so what about the attached patch? > > I have been looking at this patch, and this looks in good shape to me Thanks for the review! So, committed. > (please indent!).

Re: Speedup of relation deletes during recovery

On Tue, Jul 03, 2018 at 04:13:15AM +0900, Fujii Masao wrote: > OK, so what about the attached patch? I have been looking at this patch, and this looks in good shape to me (please indent!). +* Call smgrclose() in reverse order as when smgropen() is called. +* This trick enables

Re: Speedup of relation deletes during recovery

On Thu, Jun 28, 2018 at 3:23 AM, Andres Freund wrote: > On 2018-06-28 03:21:51 +0900, Fujii Masao wrote: >> On Wed, Jun 27, 2018 at 10:44 AM, Thomas Munro >> wrote: >> > On Wed, Jun 27, 2018 at 1:13 PM, Thomas Munro >> > wrote: >> >> On Wed, Jun 27, 2018 at 12:16 PM, Thomas Munro >> >> wrote:

Re: Speedup of relation deletes during recovery

On 2018-06-28 03:21:51 +0900, Fujii Masao wrote: > On Wed, Jun 27, 2018 at 10:44 AM, Thomas Munro > wrote: > > On Wed, Jun 27, 2018 at 1:13 PM, Thomas Munro > > wrote: > >> On Wed, Jun 27, 2018 at 12:16 PM, Thomas Munro > >> wrote: > >>> I think we should take the hint in the comments and make

Re: Speedup of relation deletes during recovery

On Wed, Jun 27, 2018 at 10:44 AM, Thomas Munro wrote: > On Wed, Jun 27, 2018 at 1:13 PM, Thomas Munro > wrote: >> On Wed, Jun 27, 2018 at 12:16 PM, Thomas Munro >> wrote: >>> I think we should take the hint in the comments and make it O(1) >>> anyway. See attached draft patch. >> >>

Re: Speedup of relation deletes during recovery

On Wed, Jun 27, 2018 at 4:17 PM, Andres Freund wrote: > On 2018-06-27 15:56:58 +1200, Thomas Munro wrote: >> Without range-scannable buffer mapping (Andres's radix tree thing), >> that bet doesn't work out too well when you do it more than once. >> Hmm... we could just... not do that? > > That'd

Re: Speedup of relation deletes during recovery

Hi, On 2018-06-27 15:56:58 +1200, Thomas Munro wrote: > That's a different code path that eats a lot of CPU on the *primary*, because: While that's obviously not great, I think it's far less dangerous than the standby having worse complexity than the primary. There's an obvious backpressure if

Re: Speedup of relation deletes during recovery

On Wed, Jun 27, 2018 at 1:46 PM, Andres Freund wrote: > On 2018-06-27 13:44:03 +1200, Thomas Munro wrote: >> On further reflection, on the basis that it's the most conservative >> change, +1 for Fujii-san's close-in-reverse-order idea. We should >> reconsider that data structure for 12; there

Re: Speedup of relation deletes during recovery

On 2018-06-27 13:44:03 +1200, Thomas Munro wrote: > On further reflection, on the basis that it's the most conservative > change, +1 for Fujii-san's close-in-reverse-order idea. We should > reconsider that data structure for 12; there doesn't seems to be a > good reason to carry all those

Re: Speedup of relation deletes during recovery

On Wed, Jun 27, 2018 at 1:13 PM, Thomas Munro wrote: > On Wed, Jun 27, 2018 at 12:16 PM, Thomas Munro > wrote: >> I think we should take the hint in the comments and make it O(1) >> anyway. See attached draft patch. > > Alternatively, here is a shorter and sweeter dlist version (I did the >

Re: Speedup of relation deletes during recovery

On Wed, Jun 27, 2018 at 12:16 PM, Thomas Munro wrote: > I think we should take the hint in the comments and make it O(1) > anyway. See attached draft patch. Alternatively, here is a shorter and sweeter dlist version (I did the open-coded one thinking of theoretical back-patchability). --

Re: Speedup of relation deletes during recovery

On Fri, Jun 22, 2018 at 5:41 AM, Andres Freund wrote: > On 2018-06-21 14:40:58 +0900, Michael Paquier wrote: >> On Wed, Jun 20, 2018 at 08:43:11PM -0700, Andres Freund wrote: >> > On 2018-06-18 11:13:47 -0700, Andres Freund wrote: >> >> We could do that - but add_to_unowned_list() is actually a

Re: Speedup of relation deletes during recovery

On 2018-06-21 14:40:58 +0900, Michael Paquier wrote: > On Wed, Jun 20, 2018 at 08:43:11PM -0700, Andres Freund wrote: > > On 2018-06-18 11:13:47 -0700, Andres Freund wrote: > >> We could do that - but add_to_unowned_list() is actually a bottleneck in > >> other places during recovery too. We

Re: Speedup of relation deletes during recovery

On Wed, Jun 20, 2018 at 08:43:11PM -0700, Andres Freund wrote: > On 2018-06-18 11:13:47 -0700, Andres Freund wrote: >> We could do that - but add_to_unowned_list() is actually a bottleneck in >> other places during recovery too. We pretty much never (outside of >> dropping relations / databases)

Re: Speedup of relation deletes during recovery

On 2018-06-18 11:13:47 -0700, Andres Freund wrote: > On 2018-06-19 03:06:54 +0900, Fujii Masao wrote: > > On Sat, Jun 16, 2018 at 3:28 AM, Andres Freund wrote: > > > Hi, > > > > > > On 2018-06-15 10:45:04 -0700, Andres Freund wrote: > > >> > + > > >> > + srels = palloc(sizeof(SMgrRelation) *

Re: Speedup of relation deletes during recovery

On Tue, Jun 19, 2018 at 6:13 AM, Fujii Masao wrote: > On Sat, Jun 16, 2018 at 2:54 AM, Teodor Sigaev wrote: >>> We just had a customer hit this issue. I kind of wonder whether this >>> shouldn't be backpatched: Currently the execution on the primary is >>> O(NBuffers * log(ndrels)) whereas it's

Re: Speedup of relation deletes during recovery

On 2018-06-19 03:06:54 +0900, Fujii Masao wrote: > On Sat, Jun 16, 2018 at 3:28 AM, Andres Freund wrote: > > Hi, > > > > On 2018-06-15 10:45:04 -0700, Andres Freund wrote: > >> > + > >> > + srels = palloc(sizeof(SMgrRelation) * ndelrels); > >> > for (i = 0; i < ndelrels; i++) > >> > - { >

Re: Speedup of relation deletes during recovery

On Sat, Jun 16, 2018 at 2:54 AM, Teodor Sigaev wrote: >> We just had a customer hit this issue. I kind of wonder whether this >> shouldn't be backpatched: Currently the execution on the primary is >> O(NBuffers * log(ndrels)) whereas it's O(NBuffers * ndrels) on the >> standby - with a lot higher

Re: Speedup of relation deletes during recovery

On Sat, Jun 16, 2018 at 3:28 AM, Andres Freund wrote: > Hi, > > On 2018-06-15 10:45:04 -0700, Andres Freund wrote: >> > + >> > + srels = palloc(sizeof(SMgrRelation) * ndelrels); >> > for (i = 0; i < ndelrels; i++) >> > - { >> > - SMgrRelation srel = smgropen(delrels[i],

Re: Speedup of relation deletes during recovery

Hi, On 2018-06-15 10:45:04 -0700, Andres Freund wrote: > > + > > + srels = palloc(sizeof(SMgrRelation) * ndelrels); > > for (i = 0; i < ndelrels; i++) > > - { > > - SMgrRelation srel = smgropen(delrels[i], InvalidBackendId); > > + srels[i] = smgropen(delrels[i],

Re: Speedup of relation deletes during recovery

We just had a customer hit this issue. I kind of wonder whether this shouldn't be backpatched: Currently the execution on the primary is O(NBuffers * log(ndrels)) whereas it's O(NBuffers * ndrels) on the standby - with a lot higher constants to boot. That means it's very easy to get into

Re: Speedup of relation deletes during recovery

Hi, We just had a customer hit this issue. I kind of wonder whether this shouldn't be backpatched: Currently the execution on the primary is O(NBuffers * log(ndrels)) whereas it's O(NBuffers * ndrels) on the standby - with a lot higher constants to boot. That means it's very easy to get into

RE: Speedup of relation deletes during recovery

From: Fujii Masao [mailto:masao.fu...@gmail.com] > Yeah, it's worth working on this problem. To decrease the number of scans > of > shared_buffers, you would need to change the order of truncations of files > and > WAL logging. In RelationTruncate(), currently WAL is logged after FSM and > VM >

Re: Speedup of relation deletes during recovery

On Thu, Apr 19, 2018 at 01:52:26AM +0900, Fujii Masao wrote: > No. But after my colleague truncated more than one hundred tables on > the server with shared_buffers = 300GB, the recovery could not finish > even after 10 minutes since the startup of the recovery. So I had to > shutdown the server

Re: Speedup of relation deletes during recovery

On Wed, Apr 18, 2018 at 10:44 AM, Michael Paquier wrote: > On Wed, Apr 18, 2018 at 12:46:58AM +0900, Fujii Masao wrote: >> Yes, I think. And, I found that smgrdounlinkfork() is also dead code. >> Per the discussion [1], this unused function was left intentionally. >> But it's

Re: Speedup of relation deletes during recovery

On Wed, Apr 18, 2018 at 12:46:58AM +0900, Fujii Masao wrote: > Yes, I think. And, I found that smgrdounlinkfork() is also dead code. > Per the discussion [1], this unused function was left intentionally. > But it's still dead code since 2012, so I'd like to remove it. Patch attached. Indeed, it's

Re: Speedup of relation deletes during recovery

On Fri, Mar 30, 2018 at 11:46 AM, Michael Paquier wrote: > On Fri, Mar 30, 2018 at 11:19:58AM +0900, Kyotaro HORIGUCHI wrote: >> At Fri, 30 Mar 2018 08:31:29 +0900, Fujii Masao >> wrote in

RE: Speedup of relation deletes during recovery

From: Jerry Sievers [mailto:gsiever...@comcast.net] > Wonder if this is the case for streaming standbys replaying truncates > also? Yes, As I wrote in my previous mail, TRUNCATE is worse than DROP TABLE. Regards Takayuki Tsunakawa

Re: Speedup of relation deletes during recovery

Fujii Masao writes: > Hi, > > When multiple relations are deleted at the same transaction, > the files of those relations are deleted by one call to smgrdounlinkall(), > which leads to scan whole shared_buffers only one time. OTOH, > during recovery, smgrdounlink() (not

Re: Speedup of relation deletes during recovery

Fujii Masao writes: > Hi, > > When multiple relations are deleted at the same transaction, > the files of those relations are deleted by one call to smgrdounlinkall(), > which leads to scan whole shared_buffers only one time. OTOH, > during recovery, smgrdounlink() (not

RE: Speedup of relation deletes during recovery

From: Fujii Masao [mailto:masao.fu...@gmail.com] > When multiple relations are deleted at the same transaction, the files of > those relations are deleted by one call to smgrdounlinkall(), which leads > to scan whole shared_buffers only one time. OTOH, during recovery, > smgrdounlink() (not

Re: Speedup of relation deletes during recovery

On Fri, Mar 30, 2018 at 11:19:58AM +0900, Kyotaro HORIGUCHI wrote: > At Fri, 30 Mar 2018 08:31:29 +0900, Fujii Masao wrote > in >> When multiple relations are deleted at the same transaction, >> the files

Re: Speedup of relation deletes during recovery

Hello. At Fri, 30 Mar 2018 08:31:29 +0900, Fujii Masao wrote in > Hi, > > When multiple relations are deleted at the same transaction, > the files of those relations are deleted by one call to