• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 587
  • Last Modified:

Informix Monitor and Performance

Somebody knows for a Informix Tool or a 3rd Party Tool that allows me to prevent or show when Informix performance goes down.

Let me explain : we have recently find some tables (with a lot of records) that become slow in their access. if we download and upload the tables, their becomes fast.

So How can I know were is time to do this process (download and upload) or Do I Have to do this periodically (weekly, dialy) ?

any Tips or recommendations on performance tunning is welcome. Thanks in advance
1 Solution
Brendt HessSenior DBACommented:
The problem is probably related to the creation of the data extents.  This citation explains the issue:

"If a table is designed without specifying extent sizes, Informix uses a default of 16 pages for the first and 8 pages for each subsequent extent. There are mechanisms that automatically increase the first and next extent values based upon the number of extents in the table, but it is much better for the extent sizing to be specifically addressed by the designer. Extent sizing is done when the table is initially created using the following syntax:

CREATE TABLE tablename <field definitions>
   EXTENT SIZE <first extent size, in K>
   NEXT SIZE <next size, in K>

The problem with extent sizing is that it is almost totally invisible to the user. Use the following command to check the extent sizing of an existing database:

dbschema -d <databasename> -ss

This will dump the database schema to standard output. To dump it to file, do the following:

dbschema -d <databasename> -ss <name_of_output_file>

If you find that all of the extents are of size 16, your database has probably not been designed properly.

The problem with using this default extent sizing is that the data ends up located in noncontiguous areas of the disk. As additional extents are added, they are placed wherever the system has free space. In order to read the data, the database has to keep track of and search many separate areas of the disk.

The way to make this more efficient is to size the extents properly so that the entire table fits in eight or fewer contiguous extents. With monster tables of millions or billions of rows, this is obviously not possible, but it is still more efficient to make the extents very large for tables that get to be very big. This is more critical in earlier versions of IDS, which had a limit of about 200 extents for a table. Poor extent sizing in these earlier systems actually resulted in limiting the number of rows that a table could hold. "

So, what you probably want to do with these tables is re-create them with a larger extent sizing, e.g.

CREATE TABLE tablename <field definitions>
   NEXT SIZE 128

This will place the records in larger contiguous blocks, speeding overall access.

To monitor the need for a table download/upload, use the dbschema command above.  Record how many extents you have.  If the total number of extents increases over your last down/upload by some percentage (say, 15%) or absolute number, download and upload (you will have to monitor things to see when that might be needed - it depends on your DB layout, schema, extent size, etc.)

 Extents can be part of the problem; but I would tend to
think statistics are playing a major role.Depending on what version of Informix you are using, statistics which allow Informix to access your data quicker are built after a new load to the table. You can easily check to see if this is your bottleneck by rebuilding the indexes without a reload with the query(frominside ISQL): update statistics
medium.  It could take a while depending on your schema; so,you could run it over night.
 If this does not increase performance; you have to isolate
the chokepoint. If you have 7.x and above, there is a wonderful tool called "Onperf" . It has tools to isolate which of the 3 major areas of degradation: CPU, I/O i.e.,hard disks or space on HDs or the third, RAM memory- your computer's memory. There are Unix commands to observe all of these- "PS" is the the most general.For Informix 5.x
you can use "tbstat". The prescribed method is to run
the proccess(es) that are slowing your system then observe with one of your tools to see which area is being overutilized : CPU, I/O or Memory; then drill down from there. You really need a book or a good site the the IIUG
to get in to all the details of performance tuning.
 Creating indexes on the primary keys of your most active tables might "solve" your current problem. Once again
from within ISQL, run the query: CREATE UNIQUE INDEX your_index_name ON tablename (primary_key_name).
 Happy tuning
Dear: defcon1

I've rejected your proposed answer as Experts Exchange holds an experiment to work without the answer button.

See:        http://www.experts-exchange.com/jsp/communityNews.jsp
Paragraph: Site Update for Wednesday, November 06, 2002

By this rejection the Asker will be notified by mail and hopefully he will take his responsibility to finalize the question or post an additional comment.
The Asker sees a button beside every post which says "Accept This Comment As Answer" (including rejected answers) -- so if he/she thinks yours is the best, you'll be awarded the points and the grade.

Thanks !


Community Support Moderator
Experts Exchange

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - Answered by: bhess1
Please leave any comments here within the
next seven days.


Comment from expert accepted as answer

E-E Admin
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now