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

Commitae55855

Browse files
committed
Update TODO.detail.
1 parentaaa318d commitae55855

File tree

1 file changed

+331
-2
lines changed

1 file changed

+331
-2
lines changed

‎doc/TODO.detail/vacuum

Lines changed: 331 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.3 $) 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.4 $) 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.3 $) 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.4 $) 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)
@@ -1539,3 +1539,332 @@ if vacuum did a drop/create index, would it be competitive?
15391539

15401540
regards, tom lane
15411541

1542+
From pgsql-hackers-owner+M5909@hub.org Thu Aug 17 20:15:33 2000
1543+
Received: from hub.org (root@hub.org [216.126.84.1])
1544+
by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id UAA00644
1545+
for <pgman@candle.pha.pa.us>; Thu, 17 Aug 2000 20:15:32 -0400 (EDT)
1546+
Received: from hub.org (majordom@localhost [127.0.0.1])
1547+
by hub.org (8.10.1/8.10.1) with SMTP id e7I0APm69660;
1548+
Thu, 17 Aug 2000 20:10:25 -0400 (EDT)
1549+
Received: from fw.wintelcom.net (bright@ns1.wintelcom.net [209.1.153.20])
1550+
by hub.org (8.10.1/8.10.1) with ESMTP id e7I01Jm68072
1551+
for <pgsql-hackers@postgresql.org>; Thu, 17 Aug 2000 20:01:19 -0400 (EDT)
1552+
Received: (from bright@localhost)
1553+
by fw.wintelcom.net (8.10.0/8.10.0) id e7I01IA20820
1554+
for pgsql-hackers@postgresql.org; Thu, 17 Aug 2000 17:01:18 -0700 (PDT)
1555+
Date: Thu, 17 Aug 2000 17:01:18 -0700
1556+
From: Alfred Perlstein <bright@wintelcom.net>
1557+
To: pgsql-hackers@postgresql.org
1558+
Subject: [HACKERS] VACUUM optimization ideas.
1559+
Message-ID: <20000817170118.K4854@fw.wintelcom.net>
1560+
Mime-Version: 1.0
1561+
Content-Type: text/plain; charset=us-ascii
1562+
Content-Disposition: inline
1563+
User-Agent: Mutt/1.2.4i
1564+
X-Mailing-List: pgsql-hackers@postgresql.org
1565+
Precedence: bulk
1566+
Sender: pgsql-hackers-owner@hub.org
1567+
Status: ORr
1568+
1569+
Here's two ideas I had for optimizing vacuum, I apologize in advance
1570+
if the ideas presented here are niave and don't take into account
1571+
the actual code that makes up postgresql.
1572+
1573+
================
1574+
1575+
#1
1576+
1577+
Reducing the time vacuum must hold an exlusive lock on a table:
1578+
1579+
The idea is that since rows are marked deleted it's ok for the
1580+
vacuum to fill them with data from the tail of the table as
1581+
long as no transaction is in progress that has started before
1582+
the row was deleted.
1583+
1584+
This may allow the vacuum process to copyback all the data without
1585+
a lock, when all the copying is done it then aquires an exlusive lock
1586+
and does this:
1587+
1588+
Aquire an exclusive lock.
1589+
Walk all the deleted data marking it as current.
1590+
Truncate the table.
1591+
Release the lock.
1592+
1593+
Since the data is still marked invalid (right?) even if valid data
1594+
is copied into the space it should be ignored as long as there's no
1595+
transaction occurring that started before the data was invalidated.
1596+
1597+
================
1598+
1599+
#2
1600+
1601+
Reducing the amount of scanning a vaccum must do:
1602+
1603+
It would make sense that if a value of the earliest deleted chunk
1604+
was kept in a table then vacuum would not have to scan the entire
1605+
table in order to work, it would only need to start at the 'earliest'
1606+
invalidated row.
1607+
1608+
The utility of this (at least for us) is that we have several tables
1609+
that will grow to hundreds of megabytes, however changes will only
1610+
happen at the tail end (recently added rows). If we could reduce the
1611+
amount of time spent in a vacuum state it would help us a lot.
1612+
1613+
================
1614+
1615+
I'm wondering if these ideas make sense and may help at all.
1616+
1617+
thanks,
1618+
--
1619+
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
1620+
1621+
From pgsql-hackers-owner+M5912@hub.org Fri Aug 18 01:36:14 2000
1622+
Received: from hub.org (root@hub.org [216.126.84.1])
1623+
by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id BAA07787
1624+
for <pgman@candle.pha.pa.us>; Fri, 18 Aug 2000 01:36:12 -0400 (EDT)
1625+
Received: from hub.org (majordom@localhost [127.0.0.1])
1626+
by hub.org (8.10.1/8.10.1) with SMTP id e7I5Q2m38759;
1627+
Fri, 18 Aug 2000 01:26:04 -0400 (EDT)
1628+
Received: from courier02.adinet.com.uy (courier02.adinet.com.uy [206.99.44.245])
1629+
by hub.org (8.10.1/8.10.1) with ESMTP id e7I5Bam35785
1630+
for <pgsql-hackers@postgresql.org>; Fri, 18 Aug 2000 01:11:37 -0400 (EDT)
1631+
Received: from adinet.com.uy (haroldo@r207-50-240-116.adinet.com.uy [207.50.240.116])
1632+
by courier02.adinet.com.uy (8.9.3/8.9.3) with ESMTP id CAA17259;
1633+
Fri, 18 Aug 2000 02:10:49 -0300 (GMT)
1634+
Message-ID: <399CC739.B9B13D18@adinet.com.uy>
1635+
Date: Fri, 18 Aug 2000 02:18:49 -0300
1636+
From: hstenger@adinet.com.uy
1637+
Reply-To: hstenger@ieee.org
1638+
Organization: PRISMA, Servicio y Desarrollo
1639+
X-Mailer: Mozilla 4.72 [en] (X11; I; Linux 2.2.14 i586)
1640+
X-Accept-Language: en
1641+
MIME-Version: 1.0
1642+
To: Alfred Perlstein <bright@wintelcom.net>, pgsql-hackers@postgresql.org
1643+
Subject: Re: [HACKERS] VACUUM optimization ideas.
1644+
References: <20000817170118.K4854@fw.wintelcom.net>
1645+
Content-Type: text/plain; charset=us-ascii
1646+
Content-Transfer-Encoding: 7bit
1647+
X-Mailing-List: pgsql-hackers@postgresql.org
1648+
Precedence: bulk
1649+
Sender: pgsql-hackers-owner@hub.org
1650+
Status: ORr
1651+
1652+
Alfred Perlstein wrote:
1653+
> #1
1654+
>
1655+
> Reducing the time vacuum must hold an exlusive lock on a table:
1656+
>
1657+
> The idea is that since rows are marked deleted it's ok for the
1658+
> vacuum to fill them with data from the tail of the table as
1659+
> long as no transaction is in progress that has started before
1660+
> the row was deleted.
1661+
>
1662+
> This may allow the vacuum process to copyback all the data without
1663+
> a lock, when all the copying is done it then aquires an exlusive lock
1664+
> and does this:
1665+
>
1666+
> Aquire an exclusive lock.
1667+
> Walk all the deleted data marking it as current.
1668+
> Truncate the table.
1669+
> Release the lock.
1670+
>
1671+
> Since the data is still marked invalid (right?) even if valid data
1672+
> is copied into the space it should be ignored as long as there's no
1673+
> transaction occurring that started before the data was invalidated.
1674+
1675+
Yes, but nothing prevents newer transactions from modifying the _origin_ side of
1676+
the copied data _after_ it was copied, but before the Lock-Walk-Truncate-Unlock
1677+
cycle takes place, and so it seems unsafe. Maybe locking each record before
1678+
copying it up ...
1679+
1680+
Regards,
1681+
Haroldo.
1682+
1683+
--
1684+
----------------------+------------------------
1685+
Haroldo Stenger | hstenger@ieee.org
1686+
Montevideo, Uruguay. | hstenger@adinet.com.uy
1687+
----------------------+------------------------
1688+
Visit UYLUG Web Site: http://www.linux.org.uy
1689+
-----------------------------------------------
1690+
1691+
From pgsql-hackers-owner+M5917@hub.org Fri Aug 18 09:41:33 2000
1692+
Received: from hub.org (root@hub.org [216.126.84.1])
1693+
by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id JAA05170
1694+
for <pgman@candle.pha.pa.us>; Fri, 18 Aug 2000 09:41:33 -0400 (EDT)
1695+
Received: from hub.org (majordom@localhost [127.0.0.1])
1696+
by hub.org (8.10.1/8.10.1) with SMTP id e7IDVjm75143;
1697+
Fri, 18 Aug 2000 09:31:46 -0400 (EDT)
1698+
Received: from andie.ip23.net (andie.ip23.net [212.83.32.23])
1699+
by hub.org (8.10.1/8.10.1) with ESMTP id e7IDPIm73296
1700+
for <pgsql-hackers@postgresql.org>; Fri, 18 Aug 2000 09:25:18 -0400 (EDT)
1701+
Received: from imap1.ip23.net (imap1.ip23.net [212.83.32.35])
1702+
by andie.ip23.net (8.9.3/8.9.3) with ESMTP id PAA58387;
1703+
Fri, 18 Aug 2000 15:25:12 +0200 (CEST)
1704+
Received: from ip23.net (spc.ip23.net [212.83.32.122])
1705+
by imap1.ip23.net (8.9.3/8.9.3) with ESMTP id PAA59177;
1706+
Fri, 18 Aug 2000 15:41:28 +0200 (CEST)
1707+
Message-ID: <399D3938.582FDB49@ip23.net>
1708+
Date: Fri, 18 Aug 2000 15:25:12 +0200
1709+
From: Sevo Stille <sevo@ip23.net>
1710+
Organization: IP23
1711+
X-Mailer: Mozilla 4.61 [en] (X11; I; Linux 2.2.10 i686)
1712+
X-Accept-Language: en, de
1713+
MIME-Version: 1.0
1714+
To: Alfred Perlstein <bright@wintelcom.net>
1715+
CC: pgsql-hackers@postgresql.org
1716+
Subject: Re: [HACKERS] VACUUM optimization ideas.
1717+
References: <20000817170118.K4854@fw.wintelcom.net>
1718+
Content-Type: text/plain; charset=us-ascii
1719+
Content-Transfer-Encoding: 7bit
1720+
X-Mailing-List: pgsql-hackers@postgresql.org
1721+
Precedence: bulk
1722+
Sender: pgsql-hackers-owner@hub.org
1723+
Status: OR
1724+
1725+
Alfred Perlstein wrote:
1726+
1727+
> The idea is that since rows are marked deleted it's ok for the
1728+
> vacuum to fill them with data from the tail of the table as
1729+
> long as no transaction is in progress that has started before
1730+
> the row was deleted.
1731+
1732+
Well, isn't one of the advantages of vacuuming in the reordering it
1733+
does? With a "fill deleted chunks" logic, we'd have far less order in
1734+
the databases.
1735+
1736+
> This may allow the vacuum process to copyback all the data without
1737+
> a lock,
1738+
1739+
Nope. Another process might update the values in between move and mark,
1740+
if the record is not locked. We'd either have to write-lock the entire
1741+
table for that period, write lock every item as it is moved, or lock,
1742+
move and mark on a per-record base. The latter would be slow, but it
1743+
could be done in a permanent low priority background process, utilizing
1744+
empty CPU cycles. Besides, it probably could not only be done simply
1745+
filling from the tail, but also moving up the records in a sorted
1746+
fashion.
1747+
1748+
> #2
1749+
>
1750+
> Reducing the amount of scanning a vaccum must do:
1751+
>
1752+
> It would make sense that if a value of the earliest deleted chunk
1753+
> was kept in a table then vacuum would not have to scan the entire
1754+
> table in order to work, it would only need to start at the 'earliest'
1755+
> invalidated row.
1756+
1757+
Trivial to do. But of course #1 may imply that the physical ordering is
1758+
even less likely to be related to the logical ordering in a way where
1759+
this helps.
1760+
1761+
> The utility of this (at least for us) is that we have several tables
1762+
> that will grow to hundreds of megabytes, however changes will only
1763+
> happen at the tail end (recently added rows).
1764+
1765+
The tail is a relative position - except for the case where you add
1766+
temporary records to a constant default set, everything in the tail will
1767+
move, at least relatively, to the head after some time.
1768+
1769+
> If we could reduce the
1770+
> amount of time spent in a vacuum state it would help us a lot.
1771+
1772+
Rather: If we can reduce the time spent in a locked state while
1773+
vacuuming, it would help a lot. Being in a vacuum is not the issue -
1774+
even permanent vacuuming need not be an issue, if the locks it uses are
1775+
suitably short-time.
1776+
1777+
Sevo
1778+
1779+
--
1780+
sevo@ip23.net
1781+
1782+
From pgsql-hackers-owner+M5911@hub.org Thu Aug 17 21:11:20 2000
1783+
Received: from hub.org (root@hub.org [216.126.84.1])
1784+
by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id VAA01882
1785+
for <pgman@candle.pha.pa.us>; Thu, 17 Aug 2000 21:11:20 -0400 (EDT)
1786+
Received: from hub.org (majordom@localhost [127.0.0.1])
1787+
by hub.org (8.10.1/8.10.1) with SMTP id e7I119m80626;
1788+
Thu, 17 Aug 2000 21:01:09 -0400 (EDT)
1789+
Received: from acheron.rime.com.au (root@albatr.lnk.telstra.net [139.130.54.222])
1790+
by hub.org (8.10.1/8.10.1) with ESMTP id e7I0wMm79870
1791+
for <pgsql-hackers@postgresql.org>; Thu, 17 Aug 2000 20:58:22 -0400 (EDT)
1792+
Received: from oberon (Oberon.rime.com.au [203.8.195.100])
1793+
by acheron.rime.com.au (8.9.3/8.9.3) with SMTP id KAA03215;
1794+
Fri, 18 Aug 2000 10:58:25 +1000
1795+
Message-Id: <3.0.5.32.20000818105835.0280ade0@mail.rhyme.com.au>
1796+
X-Sender: pjw@mail.rhyme.com.au
1797+
X-Mailer: QUALCOMM Windows Eudora Pro Version 3.0.5 (32)
1798+
Date: Fri, 18 Aug 2000 10:58:35 +1000
1799+
To: Chris Bitmead <chrisb@nimrod.itg.telstra.com.au>,
1800+
Ben Adida <ben@openforce.net>
1801+
From: Philip Warner <pjw@rhyme.com.au>
1802+
Subject: Re: [HACKERS] Inserting a select statement result into another
1803+
table
1804+
Cc: Andrew Selle <aselle@upl.cs.wisc.edu>, pgsql-hackers@postgresql.org
1805+
In-Reply-To: <399C7689.2DDDAD1D@nimrod.itg.telecom.com.au>
1806+
References: <20000817130517.A10909@upl.cs.wisc.edu>
1807+
<399BF555.43FB70C8@openforce.net>
1808+
Mime-Version: 1.0
1809+
Content-Type: text/plain; charset="us-ascii"
1810+
X-Mailing-List: pgsql-hackers@postgresql.org
1811+
Precedence: bulk
1812+
Sender: pgsql-hackers-owner@hub.org
1813+
Status: O
1814+
1815+
At 09:34 18/08/00 +1000, Chris Bitmead wrote:
1816+
>
1817+
>He does ask a legitimate question though. If you are going to have a
1818+
>LIMIT feature (which of course is not pure SQL), there seems no reason
1819+
>you shouldn't be able to insert the result into a table.
1820+
1821+
This feature is supported by two commercial DBs: Dec/RDB and SQL/Server. I
1822+
have no idea if Oracle supports it, but it is such a *useful* feature that
1823+
I would be very surprised if it didn't.
1824+
1825+
1826+
>Ben Adida wrote:
1827+
>>
1828+
>> What is the purpose you're trying to accomplish with this order by? No
1829+
matter what, all the
1830+
>> rows where done='f' will be inserted, and you will not be left with any
1831+
indication of that
1832+
>> order once the rows are in the todolist table.
1833+
1834+
I don't know what his *purpose* was, but the query should only insert the
1835+
first two rows from the select bacause of the limit).
1836+
1837+
>> Andrew Selle wrote:
1838+
>>
1839+
>> > Alright. My situation is this. I have a list of things that need to
1840+
be done
1841+
>> > in a table called tasks. I have a list of users who will complete
1842+
these tasks.
1843+
>> > I want these users to be able to come in and "claim" the top 2 most
1844+
recent tasks
1845+
>> > that have been added. These tasks then get stored in a table called
1846+
todolist
1847+
>> > which stores who claimed the task, the taskid, and when the task was
1848+
claimed.
1849+
>> > For each time someone wants to claim some number of tasks, I want to
1850+
do something
1851+
>> > like
1852+
>> >
1853+
>> > INSERT INTO todolist
1854+
>> > SELECT taskid,'1',now()
1855+
>> > FROM tasks
1856+
>> > WHERE done='f'
1857+
>> > ORDER BY submit DESC
1858+
>> > LIMIT 2;
1859+
1860+
----------------------------------------------------------------
1861+
Philip Warner | __---_____
1862+
Albatross Consulting Pty. Ltd. |----/ - \
1863+
(A.B.N. 75 008 659 498) | /(@) ______---_
1864+
Tel: (+61) 0500 83 82 81 | _________ \
1865+
Fax: (+61) 0500 83 82 82 | ___________ |
1866+
Http://www.rhyme.com.au | / \|
1867+
| --________--
1868+
PGP key available upon request, | /
1869+
and from pgp5.ai.mit.edu:11371 |/
1870+

0 commit comments

Comments
 (0)

[8]ページ先頭

©2009-2025 Movatter.jp