Solved

Informix Monitor and Performance

Posted on 2002-07-03
5
493 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:NetoMan
5 Comments
 
LVL 32

Accepted Solution

by:
bhess1 earned 200 total points
ID: 7128243
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>
   EXTENT SIZE 256
   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.)
0
 

Expert Comment

by:defcon1
ID: 7212520

 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
0
 

Expert Comment

by:modulo
ID: 7425948
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 !

modulo

Community Support Moderator
Experts Exchange
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7486054

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.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 1

Expert Comment

by:Computer101
ID: 7516887
Comment from expert accepted as answer

Computer101
E-E Admin
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now