Skip to content

Commit eb97884

Browse files
bmomjianbmomjian
committed
Add auto-vacuum emails.
1 parent 2609ccc commit eb97884

File tree

1 file changed

+258
-2
lines changed

1 file changed

+258
-2
lines changed

‎doc/TODO.detail/vacuum

Lines changed: 258 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1403,7 +1403,7 @@ From owner-pgsql-hackers@hub.org Sat Jan 22 02:31:03 2000
14031403
Received: from renoir.op.net (root@renoir.op.net [207.29.195.4])
14041404
by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id DAA06743
14051405
for <pgman@candle.pha.pa.us>; Sat, 22 Jan 2000 03:31:02 -0500 (EST)
1406-
Received: from hub.org (hub.org [216.126.84.1]) by renoir.op.net (o1/$Revision: 1.6 $) with ESMTP id DAA07529 for <pgman@candle.pha.pa.us>; Sat, 22 Jan 2000 03:25:13 -0500 (EST)
1406+
Received: from hub.org (hub.org [216.126.84.1]) by renoir.op.net (o1/$Revision: 1.7 $) with ESMTP id DAA07529 for <pgman@candle.pha.pa.us>; Sat, 22 Jan 2000 03:25:13 -0500 (EST)
14071407
Received: from localhost (majordom@localhost)
14081408
by hub.org (8.9.3/8.9.3) with SMTP id DAA31900;
14091409
Sat, 22 Jan 2000 03:19:53 -0500 (EST)
@@ -1475,7 +1475,7 @@ From tgl@sss.pgh.pa.us Sat Jan 22 10:31:02 2000
14751475
Received: from renoir.op.net (root@renoir.op.net [207.29.195.4])
14761476
by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id LAA20882
14771477
for <pgman@candle.pha.pa.us>; Sat, 22 Jan 2000 11:31:00 -0500 (EST)
1478-
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by renoir.op.net (o1/$Revision: 1.6 $) with ESMTP id LAA26612 for <pgman@candle.pha.pa.us>; Sat, 22 Jan 2000 11:12:44 -0500 (EST)
1478+
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by renoir.op.net (o1/$Revision: 1.7 $) with ESMTP id LAA26612 for <pgman@candle.pha.pa.us>; Sat, 22 Jan 2000 11:12:44 -0500 (EST)
14791479
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
14801480
by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id LAA20569;
14811481
Sat, 22 Jan 2000 11:11:26 -0500 (EST)
@@ -1868,3 +1868,259 @@ Http://www.rhyme.com.au | / \|
18681868
PGP key available upon request, | /
18691869
and from pgp5.ai.mit.edu:11371 |/
18701870

1871+
From pgsql-hackers-owner+M29308@postgresql.org Mon Sep 23 09:47:54 2002
1872+
Return-path: <pgsql-hackers-owner+M29308@postgresql.org>
1873+
Received: from postgresql.org (postgresql.org [64.49.215.8])
1874+
by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g8NDlqd00289
1875+
for <pgman@candle.pha.pa.us>; Mon, 23 Sep 2002 09:47:53 -0400 (EDT)
1876+
Received: from localhost (postgresql.org [64.49.215.8])
1877+
by postgresql.org (Postfix) with ESMTP
1878+
id 7CA64476497; Mon, 23 Sep 2002 09:43:28 -0400 (EDT)
1879+
Received: from postgresql.org (postgresql.org [64.49.215.8])
1880+
by postgresql.org (Postfix) with SMTP
1881+
id EDA70475BC3; Mon, 23 Sep 2002 09:43:20 -0400 (EDT)
1882+
Received: from localhost (postgresql.org [64.49.215.8])
1883+
by postgresql.org (Postfix) with ESMTP id 85264476479
1884+
for <pgsql-hackers@postgresql.org>; Mon, 23 Sep 2002 09:43:15 -0400 (EDT)
1885+
Received: from www.pspl.co.in (www.pspl.co.in [202.54.11.65])
1886+
by postgresql.org (Postfix) with ESMTP id C7899476477
1887+
for <pgsql-hackers@postgresql.org>; Mon, 23 Sep 2002 09:43:12 -0400 (EDT)
1888+
Received: (from root@localhost)
1889+
by www.pspl.co.in (8.11.6/8.11.6) id g8NDiQ030526
1890+
for <pgsql-hackers@postgresql.org>; Mon, 23 Sep 2002 19:14:26 +0530
1891+
Received: from daithan (daithan.intranet.pspl.co.in [192.168.7.161])
1892+
by www.pspl.co.in (8.11.6/8.11.0) with ESMTP id g8NDiQ330521;
1893+
Mon, 23 Sep 2002 19:14:26 +0530
1894+
From: "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
1895+
To: pgsql-hackers@postgresql.org, pgsql-general@postgresql.org
1896+
Date: Mon, 23 Sep 2002 19:13:44 +0530
1897+
MIME-Version: 1.0
1898+
Subject: [HACKERS] Postgresql Automatic vacuum
1899+
Reply-To: shridhar_daithankar@persistent.co.in
1900+
Message-ID: <3D8F67E8.7500.4E0E180@localhost>
1901+
X-Mailer: Pegasus Mail for Windows (v4.02)
1902+
Content-Type: text/plain; charset=US-ASCII
1903+
Content-Transfer-Encoding: 7BIT
1904+
Content-Description: Mail message body
1905+
X-Virus-Scanned: by AMaViS new-20020517
1906+
Precedence: bulk
1907+
Sender: pgsql-hackers-owner@postgresql.org
1908+
X-Virus-Scanned: by AMaViS new-20020517
1909+
Status: OR
1910+
1911+
Hello All,
1912+
1913+
I have written a small daemon that can automatically vacuum PostgreSQL
1914+
database, depending upon activity per table.
1915+
1916+
It sits on top of postgres statistics collector. The postgres installation
1917+
should have per row statistics collection enabled.
1918+
1919+
Features are,
1920+
1921+
* Vacuuming based on activity on the table
1922+
* Per table vacuum. So only heavily updated tables are vacuumed.
1923+
* multiple databases supported
1924+
* Performs 'vacuum analyze' only, so it will not block the database
1925+
1926+
1927+
The project location is
1928+
http://gborg.postgresql.org/project/pgavd/projdisplay.php
1929+
1930+
Let me know for bugs/improvements and comments..
1931+
1932+
I am sure real world postgres installations has some sort of scripts doing
1933+
similar thing. This is an attempt to provide a generic interface to periodic
1934+
vacuum.
1935+
1936+
1937+
Bye
1938+
Shridhar
1939+
1940+
--
1941+
The Abrams' Principle: The shortest distance between two points is off the
1942+
wall.
1943+
1944+
1945+
---------------------------(end of broadcast)---------------------------
1946+
TIP 3: if posting/reading through Usenet, please send an appropriate
1947+
subscribe-nomail command to majordomo@postgresql.org so that your
1948+
message can get through to the mailing list cleanly
1949+
1950+
From pgsql-hackers-owner+M29344@postgresql.org Tue Sep 24 02:42:36 2002
1951+
Return-path: <pgsql-hackers-owner+M29344@postgresql.org>
1952+
Received: from postgresql.org (postgresql.org [64.49.215.8])
1953+
by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g8O6gYg19416
1954+
for <pgman@candle.pha.pa.us>; Tue, 24 Sep 2002 02:42:35 -0400 (EDT)
1955+
Received: from localhost (postgresql.org [64.49.215.8])
1956+
by postgresql.org (Postfix) with ESMTP
1957+
id 128704762AF; Tue, 24 Sep 2002 02:42:36 -0400 (EDT)
1958+
Received: from postgresql.org (postgresql.org [64.49.215.8])
1959+
by postgresql.org (Postfix) with SMTP
1960+
id DE80C4760F5; Tue, 24 Sep 2002 02:42:32 -0400 (EDT)
1961+
Received: from localhost (postgresql.org [64.49.215.8])
1962+
by postgresql.org (Postfix) with ESMTP id 40A8A475DBC
1963+
for <pgsql-hackers@postgresql.org>; Tue, 24 Sep 2002 02:42:25 -0400 (EDT)
1964+
Received: from relay.icomedias.com (relay.icomedias.com [62.99.232.66])
1965+
by postgresql.org (Postfix) with ESMTP id 7ECC8475DAD
1966+
for <pgsql-hackers@postgresql.org>; Tue, 24 Sep 2002 02:42:23 -0400 (EDT)
1967+
Received: from loki ([10.192.17.128])
1968+
by relay.icomedias.com (8.12.5/8.12.5) with ESMTP id g8O6g8BX014226;
1969+
Tue, 24 Sep 2002 08:42:09 +0200
1970+
Content-Type: text/plain;
1971+
charset="iso-8859-1"
1972+
From: Mario Weilguni <mweilguni@sime.com>
1973+
To: shridhar_daithankar@persistent.co.in, matthew@zeut.net
1974+
Subject: Re: [HACKERS] Postgresql Automatic vacuum
1975+
Date: Tue, 24 Sep 2002 08:42:06 +0200
1976+
User-Agent: KMail/1.4.3
1977+
cc: pgsql-hackers@postgresql.org
1978+
References: <3D8F67E8.7500.4E0E180@localhost> <3D9050B2.9782.86E55C0@localhost>
1979+
In-Reply-To: <3D9050B2.9782.86E55C0@localhost>
1980+
MIME-Version: 1.0
1981+
Message-ID: <200209240842.06459.mweilguni@sime.com>
1982+
avpresult: 0, ok, ok
1983+
X-Scanned-By: MIMEDefang 2.16 (www . roaringpenguin . com / mimedefang)
1984+
X-Virus-Scanned: by AMaViS new-20020517
1985+
Precedence: bulk
1986+
Sender: pgsql-hackers-owner@postgresql.org
1987+
X-Virus-Scanned: by AMaViS new-20020517
1988+
Content-Transfer-Encoding: 8bit
1989+
X-MIME-Autoconverted: from quoted-printable to 8bit by candle.pha.pa.us id g8O6gYg19416
1990+
Status: OR
1991+
1992+
Am Dienstag, 24. September 2002 08:16 schrieb Shridhar Daithankar:
1993+
>
1994+
> > I will play with it more and give you some more feedback.
1995+
>
1996+
> Awaiting that.
1997+
>
1998+
1999+
IMO there are still several problems with that approach, namely:
2000+
* every database will get "polluted" with the autovacuum table, which is undesired
2001+
* the biggest problem is the ~/.pgavrc file. I think it should work like other postgres utils do, e.g. supporting -U, -d, ....
2002+
* it's not possible to use without activly administration the config file. it should be able to work without
2003+
adminstrator assistance.
2004+
2005+
When this is a daemon, why not store the data in memory? Even with several thousands of tables the memory footprint would
2006+
still be small. And it should be possible to use for all databases without modifying a config file.
2007+
2008+
Two weeks ago I began writing a similar daemon, but had no time yet to finish it. I've tried to avoid using fixed numbers (namely "vacuum table
2009+
after 1000 updates") and tried to make my own heuristic based on the statistics data and the size of the table. The reason is, for a large table 1000 entries might be
2010+
a small percentage and vacuum is not necessary, while for small tables 10 updates might be sufficient.
2011+
2012+
Best regards,
2013+
Mario Weilguni
2014+
2015+
2016+
---------------------------(end of broadcast)---------------------------
2017+
TIP 2: you can get off all lists at once with the unregister command
2018+
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
2019+
2020+
From pgsql-hackers-owner+M29345@postgresql.org Tue Sep 24 03:02:50 2002
2021+
Return-path: <pgsql-hackers-owner+M29345@postgresql.org>
2022+
Received: from postgresql.org (postgresql.org [64.49.215.8])
2023+
by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g8O72lg21051
2024+
for <pgman@candle.pha.pa.us>; Tue, 24 Sep 2002 03:02:48 -0400 (EDT)
2025+
Received: from localhost (postgresql.org [64.49.215.8])
2026+
by postgresql.org (Postfix) with ESMTP
2027+
id 9B3EA4762F6; Tue, 24 Sep 2002 03:02:48 -0400 (EDT)
2028+
Received: from postgresql.org (postgresql.org [64.49.215.8])
2029+
by postgresql.org (Postfix) with SMTP
2030+
id 902EA476020; Tue, 24 Sep 2002 03:02:45 -0400 (EDT)
2031+
Received: from localhost (postgresql.org [64.49.215.8])
2032+
by postgresql.org (Postfix) with ESMTP id 98689475DAD
2033+
for <pgsql-hackers@postgresql.org>; Tue, 24 Sep 2002 03:02:18 -0400 (EDT)
2034+
Received: from www.pspl.co.in (www.pspl.co.in [202.54.11.65])
2035+
by postgresql.org (Postfix) with ESMTP id 47B8647592C
2036+
for <pgsql-hackers@postgresql.org>; Tue, 24 Sep 2002 03:02:16 -0400 (EDT)
2037+
Received: (from root@localhost)
2038+
by www.pspl.co.in (8.11.6/8.11.6) id g8O73QQ16318
2039+
for <pgsql-hackers@postgresql.org>; Tue, 24 Sep 2002 12:33:26 +0530
2040+
Received: from daithan (daithan.intranet.pspl.co.in [192.168.7.161])
2041+
by www.pspl.co.in (8.11.6/8.11.0) with ESMTP id g8O73Q316313
2042+
for <pgsql-hackers@postgresql.org>; Tue, 24 Sep 2002 12:33:26 +0530
2043+
From: "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
2044+
To: pgsql-hackers@postgresql.org
2045+
Date: Tue, 24 Sep 2002 12:32:43 +0530
2046+
MIME-Version: 1.0
2047+
Subject: Re: [HACKERS] Postgresql Automatic vacuum
2048+
Reply-To: shridhar_daithankar@persistent.co.in
2049+
Message-ID: <3D905B6B.1635.898382A@localhost>
2050+
References: <3D9050B2.9782.86E55C0@localhost>
2051+
In-Reply-To: <200209240842.06459.mweilguni@sime.com>
2052+
X-Mailer: Pegasus Mail for Windows (v4.02)
2053+
Content-Type: text/plain; charset=US-ASCII
2054+
Content-Transfer-Encoding: 7BIT
2055+
Content-Description: Mail message body
2056+
X-Virus-Scanned: by AMaViS new-20020517
2057+
Precedence: bulk
2058+
Sender: pgsql-hackers-owner@postgresql.org
2059+
X-Virus-Scanned: by AMaViS new-20020517
2060+
Status: OR
2061+
2062+
On 24 Sep 2002 at 8:42, Mario Weilguni wrote:
2063+
2064+
> Am Dienstag, 24. September 2002 08:16 schrieb Shridhar Daithankar:
2065+
> IMO there are still several problems with that approach, namely:
2066+
> * every database will get "polluted" with the autovacuum table, which is undesired
2067+
2068+
I agree. But that was the best alternative I could see. explanation
2069+
follows..Besides I didn't want to touch PG meta data..
2070+
2071+
> * the biggest problem is the ~/.pgavrc file. I think it should work like other postgres utils do, e.g. supporting -U, -d, ....
2072+
2073+
Shouldn't be a problem. The config stuff is working and I can add that. I would
2074+
rather term it a minor issue. On personal preference, I would just fire it
2075+
without any arguments. It's not a thing that you change daily. Configure it in
2076+
config file and done..
2077+
2078+
> * it's not possible to use without activly administration the config file. it should be able to work without
2079+
> adminstrator assistance.
2080+
2081+
Well. I would call that tuning. Each admin can tune it. Yes it's an effort but
2082+
certainly not an active administration.
2083+
2084+
> When this is a daemon, why not store the data in memory? Even with several thousands of tables the memory footprint would
2085+
> still be small. And it should be possible to use for all databases without modifying a config file.
2086+
2087+
Well. When postgresql has ability to deal with arbitrary number of rows, it
2088+
seemed redundant to me to duplicate all those functionality. Why write lists
2089+
and arrays again and again? Let postgresql do it.
2090+
2091+
2092+
> Two weeks ago I began writing a similar daemon, but had no time yet to finish it. I've tried to avoid using fixed numbers (namely "vacuum table
2093+
> after 1000 updates") and tried to make my own heuristic based on the statistics data and the size of the table. The reason is, for a large table 1000 entries might be
2094+
> a small percentage and vacuum is not necessary, while for small tables 10 updates might be sufficient.
2095+
2096+
Well, that fixed number is not really fixed but admin tunable, that too per
2097+
database. These are just defaults. Tune it to suit your needs.
2098+
2099+
The objective of whole exercise is to get rid of periodic vacuum as this app.
2100+
shifts threshold to activity rather than time.
2101+
2102+
Besides a table should be vacuumed when it starts affecting performance. On an
2103+
installation if a table a 1M rows and change 1K rows affects performance, there
2104+
will be a similar performance hit for a 100K rows table for 1K rows update.
2105+
Because overhead involved would be almost same.(Not disk space. pgavd does not
2106+
target vacuum full but tuple size should matter).
2107+
2108+
At least me thinks so..
2109+
2110+
I plan to implement per table threshold in addition to per database thresholds.
2111+
But right now, it seems like overhead to me. Besides there is an item in TODO,
2112+
to shift unit of work from rows to blocks affected. I guess that takes care of
2113+
some of your points..
2114+
Bye
2115+
Shridhar
2116+
2117+
--
2118+
Jones' Second Law: The man who smiles when things go wrong has thought of
2119+
someone to blame it on.
2120+
2121+
2122+
---------------------------(end of broadcast)---------------------------
2123+
TIP 5: Have you checked our extensive FAQ?
2124+
2125+
http://www.postgresql.org/users-lounge/docs/faq.html
2126+

0 commit comments

Comments
 (0)