IDS Forum

Re: RE: Checkpoints slowing down the insert on....

Posted By: NITIN MATHUR
Date: Thursday, 3 April 2014, at 9:57 a.m.

In Response To: Re: RE: Checkpoints slowing down the insert on.... (david@smooth1.co.uk)

Thanks David for the response.

Currently we have 7 indexes on this table, which is fragmented by round-robin (4 dbspces), please see the complete schema

create table "informix".t_quote_history
(

qh_seq serial not null ,

qh_timestamp datetime year to fraction(3),

qh_userid varchar(25),

qh_update_type integer,

qh_id integer not null ,

qh_security integer not null ,

qh_trader integer not null ,

qh_upd_seqnum integer,

qh_montage_rank_seqnum integer,

qh_tstamp datetime year to fraction(3),

qh_currency varchar(3),

qh_bidpricetype varchar(3),

qh_bidprice decimal(11,5),

qh_bidquantity integer,

qh_askpricetype varchar(3),

qh_askprice decimal(11,5),

qh_askquantity integer,

qh_open smallint,

qh_unsolicited smallint,

qh_lockcross smallint,

qh_otcbb smallint,

qh_source_node_cd varchar(4),

qh_nqbsecid integer not null ,

qh_symbol varchar(10),

qh_shortname varchar(25),

qh_trid char(8),

qh_mmid char(5),

qh_inbid_mmid char(5),

qh_inask_mmid char(5),

qh_inside_type integer,

qh_bidtstamp datetime year to fraction(3) not null ,

qh_asktstamp datetime year to fraction(3) not null ,

qh_bid_qap_rt integer

default 0,

qh_ask_qap_rt integer

default 0,

qh_publishtime_ts datetime year to fraction(3)

default current year to fraction(3),

qh_lockcross_occurred_in integer

default 0,

qh_inside_seqnum integer

default 0,

qh_origtime_ts datetime year to fraction(3)
)
fragment by round robin in t_quote_spc01 , t_quote_spc02 , t_quote_spc03

, t_quote_spc04
extent size 1000000 next size 50000 lock mode row;
create unique index "informix".tqh_idx1 on "informix".t_quote_history

(qh_timestamp,qh_upd_seqnum) using btree in t_quote_indx01;

create index "informix".tqh_idx10 on "informix".t_quote_history

(qh_seq) using btree in index1;
create index "informix".tqh_idx2 on "informix".t_quote_history

(qh_tstamp) using btree in t_quote_indx02;
create index "informix".tqh_idx3 on "informix".t_quote_history

(qh_mmid,qh_nqbsecid,qh_timestamp) using btree in t_quote_indx02;

create index "informix".tqh_idx4 on "informix".t_quote_history

(qh_mmid,qh_timestamp) using btree in t_quote_indx02;
create index "informix".tqh_idx7 on "informix".t_quote_history

(qh_timestamp,qh_nqbsecid,qh_update_type) using btree in

t_quote_indx01;
create index "informix".tqh_idx9 on "informix".t_quote_history

(qh_id) using btree in t_quote_indx01;
alter table "informix".t_quote_history add constraint primary

key (qh_timestamp,qh_upd_seqnum) constraint "informix".pk_f_tqh

Below are the IO numbers for last 2 days (almost)

Dbsname Table Or Index Isreads Bufreads Pagreads Iswrites Bufwrites Pagwrites
eqs_prod tqh_idx9 86228411 202418104 1782656 26146723 27370023 5883727
eqs_prod tqh_idx3 3487 142789159 3558642 26146653 30035704 3873674
eqs_prod t_quote_history 135337013 403912235 84057636 7076534 8719552 727112
eqs_prod t_quote_history 135340433 404665601 84099548 7076075 8720026 727103
eqs_prod t_quote_history 135338291 404826411 84082794 7075259 8719047 727025
eqs_prod t_quote_history 135325075 405013796 84045494 7073991 8719412 726904
eqs_prod tqh_idx7 0 141466885 3061330 26146723 29025238 569114
eqs_prod tqh_idx2 86228411 224519553 2441969 26146653 27311817 464807
eqs_prod tqh_idx4 86228411 228143077 3281548 26146664 28497666 416872
eqs_prod tqh_idx1 86739423 232305692 7488025 26146723 27980157 367392
eqs_prod tqh_idx10 86228415 197928976 2190271 26145912 27277343 226993

I am planning to isolate tqh_idx9 on a dbspace.

Let me know if you can think of something else.

regards,

Nitin

Messages In This Thread

• Checkpoints slowing down the insert on Primary
  NITIN MATHUR -- Tuesday, 18 March 2014, at 12:01 p.m.
  • RE: Checkpoints slowing down the insert on Primary
    Alexandre Marini -- Tuesday, 18 March 2014, at 1:50 p.m.
    • Re: RE: Checkpoints slowing down the insert on Pri
      NITIN MATHUR -- Tuesday, 18 March 2014, at 2:25 p.m.
      • RE: Checkpoints slowing down the insert on....
        Alexandre Marini -- Tuesday, 18 March 2014, at 2:45 p.m.
        • Re: RE: Checkpoints slowing down the insert on....
          NITIN MATHUR -- Wednesday, 2 April 2014, at 10:24 a.m.
          • RE: Checkpoints slowing down the insert on....
            Alexandre Marini -- Wednesday, 2 April 2014, at 2:09 p.m.
            • Re: RE: Checkpoints slowing down the insert on....
              NITIN MATHUR -- Wednesday, 2 April 2014, at 3:21 p.m.
              • Re: RE: Checkpoints slowing down the insert on....
                david@smooth1.co.uk -- Wednesday, 2 April 2014, at 4:32 p.m.
                • Re: RE: Checkpoints slowing down the insert on....
                  NITIN MATHUR -- Thursday, 3 April 2014, at 9:57 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.