cached tables

Posted in 2003

rkusenet — Thu, 03 Jul 2003 16:40:19 — source: IIUG Forums & Mailing Lists

We have some tables which are static in nature. They are loaded every night and during the day are subjected to massive hits (read). Is there a way in Informix to keep such tables in memory only so that performance in enhanced. We use 9.21.UC4 and IIRC it allows STATIC tables to be created. But apart from avoiding logging it does not give us any benefit. Ravi

↪ replying to rkusenet

Scott MacKenzie — Thu, 03 Jul 2003 17:14:41 — source: IIUG Forums & Mailing Lists

SET TABLE tab_name/index_name MEMORY_RESIDENT/NON_RESIDENT; rkusenet wrote: > We have some tables which are static in nature. They are loaded every > night and during the day are subjected to massive hits (read). > Is there a way in Informix to keep such tables in memory only > so that performance in enhanced. We use 9.21.UC4 and IIRC it > allows STATIC tables to be created. But apart from avoiding logging > it does not give us any benefit. > > Ravi > -- ( ______ )) .-- Scott MacKenzie; Dine' College ISD --. >===<--. C|~~| (>--- Phone/Voice Mail: 928-724-6639 ---<) | ; o |-' | | \\\\--- Senior DBA/CARS Coordinator/Etc. --/ | _ | `--' `-- E: scottm at dinecollege dot edu -' `-----'

↪ replying to Scott MacKenzie

Jonathan Leffler — Thu, 03 Jul 2003 19:07:21 — source: IIUG Forums & Mailing Lists

Scott's right, but be wary - if those tables are massively hit on for reads, they will be in memory anyway, and if you make them memory resident, then all of the table will be resident, whereas if you leave them to be cached normally, only the parts of the tables that are actually being hit will be in cache, and the memory that is saved can be used for other purposes. In other words, if your table is heavily used, it is functionally memory resident regardless of whether you formally make it memory resident - that's what the shared memory buffer pool does for you automatically. And if they're read-only, then the pages will never be dirty, so they'll never need processing at checkpoint time. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" |---------+----------------------------> | | "Scott MacKe...."| | | <scottm@dinecolle| | | ge.edu> | | | Sent by: | | | forum.subscriber@| | | iiug.org | | | | | | | | | 07/03/2003 10:14 | | | AM | | | | |---------+----------------------------> >------------------------------------------------------------------------------- --------------------------------------------------------------| | | | To: ids@iiug.org | | cc: | | Subject: Re: cached tables [1490] | | | >------------------------------------------------------------------------------- --------------------------------------------------------------| SET TABLE tab_name/index_name MEMORY_RESIDENT/NON_RESIDENT; rkusenet wrote: > We have some tables which are static in nature. They are loaded every > night and during the day are subjected to massive hits (read). > Is there a way in Informix to keep such tables in memory only > so that performance in enhanced. We use 9.21.UC4 and IIRC it > allows STATIC tables to be created. But apart from avoiding logging > it does not give us any benefit. > > Ravi > -- ( ______ )) .-- Scott MacKenzie; Dine' College ISD --. >===<--. C|~~| (>--- Phone/Voice Mail: 928-724-6639 ---<) | ; o |-' | | \\\\--- Senior DBA/CARS Coordinator/Etc. --/ | _ | `--' `-- E: scottm at dinecollege dot edu -' `-----'

↪ replying to Jonathan Leffler

Mark Scranton — Mon, 07 Jul 2003 15:03:34 — source: IIUG Forums & Mailing Lists

Let me add some points to Jonathon's comments here... If those tables are very active (massively hit), then they MAY stay in memory, or the buffer cache in particular. But this depends heavily on the size of the buffer cache. If the cache (BUFFERS) is too small, then these pages, especially since they are free, are candidates for replacement, and will be overwritten by new pages coming in. Then we have to go get them again - off disk. They are essentially being thrashed between mem and disk - that's the reason we created MEMORY RESIDENT tables/indexes. With that said, another statement was "if you make those tables memory resident, then the whole table will be in memory" ... only the pages that have been retrieved are "memory resident". OK - if all are retrieved then there is a possibility that they will all be memory resident. But keep in mind, that the allocation for memory resident pages is a fixed size, and if the pages exceeds what's available, they'll fall out the back. HTH - Mark Mark Scranton Principal Consultant/Teacher IBM Denver IBM Software Group - Data Management Office: 303-773-5067 Cell: 303-929-0914 email: mscranto@us.ibm.com Jonathan Leffler/Menlo To: ids@iiug.org Park/IBM@IBMUS cc: Sent by: Subject: Re: cached tables [1491] forum.subscriber@ iiug.org 07/03/2003 01:07 PM Scott's right, but be wary - if those tables are massively hit on for reads, they will be in memory anyway, and if you make them memory resident, then all of the table will be resident, whereas if you leave them to be cached normally, only the parts of the tables that are actually being hit will be in cache, and the memory that is saved can be used for other purposes. In other words, if your table is heavily used, it is functionally memory resident regardless of whether you formally make it memory resident - that's what the shared memory buffer pool does for you automatically. And if they're read-only, then the pages will never be dirty, so they'll never need processing at checkpoint time. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" |---------+----------------------------> | | "Scott MacKe...."| | | <scottm@dinecolle| | | ge.edu> | | | Sent by: | | | forum.subscriber@| | | iiug.org | | | | | | | | | 07/03/2003 10:14 | | | AM | | | | |---------+----------------------------> >------------------------------------------------------------------------------- --------------------------------------------------------------| | | | To: ids@iiug.org | | cc: | | Subject: Re: cached tables [1490] | | | >------------------------------------------------------------------------------- --------------------------------------------------------------| SET TABLE tab_name/index_name MEMORY_RESIDENT/NON_RESIDENT; rkusenet wrote: > We have some tables which are static in nature. They are loaded every > night and during the day are subjected to massive hits (read). > Is there a way in Informix to keep such tables in memory only > so that performance in enhanced. We use 9.21.UC4 and IIRC it > allows STATIC tables to be created. But apart from avoiding logging > it does not give us any benefit. > > Ravi > -- ( ______ )) .-- Scott MacKenzie; Dine' College ISD --. >===<--. C|~~| (>--- Phone/Voice Mail: 928-724-6639 ---<) | ; o |-' | | \\\\--- Senior DBA/CARS Coordinator/Etc. --/ | _ | `--' `-- E: scottm at dinecollege dot edu -' `-----'