Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q029M-0003Ag-JP for pgsql-admin@arkaria.postgresql.org; Fri, 19 May 2023 15:36:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q029L-00042F-Ci for pgsql-admin@arkaria.postgresql.org; Fri, 19 May 2023 15:36:11 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q029K-000425-Gt for pgsql-admin@lists.postgresql.org; Fri, 19 May 2023 15:36:11 +0000 Received: from mail-bl0gcc02on20726.outbound.protection.outlook.com ([2a01:111:f400:7d05::726] helo=GCC02-BL0-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1q029D-000hAy-VO for pgsql-admin@lists.postgresql.org; Fri, 19 May 2023 15:36:09 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=OYCBXP7F1ryXJV/dreDStSWwSZr1J0duNS+WFca1G4xGSR994m+dbkjEhR0RXQ6tX39IyHnUndod3ly2QfoTlvpk44tgkQP5tekARHfMFxAsRDBsYKNdNoARLxaSt0WvdCJUllxHLjB7I4uT5kfHW7xnMusqveIhrC9+Ph0kIRm+xA7c8It/7QcdrnlHxlN1Mlc6QxybsBMepH/lixnXxSULMGNVMPa7cccjeFwAVoWNhkMXiD1kv9spPxXNWpGaYUxZreFCVMX4ic/b680k2TeRglpJp6udH81JcAuUqPy00So/ma9TRo3/PY07gYIkl0efFFVwJpYw7d6NQwyUVA== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=o5CiDcJVmrw1BIeF/la5sKK7eYB2C+q+0tk84o0mkO0=; b=UtRO3eHltXrH+8adyh6vNQh2Uc5tFN5m45PWd0KyoP3Gib0wtj4mAF4A6JwPYnWI2piefN7Rn9sXO8Z+4Vzg26QUzujB+7QEptyS2qd73GK5jxMTECpDXpU8COVtYcO4EZ7U45TVjMmGGrL/BRXleILAxHhmAijkfJNmwJB5hnkToFwKwEBGOgnn+CYJ5H21s6n+8iVb/S92EQG4+EyJKXXIc8ZHzl907klfNadUsOzGHyJ3Y7EfVIZy+INz60zuFvwRQr+YPhBdAUaybHcSUo3zmNAxEZDUE2qW/677JW3lZmOVr81y+BPX4IjywthFN0ol44yq0mikr9Zo5b+Kiw== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=fnal.gov; dmarc=pass action=none header.from=fnal.gov; dkim=pass header.d=fnal.gov; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=fnal.gov; s=selector2; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=o5CiDcJVmrw1BIeF/la5sKK7eYB2C+q+0tk84o0mkO0=; b=bki+k2RltJwHvR0tpml/KrYFmMzOAP+X/SFaEJSL8mCVzPg5b4buW2BtuUPz7C01RhdJNNB/btI/tapw9USmFchQvy5q/1VTbYDrRfHYI2q3hvEugI/vYu3AObYRToXGKBmb0hDnuQfhVf2kGqhLWETLSjJ4Bap0EZRk35d7IQew1InB1dAz+Gqq9Xv88zukbgM18ZJ3/OceTWJAI7uPJybbtYoh6GtBNQNqk67yhW4sm4zRLp1y2DWW6BKBHKKmXjMxE+Q3HOx1ZFhOncN3KIQgG36UNvgDV1Zz0Wc7wYNsFqRTaC4/LOqCVP5EPjwKRrN3SDB5E5PPlU9CkzUWeg== Received: from DM8PR09MB6677.namprd09.prod.outlook.com (2603:10b6:5:2ee::12) by PH8PR09MB9088.namprd09.prod.outlook.com (2603:10b6:510:17e::12) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.6411.22; Fri, 19 May 2023 15:35:57 +0000 Received: from DM8PR09MB6677.namprd09.prod.outlook.com ([fe80::727a:bf0f:a6bb:80ba]) by DM8PR09MB6677.namprd09.prod.outlook.com ([fe80::727a:bf0f:a6bb:80ba%7]) with mapi id 15.20.6411.021; Fri, 19 May 2023 15:35:57 +0000 From: Murthy Nunna To: "pgsql-admin@lists.postgresql.org" Subject: Vacuum Tuning Question Thread-Topic: Vacuum Tuning Question Thread-Index: AdmKZIU2HzQwXKyXSSWspV4ZhqrdWA== Date: Fri, 19 May 2023 15:35:57 +0000 Message-ID: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: dkim=none (message not signed) header.d=none;dmarc=none action=none header.from=fnal.gov; x-ms-publictraffictype: Email x-ms-traffictypediagnostic: DM8PR09MB6677:EE_|PH8PR09MB9088:EE_ x-ms-office365-filtering-correlation-id: 76abd9dc-f055-4136-8ca7-08db587ebdc4 x-fermilab-sender-location: inside x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0; x-microsoft-antispam-message-info: RpI+Ju0si+c36ENaRe65+IdNWu9PJkLOk5XHqOLVwux11+TIc/27NiKV17XK7G7t4L4x/e+qrBTQ/2vQ3QnRHtWyZ7LmJbbbvtHSInX/q8fkxE8kp5pMiDBVTJeDg7N2ahXUyBf2Vp/WItmA6GAYEZl1aVD4Lry4ys8kvq9JuB9ZH4+QgKcoeHC0ITFsFefUfQYhCCoBRvg8sggsksr8Eiz5LS3i1Mo3Jv/uWiC0YKOXl4P8Yc+Rh+ao0P1p0XVuqzsW4bOejlN35su0CFvegqj1TP+SUMyVTbjIdzf4nkpr7Q65YTyw9+cmbzwCi1tpprOIGtXvHcZeXrCJ2UZuErbZ7kRbMYJDPzgXOYmyCsk7ItLUb46Jykpcw8qb+tz92Ox5JnCemz0UTsUjnaDyK2vauUElqe3Rjssvw6VjIitVrmkEA3SyBBuAPHK1JntwyGu7CpDemWqeDTjBqBzK6o4m3sHpYaloT6Y+L/l2+I24in0y+rR6Q7wIMouOxAWwLazEgNQ3bZZNmADl0lu8cVJPKSOnpZ4mWHUZCpuq7QDGgFpZ0KUGiS0q1ze9ZmEBPVcYd8LC3L4CHCBjNYaX0Jwj30RyDWFEufCXhWkRrqNxqSVb9qfpVwL+iAZcaUKc x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:DM8PR09MB6677.namprd09.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230028)(366004)(451199021)(55016003)(66556008)(76116006)(82960400001)(66946007)(6916009)(66476007)(64756008)(186003)(508600001)(8676002)(8936002)(7116003)(9326002)(5660300002)(52536014)(66446008)(122000001)(38100700002)(83380400001)(7696005)(71200400001)(3480700007)(26005)(9686003)(6506007)(86362001)(33656002)(2906002)(38070700005);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?us-ascii?Q?YTORHTaf5hDvCzcUCq+auuPHSetIw0f7VodzK0rEy59U4XE6u/Py1P1JZ6Nh?= =?us-ascii?Q?EW1V5mcWdIUSXWCXwoYLAz5PvngrfwlsZUOZ925dpHO1UtMDfOc9YQqjIqYz?= =?us-ascii?Q?b5dRv2odpLXxb3X5T8P8JYlfTb/iPMuEGUzp8ziK710a+xFz//qBip8BgouF?= =?us-ascii?Q?ByofLG3D12cWvnJYfe4po4WryFVPeXsEDsHL7OiRgGThrQ0Qb6fHaRRKkx1t?= =?us-ascii?Q?cdBYvkgEik9WaPGIlQJ12cXthg0NfQHDwShfmCMOHh4JhXTII8G9TZZckzZi?= =?us-ascii?Q?lKJAeicVYGGW6YTVqIZzBVSktingLT9WUQhzC6S01hcrUXvIcP1GR0Q3KEC0?= =?us-ascii?Q?8gPnWoJT1Lo+dc3g+xduahqsGlvJgTKHkFKTmkP5QGBPzkS5nwyaLUwgcmqA?= =?us-ascii?Q?6ZdhuSlrU/OQ4Zy27AvWfqLqflULF5isvgqcT5YzjZmeoDEZT3GOzPU6dq5B?= =?us-ascii?Q?u6vbWOJ1kfEwIRtRUkXBQ2+hf7D5uItiWHAMCPesuR9RRciTOCegJARdvAR+?= =?us-ascii?Q?HDeC9cpqUoDbq/Wytku+MXNYZIuPeoToeo15gCi/okvCPLT6gpbf5+9bMfKX?= =?us-ascii?Q?sIF1NbEUgQMUN/vXpU6fi8kOvIAlQQMSe1sM22ffvKvSXQ8t+CSoDEsS21uJ?= =?us-ascii?Q?nRJRv4cNOBYpYS/ia8YHxYa0aLgTa1YZUVHRkWBLGoEbA/00R8ZMv0aFTMMz?= =?us-ascii?Q?URHo0dFFUcnf7fcOyK1rdVcpUFKKXgX/qYrm1BU7g5nRrpeNU752esA0xLjO?= =?us-ascii?Q?eL6XfqLdFzBHBq+h5Fqi3R0QBemggyYUmELOQSdZdcHyQMN6IaBNeYRvE7R6?= =?us-ascii?Q?3CoYvcjW/HokHPNnE+NOTeXD14gLNiegNknhbYKCMh99z1w4rnJ5T3eDfy01?= =?us-ascii?Q?LKtQJGO0Ufk3Waju7ShcNfvd/1dSCuQxg80yLzt2iBBms9+NofIdG1HpLzy6?= =?us-ascii?Q?hwif+eDmk7Ju85kvBegAd2Ku+3SIhsc2fiwPlNkFUN3J0ZZtlEqoAK17qY/T?= =?us-ascii?Q?sc7ZlFYWzfkf8Tn9OFtzdrprE+ZOkhDn48ahnUGBw+od83eHobHqX00TzXXS?= =?us-ascii?Q?ihsxtqtWaAfePQ74b6nXdqbqH8w+9aBxEfu34b8WozdwyYIQcNNqOw+i4P4P?= =?us-ascii?Q?AI4gz8WGgocqTYl6fBSLn5pWsCFOyUjEU/kPWLtElpjVyhmIUC9flYe/wKpn?= =?us-ascii?Q?o0dGdv8Y851dWawlxJA1hYn6b+n3LMYDfXF+sOvMI8nezNdbPJHtpRJZ9CO6?= =?us-ascii?Q?ptazD6EA5Z0HS2blA/cAHKY8HGMKCpmHJWZqZ9dpabMK+p3V1lK4oooFHiRk?= =?us-ascii?Q?hz7TM5SgaQXGAS8cp/19BECinhFLYwaItPql77m7K3O2jpZKafCvXQKjCuAH?= =?us-ascii?Q?uOeWRc8RESgIetc/1ITejD4yrISRD/ESQqKErWLiBigp9ruklS7Ur55s033g?= =?us-ascii?Q?Ehgb/SqsK2Mljcf14VwBPNbbjmEL3e8GFIYfzGGY7doaGl1t0OFjviTTm32R?= =?us-ascii?Q?cH82mu96wid34ycCScDuanKfLKSCW23SQc+Tybo4mx9g8+qtfP9LBvt1fim6?= =?us-ascii?Q?Zdvo+G5ZMox2A0k9/hM6wdQD1DJdH184sl6lxpDb?= Content-Type: multipart/alternative; boundary="_000_DM8PR09MB667745E57CD0351317FAA785B87C9DM8PR09MB6677namp_" MIME-Version: 1.0 X-OriginatorOrg: fnal.gov X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: DM8PR09MB6677.namprd09.prod.outlook.com X-MS-Exchange-CrossTenant-Network-Message-Id: 76abd9dc-f055-4136-8ca7-08db587ebdc4 X-MS-Exchange-CrossTenant-originalarrivaltime: 19 May 2023 15:35:57.5747 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 9d5f83d3-d338-4fd3-b1c9-b7d94d70255a X-MS-Exchange-Transport-CrossTenantHeadersStamped: PH8PR09MB9088 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_DM8PR09MB667745E57CD0351317FAA785B87C9DM8PR09MB6677namp_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi, I have a fairly large database (several TBs) where auto vacuum is enabled. = My focus is to keep the datfrozenxid reasonably low and avoid manual vacuum= . When I run following query, I see the percentage creeping up every day. R= ight after running vacuum manually on tables with large relfrozenxid the fo= llowing query would return 7% on pgprd1 database but after 5 months it incr= eased to 40%. So, eventually I am afraid I have to vacuum the tables manual= ly which has its own problems like creating massive WALs in a short time et= c. I would like to avoid manual vacuuming for this reason. SELECT freez, txns, ROUND(100*(txns/freez::float)) AS perc, datname FROM (SELECT foo.freez::int, age(datfrozenxid) AS txns, datname FROM pg_database d JOIN (SELECT setting AS freez FROM pg_settings WHERE nam= e =3D 'autovacuum_freeze_max_age') AS foo ON (true) WHERE d.datallowconn) AS foo2 ORDER BY 3 DESC, 4 ASC; 1500000000 | 599260139 | 40 | pgprd1 1500000000 | 50138249 | 3 | postgres 1500000000 | 50138249 | 3 | template1 Auto vacuum is working as I can see from logs it is repeatedly vacuuming sa= me tables, but I don't see that as a problem because those are the tables t= hat get updated/inserted continuously. Following are the settings I have. I am wondering if there is a way autovac= uum can keep the above "datfrozenxid" low and not keep increasing. Thank y= ou ahead time for reading my post. postgres=3D# select name, setting, unit from pg_settings where name like '%= autovacuum%'; name | setting | unit ---------------------------------------+------------+------ autovacuum | on | autovacuum_analyze_scale_factor | 0.1 | autovacuum_analyze_threshold | 5000 | autovacuum_freeze_max_age | 1500000000 | autovacuum_max_workers | 5 | autovacuum_multixact_freeze_max_age | 400000000 | autovacuum_naptime | 60 | s autovacuum_vacuum_cost_delay | 2 | ms autovacuum_vacuum_cost_limit | -1 | autovacuum_vacuum_insert_scale_factor | 0 | autovacuum_vacuum_insert_threshold | 5000 | autovacuum_vacuum_scale_factor | 0 | autovacuum_vacuum_threshold | 5000 | autovacuum_work_mem | -1 | kB log_autovacuum_min_duration | 0 | ms (15 rows) --_000_DM8PR09MB667745E57CD0351317FAA785B87C9DM8PR09MB6677namp_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi,

 

I have a fairly lar= ge database (several TBs) where auto vacuum is enabled. My focus is to keep= the datfrozenxid reasonably low and avoid manual vacuum. When I run follow= ing query, I see the percentage creeping up every day. Right after running vacuum manually on tables with large rel= frozenxid the following query would return 7% on pgprd1 database but after = 5 months it increased to 40%. So, eventually I am afraid I have to vacuum t= he tables manually which has its own problems like creating massive WALs in a short time etc. I would like = to avoid manual vacuuming for this reason.

 

 

 

SELECT freez, txns, ROUND(100*(txns/freez::fl= oat)) AS perc, datname

FROM (SELECT foo.freez::int, age(datfrozenxid= ) AS txns, datname

FROM pg_database d JOIN (SELECT setting AS fr= eez FROM pg_settings WHERE name =3D 'autovacuum_freeze_max_age') AS foo

ON (true) WHERE d.datallowconn) AS foo2 ORDER= BY 3 DESC, 4 ASC;

 

1500000000 | 599260139 |   40 | pgprd1

1500000000 |  50138249 |    3 | postgres

1500000000 |  50138249 |    3 | template1

 

 

Auto vacuum is working as I can see from logs it is repeatedly vacuumi= ng same tables, but I don’t see that as a problem because those are t= he tables that get updated/inserted continuously.

Following are the settings I have. I am wondering if there is a way au= tovacuum  can keep the above “datfrozenxid” low and not ke= ep increasing. Thank you ahead time for reading my post.<= /p>

 

postgres=3D# select name, setting, unit from pg_settings where name li= ke '%autovacuum%';

=             &nb= sp;    name        &= nbsp;         |  setting =   | unit

= ---------------------------------------+------------+------

= autovacuum           = ;            &n= bsp;    | on        = |

= autovacuum_analyze_scale_factor       | 0.1&n= bsp;       |

= autovacuum_analyze_threshold        = ;  | 5000       |

= autovacuum_freeze_max_age        &n= bsp;    | 1500000000 |

= autovacuum_max_workers         = ;       | 5     &nbs= p;    |

= autovacuum_multixact_freeze_max_age   | 400000000  |

= autovacuum_naptime         &nb= sp;          | 60  &= nbsp;      | s

= autovacuum_vacuum_cost_delay        = ;  | 2          | ms=

= autovacuum_vacuum_cost_limit        = ;  | -1         |

= autovacuum_vacuum_insert_scale_factor | 0     &nbs= p;    |

= autovacuum_vacuum_insert_threshold    | 5000  &nbs= p;    |

= autovacuum_vacuum_scale_factor        | = 0          |=

= autovacuum_vacuum_threshold        =    | 5000       |=

= autovacuum_work_mem         &n= bsp;         | -1   =       | kB

= log_autovacuum_min_duration        =    | 0          | ms=

(15 rows)

 

--_000_DM8PR09MB667745E57CD0351317FAA785B87C9DM8PR09MB6677namp_--