Re: Using old master as new replica after clean switchover

On Fri, Feb 22, 2019 at 5:47 AM Jehan-Guillaume de Rorthais wrote: > > On Thu, 21 Feb 2019 15:38:21 -0300 > Claudio Freire wrote: > > > On Tue, Feb 19, 2019 at 9:44 PM Michael Paquier wrote: > > > > > > On Tue, Feb 19, 2019 at 04:27:02PM -0800, RSR9

Re: Using old master as new replica after clean switchover

On Tue, Feb 19, 2019 at 9:44 PM Michael Paquier wrote: > > On Tue, Feb 19, 2019 at 04:27:02PM -0800, RSR999GMAILCOM wrote: > > So wanted to clarify if this procedure really requires the WAL archive > > location on a shared storage ? > > Shared storage for WAL archives is not a requirement. It

Re: Vacuum: allow usage of more than 1GB of work mem

On Mon, Jul 16, 2018 at 3:30 PM Andrew Dunstan wrote: > > > > On 07/16/2018 10:34 AM, Claudio Freire wrote: > > On Fri, Jul 13, 2018 at 5:43 PM Andrew Dunstan > > wrote: > >> > >> > >> On 07/13/2018 09:44 AM, Heikki Linnakangas wrote: > >

Re: Vacuum: allow usage of more than 1GB of work mem

On Mon, Jul 16, 2018 at 11:34 AM Claudio Freire wrote: > > On Fri, Jul 13, 2018 at 5:43 PM Andrew Dunstan > wrote: > > > > > > > > On 07/13/2018 09:44 AM, Heikki Linnakangas wrote: > > > On 13/07/18 01:39, Andrew Dunstan wrote: > > >> On 07/1

Re: Vacuum: allow usage of more than 1GB of work mem

On Fri, Jul 13, 2018 at 5:43 PM Andrew Dunstan wrote: > > > > On 07/13/2018 09:44 AM, Heikki Linnakangas wrote: > > On 13/07/18 01:39, Andrew Dunstan wrote: > >> On 07/12/2018 06:34 PM, Alvaro Herrera wrote: > >>> On 2018-Jul-12, Andrew Dunstan wrote: > >>> > I fully understand. I think this

Re: Vacuum: allow usage of more than 1GB of work mem

On Thu, Jul 12, 2018 at 10:44 AM Andrew Dunstan wrote: > > > > On 04/06/2018 08:00 PM, Claudio Freire wrote: > > On Fri, Apr 6, 2018 at 5:25 PM, Claudio Freire > > wrote: > >> On Fri, Apr 6, 2018 at 10:39 AM, Heikki Linnakangas > >> wrote: >

Re: [WIP] [B-Tree] Retail IndexTuple deletion

On Mon, Jun 18, 2018 at 4:59 PM Peter Geoghegan wrote: > > Note, guaranteed allowable time of index scans (used for quick deletion) > > will be achieved by storing equal-key index tuples in physical TID order [2] > > with patch [3]. > > I now have greater motivation to develop that patch into a

Re: Making all nbtree entries unique by having heap TIDs participate in comparisons

On Mon, Jun 18, 2018 at 2:03 PM Peter Geoghegan wrote: > > On Mon, Jun 18, 2018 at 7:57 AM, Claudio Freire > wrote: > > Way back when I was dabbling in this kind of endeavor, my main idea to > > counteract that, and possibly improve performance overall, was a > >

Re: Making all nbtree entries unique by having heap TIDs participate in comparisons

On Fri, Jun 15, 2018 at 8:47 PM Peter Geoghegan wrote: > > I think it would be helpful if you could talk more about these > > regressions (and the wins). > > I think that the performance regressions are due to the fact that when > you have a huge number of duplicates today, it's useful to be

Re: Locking B-tree leafs immediately in exclusive mode

On Thu, Jun 14, 2018 at 9:44 AM Alexander Korotkov wrote: > > > Our B-tree is currently maintaining duplicates unordered. So, during > > > insertion > > > we can traverse rightlinks in order to find page, which would fit new > > > index tuple. > > > However, in this case we're traversing pages

Re: Spilling hashed SetOps and aggregates to disk

On Tue, Jun 5, 2018 at 5:05 AM Tomas Vondra wrote: > > On 06/05/2018 07:46 AM, Jeff Davis wrote: > > On Tue, 2018-06-05 at 07:04 +0200, Tomas Vondra wrote: > >> I expect the eviction strategy to be the primary design challenge of > >> this patch. The other bits will be mostly determined by this

Re: POC: GROUP BY optimization

On Wed, Jun 6, 2018 at 8:06 PM Tomas Vondra wrote: > >>> Comparison cost can be approximated probabilistically as: > >>> > >>> cost_comp = sum(cost_op_n * (1.0 / ndistinct(col_1_to_n))) > >>> > >>> Where cost_op_n is the cost of the comparison function for column N, > >>> and

Re: POC: GROUP BY optimization

On Wed, Jun 6, 2018 at 7:18 PM Claudio Freire wrote: > > > Comparison cost can be approximated probabilistically as: > > > > > > cost_comp = sum(cost_op_n * (1.0 / ndistinct(col_1_to_n))) > > > > > > Where cost_op_n is the cost of the comparis

Re: POC: GROUP BY optimization

On Wed, Jun 6, 2018 at 6:57 PM Tomas Vondra wrote: > > On 06/06/2018 11:22 PM, Claudio Freire wrote: > > On Wed, Jun 6, 2018 at 5:43 PM Tomas Vondra > > As such, estimating sort performance correctly in the various plan > > variants being considered seems to

Re: POC: GROUP BY optimization

On Wed, Jun 6, 2018 at 5:43 PM Tomas Vondra wrote: > > For example, it seems to disregard that different data types have > different comparison costs. For example comparing bytea will be far > more expensive compared to int4, so it may be much more efficient to > compare int4

Re: Faster inserts with mostly-monotonically increasing values

On Tue, Apr 10, 2018 at 11:10 AM, Heikki Linnakangas wrote: >> /* XLOG stuff */ >> if (RelationNeedsWAL(rel)) >> { >> ... >> >> if (P_ISLEAF(lpageop)) >> { >>

Re: Vacuum: allow usage of more than 1GB of work mem

On Fri, Apr 6, 2018 at 5:25 PM, Claudio Freire <klaussfre...@gmail.com> wrote: > On Fri, Apr 6, 2018 at 10:39 AM, Heikki Linnakangas <hlinn...@iki.fi> wrote: >> On 06/04/18 01:59, Claudio Freire wrote: >>> >>> The iteration interface, however, se

Re: Vacuum: allow usage of more than 1GB of work mem

On Thu, Apr 5, 2018 at 5:02 PM, Heikki Linnakangas <hlinn...@iki.fi> wrote: > On 03/04/18 17:20, Claudio Freire wrote: >> >> Ok, rebased patches attached > > > Thanks! I took a look at this. > > First, now that the data structure is more complicated, I think

Re: Vacuum: allow usage of more than 1GB of work mem

On Tue, Apr 3, 2018 at 11:09 AM, Claudio Freire <klaussfre...@gmail.com> wrote: > On Tue, Apr 3, 2018 at 11:06 AM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> I didn't receive your comment, I just saw it. Nevertheless, I rebased the >> patches a wh

Re: Vacuum: allow usage of more than 1GB of work mem

On Tue, Apr 3, 2018 at 11:06 AM, Claudio Freire <klaussfre...@gmail.com> wrote: > I didn't receive your comment, I just saw it. Nevertheless, I rebased the > patches a while ago just because I noticed they didn't apply anymore in > cputube, and they still seem to apply. Sorry

Re: Vacuum: allow usage of more than 1GB of work mem

I didn't receive your comment, I just saw it. Nevertheless, I rebased the patches a while ago just because I noticed they didn't apply anymore in cputube, and they still seem to apply. Patch number 2 was committed a long while ago, that's why it's missing. It was a simple patch, it landed

Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently

On Tue, Apr 3, 2018 at 10:19 AM, Claudio Freire <klaussfre...@gmail.com> wrote: > On Thu, Mar 29, 2018 at 2:09 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: >> Claudio Freire <klaussfre...@gmail.com> writes: >>> On Wed, Mar 28, 2018 at 6:54 PM, Tom Lane <t..

Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently

On Thu, Mar 29, 2018 at 7:55 PM, Tom Lane wrote: > I wrote: >> I have to go do something >> else right now, but I'll take a closer look at 0004 later. > > OK, so after studying 0004, it seems to me that we could do it more > simply as attached; that is, move the

Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently

On Wed, Mar 28, 2018 at 6:54 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Claudio Freire <klaussfre...@gmail.com> writes: >> Attached patches, rebased and modified as discussed: >> 1 no longer does tree pruning, it just vacuums a range of the FSM >> 2 reintroduces

Re: Index scan prefetch?

On Mon, Mar 26, 2018 at 4:59 PM, Justin Pryzby wrote: >> But now effective_io_concurrency parameter is applicable only for bitmap > ... >> Will it be useful to support it also for index scan? >> Or there are some other ways to address this problem? > > Does your case perform

Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently

On Mon, Mar 26, 2018 at 2:46 PM, Claudio Freire <klaussfre...@gmail.com> wrote: > On Mon, Mar 26, 2018 at 11:26 AM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> On Mon, Mar 26, 2018 at 11:19 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: >>> Claudio

Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently

On Mon, Mar 26, 2018 at 11:26 AM, Claudio Freire <klaussfre...@gmail.com> wrote: > On Mon, Mar 26, 2018 at 11:19 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: >> Claudio Freire <klaussfre...@gmail.com> writes: >>> On Sat, Mar 24, 2018 at 4:17 PM, Tom Lane <t...@s

Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently

On Sat, Mar 24, 2018 at 4:17 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Claudio Freire <klaussfre...@gmail.com> writes: >> [ 0001-Vacuum-Update-FSM-more-frequently-v9.patch ] > > I hadn't paid any attention to this patch previously, so maybe I'm > missing somethin

Re: Faster inserts with mostly-monotonically increasing values

On Thu, Mar 22, 2018 at 3:29 AM, Pavan Deolasee <pavan.deola...@gmail.com> wrote: > > On Thu, Mar 22, 2018 at 7:22 AM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> >> >> If you're not planning on making any further changes, would you mind >> p

Re: Faster inserts with mostly-monotonically increasing values

On Mon, Mar 19, 2018 at 12:06 PM, Claudio Freire <klaussfre...@gmail.com> wrote: > On Mon, Mar 19, 2018 at 11:57 AM, Pavan Deolasee > <pavan.deola...@gmail.com> wrote: >> >> >> On Thu, Mar 15, 2018 at 7:51 AM, Claudio Freire <klaussfre...@gmail.com> >&g

Re: Faster inserts with mostly-monotonically increasing values

On Mon, Mar 19, 2018 at 11:57 AM, Pavan Deolasee <pavan.deola...@gmail.com> wrote: > > > On Thu, Mar 15, 2018 at 7:51 AM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> >> >> >> I applied the attached patches on top of your patch, so it would be

Re: [HACKERS] GUC for cleanup indexes threshold.

On Mon, Mar 19, 2018 at 8:50 AM, Masahiko Sawada wrote: >>> require the bulk-delete method of scanning whole index and of logging >>> WAL. But it leads some extra overhead. With this patch we no longer >>> need to depend on the full scan on b-tree index. This might be

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Fri, Feb 9, 2018 at 1:05 PM, Claudio Freire <klaussfre...@gmail.com> wrote: > Turns out that it was a tad oversized. 300k tuples seems enough. > > Attached is a new patch version that: > > - Uses an unlogged table to make the large mwm test faster > - Uses a wait_b

Re: Faster inserts with mostly-monotonically increasing values

On Wed, Mar 14, 2018 at 12:05 PM, Claudio Freire <klaussfre...@gmail.com> wrote: > On Wed, Mar 14, 2018 at 1:36 AM, Pavan Deolasee > <pavan.deola...@gmail.com> wrote: >> >> >> On Sun, Mar 11, 2018 at 9:18 PM, Claudio Freire <klaussfre...@gmail.com> >>

Re: Faster inserts with mostly-monotonically increasing values

On Wed, Mar 14, 2018 at 1:36 AM, Pavan Deolasee <pavan.deola...@gmail.com> wrote: > > > On Sun, Mar 11, 2018 at 9:18 PM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> >> On Sun, Mar 11, 2018 at 2:27 AM, Pavan Deolasee >> >> > >> &g

Re: Faster inserts with mostly-monotonically increasing values

On Wed, Mar 14, 2018 at 10:51 AM, Simon Riggs wrote: > If there is enough delay in step 1 then any benefit is lost anyway, so > there is no point taking the cached path even when successful, so its > better to ignore in that case. I find myself agreeing with that.

Re: Faster inserts with mostly-monotonically increasing values

On Wed, Mar 14, 2018 at 1:36 AM, Pavan Deolasee <pavan.deola...@gmail.com> wrote: > > > On Sun, Mar 11, 2018 at 9:18 PM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> >> On Sun, Mar 11, 2018 at 2:27 AM, Pavan Deolasee >> >> > >> &g

Re: Faster inserts with mostly-monotonically increasing values

On Sun, Mar 11, 2018 at 2:27 AM, Pavan Deolasee <pavan.deola...@gmail.com> wrote: > > > On Sat, Mar 10, 2018 at 12:11 AM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> >> On Fri, Mar 9, 2018 at 2:54 PM, Pavan Deolasee <pavan.deola...@gmail.com&g

Re: Faster inserts with mostly-monotonically increasing values

t.ie> wrote: >>> >>> On Mon, Mar 5, 2018 at 5:48 PM, Claudio Freire <klaussfre...@gmail.com> >>> wrote: >>> >>> > I believe PKs are a prime candidate for this optimization, and >>> > expecting it to apply only when no concurrency is

Re: disable SSL compression?

On Thu, Mar 8, 2018 at 11:06 PM, Peter Eisentraut <peter.eisentr...@2ndquadrant.com> wrote: > On 3/8/18 14:23, Claudio Freire wrote: >> On Thu, Mar 8, 2018 at 3:40 PM, Peter Eisentraut >> <peter.eisentr...@2ndquadrant.com> wrote: >>> It appears that SS

Re: disable SSL compression?

On Thu, Mar 8, 2018 at 3:40 PM, Peter Eisentraut wrote: > It appears that SSL compression is nowadays deprecated as insecure. > Yet, it is still enabled by libpq by default, and there is no way to > disable it in the server. Should we make some changes here?

Re: Faster inserts with mostly-monotonically increasing values

On Tue, Mar 6, 2018 at 9:06 AM, Simon Riggs wrote: >> Simon had raised concerns about DESC indexes and whether we need to do the >> checks for leftmost page in that case. I haven't yet figured out if DESC >> indexes are actually stored in the reverse order. I am gonna

Re: Faster inserts with mostly-monotonically increasing values

On Tue, Mar 6, 2018 at 1:45 AM, Peter Geoghegan <p...@bowt.ie> wrote: > On Mon, Mar 5, 2018 at 7:10 PM, Claudio Freire <klaussfre...@gmail.com> wrote: >>> Introducing any case that allows us to land on a recycled page, and >>> reason that it must at least not be t

Re: Faster inserts with mostly-monotonically increasing values

On Mon, Mar 5, 2018 at 10:59 PM, Peter Geoghegan <p...@bowt.ie> wrote: > On Mon, Mar 5, 2018 at 5:48 PM, Claudio Freire <klaussfre...@gmail.com> wrote: >> But in thise case there's no right link to follow, so it's a non-issue. >> >> BTree doesn't truncate AFAIK,

Re: Faster inserts with mostly-monotonically increasing values

On Mon, Mar 5, 2018 at 9:52 PM, Peter Geoghegan <p...@bowt.ie> wrote: > On Mon, Mar 5, 2018 at 4:37 PM, Claudio Freire <klaussfre...@gmail.com> wrote: >> Correct me if I'm wrong, but there's lots of code in nbtree already >> that risks reading recycled pages for

Re: Faster inserts with mostly-monotonically increasing values

On Mon, Mar 5, 2018 at 9:37 PM, Claudio Freire <klaussfre...@gmail.com> wrote: > Assuming the rightmost page is the first page the value could be on, > it already does get an exclusive buffer lock. That made me check, and: +/* + * Acquire exclusive lock on the buffer

Re: Faster inserts with mostly-monotonically increasing values

On Mon, Mar 5, 2018 at 9:12 PM, Peter Geoghegan <p...@bowt.ie> wrote: > On Thu, Mar 1, 2018 at 3:18 PM, Claudio Freire <klaussfre...@gmail.com> wrote: >> The key there is strictly greater than the rightmost key. As such, it >> must precede the first page with a

Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently

On Sun, Mar 4, 2018 at 10:25 PM, Masahiko Sawada <sawada.m...@gmail.com> wrote: > On Mon, Mar 5, 2018 at 10:21 AM, Masahiko Sawada <sawada.m...@gmail.com> > wrote: >> On Fri, Mar 2, 2018 at 10:50 PM, Claudio Freire <klaussfre...@gmail.com> >> wrote: >>

Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently

On Fri, Mar 2, 2018 at 10:47 AM, Claudio Freire <klaussfre...@gmail.com> wrote: > On Fri, Mar 2, 2018 at 7:38 AM, Masahiko Sawada <sawada.m...@gmail.com> wrote: >> Thank you for updating the patches! >> >> +/* >> + * When a table has no indexes, vacuum the F

Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently

tups_vacuumed, > nkeep, > > Regarding fsm_updated_pages variable name, I think it's better to be > freespace_updated_pages because we actually counts the page updated > its freespace, not fsm. Good point. New version attached. From bc79add4171beb1c8a0a714436cb4d7c27d6a2fc Mon Sep 17 00

Re: Faster inserts with mostly-monotonically increasing values

On Sun, Dec 31, 2017 at 8:06 AM, Peter Geoghegan wrote: > I also have my > doubts about unique index enforcement remaining correct with the patch > when there are many physical duplicates, to the extent that more than > a single leaf page is needed for a single value. given... +

Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently

to nbtree's bulkdelete implementation. Seems to be the best place to add such a thing. GIN and GIST don't delete pages until vacuumcleanup, so they can't do the same, sadly. From 505f3143f85d42cea5adf6f04332443a61edcac0 Mon Sep 17 00:00:00 2001 From: Claudio Freire <klaussfre...@gmail.com> Date

Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently

For 0003 patch, I think fsm_set() function name could lead misreading > because it actually not only sets the value but also returns the > value. fsm_set_and_get() might be better? Attached is the patchset modified as requested From bc79add4171beb1c8a0a714436cb4d7c27d6a2fc Mon Sep 17 00:

Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently

On Mon, Feb 26, 2018 at 1:32 PM, Claudio Freire <klaussfre...@gmail.com> wrote: > On Mon, Feb 26, 2018 at 11:31 AM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> On Mon, Feb 26, 2018 at 6:00 AM, Masahiko Sawada <sawada.m...@gmail.com> >> wrote: >

Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently

On Mon, Feb 26, 2018 at 11:31 AM, Claudio Freire <klaussfre...@gmail.com> wrote: > On Mon, Feb 26, 2018 at 6:00 AM, Masahiko Sawada <sawada.m...@gmail.com> > wrote: >> Here is review comment for v4 patch. >> >> @@ -1922,6 +1988,8 @@ count_nondeletable_

Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently

On Mon, Feb 26, 2018 at 11:31 AM, Claudio Freire <klaussfre...@gmail.com> wrote: >> >> + /* >> +* If there are no indexes then we should periodically >> vacuum the FSM >> +* on huge relati

Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently

On Mon, Feb 26, 2018 at 6:00 AM, Masahiko Sawada wrote: > Here is review comment for v4 patch. > > @@ -1922,6 +1988,8 @@ count_nondeletable_pages(Relation onerel, > LVRelStats *vacrelstats) > * We don't insert a vacuum delay point here, because we have >

Re: Hash Joins vs. Bloom Filters / take 2

On Thu, Feb 22, 2018 at 5:11 PM, Tomas Vondra <tomas.von...@2ndquadrant.com> wrote: > On 02/22/2018 08:33 PM, Claudio Freire wrote: >> That's kinda slow to do per-item. I tried to "count" distinct items by >> checking the BF before adding (don't add redundantly),

Re: Hash Joins vs. Bloom Filters / take 2

On Thu, Feb 22, 2018 at 12:45 PM, Tomas Vondra <tomas.von...@2ndquadrant.com> wrote: > > > On 02/22/2018 12:44 PM, Claudio Freire wrote: >> Let me reiterate, you can avoid both issues with scalable bloom filters[1]. >> > > I'm afraid it's not as straight-forwa

Re: Hash Joins vs. Bloom Filters / take 2

On Wed, Feb 21, 2018 at 11:21 PM, Tomas Vondra wrote: > On 02/21/2018 02:10 AM, Peter Geoghegan wrote: >> On Tue, Feb 20, 2018 at 3:54 PM, Tomas Vondra >> wrote: I suspect that it could make sense to use a Bloom filter to

Re: Hash Joins vs. Bloom Filters / take 2

On Tue, Feb 20, 2018 at 8:23 PM, Peter Geoghegan <p...@bowt.ie> wrote: > On Tue, Feb 20, 2018 at 3:17 PM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> I've worked a lot with bloom filters, and for large false positive >> rates and large sets (multi-million

Re: Hash Joins vs. Bloom Filters / take 2

On Tue, Feb 20, 2018 at 8:06 PM, Peter Geoghegan wrote: > You should try to exploit the fact that a Bloom filter can summarize a > large set reasonably well with a very compact, simple representation. > A false positive rate of 10% sounds a lot worse than 1% or 0.1%, but > for cases

Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently

On Thu, Feb 15, 2018 at 4:47 PM, Claudio Freire <klaussfre...@gmail.com> wrote: > On Wed, Feb 14, 2018 at 3:59 AM, Masahiko Sawada <sawada.m...@gmail.com> > wrote: >>> The final FSM vacuum pass isn't partial, to finally correct all those >>> small inconsi

Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently

On Wed, Feb 14, 2018 at 3:59 AM, Masahiko Sawada <sawada.m...@gmail.com> wrote: > On Fri, Feb 9, 2018 at 11:48 PM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> On Fri, Feb 9, 2018 at 1:36 AM, Masahiko Sawada <sawada.m...@gmail.com> >> wrote: >>

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Fri, Feb 9, 2018 at 10:32 AM, Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote: > Claudio Freire wrote: >> On Thu, Feb 8, 2018 at 8:39 PM, Alvaro Herrera <alvhe...@alvh.no-ip.org> >> wrote: > >> During the process of developing the patch, I got seriously

Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently

On Fri, Feb 9, 2018 at 1:36 AM, Masahiko Sawada <sawada.m...@gmail.com> wrote: > On Fri, Feb 9, 2018 at 12:45 AM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> On Thu, Feb 8, 2018 at 1:36 AM, Masahiko Sawada <sawada.m...@gmail.com> >> wrote: >>

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Thu, Feb 8, 2018 at 8:39 PM, Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote: > Claudio Freire wrote: > >> I don't like looping, though, seems overly cumbersome. What's worse? >> maintaining that fragile weird loop that might break (by causing >> unexpected

Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently

On Thu, Feb 8, 2018 at 1:36 AM, Masahiko Sawada <sawada.m...@gmail.com> wrote: > On Tue, Feb 6, 2018 at 9:51 PM, Claudio Freire <klaussfre...@gmail.com> wrote: >> I can look into doing 3, that *might* get rid of the need to do that >> initial FSM vacuum, but all

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Thu, Feb 8, 2018 at 2:44 AM, Kyotaro HORIGUCHI wrote: > Hello, I looked this a bit closer. > > In upthread[1] Robert mentioned the exponentially increasing size > of additional segments. > >>> Hmm, I had imagined making all of the segments the same size rather

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Thu, Feb 8, 2018 at 12:13 AM, Claudio Freire <klaussfre...@gmail.com> wrote: > On Wed, Feb 7, 2018 at 11:29 PM, Alvaro Herrera <alvhe...@alvh.no-ip.org> > wrote: >> Claudio Freire wrote: >>> On Wed, Feb 7, 2018 at 8:52 PM, Alvaro Herrera <alvhe...@alvh.n

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Wed, Feb 7, 2018 at 11:29 PM, Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote: > Claudio Freire wrote: >> On Wed, Feb 7, 2018 at 8:52 PM, Alvaro Herrera <alvhe...@alvh.no-ip.org> >> wrote: >> >> Waiting as you say would be akin to what the patch does by p

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

ould conflict with basically any other running transaction and have pretty much that effect. Attached is a version of patch 1 with that approach. From 3f1eb8829f9b396d1e5aef33114df44b04631455 Mon Sep 17 00:00:00 2001 From: Claudio Freire <klaussfre...@gmail.com> Date: Mon, 12 Sep 2016 23:36:42 -0300 Su

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Wed, Feb 7, 2018 at 7:57 PM, Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote: > Claudio Freire wrote: > >> - vacuum test on its own parallel group > > Hmm, this solution is not very friendly to the goal of reducing test > runtime, particularly since the new test

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Wed, Feb 7, 2018 at 12:50 AM, Kyotaro HORIGUCHI <horiguchi.kyot...@lab.ntt.co.jp> wrote: > Hello, > > At Tue, 6 Feb 2018 10:41:22 -0300, Claudio Freire <klaussfre...@gmail.com> > wrote in

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Tue, Feb 6, 2018 at 10:18 AM, Claudio Freire <klaussfre...@gmail.com> wrote: > On Tue, Feb 6, 2018 at 4:35 AM, Kyotaro HORIGUCHI > <horiguchi.kyot...@lab.ntt.co.jp> wrote: >>> It's starting to look like a timing effect indeed. >> >> It seems to be trun

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Tue, Feb 6, 2018 at 4:35 AM, Kyotaro HORIGUCHI wrote: >> It's starting to look like a timing effect indeed. > > It seems to be truncation skip, maybe caused by concurrent > autovacuum. Good point, I'll also disable autovacuum on vactst. > See

Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently

On Tue, Feb 6, 2018 at 4:56 AM, Masahiko Sawada <sawada.m...@gmail.com> wrote: > On Tue, Feb 6, 2018 at 2:55 AM, Claudio Freire <klaussfre...@gmail.com> wrote: >> On Mon, Feb 5, 2018 at 1:53 AM, Masahiko Sawada <sawada.m...@gmail.com> >> wrote: >>> On Fr

Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently

On Mon, Feb 5, 2018 at 2:55 PM, Claudio Freire <klaussfre...@gmail.com> wrote: > With that in mind, I'm noticing WorkItems have a avw_database that > isn't checked by do_autovacuum. Is that right? Shouldn't only work > items that belong to the database being autovacuumed be process

Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently

On Mon, Feb 5, 2018 at 1:53 AM, Masahiko Sawada <sawada.m...@gmail.com> wrote: > On Fri, Feb 2, 2018 at 11:13 PM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> After autovacuum gets cancelled, the next time it wakes up it will >> retry vacuuming the canc

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Thu, Jan 25, 2018 at 6:21 PM, Thomas Munro <thomas.mu...@enterprisedb.com> wrote: > On Fri, Jan 26, 2018 at 9:38 AM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> I had the tests running in a loop all day long, and I cannot reproduce >> that variance.

Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently

On Thu, Feb 1, 2018 at 9:34 PM, Masahiko Sawada <sawada.m...@gmail.com> wrote: > On Mon, Jan 29, 2018 at 11:31 PM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> On Mon, Jan 29, 2018 at 4:12 AM, Masahiko Sawada <sawada.m...@gmail.com> >> wrote: >>

Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently

On Mon, Jan 29, 2018 at 4:12 AM, Masahiko Sawada <sawada.m...@gmail.com> wrote: > On Sat, Jul 29, 2017 at 9:42 AM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> Introduce a tree pruning threshold to FreeSpaceMapVacuum that avoids >> recursing into branches th

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Thu, Jan 25, 2018 at 10:56 AM, Claudio Freire <klaussfre...@gmail.com> wrote: > On Thu, Jan 25, 2018 at 4:11 AM, Thomas Munro > <thomas.mu...@enterprisedb.com> wrote: >> On Thu, Jan 18, 2018 at 9:17 AM, Claudio Freire <klaussfre...@gmail.com> >> wrote: >

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Thu, Jan 25, 2018 at 4:11 AM, Thomas Munro <thomas.mu...@enterprisedb.com> wrote: > On Thu, Jan 18, 2018 at 9:17 AM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> Huh. That was simpler than I thought. >> >> Attached rebased versions. > > Hi

Re: Built-in connection pooling

On Fri, Jan 19, 2018 at 2:22 PM, Tomas Vondra <tomas.von...@2ndquadrant.com> wrote: > > > On 01/19/2018 06:13 PM, Claudio Freire wrote: > > > > > > On Fri, Jan 19, 2018 at 2:07 PM, Konstantin Knizhnik > > <k.knizh...@postgrespro.ru

Re: Built-in connection pooling

On Fri, Jan 19, 2018 at 2:07 PM, Konstantin Knizhnik < k.knizh...@postgrespro.ru> wrote: > > > >> Well, I haven't said it has to be single-threaded like pgbouncer. I >> don't see why the bgworker could not use multiple threads internally (of >> course, it'd need to be not to mess the stuff that

Re: Built-in connection pooling

On Fri, Jan 19, 2018 at 2:06 PM, Tomas Vondra <tomas.von...@2ndquadrant.com> wrote: > > > On 01/19/2018 06:03 PM, Claudio Freire wrote: > > > > > > On Fri, Jan 19, 2018 at 1:53 PM, Konstantin Knizhnik > > <k.knizh...@postgrespro.ru

Re: Built-in connection pooling

On Fri, Jan 19, 2018 at 1:53 PM, Konstantin Knizhnik < k.knizh...@postgrespro.ru> wrote: > > > On 19.01.2018 19:28, Pavel Stehule wrote: > > > > When I've been thinking about adding a built-in connection pool, my >>> rough plan was mostly "bgworker doing something like pgbouncer" (that >>> is,

Re: Built-in connection pooling

On Thu, Jan 18, 2018 at 11:48 AM, Konstantin Knizhnik < k.knizh...@postgrespro.ru> wrote: > > Attached please find new version of the patch with few fixes. > And more results at NUMA system with 144 cores and 3Tb of RAM. > > Read-only pgbench (-S): > > > #Connections\kTPS > Vanilla Postgres >

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Wed, Jan 17, 2018 at 5:02 PM, Claudio Freire <klaussfre...@gmail.com> wrote: > > > On Sat, Jan 6, 2018 at 7:35 PM, Stephen Frost <sfr...@snowman.net> wrote: > >> Greetings, >> >> * Michael Paquier (michael.paqu...@gmail.com) wrote: >> > On Mon

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Sat, Jan 6, 2018 at 7:35 PM, Stephen Frost <sfr...@snowman.net> wrote: > Greetings, > > * Michael Paquier (michael.paqu...@gmail.com) wrote: > > On Mon, Dec 4, 2017 at 2:38 PM, Claudio Freire <klaussfre...@gmail.com> > wrote: > > > They did apply at the

Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently

pages_at_fsm_vac - vacuumed_pages) > > vacuum_fsm_every_pages) > + { > + /* Vacuum the Free Space Map */ > + FreeSpaceMapVacuum(onerel, 0); > + vacuumed_pages_at_fsm_vac = vacuumed_pages; > + } > > vacuumed_pages_at_fsm_vac is initial

Re: Changing WAL Header to reduce contention during ReserveXLogInsertLocation()

On Fri, Jan 12, 2018 at 10:49 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Claudio Freire <klaussfre...@gmail.com> writes: > > On Sat, Dec 30, 2017 at 7:32 AM, Simon Riggs <si...@2ndquadrant.com> > wrote: > >> So we can't completely remove xl_prev field, wi

Re: Changing WAL Header to reduce contention during ReserveXLogInsertLocation()

On Fri, Jan 12, 2018 at 8:43 PM, Claudio Freire <klaussfre...@gmail.com> wrote: > > > On Sat, Dec 30, 2017 at 7:32 AM, Simon Riggs <si...@2ndquadrant.com> > wrote: > >> So we can't completely remove xl_prev field, without giving up some >> functionality.

Re: Huge backend memory footprint

On Fri, Dec 22, 2017 at 10:07 AM, Konstantin Knizhnik < k.knizh...@postgrespro.ru> wrote: > While my experiments with pthreads version of Postgres I find out that I > can not create more than 100k backends even at the system with 4Tb of RAM. > I do not want to discuss now the idea of creating so

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Tue, Nov 28, 2017 at 10:37 PM, Michael Paquier <michael.paqu...@gmail.com> wrote: > On Mon, Oct 2, 2017 at 11:02 PM, Claudio Freire <klaussfre...@gmail.com> > wrote: >> Rebased version of the patches attached > > The status of the patch is misleading: > https:/