RE: Do extents impact speed?
Yes, this many extents will almost certainly impact speed for the worst. At the very simple level, there would be a significant increase in disk seek time. Even the Performance Guide says: "Performance suffers when disk seeks for a table must span more than one extent, particularly for sequential scans." http://publib.boulder.ibm.com/epubs/html/25122960/perf189.htm#idx2131 An even worse scenario is when the extents are no longer ordered sequentially (i.e.. extent x, extent z, extent y). This means that, in order to read through a table or index, the disk head must move backward and forward, increasing the lag time. This can happen if some tables are dropped or reorganized while other tables continue to grow, a common cause of interleaving. With increases in storage technology, the number of extents where you start seeing problems may be higher than it once was. It may no longer be strictly true, as it once was, that the majority of the time spent performing an i/o operation is spent positioning for the i/o. However, the cost of positioning is still a factor, especially when you get into the numbers you are talking about. The more highly accessed the table is, the more important it is to keep the number of extents down. Ideally, of course, you want one extent per fragment. This may be impossible, especially with detached indexes. However, I get concerned when I see double-digit extents. At 100, I would definitely be thinking about reorganization strategies. I'm in the same boat. Many of our customers have even crossed the 200 extent mark. Some even reached the point where no further extents could be allocated for a table. Reorganizing their data was not fun, and involved, in some cases, significant downtimes. However, their performance did improve, in some cases drastically. Sincerely, Christopher Coleman Steering Committee President Kansas City Informix Users Group www.iiug.org/kciug Database Analyst Pharmacy Division Mediware Information Systems, Inc. sending to informix-list
"Christopher Coleman" <Christopher.Coleman@mediware.com> wrote in message news:1125707836.27d72252da0b7b38721d279905f4197d@teranews... > > Yes, this many extents will almost certainly impact speed for the worst. > At the very simple level, there would be a significant increase in disk > seek time. > > Even the Performance Guide says: "Performance suffers when disk seeks for > a table must span more than one extent, particularly for sequential > scans." > http://publib.boulder.ibm.com/epubs/html/25122960/perf189.htm#idx2131 > > An even worse scenario is when the extents are no longer ordered > sequentially (i.e.. extent x, extent z, extent y). This means that, in > order to read through a table or index, the disk head must move backward > and forward, increasing the lag time. This can happen if some tables are > dropped or reorganized while other tables continue to grow, a common cause > of interleaving. Isn't this making some fairly sweeping assummptions about the nature of the storage technology in use (I know you went on to make this point in part, but I'm playing Devil's Advocate as I'd like to canvass views on how much if at all the above applies to cached SANs). Many users these days - although possibly not Malcolm's - will be using sophisticated, cached SANs where there will be a high level of indeirection between the apparent and actual physical layout.
Christopher Coleman wrote: > Yes, this many extents will almost certainly impact speed for the worst. > At the very simple level, there would be a significant increase in disk > seek time. > > Even the Performance Guide says: "Performance suffers when disk seeks > for a table must span more than one extent, particularly for sequential scans." > http://publib.boulder.ibm.com/epubs/html/25122960/perf189.htm#idx2131 This said ... we've had the same mythology in Oracle and repeatedly proven that it is not true. So I wonder, with Informix, whether anyone has actually taken the time to verify this or whether it too is pure myth. In Oracle what we have discovered, though book after book repeats this myth: The reality of a multi-user database on a real-world server given real-world storage conditions ... it just doesn't matter. Has anyone actually verified this? -- Daniel A. Morgan http://www.psoug.org damorgan@x.washington.edu (replace x with u to respond)