await_MC3 ??? How release a thread?

Hi Folks, We migrate a server to 11.50 FC4 on Solaris 9. Some queries which runs on a table stored in 17 dbspaces by round robin with detached indexes are running very slow. If we search only the index it runs well, but if i increase a column who is not in the index, it runs very slow select index where index in (1,2,3..) -> FAST select index, column where index in (1,2,3,...) -> to SLOW. seems like the thread is waiting some resources. looking the output from onstat -g con, this thread (scan_3.1) are waiting for await_MC3 which means "Producer thread(s) are waiting to coordinate their status with their Master Consumer thread" What this really means? how can I manage that? Are there some tasks That i can do to release this thread ? Thanks for you help Miguel Carbone +55 11 96347103 +55 11 78327160 MC IT Solutions Chief Technical Officer miguel@mcsoftware.com.br IIUG - International Informix Users Group Board of Directors miguel@iiug.org --Apple-Mail-415--100926210

Are you setting PDQ? Did you do it before migration? Regards On Tue, Jul 28, 2009 at 2:55 PM, Miguel Carbone <miguel@mcsoftware.com.br>wrote: > Hi Folks, > > We migrate a server to 11.50 FC4 on Solaris 9. > > Some queries which runs on a table stored in 17 dbspaces by round > robin with detached indexes are running very slow. > > If we search only the index it runs well, but if i increase a column > who is not in the index, it runs very slow > > select index where index in (1,2,3..) -> FAST > select index, column where index in (1,2,3,...) -> to SLOW.> > seems like the thread is waiting some resources. > > looking the output from onstat -g con, this thread (scan_3.1) are > waiting for await_MC3 which means "Producer thread(s) are waiting to > coordinate their status with their Master Consumer thread" > > What this really means? how can I manage that? Are there some tasks > That i can do to release this thread ? > > Thanks for you help > > Miguel Carbone > +55 11 96347103 > +55 11 78327160 > > MC IT Solutions > Chief Technical Officer > miguel@mcsoftware.com.br > > IIUG - International Informix Users Group > Board of Directors > miguel@iiug.org > > --Apple-Mail-415--100926210 > > > > ******************************************************************************* > 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... --000e0cd51b78b1ca44046fc4a134

Fernando, Yes, PDQ is set, now and before. Miguel Carbone +55 11 96347103 +55 11 78327160 MC IT Solutions Chief Technical Officer miguel@mcsoftware.com.br IIUG - International Informix Users Group Board of Directors miguel@iiug.org On 28/07/2009, at 11:10, Fernando Nunes wrote: > Are you setting PDQ? Did you do it before migration? > Regards > > On Tue, Jul 28, 2009 at 2:55 PM, Miguel Carbone > <miguel@mcsoftware.com.br>wrote: > >> Hi Folks, >> >> We migrate a server to 11.50 FC4 on Solaris 9. >> >> Some queries which runs on a table stored in 17 dbspaces by round >> robin with detached indexes are running very slow. >> >> If we search only the index it runs well, but if i increase a column >> who is not in the index, it runs very slow >> >> select index where index in (1,2,3..) -> FAST >> select index, column where index in (1,2,3,...) -> to SLOW.>> >> seems like the thread is waiting some resources. >> >> looking the output from onstat -g con, this thread (scan_3.1) are >> waiting for await_MC3 which means "Producer thread(s) are waiting to >> coordinate their status with their Master Consumer thread" >> >> What this really means? how can I manage that? Are there some tasks >> That i can do to release this thread ? >> >> Thanks for you help >> >> Miguel Carbone >> +55 11 96347103 >> +55 11 78327160 >> >> MC IT Solutions >> Chief Technical Officer >> miguel@mcsoftware.com.br >> >> IIUG - International Informix Users Group >> Board of Directors >> miguel@iiug.org >> >> --Apple-Mail-415--100926210 >> >> >> >> > ******************************************************************************* >> 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... > > --000e0cd51b78b1ca44046fc4a134 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --Apple-Mail-419--99536718

And you have enough PDQ resources configured? Any hints from onstat -g mgm? What is the status of the other threads when the query is running? And what do you mean by detached index? I now this may sound weird... But sometimes we confuse indexes in their own tablespace with indexes that don't follow the table fragmentation scheme. And yes, you can have non unique indexes on round robin tables that follow the table fragmentation scheme (round robin). The other option is to have an index with just one fragment. Can you still check is this is the same as before (it will be if it was an inplace migration, but it may have change if you exported/imported). Regards. On Tue, Jul 28, 2009 at 3:16 PM, Miguel Carbone <miguel@mcsoftware.com.br>wrote: > Fernando, > > Yes, PDQ is set, now and before. > > Miguel Carbone > +55 11 96347103 > +55 11 78327160 > > MC IT Solutions > Chief Technical Officer > miguel@mcsoftware.com.br > > IIUG - International Informix Users Group > Board of Directors > miguel@iiug.org > > On 28/07/2009, at 11:10, Fernando Nunes wrote: > > > Are you setting PDQ? Did you do it before migration? > > Regards > > > > On Tue, Jul 28, 2009 at 2:55 PM, Miguel Carbone > > <miguel@mcsoftware.com.br>wrote: > > > >> Hi Folks, > >> > >> We migrate a server to 11.50 FC4 on Solaris 9. > >> > >> Some queries which runs on a table stored in 17 dbspaces by round > >> robin with detached indexes are running very slow. > >> > >> If we search only the index it runs well, but if i increase a column > >> who is not in the index, it runs very slow > >> > >> select index where index in (1,2,3..) -> FAST > >> select index, column where index in (1,2,3,...) -> to SLOW.> >> > >> seems like the thread is waiting some resources. > >> > >> looking the output from onstat -g con, this thread (scan_3.1) are > >> waiting for await_MC3 which means "Producer thread(s) are waiting to > >> coordinate their status with their Master Consumer thread" > >> > >> What this really means? how can I manage that? Are there some tasks > >> That i can do to release this thread ? > >> > >> Thanks for you help > >> > >> Miguel Carbone > >> +55 11 96347103 > >> +55 11 78327160 > >> > >> MC IT Solutions > >> Chief Technical Officer > >> miguel@mcsoftware.com.br > >> > >> IIUG - International Informix Users Group > >> Board of Directors > >> miguel@iiug.org > >> > >> --Apple-Mail-415--100926210 > >> > >> > >> > >> > > > > ******************************************************************************* > >> 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... > > > > --000e0cd51b78b1ca44046fc4a134 > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --Apple-Mail-419--99536718 > > > > ******************************************************************************* > 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... --0015174c33067ea1de046fc4d6f4

I think seeing a query plan would help maybe... (set explain). MM

Fernando. My table: Minha Tabela { TABLE "informix".t_retornodet row size = 328 number of columns = 46 index size = 51 } create table "informix".t_retornodet ( num_seqenvio integer not null filtering , num_seqreglotremdt integer not null filtering , posicao_retorno serial not null filtering , num_seqremessa integer not null filtering , num_seqreglotretdt integer not null filtering , num_termcobretdt varchar(21), cod_evento char(1) not null filtering , cod_motivo char(5), dat_eventoretdt datetime year to day not null filtering , num_reclretdt varchar(15), num_contrprcretdt varchar(18), num_nfretdt decimal(12,0), cod_serienfretdt char(2), vlr_brutoretdt decimal(16,5), dat_vencctaretdt datetime year to day, uf_nfretdt char(2), dat_emissctaretdt datetime year to day, nom_arqremretdt char(35), des_fillerretdt varchar(34), num_assinaaretdt varchar(21) not null filtering , num_assinabretdt varchar(23) not null filtering , dat_chamadaretdt datetime year to second not null filtering , num_duratariretdt integer not null filtering , vlr_chamadaretdt decimal(16,5) not null filtering , cod_registroretdt char(1), cod_paisretdt char(3), cod_naturezaremdt char(3), cod_eotoriremrg char(3), cod_eotdesremrg char(3), num_cnlaremdt integer, num_cnlbremdt integer, num_durarealremdt integer, num_grupohoraremdt char(1), num_degrauremdt char(2), num_areavisitremdt char(2), cod_refatremdt char(2), cod_cspremdt char(2), cod_servicoremdt char(5), cod_eotreceitaremd char(3), num_assinacremdt varchar(10), cod_tpchamadaremd char(2), idn_parttaifremdt char(1), flg_portabilidade char(1) not null filtering , eot_doadora char(3) not null filtering , eot_receptora char(3) not null filtering , dat_portabilidade datetime year to second ) extent size 20000000 next size 10000000 lock mode row; alter fragment on table "informix".t_retornodet init fragment by round robin in cobretdbs01 , cobretdbs02 , cobretdbs03 , cobretdbs04 , cobretdbs05 , cobretdbs06 , cobretdbs07 , cobretdbs08 , cobretdbs09 , cobretdbs10 , cobretdbs11 , cobretdbs12 , cobretdbs13 , cobretdbs14 , cobretdbs15 , cobretdbs16 , cobretdbs17 ; revoke all on "informix".t_retornodet from "public" as "informix"; start violations table for "informix".t_retornodet using t_retornodet_vio, t_retornodet_dia; revoke all on "informix".t_retornodet_vio from "public" as "informix" ; revoke all on "informix".t_retornodet_dia from "public" as "informix"; create unique index "informix".ix_t_retornodet01 on "informix".t_retornodet (num_seqenvio,num_seqreglotremdt,posicao_retorno) using btree in cobretidx05 filtering ; create index "informix".ix_t_retornodet02 on "informix".t_retornodet (num_seqremessa) using btree in cobretidx06; create index "informix".ix_t_retornodet03 on "informix".t_retornodet (num_seqenvio,num_seqreglotremdt) using btree in cobretidx07; alter table "informix".t_retornodet add constraint primary key (num_seqenvio,num_seqreglotremdt,posicao_retorno) constraint "informix".pk_tretornodet filtering ; Number of records select count(*) from t_retornodet; (count(*)) 1957196606 Test 1, only index : database c31db31; SET ENVIRONMENT OPTCOMPIND '1'; set pdqpriority high; set explain on; set explain file to 'query5.sql.out'; select current from sysmaster:sysdual; select num_seqremessa from t_retornodet where num_seqremessa in ( 615136,-- 389582 614631,-- 315137 591146,-- 301714 586517 -- 242284 ) into temp tmpok with no log; select current from sysmaster:sysdual; select count(*) from tmpok Output teste 1 (expression) 2009-07-24 17:26:40.000 (expression) 2009-07-24 17:26:54.000 (count(*)) 1248717 Test 2: database c31db31; SET ENVIRONMENT OPTCOMPIND '1'; set pdqpriority high; set explain on; set explain file to 'query6.sql.out'; select current from sysmaster:sysdual; select num_seqremessa, cod_evento from t_retornodet where num_seqremessa in ( 615136,-- 389582 614631,-- 315137 591146,-- 301714 586517 -- 242284 ) into temp tmpok1 with no log; select current from sysmaster:sysdual; select count(*) from tmpok1 Output Test 2 (expression) 2009-07-24 17:07:23.000 (expression) 2009-07-24 17:20:16.000 (count(*)) 1248717 When i look to the threads i saw: output from onstat -u 4bdc30918 Y------ 28335 fhenriqu - 4b9208b60 0 4 2459 0 output from onstat –g com 1038 4b9208b60 await_MC3 571818 62070 output from onstat -g ath 571818 4bee0a028 4bdc30918 1 cond wait await_MC3 12cpu scan_3.1 Thread CPU Info: 571818 scan_3.1 12cpu 07/26 21:49:27 4.3307 32908 cond wait await_MC3 Conditions with waiters: cid addr name waiter waittime 1038 4b9208b60 await_MC3 571818 62070 Obrigado, Na proxima ustilizamos a lista em portugues, ok? Miguel Carbone +55 11 96347103 +55 11 78327160 MC IT Solutions Chief Technical Officer miguel@mcsoftware.com.br IIUG - International Informix Users Group Board of Directors miguel@iiug.org On 28/07/2009, at 11:25, Fernando Nunes wrote: > And you have enough PDQ resources configured? > Any hints from onstat -g mgm? > What is the status of the other threads when the query is running? > And what do you mean by detached index? I now this may sound > weird... But > sometimes we confuse indexes in their own tablespace with indexes > that don't > follow the table fragmentation scheme. And yes, you can have non > unique > indexes on round robin tables that follow the table fragmentation > scheme > (round robin). The other option is to have an index with just one > fragment. > Can you still check is this is the same as before (it will be if it > was an > inplace migration, but it may have change if you exported/imported). > > Regards. > > On Tue, Jul 28, 2009 at 3:16 PM, Miguel Carbone > <miguel@mcsoftware.com.br>wrote: > >> Fernando, >> >> Yes, PDQ is set, now and before. >> >> Miguel Carbone >> +55 11 96347103 >> +55 11 78327160 >> >> MC IT Solutions >> Chief Technical Officer >> miguel@mcsoftware.com.br >> >> IIUG - International Informix Users Group >> Board of Directors >> miguel@iiug.org >> >> On 28/07/2009, at 11:10, Fernando Nunes wrote:

As John wrote the engine has to do much more work.... Because it will in the best case have to access more than 1M rows of data to get the other column. But you should take a look at both explains. If both say that the engine is using the same index, than the increase in time is the overhead of having to access the data pages. But it can happen that it's changing from an index into a full scan. Quanto à lista em Português, é simpático, mas em Inglês o feedback vai ser muito mehor/maior... Isso faz-me lembrar que tenho de referir no meu blog dois blogs que existem no Brasil cheios de informação! Cumprimentos/Regards e parabéns pela eleição. On Tue, Jul 28, 2009 at 4:14 PM, Miguel Carbone <miguel@mcsoftware.com.br>wrote: > Fernando. > > My table: Minha Tabela > > { TABLE "informix".t_retornodet row size = 328 number of columns = 46 > index size = 51 } > > create table "informix".t_retornodet > > ( > > num_seqenvio integer not null filtering , > > num_seqreglotremdt integer not null filtering , > > posicao_retorno serial not null filtering , > > num_seqremessa integer not null filtering , > > num_seqreglotretdt integer not null filtering , > > num_termcobretdt varchar(21), > > cod_evento char(1) not null filtering , > > cod_motivo char(5), > > dat_eventoretdt datetime year to day not null filtering , > > num_reclretdt varchar(15), > > num_contrprcretdt varchar(18), > > num_nfretdt decimal(12,0), > > cod_serienfretdt char(2), > > vlr_brutoretdt decimal(16,5), > > dat_vencctaretdt datetime year to day, > > uf_nfretdt char(2), > > dat_emissctaretdt datetime year to day, > > nom_arqremretdt char(35), > > des_fillerretdt varchar(34), > > num_assinaaretdt varchar(21) not null filtering , > > num_assinabretdt varchar(23) not null filtering , > > dat_chamadaretdt datetime year to second not null filtering , > > num_duratariretdt integer not null filtering , > > vlr_chamadaretdt decimal(16,5) not null filtering , > > cod_registroretdt char(1), > > cod_paisretdt char(3), > > cod_naturezaremdt char(3), > > cod_eotoriremrg char(3), > > cod_eotdesremrg char(3), > > num_cnlaremdt integer, > > num_cnlbremdt integer, > > num_durarealremdt integer, > > num_grupohoraremdt char(1), > > num_degrauremdt char(2), > > num_areavisitremdt char(2), > > cod_refatremdt char(2), > > cod_cspremdt char(2), > > cod_servicoremdt char(5), > > cod_eotreceitaremd char(3), > > num_assinacremdt varchar(10), > > cod_tpchamadaremd char(2), > > idn_parttaifremdt char(1), > > flg_portabilidade char(1) not null filtering , > > eot_doadora char(3) not null filtering , > > eot_receptora char(3) not null filtering , > > dat_portabilidade datetime year to second > > ) extent size 20000000 next size 10000000 lock mode row; > > alter fragment on table "informix".t_retornodet init> > fragment by round robin in cobretdbs01 , cobretdbs02 , > cobretdbs03 , cobretdbs04 > > , cobretdbs05 , cobretdbs06 , cobretdbs07 , cobretdbs08 , > > cobretdbs09 , cobretdbs10 , cobretdbs11 , cobretdbs12 , > > cobretdbs13 , cobretdbs14 , cobretdbs15 , cobretdbs16 , cobretdbs17 > > ; > > revoke all on "informix".t_retornodet from "public" as "informix";> > start violations table for "informix".t_retornodet using > t_retornodet_vio, t_retornodet_dia; > > revoke all on "informix".t_retornodet_vio from "public" as "informix" ;> > revoke all on "informix".t_retornodet_dia from "public" as "informix";> > create unique index "informix".ix_t_retornodet01 on > "informix".t_retornodet > (num_seqenvio,num_seqreglotremdt,posicao_retorno) using btree in > cobretidx05 filtering ; > > create index "informix".ix_t_retornodet02 on "informix".t_retornodet > (num_seqremessa) using btree in cobretidx06; > > create index "informix".ix_t_retornodet03 on "informix".t_retornodet > (num_seqenvio,num_seqreglotremdt) using btree in cobretidx07; > > alter table "informix".t_retornodet add constraint primary key > (num_seqenvio,num_seqreglotremdt,posicao_retorno) constraint > "informix".pk_tretornodet filtering ; > > Number of records > > select count(*) from t_retornodet;> > (count(*)) > > 1957196606 > > Test 1, only index : > database c31db31;> > SET ENVIRONMENT OPTCOMPIND '1';> > set pdqpriority high;> > set explain on;> > set explain file to 'query5.sql.out';> > select current from sysmaster:sysdual;> > select num_seqremessa from t_retornodet> > where num_seqremessa in ( > > 615136,-- 389582 > > 614631,-- 315137 > > 591146,-- 301714 > > 586517 -- 242284 > > ) into temp tmpok with no log; > > select current from sysmaster:sysdual;> > select count(*) from tmpok> > Output teste 1 > (expression) > > 2009-07-24 17:26:40.000 > > (expression) > > 2009-07-24 17:26:54.000 > > (count(*)) > > 1248717 > > Test 2: > database c31db31;> > SET ENVIRONMENT OPTCOMPIND '1';> > set pdqpriority high;> > set explain on;> > set explain file to 'query6.sql.out';> > select current from sysmaster:sysdual;> > select num_seqremessa, cod_evento from t_retornodet> > where num_seqremessa in ( > > 615136,-- 389582 > > 614631,-- 315137 > > 591146,-- 301714 > > 586517 -- 242284 > > ) into temp tmpok1 with no log; > > select current from sysmaster:sysdual;> > select count(*) from tmpok1> > Output Test 2 > (expression) > > 2009-07-24 17:07:23.000 > > (expression) > > 2009-07-24 17:20:16.000 > > (count(*)) > > 1248717 > > When i look to the threads i saw: > > output from onstat -u > > 4bdc30918 Y------ 28335 fhenriqu - 4b9208b60 > 0 4 2459 0 > > output from onstat –g com > > 1038 4b9208b60 await_MC3 571818 62070 > > output from onstat -g ath > > 571818 4bee0a028 4bdc30918 1 cond wait await_MC3 > 12cpu scan_3.1 > > Thread CPU Info: > > 571818 scan_3.1 12cpu 07/26 21:49:27 4.3307 > 32908 cond wait await_MC3 > > Conditions with waiters: > cid addr name waiter waittime > > 1038 4b9208b60 await_MC3 571818 62070 > > Obrigado, Na proxima ustilizamos a lista em portugues, ok? > > Miguel Carbone > +55 11 96347103 > +55 11 78327160 > > MC IT Solutions > Chief Technical Officer > miguel@mcsoftware.com.br > > IIUG - International Informix Users Group > Board of Directors > miguel@iiug.org > > On 28/07/2009, at 11:25, Fernando Nunes wrote: > > > And you have enough PDQ resources configured? > > Any hints