Hi Paul,
The 'on_logfull = commit' command allows the Ingres Transaction Log to
reach very high percentages and for the session that issued the command
to continue.
Anything else is in for a rocky ride...All the normal caveats about
Force Abort processing apply to those sessions. It can get nasty. I
normally only run on_logfull=commit on big batch programs run in off
peak times.
BTW. How big is your transaction Log file? If its <2G you may find its
just easier to expand the transaction log file to at least 2G. Be aware
that not all OS's support files of that size.
Another trick you could try is splitting the update into many smaller
updates on the basis of a key an committing each on.
eg update...where mod(a_key,10)<=5;commit; update...where
mod(a_key,10)>5; commit;
Marty
From: Paul Caffrey [mailto
aul.caffrey (AT) gmail (DOT) com]
Sent: 23 December 2008 14:45
To: Martin Bowes
Subject: Re: [Info-Ingres] transaction logging on update
Martin
Thanks for your response.
I hadn't seen the onlogfull=commit before
And I was wondering what kind of effect this might have on other
transactions at the time the logs actually fill? Will they suffer from
poor performance?
Regards
Paul
On Mon, Dec 22, 2008 at 11:24 AM, Martin Bowes
<martin.bowes (AT) ctsu (DOT) ox.ac.uk> wrote:
Hi Paul,
Presumably you want to do this because the update logging is blowing
your log file apart.
You have a few options:
1. Set nojournaling on tablename; update tablename...; set journaling on
tablename.
This should improve the logging situation, but at the cost of
removing journaling from the table until the next checkpoint runs. You
should follow the update with a checkpoint.
2. set session with on_logfull=commit;
Everything gets logged, but when the transaction log file fills
up it autocommits what it has and then continues with the rest of the
update statement.
I find this to be the best option.
3. set nologging.
This is extremely dangerous. One error of any type and you are
up for a database recovery. If you absolutely must use it, take a backup
of the database first and then run the update using an exclusive
database lock. Once completed, take another backup of the database.
Martin Bowes
-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
paul.caffrey (AT) gmail (DOT) com
Sent: 22 December 2008 11:08
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: [Info-Ingres] transaction logging on update
Hi
I know I can do a "copy.in" to a structured table which has no
secondary indexes and it is not logged.
I tried the same approach for an update but cannot seem to get it to
work.
I have
set autocommit on;
set nojournaling;
update ...
but even without secondary indexes it is being logged.
Is it possible to do updates without logging?
Regards
_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://www.kettleriverconsulting.com...fo/info-ingres
transaction logging on update -
12-22-2008
, 05:08 AM
Linear Mode
