Anyone aware of an auto update statistics bug in 12.10.xC4

Hello everyone, Assessing a server that crashed three times out of five at/around 1:11 AM on a Saturday morning due to grabbing memory all the way up to 16 GB (regular Informix shmtotal footprint is 1 GB) and hitting an "operating system" limit (this is workgroup edition on Linux) and crashing. After some research, the * only * session that is running at that time is the auto update statistics refresh job! I am reasonably certain this is the main culprit but I am not certain as to why and whether or not it is a problem on its own or only when this couples with some other activities/scenarios. I went through some of the bug lists but did not come across anything on this. Has anyone seen/is anyone aware of such a problem, possibly a bug? Thank you, Hal Maner M Systems International, Inc. Phone: +1 (919) 493-6012 ext.4009 hmaner@msystemsintl.com www.msystemsintl.com #Informix

I'd suspect that VP_MEMORY_CACHE_KB is set to DYNAMIC mode and that was the cause. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

VP_MEMORY_CACHE_KB is set to 0 (disabled).AUTO_TUNE is set to 2 which to me is a strange value. Hal

Hmm. Not just strange, not a valid setting for AUTO_TUNE. It accepts either 0 or 1. Effectively off or on. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Well, not being an expert on nor a fan of these "AUTO..." variables (but I understand and accept why they are there), "AUTO_TUNE" value is the default value for the bunch of other "AUTO_..." variables, and * one * of them, AUTO_READAHEAD, * does * take a 2 as a valid value ("aggressive") according to the documentation (and it says "not recommended" :) So, theoretically then, 2 might be valid albeit problematic value. I also just checked and found that they do have a AUTO_READAHEAD 1,128 in the onconfig, so that should override the default of 2... Hal

Yea, Scott Lashley wrote that readahead code and STRONGLY recommended not setting AUTO_READAHEAD to 2. But, as you say, even if setting AUTO_TUNE to 2 might affect the setting of AUTO_READAHEAD, the specific setting of that parameter overrides the setting of AUTO_TUNE. But the docs are very specific, only 0 and 1 are valid settings. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Hi Hal, Do you use fragment level statistics anywhere? If you do there is a bug which could be relevant: idsdb00096393 Large amount of memory used by fragment level statistics. I think this is fixed in 12.10.xC11. Have you considered simply upgrading to 12.10.xC13? There are a lot of defect fixes over xC4, which was released over five years ago. To pick up on what Art has suggested, I don't know of anything related to Auto Update Statistics which would stress the VP cache. It is straightforward to tell if it's on or not without understanding AUTO_TUNE: run 'onstat -g vpcache'. For more detail on how to monitor it and how much memory it is consuming: https://informixdba.wordpress.com/2013/11/11/monitoring-virtual-segment-usage-and-the-cpu-vp-caches/ Ben. ------------------------------ Benjamin Thompson ------------------------------

Ben, thank you for your feedback. I do not think there are any fragmented tables here – this is a workgroup edition instance that I was asked to look into briefly. It is not a server that I look after and I am not sure about the likelihood or plans about an upgrade. VP cache is disabled based on the onconfig setting of 0 for VP_MEMORY_CACHE_KB. Hal Maner M Systems International, Inc.