Re: Lock contention high

[Ashkil Dighin]

Hi
I suspect lock contention and performance issues with __int128. And I would
like to check the performance by forcibly disabling int128(Maxalign16bytes)
and enable like long long(maxlign 8bytes).
 Is it possible to disable int128 in PostgreSQL?

On Thursday, October 28, 2021, Andres Freund  wrote:

> Hi,
>
> On October 27, 2021 2:44:56 PM PDT, Ashkil Dighin <
> ashkildighi...@gmail.com> 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 reduced to 0-3%?
>
> Probably not, or at least not easily. Because of the atomic instructions
> the locking also includes  some other costs (e.g. cache misses, serializing
> store buffers,...).
>
> There's a good bit we can do to increase the cache efficiency around
> buffer headers, but it won't get us quite that low I'd guess.
>
>
> >On pg-stat-activity shown LwLock as “BufferCounter” and “WalInsert”
>
> Without knowing what proportion they have to each and to non-waiting
> backends that unfortunately doesn't help that much..
>
> Andres
>
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
>

Re: postgresql13-llvm jit-13.5-1PGDG.rhel8.x86_64

[Mladen Gogala]

On 11/15/21 00:04, Pavel Stehule wrote:


Hi


There is a CentOS8-stream version which solves the problem but I
cannot
use that in the office. I will probably have to wait for another
month
before OL8 has everything that I need in its repositories. Now, the
question is what kind of an impact will running without llvm-jit
have?
According to the links below, llvm-jit effects are quite spectacular:

https://llvm.org/devmtg/2016-09/slides/Melnik-PostgreSQLLLVM.pdf

https://www.pgcon.org/2017/schedule/events/1092.en.html


When JIT was used on very large query with a lot of CASE expr, then 
JIT has a positive effect about 50%. On usual large queries, the 
effect of JIT was about 20%. Unfortunately, JIT is sensitive to 
estimation, and the JIT sometimes increases seconds to queries, 
although without JIT this query is executed in ms. When you use a 
query that can be well calculated in parallel, then positive effect of 
JIT is less.


Regards

Pavel


Thanks Pavel, you answered my question. I'll wait with the upgrade.

Regards

--

Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: postgresql13-llvm jit-13.5-1PGDG.rhel8.x86_64

[Rick Otten]

On Mon, Nov 15, 2021 at 8:57 AM Mladen Gogala 
wrote:

>
> On 11/15/21 00:04, Pavel Stehule wrote:
>
>
> Hi
>
>
>> There is a CentOS8-stream version which solves the problem but I cannot
>> use that in the office. I will probably have to wait for another month
>> before OL8 has everything that I need in its repositories. Now, the
>> question is what kind of an impact will running without llvm-jit have?
>> According to the links below, llvm-jit effects are quite spectacular:
>>
>> https://llvm.org/devmtg/2016-09/slides/Melnik-PostgreSQLLLVM.pdf
>>
>> https://www.pgcon.org/2017/schedule/events/1092.en.html
>
>
> When JIT was used on very large query with a lot of CASE expr, then JIT
> has a positive effect about 50%. On usual large queries, the effect of JIT
> was about 20%. Unfortunately, JIT is sensitive to estimation, and the JIT
> sometimes increases seconds to queries, although without JIT this query is
> executed in ms. When you use a query that can be well calculated in
> parallel, then positive effect of JIT is less.
>
> Regards
>
> Pavel
>
>
> Thanks Pavel, you answered my question. I'll wait with the upgrade.
>
>
>
FWIW, there was a lively discussion on the postgresql subreddit over the
weekend on JIT:
https://www.reddit.com/r/PostgreSQL/comments/qtsif5/cascade_of_doom_jit_and_how_a_postgres_update_led/

(lively for that subreddit anyway)

Re: Lock contention high

[Tom Lane]

Ashkil Dighin  writes:
> I suspect lock contention and performance issues with __int128. And I would
> like to check the performance by forcibly disabling int128(Maxalign16bytes)
> and enable like long long(maxlign 8bytes).
>  Is it possible to disable int128 in PostgreSQL?

Sure, you can build without it --- easiest way would be to modify
pg_config.h after the configure step.  But the idea that it has
something to do with lock contention seems like nonsense.

regards, tom lane

Re: Need help identifying a periodic performance issue.

[Justin Pryzby]

On Tue, Nov 16, 2021 at 04:43:25AM +, Robert Creager wrote:
> We’re executing the following copy to fill a table with approximately 5k 
> records, then repeating for a total of 250k records.  Normally, this copy 
> executes < 1 second, with the entire set taking a couple of minutes. The 
> problem is not reproducible on command, but usually within a couple of hours 
> of starting some test runs.
> 
> COPY ds3.blob (byte_offset, checksum, checksum_type, id, length, object_id) 
> FROM STDIN WITH DELIMITER AS ‘|’
> 
> But, occasionally we get into a huge performance bottleneck for about 2 
> hours, where these copy operations are taking 140 seconds or so
> 
> Nov 15 22:25:49 sm4u-34 postgres[5799]: [381-1] 
> db=tapesystem,user=Administrator,app=PostgreSQL JDBC Driver,client=127.0.0.1 
> LOG:  duration: 145326.293 ms  statement: COPY ds3.blob (byte_offset, 
> checksum, checksum_type, id, length, object_id) FROM STDIN WITH DELIMITER AS 
> '|'

> I’m logging statements with pgbadger monitoring the logs. There are no 
> apparent auto-vacuum’s running, nor any vacuums, nor anything at all really. 
> Other select queries around that time frame are executing normally.

What about checkpoints ?

Would you show the "^checkpoint starting" and "^checkpoint complete" logs
surrounding a slow COPY ?

> We’re coming from PostgreSQL 9.6 on FreeBSD 11 where we did not see this 
> problem, but have a major release upgrade happening.  I’m checking to see if 
> this machine was updated or was a fresh install.
>  PostgreSQL 13.2 on amd64-portbld-freebsd13.0, compiled by FreeBSD clang 
> version 11.0.1 (g...@github.com:llvm/llvm-project.git 
> llvmorg-11.0.1-0-g43ff75f2c3fe), 64-bit
> 
> Changes made to the settings in the postgresql.conf file
>  checkpoint_timeout  | 30min   | 
> configuration file
>  log_checkpoints | on  | 
> configuration file
>  log_lock_waits  | on  | 
> configuration file
...
>  shared_buffers  | 21679MB | 
> configuration file

> Operating system and version:
> FreeBSD sm4u-34 13.0-STABLE FreeBSD 13.0-STABLE #0: Mon Sep 13 10:11:57 MDT 
> 2021

> These are the system calls made over 30 seconds from Postgres during a 
> slowdown.
...
>   fsync27

-- 
Justin

Re: Need help identifying a periodic performance issue.

[Thomas Munro]

On Tue, Nov 16, 2021 at 5:43 PM Robert Creager  wrote:
> One CPU is pegged, the data has been sent over STDIN, so Postgres is not 
> waiting for more, there are no other queries running using this select:

So PostgreSQL is eating 100% CPU, with no value shown in
wait_event_type, and small numbers of system calls are counted.  In
that case, is there an interesting user stack that jumps out with a
profiler during the slowdown (or the kernel version, stack())?

sudo dtrace -n 'profile-99 /arg0/ { @[ustack()] = count(); } tick-10s
{ exit(0); }'