subset thereof).
>From what I know of btrfs, I don't think you want direct IO though. Possibly
for WAL, but definitely not for data. IIRC it currently can cause corruption.
Greetings,
Andres Freund
if you bind both server and client to the same
CPU socket.
numactl --membind 1 --cpunodebind 1
forces programs to allocate memory and run on a specific CPU socket.
Greetings,
Andres Freund
ale does flush WAL too frequently for some
reason...
Greetings,
Andres Freund
t might be beneficial to
increase effective_io_concurrency some.
> Recheck Cond: ((ts >= '2023-03-16
> 10:51:28.397+01'::timestamp with time zone) AND (ts <= '2023-04-16
> 10:51:28.397+02'::timestamp with time zone))
> Rows Removed by Index Recheck: 2131
> Heap Blocks: lossy=4742
The lossiness might also incur some overhead, so increasing work_mem a bit
will help some.
Greetings,
Andres Freund
Hi,
On 2023-02-10 20:45:39 +0100, Pavel Stehule wrote:
> But for significant improvements it needs some form of JIT (Postgres has JIT
> for SQL expressions, but it is not used for PLpgSQL expressions). On second
> hand, PL/pgSQL is not designed (and usually) not used for extensive numeric
>
mory. There's a small
degradation of performance due to the increased size of some shared
datastructures, most prominently the lock table for heavyweight locks.
Greetings,
Andres Freund
Hi,
On November 30, 2022 3:47:32 AM PST, Andrew Dunstan wrote:
>
>On 2022-11-29 Tu 16:06, David Rowley wrote:
>> On Wed, 30 Nov 2022 at 03:31, Tom Lane wrote:
>>> Alvaro Herrera writes:
IMO it was a mistake to turn JIT on in the default config, so that's one
thing you'll likely want
onnections are made, and thus how quickly the backlog is
filled.
Do you get the same behaviour if you set net.core.somaxconn to higher than the
number of connections? IIRC you need to restart postgres for it to take
effect.
Greetings,
Andres Freund
a simple LEFT JOINed query when
> under high load (> 1000 queries / sec). Furthermore the CPU usage is quite
> high.
We can't say much about aurora. It's a heavily modified fork of postgres. Did
you reproduce this with vanilla postgres? And if so, do you have it in a form
that somebody could try out?
Greetings,
Andres Freund
Hi,
On March 4, 2022 10:42:39 AM PST, Tom Lane wrote:
>aditya desai writes:
>> One of the service layer app is inserting Millions of records in a table
>> but one row at a time. Although COPY is the fastest way to import a file in
>> a table. Application has a requirement of processing a row
cks, but instead just swapping in the
currently-last lock.
Greetings,
Andres Freund
(*) IIRC the issue is when writing back we try to write back multiple buffers
at once (using conditional lock acquisition to avoid deadlocks). Those then
are likely released in FIFO order. I think it's now not a problem anymore
Hi,
On October 27, 2021 2:44:56 PM PDT, Ashkil Dighin
wrote:
>Hi,
>Yes, lock contention reduced with postgresqlv14.
>Lock acquire reduced 18% to 10%
>10.49 %postgres postgres[.] LWLockAcquire
>5.09% postgres postgres[.] _bt_compare
>
>Is lock contention can be
Hi,
On 2021-10-25 18:38:40 -0600, Michael Lewis wrote:
> On Mon, Oct 25, 2021, 5:36 PM Andres Freund wrote:
> If your hot data set is actually larger than s_b, I'd recommend trying a
> larger s_b. It's plausible that a good chunk of lock contention is from
> that.
> How much larg
gt; Postgres.conf used in Baremetal
>
> shared_buffers = 128GB(1/4 th RAM size)
> effective_cachesize=392 GB(1/3 or 75% of RAM size)
If your hot data set is actually larger than s_b, I'd recommend trying a
larger s_b. It's plausible that a good chunk of lock contention is from that.
Greetings,
Andres Freund
Hi,
On 2021-04-13 23:04:50 -0400, Tom Lane wrote:
> Andres Freund writes:
> > On 2021-04-13 19:16:46 -0400, Tom Lane wrote:
> >> Like this? This passes check-world, modulo the one very-unsurprising
> >> regression test change. I've not tried to do any performan
ng.
I wonder if there's a realistic chance it could create additional
deadlocks that don't exist right now?
Would it be a problem that we'd still release the locks on catalog
tables early, but not on its toast table?
Greetings,
Andres Freund
ted improvement?
The ability to lock a toast table? Yea, it might be worth doing that. I
seem to recall this being discussed not too long ago...
Greetings,
Andres Freund
Hi,
On 2021-04-13 09:33:48 -0700, Paul Friedman wrote:
> I've attached the 2 perf reports. From the 2nd one, I can see lots of
> time waiting for TOAST table locks on the geometry column, but I
> definitely don't fully understand the implications or why LockManager
> would be struggling here.
n turn is what lwlocks
end up using on linux when the lock is contended.
Check the second half of:
https://www.postgresql.org/message-id/20210412215738.xytq33wlciljyva5%40alap3.anarazel.de
Greetings,
Andres Freund
n't just the first few
milliseconds of starting those 60 queries, there shouldn't be any
additional "heavyweight locks" taken given the duration of your queries.
The futex profile hopefully will tell us from where that is coming
from...
Greetings,
Andres Freund
-graph dwarf -e syscalls:sys_enter_futex -a sleep 3
perf report --no-children --sort comm,symbol
Greetings,
Andres Freund
Hi,
On 2020-04-29 10:50:54 +0200, Laurenz Albe wrote:
> On Wed, 2020-04-29 at 08:54 +0200, Marc Rechté wrote:
> > I am trying to figure out the recommended settings for a PG dedicated
> > machine regarding NUMA.
> >
> > I assume that the shared buffers are using Huge Phages only. Please
> >
of common
code/infrastructure between deleting row versions that are invisible due
to no backend having a snapshot to see them (presumably inferred via
xmin/xmax), and newly created row versions within a transaction that are
invisible because there's no snapshot with that cid.
Greetings,
Andres Freund
es the input slot to be materialized into a HeapTuple
(should probably be MinimalTuple), which often the input will not
yet be. So I think it'll often be much worse than 10x.
Greetings,
Andres Freund
Hi,
On 2019-12-16 17:48:16 -0500, Tom Lane wrote:
> Hmm, that's an interesting thought. The OP did say the CPU type,
> but according to Intel's spec page for it [1] the difference between
> base and turbo frequency is only 4.0 vs 4.2 GHz, which doesn't seem
> like enough to explain the results
gories.category_id unique? Does the plan change if you ANALYZE
the tables?
This plan doesn't look like it'd actually take long, if the estimates
are correct.
> What on Earth could be causing this simple query to be running 100% CPU for
> hours?
Is the DELETE actually taking that long, or the query you showed the
explain for, or both?
Greetings,
Andres Freund
everyone using PG,
even without access to special purpose hardware.
Greetings,
Andres Freund
gstattuple.html
not the pg_stat_user_indexes entry...
Greetings,
Andres Freund
the preceding message contains
> advice relating to a Federal tax issue, unless expressly stated otherwise the
> advice is not intended or written to be used, and it cannot be used by the
> recipient or any other taxpayer, for the purpose of avoiding Federal tax
> penalties, and was not written to support the promotion or marketing of any
> transaction or matter discussed herein.
GNGNGGRR.
Greetings,
Andres Freund
Hi,
On August 24, 2019 12:41:03 PM PDT, Tom Lane wrote:
>Jeff Janes writes:
>> Most of the time is not after the clock stops, but before the
>stepwise
>> ANALYZE clock starts. If you just do an EXPLAIN rather than EXPLAIN
>> ANALYZE, that is also slow. The giant hash table is created during
Hi,
On 2019-08-20 19:55:56 +0200, Felix Geisendörfer wrote:
> > On 20. Aug 2019, at 19:32, Andres Freund wrote:
> > FWIW, that's not a mis-estimate I'm getting on master ;). Obviously
> > that doesn't actually address your concern...
>
> I suppose this is thanks to t
s libc-2.28.so [.] __memset_avx2_erms
- 90.94% page_fault
__memset_avx2_erms
tuplehash_allocate
tuplehash_create
BuildTupleHashTableExt
build_hash_table
ExecInitAgg
ExecInitNode
InitPlan
standard_ExecutorStart
Greetings,
Andres Freund
t; That can matter quite a bit.
Why aren't you surprised? I can easily get 20k+ write transactions/sec
on my laptop, with synchronous_commit=off. With appropriate
shared_buffers and other settings, the disk speed shouldn't matter that
much for in insertion mostly workload.
Greetings,
Andres Freund
that?
Are your clients in the same datacenter as your database? Otherwise it
could be that you're mostly seeing latency effects.
Greetings,
Andres Freund
(
SELECT datname,
pd.blks_read - ps.blks_read AS blks_read,
pd.blks_hit - ps.blks_hit AS blks_hit
FROM pg_stat_database pd JOIN pg_stat_database_snap ps USING (datname) )
pd_diff;
Greetings,
Andres Freund
Ivan Kartyshov, reviewed by Tomas Vondra and Robert Haas.
Discusssion:
so everything from 10 onwards ought to be fine.
Greetings,
Andres Freund
Hi,
On 2019-05-07 10:32:45 -0700, Andres Freund wrote:
> pgbench -i -q -s 96 && pgbench -n -c 8 -j 8 -T 100 -P1
possibly also worthwhile to note: Adding -M prepared (which I think
phoronix doesn't specify) makes this considerably faster...
Greetings,
Andres Freund
as a whole, not just a single TU at a time. This enables
> it to perform additional aggressive optimization.
Note that the flags described don't enable LTO.
Greetings,
Andres Freund
t the data types we collect and what we use this for and
> your related rights is set out in our online privacy policy at
> https://www.gam.com/en/legal/privacy-policy.
> Please familiarise yourself with this policy and check it from time to time
> for updates as it supplements this notice.
This is a public list.
Greetings,
Andres Freund
the custom plan leads to the generic plan to always be preferred. In
particular for indexed queries, on system that set random_page_cost =
seq_page_cost = 1 (due to SSD or expectation that workload is entirely
cached), the added cost from cached_plan_cost() can be noticable in
comparison to the estimated cost of the total query.
Greetings,
Andres Freund
t testing it with synchronous_commit=off instead. That's about
as fast for this type of workload, doesn't have cluster corruption
issues, the window of a transaction not persisting in case of a crash is
very small, and it can just set by any user in individual sessions.
Greetings,
Andres Freund
initial_cost_mergejoin
> try_mergejoin_path
> add_paths_to_joinrel
> make_join_rel
> join_search_one_level
> standard_join_search
> make_one_rel
> query_planner
> ...
I suspect some of this might be related to < 11 not having the following
commit:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3ca930fc39ccf987c1c22fd04a1e7463b5dd0dfd
Greetings,
Andres Freund
d 8KB postgres block size.
There still might be some benefit of different FS block sizes, but it's
not going to be related directly to IOPS.
Greetings,
Andres Freund
Hi,
On 2019-03-06 19:21:33 +0100, Tobias Gierke wrote:
> On 06.03.19 18:42, Andres Freund wrote:
> >
> > It's hard to know precisely without running a profile of the
> > workload. My suspicion is that the bottleneck in this query is the use
> > of numeric, which
n JITing aggregation.
There's a lot of further improvements on the table with better JIT code
generation, I just haven't gotten around implementing those :(
Greetings,
Andres Freund
e=74.799..74.799 rows=0 loops=1)
> Filter: (SubPlan 1)
> Rows Removed by Filter: 30044
> SubPlan 1
> -> Subquery Scan on subq_1 (cost=0.00..0.02 rows=1 width=0) (actual
> time=0.002..0.002 rows=0 loops=30044)
> -> HashAggregate (cost=0.00..0.01 rows=1 width=20) (actual
> time=0.000..0.000 rows=0 loops=30044)
> Group Key: ref_0.o_entry_d, c_credit
> -> Result (cost=0.00..0.00 rows=0 width=20) (actual
> time=0.000..0.000 rows=0 loops=30044)
> One-Time Filter: false
> Planning Time: 0.350 ms
> Execution Time: 79.237 ms
I think that might be fixed in the latest point release. I screwed up
and made resets of tuple hash tables (and there's 30044 of those here)
more expensive. It's fixed in the latest minor release however.
Greetings,
Andres Freund
, so I appreciate self contained email.
Greetings,
Andres Freund
u want to play around with it?
Greetings,
Andres Freund
On 2018-12-08 15:23:19 -0800, Rob Sargent wrote:
>
>
> > On Dec 8, 2018, at 3:12 PM, Andres Freund wrote:
> >
> > On 2018-12-08 12:06:23 -0800, Jeremy Schneider wrote:
> >> On RDS PostgreSQL, the default is 25% of your server memory. This seems
> >&
I thought it would also have been related to
> https://www.postgresql.org/message-id/CAMkU%3D1xPqHP%3D7YPeChq6n1v_qd4WGf%2BZvtnR-b%2BgyzFqtJqMMQ%40mail.gmail.com
Neither of these are related to the problem.
Greetings,
Andres Freund
called once for each column in
a select list (or using or ...). IIRC we've hit this once when I was at
citus, too.
We really should be usign a more appropriate datastructure here - very
likely a hashtable. Unfortunately such a change would likely be a bit
too much to backpatch...
Greetings,
On 2018-08-17 15:21:19 +0200, Alexis Lê-Quôc wrote:
> On Tue, Aug 14, 2018 at 7:50 PM Andres Freund wrote:
>
> > Hi,
> >
> > On 2018-08-14 10:46:45 -0700, Andres Freund wrote:
> > > On 2018-08-14 15:18:55 +0200, Alexis Lê-Quôc wrote:
> > >
will likely write the full page very often. Yes, my hands are
> tied!
Why is that a requirement / how is specifically phrased? Is it a bounded
recovery time?
Greetings,
Andres Freund
[k]
> copy_user_enhanced_fast_string
Possible that a slightly bigger shared buffer would help you.
It'd probably more helpful to look at a perf report --no-children for
this kind of analysis.
Greetings,
Andres Freund
Hi,
On 2018-07-31 12:56:26 -0400, Jeff Janes wrote:
> On Mon, Jul 30, 2018 at 1:23 PM, Andres Freund wrote:
>
> > On 2018-07-30 07:19:07 -0400, Jeff Janes wrote:
> >
> > > And indeed, in my hands JIT makes it almost 3 times worse.
> >
> > Not in my measure
time show up there?
As my timings showed, I don't see the slowdown you're reporting. Could
you post a few EXPLAIN ANALYZEs?
Greetings,
Andres Freund
Hi,
On 2018-07-30 18:01:34 +0200, Pavel Stehule wrote:
> look on
> http://www.postgresql-archive.org/PATCH-LLVM-tuple-deforming-improvements-td6029385.html
> thread, please.
Given the results I just posted in the sibling email I don't think those
issues apply here.
Greetings,
Andres Freund
n to a near
optimal loop around the intermittent bigint columns (which we deform
because we use a slot - at some point we're going to have to do
better). No checks for the NULL bitmap, no alignment considerations,
all that's optimized away.
Greetings,
Andres Freund
Hi,
On 2018-07-20 08:27:34 -0400, Robert Haas wrote:
> On Thu, Jul 19, 2018 at 4:32 PM, Andres Freund wrote:
> >> 1. Why the error message changes? If there's a good reason, it should
> >> be done as a separate commit, or at least well-documented in the
> >> c
Hi,
On 2018-07-18 14:34:34 -0400, Robert Haas wrote:
> On Sat, Jul 7, 2018 at 4:01 PM, Andres Freund wrote:
> > FWIW, here's a rebased version of this patch. Could probably be polished
> > further. One might argue that we should do a bit more wide ranging
> > changes
Hi,
On 2017-12-08 13:44:37 -0800, Andres Freund wrote:
> On 2017-12-08 10:17:34 -0800, Andres Freund wrote:
> > the strtoll is libc functionality triggered by pg_atoi(), something I've
> > seen show up in numerous profiles. I think it's probably time to have
> > our
s suggest that the sweet spot is
more likely to be an order of magnitude or two bigger. Depends a bit on
your workload (including size of scans and concurrency) obviously.
Greetings,
Andres Freund
62 matches
Mail list logo
