Shared Memory Allocation
Amit Patel asked for advice about a customer with 1 TB RAM using only 38 GB shared memory, 10 TB of data and slowness. Art Kagel suggested adding buffer pool first, checking buffer turnover (BTR3) with his new_ratios.ksh script, and checking SHMVIRTSIZE only if segments are added often. SangGyu Jeong and Eric Vercelletto also advised finding hot data, bad queries and missing indexes. No outcome reported.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Dear Informix group user, Greeting from Amit Patel , hope all are safe and healthy in Covid-19 Era. I need a suggestion . In my new project , my customer has 1 TB of RAM and using only 38 GB as SHARED MEMORY with having 10 TB data size and they are feeling Server slowness. So far I've not got into the system , as project is likely to start and only got information from customer side. So what could be the assumption ? Should I say customer to share 75-80% RAM as SHARED MEMORY ? (means around 800 GB) or Should I increase Shared Memory around 100 GB and BUFFERPOOL , SHMVERTSIZE parameter? Please advise. Thanks Amit Patel ------------------------------ AMIT PATEL ------------------------------ #Informix
Unless they are adding shared memory segments regularly, they probably do not need more SHMVIRTSIZE, but check that out first. Most bang for the buck will likely be in adding to the buffer pool(s). Check the BTR3 (aka ABTR) as reported by my new_ratios.ksh script in the ratios.shr_ak package in the IIUG Repository ( www.iiug.org/software ). If it is close to or over 10 turns per hour, more buffers will be used. 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.
Hello Amit, Modifying the onconfig setting may improve server performance, but I think other aspects should also be considered. Even if the total data size is 10 TB, the size of frequently accessed data may be smaller. (I think the data size is large due to the accumulation of historical data.) Of course, if it is an application that needs to frequently access and fetch large volumes of data, it is a different story. It may also be helpful to track the frequently used tables and inefficient queries in your application to find improvements. ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------
Amit, As a starter, if the server has 1 TB of shared memory, it is a bit stupid to use only only 38 Gb (not even 4% of the server's RAM...) I was going to say check you Buffer Turnover ratio before anything, but Art was still awake to answer that ( and he is the author of the formula ). This ratio will tell you if you need more BUFFERPOOL, but I am sure you do in a mean way. How many users on the server? SHMVIRTSIZE will mainly depend on how many users are connected, then on how their queries consume shmvirt. To tune it, if possible, set an initial value, a bit short, and see how many additional segments get assigned during the day. This will be the 'cruising' value or initial value you will want to set for the server. But you can have many possible reasons for slowness. Badly written queries, lack of indexes or bad indexes as a starter. Eric Vercelletto Data Management Architect and Owner / Begooden IT Consulting Board of Directors, International Informix Users group IBM Champion 2013,2014,2015,2016,2017,2018,2019,2020 Tel: +33(0) 298 51 3210 Mob : +33(0)626 52 50 68 skype: begooden-it Google Hangout: eric.vercelletto@begooden-it.com Email: eric.vercelletto@begooden-it.com www : http://www.vercelletto.com www https://kandooerp.org