[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: vacuum TOAST tables
From: senor <frio_cervesa () hotmail ! com>
Date: 2023-04-21 21:55:09
Message-ID: SN4P221MB0683C0DDE1302AF46B0AC949F7609 () SN4P221MB0683 ! NAMP221 ! PROD ! OUTLOOK ! COM
[Download RAW message or body]
Thank you Laurenz.
Current settings:
name | setting | unit | source
-------------------------------------+-----------+------+------------------=
--
autovacuum | on | | default
autovacuum_analyze_scale_factor | 0.1 | | default
autovacuum_analyze_threshold | 50 | | default
autovacuum_freeze_max_age | 200000000 | | default
autovacuum_max_workers | 15 | | configuration fil=
e
autovacuum_multixact_freeze_max_age | 400000000 | | default
autovacuum_naptime | 1 | s | configuration fil=
e
autovacuum_vacuum_cost_delay | 20 | ms | default
autovacuum_vacuum_cost_limit | 3000 | | configuration fil=
e
autovacuum_vacuum_scale_factor | 0.2 | | default
autovacuum_vacuum_threshold | 50 | | default
autovacuum_work_mem | -1 | kB | default
maintenance_work_mem | 2097152 | kB | configuration fil=
e
max_parallel_maintenance_workers | 2 | | default
vacuum_cleanup_index_scale_factor | 0.1 | | default
vacuum_cost_delay | 0 | ms | default
vacuum_cost_limit | 200 | | default
vacuum_cost_page_dirty | 20 | | default
vacuum_cost_page_hit | 1 | | default
vacuum_cost_page_miss | 10 | | default
vacuum_defer_cleanup_age | 0 | | default
vacuum_freeze_min_age | 50000000 | | default
vacuum_freeze_table_age | 150000000 | | default
vacuum_multixact_freeze_min_age | 5000000 | | default
vacuum_multixact_freeze_table_age | 150000000 | | default
I've compared 'vacuums per hour' with autovacuum_vacuum_cost_delay at 0 and=
default with no change.
My gut says there's an issue with the stats collection. Seems like autovacu=
um is not correctly updating the stats. As I mentioned in previous posts, I=
often see long-running active autovacuums in pg_stat_activity on small tab=
les and nothing in pg_stat_progress_vacuum. I found one reference (I can't =
find now) to a similar issue where the suspected problem was a huge and uni=
ntended number of tables. I eliminated over half and it did improve. I'm wo=
rking to convince the powers that be to upgrade to pg15 for the new stats. =
Not holding my breath.
I'll get back to the other thread when I get time to focus on it. For now m=
y question is answered and I'll modify my scripts
Your help is appreciated. If you have additional ideas, I'm all ears.
Thanks,
Senor
________________________________
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Thursday, April 20, 2023 11:39 PM
To: senor <frio_cervesa@hotmail.com>; pgsql-general@lists.postgresql.org <p=
gsql-general@lists.postgresql.org>
Subject: Re: vacuum TOAST tables
On Fri, 2023-04-21 at 04:37 +0000, senor wrote:
> PostgreSQL v11.19-1PGDG.rhel7 (upgrading to 12+ is so far only talk)
> CentOS 7.9
>
> If I understand correctly, autovacuum handles tables and their associated=
TOAST tables separately
> but a manual vacuum will also vacuum the TOAST.
That is correct.
> When manually vacuuming does it matter whether it's the main table or TOA=
ST?
It makes a difference. As superuser you can directly VACUUM a toast table,=
and that will
no VACUUM the table it belongs to. However, if you VACUUM the main table, =
both tables
will be vacuumed, which is more work. So if you only need VACUUM on the to=
ast table,
doing that directly will be cheaper.
> I've posted before about these same systems. It'll get to age(datfrozenxi=
d) > 2,000,000,000 and
> is not able to keep up until I get it back down to under ~600000000. Then=
it starts humming along
> as if I "cleared" something.
That sounds unsavory. Did you set "autovacuum_freeze_max_age" to an extrem=
ely high value?
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
[Attachment #3 (text/html)]
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} \
</style> </head>
<body dir="ltr">
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: \
rgb(0, 0, 0);" class="elementToProof"> Thank you Laurenz. </div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: \
rgb(0, 0, 0);" class="elementToProof"> <br>
</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: \
rgb(0, 0, 0);" class="elementToProof ContentPasted0"> Current settings:<br>
</div>
<div class="elementToProof"><span style="font-family: "Courier New", \
monospace;"> name \
| setting | unit | \
source</span></div> <div><span style="font-family: "Courier \
New", monospace; font-size: \
9pt;">-------------------------------------+-----------+------+--------------------</span></div>
<div><span style="font-family: "Courier New", monospace; font-size: \
9pt;"> autovacuum \
| on | | \
default</span></div> <div class="elementToProof"><span style="font-family: \
"Courier New", monospace; font-size: \
9pt;"> autovacuum_analyze_scale_factor | 0.1 \
| | default</span></div> <div><span style="font-family: \
"Courier New", monospace; font-size: \
9pt;"> autovacuum_analyze_threshold | 50 \
| | default</span></div> <div><span \
style="font-family: "Courier New", monospace; font-size: \
9pt;"> autovacuum_freeze_max_age | 200000000 \
| | default</span></div> <div><span style="font-family: \
"Courier New", monospace; font-size: 9pt;"> autovacuum_max_workers \
| 15 | \
| configuration file</span></div> <div><span style="font-family: \
"Courier New", monospace; font-size: \
9pt;"> autovacuum_multixact_freeze_max_age | 400000000 | | \
default</span></div> <div><span style="font-family: "Courier New", \
monospace; font-size: 9pt;"> autovacuum_naptime \
| 1 | s | \
configuration file</span></div> <div><span style="font-family: "Courier \
New", monospace; font-size: 9pt;"> autovacuum_vacuum_cost_delay \
| 20 | ms | \
default</span></div> <div><span style="font-family: "Courier New", \
monospace; font-size: 9pt;"> autovacuum_vacuum_cost_limit \
| 3000 | | configuration \
file</span></div> <div><span style="font-family: "Courier New", monospace; \
font-size: 9pt;"> autovacuum_vacuum_scale_factor | 0.2 \
| | default</span></div> <div><span \
style="font-family: "Courier New", monospace; font-size: \
9pt;"> autovacuum_vacuum_threshold | 50 \
| | default</span></div> <div><span \
style="font-family: "Courier New", monospace; font-size: \
9pt;"> autovacuum_work_mem \
| -1 | kB | default</span></div> <div><span \
style="font-family: "Courier New", monospace; font-size: \
9pt;"> maintenance_work_mem \
| 2097152 | kB | configuration file</span></div> <div \
class="elementToProof"><span style="font-family: "Courier New", monospace; \
font-size: 9pt;"> max_parallel_maintenance_workers | 2 \
| | default</span></div> <div><span \
style="font-family: "Courier New", monospace; font-size: \
9pt;"> vacuum_cleanup_index_scale_factor | 0.1 | \
| default</span></div> <div><span style="font-family: \
"Courier New", monospace; font-size: 9pt;"> vacuum_cost_delay \
| 0 \
| ms | default</span></div> <div><span style="font-family: \
"Courier New", monospace; font-size: 9pt;"> vacuum_cost_limit \
| 200 | \
| default</span></div> <div><span style="font-family: \
"Courier New", monospace; font-size: 9pt;"> vacuum_cost_page_dirty \
| 20 | \
| default</span></div> <div><span style="font-family: \
"Courier New", monospace; font-size: 9pt;"> vacuum_cost_page_hit \
| 1 \
| | default</span></div> <div><span style="font-family: \
"Courier New", monospace; font-size: 9pt;"> vacuum_cost_page_miss \
| 10 | \
| default</span></div> <div><span style="font-family: \
"Courier New", monospace; font-size: 9pt;"> vacuum_defer_cleanup_age \
| 0 | \
| default</span></div> <div><span style="font-family: "Courier \
New", monospace; font-size: 9pt;"> vacuum_freeze_min_age \
| 50000000 | | \
default</span></div> <div><span style="font-family: "Courier New", \
monospace; font-size: 9pt;"> vacuum_freeze_table_age \
| 150000000 | | default</span></div> <div><span \
style="font-family: "Courier New", monospace; font-size: \
9pt;"> vacuum_multixact_freeze_min_age | 5000000 | \
| default</span></div> <div class="elementToProof"><span \
style="font-family: "Courier New", monospace; font-size: \
9pt;"> vacuum_multixact_freeze_table_age | 150000000 | \
| default</span></div> <div style="font-family: Calibri, Helvetica, sans-serif; \
font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted0"> <div \
class="ContentPasted0"><br> </div>
</div>
<div style="" class="elementToProof"><font face="Calibri, Helvetica, sans-serif" \
style="color: rgb(0, 0, 0);"><span style="font-size: 12pt;">I've compared 'vacuums \
per hour' with </span></font><span style="font-family: "Courier New", \
monospace; font-size: 10pt; display: inline !important; color: rgb(0, 0, 0); \
background-color: rgb(255, 255, 255);" \
class="ContentPasted2">autovacuum_vacuum_cost_delay </span><span style="display: \
inline !important; font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; \
font-weight: 400;">at 0 and default with no change.</span></div> <div style="" \
class="elementToProof"><span style="display: inline !important; font-family: Calibri, \
Helvetica, sans-serif; font-size: 12pt; font-weight: 400;"><br> </span></div>
<div style="" class="elementToProof"><font face="Calibri, Helvetica, sans-serif">My \
gut says there's an issue with the stats collection. Seems like autovacuum is not \
correctly updating the stats. As I mentioned in pr</font><span style="font-family: \
Calibri, Helvetica, sans-serif; font-size: 12pt; font-weight: 400;">evious posts, I \
often see long-running active autovacuums in </span><span style="font-family: \
"Courier New", monospace; font-size: 10pt;">pg_stat_activity </span><span \
style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; font-weight: \
400;">on small tables and nothing </span><span style="font-family: Calibri, \
Helvetica, sans-serif; font-size: 12pt; font-weight: 400;">in </span><span \
style="font-family: "Courier New", monospace; font-size: 10pt; font-weight: \
400;">pg</span><span style="text-align: left; display: inline !important; \
font-family: "Courier New", monospace; font-size: 10pt; font-weight: \
400;">_</span><span style="font-family: "Courier New", monospace; \
font-size: 10pt; font-weight: 400;">stat</span><span style="text-align: left; \
font-family: "Courier New", monospace; font-size: 10pt; display: inline \
!important; color: rgb(0, 0, 0);">_progress_</span><span style="font-family: \
"Courier New", monospace; font-size: 10pt; font-weight: \
400;">vacuum</span><span style="font-family: Calibri, Helvetica, sans-serif; \
font-size: 12pt; font-weight: 400;">. I found one reference (I can't find now) to a \
similar issue where the suspected problem was a huge and unintended number of tables. \
I eliminated over half and it did improve. I'm working to convince the powers that be \
to upgrade to pg15 for the new stats. Not holding my breath.</span><font \
face="Calibri, Helvetica, sans-serif"></font></div> <div style="" \
class="elementToProof"><br> </div>
<div style="" class="elementToProof"><span style="font-family: Calibri, Helvetica, \
sans-serif; font-size: 12pt; font-weight: 400;">I'll get back to the other thread \
when I get time to focus on it. For now my question is answered and I'll modify my \
scripts</span></div> <div style="font-family: Calibri, Helvetica, sans-serif; \
font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted0 \
ContentPasted1"> Your help is appreciated. If you have additional ideas, I'm all \
ears.</div> <div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; \
color: rgb(0, 0, 0);" class="elementToProof ContentPasted0 ContentPasted1"> <br>
</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: \
rgb(0, 0, 0);" class="elementToProof ContentPasted0 ContentPasted1"> Thanks,</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: \
rgb(0, 0, 0);" class="elementToProof ContentPasted0 ContentPasted1"> Senor</div>
<div id="appendonsend"></div>
<hr style="display:inline-block;width:98%" tabindex="-1">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" \
style="font-size:11pt" color="#000000"><b>From:</b> Laurenz Albe \
<laurenz.albe@cybertec.at><br> <b>Sent:</b> Thursday, April 20, 2023 11:39 \
PM<br> <b>To:</b> senor <frio_cervesa@hotmail.com>; \
pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org><br> \
<b>Subject:</b> Re: vacuum TOAST tables</font> <div> </div>
</div>
<div class="BodyFragment"><font size="2"><span style="font-size:11pt;">
<div class="PlainText">On Fri, 2023-04-21 at 04:37 +0000, senor wrote:<br>
> PostgreSQL v11.19-1PGDG.rhel7 (upgrading to 12+ is so far only talk)<br>
> CentOS 7.9<br>
> <br>
> If I understand correctly, autovacuum handles tables and their associated TOAST \
tables separately<br> > but a manual vacuum will also vacuum the TOAST.<br>
<br>
That is correct.<br>
<br>
> When manually vacuuming does it matter whether it's the main table or TOAST?<br>
<br>
It makes a difference. As superuser you can directly VACUUM a toast table, and \
that will<br> no VACUUM the table it belongs to. However, if you VACUUM the \
main table, both tables<br> will be vacuumed, which is more work. So if you \
only need VACUUM on the toast table,<br> doing that directly will be cheaper.<br>
<br>
> I've posted before about these same systems. It'll get to age(datfrozenxid) \
> 2,000,000,000 and<br> > is not able to keep up until I get it back down to \
under ~600000000. Then it starts humming along<br> > as if I "cleared" \
something.<br> <br>
That sounds unsavory. Did you set "autovacuum_freeze_max_age" to an \
extremely high value?<br> <br>
Yours,<br>
Laurenz Albe<br>
-- <br>
Cybertec | <a href="https://www.cybertec-postgresql.com">https://www.cybertec-postgresql.com</a><br>
</div>
</span></font></div>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic