Index fragmentation
Neil Truby (IDS 10.00.FC9, Solaris 10) hit the 16GB partition limit on a single index and didn't want to fragment it by expression (round-robin isn't available for indexes). Art Kagel explained the limit is 16 million pages, not 16GB, so moving the index into a dbspace with a larger page size (e.g. 4K+) raises the ceiling; he added that wider pages usually make indexes faster because the B+tree is flatter. On the side question of CREATE INDEX ONLINE, Richard Kofler was wary of it (see ONLIDX_MAXMEM, locking/concurrency caveats), while Davorin Kremenjas reported it necessary in HDR setups to avoid table-lock conflicts, but warned never to use it on temporary tables due to a checkpoint bug fixed in 10.00.xC11.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IDS 10.0FC9 on Solaris 10 I have a site where an index has hot the 16g limit for a tablespace. It seems from the fine manual that you cannot fragment an index across dbspaces round-robin (I suppose this is meaningless), only by expression. But I don't really want to do this; I just want to get around the 16g limit. Any ideas? Is it true that the "online index build" facility in IDS 10 is actually nothing of the sort? thx N
Put the index into a 4K larger pagesize dbspace. The limit is on 16 million PAGES not KB, so larger pages hold bigger index and table partitions without fragmenting. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Jun 9, 2009 at 3:59 PM, Neil Truby <neil.truby@ardenta.com> wrote: > IDS 10.0FC9 on Solaris 10 > > I have a site where an index has hot the 16g limit for a tablespace. > It seems from the fine manual that you cannot fragment an index across > dbspaces round-robin (I suppose this is meaningless), only by expression. > But I don't really want to do this; I just want to get around the 16g > limit. > Any ideas? > > Is it true that the "online index build" facility in IDS 10 is actually > nothing of the sort? > > thx > N > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >
Neil Truby schrieb: > IDS 10.0FC9 on Solaris 10 > > I have a site where an index has hot the 16g limit for a tablespace. > It seems from the fine manual that you cannot fragment an index across > dbspaces round-robin (I suppose this is meaningless), only by > expression. But I don't really want to do this; I just want to get > around the 16g limit. Any ideas? > > Is it true that the "online index build" facility in IDS 10 is actually > nothing of the sort? > > thx > N Hi Neil, I must admit I did not fully understand what you mean with the last sentence, but here goes: CREATE INDEX ... ONLINE and DROP INDEX ... ONLINE we do not recommend, and therefore I never did sufficient testing. I think (pls, whoever knows more about it, do correct me if I am wrong!) it is a feature for small indexes. Read about the env variable ONLIDX_MAXMEM, and see the default ... I *think* one cannot use memory under control of the MGM, aka PDQ memory, when using the 'ONLINE' keyword. The difference is, that it does not put an exclusive lock on the table, whereas a normal CREATE INDEX does need exclusive locking. I did not test what happens with an update changing values of the column which is used in the index. The SQL syntax docs V10 has some info on SET LOCK MODE TO WAIT, deadlock resolution and DROP INDEX. So there *are* concurrency and race condition effects. After seeing this back in 2007, we decided not to test it until there is need. But never was. To test things like this is definitely not an easy task for a rainy sunday afternoon! dic_k -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe
> > "Art Kagel" <art.kagel@gmail.com> wrote in message > > news:mailman.21.1244578688.4791.informix-list@iiug.org... >> Put the index into a 4K larger pagesize dbspace. The limit is on 16 >> million PAGES not KB, so larger pages hold bigger index and table >> partitions without fragmenting. Surely I'd need to do a bit of testing? There must be a performance implication ...?
Yes. YMMV, but the vast majority of indexes run FASTER on wider pages because more nodes are on a page so the index strucuture is flatter making the B+Tree more efficient to search! Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Jun 9, 2009 at 5:49 PM, Neil Truby <neil.truby@ardenta.com> wrote: > > > "Art Kagel" <art.kagel@gmail.com> wrote in message > > > news:mailman.21.1244578688.4791.informix-list@iiug.org... > >> Put the index into a 4K larger pagesize dbspace. The limit is on 16 > >> million PAGES not KB, so larger pages hold bigger index and table > >> partitions without fragmenting. > > Surely I'd need to do a bit of testing? There must be a performance > implication ...? > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >
> Is it true that the "online index build" facility in IDS 10 is actually
> nothing of the sort?
Can't tell about the performance, we never timed online build vs the
usual one, but we found out we have to create indexes online in HDR
environment when upgrading the database schema during scheduled
downtimes. This goes for indexes on existing, populated tables,
especially big tables, not for new, empty tables.
The reason for this was if it is NOT an online build then the engine
will hold a lock on the table (or at least have the table partition
open) while it's transferring the index to HDR secondary (although the
create index statement finished on primary already). This causes non-exclusive access to the table and if there are other statements in the
script immediately after an index build (alter on the same table, for
example) it will fail. This happens rarely, but it happens. After we
started building all indexes online the problem never reappeared.
Might be just the coincidence, but it makes us happy.
HTH
Davorin
BTW, there's an exception to the rule. NEVER build indexes online on
temporary tables, there's a confirmed bug in the engine which makes it
checkpoint every few seconds, to be fixed in IDS10xC11.