Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commiteb97884

Browse files
committed
Add auto-vacuum emails.
1 parent2609ccc commiteb97884

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+
someoneto 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)
[8]ページ先頭
©2009-2025 Movatter.jp