Keep definied tables/columns in shared memory?
Marc Demhartner asked how to keep tables or columns in memory on Informix 12.10 Workgroup Edition. Fernando Nunes and Art Kagel said there is no way: the buffer pool does it, and SET TABLE MEMORY_RESIDENT has been ignored since 9.4. Hal Maner suggested a dbspace with a different page size, a dedicated buffer pool, and one SELECT * to load the table.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hello, i´d like to know if there is away for Informix to mark some tables or columns to stay "in memory" at long as possible. I know there is the IWA, but we want a to try a more simple solution. Informix 12.10 - Workgroup Edition Linux 64bit ------------------------------ Marc Demhartner ------------------------------ #Informix
Not explicitly. The buffer pool management algorithm should achieve that if the pages are effectively being used (provided you have enough buffer pool). If the pages are not effectively being used it's a bit useless to keep them in memory (the only advantage would be to guarantee the best possible response time IF a query that require them ever was issued). In the manuals (old ones) you may find a statement that once did that (before the changing on the buffer pool management): SET TABLE|INDEX <NAME> MEMORY_RESIDENT The statement is possible still accepted by the parser, but is ignored since version 9.4 Regards. ------------------------------ FERNANDO NUNES ------------------------------
That was an option in v7.30/9.21 but it turned out to be a performance disaster and was removed in 7.31/9.30. The command tool declares a table memory resident was still recognized the last time I tried it, but it is no-op now.
The simple way I approach this is to: Create chunk(s)/dbspace(s) with a different page size from the others, Assign a separate/dedicated/sufficiently sized bufferpool to that page size, Put your subject tables in these dbspaces (and perhaps run a SELECT * FROM table at least once to force it to be read). A pproach their index space similarly where feasible. Always had good results/good performance with this approach in 12.10 on Linux, 11.50 on HP-UX, etc. I prefer it for mostly/almost always read only tables. Assumes you have not already used all the page sizes in the existing dbspaces, assumes you have sufficient RAM for the bufferpools (which, since you want to keep tables in memory, you probably do). Hal Maner M Systems International, Inc. www.mystemsintl.com