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

Speedup of relation deletes during recovery

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 smgrdounlinkall()) is called for each file to delete,