ALTER FRAGMENT ...INIT IN
Neil Truby asked why cancelling an ALTER FRAGMENT ... INIT IN now takes a long time to roll back, when in older versions (9.20/9.40) aborting was instantaneous — since the operation builds a new table and only switches at the end, backout should be trivial. Responses noted that INIT IN generates heavy logging and long exclusive locks, and that some things (e.g. space allocation) are logged even with database logging turned off; suggestions included checking log consumption/first extent sizing and adding logical logs. Fernando Nunes pointed to an existing PMR (05608,019,866, from 2011) that appears to have been fixed, suggesting a retest on 12.10 or a follow-up with a lab advocate. No confirmed resolution is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Back in the day, say about 1999, I used to use this all the time to re-order tables (yes, I call it re-order, I learnt database administration on Adabas, a far superior product to its main competitor of the day, a product called "DB2", which nonetheless lost out because IBM willed it so. How times change ... ;-)). Its beauty was that you could can it at any time and said canning was instantaneous, providing an instant backout. This was quite valuable as one of ALTER FRAGMENT's downsides was that it was completely impenetrable, and it was impossible to tell if your run was 1% or 99% complete. My feeling in more recent tests is that somewhere in the intervening years something fundamental changed, and you can now wait painfully long seconds/minutes/hours for an aborted ALTER FRAGMENT ...INIT IN to do some kind of rollback. Does anyone have any insight? thanks Neil
INIT IN moves the data and generates a lot of logging for the transaction being aborted as a long transaction, and a long exclusive lock being held on the affected tables.
>> INIT IN moves the data and generates a lot of logging for the transaction being aborted as a long transaction, and a long exclusive lock being held on the affected tables. Thanks for your reply. I should have added that I always turned logging off before launching an ALTER INIT ... FRAGMENT on a large table, for exactly that reason. So my question is, given that I've disabled transaction logging at the DB level, am I right to expect an abort to back out cleanly and instantly, as it definitely used to back in the day.
I have no special insight about this particular subject. But even without logging, somethings are logged. Space allocation for instance. Is this something you can reproduce easily? Have you checked log consumption? Do you have the first extent correctly defined? If not, fixing it, reduces the log usage? I can do some research... Regards. On Tue, Jul 23, 2013 at 5:08 PM, NEIL TRUBY <neil.truby@ardenta.com> wrote: > >> INIT IN moves the data and generates a lot of logging for the > transaction > being aborted as a long transaction, and a long exclusive lock being held > on > the affected tables. > > Thanks for your reply. I should have added that I always turned logging off > before launching an ALTER INIT ... FRAGMENT on a large table, for exactly > that > reason. > > So my question is, given that I've disabled transaction logging at the DB > level, am I right to expect an abort to back out cleanly and instantly, as > it > definitely used to back in the day. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a1133a182e9a5f704e231834d
Hmmmm.... I did look this up... And a PMR from you came up... around April this year... is this a different situation? Regards. On Tue, Jul 23, 2013 at 6:56 PM, Fernando Nunes <domusonline@gmail.com>wrote: > I have no special insight about this particular subject. But even without > logging, somethings are logged. Space allocation for instance. > Is this something you can reproduce easily? Have you checked log > consumption? Do you have the first extent correctly defined? If not, fixing > it, reduces the log usage? > I can do some research... > > Regards. > > On Tue, Jul 23, 2013 at 5:08 PM, NEIL TRUBY <neil.truby@ardenta.com> > wrote: > > > >> INIT IN moves the data and generates a lot of logging for the > > transaction > > being aborted as a long transaction, and a long exclusive lock being held > > on > > the affected tables. > > > > Thanks for your reply. I should have added that I always turned logging > off > > before launching an ALTER INIT ... FRAGMENT on a large table, for exactly > > that > > reason. > > > > So my question is, given that I've disabled transaction logging at the DB > > level, am I right to expect an abort to back out cleanly and instantly, > as > > it > > definitely used to back in the day. > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --001a1133a182e9a5f704e231834d > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --089e013a04f28c87e404e231a67b
Blimey, I don't remember. I've drunk a lot of beer since then. I doubt it though, as we haven't tried ALTER FRAGMENT ... INIT for years as I recall, since all my colleagues hate it for its lack of predictability. What was the number?
Was it 76641,019,866?
Sorry... I messed up the dates... I confused month day with year... It's 05608,019,866 and it was in 2011... Please try the same on 12.10.... It seems to have been fixed. Or ask your lab advocate to dig a little more than I did. (don't blame beer!) :) Regardsb On Tue, Jul 23, 2013 at 9:39 PM, NEIL TRUBY <neil.truby@ardenta.com> wrote: > Was it 76641,019,866? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf307cfcee7a676a04e234bc83
You're welcome on my reply! >> As to would it take a long time, even if you disabled transaction loging? It all depends on several factors: table sizes, where fragments are stored, hareware, indexes, locks, shared memory, internal logs, etc. etc. >> As to "back in the day..." What version are you refering to? Saludos (Regards)
>> As to would it take a long time, even if you disabled transaction loging?: It all depends on several factors: table sizes, where fragments are stored, hareware, indexes, locks, shared memory, internal logs, etc. etc. I don't really get that. The principle of ALTER FRAGMENT ... INIT is that it builds the new table leaving the old completely intact, and switches over to the new one only at the very end. Therefore the back-out should really just be a couple of reserved page changes and done. So I agree that the ALTER FRAGMENT itself is highly dependent upon all those things. But the back-out SHOULD be trivial, and therefore shouldn't. >> As to "back in the day..." What version are you refering to? Oooh. 9.20 and 9.40?
Perhaps newer versions of Informix work differntly when aborting. Could be its
internally logging in exclusive mode even though you've disabled it with
ontape -N. If so, try increasing number of logical logs in your onconfig andsee what happens.