Informix Warehouse Accelerator : Memory usage, nec

Hello, I have some questions to understand well the memory management with the IWA : 1/ How to compute the max available shared memory usage for IWA ? I found this formula with my tests : - MAX Available shared memory = SHMMAX * number of worker nodes If the result is greater than the parameter WORKER_SHM then the MAX available shared memory = WORKER_SHM The formula is good ? 2/ How to compute the necessary prerequisites (shared memory, space for /dev/shm, space disk to create the data mart) to load a data mart ? In my database, my fact table size is 10 GB and my dimension table size is 1 GB, How to compute an estimated space disk of the compressed tables ? What is the necessary shared memory to allocate IWA to load this data mart ? What is the necessary shared memory of each worker node to load this data mart ? Same question for the coordinator node ? What is the necessary space for /dev/shm ? Are there any formula to estimate these parameters ? 3/ If my physical memory is 10 Gb on my server and the estimated size of my data mart is greater than 10 Gb (by example 15 Gb), Can I load my data mart ? Or is this impossible ? Regards, -- Franck Thomas ConsultiX franck.thomas@consult-ix.fr Téléphone : 33 (0) 1 39 12 18 00 Mobile : 33 (0) 6 78 81 09 33 Fax : 33 (0) 1 39 12 18 18

Hi, I'll try to answer ... 1. about shmmax, shmall, shmmni ... see also http://www.rwitkop.com/VistA/ConfigureSysctl4Cache.html and https://www.ibm.com/developerworks/mydeveloperworks/blogs/2fa81a5c-cb30-4873-b77 5-1370151e3614/entry/informix_warehouse_accelerator_configuration_tips_3_linux_k ernel4?lang=en shmmax is the max size of a single SHM segment, whereas shmall is the max number of SHM pages on a system and shmmni is the max number of SHM segments in the system. So each one of them can be a limiting factor in its own way. Once you have increased one, you may next hit the other one, and so on. For the accelerator, WORKER_SHM config parameter is an additional limiting factor, especially for the combined size of all data marts. So your formula is good, as long as you do not hit the limits of shmmni and/or shmall. 2. Estimating the size of a data mart (and the amount of memory needed for it) ... is a quite tricky business. I've already tried to put some background info together for an upcoming blog entry - to be published next week. But I have to admit, that in general there are so many different factors at work that I found it hard to describe this with sufficient certainty. :-( The one thing to be answered easily is that /dev/shm must be big enough to hold all the really allocated and used shared memory portions of all nodes. Otherwise, for the moment I recommend using the ISAO Studio GUI for data mart size estimation. I think it mostly does a pretty good job. But I've also seen that it was considerably off the mark in specific scenarios. 3. One of the main features of the Informix Warehouse Accelerator is that all the data is kept in memory. Therefore a data mart certainly cannot be bigger than the physically available memory on the machine. Enforcing the use of swap space would be very counterproductive regarding the performance of the accelerator - and hence is absolutely not recommended. The accelerator will also need some additional memory for compression processing during data load time and for query processing. Thus filling 98% of the physically available memory with the data mart by itself will most probably not work (due to hitting the memory limit during data load) and would not be very useful (due to hitting the memory limit during query processing). Using between 50-65% of memory available for the accelerator for the data marts has been found a useful "compromise". Regards, Martin -- Martin Fuerderer IBM Informix Development Munich, Germany Information Management Read about the Informix Warehouse Accelerator: https://www.ibm.com/developerworks/mydeveloperworks/ blogs/2fa81a5c-cb30-4873-b775-1370151e3614/entry/ introducing_informix_warehouse_accelerator9?lang=en IBM Deutschland Research & Development GmbH Chairman of the Supervisory Board: Martin Jetter Board of Management: Dirk Wittkopp Corporate Seat: Boeblingen, Germany Reg.-Gericht: Amtsgericht Stuttgart, HRB 243294 ids-bounces@iiug.org wrote on 05/06/2011 01:25:37 PM: > > Hello, > > I have some questions to understand well the memory management with the > IWA : > > 1/ How to compute the max available shared memory usage for IWA ? > > I found this formula with my tests : > - MAX Available shared memory = SHMMAX * number of worker nodes > > If the result is greater than the parameter WORKER_SHM > then the MAX available shared memory = WORKER_SHM > > The formula is good ? > > 2/ How to compute the necessary prerequisites (shared memory, space for > /dev/shm, space disk to create the data mart) to load a data mart ? > In my database, my fact table size is 10 GB and my dimension table size > is 1 GB, > > How to compute an estimated space disk of the compressed tables ? > What is the necessary shared memory to allocate IWA to load this data mart ? > What is the necessary shared memory of each worker node to load this > data mart ? > Same question for the coordinator node ? > What is the necessary space for /dev/shm ? > > Are there any formula to estimate these parameters ? > > 3/ If my physical memory is 10 Gb on my server and the estimated size of > my data mart is greater than 10 Gb (by example 15 Gb), > Can I load my data mart ? Or is this impossible ? > > Regards, > > -- > Franck Thomas > ConsultiX > franck.thomas@consult-ix.fr > Téléphone : 33 (0) 1 39 12 18 00 > Mobile : 33 (0) 6 78 81 09 33 > Fax : 33 (0) 1 39 12 18 18 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >