Re: Bizarre sysmaster/IDS 10 problem

Further research shows that page size matters. If you're creating indexes in dbspaces with 2K pages, they "only" take up 5K of RSAM memory each. The numbers are much larger if you use 16K pages, as we do for our index spaces. Also, once you create the indexes, the memory is allocated forever and ever amen (or until you bounce). If I create 100 indexes, drop them, and then create another 100, the total memory profile will be for 200 created indexes. On the other hand, if I create 100 indexes, bounce the server, then create an additional 100, my rsam pool will only grow by the 100 new indexes I've just created; I'm not penalized for the 100 that previously existed. However, do a sysmaster query from systabnames, and it's all out the window. Now you allocate memory for every object in the system (and that memory will be separate from/in addition to the memory allocated when you created the objects). And oh yeah, when you end your session, all that juicy rsam memory stays put. Once it's been allocated, you never ever get it back. Thomas J. Girsch wrote: > My luck with support hasn't been THAT bad! Wow. > > In other news, I've done some more testing on this issue, and have > narrowed things down. The memory hole doesn't involve database objects > in general -- it involves indexes in particular. You can create tables > until you're blue in the face -- these only take about 1.5K of rsam > memory each. But when you start building indexes, THAT's the problem. I > broke out the "create table" and "create index" steps, and checked > individually. Creating tables, even in large numbers, only allocated at > most 1.8K per object. Creating indexes allocated _35K-40K_ of rsam pool > memory _per object_. Yikes! > > IBM support has identified a couple of older bugs that seem to match, > except that these bugs are supposed to be fix in 9.40.xC6, and we're on > 10.00.xC5. > > > Paul Watson wrote: >> You think that will help ? >> >> We've had a down/unuseable dev server for the last three weeks, if you >> run 'Drop Database' the instance cores. After three weeks IBM response >> is 'reinitialise - that will drop the database' Sometimes I wonder if >> they live in the same world as the rest of us :-)

Forgot to mention that I've replicated this on Solaris, AIX, and Linux, so it doesn't seem to be a platform-specific problem. Tom Girsch wrote: > Further research shows that page size matters. If you're creating > indexes in dbspaces with 2K pages, they "only" take up 5K of RSAM memory > each. The numbers are much larger if you use 16K pages, as we do for > our index spaces. > > Also, once you create the indexes, the memory is allocated forever and > ever amen (or until you bounce). If I create 100 indexes, drop them, > and then create another 100, the total memory profile will be for 200 > created indexes. On the other hand, if I create 100 indexes, bounce the > server, then create an additional 100, my rsam pool will only grow by > the 100 new indexes I've just created; I'm not penalized for the 100 > that previously existed. > > However, do a sysmaster query from systabnames, and it's all out the > window. Now you allocate memory for every object in the system (and > that memory will be separate from/in addition to the memory allocated > when you created the objects). And oh yeah, when you end your session, > all that juicy rsam memory stays put. Once it's been allocated, you > never ever get it back.

On Mar 22, 9:44 am, Tom Girsch <tgir...@NOSPAM.gmail.com> wrote: > Further research shows that page size matters. If you're creating > indexes in dbspaces with 2K pages, they "only" take up 5K of RSAM memory > each. The numbers are much larger if you use 16K pages, as we do for > our index spaces. > > Also, once you create the indexes, the memory is allocated forever and > ever amen (or until you bounce). If I create 100 indexes, drop them, > and then create another 100, the total memory profile will be for 200 > created indexes. On the other hand, if I create 100 indexes, bounce the > server, then create an additional 100, my rsam pool will only grow by > the 100 new indexes I've just created; I'm not penalized for the 100 > that previously existed. > > However, do a sysmaster query from systabnames, and it's all out the > window. Now you allocate memory for every object in the system (and > that memory will be separate from/in addition to the memory allocated > when you created the objects). And oh yeah, when you end your session, > all that juicy rsam memory stays put. Once it's been allocated, you > never ever get it back. > > Thomas J. Girsch wrote: > > My luck with support hasn't been THAT bad! Wow. > > > In other news, I've done some more testing on this issue, and have > > narrowed things down. The memory hole doesn't involve database objects > > in general -- it involves indexes in particular. You can create tables > > until you're blue in the face -- these only take about 1.5K of rsam > > memory each. But when you start building indexes, THAT's the problem. I > > broke out the "create table" and "create index" steps, and checked > > individually. Creating tables, even in large numbers, only allocated at > > most 1.8K per object. Creating indexes allocated _35K-40K_ of rsam pool > > memory _per object_. Yikes! > > > IBM support has identified a couple of older bugs that seem to match, > > except that these bugs are supposed to be fix in 9.40.xC6, and we're on > > 10.00.xC5. > > > Paul Watson wrote: > >> You think that will help ? > > >> We've had a down/unuseable dev server for the last three weeks, if you > >> run 'Drop Database' the instance cores. After three weeks IBM response > >> is 'reinitialise - that will drop the database' Sometimes I wonder if > >> they live in the same world as the rest of us :-) Fixed and known defects in ibm informix dynamic server 10.00.xc5 product release Date: 18 may 2006 Customer-reported bugs fixed in ibm informix dynamic server 10.00.xc4 115066 System with large number of tables can consume large amounts of memory in the rsam pool Known issues with 10.00.xc5 176165 When checking databases with lots of tables/indexes the rsam pool grows dramatically even activating the fix for bug 115066