Indexes on large tables

Path: emory!att!cbfsb!cbnewsg.cb.att.com!ciesla From: ciesla@cbnewsg.cb.att.com (lawrence.w.ciesla) Newsgroups: comp.databases.informix Keywords: index OnLine Message-ID: <1991Nov12.135640.29040@cbfsb.att.com> Date: 12 Nov 91 13:56:40 GMT Sender: news@cbfsb.att.com Organization: AT&T Bell Laboratories We are trying to do some performance tests on a table of 6 million rows created without transactions, using Informix Online 4.1. During our attempts to create indexes, we are encountering some very strange behavior. We are trying to create two composite indexes. The first is a unique combination of three smallints and the second is the combination of three four character strings. Our first attempt was made with no changes to the default tunables, and the performance was horrible, about 103 hours for the first index. It was determined that one of the major bottlenecks was the fact that we were doing checkpoints about every 30 seconds. So a second attempt was made after increasing the size of the physical log to 50 meg and changing the checkpoint interval to approximately ten hours. This increased the performance on the first index to 29.5 hours, but the second index still took about 55 hours. A number of strange things were noticed on the second attempt. On the creation of the first index, checkpointing was nearly totally suppressed. Checkpoints occurred about once every five hours. During the creation of the second index, they occurred every twenty minutes. Why does this index use up physical log space so much quicker? Also, during our first attempt, we used considerable logical log space. We used up nearly 13 1.25 meg logical log files during the creation of each index. On the second attempt, we only used about half of one log during the creation of both indexes combined. Does the rate of checkpointing affect the size of the logical logs? Does the allocation of extents take up major space in the logical logs? Answers to any or all of these questions would be greatly appreciated. Responses can be mailed directly to ciesla@stairs.att.com or mtn@stairs.att.com. Thanks