Re: Bizarre sysmaster/IDS 10 problem

Hi, you can try to run variations of commands "onstat -g ..." commands, like options "ses [session id]", "mem ...", "afr ...", "ffr ..." and "ufr ...". Also see the usage of the onstat utility. My guess is, that this is caused by some sorting for the query. With that many databases and tables there's a lot of memory allocated. Though I'm not sure why it can't be freed afterwards. See if you can configure IDS to either use less sorting memory or use disk instead or SHM. Probably somewhere there is the difference between the migrated systems and the newly installed systems. The migrated systems have some configuration setting that's missing/different on the newly installed ones? Regards, Martin -- Martin Fuerderer IBM Informix Development Munich, Germany Information Management Sorry, but the following text is now required by German law: IBM Deutschland GmbH Vorsitzender des Aufsichtsrats: Hans Ulrich Maerki Geschäftsführung: Martin Jetter (Vorsitzender), Rudolf Bauer, Christian Diedrich, Christoph Grandpierre, Matthias Hartmann, Andreas Kerstan Sitz der Gesellschaft: Stuttgart Registergericht: Amtsgericht Stuttgart, HRB 14562 WEEE-Reg.-Nr. DE 99369940 informix-list-bounces@iiug.org wrote on 26.02.2007 05:47:07: > I'm encountering a weird problem with the sysmaster database, and was > wondering if anyone else had encountered anything similar. I have a > database server that has a LOT of databases on it, and each database has > LOTS of tables. We're talking hundreds of databases, each containing > hundreds of tables. The system is running IDS 10.00.FC5, on AIX 5.2. > On this system, whenever I do a query against systabextents, sysextents, > or sysptnext, the engine starts allocating shared memory like mad. It > takes less than a minute to fill my 400,000KB virtual segment, and then > continues to add additional ones until ultimately either SHMTOTAL is > hit, or the server runs out of memory. > > Aborting the query stops the memory from growing, but does not release > any of the memory that has been allocated. After I abort, I can have no > user sessions connected to at all, and the engine will still show > hundreds of megabytes of virtual shared memory used. An onmode -F > accomplishes nothing. > > I have now replicated this on two different boxes, but oddly I have not > been able to replicate on two others that are running the same engine > and OS version. The only differences I can think of between the systems > where I can replicate the issue and those where I cannot is that the > systems that exhibit the problem are more recent installations, first > initialized on IDS 10.00.FC5, whereas the ones where I cannot were > upgraded from IDS 9.x to 10; and that the systems where the problem > occurs have expanded chunk mode "always," as opposed to "enabled" on the > systems where it doesn't. > > Has anyone else encountered this behavior (or similar behavior?) > > Thanks, > > - TJG > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list

Martin Fuerderer wrote: > you can try to run variations of commands "onstat -g ..." commands, > like options "ses [session id]", "mem ...", "afr ...", "ffr ..." and "ufr > ...". > Also see the usage of the onstat utility. The memory usage doesn't show up as associated with any particular session, using onstat -g ses or onstat -g stm. I'll try a few of the others you suggest. > > My guess is, that this is caused by some sorting for the query. Doing a table->info->status in dbaccess does a sort? That's enough to cause the memory to go haywire. > With that many databases and tables there's a lot of memory allocated. Understood, but hundreds of megabytes? I've done join queries between multiple million-row-tables and not used near that much memory. > Probably somewhere there is the difference > between > the migrated systems and the newly installed systems. The migrated > systems have some configuration setting that's missing/different on > the newly installed ones? I don't think anything significant, but I'll check. Thanks, - TJG

> > you can try to run variations of commands "onstat -g ..." commands, > > like options "ses [session id]", "mem ...", "afr ...", "ffr ..." and "ufr > > ...". An onstat -g mem shows that all of the memory involved goes to the "rsam" pool. And although it doesn't free when sessions clear up, subsequent sessions that do the same thing <i>will</i> re-use the memory that has been allocated there. I have figured out that sysptnext isn't the only culprit. I can replicate against systabnames, too. I've also figured out that there DOES seem to be a relationship between the number of objects and the amount of memory that's used, and that the number of objects is what prevented me from being able to replicate on the older boxes (in one case because there were far fewer objects, and in the other case because the initial SHMVIRTSIZE was large enough to mask the problem). Just to give a sense of scale, on my dev box where I can replicate the problem, doing a SELECT COUNT(*) FROM systabnames takes several minutes, allocates about 60-70 MB of virtual shared memory (all in the "rsam" pool) and returns a count of around 55,000. On the larger box, where I first noticed the problem, there were probably five times as many records (or maybe even more) in systabnames, and it would hit the 4 GB SHMTOTAL value before finishing.

Hi, as someone has said before: If you are entitled to IBM Informix Technical Support, I guess this is a good occasion to make use of it. It seems you gathered enough information and even have a not so complicated way to reproduce it ... good pre-requisites for a technical solution of a technical problem. Contracts are a different story ... Regards, Martin -- Martin Fuerderer IBM Informix Development Munich, Germany Information Management Sorry, but the following text is now required by German law: IBM Deutschland GmbH Vorsitzender des Aufsichtsrats: Hans Ulrich Maerki Geschäftsführung: Martin Jetter (Vorsitzender), Rudolf Bauer, Christian Diedrich, Christoph Grandpierre, Matthias Hartmann, Andreas Kerstan Sitz der Gesellschaft: Stuttgart Registergericht: Amtsgericht Stuttgart, HRB 14562 WEEE-Reg.-Nr. DE 99369940 informix-list-bounces@iiug.org wrote on 26.02.2007 22:58:05: > > > > you can try to run variations of commands "onstat -g ..." commands, > > > like options "ses [session id]", "mem ...", "afr ...", "ffr ..." and "ufr > > > ...". > > An onstat -g mem shows that all of the memory involved goes to the > "rsam" pool. And although it doesn't free when sessions clear up, > subsequent sessions that do the same thing <i>will</i> re-use the > memory that has been allocated there. > > I have figured out that sysptnext isn't the only culprit. I can > replicate against systabnames, too. I've also figured out that there > DOES seem to be a relationship between the number of objects and the > amount of memory that's used, and that the number of objects is what > prevented me from being able to replicate on the older boxes (in one > case because there were far fewer objects, and in the other case > because the initial SHMVIRTSIZE was large enough to mask the problem). > > Just to give a sense of scale, on my dev box where I can replicate the > problem, doing a SELECT COUNT(*) FROM systabnames takes several > minutes, allocates about 60-70 MB of virtual shared memory (all in the > "rsam" pool) and returns a count of around 55,000. On the larger box, > where I first noticed the problem, there were probably five times as > many records (or maybe even more) in systabnames, and it would hit the > 4 GB SHMTOTAL value before finishing. > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list

I have opened a PMR with technical support. In further testing, I discovered that there's a linear relationship between the amount of memory allocated and the number of objects (tables and indices) on your server. For every object you have, it will allocate nearly _23KB_ of memory to the "rsam" pool when you query against systabnames. So if you have 100 tables and indices total, you'll allocate about 2.3 MB. If you have 1,000 such object, 23 MB, and so on. Because I've inherited a system with questionable design, our database server will ultimately have hundreds of thousands of objects. On Feb 27, 4:51 am, Martin Fuerderer <MARTI...@de.ibm.com> wrote: > Hi, > > as someone has said before: > If you are entitled to IBM Informix Technical Support, I guess > this is a good occasion to make use of it. > > It seems you gathered enough information and even have a > not so complicated way to reproduce it ... good pre-requisites > for a technical solution of a technical problem. > Contracts are a different story ... >