On Mon, 8 Apr 2024 at 16:26, Robert Haas wrote:
> And maybe we need to think of a way to further mitigate this crush of
> last minute commits. e.g. In the last week, you can't have more
> feature commits, or more lines of insertions in your commits, than you
> did in the prior 3 weeks combined.
nually giving essentially the same result in gcc. As most
distro packages are built using gcc I think it would make sense to
have the extra code if it gives a noticeable benefit for large cases.
The visibility map patch has the same issue, otherwise looks good.
Regards,
Ants Aasma
diff --git a/sr
he choice easy.
Regards,
Ants Aasma
On Tue, 2 Apr 2024 at 00:31, Nathan Bossart wrote:
> On Tue, Apr 02, 2024 at 12:11:59AM +0300, Ants Aasma wrote:
> > What about using the masking capabilities of AVX-512 to handle the
> > tail in the same code path? Masked out portions of a load instruction
> > will not
On Tue, 2 Apr 2024 at 00:31, Nathan Bossart wrote:
>
> On Tue, Apr 02, 2024 at 12:11:59AM +0300, Ants Aasma wrote:
> > What about using the masking capabilities of AVX-512 to handle the
> > tail in the same code path? Masked out portions of a load instruction
> > will n
s sane. I added the clang
pragma because it insisted on unrolling otherwise and based on how the
instruction dependencies look that is probably not too helpful even
for large cases (needs to be tested). The configure check and compile
flags of course need to be amended for BW.
Regards,
Ants Aas
On Wed, 13 Mar 2024 at 04:56, Kyotaro Horiguchi wrote:
>
> At Mon, 11 Mar 2024 16:43:32 +0900 (JST), Kyotaro Horiguchi
> wrote in
> > Oh, I once saw the fix work, but seems not to be working after some
> > point. The new issue was a corruption of received WAL records on the
> > first standby,
On Tue, 30 Jan 2024 at 12:04, John Naylor wrote:
>
> On Tue, Jan 30, 2024 at 4:13 AM Ants Aasma wrote:
> > But given that we know the data length and we have it in a register
> > already, it's easy enough to just mask out data past the end with a
> > shift. See patc
ash function is used for.
Regards,
Ants Aasma
From 912f46be12536985dda7bcfb669d4ec13e79d073 Mon Sep 17 00:00:00 2001
From: Ants Aasma
Date: Mon, 29 Jan 2024 21:07:44 +0200
Subject: [PATCH 2/2] Unaligned fasthash word at a time hashing
About 10% performance benefit on short strings, 50% on long o
On Tue, 9 Jan 2024 at 18:20, Nathan Bossart wrote:
>
> On Tue, Jan 09, 2024 at 09:20:09AM +0700, John Naylor wrote:
> > On Tue, Jan 9, 2024 at 12:37 AM Nathan Bossart
> > wrote:
> >>
> >> > I suspect that there could be a regression lurking for some inputs
> >> > that the benchmark doesn't look
On Tue, 9 Jan 2024 at 16:03, Peter Eisentraut wrote:
> On 29.11.23 18:15, Nathan Bossart wrote:
> > Using the same benchmark as we did for the SSE2 linear searches in
> > XidInMVCCSnapshot() (commit 37a6e5d) [1] [2], I see the following:
> >
> >writerssse2avx2 %
> >256
9s (26.7 GiB/s)
clang-14 -O2 -msse4.1 -mavx2 1.378s (55.4 GiB/s)
--
Ants Aasma
Senior Database Engineer
www.cybertec-postgresql.com
nly
branch is for found/not found. Hoping to have a working prototype of SLRU
on top in the next couple of days.
Regards,
Ants Aasma
tential right
now either. We only check that it's not going backwards, but there is at
least one not very hard to hit way to get postgres to silently replay on
the wrong timeline. [1]
[1]
https://www.postgresql.org/message-id/canwkhkmn3qwacvudzhb6wsvlrtkwebiyso-klfykkqvwuql...@mail.gmail.com
--
Ants Aasma
Senior Database Engineerwww.cybertec-postgresql.com
y based/extensible tuple
placement, I would at some point try out placing INSERT ON CONFLICT
tuples on the same page as the preceding key in the index. Use case is
in tables with (series, timestamp) primary key to get locality of
access range scanning for a single series. Placement will always be a
tradeoff that is dependent on hardware and workload, and the effect
can be pretty large. For the mentioned use case, if placement can
maintain some semblance of clustering, there will be a 10-100x
reduction in buffers accessed for a relatively minor increase in
bloat.
--
Ants Aasma
Senior Database Engineer
www.cybertec-postgresql.com
On Tue, 27 Jun 2023 at 18:40, Andres Freund wrote:
> On 2023-06-27 14:49:48 +0300, Ants Aasma wrote:
> > If you want to experiment, here is a rebased version of something I
> > hacked up a couple of years back on the way to Fosdem Pgday. I didn't
> > pursue it further becaus
On Tue, 27 Jun 2023 at 07:09, Andres Freund wrote:
> On 2023-06-27 15:33:57 +1200, Thomas Munro wrote:
> > On Tue, Jun 27, 2023 at 2:05 PM Andres Freund wrote:
> > > Unfortunately it scaled way worse at first. This is not an inherent
> > > issue, but
> > > due to an implementation choice in
ID space to get the
performance boost, which seems essential to the approach. The latter
issue means that it can't be easily dropped into GIN or B-tree indexes
for ctid storage.
[1] https://github.com/ChenHuajun/pg_roaringbitmap
[2] https://github.com/cybertec-postgresql/pgfaceting
--
Ants Aasma
www.cybertec-postgresql.com
On Mon, 20 Mar 2023 at 00:59, Melanie Plageman
wrote:
>
> On Wed, Mar 15, 2023 at 6:46 AM Ants Aasma wrote:
> >
> > On Wed, 15 Mar 2023 at 02:29, Melanie Plageman
> > wrote:
> > > As for routine vacuuming and the other buffer access strategies, I
(what do
> you think of the name?).
A static inline function seems like a less surprising and more type
safe solution for this.
--
Ants Aasma
Senior Database Engineer
www.cybertec-postgresql.com
ch examples.
Ideally yes, though I am not hopeful of finding a solution that does
this any time soon. Just to take your example, if a nightly
maintenance job wipes out the shared buffer contents slightly
optimizing its non time-critical work and then causes morning user
visible load to have
uffer(). So maybe a dynamic
sizing algorithm could be applied to the ringbuffer. Make the buffers
array in strategy capable of holding up to the limit of buffers, but
set ring size conservatively. If we have to flush WAL, double the ring
size (up to the limit). If we loop around the ring without flushing,
decrease the ring size by a small amount to let clock sweep reclaim
them for use by other backends.
--
Ants Aasma
Senior Database Engineer
www.cybertec-postgresql.com
rect approach would still be to have ReadRecord() or
ApplyWalRecord() determine that switching timelines is needed.
--
Ants Aasma
www.cybertec-postgresql.com
diff --git a/src/backend/access/transam/xlogrecovery.c b/src/backend/access/transam/xlogrecovery.c
index cb07694aea6..73bde98b920 10064
broken.
> As you know, when new primary starts a diverged history, the
> recommended way is to blow (or stash) away the archive, then take a
> new backup from the running primary.
My understanding is that backup archives are supposed to remain valid
even after PITR or equivalently a laggin
. Maybe
while at it, we should also track that the next record should be a
checkpoint record for the timeline switch and error out if not. Thoughts?
--
Ants Aasma
Senior Database Engineerwww.cybertec-postgresql.com
recoverytest.sh
Description: application/shellscript
On Wed, 13 Oct 2021 at 02:20, Bruce Momjian wrote:
> On Wed, Oct 13, 2021 at 12:48:51AM +0300, Ants Aasma wrote:
> > On Wed, 13 Oct 2021 at 00:25, Bruce Momjian wrote:
> >
> > On Tue, Oct 12, 2021 at 11:21:28PM +0300, Ants Aasma wrote:
> > > Page encr
On Wed, 13 Oct 2021 at 00:25, Bruce Momjian wrote:
> On Tue, Oct 12, 2021 at 11:21:28PM +0300, Ants Aasma wrote:
> > On Tue, 12 Oct 2021 at 16:14, Bruce Momjian wrote:
> >
> > Well, how do you detect an all-zero page vs a page that encrypted to
> all
> >
contents of the page and they would then achieve that this page gets
ignored.
--
Ants Aasma
Senior Database Engineerwww.cybertec-postgresql.com
s. If any of those are insecure, they
> all are. Therefore, I don't see any reason to treat them differently.
>
We had to special case zero pages and not encrypt them because as far as I
can tell, there is no atomic way to extend a file and initialize it to
Enc(zero) in the same step.
--
Ants Aasma
Senior Database Engineerwww.cybertec-postgresql.com
in the tweak as that would limit the risk from re-use of the same
> tweak over time.
>
Right, my thought was to leave the first 8 bytes of pages, the LSN,
unencrypted and include the value in the tweak. Just tested that OpenSSL
aes-256-xts handles non multiple-of-16 messages just
On Wed, 6 Oct 2021 at 23:08, Bruce Momjian wrote:
> Yes, I would prefer we don't use the LSN. I only mentioned it since
> Ants Aasma mentioned LSN use above.
>
Is there a particular reason why you would prefer not to use LSN? I
suggested it because in my view having a variable tweak
g encryption mode choices given concerns expressed is next.
Currently a viable option seems to be AES-XTS with LSN added into the IV.
XTS doesn't have an issue with predictable IV and isn't totally broken in
case of IV reuse.
--
Ants Aasma
Senior Database Engineerwww.cybertec-postgresql.com
very performance critical spinlock had already been removed.
To be clear, I am not advocating for this patch to get included. I just had
the patch immediately available and it could have confirmed that using a
better lock fixes things.
--
Ants Aasma
Senior Database Engineerwww.cybertec-postgresql.com
nlock. A process holding the spinlock might easily get
scheduled out leading to excessive spinning by everybody. I think a simple
thing to try would be to replace the spinlock with LWLock.
I did a prototype patch that replaces spinlocks with futexes, but was not
able to find a workload wher
O= value that it's claiming to come from to automate replacement
of intermediate certificates, but not trust that every other sub-CA signed
by root and their sub-sub-CA-s are completely honest and secure.
Regards,
Ants Aasma
an. A highly parallel index only scan on a
fully cached index should create at least some spinlock contention.
Regards,
Ants Aasma
On Mon, 13 Apr 2020 at 23:16, Andres Freund wrote:
> > Still, if the reader does the splitting, then you don't need as much
> > IPC, right? The shared memory data structure is just a ring of bytes,
> > and whoever reads from it is responsible for the rest.
>
> I don't think so. If only one
rting to an
unindexed unlogged table. If we get the SIMD line splitting in, it
will be enough to overwhelm most I/O subsystems available today.
Regards,
Ants Aasma
mance issues would be basically
the same as a normal parallel insert workload. There may well be
bottlenecks there, but those could be tackled independently.
Regards,
Ants Aasma
Cybertec
ow of the leader. With option 2 data could be read directly into the
shared memory buffer. With future async io support, reading and
looking for tuple boundaries could be performed concurrently.
Regards,
Ants Aasma
Cybertec
at splitting into tuples needs to be made fast before
parallelizing makes any sense.
Regards,
Ants Aasma
[1]
https://www3.stats.govt.nz/2018census/Age-sex-by-ethnic-group-grouped-total-responses-census-usually-resident-population-counts-2006-2013-2018-Censuses-RC-TA-SA2-DHB.zip
fetch the
next state).
I whipped together a quick prototype that uses SIMD and bitmap
manipulations to do the equivalent of CopyReadLineText() in csv mode
including quotes and escape handling, this runs at 0.25-0.5 cycles per
byte.
Regards,
Ants Aasma
#include
#include
#include
#include
#include
#
On Wed, 19 Feb 2020 at 06:22, Amit Kapila wrote:
>
> On Tue, Feb 18, 2020 at 8:08 PM Ants Aasma wrote:
> >
> > On Tue, 18 Feb 2020 at 15:21, Amit Kapila wrote:
> > >
> > > On Tue, Feb 18, 2020 at 5:59 PM Ants Aasma wrote:
> > > >
&
On Tue, 18 Feb 2020 at 15:21, Amit Kapila wrote:
>
> On Tue, Feb 18, 2020 at 5:59 PM Ants Aasma wrote:
> >
> > On Tue, 18 Feb 2020 at 12:20, Amit Kapila wrote:
> > > This is something similar to what I had also in mind for this idea. I
> > > had thought
h attached if you'd
like to try that out.
Regards,
Ants Aasma
diff --git a/src/main.cpp b/src/main.cpp
index 9d33a85..2cf775c 100644
--- a/src/main.cpp
+++ b/src/main.cpp
@@ -185,7 +185,6 @@ bool find_indexes(const uint8_t * buf, size_t len, ParsedCSV & pcsv) {
#endif
simd_input in =
e larger
than the data buffer, but that doesn't seem like a major issue. Once the line is
buffered and begins inserting next worker can start buffering the next tuple.
Regards,
Ants Aasma
able. Ideally the
buffers would be at least big enough to absorb one of the workers getting
scheduled out for a timeslice, which could be up to tens of megabytes.
Regards,
Ants Aasma
[1] https://github.com/geofflangdale/simdcsv/
ose is to allow an external transaction manager to perform
> atomic global transactions across multiple databases or other transactional
> resources. Unless you're writing a transaction manager, you probably
> shouldn't be using PREPARE TRANSACTION.
Regards,
Ants Aasma
tation. I'll see if I can
pick up where that thread left off and push it along.
Regards,
Ants Aasma
Web: https://www.cybertec-postgresql.com
the table can't possibly have that many tuples. It
may make sense to allocate it based on estimated number of dead tuples and
resize if needed.
Regards,
Ants Aasma
Web: https://www.cybertec-postgresql.com
From 6101b360ea85a66aba093f98a83ae335983aa4a5 Mon Sep 17 00:00:00 2001
From: Ants Aasma
Date: Wed
dia for having a
reasoned discussion about technical design decisions.
> > In other words: maybe I'm wrong here, but it looks to me like we're
>
> laboriously reinventing the wheel when we could be working on
> > improving the working prototype.
>
> The work being done is buildi
On Thu, Mar 28, 2019 at 10:38 AM Christoph Berg wrote:
> Re: Ants Aasma 2019-03-27 <
> ca+csw_twxdrzdn2xsszbxej63dez+f6_hs3qf7hmxfenxsq...@mail.gmail.com>
> > Can you try with postgres compiled with CFLAGS="-O2 -march=native"?
> There's
> > a bit of low han
_per_gather = 0:
>
> select count(bid) from pgbench_accounts;
>
> no checksums: ~456ms
> with checksums: ~489ms
>
> 456.0/489 = 0.9325
>
> The cost of checksums is about 6.75% here.
>
Can you try with postgres compiled with CFLAGS="-O2 -march=native"? There's
a
e harmonic number, you are welcome
> and probably get your name on it:-)
>
There are pretty good approximations for s > 1.0 using Riemann zeta
function and Euler derived a formula for the s = 1 case.
I also noticed that i is int in this function, but n is int64. That seems
like an oversight.
Regards,
Ants Aasma
me interval, and when the credits run out the
process sleeps. With this type of scheme it would be reasonably
straightforward to let UPDATEs being blocked by REINDEX to transfer their
WAL insertion budgets to the REINDEX, making it get a larger piece of the
total throughput pie.
Regards,
Ants Aasma
t a maintenance command is a
background task and a normal DML query is a foreground task. This is not
true for all cases, users may want to throttle transactions doing lots of
DML to keep synchronous commit latencies for smaller transactions within
reasonable limits.
As a wild idea for how to handle the throttling, what if when all our wal
insertion credits are used up XLogInsert() sets InterruptPending and the
actual sleep is done inside ProcessInterrupts()?
Regards,
Ants Aasma
s
even though the code has been like that for a very long time. I was
actually mostly worried about extension code run by logging hook
causing the panic.
Regards,
Ants Aasma
a few lwlock acquisitions away and shouldn't make
any material difference. Patch to do so is attached.
Regards,
Ants Aasma
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 7375a78ffc..faa9690e48 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend
in a loop without attempting to grab the lock. In
PostgreSQL it's called only once per retry attempt.
Regards,
Ants Aasma
--
PostgreSQL Senior Consultant
www.cybertec-postgresql.com
Austria (HQ), Wiener Neustadt | Switzerland, Zürich | Estonia,
Tallinn | Uruguay, Montevideo
Facebook: www.fb.com/cybertec.postgresql
Twitter: www.twitter.com/PostgresSupport
nvalidation scan until something can be evicted.
--
Ants Aasma
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: https://www.cybertec-postgresql.com/
es).'
>
For reference, a typical datacenter SSD needs a queue depth of 128 to
saturate a single device. [1] Multiply that appropriately for RAID arrays.
Regards,
Ants Aasma
[1]
https://www.anandtech.com/show/12435/the-intel-ssd-dc-p4510-ssd-review-part-1-virtual-raid-on-cpu-vroc-scalability/3
bed, or a buffering microservice in front of
PostgreSQL like Aleksander recommended should fix data locality for
you. If you weren't running on RDS I would even propose using Redis as
the buffer with one key per driver and redis_fdw to make the data
accessible from within PostgreSQL.
Regards,
Ants Aasma
--
+43-670-6056265
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Web: https://www.cybertec-postgresql.com
e partitioned able will contain on average 1.2 interesting
tuples. So you still have almost one page read per row.
Regards,
Ants Aasma
--
+43-670-6056265
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Web: https://www.cybertec-postgresql.com
needs have
outgrown what RDS works well with and you are in for a painful move
sooner or later.
Regards,
Ants Aasma
--
+43-670-6056265
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Web: https://www.cybertec-postgresql.com
ng a bit
> faster.
I think this would also fix oracle_fdw crashing when postgres is
compiled with --with-ldap. At least RTLD_DEEPBIND helped. [1]
[1]
https://www.postgresql.org/message-id/CA%2BCSw_tPDYgnzCYW0S4oU0mTUoUhZ9pc7MRBPXVD-3Zbiwni9w%40mail.gmail.com
Ants Aasma
65 matches
Mail list logo
