Re: adding rowids to a frag'd table
Hey Art, Thanks for the response. My ltxhwm is set at 45 which is approx 54 logs. The reason I don't supsect I'm filling the logs is because how fast it gets into the long trans. Also, the table has been altered to raw. Which shouldn't be logged unless by explicitly placing it in a trans to lock the table in exclusive is causing the raw table to be logged. ======================== Darren Jacobs Sr Database Analyst Darren_Jacobs@carmax.com 804.747.0422 x3221 ======================== Art Kagel <art.kagel@gmail. com> To Sent by: Darren_Jacobs@carmax.com informix-list-bou cc nces@iiug.org informix-list@iiug.org Subject Re: adding rowids to a frag'd table 11/03/2009 09:41 AM Long transaction rollback is only caused by filling the logical logs past the LTXHWM Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Nov 3, 2009 at 8:23 AM, <Darren_Jacobs@carmax.com> wrote: IDS 10 FC8 HPUX 11 Greetings I'm in the process of altering a 22 mil row table frag'd across 4 dbspaces to accomodate connectivity through windoze SSIS which is dependant upon the rowid. Why...I don't know....is sqeal! Anyway, I keep running into a long trans. I've dropped all the indexes, altered the table to raw, locked the table in exclusive to avoid blowing the locks, and I still run into a long trans. This is a test system I'm running this on so there is little or no other activity running. There are approx 120 logs at 100 meg each. I know I'm not filling 50% of the logs. I'm also not blowing space. Other than unloading, dropping, recreating the table with rowids, does anyone have other ideas? or what may be causing the long trans? Thanks! ======================== Darren Jacobs Sr Database Analyst Darren_Jacobs@carmax.com 804.747.0422 x3221 ======================== _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list
Darren_Jacobs@carmax.com wrote: > Hey Art, > > Thanks for the response. > > My ltxhwm is set at 45 which is approx 54 logs. The reason I don't supsect > I'm filling the logs is because how fast it gets into the long trans. > Also, the table has been altered to raw. Which shouldn't be logged unless > by explicitly placing it in a trans to lock the table in exclusive is > causing the raw table to be logged. > > > ======================== > Darren Jacobs > Sr Database Analyst > Darren_Jacobs@carmax.com > 804.747.0422 x3221 > ======================== > > > > Art Kagel > <art.kagel@gmail. > com> To > Sent by: Darren_Jacobs@carmax.com > informix-list-bou cc > nces@iiug.org informix-list@iiug.org > Subject > Re: adding rowids to a frag'd table > 11/03/2009 09:41 > AM > > > > > > > > > Long transaction rollback is only caused by filling the logical logs past > the LTXHWM > > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Oninit, the IIUG, nor any other > organization with which I am associated either explicitly or implicitly. > Neither do those opinions reflect those of other individuals affiliated > with any entity with which I am affiliated nor those of the entities > themselves. > > > > On Tue, Nov 3, 2009 at 8:23 AM, <Darren_Jacobs@carmax.com> wrote: > > IDS 10 FC8 > HPUX 11 > > Greetings > > I'm in the process of altering a 22 mil row table frag'd across 4 > dbspaces > to accomodate connectivity through windoze SSIS which is dependant upon > the > rowid. Why...I don't know....is sqeal! > > Anyway, I keep running into a long trans. I've dropped all the indexes, > altered the table to raw, locked the table in exclusive to avoid blowing > the locks, and I still run into a long trans. This is a test system I'm > running this on so there is little or no other activity running. There > are > approx 120 logs at 100 meg each. I know I'm not filling 50% of the logs. > I'm also not blowing space. > > Other than unloading, dropping, recreating the table with rowids, does > anyone have other ideas? or what may be causing the long trans? > > Thanks! > > ======================== > Darren Jacobs > Sr Database Analyst > Darren_Jacobs@carmax.com > 804.747.0422 x3221 > ======================== > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > Just some odd comments : 1. Are all your logical logs the same size? 2. Have you decent extent size allocations? 3. What is the actual statement you are running?
Hello Darren,
when you start do a onstat -lx and search for
address flags userthread locks log begin isolation
retrys coordinator
c00000000be85028 A---- c00000000be44028 0 0 COMMIT
0
c00000000be85268 A---- c00000000be44828 0 0 COMMIT
0
c00000000be854a8 A---- c00000000be45028 0 0 COMMIT
0
c00000000be856e8 A---- c00000000be45828 0 0 COMMIT
0
c00000000be85928 A---- c00000000be46028 0 0 COMMIT
0
c00000000be85b68 A---- c00000000be46828 0 0 COMMIT
0
c00000000be85da8 A---- c00000000be47028 0 0 COMMIT
0
c00000000be86468 A---- c00000000be48028 0 0 COMMIT
0
c00000000be866a8 A-B-- c00000000be49028 14 22498 COMMIT
0
------------------------------------------------------------------------------
^^^^
9 active, 128 total, 11 maximum concurrent
c000000009161220 15 U-B---- 22495 10782b 2000
2000 100.00
c000000009161240 16 U-B---- 22496 107ffb 2000
2000 100.00
c000000009161260 17 U-B---- 22497 1087cb 2000
2000 100.00
c000000009161280 18 U---C-L 22498 108f9b 2000
1699 84.95
------------------------------------------------------^^^^^
log 22498 is where the trx starts. even an unlogged database can have
a long trx rollback when a lot of
challocs has to be done; this can be caused by tables who have a lot
of extents ; however it is a bit doubtfull that
this is the case since your logspace is...
> approx 120 logs at 100 meg each. I know I'm not filling 50% of the logs.
maybe an onlog can tell you what actions are performed???
Superboer.
On 3 nov, 16:13, Darren_Jac...@carmax.com wrote:
> Hey Art,
>
> Thanks for the response.
>
> My ltxhwm is set at 45 which is approx 54 logs. The reason I don't supsect
> I'm filling the logs is because how fast it gets into the long trans.
> Also, the table has been altered to raw. Which shouldn't be logged unless
> by explicitly placing it in a trans to lock the table in exclusive is
> causing the raw table to be logged.
>
> ========================
> Darren Jacobs
> Sr Database Analyst
> Darren_Jac...@carmax.com
> 804.747.0422 x3221
> ========================
>
> Art Kagel
> <art.kagel@gmail.
> com> To
> Sent by: Darren_Jac...@carmax.com
> informix-list-bou cc
> n...@iiug.org informix-l...@iiug.org
> Subject
> Re: adding rowids to a frag'd table
> 11/03/2009 09:41
> AM
>
> Long transaction rollback is only caused by filling the logical logs past
> the LTXHWM
>
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (a...@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Oninit, the IIUG, nor any other
> organization with which I am associated either explicitly or implicitly.
> Neither do those opinions reflect those of other individuals affiliated
> with any entity with which I am affiliated nor those of the entities
> themselves.
>
> On Tue, Nov 3, 2009 at 8:23 AM, <Darren_Jac...@carmax.com> wrote:
>
> IDS 10 FC8
> HPUX 11
>
> Greetings
>
> I'm in the process of altering a 22 mil row table frag'd across 4
> dbspaces
> to accomodate connectivity through windoze SSIS which is dependant upon
> the
> rowid. Why...I don't know....is sqeal!
>
> Anyway, I keep running into a long trans. I've dropped all the indexes,
> altered the table to raw, locked the table in exclusive to avoid blowing
> the locks, and I still run into a long trans. This is a test system I'm
> running this on so there is little or no other activity running. There
> are
> approx 120 logs at 100 meg each. I know I'm not filling 50% of the logs.
> I'm also not blowing space.
>
> Other than unloading, dropping, recreating the table with rowids, does
> anyone have other ideas? or what may be causing the long trans?
>
> Thanks!
>
> ========================
> Darren Jacobs
> Sr Database Analyst
> Darren_Jac...@carmax.com
> 804.747.0422 x3221
> ========================
>
> _______________________________________________
> Informix-list mailing list
> Informix-l...@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
> _______________________________________________
> Informix-list mailing list
> Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list
TBP,
1. Are all your logical logs the same size?
Yes, all logs are the same size.
2. Have you decent extent size allocations?
tbl is frag'd in its own dbspace. extent size 1500000 next size 750000
3. What is the actual statement you are running?
drop index ps9jrnl_ln_dwp;
drop index ps_jrnl_ln_dwp;
drop index psbjrnl_ln_dwp;
drop index psdjrnl_ln_dwp;
drop index psfjrnl_ln_dwp;
drop index psvjrnl_ln_dwp;
drop index pswjrnl_ln_dwp;
drop index psxjrnl_ln_dwp;
drop index psyjrnl_ln_dwp;
drop index pszjrnl_ln_dwp;
alter table ps_jrnl_ln_dwp type (raw);
begin work;
lock table ps_jrnl_ln_dwp in exclusive mode;
alter table ps_jrnl_ln_dwp add rowids;commit;
========================
Darren Jacobs
Sr Database Analyst
Darren_Jacobs@carmax.com
804.747.0422 x3221
========================
theBP
<theBP@Usenet-New
s.Net> To
Sent by: informix-list@iiug.org
informix-list-bou cc
nces@iiug.org
Subject
Re: adding rowids to a frag'd table
11/03/2009 10:55
AM
Darren_Jacobs@carmax.com wrote:
> Hey Art,
>
> Thanks for the response.
>
> My ltxhwm is set at 45 which is approx 54 logs. The reason I don't
supsect
> I'm filling the logs is because how fast it gets into the long trans.
> Also, the table has been altered to raw. Which shouldn't be logged
unless
> by explicitly placing it in a trans to lock the table in exclusive is
> causing the raw table to be logged.
>
>
> ========================
> Darren Jacobs
> Sr Database Analyst
> Darren_Jacobs@carmax.com
> 804.747.0422 x3221
> ========================
>
>
>
> Art Kagel
> <art.kagel@gmail.
> com>
To
> Sent by: Darren_Jacobs@carmax.com
> informix-list-bou
cc
> nces@iiug.org informix-list@iiug.org
>
Subject
> Re: adding rowids to a frag'd
table
> 11/03/2009 09:41
> AM
>
>
>
>
>
>
>
>
> Long transaction rollback is only caused by filling the logical logs past
> the LTXHWM
>
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Oninit, the IIUG, nor any other
> organization with which I am associated either explicitly or implicitly.
> Neither do those opinions reflect those of other individuals affiliated
> with any entity with which I am affiliated nor those of the entities
> themselves.
>
>
>
> On Tue, Nov 3, 2009 at 8:23 AM, <Darren_Jacobs@carmax.com> wrote:
>
> IDS 10 FC8
> HPUX 11
>
> Greetings
>
> I'm in the process of altering a 22 mil row table frag'd across 4
> dbspaces
> to accomodate connectivity through windoze SSIS which is dependant upon
> the
> rowid. Why...I don't know....is sqeal!
>
> Anyway, I keep running into a long trans. I've dropped all the
indexes,
> altered the table to raw, locked the table in exclusive to avoid
blowing
> the locks, and I still run into a long trans. This is a test system
I'm
> running this on so there is little or no other activity running. There
> are
> approx 120 logs at 100 meg each. I know I'm not filling 50% of the
logs.
> I'm also not blowing space.
>
> Other than unloading, dropping, recreating the table with rowids, does
> anyone have other ideas? or what may be causing the long trans?
>
> Thanks!
>
> ========================
> Darren Jacobs
> Sr Database Analyst
> Darren_Jacobs@carmax.com
> 804.747.0422 x3221
> ========================
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
>
Just some odd comments :
1. Are all your logical logs the same size?
2. Have you decent extent size allocations?
3. What is the actual statement you are running?
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
Darren_Jacobs@carmax.com wrote:
> TBP,
>
> 1. Are all your logical logs the same size?
> Yes, all logs are the same size.
> 2. Have you decent extent size allocations?
> tbl is frag'd in its own dbspace. extent size 1500000 next size 750000
> 3. What is the actual statement you are running?
> drop index ps9jrnl_ln_dwp;
> drop index ps_jrnl_ln_dwp;
> drop index psbjrnl_ln_dwp;
> drop index psdjrnl_ln_dwp;
> drop index psfjrnl_ln_dwp;
> drop index psvjrnl_ln_dwp;
> drop index pswjrnl_ln_dwp;
> drop index psxjrnl_ln_dwp;
> drop index psyjrnl_ln_dwp;
> drop index pszjrnl_ln_dwp;>
> alter table ps_jrnl_ln_dwp type (raw);>
> begin work;
> lock table ps_jrnl_ln_dwp in exclusive mode;
> alter table ps_jrnl_ln_dwp add rowids;> commit;
>
>
>
> ========================
> Darren Jacobs
> Sr Database Analyst
> Darren_Jacobs@carmax.com
> 804.747.0422 x3221
> ========================
>
>
>
> theBP
> <theBP@Usenet-New
> s.Net> To
> Sent by: informix-list@iiug.org
> informix-list-bou cc
> nces@iiug.org
> Subject
> Re: adding rowids to a frag'd table
> 11/03/2009 10:55
> AM
>
>
>
>
>
>
>
>
> Darren_Jacobs@carmax.com wrote:
>> Hey Art,
>>
>> Thanks for the response.
>>
>> My ltxhwm is set at 45 which is approx 54 logs. The reason I don't
> supsect
>> I'm filling the logs is because how fast it gets into the long trans.
>> Also, the table has been altered to raw. Which shouldn't be logged
> unless
>> by explicitly placing it in a trans to lock the table in exclusive is
>> causing the raw table to be logged.
>>
>>
>> ========================
>> Darren Jacobs
>> Sr Database Analyst
>> Darren_Jacobs@carmax.com
>> 804.747.0422 x3221
>> ========================
>>
>>
>>
>
>> Art Kagel
>
>> <art.kagel@gmail.
>
>> com>
> To
>> Sent by: Darren_Jacobs@carmax.com
>
>> informix-list-bou
> cc
>> nces@iiug.org informix-list@iiug.org
>
> Subject
>> Re: adding rowids to a frag'd
> table
>> 11/03/2009 09:41
>
>> AM
>
>
>
>
>
>>
>>
>>
>> Long transaction rollback is only caused by filling the logical logs past
>> the LTXHWM
>>
>> Art S. Kagel
>> Oninit (www.oninit.com)
>> IIUG Board of Directors (art@iiug.org)
>>
>> Disclaimer: Please keep in mind that my own opinions are my own opinions
>> and do not reflect on my employer, Oninit, the IIUG, nor any other
>> organization with which I am associated either explicitly or implicitly.
>> Neither do those opinions reflect those of other individuals affiliated
>> with any entity with which I am affiliated nor those of the entities
>> themselves.
>>
>>
>>
>> On Tue, Nov 3, 2009 at 8:23 AM, <Darren_Jacobs@carmax.com> wrote:
>>
>> IDS 10 FC8
>> HPUX 11
>>
>> Greetings
>>
>> I'm in the process of altering a 22 mil row table frag'd across 4
>> dbspaces
>> to accomodate connectivity through windoze SSIS which is dependant upon
>> the
>> rowid. Why...I don't know....is sqeal!
>>
>> Anyway, I keep running into a long trans. I've dropped all the
> indexes,
>> altered the table to raw, locked the table in exclusive to avoid
> blowing
>> the locks, and I still run into a long trans. This is a test system
> I'm
>> running this on so there is little or no other activity running. There
>> are
>> approx 120 logs at 100 meg each. I know I'm not filling 50% of the
> logs.
>> I'm also not blowing space.
>>
>> Other than unloading, dropping, recreating the table with rowids, does
>> anyone have other ideas? or what may be causing the long trans?
>>
>> Thanks!
>>
>> ========================
>> Darren Jacobs
>> Sr Database Analyst
>> Darren_Jacobs@carmax.com
>> 804.747.0422 x3221
>> ========================
>>
>> _______________________________________________
>> Informix-list mailing list
>> Informix-list@iiug.org
>> http://www.iiug.org/mailman/listinfo/informix-list
>> _______________________________________________
>> Informix-list mailing list
>> Informix-list@iiug.org
>> http://www.iiug.org/mailman/listinfo/informix-list
>>
>>
>
> Just some odd comments :
>
> 1. Are all your logical logs the same size?
>
> 2. Have you decent extent size allocations?
>
> 3. What is the actual statement you are running?
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
>
How long does it take to get into ltx? How many logs do the system
consume during that time? Note that a LTX happens when the logs consumed
after "begin work" hit LTX. It doesn't mean your TX is consuming the
logs. If this is the problem, you can probably get away by not using an
explicit transaction.
If your concern is that someone may try to use the table, please
consider renaming it or revoking the appropriate privileges.
Regards.