pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedLock contention high
26+ messages / 14 participants
[nested] [flat]
* Lock contention high
@ 2021-10-12 07:35 Ashkil Dighin <ashkildighin76@gmail.com>
0 siblings, 7 replies; 26+ messages in thread
From: Ashkil Dighin @ 2021-10-12 07:35 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Hi,
Lock contention observed high in PostgreSQLv13.3
The source code compiled with GNC(GCCv11.x)
PostgreSQL version: 13.3
Operating system: RHEL8.3
Kernel name:4.18.0-305.10.2.el8_4.x86_64
RAM Size:512GB
SSD: 1TB
The environment used IBM metal and test benchmark environment HammerDbv4.2
Test case :TPC-C
Perf data for 24vu(TPC-C)
--------------------------------
18.99% postgres postgres [.] LWLockAcquire
7.09% postgres postgres [.] _bt_compare
8.66% postgres postgres [.] LWLockRelease
2.28% postgres postgres [.] GetSnapshotData
2.25% postgres postgres [.] hash_search_with_hash_value
2.11% postgres postgres [.] XLogInsertRecord
1.98% postgres postgres [.] PinBuffer
1.Is there a way to tune the lock contention ?
2.Is any recommendations to tune/reduce the lock contention via
postgres.conf
Postgres.conf used in Baremetal
========================
shared_buffers = 128GB(1/4 th RAM size)
effective_cachesize=392 GB(1/3 or 75% of RAM size)
huge_pages = on
temp_buffers = 4000MB
work_mem = 4000MB
maintenance_work_mem = 512MB
autovacuum_work_mem = -1
max_stack_depth = 7MB
dynamic_shared_memory_type = posix
max_files_per_process = 4000
effective_io_concurrency = 32
wal_level = minimal
synchronous_commit = off
wal_buffers = 512MB
checkpoint_timeout = 1h
checkpoint_completion_target = 1
checkpoint_warning = 0
log_min_messages = error
log_min_error_statement = error
log_timezone = 'GB'
autovacuum = off
datestyle = 'iso, dmy'
timezone = 'GB'
lc_messages = 'en_GB.UTF-8'
lc_monetary = 'en_GB.UTF-8'
lc_numeric = 'en_GB.UTF-8'
lc_time = 'en_GB.UTF-8'
default_text_search_config = 'pg_catalog.english'
max_locks_per_transaction = 64
max_pred_locks_per_transaction = 64
Best Regards
Anil
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Lock contention high
@ 2021-10-12 10:29 Mikhail Zhilin <zhilin.ms@phystech.edu>
parent: Ashkil Dighin <ashkildighin76@gmail.com>
6 siblings, 1 reply; 26+ messages in thread
From: Mikhail Zhilin @ 2021-10-12 10:29 UTC (permalink / raw)
To: Ashkil Dighin <ashkildighin76@gmail.com>; pgsql-performance@lists.postgresql.org
Hi,
How many sockets are on motherboard?
What is CPU model and interconnect type (UPI?)?
Can you share output of "lscpu"?
If you have more than 1 NUMA node it may be worth to run PostgreSQL in
single NUMA node via taskset. It will eliminate access to remote memory
and speed up processing.
Thanks,
Michael.
On 10/12/21 10:35 AM, Ashkil Dighin wrote:
>
> Hi,
> Lock contention observed high in PostgreSQLv13.3
> The source code compiled with GNC(GCCv11.x)
> PostgreSQL version: 13.3
> Operating system: RHEL8.3
> Kernel name:4.18.0-305.10.2.el8_4.x86_64
> RAM Size:512GB
> SSD: 1TB
> The environment used IBM metal and test benchmark environment HammerDbv4.2
> Test case :TPC-C
>
> Perf data for 24vu(TPC-C)
> --------------------------------
>
> 18.99% postgres postgres [.] LWLockAcquire
> 7.09% postgres postgres [.] _bt_compare
> 8.66% postgres postgres [.] LWLockRelease
> 2.28% postgres postgres [.] GetSnapshotData
> 2.25% postgres postgres [.] hash_search_with_hash_value
> 2.11% postgres postgres [.] XLogInsertRecord
> 1.98% postgres postgres [.] PinBuffer
>
> 1.Is there a way to tune the lock contention ?
> 2.Is any recommendations to tune/reduce the lock contention via
> postgres.conf
>
> Postgres.conf used in Baremetal
> ========================
> shared_buffers = 128GB(1/4 th RAM size)
> effective_cachesize=392 GB(1/3 or 75% of RAM size)
> huge_pages = on
> temp_buffers = 4000MB
> work_mem = 4000MB
> maintenance_work_mem = 512MB
> autovacuum_work_mem = -1
> max_stack_depth = 7MB
> dynamic_shared_memory_type = posix
> max_files_per_process = 4000
> effective_io_concurrency = 32
> wal_level = minimal
> synchronous_commit = off
> wal_buffers = 512MB
> checkpoint_timeout = 1h
> checkpoint_completion_target = 1
> checkpoint_warning = 0
> log_min_messages = error
> log_min_error_statement = error
> log_timezone = 'GB'
> autovacuum = off
> datestyle = 'iso, dmy'
> timezone = 'GB'
> lc_messages = 'en_GB.UTF-8'
> lc_monetary = 'en_GB.UTF-8'
> lc_numeric = 'en_GB.UTF-8'
> lc_time = 'en_GB.UTF-8'
> default_text_search_config = 'pg_catalog.english'
> max_locks_per_transaction = 64
> max_pred_locks_per_transaction = 64
>
> Best Regards
> Anil
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Lock contention high
@ 2021-10-12 14:39 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Ashkil Dighin <ashkildighin76@gmail.com>
6 siblings, 1 reply; 26+ messages in thread
From: Laurenz Albe @ 2021-10-12 14:39 UTC (permalink / raw)
To: Ashkil Dighin <ashkildighin76@gmail.com>; pgsql-performance@lists.postgresql.org
On Tue, 2021-10-12 at 13:05 +0530, Ashkil Dighin wrote:
> Perf data for 24vu(TPC-C)
> --------------------------------
>
> 18.99% postgres postgres [.] LWLockAcquire
> 7.09% postgres postgres [.] _bt_compare
> 8.66% postgres postgres [.] LWLockRelease
> 2.28% postgres postgres [.] GetSnapshotData
> 2.25% postgres postgres [.] hash_search_with_hash_value
> 2.11% postgres postgres [.] XLogInsertRecord
> 1.98% postgres postgres [.] PinBuffer
>
> 1.Is there a way to tune the lock contention ?
How many concurrent sesions are you running?
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Lock contention high
@ 2021-10-12 15:37 Mladen Gogala <gogala.mladen@gmail.com>
parent: Ashkil Dighin <ashkildighin76@gmail.com>
6 siblings, 0 replies; 26+ messages in thread
From: Mladen Gogala @ 2021-10-12 15:37 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
On 10/12/21 03:35, Ashkil Dighin wrote:
> 1.Is there a way to tune the lock contention ?
Lock contention is usually an application issue. Application processes
are stepping on each other's toes. I have never seen a situation where
the database would be slow with managing locks. Postgres uses an
in-memory queue manager which is, generally speaking, very fast.
Applications usually do stupid things. I've seen GUI doing "SELECT FOR
UPDATE". And then the operator decided to have lunch. I'll leave the
rest to your imagination.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Lock contention high
@ 2021-10-13 16:48 Justin Pryzby <pryzby@telsasoft.com>
parent: Ashkil Dighin <ashkildighin76@gmail.com>
6 siblings, 1 reply; 26+ messages in thread
From: Justin Pryzby @ 2021-10-13 16:48 UTC (permalink / raw)
To: Ashkil Dighin <ashkildighin76@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Tue, Oct 12, 2021 at 01:05:12PM +0530, Ashkil Dighin wrote:
> Hi,
> Lock contention observed high in PostgreSQLv13.3
> The source code compiled with GNC(GCCv11.x)
> PostgreSQL version: 13.3
> Operating system: RHEL8.3
> Kernel name:4.18.0-305.10.2.el8_4.x86_64
> RAM Size:512GB
> SSD: 1TB
> The environment used IBM metal and test benchmark environment HammerDbv4.2
> Test case :TPC-C
>
> Perf data for 24vu(TPC-C)
> --------------------------------
>
> 18.99% postgres postgres [.] LWLockAcquire
> 7.09% postgres postgres [.] _bt_compare
> 8.66% postgres postgres [.] LWLockRelease
...
> 1.Is there a way to tune the lock contention ?
> 2.Is any recommendations to tune/reduce the lock contention via postgres.conf
I think you'd want to find *which* LW locks are being waited on, to see if it's
something that can be easily tuned.
You can check pg_stat_activity, or maybe create a cronjob to record its content
for later analysis.
--
Justin
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Lock contention high
@ 2021-10-13 18:15 MichaelDBA <MichaelDBA@sqlexec.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 26+ messages in thread
From: MichaelDBA @ 2021-10-13 18:15 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: Ashkil Dighin <ashkildighin76@gmail.com>; pgsql-performance@lists.postgresql.org
> 1.Is there a way to tune the lock contention ?
> 2.Is any recommendations to tune/reduce the lock contention via postgres.conf
I think you'd want to find *which* LW locks are being waited on, to see if it's
something that can be easily tuned.
You can check pg_stat_activity, or maybe create a cronjob to record its content
for later analysis.
Hello,
Also turn on log_lock_waits so you can evaluate the actual SQL causing
the problems in the PG log files. Thinking ahead, you may want to
consider if using advisory locks from the application side of things
might be helpful to manage locks in a more pessimistic way. Also, join
with pg_locks table to find out the specific resources that are in
contention.
Regards,
Michael Vitale
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Lock contention high
@ 2021-10-13 18:57 Paul Friedman <paul.friedman@streetlightdata.com>
parent: MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 1 reply; 26+ messages in thread
From: Paul Friedman @ 2021-10-13 18:57 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; Ashkil Dighin <ashkildighin76@gmail.com>; pgsql-performance@lists.postgresql.org
Are you using PostGIS?
If so, there is an issue with TOAST table locking having these symptoms.
---Paul
On Wed, Oct 13, 2021 at 11:15 AM MichaelDBA <MichaelDBA@sqlexec.com> wrote:
> 1.Is there a way to tune the lock contention ?
> 2.Is any recommendations to tune/reduce the lock contention via postgres.conf
>
> I think you'd want to find *which* LW locks are being waited on, to see if it's
> something that can be easily tuned.
>
> You can check pg_stat_activity, or maybe create a cronjob to record its content
> for later analysis.
>
>
> Hello,
>
> Also turn on log_lock_waits so you can evaluate the actual SQL causing
> the problems in the PG log files. Thinking ahead, you may want to consider
> if using advisory locks from the application side of things might be
> helpful to manage locks in a more pessimistic way. Also, join with
> pg_locks table to find out the specific resources that are in contention.
>
> Regards,
> Michael Vitale
>
>
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Lock contention high
@ 2021-10-13 19:05 Peter Geoghegan <pg@bowt.ie>
parent: Ashkil Dighin <ashkildighin76@gmail.com>
6 siblings, 1 reply; 26+ messages in thread
From: Peter Geoghegan @ 2021-10-13 19:05 UTC (permalink / raw)
To: Ashkil Dighin <ashkildighin76@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Tue, Oct 12, 2021 at 12:45 AM Ashkil Dighin <ashkildighin76@gmail.com> wrote:
> Lock contention observed high in PostgreSQLv13.3
> The source code compiled with GNC(GCCv11.x)
> PostgreSQL version: 13.3
> Operating system: RHEL8.3
> Kernel name:4.18.0-305.10.2.el8_4.x86_64
> RAM Size:512GB
> SSD: 1TB
> The environment used IBM metal and test benchmark environment HammerDbv4.2
> Test case :TPC-C
You didn't say how many TPC-C warehouses you used. In my experience,
people sometimes run TPC-C with relatively few, which will tend to
result in extreme contention on certain B-Tree leaf pages. (My
experiences are with BenchmarkSQL, but I can't imagine HammerDB is too
much different.)
Assuming that's the case here, for you, then it's not clear that you
have a real problem. You're really not supposed to run the benchmark
in that way, per the TPC-C spec, which strictly limits the number of
transactions per minute per warehouse -- for better or worse, valid
results generally require that you use lots of warehouses to get a
very large database (think terabytes). If you run the benchmark with
100 warehouses or less, on a big server, then the contention you'll
see will be out of all proportion to what you're ever likely to see in
the real world.
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Lock contention high
@ 2021-10-14 01:54 Jeremy Schneider <schneider@ardentperf.com>
parent: Ashkil Dighin <ashkildighin76@gmail.com>
6 siblings, 1 reply; 26+ messages in thread
From: Jeremy Schneider @ 2021-10-14 01:54 UTC (permalink / raw)
To: Ashkil Dighin <ashkildighin76@gmail.com>; pgsql-performance@lists.postgresql.org
Your settings are interesting, I'm curious what the goal is for this
particular hammerdb exercise.
A few comments inline
On 10/12/21 00:35, Ashkil Dighin wrote:
>
> Postgres.conf used in Baremetal
> ========================
> maintenance_work_mem = 512MB
only a half GB memory for autovac? (it will have a mandatory run as soon
as you hit 200 mil XIDs, seems like you'd want the full max 1GB for it)
> synchronous_commit = off
> checkpoint_timeout = 1h
> checkpoint_completion_target = 1
> checkpoint_warning = 0
curious about this, seems you're just looking to understand how much
throughput you can get with a config that would not be used on a real system
> autovacuum = off
i assume you understand that autovacuum will still run when you hit 200
mil XIDs. this setting seems incongruent with the previous settings,
because it seemed like you were going for throughput, which generally
requires autovacuum to be more aggressive rather than less aggressive.
assuming the benchmark runs for a properly sufficient length of time,
this setting will slow things down because of accumulating bloat.
Just a few opinions, I might be wrong, hope the feedback is helpful. :)
-Jeremy
--
http://about.me/jeremy_schneider
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Lock contention high
@ 2021-10-14 02:11 Peter Geoghegan <pg@bowt.ie>
parent: Jeremy Schneider <schneider@ardentperf.com>
0 siblings, 0 replies; 26+ messages in thread
From: Peter Geoghegan @ 2021-10-14 02:11 UTC (permalink / raw)
To: Jeremy Schneider <schneider@ardentperf.com>; +Cc: Ashkil Dighin <ashkildighin76@gmail.com>; pgsql-performance@lists.postgresql.org
On Wed, Oct 13, 2021 at 6:54 PM Jeremy Schneider
<schneider@ardentperf.com> wrote:
> only a half GB memory for autovac? (it will have a mandatory run as soon
> as you hit 200 mil XIDs, seems like you'd want the full max 1GB for it)
While anti-wraparound vacuums will become a problem for TPC-C (unless
you tune for it), it's not too sensitive to mwm. You just don't end up
accumulating too many TIDs to delete from indexes in practice, even
though the overhead from VACUUM is a concern. The new autovacuum
instrumentation in Postgres 14 makes this far clearer.
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Lock contention high
@ 2021-10-14 06:03 Ashkil Dighin <ashkildighin76@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 26+ messages in thread
From: Ashkil Dighin @ 2021-10-14 06:03 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hi
Captured the concurrent session with Netsat and pg-stat-actvity. Is the
procedure the right way to capture concurrent sesssions in postgresql?
netstat -a | grep postgres tcp 0 0 0.0.0.0:postgres 0.0.0.0:* LISTEN tcp 0
0 :postgres :53984 ESTABLISHED tcp 0 0 :postgres :54012 ESTABLISHED tcp 0
74 :postgres :53998 ESTABLISHED tcp 0 73 :53986 :postgres ESTABLISHED tcp 0
0 :54004 :postgres ESTABLISHED tcp 0 75 :53990 :postgres ESTABLISHED tcp 0
0 :postgres :53994 ESTABLISHED tcp 0 0 :postgres :54004 ESTABLISHED tcp 0
106 :53978 :postgres ESTABLISHED tcp 0 0 :postgres :53972 ESTABLISHED tcp 0
90 :54000 :postgres ESTABLISHED tcp 0 0 :postgres :54018 ESTABLISHED tcp 0
0 :54016 :postgres ESTABLISHED tcp 0 0 :postgres :53986 ESTABLISHED tcp 0
59 :54006 :postgres ESTABLISHED tcp 0 74 :postgres :53982 ESTABLISHED tcp 0
75 :53994 :postgres ESTABLISHED tcp 0 0 :53970 :postgres ESTABLISHED tcp 0
0 :postgres :53974 ESTABLISHED tcp 0 76 :53988 :postgres ESTABLISHED tcp 0
0 :postgres :54008 ESTABLISHED tcp 0 93 :54014 :postgres ESTABLISHED tcp 0
74 :54012 :postgres ESTABLISHED tcp 0 75 :53972 :postgres ESTABLISHED tcp 0
76 :54002 :postgres ESTABLISHED tcp 0 68 :postgres :54006 ESTABLISHED tcp 0
0 :postgres :53978 ESTABLISHED tcp 0 73 :54008 :postgres ESTABLISHED tcp 0
0 :postgres :53976 ESTABLISHED tcp 0 93 :53974 :postgres ESTABLISHED tcp 0
59 :53998 :postgres ESTABLISHED tcp 74 0 :53984 :postgres ESTABLISHED tcp 0
0 :postgres :54014 ESTABLISHED tcp 0 76 :53982 :postgres ESTABLISHED tcp 0
0 :postgres :54002 ESTABLISHED tcp 0 76 :53996 :postgres ESTABLISHED tcp 0
0 :postgres :53990 ESTABLISHED tcp 0 59 :53976 :postgres ESTABLISHED tcp 0
74 :postgres :53996 ESTABLISHED tcp 0 76 :53992 :postgres ESTABLISHED tcp 0
0 :postgres :54016 ESTABLISHED tcp 0 0 :postgres :54000 ESTABLISHED tcp 0 0
:postgres :53980 ESTABLISHED tcp 0 77 :53980 :postgres ESTABLISHED tcp 0 74
:54018 :postgres ESTABLISHED tcp 0 0 :postgres :53970 ESTABLISHED tcp 0 0
:postgres :53988 ESTABLISHED tcp 0 104 :54010 :postgres ESTABLISHED tcp 0 0
:postgres :54010 ESTABLISHED tcp 0 0 :postgres :53992 ESTABLISHED tcp6 0 0
[::]:postgres
Select pg_stat_activity
datid | datname | pid | leader_pid | usesysid | usename | application_name
| client_addr | client_hostname | client_port | backend_start | xact_start
| query_start | state_change | wait_event_type | wait_event | state |
backend_xid | backend_xmin | query | backend_type
-------+----------+---------+------------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+---------------------+--------+-------------+--------------+-------------------------------------------------------------------------------------------------+------------------------------
| | 2092230 | | 10 | postgres | | | | | 2021-10-13 02:41:12.083391-04 | | |
| Activity | LogicalLauncherMain | | | | | logical replication launcher 16385
| tpcc | 2092540 | | 16384 | tpcc | | 127.0.0.1 | | 53970 | 2021-10-13
02:41:57.336031-04 | | 2021-10-13 02:43:58.97025-04 | 2021-10-13
02:43:58.971538-04 | Client | ClientRead | idle | | | select
sum(d_next_o_id) from district | client backend 16385 | tpcc | 2092541 | |
16384 | tpcc | | 127.0.0.1 | | 53972 | 2021-10-13 02:41:57.836054-04 |
2021-10-13 02:44:04.649045-04 | 2021-10-13 02:44:04.649054-04 | 2021-10-13
02:44:04.649055-04 | | | active | 11301598 | 11301493 | prepare delivery
(INTEGER, INTEGER) AS select delivery($1,$2) | client backend 16385 | tpcc
| 2092548 | | 16384 | tpcc | | 127.0.0.1 | | 53974 | 2021-10-13
02:41:58.336566-04 | 2021-10-13 02:44:04.649153-04 | 2021-10-13
02:44:04.649163-04 | 2021-10-13 02:44:04.649163-04 | | | active | 11301611
| 11301493 | prepare neword (INTEGER, INTEGER, INTEGER, INTEGER, INTEGER)
as select neword($1,$2,$3,$4,$5,0) | client backend 16385 | tpcc | 2092549
| | 16384 | tpcc | | 127.0.0.1 | | 53976 | 2021-10-13 02:41:58.836269-04 |
2021-10-13 02:44:04.649443-04 | 2021-10-13 02:44:04.649454-04 | 2021-10-13
02:44:04.649454-04 | | | active | | 11301528 | prepare neword (INTEGER,
INTEGER, INTEGER, INTEGER, INTEGER) as select neword($1,$2,$3,$4,$5,0) |
client backend 16385 | tpcc | 2092556 | | 16384 | tpcc | | 127.0.0.1 | |
53978 | 2021-10-13 02:41:59.336172-04 | 2021-10-13 02:44:04.648817-04 |
2021-10-13 02:44:04.648827-04 | 2021-10-13 02:44:04.648828-04 | | | active
| | 11301493 | prepare slev (INTEGER, INTEGER, INTEGER) AS select
slev($1,$2,$3) | client backend 16385 | tpcc | 2092557 | | 16384 | tpcc | |
127.0.0.1 | | 53980 | 2021-10-13 02:41:59.83835-04 | 2021-10-13
02:44:04.649027-04 | 2021-10-13 02:44:04.649036-04 | 2021-10-13
02:44:04.649036-04 | | | active | | 11301493 | prepare slev (INTEGER,
INTEGER, INTEGER) AS select slev($1,$2,$3) | client backend 16385 | tpcc |
2092564 | | 16384 | tpcc | | 127.0.0.1 | | 53982 | 2021-10-13
02:42:00.336974-04 | 2021-10-13 02:44:04.649194-04 | 2021-10-13
02:44:04.649203-04 | 2021-10-13 02:44:04.649203-04 | | | active | 11301619
| 11301493 | prepare neword (INTEGER, INTEGER, INTEGER, INTEGER, INTEGER)
as select neword($1,$2,$3,$4,$5,0) | client backend 16385 | tpcc | 2092565
| | 16384 | tpcc | | 127.0.0.1 | | 53984 | 2021-10-13 02:42:00.838269-04 |
2021-10-13 02:44:04.649441-04 | 2021-10-13 02:44:04.649452-04 | 2021-10-13
02:44:04.649453-04 | | | active | | 11301528 | prepare neword (INTEGER,
INTEGER, INTEGER, INTEGER, INTEGER) as select neword($1,$2,$3,$4,$5,0) |
client backend 16385 | tpcc | 2092572 | | 16384 | tpcc | | 127.0.0.1 | |
53986 | 2021-10-13 02:42:01.337933-04 | 2021-10-13 02:44:04.648136-04 |
2021-10-13 02:44:04.648144-04 | 2021-10-13 02:44:04.648144-04 | | | active
| 11301528 | 11301396 | prepare delivery (INTEGER, INTEGER) AS select
delivery($1,$2) | client backend 16385 | tpcc | 2092573 | | 16384 | tpcc |
| 127.0.0.1 | | 53988 | 2021-10-13 02:42:01.839434-04 | 2021-10-13
02:44:04.648999-04 | 2021-10-13 02:44:04.649007-04 | 2021-10-13
02:44:04.649007-04 | LWLock | ProcArray | active | 11301596 | 11301493 |
prepare neword (INTEGER, INTEGER, INTEGER, INTEGER, INTEGER) as select
neword($1,$2,$3,$4,$5,0) | client backend 16385 | tpcc | 2092580 | | 16384
| tpcc | | 127.0.0.1 | | 53990 | 2021-10-13 02:42:02.339335-04 | 2021-10-13
02:44:04.649463-04 | 2021-10-13 02:44:04.649474-04 | 2021-10-13
02:44:04.649474-04 | | | active | | 11301528 | prepare neword (INTEGER,
INTEGER, INTEGER, INTEGER, INTEGER) as select neword($1,$2,$3,$4,$5,0) |
client backend 16385 | tpcc | 2092581 | | 16384 | tpcc | | 127.0.0.1 | |
53992 | 2021-10-13 02:42:02.838867-04 | 2021-10-13 02:44:04.649161-04 |
2021-10-13 02:44:04.64917-04 | 2021-10-13 02:44:04.64917-04 | | | active |
11301616 | 11301493 | prepare neword (INTEGER, INTEGER, INTEGER, INTEGER,
INTEGER) as select neword($1,$2,$3,$4,$5,0) | client backend 16385 | tpcc |
2092588 | | 16384 | tpcc | | 127.0.0.1 | | 53994 | 2021-10-13
02:42:03.343136-04 | 2021-10-13 02:44:04.64934-04 | 2021-10-13
02:44:04.649351-04 | 2021-10-13 02:44:04.649352-04 | | | active | |
11301528 | prepare neword (INTEGER, INTEGER, INTEGER, INTEGER, INTEGER) as
select neword($1,$2,$3,$4,$5,0) | client backend 16385 | tpcc | 2092589 | |
16384 | tpcc | | 127.0.0.1 | | 53996 | 2021-10-13 02:42:03.839278-04 |
2021-10-13 02:44:04.648822-04 | 2021-10-13 02:44:04.648834-04 | 2021-10-13
02:44:04.648834-04 | | | active | | | prepare neword (INTEGER, INTEGER,
INTEGER, INTEGER, INTEGER) as select neword($1,$2,$3,$4,$5,0) | client
backend 16385 | tpcc | 2092596 | | 16384 | tpcc | | 127.0.0.1 | | 53998 |
2021-10-13 02:42:04.34021-04 | 2021-10-13 02:44:04.649134-04 | 2021-10-13
02:44:04.649143-04 | 2021-10-13 02:44:04.649144-04 | | | active | 11301614
| 11301493 | prepare neword (INTEGER, INTEGER, INTEGER, INTEGER, INTEGER)
as select neword($1,$2,$3,$4,$5,0) | client backend 16385 | tpcc | 2092597
| | 16384 | tpcc | | 127.0.0.1 | | 54000 | 2021-10-13 02:42:04.840163-04 |
2021-10-13 02:44:04.649429-04 | 2021-10-13 02:44:04.649438-04 | 2021-10-13
02:44:04.649438-04 | | | active | | 11301528 | prepare delivery (INTEGER,
INTEGER) AS select delivery($1,$2) | client backend 16385 | tpcc | 2092604
| | 16384 | tpcc | | 127.0.0.1 | | 54002 | 2021-10-13 02:42:05.340832-04 |
2021-10-13 02:44:04.649156-04 | 2021-10-13 02:44:04.649166-04 | 2021-10-13
02:44:04.649166-04 | LWLock | WALInsert | active | 11301618 | 11301493 |
prepare neword (INTEGER, INTEGER, INTEGER, INTEGER, INTEGER) as select
neword($1,$2,$3,$4,$5,0) | client backend 16385 | tpcc | 2092605 | | 16384
| tpcc | | 127.0.0.1 | | 54004 | 2021-10-13 02:42:05.841658-04 | 2021-10-13
02:44:04.649089-04 | 2021-10-13 02:44:04.649099-04 | 2021-10-13
02:44:04.6491-04 | | | active | 11301608 | 11301493 | prepare neword
(INTEGER, INTEGER, INTEGER, INTEGER, INTEGER) as select
neword($1,$2,$3,$4,$5,0) | client backend 16385 | tpcc | 2092612 | | 16384
| tpcc | | 127.0.0.1 | | 54006 | 2021-10-13 02:42:06.342751-04 | 2021-10-13
02:44:04.649428-04 | 2021-10-13 02:44:04.649437-04 | 2021-10-13
02:44:04.649437-04 | | | active | | 11301528 | prepare delivery (INTEGER,
INTEGER) AS select delivery($1,$2) | client backend 16385 | tpcc | 2092613
| | 16384 | tpcc | | 127.0.0.1 | | 54008 | 2021-10-13 02:42:06.841509-04 |
2021-10-13 02:44:04.649237-04 | 2021-10-13 02:44:04.649249-04 | 2021-10-13
02:44:04.649249-04 | | | active | 11301622 | 11301493 | prepare neword
(INTEGER, INTEGER, INTEGER, INTEGER, INTEGER) as select
neword($1,$2,$3,$4,$5,0) | client backend 16385 | tpcc | 2092620 | | 16384
| tpcc | | 127.0.0.1 | | 54010 | 2021-10-13 02:42:07.341743-04 | 2021-10-13
02:44:04.648736-04 | 2021-10-13 02:44:04.648746-04 | 2021-10-13
02:44:04.648746-04 | | | active | 11301580 | 11301493 | prepare neword
(INTEGER, INTEGER, INTEGER, INTEGER, INTEGER) as select
neword($1,$2,$3,$4,$5,0) | client backend 16385 | tpcc | 2092621 | | 16384
| tpcc | | 127.0.0.1 | | 54012 | 2021-10-13 02:42:07.841876-04 | 2021-10-13
02:44:04.648983-04 | 2021-10-13 02:44:04.648991-04 | 2021-10-13
02:44:04.648991-04 | | | active | 11301600 | 11301493 | prepare neword
(INTEGER, INTEGER, INTEGER, INTEGER, INTEGER) as select
neword($1,$2,$3,$4,$5,0) | client backend 16385 | tpcc | 2092628 | | 16384
| tpcc | | 127.0.0.1 | | 54014 | 2021-10-13 02:42:08.342179-04 | 2021-10-13
02:44:04.649464-04 | 2021-10-13 02:44:04.649473-04 | 2021-10-13
02:44:04.649474-04 | | | active | | 11301528 | prepare neword (INTEGER,
INTEGER, INTEGER, INTEGER, INTEGER) as select neword($1,$2,$3,$4,$5,0) |
client backend 16385 | tpcc | 2092629 | | 16384 | tpcc | | 127.0.0.1 | |
54016 | 2021-10-13 02:42:08.845321-04 | 2021-10-13 02:44:04.649456-04 |
2021-10-13 02:44:04.649472-04 | 2021-10-13 02:44:04.649472-04 | | | active
| | 11301528 | prepare slev (INTEGER, INTEGER, INTEGER) AS select
slev($1,$2,$3) | client backend 16385 | tpcc | 2092636 | | 16384 | tpcc | |
127.0.0.1 | | 54018 | 2021-10-13 02:42:09.341768-04 | 2021-10-13
02:44:04.649394-04 | 2021-10-13 02:44:04.649404-04 | 2021-10-13
02:44:04.649404-04 | | | active | | 11301528 | prepare neword (INTEGER,
INTEGER, INTEGER, INTEGER, INTEGER) as select neword($1,$2,$3,$4,$5,0) |
client backend 12711 | postgres | 2093365 | | 10 | postgres | psql | | | -1
| 2021-10-13 02:44:04.64633-04 | 2021-10-13 02:44:04.648186-04 | 2021-10-13
02:44:04.648186-04 | 2021-10-13 02:44:04.648186-04 | | | active | |
11301528 | select * from pg_stat_activity; | client backend | | 2092227 | |
| | | | | | 2021-10-13 02:41:12.082448-04 | | | | Activity | BgWriterMain |
| | | | background writer | | 2092226 | | | | | | | | 2021-10-13
02:41:12.081979-04 | | | | Activity | CheckpointerMain | | | | |
checkpointer | | 2092228 | | | |
On Tuesday, October 12, 2021, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> On Tue, 2021-10-12 at 13:05 +0530, Ashkil Dighin wrote:
> > Perf data for 24vu(TPC-C)
> > --------------------------------
> >
> > 18.99% postgres postgres [.] LWLockAcquire
> > 7.09% postgres postgres [.] _bt_compare
> > 8.66% postgres postgres [.] LWLockRelease
> > 2.28% postgres postgres [.] GetSnapshotData
> > 2.25% postgres postgres [.] hash_search_with_hash_value
> > 2.11% postgres postgres [.] XLogInsertRecord
> > 1.98% postgres postgres [.] PinBuffer
> >
> > 1.Is there a way to tune the lock contention ?
>
> How many concurrent sesions are you running?
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Lock contention high
@ 2021-10-14 06:42 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Ashkil Dighin <ashkildighin76@gmail.com>
0 siblings, 0 replies; 26+ messages in thread
From: Laurenz Albe @ 2021-10-14 06:42 UTC (permalink / raw)
To: Ashkil Dighin <ashkildighin76@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On Thu, 2021-10-14 at 11:33 +0530, Ashkil Dighin wrote:
> Captured the concurrent session with Netsat and pg-stat-actvity. Is the procedure the right way to capture concurrent sesssions in postgresql?
>
> Select pg_stat_activity
[some two dozen sessions]
That doesn't look like you would get into trouble just from the
sheer number of sessions, so it must be something else.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Lock contention high
@ 2021-10-14 06:45 Ashkil Dighin <ashkildighin76@gmail.com>
parent: Mikhail Zhilin <zhilin.ms@phystech.edu>
0 siblings, 1 reply; 26+ messages in thread
From: Ashkil Dighin @ 2021-10-14 06:45 UTC (permalink / raw)
To: Mikhail Zhilin <zhilin.ms@phystech.edu>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
NUMA node0 CPU(s): 0-63,128-191NUMA node1 CPU(s): 64-127,192-255
Thread(s) per core: 2
Core(s) per socket: 64
Socket(s): 2
NUMA node(s): 2
corepinning(ta perf lock contention results for 24,32 vu
0-63
24: 18.03% postgres postgres [.] LWLockAcquire
32: 7.02% postgres postgres [.] LWLockAcquire
64-127
24: 17.96% postgres postgres [.] LWLockAcquire
32: 7.04% postgres postgres [.] LWLockAcquire
0-63,128-191(Node0)
24: 18.4% postgres postgres [.] LWLockAcquire
32: 7.07% postgres postgres [.] LWLockAcquire
64-127,192-255(Node1)
24: 18.3% postgres postgres [.] LWLockAcquire
32: 7.06% postgres postgres [.] LWLockAcquire
I do not understand on interconnect type and has restrictions on lscpu .
On Tuesday, October 12, 2021, Mikhail Zhilin <zhilin.ms@phystech.edu> wrote:
> Hi,
>
> How many sockets are on motherboard?
> What is CPU model and interconnect type (UPI?)?
> Can you share output of "lscpu"?
>
> If you have more than 1 NUMA node it may be worth to run PostgreSQL in
> single NUMA node via taskset. It will eliminate access to remote memory and
> speed up processing.
>
> Thanks,
> Michael.
>
> On 10/12/21 10:35 AM, Ashkil Dighin wrote:
>
>
> Hi,
> Lock contention observed high in PostgreSQLv13.3
> The source code compiled with GNC(GCCv11.x)
> PostgreSQL version: 13.3
> Operating system: RHEL8.3
> Kernel name:4.18.0-305.10.2.el8_4.x86_64
> RAM Size:512GB
> SSD: 1TB
> The environment used IBM metal and test benchmark environment HammerDbv4.2
> Test case :TPC-C
>
> Perf data for 24vu(TPC-C)
> --------------------------------
>
> 18.99% postgres postgres [.] LWLockAcquire
> 7.09% postgres postgres [.] _bt_compare
> 8.66% postgres postgres [.] LWLockRelease
> 2.28% postgres postgres [.] GetSnapshotData
> 2.25% postgres postgres [.] hash_search_with_hash_value
> 2.11% postgres postgres [.] XLogInsertRecord
> 1.98% postgres postgres [.] PinBuffer
>
> 1.Is there a way to tune the lock contention ?
> 2.Is any recommendations to tune/reduce the lock contention via
> postgres.conf
>
> Postgres.conf used in Baremetal
> ========================
> shared_buffers = 128GB(1/4 th RAM size)
> effective_cachesize=392 GB(1/3 or 75% of RAM size)
> huge_pages = on
> temp_buffers = 4000MB
> work_mem = 4000MB
> maintenance_work_mem = 512MB
> autovacuum_work_mem = -1
> max_stack_depth = 7MB
> dynamic_shared_memory_type = posix
> max_files_per_process = 4000
> effective_io_concurrency = 32
> wal_level = minimal
> synchronous_commit = off
> wal_buffers = 512MB
> checkpoint_timeout = 1h
> checkpoint_completion_target = 1
> checkpoint_warning = 0
> log_min_messages = error
> log_min_error_statement = error
> log_timezone = 'GB'
> autovacuum = off
> datestyle = 'iso, dmy'
> timezone = 'GB'
> lc_messages = 'en_GB.UTF-8'
> lc_monetary = 'en_GB.UTF-8'
> lc_numeric = 'en_GB.UTF-8'
> lc_time = 'en_GB.UTF-8'
> default_text_search_config = 'pg_catalog.english'
> max_locks_per_transaction = 64
> max_pred_locks_per_transaction = 64
>
> Best Regards
> Anil
>
>
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Lock contention high
@ 2021-10-14 06:49 Ashkil Dighin <ashkildighin76@gmail.com>
parent: Paul Friedman <paul.friedman@streetlightdata.com>
0 siblings, 0 replies; 26+ messages in thread
From: Ashkil Dighin @ 2021-10-14 06:49 UTC (permalink / raw)
To: Paul Friedman <paul.friedman@streetlightdata.com>; +Cc: MichaelDBA <MichaelDBA@sqlexec.com>; Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Not using PostGIS
On Thursday, October 14, 2021, Paul Friedman <
paul.friedman@streetlightdata.com> wrote:
> Are you using PostGIS?
>
> If so, there is an issue with TOAST table locking having these symptoms.
>
>
> ---Paul
>
>
> On Wed, Oct 13, 2021 at 11:15 AM MichaelDBA <MichaelDBA@sqlexec.com>
> wrote:
>
>> 1.Is there a way to tune the lock contention ?
>> 2.Is any recommendations to tune/reduce the lock contention via postgres.conf
>>
>> I think you'd want to find *which* LW locks are being waited on, to see if it's
>> something that can be easily tuned.
>>
>> You can check pg_stat_activity, or maybe create a cronjob to record its content
>> for later analysis.
>>
>>
>> Hello,
>>
>> Also turn on log_lock_waits so you can evaluate the actual SQL causing
>> the problems in the PG log files. Thinking ahead, you may want to consider
>> if using advisory locks from the application side of things might be
>> helpful to manage locks in a more pessimistic way. Also, join with
>> pg_locks table to find out the specific resources that are in contention.
>>
>> Regards,
>> Michael Vitale
>>
>>
>>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Lock contention high
@ 2021-10-14 07:47 Mikhail Zhilin <zhilin.ms@phystech.edu>
parent: Ashkil Dighin <ashkildighin76@gmail.com>
0 siblings, 0 replies; 26+ messages in thread
From: Mikhail Zhilin @ 2021-10-14 07:47 UTC (permalink / raw)
To: Ashkil Dighin <ashkildighin76@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Ashkil,
Can you bind postgres in single NUMA node, for instance:
$ taskset -pc 0-63 <POSTMASTER_PID>
Then run your benchmark, compare results in terms of benchmark metrics &
presence on LWLock(Acquire|Release) in perf top.
BR,
Michael.
On 10/14/21 9:45 AM, Ashkil Dighin wrote:
>
> NUMA node0 CPU(s): 0-63,128-191NUMA node1 CPU(s): 64-127,192-255
> Thread(s) per core: 2
> Core(s) per socket: 64
> Socket(s): 2
> NUMA node(s): 2
> corepinning(ta perf lock contention results for 24,32 vu
> 0-63
> 24: 18.03% postgres postgres [.] LWLockAcquire
> 32: 7.02% postgres postgres [.] LWLockAcquire
> 64-127
> 24: 17.96% postgres postgres [.] LWLockAcquire
> 32: 7.04% postgres postgres [.] LWLockAcquire
> 0-63,128-191(Node0)
> 24: 18.4% postgres postgres [.] LWLockAcquire
> 32: 7.07% postgres postgres [.] LWLockAcquire
> 64-127,192-255(Node1)
> 24: 18.3% postgres postgres [.] LWLockAcquire
> 32: 7.06% postgres postgres [.] LWLockAcquire
>
> I do not understand on interconnect type and has restrictions on lscpu .
>
> On Tuesday, October 12, 2021, Mikhail Zhilin <zhilin.ms@phystech.edu
> <mailto:zhilin.ms@phystech.edu>> wrote:
>
> Hi,
>
> How many sockets are on motherboard?
> What is CPU model and interconnect type (UPI?)?
> Can you share output of "lscpu"?
>
> If you have more than 1 NUMA node it may be worth to run
> PostgreSQL in single NUMA node via taskset. It will eliminate
> access to remote memory and speed up processing.
>
> Thanks,
> Michael.
>
> On 10/12/21 10:35 AM, Ashkil Dighin wrote:
>>
>> Hi,
>> Lock contention observed high in PostgreSQLv13.3
>> The source code compiled with GNC(GCCv11.x)
>> PostgreSQL version: 13.3
>> Operating system: RHEL8.3
>> Kernel name:4.18.0-305.10.2.el8_4.x86_64
>> RAM Size:512GB
>> SSD: 1TB
>> The environment used IBM metal and test benchmark
>> environment HammerDbv4.2
>> Test case :TPC-C
>>
>> Perf data for 24vu(TPC-C)
>> --------------------------------
>>
>> 18.99% postgres postgres [.] LWLockAcquire
>> 7.09% postgres postgres [.] _bt_compare
>> 8.66% postgres postgres [.] LWLockRelease
>> 2.28% postgres postgres [.] GetSnapshotData
>> 2.25% postgres postgres [.]
>> hash_search_with_hash_value
>> 2.11% postgres postgres [.] XLogInsertRecord
>> 1.98% postgres postgres [.] PinBuffer
>>
>> 1.Is there a way to tune the lock contention ?
>> 2.Is any recommendations to tune/reduce the lock contention via
>> postgres.conf
>>
>> Postgres.conf used in Baremetal
>> ========================
>> shared_buffers = 128GB(1/4 th RAM size)
>> effective_cachesize=392 GB(1/3 or 75% of RAM size)
>> huge_pages = on
>> temp_buffers = 4000MB
>> work_mem = 4000MB
>> maintenance_work_mem = 512MB
>> autovacuum_work_mem = -1
>> max_stack_depth = 7MB
>> dynamic_shared_memory_type = posix
>> max_files_per_process = 4000
>> effective_io_concurrency = 32
>> wal_level = minimal
>> synchronous_commit = off
>> wal_buffers = 512MB
>> checkpoint_timeout = 1h
>> checkpoint_completion_target = 1
>> checkpoint_warning = 0
>> log_min_messages = error
>> log_min_error_statement = error
>> log_timezone = 'GB'
>> autovacuum = off
>> datestyle = 'iso, dmy'
>> timezone = 'GB'
>> lc_messages = 'en_GB.UTF-8'
>> lc_monetary = 'en_GB.UTF-8'
>> lc_numeric = 'en_GB.UTF-8'
>> lc_time = 'en_GB.UTF-8'
>> default_text_search_config = 'pg_catalog.english'
>> max_locks_per_transaction = 64
>> max_pred_locks_per_transaction = 64
>>
>> Best Regards
>> Anil
>>
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Lock contention high
@ 2021-10-20 10:51 Ashkil Dighin <ashkildighin76@gmail.com>
parent: Peter Geoghegan <pg@bowt.ie>
0 siblings, 0 replies; 26+ messages in thread
From: Ashkil Dighin @ 2021-10-20 10:51 UTC (permalink / raw)
To: Peter Geoghegan <pg@bowt.ie>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hi
B-tree index used in the postgres environment
Checked on warehouse different values like 100,800,1600,2400 and 3200 with
virtual user 64
On different values(warehouse) the lock contention same i.e. approx 17% and
iostat usage is 30-40%
pg_Count_ware=100
-----------------
17.76% postgres postgres [.] LWLockAcquire
4.88% postgres postgres [.] _bt_compare
3.10% postgres postgres [.] LWLockRelease
pg_Count_ware=800(previously I used Warehouse 800)
--------------------------------------------
17.91% postgres postgres [.] LWLockAcquire
5.76% postgres postgres [.] _bt_compare
3.06% postgres postgres [.] LWLockRelease
pg_Count_ware_1600
-----------------
17.80% postgres postgres [.] LWLockAcquire
5.88% postgres postgres [.] _bt_compare
2.70% postgres postgres [.] LWLockRelease
pg_Count_ware_2400
------------------
17.77% postgres postgres [.] LWLockAcquire
6.01% postgres postgres [.] _bt_compare
2.71% postgres postgres [.] LWLockRelease
pg_Count_ware_3200
------------------
17.46% postgres postgres [.] LWLockAcquire
6.32% postgres postgres [.] _bt_compare
2.86% postgres postgres [.] hash_search_with_hash_value
1.Tired different values of lock management values in postgres.conf but it
not helped to reduce lock contention.
deadlock_timeout = 5s
max_locks_per_transaction = 64
max_pred_locks_per_transaction = 64
max_pred_locks_per_relation = -2
max_pred_locks_per_page = 2
2.Intention to check the postgreSQL scalability and performance or
throughput(TPC-C/TPC-H)
with HammerDB and pgbench with server configuration on tune
settings(postgresql.conf)-reduce lock contention
CPU's :256
Threadper core: 2
Core per socket: 64
Sockets: 2
NUMA node0 : 0-63,128-191
NUMA node1 : 64-127,192-255
RAM size :512GB
SSD :1TB
Ref link:
https://www.hammerdb.com/blog/uncategorized/hammerdb-best-practice-for-postgresql-performance-and-sc...
On Thursday, October 14, 2021, Peter Geoghegan <pg@bowt.ie> wrote:
> On Tue, Oct 12, 2021 at 12:45 AM Ashkil Dighin <ashkildighin76@gmail.com>
> wrote:
> > Lock contention observed high in PostgreSQLv13.3
> > The source code compiled with GNC(GCCv11.x)
> > PostgreSQL version: 13.3
> > Operating system: RHEL8.3
> > Kernel name:4.18.0-305.10.2.el8_4.x86_64
> > RAM Size:512GB
> > SSD: 1TB
> > The environment used IBM metal and test benchmark environment
> HammerDbv4.2
> > Test case :TPC-C
>
> You didn't say how many TPC-C warehouses you used. In my experience,
> people sometimes run TPC-C with relatively few, which will tend to
> result in extreme contention on certain B-Tree leaf pages. (My
> experiences are with BenchmarkSQL, but I can't imagine HammerDB is too
> much different.)
>
> Assuming that's the case here, for you, then it's not clear that you
> have a real problem. You're really not supposed to run the benchmark
> in that way, per the TPC-C spec, which strictly limits the number of
> transactions per minute per warehouse -- for better or worse, valid
> results generally require that you use lots of warehouses to get a
> very large database (think terabytes). If you run the benchmark with
> 100 warehouses or less, on a big server, then the contention you'll
> see will be out of all proportion to what you're ever likely to see in
> the real world.
>
> --
> Peter Geoghegan
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Lock contention high
@ 2021-10-25 23:35 Andres Freund <andres@anarazel.de>
parent: Ashkil Dighin <ashkildighin76@gmail.com>
6 siblings, 2 replies; 26+ messages in thread
From: Andres Freund @ 2021-10-25 23:35 UTC (permalink / raw)
To: Ashkil Dighin <ashkildighin76@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
Hi,
On 2021-10-12 13:05:12 +0530, Ashkil Dighin wrote:
> PostgreSQL version: 13.3
You could try postgres 14 - that did improve scalability in some areas.
> Perf data for 24vu(TPC-C)
> --------------------------------
>
> 18.99% postgres postgres [.] LWLockAcquire
> 7.09% postgres postgres [.] _bt_compare
> 8.66% postgres postgres [.] LWLockRelease
> 2.28% postgres postgres [.] GetSnapshotData
> 2.25% postgres postgres [.] hash_search_with_hash_value
> 2.11% postgres postgres [.] XLogInsertRecord
> 1.98% postgres postgres [.] PinBuffer
To be more useful you'd need to create a profile with 'caller' information
using 'perf record --call-graph dwarf', and then check what the important
callers are.
> 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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Lock contention high
@ 2021-10-26 00:38 Michael Lewis <mlewis@entrata.com>
parent: Andres Freund <andres@anarazel.de>
1 sibling, 1 reply; 26+ messages in thread
From: Michael Lewis @ 2021-10-26 00:38 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; +Cc: Ashkil Dighin <ashkildighin76@gmail.com>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
On Mon, Oct 25, 2021, 5:36 PM Andres Freund <andres@anarazel.de> 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 larger might you go? Any write ups on lock contention as it
relates to shared buffers? How impactful might huge pages (off, transparent
or on) be to the use of shared buffers and the related locking mechanism?
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Lock contention high
@ 2021-10-26 00:43 Andres Freund <andres@anarazel.de>
parent: Michael Lewis <mlewis@entrata.com>
0 siblings, 0 replies; 26+ messages in thread
From: Andres Freund @ 2021-10-26 00:43 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; +Cc: Ashkil Dighin <ashkildighin76@gmail.com>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
Hi,
On 2021-10-25 18:38:40 -0600, Michael Lewis wrote:
> On Mon, Oct 25, 2021, 5:36 PM Andres Freund <andres@anarazel.de> 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 larger might you go?
I've seen s_b in the ~700GB range being a considerable speedup over lower
values quite a few years ago. I don't see a clear cut upper boundary. The one
thing this can regress measurably is the speed of dropping / truncating
tables.
> Any write ups on lock contention as it relates to shared buffers?
I don't have a concrete thing to point you to, but searching for
NUM_BUFFER_PARTITIONS might point you to some discussions.
> How impactful might huge pages (off, transparent or on) be to the use of
> shared buffers and the related locking mechanism?
Using huge pages can *hugely* help performance-wise. Not directly by relieving
postgres-side contention however (it does reduce cache usage somewhat, but
it's mainly really just the frequency of TLB misses that makes the
difference).
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Lock contention high
@ 2021-10-27 21:44 Ashkil Dighin <ashkildighin76@gmail.com>
parent: Andres Freund <andres@anarazel.de>
1 sibling, 2 replies; 26+ messages in thread
From: Ashkil Dighin @ 2021-10-27 21:44 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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%?
On pg-stat-activity shown LwLock as “BufferCounter” and “WalInsert”
On Tuesday, October 26, 2021, Andres Freund <andres@anarazel.de> wrote:
> Hi,
>
> On 2021-10-12 13:05:12 +0530, Ashkil Dighin wrote:
> > PostgreSQL version: 13.3
>
> You could try postgres 14 - that did improve scalability in some areas.
>
>
>
> > Perf data for 24vu(TPC-C)
> > --------------------------------
> >
> > 18.99% postgres postgres [.] LWLockAcquire
> > 7.09% postgres postgres [.] _bt_compare
> > 8.66% postgres postgres [.] LWLockRelease
> > 2.28% postgres postgres [.] GetSnapshotData
> > 2.25% postgres postgres [.] hash_search_with_hash_value
> > 2.11% postgres postgres [.] XLogInsertRecord
> > 1.98% postgres postgres [.] PinBuffer
>
> To be more useful you'd need to create a profile with 'caller' information
> using 'perf record --call-graph dwarf', and then check what the important
> callers are.
>
>
> > 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
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Lock contention high
@ 2021-10-27 22:22 Andres Freund <andres@anarazel.de>
parent: Ashkil Dighin <ashkildighin76@gmail.com>
1 sibling, 1 reply; 26+ messages in thread
From: Andres Freund @ 2021-10-27 22:22 UTC (permalink / raw)
To: Ashkil Dighin <ashkildighin76@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hi,
On October 27, 2021 2:44:56 PM PDT, Ashkil Dighin <ashkildighin76@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.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Lock contention high
@ 2021-11-12 14:12 Ashkil Dighin <ashkildighin76@gmail.com>
parent: Andres Freund <andres@anarazel.de>
0 siblings, 2 replies; 26+ messages in thread
From: Ashkil Dighin @ 2021-11-12 14:12 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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 <andres@anarazel.de> wrote:
> Hi,
>
> On October 27, 2021 2:44:56 PM PDT, Ashkil Dighin <
> ashkildighin76@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.
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Lock contention high
@ 2021-11-15 15:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ashkil Dighin <ashkildighin76@gmail.com>
1 sibling, 0 replies; 26+ messages in thread
From: Tom Lane @ 2021-11-15 15:25 UTC (permalink / raw)
To: Ashkil Dighin <ashkildighin76@gmail.com>; +Cc: Andres Freund <andres@anarazel.de>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Ashkil Dighin <ashkildighin76@gmail.com> 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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Lock contention high
@ 2021-11-17 06:43 arjun shetty <arjunshetty955@gmail.com>
parent: Ashkil Dighin <ashkildighin76@gmail.com>
1 sibling, 1 reply; 26+ messages in thread
From: arjun shetty @ 2021-11-17 06:43 UTC (permalink / raw)
To: Ashkil Dighin <ashkildighin76@gmail.com>; +Cc: Andres Freund <andres@anarazel.de>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hi Askhil
PostgreSQL utilizes lightweight locks(LWLocks) to synchronize and control
access to the buffer content. A process acquires an LWLock in a shared
mode to read from the buffer and an exclusive mode to write to the buffer.
Therefore, while holding an exclusive lock, a process prevents other
processes from acquiring a shared or exclusive lock. Also, a shared lock
can be acquired concurrently by other processes. The issue starts when many
processes acquire an exclusive lock on buffer content. As a result,
LwlockAcquire seen as top hot function in profilng.
Here need to understand LwlockAcquire is lock contention or cpu time spent
inside the method/ function(top function in profiling)
It can analysed log “LwStatus” with parameters like
ex-acquire-count(exclusive mode) , sh-acquire-count , block-count and
spin-delay-count
Total lock acquisition request = ex-acquire-count+sh-acquire-count)
Time lock contention %= block count)/ Total lock acquisition request.
Time lock contention may provide as most of cpu time inside the function
rather than spinning/ waiting for lock.
On Friday, November 12, 2021, Ashkil Dighin <ashkildighin76@gmail.com>
wrote:
> 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 <andres@anarazel.de> wrote:
>
>> Hi,
>>
>> On October 27, 2021 2:44:56 PM PDT, Ashkil Dighin <
>> ashkildighin76@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.
>>
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Lock contention high
@ 2021-11-29 12:39 arjun shetty <arjunshetty955@gmail.com>
parent: arjun shetty <arjunshetty955@gmail.com>
0 siblings, 0 replies; 26+ messages in thread
From: arjun shetty @ 2021-11-29 12:39 UTC (permalink / raw)
To: Ashkil Dighin <ashkildighin76@gmail.com>; +Cc: Andres Freund <andres@anarazel.de>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
1. How to check which NUMA node in PostgreSQL process fetching from the
memory?
2. Is NUMA configuration is better for PostgreSQL?
vm.zone_reclaim_mode= 0
numactl --interleave = all /init.d/ PostgreSQL start
kernel.numa_balancing= 0
On Wednesday, November 17, 2021, arjun shetty <arjunshetty955@gmail.com>
wrote:
> Hi Askhil
>
> PostgreSQL utilizes lightweight locks(LWLocks) to synchronize and
> control access to the buffer content. A process acquires an LWLock in a
> shared mode to read from the buffer and an exclusive mode to write to
> the buffer. Therefore, while holding an exclusive lock, a process prevents
> other processes from acquiring a shared or exclusive lock. Also, a shared
> lock can be acquired concurrently by other processes. The issue starts when
> many processes acquire an exclusive lock on buffer content. As a result,
> LwlockAcquire seen as top hot function in profilng.
> Here need to understand LwlockAcquire is lock contention or cpu time
> spent inside the method/ function(top function in profiling)
>
> It can analysed log “LwStatus” with parameters like
> ex-acquire-count(exclusive mode) , sh-acquire-count , block-count and
> spin-delay-count
>
> Total lock acquisition request = ex-acquire-count+sh-acquire-count)
> Time lock contention %= block count)/ Total lock acquisition request.
>
> Time lock contention may provide as most of cpu time inside the function
> rather than spinning/ waiting for lock.
>
> On Friday, November 12, 2021, Ashkil Dighin <ashkildighin76@gmail.com>
> wrote:
>
>> 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 <andres@anarazel.de> wrote:
>>
>>> Hi,
>>>
>>> On October 27, 2021 2:44:56 PM PDT, Ashkil Dighin <
>>> ashkildighin76@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.
>>>
>>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Lock contention high
@ 2021-12-21 05:45 Yura Sokolov <y.sokolov@postgrespro.ru>
parent: Ashkil Dighin <ashkildighin76@gmail.com>
1 sibling, 0 replies; 26+ messages in thread
From: Yura Sokolov @ 2021-12-21 05:45 UTC (permalink / raw)
To: Ashkil Dighin <ashkildighin76@gmail.com>; Andres Freund <andres@anarazel.de>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
В Чт, 28/10/2021 в 03:14 +0530, Ashkil Dighin пишет:
> 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%?
> On pg-stat-activity shown LwLock as “BufferCounter” and “WalInsert”
>
>
> On Tuesday, October 26, 2021, Andres Freund <andres@anarazel.de> wrote:
> > Hi,
> >
> > On 2021-10-12 13:05:12 +0530, Ashkil Dighin wrote:
> > > PostgreSQL version: 13.3
> >
> > You could try postgres 14 - that did improve scalability in some areas.
> >
> >
> >
> > > Perf data for 24vu(TPC-C)
> > > --------------------------------
> > >
> > > 18.99% postgres postgres [.] LWLockAcquire
> > > 7.09% postgres postgres [.] _bt_compare
> > > 8.66% postgres postgres [.] LWLockRelease
> > > 2.28% postgres postgres [.] GetSnapshotData
> > > 2.25% postgres postgres [.] hash_search_with_hash_value
> > > 2.11% postgres postgres [.] XLogInsertRecord
> > > 1.98% postgres postgres [.] PinBuffer
> >
> > To be more useful you'd need to create a profile with 'caller' information
> > using 'perf record --call-graph dwarf', and then check what the important
> > callers are.
> >
> >
> > > 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.
> >
Could you try attached patch?
It reduces lock contention in buffer manager by not acquiring
two locks simultaneously on buffer eviction.
v1-0001-* - it is file for postgresql 14 and master branch
vpg13v1-0001-* - this file for postgresql 13
Corresponding (not so loud) discussion:
https://postgr.es/m/flat/1edbb61981fe1d99c3f20e3d56d6c88999f4227c.camel%40postgrespro.ru
--------
regards,
Yura Sokolov
y.sokolov@postgrespro.ru
funny.falcon@gmail.com
Attachments:
[text/x-patch] vpg13v1-0001-bufmgr-do-not-acquire-two-partition-locks.patch (25.1K, ../../bca5495fa6ef812028ae8eb9424b66a9da1ce47d.camel@postgrespro.ru/2-vpg13v1-0001-bufmgr-do-not-acquire-two-partition-locks.patch)
download | inline diff:
From efa1d36f0e6a83f4329d259f310b8da25b04bc24 Mon Sep 17 00:00:00 2001
From: Yura Sokolov <y.sokolov@postgrespro.ru>
Date: Wed, 22 Sep 2021 13:10:37 +0300
Subject: [PATCH vpg13v1] bufmgr: do not acquire two partition locks.
Acquiring two partition locks leads to complex dependency chain that hurts
at high concurrency level.
There is no need to hold both lock simultaneously. Buffer is pinned so
other processes could not select it for eviction. If tag is cleared and
buffer removed from old partition other processes will not find it.
Therefore it is safe to release old partition lock before acquiring
new partition lock.
This change requires to manually return BufferDesc to free list.
Also insertion and deletion to dynahash is optimized by avoiding
unnecessary free list manipulations in common case (when buffer is
reused)
Also small and never triggered bug in hash_update_hash_key is fixed.
---
src/backend/storage/buffer/buf_table.c | 54 +++--
src/backend/storage/buffer/bufmgr.c | 190 ++++++++--------
src/backend/utils/hash/dynahash.c | 289 +++++++++++++++++++++++--
src/include/storage/buf_internals.h | 6 +-
src/include/utils/hsearch.h | 17 ++
5 files changed, 410 insertions(+), 146 deletions(-)
diff --git a/src/backend/storage/buffer/buf_table.c b/src/backend/storage/buffer/buf_table.c
index 4953ae9f824..feab7f62f5b 100644
--- a/src/backend/storage/buffer/buf_table.c
+++ b/src/backend/storage/buffer/buf_table.c
@@ -107,36 +107,29 @@ BufTableLookup(BufferTag *tagPtr, uint32 hashcode)
/*
* BufTableInsert
- * Insert a hashtable entry for given tag and buffer ID,
- * unless an entry already exists for that tag
- *
- * Returns -1 on successful insertion. If a conflicting entry exists
- * already, returns the buffer ID in that entry.
+ * Insert a hashtable entry for given tag and buffer ID.
+ * Caller should be sure there is no conflicting entry.
*
* Caller must hold exclusive lock on BufMappingLock for tag's partition
+ * and call BufTableLookup to check for conflicting entry.
+ *
+ * If oldelem is passed it is reused.
*/
-int
-BufTableInsert(BufferTag *tagPtr, uint32 hashcode, int buf_id)
+void
+BufTableInsert(BufferTag *tagPtr, uint32 hashcode, int buf_id, void *oldelem)
{
BufferLookupEnt *result;
- bool found;
Assert(buf_id >= 0); /* -1 is reserved for not-in-table */
Assert(tagPtr->blockNum != P_NEW); /* invalid tag */
result = (BufferLookupEnt *)
- hash_search_with_hash_value(SharedBufHash,
- (void *) tagPtr,
- hashcode,
- HASH_ENTER,
- &found);
-
- if (found) /* found something already in the table */
- return result->id;
+ hash_insert_with_hash_nocheck(SharedBufHash,
+ (void *) tagPtr,
+ hashcode,
+ oldelem);
result->id = buf_id;
-
- return -1;
}
/*
@@ -144,19 +137,32 @@ BufTableInsert(BufferTag *tagPtr, uint32 hashcode, int buf_id)
* Delete the hashtable entry for given tag (which must exist)
*
* Caller must hold exclusive lock on BufMappingLock for tag's partition
+ *
+ * Returns pointer to internal hashtable entry that should be passed either
+ * to BufTableInsert or BufTableFreeDeleted.
*/
-void
+void *
BufTableDelete(BufferTag *tagPtr, uint32 hashcode)
{
BufferLookupEnt *result;
result = (BufferLookupEnt *)
- hash_search_with_hash_value(SharedBufHash,
- (void *) tagPtr,
- hashcode,
- HASH_REMOVE,
- NULL);
+ hash_delete_skip_freelist(SharedBufHash,
+ (void *) tagPtr,
+ hashcode);
if (!result) /* shouldn't happen */
elog(ERROR, "shared buffer hash table corrupted");
+
+ return result;
+}
+
+/*
+ * BufTableFreeDeleted
+ * Returns deleted hashtable entry to freelist.
+ */
+void
+BufTableFreeDeleted(void *oldelem, uint32 hashcode)
+{
+ hash_return_to_freelist(SharedBufHash, oldelem, hashcode);
}
diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c
index 9381f9981d3..705919226e5 100644
--- a/src/backend/storage/buffer/bufmgr.c
+++ b/src/backend/storage/buffer/bufmgr.c
@@ -1020,6 +1020,7 @@ BufferAlloc(SMgrRelation smgr, char relpersistence, ForkNumber forkNum,
BufferDesc *buf;
bool valid;
uint32 buf_state;
+ void *oldElem = NULL;
/* create a tag so we can lookup the buffer */
INIT_BUFFERTAG(newTag, smgr->smgr_rnode.node, forkNum, blockNum);
@@ -1194,93 +1195,16 @@ BufferAlloc(SMgrRelation smgr, char relpersistence, ForkNumber forkNum,
oldHash = BufTableHashCode(&oldTag);
oldPartitionLock = BufMappingPartitionLock(oldHash);
- /*
- * Must lock the lower-numbered partition first to avoid
- * deadlocks.
- */
- if (oldPartitionLock < newPartitionLock)
- {
- LWLockAcquire(oldPartitionLock, LW_EXCLUSIVE);
- LWLockAcquire(newPartitionLock, LW_EXCLUSIVE);
- }
- else if (oldPartitionLock > newPartitionLock)
- {
- LWLockAcquire(newPartitionLock, LW_EXCLUSIVE);
- LWLockAcquire(oldPartitionLock, LW_EXCLUSIVE);
- }
- else
- {
- /* only one partition, only one lock */
- LWLockAcquire(newPartitionLock, LW_EXCLUSIVE);
- }
+ LWLockAcquire(oldPartitionLock, LW_EXCLUSIVE);
}
else
{
- /* if it wasn't valid, we need only the new partition */
- LWLockAcquire(newPartitionLock, LW_EXCLUSIVE);
/* remember we have no old-partition lock or tag */
oldPartitionLock = NULL;
/* keep the compiler quiet about uninitialized variables */
oldHash = 0;
}
- /*
- * Try to make a hashtable entry for the buffer under its new tag.
- * This could fail because while we were writing someone else
- * allocated another buffer for the same block we want to read in.
- * Note that we have not yet removed the hashtable entry for the old
- * tag.
- */
- buf_id = BufTableInsert(&newTag, newHash, buf->buf_id);
-
- if (buf_id >= 0)
- {
- /*
- * Got a collision. Someone has already done what we were about to
- * do. We'll just handle this as if it were found in the buffer
- * pool in the first place. First, give up the buffer we were
- * planning to use.
- */
- UnpinBuffer(buf, true);
-
- /* Can give up that buffer's mapping partition lock now */
- if (oldPartitionLock != NULL &&
- oldPartitionLock != newPartitionLock)
- LWLockRelease(oldPartitionLock);
-
- /* remaining code should match code at top of routine */
-
- buf = GetBufferDescriptor(buf_id);
-
- valid = PinBuffer(buf, strategy);
-
- /* Can release the mapping lock as soon as we've pinned it */
- LWLockRelease(newPartitionLock);
-
- *foundPtr = true;
-
- if (!valid)
- {
- /*
- * We can only get here if (a) someone else is still reading
- * in the page, or (b) a previous read attempt failed. We
- * have to wait for any active read attempt to finish, and
- * then set up our own read attempt if the page is still not
- * BM_VALID. StartBufferIO does it all.
- */
- if (StartBufferIO(buf, true))
- {
- /*
- * If we get here, previous attempts to read the buffer
- * must have failed ... but we shall bravely try again.
- */
- *foundPtr = false;
- }
- }
-
- return buf;
- }
-
/*
* Need to lock the buffer header too in order to change its tag.
*/
@@ -1297,31 +1221,102 @@ BufferAlloc(SMgrRelation smgr, char relpersistence, ForkNumber forkNum,
break;
UnlockBufHdr(buf, buf_state);
- BufTableDelete(&newTag, newHash);
- if (oldPartitionLock != NULL &&
- oldPartitionLock != newPartitionLock)
+ if (oldPartitionLock != NULL)
LWLockRelease(oldPartitionLock);
- LWLockRelease(newPartitionLock);
UnpinBuffer(buf, true);
}
/*
- * Okay, it's finally safe to rename the buffer.
+ * Clear out the buffer's tag and flags. We must do this to ensure that
+ * linear scans of the buffer array don't think the buffer is valid. We
+ * also reset the usage_count since any recency of use of the old content
+ * is no longer relevant.
*
- * Clearing BM_VALID here is necessary, clearing the dirtybits is just
- * paranoia. We also reset the usage_count since any recency of use of
- * the old content is no longer relevant. (The usage_count starts out at
- * 1 so that the buffer can survive one clock-sweep pass.)
+ * Since we are single pinner, there should no be PIN_COUNT_WAITER or
+ * IO_IN_PROGRESS (flags that were not cleared in previous code).
+ */
+ Assert((oldFlags & (BM_PIN_COUNT_WAITER | BM_IO_IN_PROGRESS)) == 0);
+ CLEAR_BUFFERTAG(buf->tag);
+ buf_state &= ~(BUF_FLAG_MASK | BUF_USAGECOUNT_MASK);
+ UnlockBufHdr(buf, buf_state);
+
+ if (oldFlags & BM_TAG_VALID)
+ oldElem = BufTableDelete(&oldTag, oldHash);
+
+ if (oldPartitionLock != newPartitionLock)
+ {
+ if (oldPartitionLock != NULL)
+ LWLockRelease(oldPartitionLock);
+ LWLockAcquire(newPartitionLock, LW_EXCLUSIVE);
+ }
+
+ /*
+ * Try to make a hashtable entry for the buffer under its new tag. This
+ * could fail because while we were writing someone else allocated another
+ * buffer for the same block we want to read in. Note that we have not yet
+ * removed the hashtable entry for the old tag.
+ */
+ buf_id = BufTableLookup(&newTag, newHash);
+
+ if (buf_id >= 0)
+ {
+ /*
+ * Got a collision. Someone has already done what we were about to do.
+ * We'll just handle this as if it were found in the buffer pool in
+ * the first place. First, give up the buffer we were planning to use
+ * and put it to free lists.
+ */
+ UnpinBuffer(buf, true);
+ StrategyFreeBuffer(buf);
+ if (oldElem != NULL)
+ BufTableFreeDeleted(oldElem, oldHash);
+
+ /* remaining code should match code at top of routine */
+
+ buf = GetBufferDescriptor(buf_id);
+
+ valid = PinBuffer(buf, strategy);
+
+ /* Can release the mapping lock as soon as we've pinned it */
+ LWLockRelease(newPartitionLock);
+
+ *foundPtr = true;
+
+ if (!valid)
+ {
+ /*
+ * We can only get here if (a) someone else is still reading in
+ * the page, or (b) a previous read attempt failed. We have to
+ * wait for any active read attempt to finish, and then set up our
+ * own read attempt if the page is still not BM_VALID.
+ * StartBufferIO does it all.
+ */
+ if (StartBufferIO(buf, true))
+ {
+ /*
+ * If we get here, previous attempts to read the buffer must
+ * have failed ... but we shall bravely try again.
+ */
+ *foundPtr = false;
+ }
+ }
+
+ return buf;
+ }
+
+ /*
+ * Okay, it's finally safe to rename the buffer.
*
* Make sure BM_PERMANENT is set for buffers that must be written at every
* checkpoint. Unlogged buffers only need to be written at shutdown
* checkpoints, except for their "init" forks, which need to be treated
* just like permanent relations.
+ *
+ * The usage_count starts out at 1 so that the buffer can survive one
+ * clock-sweep pass.
*/
+ buf_state = LockBufHdr(buf);
buf->tag = newTag;
- buf_state &= ~(BM_VALID | BM_DIRTY | BM_JUST_DIRTIED |
- BM_CHECKPOINT_NEEDED | BM_IO_ERROR | BM_PERMANENT |
- BUF_USAGECOUNT_MASK);
if (relpersistence == RELPERSISTENCE_PERMANENT || forkNum == INIT_FORKNUM)
buf_state |= BM_TAG_VALID | BM_PERMANENT | BUF_USAGECOUNT_ONE;
else
@@ -1329,13 +1324,7 @@ BufferAlloc(SMgrRelation smgr, char relpersistence, ForkNumber forkNum,
UnlockBufHdr(buf, buf_state);
- if (oldPartitionLock != NULL)
- {
- BufTableDelete(&oldTag, oldHash);
- if (oldPartitionLock != newPartitionLock)
- LWLockRelease(oldPartitionLock);
- }
-
+ BufTableInsert(&newTag, newHash, buf->buf_id, oldElem);
LWLockRelease(newPartitionLock);
/*
@@ -1444,7 +1433,12 @@ retry:
* Remove the buffer from the lookup hashtable, if it was in there.
*/
if (oldFlags & BM_TAG_VALID)
- BufTableDelete(&oldTag, oldHash);
+ {
+ void *oldElem;
+
+ oldElem = BufTableDelete(&oldTag, oldHash);
+ BufTableFreeDeleted(oldElem, oldHash);
+ }
/*
* Done with mapping lock.
diff --git a/src/backend/utils/hash/dynahash.c b/src/backend/utils/hash/dynahash.c
index 2688e277267..3666c550715 100644
--- a/src/backend/utils/hash/dynahash.c
+++ b/src/backend/utils/hash/dynahash.c
@@ -88,6 +88,7 @@
#include "access/xact.h"
#include "common/hashfn.h"
#include "port/pg_bitutils.h"
+#include "port/atomics.h"
#include "storage/shmem.h"
#include "storage/spin.h"
#include "utils/dynahash.h"
@@ -123,6 +124,18 @@ typedef HASHELEMENT *HASHBUCKET;
/* A hash segment is an array of bucket headers */
typedef HASHBUCKET *HASHSEGMENT;
+#if SIZEOF_LONG == 8
+typedef pg_atomic_uint64 Count;
+#define count_atomic_inc(x) pg_atomic_fetch_add_u64((x), 1)
+#define count_atomic_dec(x) pg_atomic_fetch_sub_u64((x), 1)
+#define MAX_NENTRIES ((uint64)PG_INT64_MAX)
+#else
+typedef pg_atomic_uint32 Count;
+#define count_atomic_inc(x) pg_atomic_fetch_add_u32((x), 1)
+#define count_atomic_dec(x) pg_atomic_fetch_sub_u32((x), 1)
+#define MAX_NENTRIES ((uint32)PG_INT32_MAX)
+#endif
+
/*
* Per-freelist data.
*
@@ -143,7 +156,7 @@ typedef HASHBUCKET *HASHSEGMENT;
typedef struct
{
slock_t mutex; /* spinlock for this freelist */
- long nentries; /* number of entries in associated buckets */
+ Count nentries; /* number of entries in associated buckets */
HASHELEMENT *freeList; /* chain of free elements */
} FreeListData;
@@ -297,6 +310,54 @@ string_compare(const char *key1, const char *key2, Size keysize)
return strncmp(key1, key2, keysize - 1);
}
+/*
+ * Free list routines
+ */
+static inline void
+free_list_link_entry(HASHHDR *hctl, HASHBUCKET currBucket, int freelist_idx)
+{
+ FreeListData *list = &hctl->freeList[freelist_idx];
+
+ if (IS_PARTITIONED(hctl))
+ {
+ SpinLockAcquire(&list->mutex);
+ currBucket->link = list->freeList;
+ list->freeList = currBucket;
+ SpinLockRelease(&list->mutex);
+ }
+ else
+ {
+ currBucket->link = list->freeList;
+ list->freeList = currBucket;
+ }
+}
+
+static inline void
+free_list_increment_nentries(HASHHDR *hctl, int freelist_idx)
+{
+ FreeListData *list = &hctl->freeList[freelist_idx];
+
+ /* Check for overflow */
+ Assert(hctl->freeList[freelist_idx].nentries.value < MAX_NENTRIES);
+
+ if (IS_PARTITIONED(hctl))
+ count_atomic_inc(&list->nentries);
+ else
+ list->nentries.value++;
+}
+
+static inline void
+free_list_decrement_nentries(HASHHDR *hctl, int freelist_idx)
+{
+ FreeListData *list = &hctl->freeList[freelist_idx];
+
+ if (IS_PARTITIONED(hctl))
+ count_atomic_dec(&list->nentries);
+ else
+ list->nentries.value--;
+ /* Check for overflow */
+ Assert(hctl->freeList[freelist_idx].nentries.value < MAX_NENTRIES);
+}
/************************** CREATE ROUTINES **********************/
@@ -956,7 +1017,7 @@ hash_search_with_hash_value(HTAB *hashp,
* order of these tests is to try to check cheaper conditions first.
*/
if (!IS_PARTITIONED(hctl) && !hashp->frozen &&
- hctl->freeList[0].nentries / (long) (hctl->max_bucket + 1) >= hctl->ffactor &&
+ hctl->freeList[0].nentries.value / (long) (hctl->max_bucket + 1) >= hctl->ffactor &&
!has_seq_scans(hashp))
(void) expand_table(hashp);
}
@@ -1012,23 +1073,14 @@ hash_search_with_hash_value(HTAB *hashp,
case HASH_REMOVE:
if (currBucket != NULL)
{
- /* if partitioned, must lock to touch nentries and freeList */
- if (IS_PARTITIONED(hctl))
- SpinLockAcquire(&(hctl->freeList[freelist_idx].mutex));
-
/* delete the record from the appropriate nentries counter. */
- Assert(hctl->freeList[freelist_idx].nentries > 0);
- hctl->freeList[freelist_idx].nentries--;
+ free_list_decrement_nentries(hctl, freelist_idx);
/* remove record from hash bucket's chain. */
*prevBucketPtr = currBucket->link;
/* add the record to the appropriate freelist. */
- currBucket->link = hctl->freeList[freelist_idx].freeList;
- hctl->freeList[freelist_idx].freeList = currBucket;
-
- if (IS_PARTITIONED(hctl))
- SpinLockRelease(&hctl->freeList[freelist_idx].mutex);
+ free_list_link_entry(hctl, currBucket, freelist_idx);
/*
* better hope the caller is synchronizing access to this
@@ -1070,6 +1122,7 @@ hash_search_with_hash_value(HTAB *hashp,
(errcode(ERRCODE_OUT_OF_MEMORY),
errmsg("out of memory")));
}
+ free_list_increment_nentries(hctl, freelist_idx);
/* link into hashbucket chain */
*prevBucketPtr = currBucket;
@@ -1120,6 +1173,7 @@ hash_update_hash_key(HTAB *hashp,
{
HASHELEMENT *existingElement = ELEMENT_FROM_KEY(existingEntry);
HASHHDR *hctl = hashp->hctl;
+ uint32 oldhashvalue;
uint32 newhashvalue;
Size keysize;
uint32 bucket;
@@ -1173,6 +1227,7 @@ hash_update_hash_key(HTAB *hashp,
hashp->tabname);
oldPrevPtr = prevBucketPtr;
+ oldhashvalue = existingElement->hashvalue;
/*
* Now perform the equivalent of a HASH_ENTER operation to locate the hash
@@ -1226,12 +1281,21 @@ hash_update_hash_key(HTAB *hashp,
*/
if (bucket != newbucket)
{
+ int old_freelist_idx = FREELIST_IDX(hctl, oldhashvalue);
+ int new_freelist_idx = FREELIST_IDX(hctl, newhashvalue);
+
/* OK to remove record from old hash bucket's chain. */
*oldPrevPtr = currBucket->link;
/* link into new hashbucket chain */
*prevBucketPtr = currBucket;
currBucket->link = NULL;
+
+ if (old_freelist_idx != new_freelist_idx)
+ {
+ free_list_decrement_nentries(hctl, old_freelist_idx);
+ free_list_increment_nentries(hctl, new_freelist_idx);
+ }
}
/* copy new key into record */
@@ -1243,6 +1307,193 @@ hash_update_hash_key(HTAB *hashp,
return true;
}
+/*
+ * hash_insert_with_hash_nocheck - inserts new entry into bucket without
+ * checking for duplicates.
+ *
+ * Caller should be sure there is no conflicting entry.
+ *
+ * Caller may pass pointer to old entry acquired with hash_delete_skip_freelist.
+ * In this case entry will be reused and returned as a new.
+ */
+void *
+hash_insert_with_hash_nocheck(HTAB *hashp,
+ const void *keyPtr,
+ uint32 hashvalue,
+ void *oldentry)
+{
+ HASHHDR *hctl = hashp->hctl;
+ int freelist_idx = FREELIST_IDX(hctl, hashvalue);
+ uint32 bucket;
+ long segment_num;
+ long segment_ndx;
+ HASHSEGMENT segp;
+ HASHBUCKET currBucket;
+ HASHBUCKET *prevBucketPtr;
+
+#if HASH_STATISTICS
+ hash_accesses++;
+ hctl->accesses++;
+#endif
+
+ /* disallow updates if frozen */
+ if (hashp->frozen)
+ elog(ERROR, "cannot update in frozen hashtable \"%s\"",
+ hashp->tabname);
+
+ if (!IS_PARTITIONED(hctl) &&
+ hctl->freeList[0].nentries.value > (long) hctl->max_bucket &&
+ !has_seq_scans(hashp))
+ (void) expand_table(hashp);
+
+ /*
+ * Lookup the existing element using its saved hash value. We need to do
+ * this to be able to unlink it from its hash chain, but as a side benefit
+ * we can verify the validity of the passed existingEntry pointer.
+ */
+ bucket = calc_bucket(hctl, hashvalue);
+
+ segment_num = bucket >> hashp->sshift;
+ segment_ndx = MOD(bucket, hashp->ssize);
+
+ segp = hashp->dir[segment_num];
+
+ if (segp == NULL)
+ hash_corrupted(hashp);
+
+ prevBucketPtr = &segp[segment_ndx];
+
+ if (oldentry != NULL)
+ currBucket = ELEMENT_FROM_KEY(oldentry);
+ else
+ currBucket = get_hash_entry(hashp, freelist_idx);
+ free_list_increment_nentries(hctl, freelist_idx);
+
+ if (currBucket == NULL)
+ {
+ /* report a generic message */
+ if (hashp->isshared)
+ ereport(ERROR,
+ (errcode(ERRCODE_OUT_OF_MEMORY),
+ errmsg("out of shared memory")));
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_OUT_OF_MEMORY),
+ errmsg("out of memory")));
+ }
+
+ /* copy key into record */
+ currBucket->hashvalue = hashvalue;
+ currBucket->link = *prevBucketPtr;
+ hashp->keycopy(ELEMENTKEY(currBucket), keyPtr, hashp->keysize);
+
+ *prevBucketPtr = currBucket;
+
+ return (void *) ELEMENTKEY(currBucket);
+}
+
+/*
+ * hash_delete_skip_freelist - find and delete entry, but don't put it
+ * to free list.
+ *
+ * Used in Buffer Manager to reuse entry for evicted buffer.
+ *
+ * Returned entry should be either reused with hash_insert_with_hash_nocheck
+ * or returned to free list with hash_return_to_freelist.
+ */
+void *
+hash_delete_skip_freelist(HTAB *hashp,
+ const void *keyPtr,
+ uint32 hashvalue)
+{
+ HASHHDR *hctl = hashp->hctl;
+ int freelist_idx = FREELIST_IDX(hctl, hashvalue);
+ Size keysize;
+ uint32 bucket;
+ long segment_num;
+ long segment_ndx;
+ HASHSEGMENT segp;
+ HASHBUCKET currBucket;
+ HASHBUCKET *prevBucketPtr;
+ HashCompareFunc match;
+
+#if HASH_STATISTICS
+ hash_accesses++;
+ hctl->accesses++;
+#endif
+
+ /*
+ * Do the initial lookup
+ */
+ bucket = calc_bucket(hctl, hashvalue);
+
+ segment_num = bucket >> hashp->sshift;
+ segment_ndx = MOD(bucket, hashp->ssize);
+
+ segp = hashp->dir[segment_num];
+
+ if (segp == NULL)
+ hash_corrupted(hashp);
+
+ prevBucketPtr = &segp[segment_ndx];
+ currBucket = *prevBucketPtr;
+
+ /*
+ * Follow collision chain looking for matching key
+ */
+ match = hashp->match; /* save one fetch in inner loop */
+ keysize = hashp->keysize; /* ditto */
+
+ while (currBucket != NULL)
+ {
+ if (currBucket->hashvalue == hashvalue &&
+ match(ELEMENTKEY(currBucket), keyPtr, keysize) == 0)
+ break;
+ prevBucketPtr = &(currBucket->link);
+ currBucket = *prevBucketPtr;
+#if HASH_STATISTICS
+ hash_collisions++;
+ hctl->collisions++;
+#endif
+ }
+
+ if (currBucket == NULL)
+ return NULL;
+
+ /* delete the record from the appropriate nentries counter. */
+ free_list_decrement_nentries(hctl, freelist_idx);
+
+ /* remove record from hash bucket's chain. */
+ *prevBucketPtr = currBucket->link;
+
+ return (void *) ELEMENTKEY(currBucket);
+}
+
+/*
+ * hash_return_to_freelist - return entry deleted with
+ * hash_delete_skip_freelist to free list.
+ *
+ * Used in Buffer Manager in case new conflicting entry were inserted by
+ * concurrent process.
+ */
+void
+hash_return_to_freelist(HTAB *hashp,
+ const void *entry,
+ uint32 hashvalue)
+{
+ HASHHDR *hctl = hashp->hctl;
+ int freelist_idx = FREELIST_IDX(hctl, hashvalue);
+ HASHBUCKET currBucket;
+
+ if (entry == NULL)
+ return;
+
+ currBucket = ELEMENT_FROM_KEY(entry);
+
+ /* add the record to the appropriate freelist. */
+ free_list_link_entry(hctl, currBucket, freelist_idx);
+}
+
/*
* Allocate a new hashtable entry if possible; return NULL if out of memory.
* (Or, if the underlying space allocator throws error for out-of-memory,
@@ -1304,11 +1555,6 @@ get_hash_entry(HTAB *hashp, int freelist_idx)
hctl->freeList[borrow_from_idx].freeList = newElement->link;
SpinLockRelease(&(hctl->freeList[borrow_from_idx].mutex));
- /* careful: count the new element in its proper freelist */
- SpinLockAcquire(&hctl->freeList[freelist_idx].mutex);
- hctl->freeList[freelist_idx].nentries++;
- SpinLockRelease(&hctl->freeList[freelist_idx].mutex);
-
return newElement;
}
@@ -1320,9 +1566,8 @@ get_hash_entry(HTAB *hashp, int freelist_idx)
}
}
- /* remove entry from freelist, bump nentries */
+ /* remove entry from freelist */
hctl->freeList[freelist_idx].freeList = newElement->link;
- hctl->freeList[freelist_idx].nentries++;
if (IS_PARTITIONED(hctl))
SpinLockRelease(&hctl->freeList[freelist_idx].mutex);
@@ -1337,7 +1582,7 @@ long
hash_get_num_entries(HTAB *hashp)
{
int i;
- long sum = hashp->hctl->freeList[0].nentries;
+ long sum = hashp->hctl->freeList[0].nentries.value;
/*
* We currently don't bother with acquiring the mutexes; it's only
@@ -1347,7 +1592,7 @@ hash_get_num_entries(HTAB *hashp)
if (IS_PARTITIONED(hashp->hctl))
{
for (i = 1; i < NUM_FREELISTS; i++)
- sum += hashp->hctl->freeList[i].nentries;
+ sum += hashp->hctl->freeList[i].nentries.value;
}
return sum;
diff --git a/src/include/storage/buf_internals.h b/src/include/storage/buf_internals.h
index 3377fa56768..51c699e5e8f 100644
--- a/src/include/storage/buf_internals.h
+++ b/src/include/storage/buf_internals.h
@@ -323,8 +323,10 @@ extern Size BufTableShmemSize(int size);
extern void InitBufTable(int size);
extern uint32 BufTableHashCode(BufferTag *tagPtr);
extern int BufTableLookup(BufferTag *tagPtr, uint32 hashcode);
-extern int BufTableInsert(BufferTag *tagPtr, uint32 hashcode, int buf_id);
-extern void BufTableDelete(BufferTag *tagPtr, uint32 hashcode);
+extern void BufTableInsert(BufferTag *tagPtr, uint32 hashcode, int buf_id,
+ void *oldelem);
+extern void *BufTableDelete(BufferTag *tagPtr, uint32 hashcode);
+extern void BufTableFreeDeleted(void *oldelem, uint32 hashcode);
/* localbuf.c */
extern PrefetchBufferResult PrefetchLocalBuffer(SMgrRelation smgr,
diff --git a/src/include/utils/hsearch.h b/src/include/utils/hsearch.h
index f1deb9beab0..674929ec11b 100644
--- a/src/include/utils/hsearch.h
+++ b/src/include/utils/hsearch.h
@@ -146,4 +146,21 @@ extern Size hash_get_shared_size(HASHCTL *info, int flags);
extern void AtEOXact_HashTables(bool isCommit);
extern void AtEOSubXact_HashTables(bool isCommit, int nestDepth);
+/*
+ * Buffer Manager optimization utilities.
+ * They made to avoid taking two partition locks simultaneously and
+ * skip interraction with dynahash's freelist.
+ * Use them carefully and only if they gives meaningful improvement.
+ */
+extern void *hash_insert_with_hash_nocheck(HTAB *hashp,
+ const void *keyPtr,
+ uint32 hashvalue,
+ void *oldentry);
+extern void *hash_delete_skip_freelist(HTAB *hashp,
+ const void *keyPtr,
+ uint32 hashvalue);
+extern void hash_return_to_freelist(HTAB *hashp,
+ const void *entry,
+ uint32 hashvalue);
+
#endif /* HSEARCH_H */
--
2.34.1
[text/x-patch] v1-0001-bufmgr-do-not-acquire-two-partition-lo.patch (25.1K, ../../bca5495fa6ef812028ae8eb9424b66a9da1ce47d.camel@postgrespro.ru/3-v1-0001-bufmgr-do-not-acquire-two-partition-lo.patch)
download | inline diff:
From c3388704432853d9c4cdf6e77b44360b572c6878 Mon Sep 17 00:00:00 2001
From: Yura Sokolov <y.sokolov@postgrespro.ru>
Date: Wed, 22 Sep 2021 13:10:37 +0300
Subject: [PATCH v1] bufmgr: do not acquire two partition locks.
Acquiring two partition locks leads to complex dependency chain that hurts
at high concurrency level.
There is no need to hold both lock simultaneously. Buffer is pinned so
other processes could not select it for eviction. If tag is cleared and
buffer removed from old partition other processes will not find it.
Therefore it is safe to release old partition lock before acquiring
new partition lock.
This change requires to manually return BufferDesc to free list.
Also insertion and deletion to dynahash is optimized by avoiding
unnecessary free list manipulations in common case (when buffer is
reused)
Also small and never triggered bug in hash_update_hash_key is fixed.
---
src/backend/storage/buffer/buf_table.c | 54 +++--
src/backend/storage/buffer/bufmgr.c | 190 ++++++++--------
src/backend/utils/hash/dynahash.c | 289 +++++++++++++++++++++++--
src/include/storage/buf_internals.h | 6 +-
src/include/utils/hsearch.h | 17 ++
5 files changed, 410 insertions(+), 146 deletions(-)
diff --git a/src/backend/storage/buffer/buf_table.c b/src/backend/storage/buffer/buf_table.c
index caa03ae1233..05e1dc9dd29 100644
--- a/src/backend/storage/buffer/buf_table.c
+++ b/src/backend/storage/buffer/buf_table.c
@@ -107,36 +107,29 @@ BufTableLookup(BufferTag *tagPtr, uint32 hashcode)
/*
* BufTableInsert
- * Insert a hashtable entry for given tag and buffer ID,
- * unless an entry already exists for that tag
- *
- * Returns -1 on successful insertion. If a conflicting entry exists
- * already, returns the buffer ID in that entry.
+ * Insert a hashtable entry for given tag and buffer ID.
+ * Caller should be sure there is no conflicting entry.
*
* Caller must hold exclusive lock on BufMappingLock for tag's partition
+ * and call BufTableLookup to check for conflicting entry.
+ *
+ * If oldelem is passed it is reused.
*/
-int
-BufTableInsert(BufferTag *tagPtr, uint32 hashcode, int buf_id)
+void
+BufTableInsert(BufferTag *tagPtr, uint32 hashcode, int buf_id, void *oldelem)
{
BufferLookupEnt *result;
- bool found;
Assert(buf_id >= 0); /* -1 is reserved for not-in-table */
Assert(tagPtr->blockNum != P_NEW); /* invalid tag */
result = (BufferLookupEnt *)
- hash_search_with_hash_value(SharedBufHash,
- (void *) tagPtr,
- hashcode,
- HASH_ENTER,
- &found);
-
- if (found) /* found something already in the table */
- return result->id;
+ hash_insert_with_hash_nocheck(SharedBufHash,
+ (void *) tagPtr,
+ hashcode,
+ oldelem);
result->id = buf_id;
-
- return -1;
}
/*
@@ -144,19 +137,32 @@ BufTableInsert(BufferTag *tagPtr, uint32 hashcode, int buf_id)
* Delete the hashtable entry for given tag (which must exist)
*
* Caller must hold exclusive lock on BufMappingLock for tag's partition
+ *
+ * Returns pointer to internal hashtable entry that should be passed either
+ * to BufTableInsert or BufTableFreeDeleted.
*/
-void
+void *
BufTableDelete(BufferTag *tagPtr, uint32 hashcode)
{
BufferLookupEnt *result;
result = (BufferLookupEnt *)
- hash_search_with_hash_value(SharedBufHash,
- (void *) tagPtr,
- hashcode,
- HASH_REMOVE,
- NULL);
+ hash_delete_skip_freelist(SharedBufHash,
+ (void *) tagPtr,
+ hashcode);
if (!result) /* shouldn't happen */
elog(ERROR, "shared buffer hash table corrupted");
+
+ return result;
+}
+
+/*
+ * BufTableFreeDeleted
+ * Returns deleted hashtable entry to freelist.
+ */
+void
+BufTableFreeDeleted(void *oldelem, uint32 hashcode)
+{
+ hash_return_to_freelist(SharedBufHash, oldelem, hashcode);
}
diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c
index e88e4e918b0..6053a870e61 100644
--- a/src/backend/storage/buffer/bufmgr.c
+++ b/src/backend/storage/buffer/bufmgr.c
@@ -1114,6 +1114,7 @@ BufferAlloc(SMgrRelation smgr, char relpersistence, ForkNumber forkNum,
BufferDesc *buf;
bool valid;
uint32 buf_state;
+ void *oldElem = NULL;
/* create a tag so we can lookup the buffer */
INIT_BUFFERTAG(newTag, smgr->smgr_rnode.node, forkNum, blockNum);
@@ -1288,93 +1289,16 @@ BufferAlloc(SMgrRelation smgr, char relpersistence, ForkNumber forkNum,
oldHash = BufTableHashCode(&oldTag);
oldPartitionLock = BufMappingPartitionLock(oldHash);
- /*
- * Must lock the lower-numbered partition first to avoid
- * deadlocks.
- */
- if (oldPartitionLock < newPartitionLock)
- {
- LWLockAcquire(oldPartitionLock, LW_EXCLUSIVE);
- LWLockAcquire(newPartitionLock, LW_EXCLUSIVE);
- }
- else if (oldPartitionLock > newPartitionLock)
- {
- LWLockAcquire(newPartitionLock, LW_EXCLUSIVE);
- LWLockAcquire(oldPartitionLock, LW_EXCLUSIVE);
- }
- else
- {
- /* only one partition, only one lock */
- LWLockAcquire(newPartitionLock, LW_EXCLUSIVE);
- }
+ LWLockAcquire(oldPartitionLock, LW_EXCLUSIVE);
}
else
{
- /* if it wasn't valid, we need only the new partition */
- LWLockAcquire(newPartitionLock, LW_EXCLUSIVE);
/* remember we have no old-partition lock or tag */
oldPartitionLock = NULL;
/* keep the compiler quiet about uninitialized variables */
oldHash = 0;
}
- /*
- * Try to make a hashtable entry for the buffer under its new tag.
- * This could fail because while we were writing someone else
- * allocated another buffer for the same block we want to read in.
- * Note that we have not yet removed the hashtable entry for the old
- * tag.
- */
- buf_id = BufTableInsert(&newTag, newHash, buf->buf_id);
-
- if (buf_id >= 0)
- {
- /*
- * Got a collision. Someone has already done what we were about to
- * do. We'll just handle this as if it were found in the buffer
- * pool in the first place. First, give up the buffer we were
- * planning to use.
- */
- UnpinBuffer(buf, true);
-
- /* Can give up that buffer's mapping partition lock now */
- if (oldPartitionLock != NULL &&
- oldPartitionLock != newPartitionLock)
- LWLockRelease(oldPartitionLock);
-
- /* remaining code should match code at top of routine */
-
- buf = GetBufferDescriptor(buf_id);
-
- valid = PinBuffer(buf, strategy);
-
- /* Can release the mapping lock as soon as we've pinned it */
- LWLockRelease(newPartitionLock);
-
- *foundPtr = true;
-
- if (!valid)
- {
- /*
- * We can only get here if (a) someone else is still reading
- * in the page, or (b) a previous read attempt failed. We
- * have to wait for any active read attempt to finish, and
- * then set up our own read attempt if the page is still not
- * BM_VALID. StartBufferIO does it all.
- */
- if (StartBufferIO(buf, true))
- {
- /*
- * If we get here, previous attempts to read the buffer
- * must have failed ... but we shall bravely try again.
- */
- *foundPtr = false;
- }
- }
-
- return buf;
- }
-
/*
* Need to lock the buffer header too in order to change its tag.
*/
@@ -1391,31 +1315,102 @@ BufferAlloc(SMgrRelation smgr, char relpersistence, ForkNumber forkNum,
break;
UnlockBufHdr(buf, buf_state);
- BufTableDelete(&newTag, newHash);
- if (oldPartitionLock != NULL &&
- oldPartitionLock != newPartitionLock)
+ if (oldPartitionLock != NULL)
LWLockRelease(oldPartitionLock);
- LWLockRelease(newPartitionLock);
UnpinBuffer(buf, true);
}
/*
- * Okay, it's finally safe to rename the buffer.
+ * Clear out the buffer's tag and flags. We must do this to ensure that
+ * linear scans of the buffer array don't think the buffer is valid. We
+ * also reset the usage_count since any recency of use of the old content
+ * is no longer relevant.
*
- * Clearing BM_VALID here is necessary, clearing the dirtybits is just
- * paranoia. We also reset the usage_count since any recency of use of
- * the old content is no longer relevant. (The usage_count starts out at
- * 1 so that the buffer can survive one clock-sweep pass.)
+ * Since we are single pinner, there should no be PIN_COUNT_WAITER or
+ * IO_IN_PROGRESS (flags that were not cleared in previous code).
+ */
+ Assert((oldFlags & (BM_PIN_COUNT_WAITER | BM_IO_IN_PROGRESS)) == 0);
+ CLEAR_BUFFERTAG(buf->tag);
+ buf_state &= ~(BUF_FLAG_MASK | BUF_USAGECOUNT_MASK);
+ UnlockBufHdr(buf, buf_state);
+
+ if (oldFlags & BM_TAG_VALID)
+ oldElem = BufTableDelete(&oldTag, oldHash);
+
+ if (oldPartitionLock != newPartitionLock)
+ {
+ if (oldPartitionLock != NULL)
+ LWLockRelease(oldPartitionLock);
+ LWLockAcquire(newPartitionLock, LW_EXCLUSIVE);
+ }
+
+ /*
+ * Try to make a hashtable entry for the buffer under its new tag. This
+ * could fail because while we were writing someone else allocated another
+ * buffer for the same block we want to read in. Note that we have not yet
+ * removed the hashtable entry for the old tag.
+ */
+ buf_id = BufTableLookup(&newTag, newHash);
+
+ if (buf_id >= 0)
+ {
+ /*
+ * Got a collision. Someone has already done what we were about to do.
+ * We'll just handle this as if it were found in the buffer pool in
+ * the first place. First, give up the buffer we were planning to use
+ * and put it to free lists.
+ */
+ UnpinBuffer(buf, true);
+ StrategyFreeBuffer(buf);
+ if (oldElem != NULL)
+ BufTableFreeDeleted(oldElem, oldHash);
+
+ /* remaining code should match code at top of routine */
+
+ buf = GetBufferDescriptor(buf_id);
+
+ valid = PinBuffer(buf, strategy);
+
+ /* Can release the mapping lock as soon as we've pinned it */
+ LWLockRelease(newPartitionLock);
+
+ *foundPtr = true;
+
+ if (!valid)
+ {
+ /*
+ * We can only get here if (a) someone else is still reading in
+ * the page, or (b) a previous read attempt failed. We have to
+ * wait for any active read attempt to finish, and then set up our
+ * own read attempt if the page is still not BM_VALID.
+ * StartBufferIO does it all.
+ */
+ if (StartBufferIO(buf, true))
+ {
+ /*
+ * If we get here, previous attempts to read the buffer must
+ * have failed ... but we shall bravely try again.
+ */
+ *foundPtr = false;
+ }
+ }
+
+ return buf;
+ }
+
+ /*
+ * Okay, it's finally safe to rename the buffer.
*
* Make sure BM_PERMANENT is set for buffers that must be written at every
* checkpoint. Unlogged buffers only need to be written at shutdown
* checkpoints, except for their "init" forks, which need to be treated
* just like permanent relations.
+ *
+ * The usage_count starts out at 1 so that the buffer can survive one
+ * clock-sweep pass.
*/
+ buf_state = LockBufHdr(buf);
buf->tag = newTag;
- buf_state &= ~(BM_VALID | BM_DIRTY | BM_JUST_DIRTIED |
- BM_CHECKPOINT_NEEDED | BM_IO_ERROR | BM_PERMANENT |
- BUF_USAGECOUNT_MASK);
if (relpersistence == RELPERSISTENCE_PERMANENT || forkNum == INIT_FORKNUM)
buf_state |= BM_TAG_VALID | BM_PERMANENT | BUF_USAGECOUNT_ONE;
else
@@ -1423,13 +1418,7 @@ BufferAlloc(SMgrRelation smgr, char relpersistence, ForkNumber forkNum,
UnlockBufHdr(buf, buf_state);
- if (oldPartitionLock != NULL)
- {
- BufTableDelete(&oldTag, oldHash);
- if (oldPartitionLock != newPartitionLock)
- LWLockRelease(oldPartitionLock);
- }
-
+ BufTableInsert(&newTag, newHash, buf->buf_id, oldElem);
LWLockRelease(newPartitionLock);
/*
@@ -1539,7 +1528,12 @@ retry:
* Remove the buffer from the lookup hashtable, if it was in there.
*/
if (oldFlags & BM_TAG_VALID)
- BufTableDelete(&oldTag, oldHash);
+ {
+ void *oldElem;
+
+ oldElem = BufTableDelete(&oldTag, oldHash);
+ BufTableFreeDeleted(oldElem, oldHash);
+ }
/*
* Done with mapping lock.
diff --git a/src/backend/utils/hash/dynahash.c b/src/backend/utils/hash/dynahash.c
index 6546e3c7c79..ce5bba8e975 100644
--- a/src/backend/utils/hash/dynahash.c
+++ b/src/backend/utils/hash/dynahash.c
@@ -99,6 +99,7 @@
#include "access/xact.h"
#include "common/hashfn.h"
#include "port/pg_bitutils.h"
+#include "port/atomics.h"
#include "storage/shmem.h"
#include "storage/spin.h"
#include "utils/dynahash.h"
@@ -133,6 +134,18 @@ typedef HASHELEMENT *HASHBUCKET;
/* A hash segment is an array of bucket headers */
typedef HASHBUCKET *HASHSEGMENT;
+#if SIZEOF_LONG == 8
+typedef pg_atomic_uint64 Count;
+#define count_atomic_inc(x) pg_atomic_fetch_add_u64((x), 1)
+#define count_atomic_dec(x) pg_atomic_fetch_sub_u64((x), 1)
+#define MAX_NENTRIES ((uint64)PG_INT64_MAX)
+#else
+typedef pg_atomic_uint32 Count;
+#define count_atomic_inc(x) pg_atomic_fetch_add_u32((x), 1)
+#define count_atomic_dec(x) pg_atomic_fetch_sub_u32((x), 1)
+#define MAX_NENTRIES ((uint32)PG_INT32_MAX)
+#endif
+
/*
* Per-freelist data.
*
@@ -153,7 +166,7 @@ typedef HASHBUCKET *HASHSEGMENT;
typedef struct
{
slock_t mutex; /* spinlock for this freelist */
- long nentries; /* number of entries in associated buckets */
+ Count nentries; /* number of entries in associated buckets */
HASHELEMENT *freeList; /* chain of free elements */
} FreeListData;
@@ -306,6 +319,54 @@ string_compare(const char *key1, const char *key2, Size keysize)
return strncmp(key1, key2, keysize - 1);
}
+/*
+ * Free list routines
+ */
+static inline void
+free_list_link_entry(HASHHDR *hctl, HASHBUCKET currBucket, int freelist_idx)
+{
+ FreeListData *list = &hctl->freeList[freelist_idx];
+
+ if (IS_PARTITIONED(hctl))
+ {
+ SpinLockAcquire(&list->mutex);
+ currBucket->link = list->freeList;
+ list->freeList = currBucket;
+ SpinLockRelease(&list->mutex);
+ }
+ else
+ {
+ currBucket->link = list->freeList;
+ list->freeList = currBucket;
+ }
+}
+
+static inline void
+free_list_increment_nentries(HASHHDR *hctl, int freelist_idx)
+{
+ FreeListData *list = &hctl->freeList[freelist_idx];
+
+ /* Check for overflow */
+ Assert(hctl->freeList[freelist_idx].nentries.value < MAX_NENTRIES);
+
+ if (IS_PARTITIONED(hctl))
+ count_atomic_inc(&list->nentries);
+ else
+ list->nentries.value++;
+}
+
+static inline void
+free_list_decrement_nentries(HASHHDR *hctl, int freelist_idx)
+{
+ FreeListData *list = &hctl->freeList[freelist_idx];
+
+ if (IS_PARTITIONED(hctl))
+ count_atomic_dec(&list->nentries);
+ else
+ list->nentries.value--;
+ /* Check for overflow */
+ Assert(hctl->freeList[freelist_idx].nentries.value < MAX_NENTRIES);
+}
/************************** CREATE ROUTINES **********************/
@@ -1000,7 +1061,7 @@ hash_search_with_hash_value(HTAB *hashp,
* Can't split if running in partitioned mode, nor if frozen, nor if
* table is the subject of any active hash_seq_search scans.
*/
- if (hctl->freeList[0].nentries > (long) hctl->max_bucket &&
+ if (hctl->freeList[0].nentries.value > (long) hctl->max_bucket &&
!IS_PARTITIONED(hctl) && !hashp->frozen &&
!has_seq_scans(hashp))
(void) expand_table(hashp);
@@ -1057,23 +1118,14 @@ hash_search_with_hash_value(HTAB *hashp,
case HASH_REMOVE:
if (currBucket != NULL)
{
- /* if partitioned, must lock to touch nentries and freeList */
- if (IS_PARTITIONED(hctl))
- SpinLockAcquire(&(hctl->freeList[freelist_idx].mutex));
-
/* delete the record from the appropriate nentries counter. */
- Assert(hctl->freeList[freelist_idx].nentries > 0);
- hctl->freeList[freelist_idx].nentries--;
+ free_list_decrement_nentries(hctl, freelist_idx);
/* remove record from hash bucket's chain. */
*prevBucketPtr = currBucket->link;
/* add the record to the appropriate freelist. */
- currBucket->link = hctl->freeList[freelist_idx].freeList;
- hctl->freeList[freelist_idx].freeList = currBucket;
-
- if (IS_PARTITIONED(hctl))
- SpinLockRelease(&hctl->freeList[freelist_idx].mutex);
+ free_list_link_entry(hctl, currBucket, freelist_idx);
/*
* better hope the caller is synchronizing access to this
@@ -1115,6 +1167,7 @@ hash_search_with_hash_value(HTAB *hashp,
(errcode(ERRCODE_OUT_OF_MEMORY),
errmsg("out of memory")));
}
+ free_list_increment_nentries(hctl, freelist_idx);
/* link into hashbucket chain */
*prevBucketPtr = currBucket;
@@ -1165,6 +1218,7 @@ hash_update_hash_key(HTAB *hashp,
{
HASHELEMENT *existingElement = ELEMENT_FROM_KEY(existingEntry);
HASHHDR *hctl = hashp->hctl;
+ uint32 oldhashvalue;
uint32 newhashvalue;
Size keysize;
uint32 bucket;
@@ -1218,6 +1272,7 @@ hash_update_hash_key(HTAB *hashp,
hashp->tabname);
oldPrevPtr = prevBucketPtr;
+ oldhashvalue = existingElement->hashvalue;
/*
* Now perform the equivalent of a HASH_ENTER operation to locate the hash
@@ -1271,12 +1326,21 @@ hash_update_hash_key(HTAB *hashp,
*/
if (bucket != newbucket)
{
+ int old_freelist_idx = FREELIST_IDX(hctl, oldhashvalue);
+ int new_freelist_idx = FREELIST_IDX(hctl, newhashvalue);
+
/* OK to remove record from old hash bucket's chain. */
*oldPrevPtr = currBucket->link;
/* link into new hashbucket chain */
*prevBucketPtr = currBucket;
currBucket->link = NULL;
+
+ if (old_freelist_idx != new_freelist_idx)
+ {
+ free_list_decrement_nentries(hctl, old_freelist_idx);
+ free_list_increment_nentries(hctl, new_freelist_idx);
+ }
}
/* copy new key into record */
@@ -1288,6 +1352,193 @@ hash_update_hash_key(HTAB *hashp,
return true;
}
+/*
+ * hash_insert_with_hash_nocheck - inserts new entry into bucket without
+ * checking for duplicates.
+ *
+ * Caller should be sure there is no conflicting entry.
+ *
+ * Caller may pass pointer to old entry acquired with hash_delete_skip_freelist.
+ * In this case entry will be reused and returned as a new.
+ */
+void *
+hash_insert_with_hash_nocheck(HTAB *hashp,
+ const void *keyPtr,
+ uint32 hashvalue,
+ void *oldentry)
+{
+ HASHHDR *hctl = hashp->hctl;
+ int freelist_idx = FREELIST_IDX(hctl, hashvalue);
+ uint32 bucket;
+ long segment_num;
+ long segment_ndx;
+ HASHSEGMENT segp;
+ HASHBUCKET currBucket;
+ HASHBUCKET *prevBucketPtr;
+
+#if HASH_STATISTICS
+ hash_accesses++;
+ hctl->accesses++;
+#endif
+
+ /* disallow updates if frozen */
+ if (hashp->frozen)
+ elog(ERROR, "cannot update in frozen hashtable \"%s\"",
+ hashp->tabname);
+
+ if (!IS_PARTITIONED(hctl) &&
+ hctl->freeList[0].nentries.value > (long) hctl->max_bucket &&
+ !has_seq_scans(hashp))
+ (void) expand_table(hashp);
+
+ /*
+ * Lookup the existing element using its saved hash value. We need to do
+ * this to be able to unlink it from its hash chain, but as a side benefit
+ * we can verify the validity of the passed existingEntry pointer.
+ */
+ bucket = calc_bucket(hctl, hashvalue);
+
+ segment_num = bucket >> hashp->sshift;
+ segment_ndx = MOD(bucket, hashp->ssize);
+
+ segp = hashp->dir[segment_num];
+
+ if (segp == NULL)
+ hash_corrupted(hashp);
+
+ prevBucketPtr = &segp[segment_ndx];
+
+ if (oldentry != NULL)
+ currBucket = ELEMENT_FROM_KEY(oldentry);
+ else
+ currBucket = get_hash_entry(hashp, freelist_idx);
+ free_list_increment_nentries(hctl, freelist_idx);
+
+ if (currBucket == NULL)
+ {
+ /* report a generic message */
+ if (hashp->isshared)
+ ereport(ERROR,
+ (errcode(ERRCODE_OUT_OF_MEMORY),
+ errmsg("out of shared memory")));
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_OUT_OF_MEMORY),
+ errmsg("out of memory")));
+ }
+
+ /* copy key into record */
+ currBucket->hashvalue = hashvalue;
+ currBucket->link = *prevBucketPtr;
+ hashp->keycopy(ELEMENTKEY(currBucket), keyPtr, hashp->keysize);
+
+ *prevBucketPtr = currBucket;
+
+ return (void *) ELEMENTKEY(currBucket);
+}
+
+/*
+ * hash_delete_skip_freelist - find and delete entry, but don't put it
+ * to free list.
+ *
+ * Used in Buffer Manager to reuse entry for evicted buffer.
+ *
+ * Returned entry should be either reused with hash_insert_with_hash_nocheck
+ * or returned to free list with hash_return_to_freelist.
+ */
+void *
+hash_delete_skip_freelist(HTAB *hashp,
+ const void *keyPtr,
+ uint32 hashvalue)
+{
+ HASHHDR *hctl = hashp->hctl;
+ int freelist_idx = FREELIST_IDX(hctl, hashvalue);
+ Size keysize;
+ uint32 bucket;
+ long segment_num;
+ long segment_ndx;
+ HASHSEGMENT segp;
+ HASHBUCKET currBucket;
+ HASHBUCKET *prevBucketPtr;
+ HashCompareFunc match;
+
+#if HASH_STATISTICS
+ hash_accesses++;
+ hctl->accesses++;
+#endif
+
+ /*
+ * Do the initial lookup
+ */
+ bucket = calc_bucket(hctl, hashvalue);
+
+ segment_num = bucket >> hashp->sshift;
+ segment_ndx = MOD(bucket, hashp->ssize);
+
+ segp = hashp->dir[segment_num];
+
+ if (segp == NULL)
+ hash_corrupted(hashp);
+
+ prevBucketPtr = &segp[segment_ndx];
+ currBucket = *prevBucketPtr;
+
+ /*
+ * Follow collision chain looking for matching key
+ */
+ match = hashp->match; /* save one fetch in inner loop */
+ keysize = hashp->keysize; /* ditto */
+
+ while (currBucket != NULL)
+ {
+ if (currBucket->hashvalue == hashvalue &&
+ match(ELEMENTKEY(currBucket), keyPtr, keysize) == 0)
+ break;
+ prevBucketPtr = &(currBucket->link);
+ currBucket = *prevBucketPtr;
+#if HASH_STATISTICS
+ hash_collisions++;
+ hctl->collisions++;
+#endif
+ }
+
+ if (currBucket == NULL)
+ return NULL;
+
+ /* delete the record from the appropriate nentries counter. */
+ free_list_decrement_nentries(hctl, freelist_idx);
+
+ /* remove record from hash bucket's chain. */
+ *prevBucketPtr = currBucket->link;
+
+ return (void *) ELEMENTKEY(currBucket);
+}
+
+/*
+ * hash_return_to_freelist - return entry deleted with
+ * hash_delete_skip_freelist to free list.
+ *
+ * Used in Buffer Manager in case new conflicting entry were inserted by
+ * concurrent process.
+ */
+void
+hash_return_to_freelist(HTAB *hashp,
+ const void *entry,
+ uint32 hashvalue)
+{
+ HASHHDR *hctl = hashp->hctl;
+ int freelist_idx = FREELIST_IDX(hctl, hashvalue);
+ HASHBUCKET currBucket;
+
+ if (entry == NULL)
+ return;
+
+ currBucket = ELEMENT_FROM_KEY(entry);
+
+ /* add the record to the appropriate freelist. */
+ free_list_link_entry(hctl, currBucket, freelist_idx);
+}
+
/*
* Allocate a new hashtable entry if possible; return NULL if out of memory.
* (Or, if the underlying space allocator throws error for out-of-memory,
@@ -1349,11 +1600,6 @@ get_hash_entry(HTAB *hashp, int freelist_idx)
hctl->freeList[borrow_from_idx].freeList = newElement->link;
SpinLockRelease(&(hctl->freeList[borrow_from_idx].mutex));
- /* careful: count the new element in its proper freelist */
- SpinLockAcquire(&hctl->freeList[freelist_idx].mutex);
- hctl->freeList[freelist_idx].nentries++;
- SpinLockRelease(&hctl->freeList[freelist_idx].mutex);
-
return newElement;
}
@@ -1365,9 +1611,8 @@ get_hash_entry(HTAB *hashp, int freelist_idx)
}
}
- /* remove entry from freelist, bump nentries */
+ /* remove entry from freelist */
hctl->freeList[freelist_idx].freeList = newElement->link;
- hctl->freeList[freelist_idx].nentries++;
if (IS_PARTITIONED(hctl))
SpinLockRelease(&hctl->freeList[freelist_idx].mutex);
@@ -1382,7 +1627,7 @@ long
hash_get_num_entries(HTAB *hashp)
{
int i;
- long sum = hashp->hctl->freeList[0].nentries;
+ long sum = hashp->hctl->freeList[0].nentries.value;
/*
* We currently don't bother with acquiring the mutexes; it's only
@@ -1392,7 +1637,7 @@ hash_get_num_entries(HTAB *hashp)
if (IS_PARTITIONED(hashp->hctl))
{
for (i = 1; i < NUM_FREELISTS; i++)
- sum += hashp->hctl->freeList[i].nentries;
+ sum += hashp->hctl->freeList[i].nentries.value;
}
return sum;
diff --git a/src/include/storage/buf_internals.h b/src/include/storage/buf_internals.h
index 33fcaf5c9a8..4a1d6b37821 100644
--- a/src/include/storage/buf_internals.h
+++ b/src/include/storage/buf_internals.h
@@ -327,8 +327,10 @@ extern Size BufTableShmemSize(int size);
extern void InitBufTable(int size);
extern uint32 BufTableHashCode(BufferTag *tagPtr);
extern int BufTableLookup(BufferTag *tagPtr, uint32 hashcode);
-extern int BufTableInsert(BufferTag *tagPtr, uint32 hashcode, int buf_id);
-extern void BufTableDelete(BufferTag *tagPtr, uint32 hashcode);
+extern void BufTableInsert(BufferTag *tagPtr, uint32 hashcode, int buf_id,
+ void *oldelem);
+extern void *BufTableDelete(BufferTag *tagPtr, uint32 hashcode);
+extern void BufTableFreeDeleted(void *oldelem, uint32 hashcode);
/* localbuf.c */
extern PrefetchBufferResult PrefetchLocalBuffer(SMgrRelation smgr,
diff --git a/src/include/utils/hsearch.h b/src/include/utils/hsearch.h
index d7af0239c8c..1d586ef1169 100644
--- a/src/include/utils/hsearch.h
+++ b/src/include/utils/hsearch.h
@@ -150,4 +150,21 @@ extern Size hash_get_shared_size(HASHCTL *info, int flags);
extern void AtEOXact_HashTables(bool isCommit);
extern void AtEOSubXact_HashTables(bool isCommit, int nestDepth);
+/*
+ * Buffer Manager optimization utilities.
+ * They made to avoid taking two partition locks simultaneously and
+ * skip interraction with dynahash's freelist.
+ * Use them carefully and only if they gives meaningful improvement.
+ */
+extern void *hash_insert_with_hash_nocheck(HTAB *hashp,
+ const void *keyPtr,
+ uint32 hashvalue,
+ void *oldentry);
+extern void *hash_delete_skip_freelist(HTAB *hashp,
+ const void *keyPtr,
+ uint32 hashvalue);
+extern void hash_return_to_freelist(HTAB *hashp,
+ const void *entry,
+ uint32 hashvalue);
+
#endif /* HSEARCH_H */
--
2.34.1
^ permalink raw reply [nested|flat] 26+ messages in thread
end of thread, other threads:[~2021-12-21 05:45 UTC | newest]
Thread overview: 26+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-10-12 07:35 Lock contention high Ashkil Dighin <ashkildighin76@gmail.com>
2021-10-12 10:29 ` Mikhail Zhilin <zhilin.ms@phystech.edu>
2021-10-14 06:45 ` Ashkil Dighin <ashkildighin76@gmail.com>
2021-10-14 07:47 ` Mikhail Zhilin <zhilin.ms@phystech.edu>
2021-10-12 14:39 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-10-14 06:03 ` Ashkil Dighin <ashkildighin76@gmail.com>
2021-10-14 06:42 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-10-12 15:37 ` Mladen Gogala <gogala.mladen@gmail.com>
2021-10-13 16:48 ` Justin Pryzby <pryzby@telsasoft.com>
2021-10-13 18:15 ` MichaelDBA <MichaelDBA@sqlexec.com>
2021-10-13 18:57 ` Paul Friedman <paul.friedman@streetlightdata.com>
2021-10-14 06:49 ` Ashkil Dighin <ashkildighin76@gmail.com>
2021-10-13 19:05 ` Peter Geoghegan <pg@bowt.ie>
2021-10-20 10:51 ` Ashkil Dighin <ashkildighin76@gmail.com>
2021-10-14 01:54 ` Jeremy Schneider <schneider@ardentperf.com>
2021-10-14 02:11 ` Peter Geoghegan <pg@bowt.ie>
2021-10-25 23:35 ` Andres Freund <andres@anarazel.de>
2021-10-26 00:38 ` Michael Lewis <mlewis@entrata.com>
2021-10-26 00:43 ` Andres Freund <andres@anarazel.de>
2021-10-27 21:44 ` Ashkil Dighin <ashkildighin76@gmail.com>
2021-10-27 22:22 ` Andres Freund <andres@anarazel.de>
2021-11-12 14:12 ` Ashkil Dighin <ashkildighin76@gmail.com>
2021-11-15 15:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-11-17 06:43 ` arjun shetty <arjunshetty955@gmail.com>
2021-11-29 12:39 ` arjun shetty <arjunshetty955@gmail.com>
2021-12-21 05:45 ` Yura Sokolov <y.sokolov@postgrespro.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox