Re: proposal: Allocate work_mem From Pool

2022-07-12 Thread John Naylor
ult project to be smart about memory -- step one might be to invent a scheduler. (The autovacuum launcher and checkpointer, etc have their own logic about when to do things, but while running they too are just OS processes scheduled by the kernel.) -- John Naylor EDB: http://www.enterprisedb.com

Re: [PATCH] Optimize json_lex_string by batching character copying

2022-07-12 Thread John Naylor
VECTOR_WIDTH I wonder if we'd lose a bit of efficiency here by not accumulating set bits from the three conditions, but it's worth trying. -- John Naylor EDB: http://www.enterprisedb.com

Re: [PATCH] Optimize json_lex_string by batching character copying

2022-07-10 Thread John Naylor
On Fri, Jul 8, 2022 at 3:06 PM John Naylor wrote: > > I've pushed 0001 (although the email seems to have been swallowed > again), and pending additional comments on 0002 and 0003 I'll squash > and push those next week. This is done. > 0004 needs some thought on integrating wi

Re: [PATCH] Optimize json_lex_string by batching character copying

2022-07-08 Thread John Naylor
I've pushed 0001 (although the email seems to have been swallowed again), and pending additional comments on 0002 and 0003 I'll squash and push those next week. 0004 needs some thought on integrating with symbols we discover during configure. -- John Naylor EDB: http://www.enterprisedb.com

Re: [PoC] Improve dead tuple storage for lazy vacuum

2022-07-08 Thread John Naylor
ode can either store a pointer or a value." But without the advantage of variable length keys). -- John Naylor EDB: http://www.enterprisedb.com

Re: tuplesort Generation memory contexts don't play nicely with index builds

2022-07-06 Thread John Naylor
On Thu, Jul 7, 2022 at 3:16 AM David Rowley wrote: > > Pushed. Hmm, the commit appeared on git.postgresql.org, but apparently not in my email nor the list archives. -- John Naylor EDB: http://www.enterprisedb.com

Re: [PATCH] Optimize json_lex_string by batching character copying

2022-07-06 Thread John Naylor
so only backslash will go back to the top. Both the above changes are split into a new 0003 patch for easier review, but in the end will likely be squashed with 0002. -- John Naylor EDB: http://www.enterprisedb.com From 3d8b39ff1c1a4abf9effc45323b293b62551770a Mon Sep 17 00:00:00 2001 From: John N

Re: [PATCH] Optimize json_lex_string by batching character copying

2022-07-05 Thread John Naylor
_string() to be inlined, with a constant parameter deciding whether > ->strval is expected. That'd likely be enough to get the compiler specialize > the code for us. I had a look at this but it's a bit more invasive than I want to devote time to at this point. -- John Naylor EDB: http://

Re: [PoC] Improve dead tuple storage for lazy vacuum

2022-07-05 Thread John Naylor
its of item offset || blockhi || blocklo A concern here is most tids won't use many bits in blockhi either, most often far fewer, so this would make the tree higher, I think. Each value of blockhi represents 0.5GB of heap (32TB max). Even with very large tables I'm guessing most pages of interest to vacuum are concentrated in a few of these 0.5GB "segments". And it's possible path compression would change the tradeoffs here. -- John Naylor EDB: http://www.enterprisedb.com

Re: [PoC] Improve dead tuple storage for lazy vacuum

2022-06-28 Thread John Naylor
choice of node types might not be terribly important for these two cases. That's good if that's true in general -- a future performance-critical use of this code might tweak things for itself without upsetting vacuum. -- John Naylor EDB: http://www.enterprisedb.com

Re: WIP Patch: Add a function that returns binary JSONB as a bytea

2022-06-27 Thread John Naylor
I wrote: > We can also shave a > few percent by having pg_utf8_verifystr use SSE2 for the ascii path. I > can look into this. Here's a patch for that. If the input is mostly ascii, I'd expect that part of the flame graph to shrink by 40-50% and give a small boost overall. -- John N

Re: [PoC] Improve dead tuple storage for lazy vacuum

2022-06-27 Thread John Naylor
e too (if I understand your claim correctly). I'm not quite convinced of that in this case. > I would definitely test before assuming binary search is better. I wasn't very clear in my language, but I did reject binary search as having bad branch prediction. -- John Naylor EDB: http://www.enterprisedb.com

Re: [PoC] Improve dead tuple storage for lazy vacuum

2022-06-27 Thread John Naylor
to you off-list, I have some thoughts on the nodes using SIMD: > On Thu, Jun 16, 2022 at 4:30 PM John Naylor > wrote: > > > > For now, though, I'd like to question > > why we even need to use 32-byte registers in the first place. For one, > > the paper referenced has 16-

Re: WIP Patch: Add a function that returns binary JSONB as a bytea

2022-06-24 Thread John Naylor
lus the ending double-quote). We can also shave a few percent by having pg_utf8_verifystr use SSE2 for the ascii path. I can look into this. -- John Naylor EDB: http://www.enterprisedb.com

Re: NAMEDATALEN increase because of non-latin languages

2022-06-23 Thread John Naylor
On Thu, Jun 23, 2022 at 9:17 PM Andres Freund wrote: > > Hi, > > On 2022-06-03 13:28:16 +0700, John Naylor wrote: > > 1. That would require putting the name physically closer to the end of > > the column list. To make this less annoying for users, we'd need to > >

Re: some aspects of our qsort might not be ideal

2022-06-23 Thread John Naylor
latest tests, then use that as a starting point to test thresholds with dual-pivot. -- John Naylor EDB: http://www.enterprisedb.com From 5e920d9d3e8d2a2a75e63ade8bc73c8322c1934b Mon Sep 17 00:00:00 2001 From: John Naylor Date: Mon, 30 May 2022 10:09:17 +0700 Subject: [PATCH v1 1/2] Create internal wo

Re: pg15b1: FailedAssertion("val > base", File: "...src/include/utils/relptr.h", Line: 67, PID: 30485)

2022-06-21 Thread John Naylor
rt-term fix, and my reading of the thread is that the other proposals are too invasive at this point in the cycle. Both of them have a draft patch in the thread. #2, i.e. wasting MAXALIGN of space, seems the simplest and most localized. Any thoughts on pulling the trigger on either of these two appro

Re: [PoC] Improve dead tuple storage for lazy vacuum

2022-06-16 Thread John Naylor
tages of review, we want to document design decisions so it's more clear for the reader. -- John Naylor EDB: http://www.enterprisedb.com

Re: NAMEDATALEN increase because of non-latin languages

2022-06-03 Thread John Naylor
er reasons. Is Matthias's patch or something like it a good next step? -- John Naylor EDB: http://www.enterprisedb.com

Re: some aspects of our qsort might not be ideal

2022-06-02 Thread John Naylor
s and timsort for objects. I wasn't sure if dual pivot was not good for objects (which could have possibly-complex comparators) or if timsort was just simply good for Java's use cases. It seems accessible to try doing, so I'll look into that. -- John Naylor EDB: http://www.enterprisedb.com

Re: PG15 beta1 sort performance regression due to Generation context change

2022-06-02 Thread John Naylor
f anyone > happened to hit this case and find the performance regression > unacceptable then they have a way out... increase work_mem a little. Since #4 is such a small lift, I'd be comfortable with closing the open item. -- John Naylor EDB: http://www.enterprisedb.com

Re: PG15 beta1 sort performance regression due to Generation context change

2022-06-02 Thread John Naylor
tuples. It also shows a 20% regression for 32MB workmem and 64 byte tuples. I don't have anything to add to the discussion about whether something needs to be done here for PG15. If anything, changing work_mem is an easy to understand (although sometimes not practical) workaround. -- John Naylor EDB

Re: First draft of the PG 15 release notes

2022-05-23 Thread John Naylor
On Mon, May 16, 2022 at 9:18 PM Bruce Momjian wrote: > > Newer wording: > > Improve validation of UTF-8 text (even if only ASCII) by processing > 16 bytes at a time (John Naylor) Thanks! I also think Heikki should be mentioned as a coauthor here -- the ASCII codi

Re: postgres_fdw has insufficient support for large object

2022-05-23 Thread John Naylor
en to remove them someday (i.e. "deprecated"), but we are not going to improve them in any meaningful way, and users would be warned about using them in new projects if better alternatives are available. -- John Naylor EDB: http://www.enterprisedb.com

Re: A qsort template

2022-05-23 Thread John Naylor
I wrote: > I agree this is only useful in development. Removal sounds fine to me, > so I'll do that soon. This is done. -- John Naylor EDB: http://www.enterprisedb.com

Re: A qsort template

2022-05-20 Thread John Naylor
together, or failing that, downgrade to DEBUG5 or so. I agree this is only useful in development. Removal sounds fine to me, so I'll do that soon. -- John Naylor EDB: http://www.enterprisedb.com

Re: First draft of the PG 15 release notes

2022-05-16 Thread John Naylor
Hi Bruce, "Improve validation of ASCII and UTF-8 text by processing 16 bytes at a time (John Naylor)" The reader might assume here that ASCII is optimized regardless of encoding, but it is only optimized in the context of UTF-8. So I would just mention UTF-8. Thanks! -- John Naylor

Re: [PoC] Improve dead tuple storage for lazy vacuum

2022-05-10 Thread John Naylor
scuss: deletion, API design, SIMD support, more tests etc. +1 (FWIW, I think the current thread is still fine.) -- John Naylor EDB: http://www.enterprisedb.com

Re: trivial comment fix

2022-04-27 Thread John Naylor
On Thu, Apr 28, 2022 at 7:27 AM Euler Taveira wrote: > > Hi, > > While reading worker.c, I noticed that the referred SQL command was wrong. > ALTER SUBSCRIPTION ... REFRESH PUBLICATION instead of ALTER TABLE ... REFRESH > PUBLICATION. Trivial fix attached. Pushed, thanks!

Re: double inclusion of '-p' flex flag

2022-04-26 Thread John Naylor
use a serious loss of performance in the resulting scanner. If you give the flag twice, you will also get comments regarding features that lead to minor performance losses." -- John Naylor EDB: http://www.enterprisedb.com

Re: A qsort template

2022-04-21 Thread John Naylor
On Fri, Apr 22, 2022 at 11:13 AM David Rowley wrote: > > On Thu, 21 Apr 2022 at 19:09, John Naylor > wrote: > > I intend to commit David's v2 fix next week, unless there are > > objections, or unless he beats me to it. > > I wasn't sure if you wanted to handle

Re: A qsort template

2022-04-21 Thread John Naylor
I intend to commit David's v2 fix next week, unless there are objections, or unless he beats me to it. -- John Naylor EDB: http://www.enterprisedb.com

Re: A qsort template

2022-04-19 Thread John Naylor
bit less noisy". -- John Naylor EDB: http://www.enterprisedb.com

Re: A qsort template

2022-04-19 Thread John Naylor
On Tue, Apr 19, 2022 at 12:30 PM David Rowley wrote: > > Thanks for looking at this. > > On Tue, 19 Apr 2022 at 02:11, John Naylor > wrote: > > IIUC, this function is called by tuplesort_begin_common, which in turn > > is called by tuplesort_begin_{heap, indexe

Re: subscribe hackers

2022-04-18 Thread John Naylor
On Mon, Apr 18, 2022 at 6:54 PM 汪洋 wrote: > > subscribe pgsql-hackers Hi, this mailing list is not managed by subject line. To subscribe, please visit https://lists.postgresql.org/ -- John Naylor EDB: http://www.enterprisedb.com

Re: A qsort template

2022-04-18 Thread John Naylor
* tie-breaker comparisons may be required. Typically, the optimization * is only of value to pass-by-value types anyway, whereas abbreviated * keys are typically only of value to pass-by-reference types. */ I can take a stab at this, unless you had something else in mind. -- John Naylor EDB: http://www.enterprisedb.com

Re: A qsort template

2022-04-14 Thread John Naylor
On Thu, Apr 14, 2022 at 1:46 PM David Rowley wrote: > > On Wed, 13 Apr 2022 at 23:19, John Naylor > wrote: > > More broadly than the regression, Thomas' is very often the fastest of > > all, at the cost of more binary size. David's is occasionally slower > >

Re: Improving the "Routine Vacuuming" docs

2022-04-14 Thread John Naylor
ink they heard: "okay, so run a full vacuum". I would prefer these misunderstandings to get a big fat syntax error if they are carried out. -- John Naylor EDB: http://www.enterprisedb.com

Re: A qsort template

2022-04-13 Thread John Naylor
es restore performance for those. More broadly than the regression, Thomas' is very often the fastest of all, at the cost of more binary size. David's is occasionally slower than v15 or v15 with revert, but much of that is a slight difference and some is probably noise. -- John Naylor

Re: A qsort template

2022-04-11 Thread John Naylor
kip the retest of column 1 in the tiebreak comparator. > Perhaps you'd just install a different comparetup function, eg > comparetup_index_btree_tail (which would sharing code), so no need to > multiply specialisations for that. If we need to add these cases to avoid regression, it ma

Re: A qsort template

2022-04-06 Thread John Naylor
Naylor EDB: http://www.enterprisedb.com From 74b934bc5ed8f6733c064c0ef832e1aa9949f216 Mon Sep 17 00:00:00 2001 From: John Naylor Date: Wed, 6 Apr 2022 16:38:28 +0700 Subject: [PATCH v5] Raise qsort insertion sort threshold Our qsort algorithm is from NetBSD and is described in the 1993 paper

Re: shared-memory based stats collector - v70

2022-04-06 Thread John Naylor
On Wed, Apr 6, 2022 at 10:00 AM Andres Freund wrote: > - while working on the above point, I noticed that hash_bytes() showed up > noticeably in profiles, so I replaced it with a fixed-width function I'm curious about this -- could you direct me to which patch introduces this? -- John

Re: Mark all GUC variable as PGDLLIMPORT

2022-04-05 Thread John Naylor
ood? Should I try to do it earlier, > before we technically hit 8am? Should I do it the night before, last > thing before I go to bed on Thursday? Do you care whether your commit > or mine goes in first? For these two patches, I'd say a day or two after feature freeze is a reasonable goal. -- John Naylor EDB: http://www.enterprisedb.com

Re: CLUSTER sort on abbreviated expressions is broken

2022-04-03 Thread John Naylor
On Sun, Apr 3, 2022 at 11:05 AM Thomas Munro wrote: > > Hi, > > Independently of a problem with a recent commit, it seems that > $SUBJECT in all releases (well, I only tested as far back as 11). I can confirm the problem on v10 as well. -- John Naylor EDB: http://www.enterprisedb.com

Re: A qsort template

2022-04-02 Thread John Naylor
d,alignment" ... -- John Naylor EDB: http://www.enterprisedb.com

Re: A qsort template

2022-04-02 Thread John Naylor
itialised value was created by a stack allocation ==1940791==at 0x74224E: tuplesort_putheaptuple (tuplesort.c:1800) -- John Naylor EDB: http://www.enterprisedb.com

Re: A qsort template

2022-04-02 Thread John Naylor
clause. I'll go through the failures and see how much can be cleaned up as a preparatory refactoring. -- John Naylor EDB: http://www.enterprisedb.com

Re: A qsort template

2022-04-02 Thread John Naylor
On Fri, Apr 1, 2022 at 4:43 AM Thomas Munro wrote: > > On Thu, Mar 31, 2022 at 11:09 PM John Naylor > wrote: > > In a couple days I'm going to commit the v3 patch "accelerate tuple > > sorting for common types" as-is after giving it one more look, barring >

Re: A qsort template

2022-03-31 Thread John Naylor
will have to wait for a bit of analysis and retesting. (My earlier tests were done in a separate module.) The rest in this series that I looked at closely were either refactoring or could use some minor tweaks so likely v16 material. -- John Naylor EDB: http://www.enterprisedb.com

Re: do only critical work during single-user vacuum?

2022-03-31 Thread John Naylor
On Wed, Mar 16, 2022 at 4:48 AM Peter Geoghegan wrote: > > On Wed, Feb 16, 2022 at 12:43 AM John Naylor > wrote: > > I'll put some effort in finding any way that it might not be robust. > > After that, changing the message and docs is trivial. > > It would be great

Re: use rotate macro in more places

2022-02-19 Thread John Naylor
022 at 11:28 PM Tom Lane wrote: > > Some comments now look a bit too obvious to keep around, but maybe > > they should be replaced with a "why", instead of a "what": > > Yeah. Maybe like "combine successive hashkeys by rotating"? Done that way. Push

use rotate macro in more places

2022-02-19 Thread John Naylor
1) | ((hashkey & 0x8000) ? 1 : 0); + hashkey = pg_rotate_left32(hashkey, 1); -- John Naylor EDB: http://www.enterprisedb.com diff --git a/src/backend/executor/execGrouping.c b/src/backend/executor/execGrouping.c index af6e9c42d8..ce8f6cd047 100644 --- a/src/backend/executor/execGrouping.c +

Re: minor code correction in typecmds.c

2022-02-16 Thread John Naylor
On Wed, Feb 16, 2022 at 7:17 PM Amul Sul wrote: > > Hi, > > The attached patch replaces the hard-coded type alignment value with > the defined macro for the same. Pushed, thanks! -- John Naylor EDB: http://www.enterprisedb.com

Re: do only critical work during single-user vacuum?

2022-02-16 Thread John Naylor
ging the message and docs is trivial. > It seemed worth noting this in comments above > should_attempt_truncation(). Pushed a commit to do that just now. Thanks for that. -- John Naylor EDB: http://www.enterprisedb.com

Re: some aspects of our qsort might not be ideal

2022-02-15 Thread John Naylor
s we attempt before we give up and partition/recurse. The author's implementation chooses 8 for this limit. The paper mentions this technique in section 5.2, but is not the origin of it. -- John Naylor EDB: http://www.enterprisedb.com

Re: some aspects of our qsort might not be ideal

2022-02-15 Thread John Naylor
Lb_Xn4-6f1ofsf2qduf24dDCVHbQidt7JPpdL_RiT1zBJ6A%40mail.gmail.com -- John Naylor EDB: http://www.enterprisedb.com

some aspects of our qsort might not be ideal

2022-02-15 Thread John Naylor
b.com/orlp/pdqsort -- John Naylor EDB: http://www.enterprisedb.com

Re: initdb / bootstrap design

2022-02-15 Thread John Naylor
division of labor between initdb and bootstrap" -- John Naylor EDB: http://www.enterprisedb.com

Re: Mark all GUC variable as PGDLLIMPORT

2022-02-15 Thread John Naylor
in favor of setting up the PGDLLIMPORT macro so that it can just be used unconditionally. That can work because in frontend code, we need no marking in either the defining or consuming files for a variable exported from these libraries; and frontend code has no need to access variables exported from the core backend, either. -- John Naylor EDB: http://www.enterprisedb.com

Re: Consistently use "startup process"/"WAL sender" instead of "Startup process"/"WAL Sender" in comments and docs.

2022-02-14 Thread John Naylor
rs that are worth fixing in the comments. I've pushed the doc fixes, thanks for the patch! -- John Naylor EDB: http://www.enterprisedb.com

Re: do only critical work during single-user vacuum?

2022-02-14 Thread John Naylor
On Tue, Feb 15, 2022 at 11:22 AM Peter Geoghegan wrote: > > On Mon, Feb 14, 2022 at 8:04 PM John Naylor > wrote: > > The failsafe mode does disable truncation as of v14: > > > > commit 60f1f09ff44308667ef6c72fbafd68235e55ae27 > > Author: Peter Geoghegan >

Re: do only critical work during single-user vacuum?

2022-02-14 Thread John Naylor
t; 4) oldest in-progress transaction id by xid / xmin, with name > 5) oldest database datfrozenxid, with database name [...] > Also, adding an SRF providing the above in a useful format would be great for > monitoring and for "remote debugging" of problems. I concur it s

Re: Consistently use "startup process"/"WAL sender" instead of "Startup process"/"WAL Sender" in comments and docs.

2022-02-13 Thread John Naylor
; is being used instead of "WAL > sender". Let's be consistent across the docs and code comments. FWIW, docs need to hold to a higher standard than code comments. -- John Naylor EDB: http://www.enterprisedb.com

Re: Non-decimal integer literals

2022-02-13 Thread John Naylor
. 0007: I've attached an addendum to restore the no-backtrack property. Will the underscore syntax need treatment in the input routines as well? -- John Naylor EDB: http://www.enterprisedb.com diff --git a/src/backend/parser/Makefile b/src/backend/parser/Makefile index 827bc4c189..5ddb9a92f0 1

Re: do only critical work during single-user vacuum?

2022-02-04 Thread John Naylor
t as well), but introduce a softer "only > > vacuum/drop/truncate" limit a good bit before that. > > +1. Since there seems to be agreement on this, I can attempt a stab at it, but it'll be another week before I can do so. -- John Naylor EDB: http://www.enterprisedb.com

Re: Removing more vacuumlazy.c special cases, relfrozenxid optimizations

2022-02-04 Thread John Naylor
72,450 > Here is the same pg_stat_database info for master: > blks_hit | 283,015,966,386 > tup_fetched | 237,052,965,901 That's impressive. -- John Naylor EDB: http://www.enterprisedb.com

Re: Stats collector's idx_blks_hit value is highly misleading in practice

2022-02-04 Thread John Naylor
On Fri, Feb 4, 2022 at 11:19 AM Peter Geoghegan wrote: > > On Thu, Feb 3, 2022 at 7:08 PM John Naylor > wrote: > > Is this a TODO candidate? What would be a succinct title for it? > > I definitely think that it's worth working on. I suppose it follows > that it sh

Re: Stats collector's idx_blks_hit value is highly misleading in practice

2022-02-03 Thread John Naylor
pgstat_count_buffer_(read|hit). That > happens in ReadBufferExtended, which just has no idea what page it's > dealing with. Not sure how to do that cleanly ... Is this a TODO candidate? What would be a succinct title for it? -- John Naylor EDB: http://www.enterprisedb.com

Re: do only critical work during single-user vacuum?

2022-02-03 Thread John Naylor
On Thu, Feb 3, 2022 at 4:58 PM Andres Freund wrote: > > Hi, > > On 2022-02-03 16:18:27 -0500, John Naylor wrote: > > I just checked some client case notes where they tried just that > > before getting outside help, and both SELECT and VACUUM FREEZE > > command

Re: do only critical work during single-user vacuum?

2022-02-03 Thread John Naylor
On Thu, Feb 3, 2022 at 1:42 PM Robert Haas wrote: > > On Thu, Feb 3, 2022 at 1:34 PM John Naylor > wrote: > > The word "advice" sounds like people have a choice, rather than the > > system not accepting commands anymore. It would be much less painful >

Re: do only critical work during single-user vacuum?

2022-02-03 Thread John Naylor
on that I'll talk about in a minute. The word "advice" sounds like people have a choice, rather than the system not accepting commands anymore. It would be much less painful if the system closed connections and forbade all but superusers to connect, but that sounds like a lot of work. (

Re: do only critical work during single-user vacuum?

2022-02-03 Thread John Naylor
ugh to do that. I'll pursue that as a follow-up. -- John Naylor EDB: http://www.enterprisedb.com

Re: do only critical work during single-user vacuum?

2022-02-03 Thread John Naylor
elminmxid); > + > > I think that instead of calling xid_age and mxid_age for each > relation, we can compute the thresholds for xid and mxid once, and > then compare them to relation's relfrozenxid and relminmxid. That sounds like a good idea if it's simple to implement, so I will

Re: speed up text_position() for utf-8

2022-02-02 Thread John Naylor
ached). The others can be faster or slower. 0002 also simplifies things, so it has that going for it. I plan to commit that this week unless there are objections. -- John Naylor EDB: http://www.enterprisedb.com haswell xeon / gcc 8 master: Time: 608.047 ms Time: 2859.939 ms (00:02.860) Time: 2255

Re: A qsort template

2022-02-02 Thread John Naylor
till thinking 20 or so is about right. I've put a lot out here recently, so I'll take a break now and come back in a few weeks. (no running tally here because the conclusions haven't changed since last message) -- John Naylor EDB: http://www.enterprisedb.com NOTICE: [direct] size=8MB, order=r

Re: do only critical work during single-user vacuum?

2022-02-01 Thread John Naylor
Id(). I'm not sure if multi-word commands should be quoted like this. - A first draft of documentation -- John Naylor EDB: http://www.enterprisedb.com doc/src/sgml/maintenance.sgml | 12 ++-- doc/src/sgml/ref/vacuum.sgml| 22 src/backend/access/transam/varsup.c | 4 +-

Re: A qsort template

2022-01-31 Thread John Naylor
stgresql.org/message-id/CA%2BhUKG%2BS5SMoG8Z2PHj0bsK70CxVLgqQR1orQJq6Cjgibu26vA%40mail.gmail.com [2] https://www.postgresql.org/message-id/CAFBsxsEFGAJ9eBpQVb5a86BE93WER3497zn2OT5wbjm1HHcqgA%40mail.gmail.com (TODO: refine test) -- John Naylor EDB: http://www.enterprisedb.com NOTICE: [direct] size=8MB, order=

Re: A qsort template

2022-01-27 Thread John Naylor
, and also attached an extra draft spreadsheet. I'll improve the tests and rerun later) -- John Naylor EDB: http://www.enterprisedb.com NOTICE: [traditional qsort] size=8MB, order=random, cmp=arg, test=0, time=0.144545 NOTICE: [traditional qsort] size=8MB, order=random, cmp=arg, test=1, tim

Re: autovacuum prioritization

2022-01-25 Thread John Naylor
for the VM even if they have not reached the configured threshold. So a worker would check the wraparound queue, and if nothing's there grab something from the other queue. Maybe low-priority work would have a low cost limit. Probably the true best way to do schedule, at least at first, is what's the

Re: do only critical work during single-user vacuum?

2022-01-21 Thread John Naylor
to have a simple, easy to type, command - intended for single-user mode, but not limited to it (so it's easy to test) - to get out of single user mode as quickly as possible -- John Naylor EDB: http://www.enterprisedb.com diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index 283f

Re: Time to increase hash_mem_multiplier default?

2022-01-19 Thread John Naylor
up in the external sorting code. Also, if the "generation context" idea gets traction, that might be another reason to consider differentiating the mem settings. -- John Naylor EDB: http://www.enterprisedb.com

Re: speed up text_position() for utf-8

2022-01-19 Thread John Naylor
chine, but not as fast as 0001 and 0002 together. Looking at the assembly, pg_mblen is inlined into pg_mbstrlen_[with_len] and pg_mbcliplen, so the specialization for utf-8 in 0001 would be inlined in the other 3 as well. That's only a few bytes, so I think it's fine. -- John Naylor EDB: http://www.enterprisedb.com

Re: do only critical work during single-user vacuum?

2022-01-19 Thread John Naylor
a bit more natural than VACUUM LIMIT. Opinions? -- John Naylor EDB: http://www.enterprisedb.com

Re: A qsort template

2022-01-19 Thread John Naylor
On Tue, Jan 18, 2022 at 9:58 PM Peter Geoghegan wrote: > > On Tue, Jan 18, 2022 at 6:39 PM John Naylor > wrote: > > Editorializing the null position in queries is not very common in my > > experience. Not null is interesting since it'd be trivial to pass > > constant

Re: A qsort template

2022-01-18 Thread John Naylor
emory usage is always good. Not sure what you mean by the third case -- there are 2+ sort keys, but the first is authoritative from the datum, so the full comparison can skip the first key? -- John Naylor EDB: http://www.enterprisedb.com qsort-specialize-types-jcn1.ods Description: application/vnd.oas

Re: do only critical work during single-user vacuum?

2022-01-14 Thread John Naylor
. -- John Naylor EDB: http://www.enterprisedb.com

Re: do only critical work during single-user vacuum?

2022-01-13 Thread John Naylor
(including > indexes) with an age above 500M on the weekends. There is already vaccumdb for that, and I think it's method of selecting tables is sound -- I'm not convinced that pushing table selection to the server command as "options" is an improvement. -- John Naylor EDB: http://www.enterprisedb.com

Re: A qsort template

2022-01-12 Thread John Naylor
RhTiQdVttNuC4W-Shdc2a-AA%40mail.gmail.com [2] https://www.postgresql.org/message-id/CAFBsxsG_c24CHKA3cWrOP1HynWGLOkLb8hyZfsD9db5g-ZPagA%40mail.gmail.com -- John Naylor EDB: http://www.enterprisedb.com

Re: do only critical work during single-user vacuum?

2022-01-12 Thread John Naylor
case, the admin would still need to add INDEX_CLEANUP = off for minimum downtime, and it should be really simple. - For the general case, we would now have the ability to vacuum a table, and possibly have no effect at all. That seems out of place with the other options. -- John Naylor EDB: http://www.enterprisedb.com

Re: do only critical work during single-user vacuum?

2022-01-12 Thread John Naylor
On Tue, Jan 11, 2022 at 9:20 PM Justin Pryzby wrote: > > On Tue, Jan 11, 2022 at 07:58:56PM -0500, John Naylor wrote: > > + // FIXME: also check reloption > > + // WIP: 95% is a starting point for discussion > > + if ((table_xid_age <

Re: do only critical work during single-user vacuum?

2022-01-12 Thread John Naylor
On Tue, Jan 11, 2022 at 8:57 PM Peter Geoghegan wrote: > On Tue, Jan 11, 2022 at 4:59 PM John Naylor > > For the PoC I wanted to try re-using existing keywords. I went with > > "VACUUM LIMIT" since LIMIT is already a keyword that cannot be used as > > a table n

Re: do only critical work during single-user vacuum?

2022-01-11 Thread John Naylor
t haven't done so here. It can be executed in normal mode (although it's not expected to be), which makes testing easier and allows for a future possibility of not requiring shutdown at all, by e.g. terminating non-superuser connections. -- John Naylor EDB: http://www.enterprisedb.com src/backend/comma

Re: Should we improve "PID XXXX is not a PostgreSQL server process" warning for pg_terminate_backend(<>)?

2022-01-11 Thread John Naylor
y. Also, v5 was a big enough change from v4 that I put Nathan as the first author. -- John Naylor EDB: http://www.enterprisedb.com

Re: Introducing PgVA aka PostgresVectorAcceleration using SIMD vector instructions starting with hex_encode

2022-01-03 Thread John Naylor
ating > point output …) which could be addressed later on. Float output has already been pretty well optimized. CRC checksums already have a hardware implementation on x86 and Arm. I don't know of any practical workload where generate_series() is too slow. Aggregation is an interesting case, but I'm not sure what the current bottlenecks are. -- John Naylor EDB: http://www.enterprisedb.com

Re: do only critical work during single-user vacuum?

2021-12-22 Thread John Naylor
. --min-xid-age > option and --min-mxid-age option of vacuumdb command would be good > examples. So I think this new command/facility might not necessarily > need to be specific to single-user mode. If we want to leave open the possibility to specify these parameters, a SQL-callable function s

Re: do only critical work during single-user vacuum?

2021-12-21 Thread John Naylor
On Tue, Dec 21, 2021 at 5:56 PM Peter Geoghegan wrote: > > On Tue, Dec 21, 2021 at 1:31 PM John Naylor > wrote: > > On second thought, we don't really need another number here. We could > > simply go by the existing failsafe parameter, and if the admin wants a > > di

Re: do only critical work during single-user vacuum?

2021-12-21 Thread John Naylor
um_failsafe_age/vacuum_multixact_failsafe_age in a session, including in single-user mode. Perhaps a new boolean called FAILSAFE_ONLY. If no table is specified, then when generating the list of tables, include only those with relfrozenxid/relminmxid greater than their failsafe thresholds. -- John Naylor EDB: http://www.enterprisedb.com

Re: do only critical work during single-user vacuum?

2021-12-21 Thread John Naylor
things we can do in this regard, but something like the above could also be useful in normal operation. In fact, that "normal" could be just after we restarted after doing the bare-minimum in single-user mode, and want to continue freezing and keep things under control. -- John Naylor EDB: http://www.enterprisedb.com

Re: speed up verifying UTF-8

2021-12-20 Thread John Naylor
On Fri, Dec 17, 2021 at 9:29 AM John Naylor wrote: > > I plan to push v25 early next week, unless there are further comments. Pushed, thanks everyone! -- John Naylor EDB: http://www.enterprisedb.com

Re: speed up text_position() for utf-8

2021-12-17 Thread John Naylor
| 1220 | 1220 | 1150 master | 385 | 2420 | 1980 0001 | 390 | 2180 | 1670 0002 | 389 | 1330 | 1100 0003 | 391 | 2100 | 1360 -- John Naylor EDB: http://www.enterprisedb.com v2-0002-Refactor-text_position_get_match_pos-to-use

<    2   3   4   5   6   7   8   9   10   11   >