RetroBBS - comp.databases.ingres - Re: [Info-ingres] Empty pages

Re: [Info-ingres] Empty pages

<mailman.101.1635167312.1681.info-ingres@lists.planetingres.org>

https://www.rocksolidbbs.com/devel/article-flat.php?id=130&group=comp.databases.ingres#130

copy link Newsgroups: comp.databases.ingres

Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!buffer2.nntp.dca1.giganews.com!buffer1.nntp.dca1.giganews.com!news.giganews.com.POSTED!not-for-mail
NNTP-Posting-Date: Mon, 25 Oct 2021 08:10:02 -0500
Date: Mon, 25 Oct 2021 14:08:32 +0100
From: roy.hann@rationalcommerce.com (Roy Hann)
Reply-To: Roy Hann <roy.hann@rationalcommerce.com>
Organization: Rational Commerce Ltd.
To: Martin Bowes <martin.bowes@ndph.ox.ac.uk>,
"info-ingres@lists.planetingres.org" <info-ingres@lists.planetingres.org>
In-Reply-To: <16ff3002404642a9acbcf6435e580f60@ndph.ox.ac.uk>
References: <16ff3002404642a9acbcf6435e580f60@ndph.ox.ac.uk>
MIME-Version: 1.0
Content-Type: text/plain; charset=windows-1250
Content-Transfer-Encoding: quoted-printable
X-Provags-ID: V03:K1:CAwYyPpA89qli+FiITViGZ/XdWeucL/uXstXze96f7JVrSpYhfO
Rw4q/HRqfNy20iZS61pDyDWAOWGwFkicT+Xlwq0hm0rXWA/3rV3qsuQh3/pkeBkkmKZSH5F
O1F5nL2k1p4cPjH6n3orDvLzG9pzBJLYC1Rc+2FKjj0x/0+kwDylPEeQxxvaTlwNa4IFGKW
otiEGoBs0xBcocb8I2EtA==
X-Spam-Flag: NO
X-UI-Out-Filterresults: notjunk:1;V03:K0:v7wMoDxwUlQ=:+OeoekfZPrcnoHIipCcKfl
lVHLrfI5Mmvos1ILlsVTtpiiZjLa0f/GuNoOhe/YAZOkrQbuQVZx0qnao6Ww4ncdrUgDoyDHy
5e8QWAdLG6RhgK4OQ2IKptw+HtE7GmvPE07OwiMdPkwHvBD+Pugu1s383WSR+l4D6tS1F/XDD
m10Uqo8jJtyLIu1HznVjKfu0s80NscKbovNuTRDnnuHHd8fwZplxlanJ7o+JxR6TUgrx4LIZo
qey3EPxsDHK3cFqp8w0a1ctY1zVd9qEYOLZe2Z5p57ILi6kExTrgLdYww2SfEAOAu9ZTlFvgD
fSESBJdJA7uu3aAnj2rxjxONyvlNs2UKzT+gAynl/sfkX5OqLxVyrM7dnk8N+JhCKSi3wPhpg
oGHgvy6A6rLhUkvCCwsYJg/ML7BZrGxkt2YOuC7/qPIwjSedAqTXC9txXQreQ+u1iabLpXhm/
K5OAO9RxRoou4J2RJ5eA3QJvIfUGfV6pTwhlaNcopyQxZtISxI0tdscJfIoEKpkHHo5R1HhQh
SQM+H3lEfJQ7hRIL+dibKLJPGySqKMnOTBBQvKqB2Sugw0izpciyvOk51plWaMJMGf9VQKRxF
Xwndw5wZ5KoziYuB+tkrz+TUhrxOkBHePu+p7+eKEBuMyXkYKaG+H5oOG/SEkBdCxJ6nS88Ex
nQ3xdU3wvwWribnCJmW401Hain3G8bpy9gaarBeEKYqHUN+EgI4cHNApO+vlpjU9oNe+lrVjT
Y5PdiP1wJgmLMRHw
Subject: Re: [Info-ingres] Empty pages
X-BeenThere: info-ingres@lists.planetingres.org
X-Mailman-Version: 2.1.12
Precedence: list
List-Id: <info-ingres.lists.planetingres.org>
List-Unsubscribe: <https://lists.planetingres.org/mailman/options/info-ingres>,
<mailto:info-ingres-request@lists.planetingres.org?subject=unsubscribe>
List-Archive: <https://lists.planetingres.org/pipermail/info-ingres/>
List-Post: <mailto:info-ingres@lists.planetingres.org>
List-Help: <mailto:info-ingres-request@lists.planetingres.org?subject=help>
List-Subscribe: <https://lists.planetingres.org/mailman/listinfo/info-ingres>,
<mailto:info-ingres-request@lists.planetingres.org?subject=subscribe>
Newsgroups: comp.databases.ingres
Message-ID: <mailman.101.1635167312.1681.info-ingres@lists.planetingres.org>
Lines: 24
X-Usenet-Provider: http://www.giganews.com
X-Trace: sv3-WXARjPtkSFkKNdNpSaMV4CBQS7HFrqzyZ4ivNYuoiIQM6imAOjcpyrHM5p6UQAttU2q0Uo7+jCwqhz8!XCMw8BhWfWcjGEkvRFMpydrr9OPm4x0YRRBTvqtGPFuysedCBcfqoR/jt2bqhQ0m/w/iZavZHD7e!hunpC5YFFtdSesTYiy2eI2U=
X-Complaints-To: abuse@giganews.com
X-DMCA-Notifications: http://www.giganews.com/info/dmca.html
X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers
X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly
X-Postfilter: 1.3.40
X-Original-Bytes: 4247

by: Roy Hann - Mon, 25 Oct 2021 13:08 UTC

Monday, October 25, 2021, 1:48:29 PM, you wrote:

> I’ve recently had a btree table which had grown to 8025535 pages
> with zero overflow. But after a modify it shrank to a little over 5000 pages.
>
> My suspicion is that this table had grown to this size because it
> is daily getting a few hundred thousand rows inserted and then
> deleted. The key of the table is increasing and so deleted rows
> on the pages are never reused.

Deleted rows on the associated data page should get reused. My
suspicion is MVCC is enabled on the database. That defeats the free
page management in Btrees.

> Is there an easy way to determine the number of ‘empty’ pages? I’ve
> started experimenting with modify table to table_debug [with
> table_option = 1|2|3], but I’m not seeing a smoking gun.

I have written various scripts over the years to join iifile_info and
iitables with the output from ls or dir, as appropriate.

Roy

Anything cut to length will be too short.

devel / comp.databases.ingres / Re: [Info-ingres] Empty pages

Subject	Author
Re: [Info-ingres] Empty pages	Roy Hann