Re: Informix 7.1 Online -- Technical Question

scottm@minerva.robadome.com (Scott T Miller) writes: >Hi, >I wrote an article about a week ago asking some questions about Informix 7.1 >performance, and whether the system could be processor-bound. I'm using >an RS/6000 running AIX 3.1.2 with 128MB of RAM, and a Shared memory ceiling of 50MB. >I'm developing a model of how our system performs given different client >configurations, and have found that the system is indeed processor bound. >So, progressing heartily ahead, I came up with a formula that I thought would >express the amount of work that the Informix database was doing for each >update to the database tables that we perform, and used this formula to predict the >number of CPU seconds Informix would require to process our data. >This model has proven very accurate. >I use the assumption that each insertion into the databases incurs a cost >roughly equivalent to the LOG of the number of rows in the table (this follows >naturally from my assumption that the depth of the tree which is index searched >is logarithmic with respect to the number of elements in the table). >BUT, what I have learned through using "oncheck -pT", is that most of our tables >have indices of DEPTH 3, (indicating a very large number of keys in each tree >level) >Given this, I would expect that the amount of time to >find the item in the index would be roughly constant, assuming the next node >traversed in the index tree is chosen by a constant time hash function, or some >such. >The burning question then, to Informix personnel, gurus, or anyone, is : >WHY would the amount of time to perform an operation on a table vary with the >logarithm of the number of rows, if the indexes used to search the tree are >roughly the same depth? For most database operations, disk accesses consume the most amount of time. Most tables quickly jump to 3 level indexes, but only relatively large tables require 4 or more levels. The question is, how many of your index and row accesses are cached? Is it possible that your larger tables require much more frequent disk io to retrieve index and data row pages? As for the question of being CPU bound, I've seen this occur when all data acces is cached ( perhaps scanning a very small table frequently ), or when there is a large number of users competing for resources ( spinning on a latch, etc. ) Check your cache percentages on transactions against different sized tables, and watch your latch wait statistics. I realize these are simplistic explanations, but without hard data it's impossible to say for sure what's going on. Jim >Am I missing something here? Is there another process involved that is O(logn) ?? >Thanks for any help in advance, >Scott Miller >Siemens Rolm Communications