SHARED MEMORY ALLOCATION

Hi, Can I ask your opinion regarding memory allocation in informix 12.10 running on AIX 7? Total Server Memory is 12 Gig I was planning on leaving 4 Gig on the server and 8 Gig to be put in Shared Memory SHMTOTAL = 8192000 BUFFERPOOL (50% of SHMTOTAL) = 4096000SHMVIRTSIZE (25% of the remaining available in SHMTOTAL) = 2048000 DS_TOTAL_MEMORY (50% of SHMVIRTSIZE) = 1024000 DS_NONPDQ_QUERY_MEM (50% remaining of SHMVIRTSIZE) = 1024000 so I still have 25% remaining on SHMTOTAL do I still need to configure this space on other config or just put it equally on above parameters? Or is this a good or bad approach?

It's not really possible to give answers to your questions in anything other than general terms. SHMTOTAL limits the size of the instance and the only restriction is that it must be greater than the size of the instance at start up. I certainly thinks it makes sense to set it (rather than leave it at 0) since it can avoid Informix allocating more memory than is available on your server. If this is a dedicated Informix server you might be able to push SHMTOTAL to 10 GB in your case as 2 GB ought to be sufficient for the OS. The initial size of your instance would be smaller of course. On the systems I run I try to avoid any dynamic virtual segment allocation: it is often difficult/impossible to ever reclaim space added without an instance restart or disconnecting most/all sessions. SHMVIRTSIZE only needs to be as big as you need to support your applications. The only way to know what you need is to monitor it and see what is actually used over time and then add a safety margin on top. BUFFERPOOLs: make them as large as possible. Knowing how big SHMVIRTSIZE needs to be for your system helps a lot in this regard. Do you use PDQ a lot? DS_TOTAL_MEMORY can be 50% of SHMVIRTSIZE if you want. I don't think the engine reserves this memory. Someone might correct me on this. Your DS_NONPDQ_QUERY_MEM is quite aggressive IMHO as every session not using PDQ (or doing a non-parallel operation) can potentially use up to this amount of memory. There is no limit on sessions running concurrently, unlike with PDQ queries which can use the memory grant manager. It may be suitable for your system however, if you don't have many sessions or it is a warehouse system. HTH. Ben.

Hi Benjamin, I know that these parameters need tuning and analysis before we can give a definitive value for them but I appreciate the time and effort you gave to post you experience since this is what I've been looking for. We have a same specs server of the production used for testing but I guess I can never really achieve the output we receive from the prod to the testing server that is why I need a baseline to check that I am on the right path on this.

Note that DS_NONPDQ_QUERY_MEM is limited to 25% of DS_TOTAL_MEMORY. Neither DS_TOTAL_MEMORY nor DS_NONPDQ_QUERY_MEM are carved permanently out of SHMVIRTSIZE but are allocated from there as needed and released. I can alleviate Benjamin's fears. The total of DS_NONPDQ_QUERY_MEM used mainly for non-PDQ sessions for in-memory sorting. It should not be set to a value greater than the size of the largest temp dbspace as that is the hard limit on the size of an in-memory sort segment since the segment must be flushed to a temp dbspace if more in-memory segments are needed for larger sorts. If you oversize DS_NONPDQ_QUERY_MEM the engine will adjust it down and a message will be in the MSGPATH log at startup time. You do not have to allocate all of memory to "something". Unspecified shared memory in SHMVIRTSIZE is used for query processing and private buffers within sessions. Some of this is also controlled by the VP_MEMORY_CACHE_KB setting and that amount times the number of CPU VPs is pre-allocated to the CPU VPs to hold in order to reduce contention for the central memory pool when many concurrent sessions require query processing memory.