Index page size recommendations

IDS 14.10.FC5 RHEL 8.2 Migrating from an older release of Informix on a different OS, so everything will be different in the new environment. New Informix, new OS, new hardware. So, this is a great time to consider changing some of the things that have remained in place for so long just because they were already in place. Among those changes, I would like to change the default page size for indexes, and possibly for some tables. My recollection from discussions at past IIUG meetings and here on the forum are that indexes should be on 16k pages. Is that still the case? Is it a universal rule, i.e., all indexes should be on 16k pages? Or are there guidelines for which indexes should be on 2k, 16k, and other size pages? Similar question for tables - any guidelines would be appreciated. I would suspect that for tables, one rule might be that tables with row size > 2k should be on a page size large enough to hold at least one row, but beyond that, I'm not sure what other considerations there might be. And if a row can be larger than 2k in length, but has several VARCHAR/LVARCHAR columns and most rows are shorter than 2k, does that impact the choice of page size for a table? Related to this, unless I'm mistaken, we would want a bufferpool for each page size that we use within our system. So, if we stick with 2k pages for tables and 16k pages for indexes, we'd need a 2k bufferpool and a 16k bufferpool. But if we have some tables that need to go on 4k pages, we'd also need a 4k bufferpool. Correct so far? If we've got dbspaces and bufferpools set up with these other page sizes, do we also need to create any dbspaces for temp tables with those other page sizes? Any other things to consider with these other page sizes? Thanks. ------------------------------ Mark Collins ------------------------------ #Informix

16 k for indices is a good starting point. After that is depends on the data. If I am using TS then I would tend to have dedicate spaces as well. And yes you need a buffer pool for each page size. Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC

Mark: I'm copying your post here so I can respond inline: Migrating from an older release of Informix on a different OS, so everything will be different in the new environment. New Informix, new OS, new hardware. So, this is a great time to consider changing some of the things that have remained in place for so long just because they were already in place. Among those changes, I would like to change the default page size for indexes, and possibly for some tables. My recollection from discussions at past IIUG meetings and here on the forum are that indexes should be on 16k pages. Is that still the case? Is it a universal rule, i.e., all indexes should be on 16k pages? Or are there guidelines for which indexes should be on 2k, 16k, and other size pages? Very small indexes are affected by page size, so indexes with few unique values and short keys don't need wide pages. Other than that, yes, indexes perform better on wider pages and 16K is the widest. Similar question for tables - any guidelines would be appreciated. I would suspect that for tables, one rule might be that tables with row size > 2k should be on a page size large enough to hold at least one row, but beyond that, I'm not sure what other considerations there might be. And if a row can be larger than 2k in length, but has several VARCHAR/LVARCHAR columns and most rows are shorter than 2k, does that impact the choice of page size for a table? Yes, obviously wide rows should be on wide pages so that they are not broken into pieces on multiple pages. For tables with smaller rows I try to minimize wasted storage while minimizing as much as possible the number of different page sizes. So if a table wastes 30 bytes per row on 6K pages and 33 bytes per row on 4K pages, but I have no other reason to have a 6K dbspace, then I'll probably put the table in a 4K dbspace. I have a script that calculates the waste for a table or a whole database on various page sizes. For tables with variable length rows, the best you can do is to do the calculation of waste using the average row size. My script does that. However, what it does not take into account is the difference between storing a variable length row with versus without MAX_FILL_DATA_PAGES set. Without that parameter set the engine will not place a new row on a page unless its maximum length will fit on the page. With this parameter set there only has to be 10% of the page free after inserting the new row. These rules are there to allow some rows on a page to grow without having to be relocated if they outgrow the free space on their home page. Related to this, unless I'm mistaken, we would want a bufferpool for each page size that we use within our system. So, if we stick with 2k pages for tables and 16k pages for indexes, we'd need a 2k bufferpool and a 16k bufferpool. But if we have some tables that need to go on 4k pages, we'd also need a 4k bufferpool. Correct so far? Yes, creating a dbspace with a pagesize that does not already exist will automatically create a buffer pool in that page size using the settings of the BUFFERPOOL default line in the ONCONFIG file (that's what that line is there for). So, it is better to create the buffer pool for a new page size before creating the dbspace so that you can configure it customized to the contents and their expected usage patterns. If we've got dbspaces and bufferpools set up with these other page sizes, do we also need to create any dbspaces for temp tables with those other page sizes? No. All temp dbspaces are created in the server's base page size (so either 2K or 4K depending on platform - 2K on Linux). Any other things to consider with these other page sizes? 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.

Oops typo: very small indexes are NOT affected by page size. 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.

Hi Mark, you can think about to use "compressed" for large indexes in additionally . This can reduce the number of pages for the index between 30 and 60% (you can calculate with 50%). Have fun, Henri ------------------------------ Henri Cujass leolo IT, CTO Germany ------------------------------

I recall seeing an article some years ago, I don't recall which Informix engineer it was. His point was that, in general, a 16k page size was great for data but, to his own surprise, the 8K index page size produced faster results in his benchmark tests. He came up with an interesting reason for it but I don't recall that (and didn't quite grok it in the first place.) -- Jacob

Hi Jacob, many dependencies: - index size - cache read rate - cpu speed ... Best Regards Henri ------------------------------ Henri Cujass leolo IT, CTO Germany ------------------------------

Hello Henri, I wish that 'compressed' was a valid option for us. Unfortunately, we're using Workgroup Edition, and I believe compression is only available in Enterprise Edition. Even if it were available to us, I would not be allowed to use it, as it would interfere with the deduplication feature of our storage system (due to compressed blocks likely creating different hashes or something). I'm also not allowed to use Informix encryption, for the same reason. We've been told to rely on the storage system to do both encryption and deduplication (rather than compression). ------------------------------ Mark Collins ------------------------------

Hi Jacob, If you do find that article, I'd really like to see it. I'm sure that there probably are several aspects that would need to be tested, as Henri mentions, but it would be interesting to see that "interesting reason" that you mentioned. ------------------------------ Mark Collins ------------------------------

Hi Art, Just trying to clarify one point. You said: No. All temp dbspaces are created in the server's base page size (so either 2K or 4K depending on platform - 2K on Linux). ​ But the online docs seem to say the opposite: You can specify a page size for a standard or temporary dbspace if you want a longer key length than is available for the default page size.​ Maybe there has been a recent change? Or is there something in the wording ("a longer key length") that I'm missing? ------------------------------ Mark Collins ------------------------------

Mark: Oh, say it isn't so! Deduplication storage of database chunks? AAAAAAAAAAAAAAAAA Not only is this less than safe, because of the way databases are stored and updated, the likelihood that deduplication is saving you even a small percentage of storage cost is so small that I don't think my calculator has enough digits to work it out! Deduping archives or the filesystems they are stored on I understand, but database chunks? Just saying! ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

16 k for indices is a good starting point. After that is depends on the data. If I am using TS then I would tend to have dedicate spaces as well. And yes you need a buffer pool for each page size. Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC

Hi Art, Well, the stated goal is to have everything, including database chunks, stored on a storage system that "just includes dedup automatically". It's one of several decisions that I have tried unsuccessfully to influence, but I still keep trying. In that spirit, if you have any specific information or experience showing how this is "less than safe," I would really appreciate it if you would share. Is it dedup in general that is the problem? Or only certain implementations of it? Any additional considerations that might mitigate the issue, in case I can't prevail? Thanks. ------------------------------ Mark Collins ------------------------------

" We've been told to rely on the storage system to do both encryption and deduplication (rather than compression)." May God have mercy on your soul. ;)

Actually, Tom, I was kind of hoping that He might deliver us (my coworkers and me) from evil. ------------------------------ Mark Collins ------------------------------

Art, Not sure whether you saw my previous post. The online docs say: You can specify a page size for a standard or temporary dbspace if you want a longer key length than is available for the default page size.​​ You had said that all temporary dbspaces were created using the server's base page size (2k on Linux). I was just trying to see if this was a recent change to allow temp spaces with different page sizes, and whether there would be any benefit to doing so if I have dbspaces with non-default page sizes? And does it matter if the non-default dbspaces are for indexes? As in maybe index pages never end up in the temporary dbspaces, but if I had a table in a dbspace with 8k pages, maybe it would use a temp space with 8k pages? Thanks. ------------------------------ Mark Collins ------------------------------