novaBBS - comp.databases.ingres - Re: [Info-ingres] delete_oldest

delete_oldest_ckp

<80233731-00ca-4a70-9266-7b08cab6e3d4n@googlegroups.com>

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

copy link Newsgroups: comp.databases.ingres

X-Received: by 2002:a37:f514:: with SMTP id l20mr13337068qkk.744.1638464549567;
Thu, 02 Dec 2021 09:02:29 -0800 (PST)
X-Received: by 2002:a9d:f45:: with SMTP id 63mr12009226ott.350.1638464549145;
Thu, 02 Dec 2021 09:02:29 -0800 (PST)
Path: i2pn2.org!rocksolid2!i2pn.org!weretis.net!feeder8.news.weretis.net!proxad.net!feeder1-2.proxad.net!209.85.160.216.MISMATCH!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.databases.ingres
Date: Thu, 2 Dec 2021 09:02:28 -0800 (PST)
Injection-Info: google-groups.googlegroups.com; posting-host=2600:8801:d500:6b60:7c6c:3b2f:2a05:5cf0;
posting-account=MD_47woAAABULmOe11ZqdjPGw6zni88O
NNTP-Posting-Host: 2600:8801:d500:6b60:7c6c:3b2f:2a05:5cf0
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <80233731-00ca-4a70-9266-7b08cab6e3d4n@googlegroups.com>
Subject: delete_oldest_ckp
From: richford...@gmail.com (Rich Ford)
Injection-Date: Thu, 02 Dec 2021 17:02:29 +0000
Content-Type: text/plain; charset="UTF-8"

by: Rich Ford - Thu, 2 Dec 2021 17:02 UTC

When running alterdb -delete_oldest_ckp how does Ingres determine the oldest available checkpoint?

Thanks,
Rich

Rich Ford wrote:

> When running alterdb -delete_oldest_ckp how does Ingres determine the oldest available checkpoint?

It gets it from the checkpoint history recorded in the database
configuration file. That file lives in the default data area of the
database. It's called aaaaaaaa.cnf but it's a binary file that is hard
for a human user to interpret.

Fortunately you don't have to interpret it. The infodb utility dumps the
file contents, including the checkpoint history, in a readable format.

The history array can save about 99 checkpoints IIRC... If you
somehow manage to accumulate more checkpoints than that they become
invisible and can no longer be deleted using alterdb.

Roy

Re: delete_oldest_ckp

<3599e3e2-107a-4b79-8c1f-539dd3cdbf88n@googlegroups.com>

copy mid

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

copy link Newsgroups: comp.databases.ingres

X-Received: by 2002:a05:622a:120e:: with SMTP id y14mr14905258qtx.671.1638466179848;
Thu, 02 Dec 2021 09:29:39 -0800 (PST)
X-Received: by 2002:aca:d704:: with SMTP id o4mr5530782oig.99.1638466179538;
Thu, 02 Dec 2021 09:29:39 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder8.news.weretis.net!proxad.net!feeder1-2.proxad.net!209.85.160.216.MISMATCH!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.databases.ingres
Date: Thu, 2 Dec 2021 09:29:39 -0800 (PST)
In-Reply-To: <soavlj$1ah8$1@gioia.aioe.org>
Injection-Info: google-groups.googlegroups.com; posting-host=2600:8801:d500:6b60:7c6c:3b2f:2a05:5cf0;
posting-account=MD_47woAAABULmOe11ZqdjPGw6zni88O
NNTP-Posting-Host: 2600:8801:d500:6b60:7c6c:3b2f:2a05:5cf0
References: <80233731-00ca-4a70-9266-7b08cab6e3d4n@googlegroups.com> <soavlj$1ah8$1@gioia.aioe.org>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <3599e3e2-107a-4b79-8c1f-539dd3cdbf88n@googlegroups.com>
Subject: Re: delete_oldest_ckp
From: richford...@gmail.com (Rich Ford)
Injection-Date: Thu, 02 Dec 2021 17:29:39 +0000
Content-Type: text/plain; charset="UTF-8"

by: Rich Ford - Thu, 2 Dec 2021 17:29 UTC

On Thursday, 2 December 2021 at 09:27:18 UTC-8, Roy Hann wrote:
> Rich Ford wrote:
>
> > When running alterdb -delete_oldest_ckp how does Ingres determine the oldest available checkpoint?
> It gets it from the checkpoint history recorded in the database
> configuration file. That file lives in the default data area of the
> database. It's called aaaaaaaa.cnf but it's a binary file that is hard
> for a human user to interpret.
>
> Fortunately you don't have to interpret it. The infodb utility dumps the
> file contents, including the checkpoint history, in a readable format.
>
> The history array can save about 99 checkpoints IIRC... If you
> somehow manage to accumulate more checkpoints than that they become
> invisible and can no longer be deleted using alterdb.
>
> Roy

Can this be modified to only store 1 checkpoint?
We currently are storing 2 but running low on disk space.

Rich

Rich Ford wrote:

> On Thursday, 2 December 2021 at 09:27:18 UTC-8, Roy Hann wrote:
>> Rich Ford wrote:
>>
>> > When running alterdb -delete_oldest_ckp how does Ingres determine the oldest available checkpoint?
>> It gets it from the checkpoint history recorded in the database
>> configuration file. That file lives in the default data area of the
>> database. It's called aaaaaaaa.cnf but it's a binary file that is hard
>> for a human user to interpret.
>>
>> Fortunately you don't have to interpret it. The infodb utility dumps the
>> file contents, including the checkpoint history, in a readable format.
>>
>> The history array can save about 99 checkpoints IIRC... If you
>> somehow manage to accumulate more checkpoints than that they become
>> invisible and can no longer be deleted using alterdb.
>
> Can this be modified to only store 1 checkpoint?
> We currently are storing 2 but running low on disk space.

alterdb -keep=1 ...

Roy

Re: delete_oldest_ckp

<788c60da-8f6c-4dc2-a5c6-e8e22951ae2en@googlegroups.com>

copy mid

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

copy link Newsgroups: comp.databases.ingres

X-Received: by 2002:a05:622a:1aa8:: with SMTP id s40mr15481949qtc.381.1638469115619;
Thu, 02 Dec 2021 10:18:35 -0800 (PST)
X-Received: by 2002:a05:6808:1644:: with SMTP id az4mr5820972oib.86.1638469115281;
Thu, 02 Dec 2021 10:18:35 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.databases.ingres
Date: Thu, 2 Dec 2021 10:18:35 -0800 (PST)
In-Reply-To: <sob0m3$1qud$1@gioia.aioe.org>
Injection-Info: google-groups.googlegroups.com; posting-host=2600:8801:d500:6b60:7c6c:3b2f:2a05:5cf0;
posting-account=MD_47woAAABULmOe11ZqdjPGw6zni88O
NNTP-Posting-Host: 2600:8801:d500:6b60:7c6c:3b2f:2a05:5cf0
References: <80233731-00ca-4a70-9266-7b08cab6e3d4n@googlegroups.com>
<soavlj$1ah8$1@gioia.aioe.org> <3599e3e2-107a-4b79-8c1f-539dd3cdbf88n@googlegroups.com>
<sob0m3$1qud$1@gioia.aioe.org>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <788c60da-8f6c-4dc2-a5c6-e8e22951ae2en@googlegroups.com>
Subject: Re: delete_oldest_ckp
From: richford...@gmail.com (Rich Ford)
Injection-Date: Thu, 02 Dec 2021 18:18:35 +0000
Content-Type: text/plain; charset="UTF-8"
Lines: 28

by: Rich Ford - Thu, 2 Dec 2021 18:18 UTC

On Thursday, 2 December 2021 at 09:44:37 UTC-8, Roy Hann wrote:
> Rich Ford wrote:
>
> > On Thursday, 2 December 2021 at 09:27:18 UTC-8, Roy Hann wrote:
> >> Rich Ford wrote:
> >>
> >> > When running alterdb -delete_oldest_ckp how does Ingres determine the oldest available checkpoint?
> >> It gets it from the checkpoint history recorded in the database
> >> configuration file. That file lives in the default data area of the
> >> database. It's called aaaaaaaa.cnf but it's a binary file that is hard
> >> for a human user to interpret.
> >>
> >> Fortunately you don't have to interpret it. The infodb utility dumps the
> >> file contents, including the checkpoint history, in a readable format.
> >>
> >> The history array can save about 99 checkpoints IIRC... If you
> >> somehow manage to accumulate more checkpoints than that they become
> >> invisible and can no longer be deleted using alterdb.
> >
> > Can this be modified to only store 1 checkpoint?
> > We currently are storing 2 but running low on disk space.
> alterdb -keep=1 ...
>
> Roy

Big help.

Thanks,
Rich

Re: [Info-ingres] delete_oldest_ckp

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

copy mid

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

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!news.giganews.com.POSTED!not-for-mail
NNTP-Posting-Date: Thu, 02 Dec 2021 17:36:03 -0600
Return-Path: <shift7solutions@gmail.com>
X-Original-To: info-ingres@lists.planetingres.org
Delivered-To: info-ingres@mort.croker.net
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112;
h=message-id:date:mime-version:user-agent:reply-to:subject
:content-language:to:references:from:organization:in-reply-to
:content-transfer-encoding;
bh=a9yZDcRQNswlJX5rAbzJqjicCJVARrOHrEsZmoRx+Bw=;
b=nj7I34is3qWwRLyj5Srl5bQUzhZakhH5KhFRTEiTMe4QkFPUMZYtIcYZ0AtFFfgQaN
yr2sMgqEC7MB5J5DlhoBtFKua2wuQrK+eeMSXwgHTSv24nUWndKJzkXibuoU4vwbRtxt
26bAh+l6ocik6/KAYaJfeFHV+45A4FF7QybSU0SI4xXW0b6vfmvP1iJmiYukLZcPUUNg
K0n83c1EaCJnbuaiSZlgg9YP9HOCkZcwPGvLzNzsrMXCRq5hyspIH1C3yuk3zSg6vrxV
xV2nqFFT/PwCWPnQbvmK1eyzIUoEHPeu4E/vPRvjHd290gs0Bm+jEFz9VVJJsHpVKKjL
XmzQ==
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
d=1e100.net; s=20210112;
h=x-gm-message-state:message-id:date:mime-version:user-agent:reply-to
:subject:content-language:to:references:from:organization
:in-reply-to:content-transfer-encoding;
bh=a9yZDcRQNswlJX5rAbzJqjicCJVARrOHrEsZmoRx+Bw=;
b=P7wQ3dxt017CJeUXDKHdJf6R4LpJ5iMLydelsRLCAWHb1ISkCLv+Fr1TqkZfDj7ElY
sazp1SnQ45GGJMR4vwDHCuwQYxkCL+D+XbyqECpxDDjDKRUG4UdE0CFCluxOEq85sZnO
ZcwOy0+CRH7Ur4LDb+P/HKCVHKLEoOePE0hALN2anLG38CaW4QM21HXOLlSBSUv6+P/F
D2RCrX4+G+nsXBfNWxGTs4352i2JG+Af6jDkF7PQZffZ+gvwoOb+87XeKS1O/bDDmZ7u
eg5k6hMvEZjNo1gAJfG7bX9Ir4WaPgBrEX5HQlMYCVKzU1RTYa9kJKw1fQQyBnByDO/g
E1wQ==
X-Gm-Message-State: AOAM533P69OmQPBnwHl8hmwd0SVMN1bw+vh6EXulVMh6TKhSvLWfvSsu
beZ01b4YjJLTVhjHN3Lb+29UnHeAXOc=
X-Google-Smtp-Source: ABdhPJxnJAvB2E+1AxshZTD9nUtkoWI0KGgJ/3OWir6wcyl8Wc2GqIO+6Q4rG/MuqP5KjNWfAEiSng==
X-Received: by 2002:a65:4d4b:: with SMTP id j11mr1788743pgt.181.1638488095362;
Thu, 02 Dec 2021 15:34:55 -0800 (PST)
Date: Fri, 3 Dec 2021 09:34:48 +1000
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Windows NT 6.3; Win64; x64; rv:91.0) Gecko/20100101
Thunderbird/91.3.2
Reply-To: shift7solutions@gmail.com
Content-Language: en-US
To: info-ingres@lists.planetingres.org
References: <80233731-00ca-4a70-9266-7b08cab6e3d4n@googlegroups.com>
<soavlj$1ah8$1@gioia.aioe.org>
<3599e3e2-107a-4b79-8c1f-539dd3cdbf88n@googlegroups.com>
<sob0m3$1qud$1@gioia.aioe.org>
<788c60da-8f6c-4dc2-a5c6-e8e22951ae2en@googlegroups.com>
From: shift7so...@gmail.com (Paul White)
Organization: Shift Seven Solutions
In-Reply-To: <788c60da-8f6c-4dc2-a5c6-e8e22951ae2en@googlegroups.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Subject: Re: [Info-ingres] delete_oldest_ckp
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.106.1638488100.1681.info-ingres@lists.planetingres.org>
Lines: 128
X-Usenet-Provider: http://www.giganews.com
X-Trace: sv3-xGwWa52MOuuy3GahWezKcIag/QGvXYMu9NpF3LIySbt6K/DYarR3hMJk8E3oq5OvTkZG9W8aGYNRr3m!+UJf3tD8mG6ZG6G7mzmld4vRJHU8lB1qvVV4qwf2a8dhW3sCn/i5bh5tCaMKg9pG+TzHv3w9rutb!nAeoSU+TCYNu+w+6w4qPR+4=
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: 10445

by: Paul White - Thu, 2 Dec 2021 23:34 UTC

Hi Rich,
you wrote:
> Can this be modified to only store 1 checkpoint?
> We currently are storing 2 but running low on disk space.

Be careful that you always have at least one good checkpoint. If checkpoint fails due to lack of disk space, it leaves an incomplete file/directory. You can get a situation where you have old, but good, checkpoints followed by a sequence of bad checkpoints and no room to make another checkpoint. Like this:

$ infodb
....
----Checkpoint History for Journal----------------------------------------------
Date Ckp_sequence First_jnl Last_jnl valid mode
----------------------------------------------------------------------------
Sat Jun 10 22:09:29 2017 224 37337 37425 1 ONLINE
Sat Jun 17 22:08:04 2017 225 37426 37970 0 ONLINE
Sun Jun 25 06:47:24 2017 226 37971 38494 1 ONLINE
Sat Jul 01 22:10:40 2017 227 38495 39046 0 ONLINE
Sat Jul 08 22:10:20 2017 228 39047 39597 0 ONLINE
Sat Jul 15 22:24:23 2017 229 39598 39858 0 ONLINE

$ . cdckp
/journals/ingresII/ingres/ckp/default

$ du -sk mydb/*
8103752 mydb/c0224001.ckp
0 mydb/c0225001.ckp
8230401 mydb/c0226001.ckp
5054221 mydb/c0227001.ckp
0 mydb/c0228001.ckp
0 mydb/c0229001.ckp

$ alterdb mydb -keep=2
Sun Jul 16 10:20:02 2017 ALTERDB: No checkpoints available to delete.

$ alterdb mydb -keep=1
Sun Jul 16 10:20:07 2017 I_DM1405_ALT_CKP_DELETED Database 'mydb', checkpoint set 224 deleted.
Sun Jul 16 10:20:07 2017 I_DM1405_ALT_CKP_DELETED Database 'mydb', checkpoint set 225 deleted.

$ infodb
....
----Checkpoint History for Journal----------------------------------------------
Date Ckp_sequence First_jnl Last_jnl valid mode
----------------------------------------------------------------------------
Sun Jun 25 06:47:24 2017 226 37971 38494 1 ONLINE
Sat Jul 01 22:10:40 2017 227 38495 39046 0 ONLINE
Sat Jul 08 22:10:20 2017 228 39047 39597 0 ONLINE
Sat Jul 15 22:24:23 2017 229 39598 39858 0 ONLINE

$ du -sk mydb/*
8230401 mydb/c0226001.ckp
5054221 mydb/c0227001.ckp
0 mydb/c0228001.ckp
0 mydb/c0229001.ckp

In this example, I would advise to manually remove incomplete checkpoints (not c0226001.ckp) before running another checkpoint. The "-keep" option removes oldest bad checkpoints when you have the required number of successful checkpoints.

You might consider running data compression. For unix/linux I generally use tar -Z or pigz. For windows I have a custom script which performs the work. Make sure custom scripts are returning the right status to the checkpoint process.

$ ingprenv II_CKTMPL_FILE
/home/ingres/IngresII/ingres/files/cktmpl_custom.def

from:/home/ingres/IngresII/ingres/files/cktmpl_custom.def
WSDD: cd %D; /home/ingres/IngresII/ingres/bin/cktmpl_wrap /bin/tar -c --use-compress-program=pigz -f %A *
WSDT: cd %D; /home/ingres/IngresII/ingres/bin/cktmpl_wrap /bin/tar -c --use-compress-program=pigz -f %A %B
WRDD: cd %D; /bin/tar -x --use-compress-program=pigz -f %A
WRDT: cd %D; /bin/tar -x --use-compress-program=pigz -f %A %B

from:c:\ingresII\ingres\files\cktmpl_custom.def
WSDD: myckxcopy "%D" "%A" BACKUP
WSDT: myckxcopy "%D" "%A" BACKUP PARTIAL %B
WRDD: myckxcopy "%D" "%A" RESTORE
WRDT: myckxcopy "%D" "%A" RESTORE PARTIAL %B

I like to keep a copy of the config file immediately after checkpoint is complete. This helps when I need to restore old checkpoints from long term archive. The backup script does this:

echo "copy current config for $db" >> $log_file
cp -p /journals/ingresII/ingres/dmp/default/${db}/aaaaaaaa.cnf /journals/ingresII/ingres/dmp/default/${db}/aaaaaaaa-`date +%y%m%d%H%M%S`.cnf

This example shows the database has 5 checkpoints in local storage. I replicate (scp, winscp etc) the checkpoint location to another server or NAS for DR purposes.

$ . cddmp
/journals/ingresII/ingres/dmp/default
$ ls -lat mydb|more
total 3380
drwxr-x---. 2 ingres ingresbak 4096 Dec 3 10:03 .
-rw-r--r--. 1 ingres ingres 562 Dec 3 10:03 aaaaaaaa.ext
-rw-r--r--. 1 ingres ingres 10240 Dec 3 10:03 aaaaaaaa.cnf
-rw-r--r--. 1 ingres ingres 10240 Dec 2 23:38 aaaaaaaa-211202233825.cnf
-rw-------. 1 ingres ingres 66048 Dec 2 23:38 d0000649.dmp
-rw-------. 1 ingres ingres 620544 Dec 2 23:38 c1846000.lst
-rw-------. 1 ingres ingres 10240 Dec 2 23:35 c0001846.dmp
-rw-r--r--. 1 ingres ingres 10240 Dec 1 23:38 aaaaaaaa-211201233825.cnf
-rw-------. 1 ingres ingres 620544 Dec 1 23:38 c1845000.lst
-rw-------. 1 ingres ingres 10240 Dec 1 23:35 c0001845.dmp
-rw-r--r--. 1 ingres ingres 10240 Nov 30 23:38 aaaaaaaa-211130233825.cnf
-rw-------. 1 ingres ingres 620544 Nov 30 23:38 c1844000.lst
-rw-------. 1 ingres ingres 10240 Nov 30 23:35 c0001844.dmp
-rw-r--r--. 1 ingres ingres 10240 Nov 29 23:38 aaaaaaaa-211129233826.cnf
-rw-------. 1 ingres ingres 620544 Nov 29 23:38 c1843000.lst
-rw-------. 1 ingres ingres 10240 Nov 29 23:35 c0001843.dmp
-rw-r--r--. 1 ingres ingres 10240 Nov 28 23:38 aaaaaaaa-211128233824.cnf
-rw-------. 1 ingres ingres 620544 Nov 28 23:38 c1842000.lst
-rw-------. 1 ingres ingres 10240 Nov 28 23:35 c0001842.dmp
-rw-r--r--. 1 ingres ingres 10240 Nov 27 23:38 aaaaaaaa-211127233833.cnf
-rw-r--r--. 1 ingres ingres 10240 Nov 26 23:38 aaaaaaaa-211126233826.cnf
-rw-r--r--. 1 ingres ingres 10240 Nov 25 23:38 aaaaaaaa-211125233826.cnf
-rw-r--r--. 1 ingres ingres 10240 Nov 24 23:38 aaaaaaaa-211124233826.cnf
-rw-r--r--. 1 ingres ingres 10240 Nov 23 23:38 aaaaaaaa-211123233828.cnf
-rw-r--r--. 1 ingres ingres 10240 Nov 22 23:38 aaaaaaaa-211122233825.cnf
-rw-r--r--. 1 ingres ingres 10240 Nov 21 23:38 aaaaaaaa-211121233825.cnf
-rw-r--r--. 1 ingres ingres 10240 Nov 20 23:38 aaaaaaaa-211120233835.cnf
-rw-r--r--. 1 ingres ingres 10240 Nov 19 23:38 aaaaaaaa-211119233825.cnf
....

Paul

Most public domain software is free, at least at first glance.

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

Subject	Author
delete_oldest_ckp	Rich Ford
Re: delete_oldest_ckp	Roy Hann
Re: delete_oldest_ckp	Rich Ford
Re: delete_oldest_ckp	Roy Hann
Re: delete_oldest_ckp	Rich Ford
Re: [Info-ingres] delete_oldest_ckp	Paul White