Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-05-03 Thread Andres Freund
Hi, On 2023-05-03 19:29:46 +, Muhammad Malik wrote: > > I use a script like: > > > c=16;psql -c 'DROP TABLE IF EXISTS copytest_0; CREATE TABLE copytest_0(data > > text not null);' && time /srv/dev/build/m-opt/src/bin/pgbench/pgbench -n > > -P1 -c$c -j$c -t$((1024/$c)) -f ~/tmp/copy.sql && ps

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-05-03 Thread Muhammad Malik
> I use a script like: > c=16;psql -c 'DROP TABLE IF EXISTS copytest_0; CREATE TABLE copytest_0(data > text not null);' && time /srv/dev/build/m-opt/src/bin/pgbench/pgbench -n -P1 > -c$c -j$c -t$((1024/$c)) -f ~/tmp/copy.sql && psql -c 'TRUNCATE copytest_0' > >[1] COPY (SELECT repeat(random():

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-05-03 Thread Andres Freund
Hi, On 2023-05-03 18:25:51 +, Muhammad Malik wrote: > Could you please share repro steps for running these benchmarks? I am doing > performance testing in this area and want to use the same benchmarks. The email should contain all the necessary information. What are you missing? c=16;psql

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-05-03 Thread Muhammad Malik
; Thomas Munro ; Melanie Plageman Cc: Yura Sokolov ; Robert Haas Subject: refactoring relation extension and BufferAlloc(), faster COPY Hi, I'm working to extract independently useful bits from my AIO work, to reduce the size of that patchset. This is one of those pieces. In workloads

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-04-14 Thread Andres Freund
Hi, On 2023-04-12 08:00:00 +0300, Alexander Lakhin wrote: > 12.04.2023 02:21, Andres Freund wrote: > > Hi, > > > > On 2023-04-11 22:00:00 +0300, Alexander Lakhin wrote: > > > A few days later I've found a new defect introduced with 31966b151. > > That's the same issue that Tom also just reported,

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-04-11 Thread Alexander Lakhin
12.04.2023 02:21, Andres Freund wrote: Hi, On 2023-04-11 22:00:00 +0300, Alexander Lakhin wrote: A few days later I've found a new defect introduced with 31966b151. That's the same issue that Tom also just reported, at https://postgr.es/m/392271.1681238924%40sss.pgh.pa.us Attached is my WIP f

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-04-11 Thread Andres Freund
Hi, On 2023-04-11 22:00:00 +0300, Alexander Lakhin wrote: > A few days later I've found a new defect introduced with 31966b151. That's the same issue that Tom also just reported, at https://postgr.es/m/392271.1681238924%40sss.pgh.pa.us Attached is my WIP fix, including a test. Greetings, Andre

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-04-11 Thread Alexander Lakhin
Hi Andres, 07.04.2023 11:39, Andres Freund wrote: Hi, On 2023-04-06 18:15:14 -0700, Andres Freund wrote: I think it might be worth having a C test for some of the bufmgr.c API. Things like testing that retrying a failed relation extension works the second time round. A few hours after this I

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-04-07 Thread Andres Freund
Hi, On 2023-04-06 18:15:14 -0700, Andres Freund wrote: > I think it might be worth having a C test for some of the bufmgr.c API. Things > like testing that retrying a failed relation extension works the second time > round. A few hours after this I hit a stupid copy-pasto (21d7c05a5cf) that would

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-04-06 Thread Andres Freund
Hi, On 2023-04-05 18:46:16 -0700, Andres Freund wrote: > On 2023-04-04 17:39:45 -0700, Andres Freund wrote: > > After that I'm planning to wait for a buildfarm cycle, and push the changes > > necessary to use bulk extension in hio.c (the main win). > > Working on that. Might end up being tomorrow

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-04-05 Thread Andres Freund
Hi, On 2023-04-04 17:39:45 -0700, Andres Freund wrote: > I'm planning to push the patches up to the hio.c changes soon, unless somebody > would like me to hold off. Done that. > After that I'm planning to wait for a buildfarm cycle, and push the changes > necessary to use bulk extension in hio.

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-04-04 Thread John Naylor
On Wed, Apr 5, 2023 at 7:32 AM Andres Freund wrote: > > Portability nit: mdzeroextend() doesn't know whether posix_fallocate() was > > used in FileFallocate(). > > Fair point. I would however like to see a different error message for the two > ways of extending, at least initially. What about ref

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-04-04 Thread Andres Freund
Hi, On 2023-03-30 12:28:57 +0700, John Naylor wrote: > On Thu, Mar 30, 2023 at 10:02 AM Andres Freund wrote: > > Attached is v6. Changes: > > 0006: > > +ereport(ERROR, > +errcode_for_file_access(), > +errmsg("could not extend file \"%s\" with posix_fallocate(): >

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-03-29 Thread John Naylor
On Thu, Mar 30, 2023 at 10:02 AM Andres Freund wrote: > Attached is v6. Changes: 0006: +ereport(ERROR, +errcode_for_file_access(), +errmsg("could not extend file \"%s\" with posix_fallocate(): %m", + FilePathName(v->mdfd_vfd)), +errhint

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-03-29 Thread Andres Freund
Hi, On 2023-03-29 20:51:04 -0400, Melanie Plageman wrote: > > > +if (returnCode == 0) > > > +return 0; > > > + > > > +/* for compatibility with %m printing etc */ > > > +errno = returnCode; > > > + > > > +/* > > > +* Return in cases of a "real" failure, if fallocate is

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-03-29 Thread Melanie Plageman
On Tue, Mar 28, 2023 at 11:47 PM Andres Freund wrote: > > On 2023-03-26 17:42:45 -0400, Melanie Plageman wrote: > > +{ > > +/* if errno is unset, assume problem is no disk space */ > > +if (errno == 0) > > +errno = ENOSPC; > > +return -1; > > +} > > > >

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-03-28 Thread Andres Freund
Hi, On 2023-03-27 15:32:47 +0900, Kyotaro Horiguchi wrote: > At Sun, 26 Mar 2023 12:26:59 -0700, Andres Freund wrote > in > > Hi, > > > > Attached is v5. Lots of comment polishing, a bit of renaming. I extracted > > the > > relation extension related code in hio.c back into its own function. >

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-03-28 Thread Andres Freund
Hi, On 2023-03-26 17:42:45 -0400, Melanie Plageman wrote: > Below is my review of a slightly older version than you just posted -- > much of it you may have already addressed. Far from all is already - thanks for the review! > From 3a6c3f41000e057bae12ab4431e6bb1c5f3ec4b0 Mon Sep 17 00:00:00 20

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-03-26 Thread Kyotaro Horiguchi
At Sun, 26 Mar 2023 12:26:59 -0700, Andres Freund wrote in > Hi, > > Attached is v5. Lots of comment polishing, a bit of renaming. I extracted the > relation extension related code in hio.c back into its own function. > > While reviewing the hio.c code, I did realize that too much stuff is done

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-03-26 Thread Melanie Plageman
Hi, Below is my review of a slightly older version than you just posted -- much of it you may have already addressed. >From 3a6c3f41000e057bae12ab4431e6bb1c5f3ec4b0 Mon Sep 17 00:00:00 2001 From: Andres Freund Date: Mon, 20 Mar 2023 21:57:40 -0700 Subject: [PATCH v5 01/15] createdb-using-wal-fix

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-03-24 Thread Andres Freund
Hi, On 2022-10-28 19:54:20 -0700, Andres Freund wrote: > I've done a fair bit of benchmarking of this patchset. For COPY it comes out > ahead everywhere. It's possible that there's a very small regression for > extremly IO miss heavy workloads, more below. > > > server "base" configuration: > >

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-03-01 Thread Heikki Linnakangas
On 01/03/2023 09:33, Andres Freund wrote: On 2023-02-21 17:33:31 +0100, Alvaro Herrera wrote: On 2023-Feb-21, Heikki Linnakangas wrote: +static BlockNumber +BulkExtendSharedRelationBuffered(Relation rel, +SMgrRelation smgr, +

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-03-01 Thread Andres Freund
Hi, On 2023-03-01 09:02:00 -0800, Andres Freund wrote: > On 2023-03-01 11:12:35 +0200, Heikki Linnakangas wrote: > > On 27/02/2023 23:45, Andres Freund wrote: > > > But, uh, isn't this code racy? Because this doesn't go through shared > > > buffers, > > > there's no IO_IN_PROGRESS interlocking ag

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-03-01 Thread Andres Freund
Hi, On 2023-03-01 11:12:35 +0200, Heikki Linnakangas wrote: > On 27/02/2023 23:45, Andres Freund wrote: > > But, uh, isn't this code racy? Because this doesn't go through shared > > buffers, > > there's no IO_IN_PROGRESS interlocking against a concurrent reader. We know > > that writing pages isn

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-03-01 Thread Heikki Linnakangas
On 27/02/2023 23:45, Andres Freund wrote: On 2023-02-27 18:06:22 +0200, Heikki Linnakangas wrote: We also do this in freespace.c and visibilitymap.c: /* Extend as needed. */ while (fsm_nblocks_now < fsm_nblocks) { PageSetChecksumInplace((Page) pg.data, fsm_nblocks_now);

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-02-28 Thread Andres Freund
Hi, On 2023-02-21 17:33:31 +0100, Alvaro Herrera wrote: > On 2023-Feb-21, Heikki Linnakangas wrote: > > > > +static BlockNumber > > > +BulkExtendSharedRelationBuffered(Relation rel, > > > + SMgrRelation > > > smgr, > > > +

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-02-27 Thread Andres Freund
Hi, On 2023-02-27 18:06:22 +0200, Heikki Linnakangas wrote: > We also do this in freespace.c and visibilitymap.c: > > /* Extend as needed. */ > while (fsm_nblocks_now < fsm_nblocks) > { > PageSetChecksumInplace((Page) pg.data, fsm_nblocks_now); > > smgrextend(reln, FS

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-02-27 Thread Heikki Linnakangas
On 21/02/2023 18:33, Alvaro Herrera wrote: On 2023-Feb-21, Heikki Linnakangas wrote: +static BlockNumber +BulkExtendSharedRelationBuffered(Relation rel, +SMgrRelation smgr, +

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-02-22 Thread Andres Freund
Hi, On 2023-02-21 11:22:26 -0800, Andres Freund wrote: > On 2023-02-21 18:18:02 +0200, Heikki Linnakangas wrote: > > Do other ReadBufferModes than RBM_ZERO_AND_LOCK make sense with > > ExtendRelationBuffered? > > Hm. That's a a good point. Probably not. Perhaps it could be useful to support > RBM

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-02-22 Thread Andres Freund
Hi, On 2023-02-22 11:18:57 +0200, Heikki Linnakangas wrote: > On 21/02/2023 21:22, Andres Freund wrote: > > On 2023-02-21 18:18:02 +0200, Heikki Linnakangas wrote: > > > Is it ever possible to call this without a relcache entry? WAL redo > > > functions do that with ReadBuffer, but they only exten

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-02-22 Thread Heikki Linnakangas
On 21/02/2023 21:22, Andres Freund wrote: On 2023-02-21 18:18:02 +0200, Heikki Linnakangas wrote: Is it ever possible to call this without a relcache entry? WAL redo functions do that with ReadBuffer, but they only extend a relation implicitly, by replay a record for a particular block. I thin

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-02-21 Thread Jim Nasby
On 2/21/23 3:12 PM, Andres Freund wrote: CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you can confirm the sender and know the content is safe. Hi, On 2023-02-21 15:00:15 -0600, Jim Nasby wrote: Some food for thought: I thi

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-02-21 Thread Andres Freund
Hi, On 2023-02-21 15:00:15 -0600, Jim Nasby wrote: > On 10/28/22 9:54 PM, Andres Freund wrote: > > b) I found that is quite beneficial to bulk-extend the relation with > > smgrextend() even without concurrency. The reason for that is the > > primarily > > the aforementioned dirty buffers

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-02-21 Thread Jim Nasby
On 10/28/22 9:54 PM, Andres Freund wrote: b) I found that is quite beneficial to bulk-extend the relation with smgrextend() even without concurrency. The reason for that is the primarily the aforementioned dirty buffers that our current extension method causes. One bit that stumped m

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-02-21 Thread Andres Freund
Hi, On 2023-02-21 18:18:02 +0200, Heikki Linnakangas wrote: > > v2-0007-bufmgr-Move-relation-extension-handling-into-Bulk.patch > > > +static BlockNumber > > +BulkExtendSharedRelationBuffered(Relation rel, > > +SMgrRelation > > smgr, >

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-02-21 Thread Andres Freund
Hi, On 2023-02-21 17:40:31 +0200, Heikki Linnakangas wrote: > > v2-0006-bufmgr-Support-multiple-in-progress-IOs-by-using-.patch > > This looks straightforward. My only concern is that it changes the order > that things happen at abort. Currently, AbortBufferIO() is called very early > in AbortTra

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-02-21 Thread Alvaro Herrera
On 2023-Feb-21, Heikki Linnakangas wrote: > > +static BlockNumber > > +BulkExtendSharedRelationBuffered(Relation rel, > > +SMgrRelation > > smgr, > > +bool > > skip_extension_l

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-02-21 Thread Heikki Linnakangas
v2-0007-bufmgr-Move-relation-extension-handling-into-Bulk.patch +static BlockNumber +BulkExtendSharedRelationBuffered(Relation rel, +SMgrRelation smgr, +bool skip_ex

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-02-21 Thread Heikki Linnakangas
v2-0006-bufmgr-Support-multiple-in-progress-IOs-by-using-.patch This looks straightforward. My only concern is that it changes the order that things happen at abort. Currently, AbortBufferIO() is called very early in AbortTransaction(), and this patch moves it much later. I don't see any imme

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-02-21 Thread Heikki Linnakangas
On 11/02/2023 23:36, Andres Freund wrote: On 2023-02-11 23:03:56 +0200, Heikki Linnakangas wrote: * I don't understand this comment: /* * Clear out the buffer's tag and flags and usagecount. We must do * this to ensure that linear scans of the buffer array don't thin

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-02-11 Thread Andres Freund
Hi, On 2023-02-11 14:25:06 -0800, Andres Freund wrote: > On 2023-01-20 13:40:55 +1300, David Rowley wrote: > > v2-0004: > > > > 5. Is it worth having two versions of PinLocalBuffer() one to adjust > > the usage count and one that does not? Couldn't the version that does > > not adjust the count s

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-02-11 Thread Andres Freund
Hi, On 2023-01-20 13:40:55 +1300, David Rowley wrote: > On Tue, 10 Jan 2023 at 15:08, Andres Freund wrote: > > Thanks for letting me now. Updated version attached. > > I'm not too sure I've qualified for giving a meaningful design review > here, but I have started looking at the patches and so f

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-02-11 Thread Andres Freund
Hi, On 2023-02-10 18:38:50 +0200, Heikki Linnakangas wrote: > I'll continue reviewing this, but here's some feedback on the first two > patches: > > v2-0001-aio-Add-some-error-checking-around-pinning.patch: > > I wonder if the extra assertion in LockBufHdr() is worth the overhead. It > won't add

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-02-11 Thread Andres Freund
On 2023-02-11 13:36:51 -0800, Andres Freund wrote: > Even though it's not a correctness issue, it seems to me that > DropRelationsAllBuffers() etc ought to check if the buffer is BM_TAG_VALID, > before doing anything further. Particularly in DropRelationsAllBuffers(), the > check we do for each bu

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-02-11 Thread Andres Freund
Hi, On 2023-02-11 23:03:56 +0200, Heikki Linnakangas wrote: > > v2-0005-bufmgr-Acquire-and-clean-victim-buffer-separately.patch > This can be applied separately from the rest of the patches, which is nice. > Some small comments on it: Thanks for looking at these! > * Needs a rebase, it conflict

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-02-11 Thread Heikki Linnakangas
v2-0005-bufmgr-Acquire-and-clean-victim-buffer-separately.patch This can be applied separately from the rest of the patches, which is nice. Some small comments on it: * Needs a rebase, it conflicted slightly with commit f30d62c2fc. * GetVictimBuffer needs a comment to explain what it does. In

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-02-10 Thread Heikki Linnakangas
I'll continue reviewing this, but here's some feedback on the first two patches: v2-0001-aio-Add-some-error-checking-around-pinning.patch: I wonder if the extra assertion in LockBufHdr() is worth the overhead. It won't add anything without assertions, of course, but still. No objections if yo

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-01-19 Thread David Rowley
On Tue, 10 Jan 2023 at 15:08, Andres Freund wrote: > Thanks for letting me now. Updated version attached. I'm not too sure I've qualified for giving a meaningful design review here, but I have started looking at the patches and so far only made it as far as 0006. I noted down the following while

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-01-09 Thread Andres Freund
Hi, On 2023-01-06 11:52:04 +0530, vignesh C wrote: > On Sat, 29 Oct 2022 at 08:24, Andres Freund wrote: > > > > The patches here aren't fully polished (as will be evident). But they should > > be more than good enough to discuss whether this is a sane direction. > > The patch does not apply on t

Re: refactoring relation extension and BufferAlloc(), faster COPY

2023-01-05 Thread vignesh C
On Sat, 29 Oct 2022 at 08:24, Andres Freund wrote: > > The patches here aren't fully polished (as will be evident). But they should > be more than good enough to discuss whether this is a sane direction. The patch does not apply on top of HEAD as in [1], please post a rebased patch: === Applying

Re: refactoring relation extension and BufferAlloc(), faster COPY

2022-10-29 Thread Andres Freund
Hi, On 2022-10-29 18:33:53 +0530, Bharath Rupireddy wrote: > On Sat, Oct 29, 2022 at 8:24 AM Andres Freund wrote: > > > > Hi, > > > > I'm working to extract independently useful bits from my AIO work, to reduce > > the size of that patchset. This is one of those pieces. > > Thanks a lot for this

Re: refactoring relation extension and BufferAlloc(), faster COPY

2022-10-29 Thread Bharath Rupireddy
On Sat, Oct 29, 2022 at 8:24 AM Andres Freund wrote: > > Hi, > > I'm working to extract independently useful bits from my AIO work, to reduce > the size of that patchset. This is one of those pieces. Thanks a lot for this great work. There are 12 patches in this thread, I believe each of these pa