RESIDENT and PDQ
A user asked what RESIDENT=1 does and what PDQ parameters affect. Art Kagel explained RESIDENT locks Informix's resident shared-memory segment (buffers and key structures) so it can't be swapped out, giving a big performance gain, and noted 7.30 added values to lock virtual segments too. Others cautioned it only helps if physical memory is ample, otherwise other applications suffer (and buffers should be sized to fit RAM). A follow-up asked whether locked memory lets PDQ light scans read from cache; Art said no, light scans deliberately bypass the main buffer cache, with cooked filesystems/OS caching suggested as an alternative. Question answered, though the light-scan caching point was left uncertain.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Can anyone explain what resident = 1 means and what the advantage of setting it is ? More performance ??? What effect does the PDQ-Parameters have ? GREETINGS OLAF
Olaf Kratzsch wrote: > > Can anyone explain what resident = 1 means and what the advantage of > setting it is ? More performance ??? > > What effect does the PDQ-Parameters have ? It means that the Informix resident segment, which contains your buffers and other critical fixed size data structures, is physically prevented from being swapped out to the swap disks. Yes it improves performance dramatically. BTW 7.30 added other values for RESIDENT to lock the Virtual segments as well. Art S. Kagel
nick wrote: > > All depends how much physical memory you have on your computer and > how big is resident portion of share memory. Some time it's not good > to set resident = 1. You are correct Rick. Setting RESIDENT is most effective if you have enough memory to be able to lock the resident segment at all and even so if memory is tight other applications may suffer. However, if the resident segment is 3/4GB and you have 4GB of memory there is no such problem. This issue should have been covered in my response, thanks. > "Art S. Kagel" wrote: > > > Olaf Kratzsch wrote: > > > > > > Can anyone explain what resident = 1 means and what the advantage of > > > setting it is ? More performance ??? > > > > > > What effect does the PDQ-Parameters have ? > > > > It means that the Informix resident segment, which contains your buffers > > and other critical fixed size data structures, is physically prevented > > from being swapped out to the swap disks. Yes it improves performance > > dramatically. BTW 7.30 added other values for RESIDENT to lock the > > Virtual segments as well. > > > > Art S. Kagel
All depends how much physical memory you have on your computer and how big is resident portion of share memory. Some time it's not good to set resident = 1. "Art S. Kagel" wrote: > Olaf Kratzsch wrote: > > > > Can anyone explain what resident = 1 means and what the advantage of > > setting it is ? More performance ??? > > > > What effect does the PDQ-Parameters have ? > > It means that the Informix resident segment, which contains your buffers > and other critical fixed size data structures, is physically prevented > from being swapped out to the swap disks. Yes it improves performance > dramatically. BTW 7.30 added other values for RESIDENT to lock the > Virtual segments as well. > > Art S. Kagel
Art S. Kagel wrote in message <36F6C3A9.5B3C@bloomberg.net>... >BTW 7.30 added other values for RESIDENT to lock the >Virtual segments as well. > >Art S. Kagel Does this suggest that it is possible to cache up and make resident light scan reads? We have a relatively small, but not totally uncommon data warehouse whose total size is <2G. We would like to use PDQ 100 for intensive DSS queries to get parallel execution, but then light scans would ignore entire tables cached in LRQ buffers (from previous operations), preferring instead to do new physical read from disk, right? Light scans are great, but wouldn't it be much, much faster if PDQ could read from buffers, instead of disk, WHEN COMPLETE TABLES ARE ALREADY RESIDENT IN BUFFERS? Also, as we all know, memory is cheap now. We would like to max out our Sun UltraEnterprise 3000 to 6G and cache up the entire database as resident, while still allowing substantial amount virtual memory. But it seems as though Informix is quite limited, permitting only 512K buffers of 2k each (total of 1G RAM for buffers). Of course, even if Informix permitted us to use as buffers the max memory our machine supports (6G), PDQ would still ignore and read in (light scans) from physical disk each time (I think). So Art's comment is enticing. Sounds like we might be able to install max RAM (no 1G limit for virtual) to be used for both query execution and data caching (with PDQ queries). I'm thinking that this might be possible because no updating of data is involved and hence the whole buffer infrastructure for writes/synchronization (which is the whole sum and substance of the DBMS) wouldn't really be required. . Am I thinking correctly? And, is it possible to configure IDS to have substantial DS_TOTAL_MEMORY and also (because physical RAM is plentiful) read data from cache? If so, how. If not, might this not be a desirable future enhancement? Memory is cheap and fast, let us use it. I would welcome any correction, if I am mistaken in my analysis. Would also welcome any further discussion on the issue. Regards, David Grove
David Grove wrote: > > Art S. Kagel wrote in message <36F6C3A9.5B3C@bloomberg.net>... > > >BTW 7.30 added other values for RESIDENT to lock the > >Virtual segments as well. > > > >Art S. Kagel > > Does this suggest that it is possible to cache up and make resident light > scan reads? > > We have a relatively small, but not totally uncommon data warehouse whose > total size is <2G. We would like to use PDQ 100 for intensive DSS queries > to get parallel execution, but then light scans would ignore entire tables > cached in LRQ buffers (from previous operations), preferring instead to do > new physical read from disk, right? Light scans are great, but wouldn't it > be much, much faster if PDQ could read from buffers, instead of disk, WHEN > COMPLETE TABLES ARE ALREADY RESIDENT IN BUFFERS? > > Also, as we all know, memory is cheap now. We would like to max out our Sun > UltraEnterprise 3000 to 6G and cache up the entire database as resident, > while still allowing substantial amount virtual memory. But it seems as > though Informix is quite limited, permitting only 512K buffers of 2k each > (total of 1G RAM for buffers). Of course, even if Informix permitted us to > use as buffers the max memory our machine supports (6G), PDQ would still > ignore and read in (light scans) from physical disk each time (I think). > > So Art's comment is enticing. Sounds like we might be able to install max > RAM (no 1G limit for virtual) to be used for both query execution and data > caching (with PDQ queries). I'm thinking that this might be possible > because no updating of data is involved and hence the whole buffer > infrastructure for writes/synchronization (which is the whole sum and > substance of the DBMS) wouldn't really be required. . Am I thinking > correctly? And, is it possible to configure IDS to have substantial > DS_TOTAL_MEMORY and also (because physical RAM is plentiful) read data from > cache? If so, how. If not, might this not be a desirable future > enhancement? Memory is cheap and fast, let us use it. > > I would welcome any correction, if I am mistaken in my analysis. Would also > welcome any further discussion on the issue. Yes you will be able to make virtual segments resident but that will still not help to cache lite scans. The whole idea of lite scans it to reduce the over head of huge queries by reading the pages one page at a time into a very few rotating buffers instead of into the main buffer cache. I honestly do not know if the engine checks to see if a page is already in the buffer cache before reading it from disk in a lite scan so I cannot comment further. Art S. Kagel
David Grove wrote: > Also, as we all know, memory is cheap now. We would like to max out our Sun > UltraEnterprise 3000 to 6G and cache up the entire database as resident, > while still allowing substantial amount virtual memory. But it seems as > though Informix is quite limited, permitting only 512K buffers of 2k each > (total of 1G RAM for buffers). Of course, even if Informix permitted us to > use as buffers the max memory our machine supports (6G), PDQ would still > ignore and read in (light scans) from physical disk each time (I think). One thing you might want to look into here is the possibility of going back to cooked filesystems. This would allow the OS to move the file contents into cache. Does Solaris allow you to specify write-back/write-thru on disk cache? That could make a large difference. Something to think about, in any rate. Call it a poor-man's EMC array. -Richard
"Art S. Kagel" wrote: > > nick wrote: > > > > All depends how much physical memory you have on your computer and > > how big is resident portion of share memory. Some time it's not good > > to set resident = 1. > > You are correct Rick. Setting RESIDENT is most effective if you have > enough memory to be able to lock the resident segment at all and even > so if memory is tight other applications may suffer. However, if the > resident segment is 3/4GB and you have 4GB of memory there is no such > problem. This issue should have been covered in my response, thanks. Contradiction!!! If your resident portion doesn't fit into physical memory, you have to reduce your buffers. What sense should a buffer have, that you have to read from disk? hth martin