[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: vacuum freeze wait_event BufferPin
From: abrahim abrahao <a_abrahao () yahoo ! com ! br>
Date: 2024-01-31 19:05:19
Message-ID: 1059371874.2807306.1706727919318 () mail ! yahoo ! com
[Download RAW message or body]
There is a three-day vacuum freeze on a partition table with wait_event = BufferPin, \
no transactions (active or idle) older than it that are not blocked, but at least one \
select query is executing at all times related of this partition table. This \
behaviour happens with some partitions, but not always. Running simply vacuum works \
because it does not wait for BufferPin. I just expect to see vacuum freeze wait_event \
= BufferPin if there is any old transaction related to the table, am I wrong? Is \
there a wait to figure out which session vacuum freeze to wait for? Below is a list \
of all sessions at a particular moment in time; at this point, there are only Select \
queries with query starts older than the vacuum freeze, and no cursor, transaction \
block, or zombie sessions.
SELECT pg.query_start, now() - query_start AS duration, pg.pid pg_pid, \
pg.state, pg.wait_event, backend_type ,pg.wait_event_type, pg_blocking_pids(pid) \
as blocked_by,SPLIT_PART(trim(pg.query),' ', 1)FROM pg_stat_activity pg order by \
now() - query_start desc; query_start \
| duration | pg_pid | state | wait_event \
| backend_type | wait_event_type | blocked_by | \
split_part----------------------------------------------+----------------------------+ \
-----------+--------+------------------------------+--------------------------------------+-----------------------+------------+------------ \
| | 249 | \
| AutoVacuumMain | autovacuum launcher | Activity \
| {} | \
| | 252 | \
| LogicalLauncherMain | logical replication launcher | Activity \
| {} | \
| | 247 | \
| BgWriterMain | background writer | \
Activity | {} | \
| | 250 | \
| ArchiverMain | archiver \
| Activity | {} | \
| | 246 | \
| CheckpointWriteDelay | checkpointer | Timeout \
| {} | \
| | 248 | \
| WalWriterMain | walwriter \
| Activity | {} | 2024-01-27 \
23:10:54.159577+00 | 3 days 18:55:48. | 271039 | active| BufferPin \
| client backend | BufferPin | {} \
| vacuum 2024-01-30 21:33:50.262265+00 | 20:32:52.490309 | 558810 | idle \
| ClientRead | client backend \
| Client | {} | SELECT 2024-01-31 \
16:53:17.695929+00 | 01:13:25.056645 | 641053 | idle | ClientRead \
| client backend | Client \
| {} | SELECT 2024-01-31 18:04:48.929097+00 | 00:01:53.823477 \
| 645812 | active | | client \
backend | | \
{} | SELECT 2024-01-31 18:05:02.221272+00 | 00:01:40.531302 \
| 645814 | active | | client \
backend | \
| {} | SELECT 2024-01-31 18:05:04.927131+00 | 00:01:37.825443 \
| 645925 | active | | client \
backend | \
| {} | SELECT 2024-01-31 18:05:12.088875+00 | 00:01:30.663699 \
| 645109 | active | | client \
backend | \
| {} | SELECT 2024-01-31 18:05:30.202305+00 | 00:01:12.550269 \
| 645695 | active | | client \
backend | \
| {} | SELECT 2024-01-31 18:05:47.872601+00 | 00:00:54.879973 \
| 645924 | active | | client \
backend | \
| {} | SELECT 2024-01-31 18:06:16.425286+00 | 00:00:26.327288 \
| 645593 | active | | client \
backend | \
| {} | SELECT 2024-01-31 18:06:18.286905+00 | 00:00:24.465669 \
| 646174 | idle | ClientRead | client backend \
| Client | {} | select 2024-01-31 \
18:06:24.878488+00 | 00:00:17.874086 | 644993 | active | \
| client backend | \
| {} | SELECT 2024-01-31 18:06:32.560233+00 | 00:00:10.192341 \
| 645590 | active | | client \
backend | \
| {} | SELECT 2024-01-31 18:06:34.068919+00 | 00:00:08.683655 \
| 646175 | idle | ClientRead | client backend \
| Client | {} | select 2024-01-31 \
18:06:36.159063+00 | 00:00:06.593511 | 645926 | active | \
| client backend | \
| {} | SELECT 2024-01-31 18:06:40.002643+00 | 00:00:02.749931 \
| 351 | idle | ClientRead | client backend \
| Client | {} | SELECT 2024-01-31 \
18:06:42.5886+00 | 00:00:00.163974 | 645212 | active | \
| client backend | \
| {} | WITH 2024-01-31 18:06:42.717906+00 | 00:00:00.034668 \
| 645207 | idle | ClientRead | client backend \
| Client | {} | SELECT 2024-01-31 \
18:06:42.721052+00 | 00:00:00.031522 | 645817 | idle | \
| client backend | \
| {} | SELECT 2024-01-31 18:06:42.723673+00 | 00:00:00.028901 \
| 644916 | idle | ClientRead | client backend \
| Client | {} | SELECT 2024-01-31 \
18:06:42.725967+00 | 00:00:00.026607 | 645815 | idle | ClientRead \
| client backend | Client \
| {} | (SELECT 2024-01-31 18:06:42.732666+00 | 00:00:00.019908 \
| 644752 | idle | ClientRead | client backend \
| Client | {} | SELECT 2024-01-31 \
18:06:42.733953+00 | 00:00:00.018621 | 645697 | idle | ClientRead \
| client backend | Client \
| {} | SELECT 2024-01-31 18:06:42.735012+00 | 00:00:00.017562 \
| 645193 | idle | ClientRead | client backend \
| Client | {} | SELECT 2024-01-31 \
18:06:42.735088+00 | 00:00:00.017486 | 645927 | idle | ClientRead \
| client backend | Client \
| {} | SELECT 2024-01-31 18:06:42.736222+00 | 00:00:00.016352 \
| 645221 | idle | ClientRead | client backend \
| Client | {} | SELECT 2024-01-31 \
18:06:42.741013+00 | 00:00:00.011561 | 645813 | active | \
| client backend | \
| {} | SELECT 2024-01-31 18:06:42.745033+00 | 00:00:00.007541 \
| 645238 | idle | ClientRead | client backend \
| Client | {} | (SELECT 2024-01-31 \
18:06:42.747161+00 | 00:00:00.005413 | 645596 | idle | ClientRead \
| client backend | Client \
| {} | SELECT 2024-01-31 18:06:42.747703+00 | 00:00:00.004871 \
| 645232 | idle | ClientRead | client backend \
| Client | {} | SELECT 2024-01-31 \
18:06:42.749182+00 | 00:00:00.003392 | 645820 | active | \
| client backend | \
| {} | SELECT 2024-01-31 18:06:42.750896+00 | 00:00:00.001678 \
| 645594 | idle | ClientRead | client backend \
| Client | {} | SELECT 2024-01-31 \
18:06:42.752015+00 | 00:00:00.000559 | 645620 | idle | ClientRead \
| client backend | Client \
| {} | SELECT 2024-01-31 18:06:42.752574+00 | 00:00:00 \
| 642199 | active | | client \
backend | \
| {} | SELECT(40 rows) SELECT * FROM pgstattuple('pg_catalog.pg_proc'); \
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | \
dead_tuple_len | dead_tuple_percent | free_space | \
free_percent-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+-------------- \
851968 | 3450 | 799839 | 93.88 | \
26 | 11602 | 1.36 | 10960 | \
1.29
SELECT * FROM pgstattuple('[table_name]');
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | \
dead_tuple_len | dead_tuple_percent | free_space | \
free_percent-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+-------------- \
922165248 | 11679432 | 805880808 | 87.39 | 0 \
| 0 | 0 | 31376484 | \
3.4
SELECT * FROM pgstatindex('[index_name_01]'); version | tree_level | index_size | \
root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | \
avg_leaf_density | leaf_fragmentation---------+------------+------------+------------- \
--+----------------+------------+-------------+---------------+------------------+-------------------- \
4 | 2 | 262348800 | 412 | 113 | \
31911 | 0 | 0 | 90.09 | \
0 SELECT * FROM pgstatindex('[index_name_02]'); version | tree_level | index_size | \
root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | \
avg_leaf_density | leaf_fragmentation---------+------------+------------+------------- \
--+----------------+------------+-------------+---------------+------------------+-------------------- \
4 | 2 | 381714432 | 351 | 226 | \
46369 | 0 | 0 | 86.9 | \
5.67
[Attachment #3 (text/html)]
<html><head></head><body><div class="ydp854ec29ayahoo-style-wrap" style="font-family: \
Helvetica Neue, Helvetica, Arial, sans-serif; font-size: 16px;"><div style=""><div \
dir="ltr" data-setdir="false" style=""> <div><div>There is a three-day vacuum freeze \
on a partition table with wait_event = BufferPin, no transactions (active or idle) \
older than it that are not blocked, but at least one select query is executing at all \
times related of this partition table. </div><div><br></div></div> \
<div><div>This behaviour happens with some partitions, but not always. Running simply \
vacuum works because it does not wait for BufferPin.</div></div></div><div dir="ltr" \
data-setdir="false" style=""> <div><div>I just expect to see vacuum freeze wait_event \
= BufferPin if there is any old transaction related to the table, am I \
wrong?</div></div> <div><div>Is there a wait to figure out which session vacuum \
freeze to wait for?</div><div><br></div></div><div dir="ltr" data-setdir="false"> \
<span>Below is a list of all sessions at a particular moment in time; at this point, \
there are only Select queries with query starts older than the vacuum freeze, and no \
cursor, transaction block, or zombie sessions.</span><br></div></div><div dir="ltr" \
data-setdir="false" style=""><span><span><span><span><br></span></span></span></span></div></div><div \
dir="ltr" data-setdir="false" style="font-family: Helvetica Neue, Helvetica, Arial, \
sans-serif; font-size: 16px;"><br></div><div dir="ltr" data-setdir="false" \
style="font-family: Helvetica Neue, Helvetica, Arial, sans-serif; font-size: 16px;"> \
<div><div dir="ltr" data-setdir="false"> <div><div>SELECT pg.query_start, now() \
- query_start AS duration, pg.pid pg_pid, pg.state, pg.wait_event, \
backend_type ,</div><div>pg.wait_event_type, pg_blocking_pids(pid) as \
blocked_by,SPLIT_PART(trim(pg.query),' ', 1)</div><div>FROM pg_stat_activity pg order \
by now() - query_start desc;</div><div> \
query_start \
| duration \
| pg_pid | state | \
wait_event | \
backend_type \
| wait_event_type | blocked_by | split_part</div><div dir="ltr" \
data-setdir="false">-----<span><span style="color: rgb(0, 0, 0); font-family: \
Helvetica Neue, Helvetica, Arial, sans-serif; font-size: \
16px;">---------------</span></span>--------------------------+-------<span><span \
style="color: rgb(0, 0, 0); font-family: Helvetica Neue, Helvetica, Arial, \
sans-serif; font-size: \
16px;">----</span></span>-----------------+-----------+--------+-------<span><span \
style="color: rgb(0, 0, 0); font-family: Helvetica Neue, Helvetica, Arial, \
sans-serif; font-size: \
16px;">-------</span></span>----------------+--------------------------------------+-----------------------+------------+------------</div><div \
dir="ltr" data-setdir="false"><span><span style="color: rgb(0, 0, 0); font-family: \
Helvetica Neue, Helvetica, Arial, sans-serif; font-size: 16px;"> \
\
</span></span> \
| \
\
| 249 | | \
AutoVacuumMain | autovacuum launcher \
| Activity \
| {} |</div><div> \
\
| \
\
| 252 | \
| LogicalLauncherMain | logical replication \
launcher | Activity \
| {} |</div><div dir="ltr" \
data-setdir="false"> <span><span style="color: rgb(0, 0, 0); font-family: Helvetica \
Neue, Helvetica, Arial, sans-serif; font-size: 16px;"> \
\
\
</span></span>| \
| \
247 | | BgWriterMain \
| background writer \
| Activity \
| {} |</div><div \
dir="ltr" data-setdir="false"> <span><span style="color: rgb(0, 0, 0); font-family: \
Helvetica Neue, Helvetica, Arial, sans-serif; font-size: 16px;"> \
\
\
</span></span>| \
| \
250 | | ArchiverMain \
| archiver \
\
| Activity | {} \
|</div><div dir="ltr" data-setdir="false"> <span><span \
style="color: rgb(0, 0, 0); font-family: Helvetica Neue, Helvetica, Arial, \
sans-serif; font-size: 16px;"> \
\
</span></span>| \
\
| 246 | \
| CheckpointWriteDelay | checkpointer \
| Timeout \
| {} \
|</div><div dir="ltr" data-setdir="false"> <span><span style="color: rgb(0, 0, \
0); font-family: Helvetica Neue, Helvetica, Arial, sans-serif; font-size: \
16px;"> \
\
</span></span>| \
\
| 248 | \
| WalWriterMain | \
walwriter \
| Activity \
| {} \
|</div><div> 2024-01-27 23:10:54.159577+00 | 3 days 18:55:48. \
| 271039 | active| BufferPin \
| client backend \
| BufferPin \
| {} | \
vacuum</div><div> 2024-01-30 21:33:50.262265+00 | 20:32:52.490309 \
| 558810 | idle | ClientRead \
| client backend \
| \
Client \
| {} | SELECT</div><div> 2024-01-31 \
16:53:17.695929+00 | 01:13:25.056645 | 641053 | idle \
| ClientRead \
| client backend \
| Client \
| {} \
| SELECT</div><div dir="ltr" data-setdir="false"> 2024-01-31 \
18:04:48.929097+00 | 00:01:53.823477 | 645812 | active \
| \
| client backend \
| \
| {} \
| SELECT</div><div> 2024-01-31 18:05:02.221272+00 | \
00:01:40.531302 | 645814 | active | \
<span><span style="color: \
rgb(0, 0, 0); font-family: Helvetica Neue, Helvetica, Arial, sans-serif; font-size: \
16px;"> </span></span>| client \
backend \
| \
| {} \
| SELECT</div><div> 2024-01-31 18:05:04.927131+00 | 00:01:37.825443 \
| 645925 | active | \
<span><span style="color: rgb(0, 0, 0); \
font-family: Helvetica Neue, Helvetica, Arial, sans-serif; font-size: 16px;"> \
</span></span>| client backend \
| \
\
| {} | SELECT</div><div \
dir="ltr" data-setdir="false"> 2024-01-31 18:05:12.088875+00 | \
00:01:30.663699 | 645109 | active | \
<span><span style="color: rgb(0, 0, 0); \
font-family: Helvetica Neue, Helvetica, Arial, sans-serif; font-size: 16px;"> \
</span></span> | \
client backend \
| \
| \
{} | SELECT</div><div dir="ltr" \
data-setdir="false"> 2024-01-31 18:05:30.202305+00 | 00:01:12.550269 \
| 645695 | active | \
<span><span style="color: rgb(0, 0, 0); \
font-family: Helvetica Neue, Helvetica, Arial, sans-serif; font-size: 16px;"> \
</span></span> | client backend \
| \
\
\
| {} | SELECT</div><div dir="ltr" \
data-setdir="false"> 2024-01-31 18:05:47.872601+00 | 00:00:54.879973 \
| 645924 | active | \
<span><span style="color: rgb(0, 0, 0); \
font-family: Helvetica Neue, Helvetica, Arial, sans-serif; font-size: 16px;"> \
</span></span> | client backend \
| \
\
| {} | \
SELECT</div><div> 2024-01-31 18:06:16.425286+00 | 00:00:26.327288 \
| 645593 | active | \
<span><span style="color: rgb(0, 0, 0); font-family: Helvetica \
Neue, Helvetica, Arial, sans-serif; font-size: 16px;"> \
</span></span> | client backend \
| \
\
| {} | \
SELECT</div><div> 2024-01-31 18:06:18.286905+00 | 00:00:24.465669 \
| 646174 | idle | ClientRead \
| client backend \
| Client \
| {} \
| select </div><div> 2024-01-31 \
18:06:24.878488+00 | 00:00:17.874086 | 644993 | active \
| \
| client backend \
| \
\
| {} | SELECT<br></div><div> 2024-01-31 \
18:06:32.560233+00 | 00:00:10.192341 | 645590 | active \
| \
| client backend \
| \
\
| {} | SELECT</div><div> 2024-01-31 \
18:06:34.068919+00 | 00:00:08.683655 | 646175 | idle \
| ClientRead \
| client backend \
| Client \
| {} \
| select</div><div> 2024-01-31 18:06:36.159063+00 | \
00:00:06.593511 | 645926 | active | \
\
| client backend \
| \
| {} \
| SELECT</div><div> 2024-01-31 18:06:40.002643+00 | \
00:00:02.749931 | 351 | \
idle | ClientRead \
| client backend \
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic