Informix

 View Only
  • 1.  index size

    Posted Fri December 10, 2021 06:37 AM

    Hi,

    I am having some performance problems with last versions 12.10 and 14.10 over customers.

    From what I have seen over systems I don't find any problem over OS and informix, I suspect the latency is related to some modifications that my colleagues made over some tables and indexes.

    Can someone give me some help to get information about calculating index sizes and performance-related problems?

    Thanks for any help,

    SP



    ------------------------------
    Sergio Peres
    AIRC
    Coimbra
    ------------------------------


  • 2.  RE: index size

    Posted Fri December 10, 2021 07:57 AM
    Sergio:

    Tough one to diagnose with no information. Here are some things you can look at:
    • onstat -C all and look for indexes with massive "hits" indicating an index that has many deleted entries and so has become inefficient while the btree cleaners are not keeping up with fixing that issue. Those are candidates for a manual rebuild.
    • Look for tables and indexes placed on the same structures (even different dbspaces but placed on the same storage), especially if they will be involved in the same queries. Move one or the other if you can. Most important if they in the same dbspace(s).
    • Look for tables that have grown so large that the default data distributions are not sufficiently detailed. Medium stats may need more buckets and a larger SAMPLING size. High distributions may need more buckets.
    • Check the bufwaits ratio. If their user base has grown there may be contention for the LRU queues now that wasn't there in the past. More LRUs will fix that.

    That's what I can offer off the top of my head.

    I hate to go into sales mode, but, if your client wants, I can do a full Health Check for them.

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: index size

    Posted Fri December 10, 2021 10:10 AM

    Thanks Art,

    As the problem is global I think my company is the best client... :)

    I will give the information about your offer.

    Best regards,

    SP



    ------------------------------
    Sergio Peres
    AIRC
    Coimbra
    ------------------------------