Auto Vacuum optimisation
HI hackers and japin li
There was an earlier discussion about auto vacuum optimisation(
/messages/by-id/6a2ac9b7-6535-4bb1-8274-0647f7c31c82@dalibo.com),I
referring to the discussion in there, I implemented a parameter selection
triggering the calculation of AUTO VACUUM,This is my first patch for the
community.Hoping for support from the experts
Attachments:
0001-Add-guc-to-optimize-the-autovacuum-tigger-algorithm.patchapplication/octet-stream; name=0001-Add-guc-to-optimize-the-autovacuum-tigger-algorithm.patchDownload
From fe09de086939c44de62fbc20acfd874c4389ba16 Mon Sep 17 00:00:00 2001
From: root <qiuwenhuifx@gmail.com>
Date: Mon, 18 Nov 2024 14:44:38 +0800
Subject: [PATCH] Add guc to optimize the autovacuum tigger algorithm
---
src/backend/postmaster/autovacuum.c | 34 ++++++++++++++++++++++-----
src/backend/utils/misc/guc_tables.c | 18 +++++++++++++-
src/backend/utils/misc/postgresql.conf.sample | 2 +-
src/include/postmaster/autovacuum.h | 11 +++++++++
4 files changed, 57 insertions(+), 8 deletions(-)
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index dc3cf87..ab4020e 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -109,7 +109,7 @@
#include "utils/syscache.h"
#include "utils/timeout.h"
#include "utils/timestamp.h"
-
+#include <math.h>
/*
* GUC parameters
@@ -131,7 +131,7 @@ double autovacuum_vac_cost_delay;
int autovacuum_vac_cost_limit;
int Log_autovacuum_min_duration = 600000;
-
+int autovacuum_algorithm = AUTOVACUUM_ALGORITHM_LINEAR;
/* the minimum allowed time between two awakenings of the launcher */
#define MIN_AUTOVAC_SLEEPTIME 100.0 /* milliseconds */
#define MAX_AUTOVAC_SLEEPTIME 300 /* seconds */
@@ -3038,11 +3038,33 @@ relation_needs_vacanalyze(Oid relid,
/* If the table hasn't yet been vacuumed, take reltuples as zero */
if (reltuples < 0)
reltuples = 0;
-
- vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
+ switch (autovacuum_algorithm)
+ {
+ case AUTOVACUUM_ALGORITHM_LOG2:
+ vacthresh = (float4) fmin(vac_base_thresh + (vac_scale_factor * reltuples), vac_base_thresh + vac_scale_factor * log2(reltuples) * 100000.0 );
+ anlthresh = (float4) fmin(anl_base_thresh + (anl_scale_factor * reltuples), anl_base_thresh + anl_scale_factor * log2(reltuples) * 100000.0 );
+ elog(DEBUG2, "Using log2 algorithm for vacuum, vacthresh values is %f,anlthresh values is %f" ,vacthresh, anlthresh );
+ break;
+ case AUTOVACUUM_ALGORITHM_SQRT:
+ vacthresh = (float4) fmin(vac_base_thresh + (vac_scale_factor * reltuples), vac_base_thresh + vac_scale_factor * sqrt(reltuples) * 1000.0 );
+ anlthresh = (float4) fmin(anl_base_thresh + (anl_scale_factor * reltuples), anl_base_thresh + anl_scale_factor * sqrt(reltuples) * 1000.0 );
+ elog(DEBUG2, "Using sqrt algorithm for vacuum, vacthresh values is %f,anlthresh values is %f" , vacthresh, anlthresh );
+ break;
+ case AUTOVACUUM_ALGORITHM_LINEAR:
+ vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
+ anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;
+ elog(DEBUG2, "Using linear algorithm for vacuum, vacthresh values is %f,anlthresh values is %f", vacthresh, anlthresh );
+ break;
+ case AUTOVACUUM_ALGORITHM_POW:
+ vacthresh = (float4) fmin(vac_base_thresh + (vac_scale_factor * reltuples), vac_base_thresh + vac_scale_factor * pow(reltuples, 0.7) * 100.0 );
+ anlthresh = (float4) fmin(anl_base_thresh + (anl_scale_factor * reltuples), anl_base_thresh + anl_scale_factor * pow(reltuples, 0.7) * 100.0 );
+ elog(DEBUG2, "Using pow algorithm for vacuum, vacthresh values is %f,anlthresh values is %f", vacthresh, anlthresh);
+ break;
+ default:
+ elog(ERROR, "Unknown vacuum_algorithm value.");
+ break;
+ }
vacinsthresh = (float4) vac_ins_base_thresh + vac_ins_scale_factor * reltuples;
- anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;
-
/*
* Note that we don't need to take special consideration for stat
* reset, because if that happens, the last vacuum and analyze counts
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 8a67f01..5ef9304 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -474,6 +474,14 @@ static const struct config_enum_entry wal_compression_options[] = {
{NULL, 0, false}
};
+/* vacuum_algorithm guc */
+static const struct config_enum_entry autovacuum_algorithm_options[] = {
+ {"log2", AUTOVACUUM_ALGORITHM_LOG2, false},
+ {"sqrt", AUTOVACUUM_ALGORITHM_SQRT, false},
+ {"linear", AUTOVACUUM_ALGORITHM_LINEAR, false},
+ {"pow", AUTOVACUUM_ALGORITHM_POW, false},
+ {NULL, 0, false}
+};
/*
* Options for enum values stored in other modules
*/
@@ -5206,7 +5214,15 @@ struct config_enum ConfigureNamesEnum[] =
DEBUG_LOGICAL_REP_STREAMING_BUFFERED, debug_logical_replication_streaming_options,
NULL, NULL, NULL
},
-
+ {
+ {"autovacuum_algorithm", PGC_SIGHUP, AUTOVACUUM,
+ gettext_noop("vacuum trigger algorithm."),
+ NULL
+ },
+ &autovacuum_algorithm,
+ AUTOVACUUM_ALGORITHM_LINEAR, autovacuum_algorithm_options,
+ NULL, NULL, NULL
+ },
/* End-of-list marker */
{
{NULL, 0, 0, NULL, NULL}, NULL, 0, NULL, NULL, NULL, NULL
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 39a3ac2..4c29fe9 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -684,7 +684,7 @@
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit
-
+#autovacuum_algorithm = linear # autovacuum_algorithm (log2,sqrt,linear,pow) default linear
#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h
index cae1e8b..6fe6732 100644
--- a/src/include/postmaster/autovacuum.h
+++ b/src/include/postmaster/autovacuum.h
@@ -47,6 +47,17 @@ extern PGDLLIMPORT int AutovacuumLauncherPid;
extern PGDLLIMPORT int Log_autovacuum_min_duration;
+/* algorithms for Vacuum */
+typedef enum Autovacuum_algorithm
+{
+ AUTOVACUUM_ALGORITHM_LOG2,
+ AUTOVACUUM_ALGORITHM_SQRT,
+ AUTOVACUUM_ALGORITHM_LINEAR,
+ AUTOVACUUM_ALGORITHM_POW
+} Autovacuum_algorithm;
+
+extern PGDLLIMPORT int autovacuum_algorithm;
+
/* Status inquiry functions */
extern bool AutoVacuumingActive(void);
--
1.8.3.1
HI hackers
These are algorithmic mathematical graphs,By default, vacuum will not be
triggered frequently.Looking at the images the algorithms for pow and sqrt
are close, do we want to cancel one of the algorithms?
log2
[image: image.png]
sqrt
[image: image.png]
pow
[image: image.png]
wenhui qiu <qiuwenhuifx@gmail.com> 于2024年11月18日周一 18:00写道:
Show quoted text
HI hackers and japin li
There was an earlier discussion about auto vacuum optimisation(/messages/by-id/6a2ac9b7-6535-4bb1-8274-0647f7c31c82@dalibo.com),I
referring to the discussion in there, I implemented a parameter selection
triggering the calculation of AUTO VACUUM,This is my first patch for the
community.Hoping for support from the experts
Attachments:
image.pngimage/png; name=image.pngDownload
�PNG
IHDR D �&[)
�iCCPICC Profile H���TS���{�-)�7�-��Z���JHB���"6dQ���"���"
��������uADA]6T�
�������s&�e��������������� Y�\I��7=>!��$�`�9�13<< 2e�.nHfoZ�r����*�<~ (�^7���~��%� � ~���b_GXU���S�M�'�L0�<�B� ���H� �B��<n��,��F��.@�#++��p��H�aY~F�y���3E���I��d/���������q�o�J��0A��. �D�
rfO3���,J
�bo"~���1S��a%Nq�0�=�<�O�<�04x�S~�A.;z��9�QS,�����*a1��#��A�#���������qS�'�
���4���%�Hy/|�����~�s����w[�67=:@~����"�t��xym<���tL�<^��-�K,�����rN^�|m.�rN�
��a'0|�d!�@��? r��re�����%���\:�m|:[���E���s @vw'_�w��; �.O�
� p7���� p�
����� ] .��J%y�>���@�
��)�v� �/�A� `��t��T������
`��`����t�3����^� ��A���`� D����A���< _(���d(
AR� Z�B�PT5@�B��3�%���C��[�����*�
��0f�Ap4<N���p�����p|���}�KxP$
���D1P,T*������JP�zT������C�B}Fc�T4m�vC�c�\��2t�
���>����G���c(-�����c�01��
��1�yL/f�����&Xgl 6��]�-�n�6cOc{��Q�������p\.��
w w
w7���'�u�vx?|"^�/�W���O�o����J#�+!��#,&�'�&�� cDe� ��M� �$V������H$�>��A�V�*I�HI���d�9�EN"K���{������(�1���H����4P�RS>)P��
<��
�
-
7^+�����+�(^S|�DP2Vb)q��)U+W��4�LU�US�R.S��|IyH�b����S)R��rVe���PYT.uu7�<uP�j��V�P-U=���:�������H�Z��Z
E3��iB�z�a�m���3�3�3��h�qc�G���^�|��f�^�/t
_�L����4�����5wh��|5Su��L�����g������"��h����5�����-���}V��M�K'Cg��I�a]����@w��)�t5:�.�W���G�����zuz�zc�&�1��������T���#���!������F�t��F]F�M���W�����M�MM�RL=M����2��1�2���]7��������Y�N��=�0�\f�f���cI�dZ�Y6Z�[����
�Z�^[Z'Zo����n�h#��m��V�6��������������=���~�}�������T����������$NMN������5�w��pF�����e�K��gW'�\����Y�e��w�m2�?{��w}w�{�{��#�c�G���'��������k��s�3�y�����[�}��#����u����S������[���O�/���o������ L@P���;lm6��� t\x.�T�$�<X���l
yj*
m
a��Ma��M��������xiY�E���?�C�w���1�1���X�������q>q�q}���K��$h&�q���{G����2g0�1�8��\����^��9O8��|����G�1�q����r�8���vJM�����}���m�
�����������Ci�i����=�+�_ X�*���������a�{3��q��,|Vr�q��(St.['{Qv��B\,�[��`��I�dO�37�-W��JM�?I��<���>-�]xd��"������]�<�/��%�%�%�z+��2��-���,�\n��h��
��VWf�����������U�E�E+�~����X�XR|g����5�5�5�k��n[���Wr������k�����?W�<�.u]�z��;6`7�6����q_�ry~����M-���K6��2��
��������}���m��m���*�������F�fm�����7vx�h���-���S��n�]K�q}�.���]�v������K��=�{�������\�sC�~����Fi������lk�l�k�5����^������A�;�0�45:Zs�z��jY�2��������s<�xg�[����~����Q}B������E'�O��=->��L����������u.�\����/�]8���:u��b�%�K�/3.�^q��r�����?����r��Z�u���=�{N���q���������������}�N������C�������?�`�C���GJ�*k=������>���>�W�D=y0�x�4�����g�g�u�7�
u�
_1���K���W�*�Y���������:?2�F�f�m�;�w{�;��
}�!�����O��}f|�����������������p<k|\��p&F�pj* o�@I �����9����@���O���@bdc�4 �5F���l$�����\�����]&X��e���z�c����<�C���@������"
��u� 8eXIfMM * �i � � D 7G @ IDATx���E���'n��,�,9�(J�SP�3'T<0�3���'g�zxw�Fg�(������3l�a��������������o������_������}�v���n�O����O���z<Z��H6-��
Ivt���3�*�+���f(�] hB�g��%R`���RVV�$/���TUM���{���[e����:�������;wJ��k���;w�V�\YYY)���w����-[����G�!I111��oC���Q����o���"?~�9!!��r�j� ��#PTT�v�Z�d�X�9��
M����6�
����RSS�V��cl��y��}"��pu�Q2��/ #b��#�8aALfD�
�V�����@/@�������n���]X�|�l6*y��&�rc���4[d��v�Q
����1~q�k�(���7��~�z�G��a J
�����&"�%BW�j0�he%%%"��Y�&�HFF�D�w����j�2)�c���6H�� -::Zh����yyy���]���o�FJ��6�m�&3C[�4�+��=z���4����4$%&&�/2>�/B������$�����E*x"���B�ZH��+6�?==]/A��B��8����M��i���bd��2�:0��c64.66Z;6�Q���D���� � � � � � � 4|J��,�X|@ ��PQ�����o�� �)6�*Ci�(����i�Z|*rb�A��jl�Q���A5���oM����QR�"��w`���v�a � � � � � � 4 ���?��������A�PQ��[���U;v���-����B������Zk��g����B�;c�����k�������R�����:4����B�d��?�k��� � � � � � ���# �3�5e�j�1����7l�0j�(�c���#����#�h2t[�[��B������BL:t(����6�A`��s����I�H�H�H�H�H�H ��f�Z�
W�^m�����1�W�=��� �X�����Bk���ee����2d���.ZD���h �VF�n�A���_
���d��B+F3k�����6�>��>7_YYY���]�x92���/�C$@$@$@$@$@$�������A�o�� ���Oj�B�=��ZQ5�ZH�Da�Kc|X��s��Ti>����� �VI ��5�j(���{�����y�h/�_XQ�_��W%�>Z�?{��n�0>�*��F� � � � � � �@X����t��(/(,�)�����u����`�0[���
r{<a�g& h�<n�����.^��P~���j�
��*1�.((�?-h��;-s0@$@$@$@$@$@$@m� ���;x������B����j/����������/���� � � � � � �@{ �7&&:9)���L�%�����b��`qIII�^����C�� �x}}��...���Wj�p��D��ccc�v�L 5 h`>m���t�o��b���?h�����N;j]�$��q�� �� t���������6X�Q��(qFF�">�H�H�H�H�H�H�� h��2�����~eX�������
���������x=���a�YLY�>�����buY,n�5��`S�E�X��Re����[��4[p����X=���Dy�W���a�&�|���z\����rY���Q�^� �
��V������6���6����aYP� h9�yy�,���>��#T�A��H�H�H�=�Z,X��US�����lP�������A���.���l��������U��k�ySRR3:wN������t�aq8�6�b��4���a�RQY��8wUe�����5���~��3��;��u�%���]j�K��4�J}��Z��p��y�/��5�����;11�sj��i�=3{�L��V�����8�>[c��, � �@K(**���[�7�t�v�i�'O�N���Y � � 4+�������1�bu��e}��i�(�����z�6��y�����w����$�%��BO�xt��ST]]�r�1*n�(�w_���j�6\��1[����pi�������X�!�4)�<��rX�T����T���*�����=4)k�%��OI�Q�#�
WP��Xk6�nY�u���K��Jk�<�(����J�t_��W��WYG�>����)}�j�c������zh)�H�H����o�QYUe�I'�xR��=g������vS�����M���m{�;v������kD4��?��K��m�l1 � � DB����7�/-/OOO�p�,j��#n�}x��s����cU����E�{<vXI��B�������^o��+����:n�r�X���*��vC7���[���}N�����+mj�U�����������8��XKj������+��c��� N%KQ3k���V�@�����kg�������D)U���������V�a�%��on���V�4�����Q��R��n ���I�H�$�����������~������o�~2��p� �����f����a$\vj��a�]�r<\a�H�H�����.,*B���4����m?�Nc���������?"=%�i�����3kb���)6��v�z{���
����K�������H���������}������K�X|�
VX�4f9EY�����*�eS�)1��������s�S�c���?�8�z1>���\5��(���b[�K��~_�� hj�\i��Z3���o-|��Uyz��6mR�����GT/3� � 4
���6=z����+�����S�s�9����������/�^��@U{r��7 h�������������Zk�3V$��������a�'������v�K�f|���/v\+Ph�~�,���'+k�����VN��v�[�[#����������us�[�X|��U�ZuOrT��������3'm���z���M[��T���-���}��w{^\q����uc.���|��^'�Y��kq��r��Y��e='�dx��w%8��s# 8�RSS��R�-�11�~>����v�����N�ZQY�nz��� � �@��6����>��r���p8���}{6m\=�����^wT�>�W
�����q�_��b��1s��o2�����V��b��X��
��Ws����{7.��k��x1>������.�&������xU��W����g�g+�N��I��T7�[W0��� ����f����3�06���[�j4�Z��>�0N���: ��&tO�������������u�{{p����X �f!p���c(�(���P^^�i�&��������-��s���[�n��(�����o��������;w�l��;vB��� �u�LX���yD�������u����z��v��*V�^
!XDq������E����|�����6t�PT��4���UR���+�������d$ � � �WP~�kJ�P��N�5�4�C5�����K:T�w����K1� ���-���TY� >���j�{�������t������ ��/����0�ti��LM�Gc����b���G�v���-j"�e:X|�=�*��7��+*�4���R
E�_���V��z�p��O�a:u�Z���������1�t��
xsK$@$�4B��7o��������
x�}�����~j�_a��cu�E!����"����aw�����^�u ����r��X�������1c���ZdZ�n���<#�:t��Q�~�e<���S���G#$����g���};������G��?_g�Xe-2���g�q�� ���^��8�% h���+�a�kc�>��[gBN�1��w����w��Jt�����A�?sJ\�����M����h��al��)-��w����J�/O��9��P� d�+� ���;V*����TC`��
��+o���W���{Y�*��`m���|!�E8:)v��ek+7��+]�S/�� � B����=a\n���?�����U�/Y�T6��l8�['G������T�����^{M��R2%%%�?��AF<�u��?�_/�(@�R�0��L�2���6k��_�d�H�H���V���"��%���w /���>�n��2��V��}�EjS�
����x�C�}l�U������(�qq���x�]-S}�\�p�y���Z�M��y��XRJ�X+I�y��_�Pz��M5��J��Ru�V�d�����E�,�@{)����_�U�D[6��2���U�*��J'[�� �
���v����!����v�:}�����2��5x������� c����O>���=�I�}�����W�Z�_�_�`,�/��R1�l�����;������W
n$@$@$p������������a���~�P�������)�������>-�plt�k�;&!{=������w�G�?U��s�~�7���h�0��(�Q������2�ZaQb�;D��X`iG�_,�[�q����k��C��3�S����-��#oWe��jku4Wc
�� $@$�f�����b��{N������3�f���eL�.]n��V�3���z�O?���
@q�������-���b.\ �����{�1�L*�����r���|��7g�}vbb��
�0?9 �$@$@$p��C���X<j��WrF�c�n���~����a
�J�uc�6�m�����r��mQ�r�;���=�imn�V/�#�r�VVc�'�Z4�\�s^a�g��aN{T���J:�V��&Y�o���Z����)+QN�����JM��Eq�� � .�w������u��{�n1�0L�������������t���w�]�ss�lp�}����WoQ<�?��h�")m���R���0�U���T�|�X�b���2'$@$@$@���4���\$2�aq~^T�tKXSq
��w}>����kw�Z��>e��^[]Yb�z���S���\-�R��`�m�UU����qf, ,�M>��_Z�t��:���a�VQq�[�x,X�)��
a � ���"