BUFFERPOOL with memory vs pages strange behavior

I'm experimenting with performance and I started playing with buffer pools. I'm getting strange behavior when switching from a line like: BUFFERPOOL size=4K,buffers=790000,lrus=20,lru_min_dirty=30.00,lru_max_dirty=40.00 To this line: BUFFERPOOL size=4K,memory=4gb,lrus=20,lru_min_dirty=30.00,lru_max_dirty=40.00 Firstly, it doesn't truly allocate 4gb of memory when the instance starts like I expect it to. In fact, it is only a fraction of that memory. Then after the instance runs for a bit, it extends it with some kind of AUTO TUNE but those pages error and don't go into hugepages like they should. I found an old bug on that which should be fixed in this version (14.10.FC7) but it doesn't appear to be so. Here is the onstat -g buf for 4k pages Buffer pool page size: 4096 dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 11268845 24550728 3118620529 99.64 350957 8661346 7280333 95.18 bufwrits_sinceckpt bufwaits ovbuff flushes 0 76047 0 37 Fg Writes LRU Writes Avg. LRU Time Chunk Writes Total Mem 0 0 0.000 287533 1448Mb cache # extends max memory next memory hit ratio last 3 4096Mb 704Mb 90 18:43:23 Bufferpool Segments id segment size # buffs 0 0x69a400000 392Mb 86497 1 0x724200000 352Mb 86556 2 0x73a200000 352Mb 86556 3 0x810200000 352Mb 86556 Questions: - why, when I said allocate 4gb, did it think 400mb would be the right thing to start with. Is this a bug? (I think it is) - is there some setting I am missing that is causing the behavior? - why are the additional pages getting this error: 18:43:23 ** AUTO TUNING - Extending bufferpool 4K. 18:43:23 Warning: Server is unable to lock huge pages in memory.Switching to normal pages. 18:43:23 Dynamically allocated new bufferpool shared memory segment (size 360448KB) 18:43:23 Memory sizes:resident:692224 KB, virtual:25001984 KB, message:4760, bufferpool:7004160, no SHMTOTAL limit 18:43:23 Segment locked: addr=0x810200000, size=369098752 18:43:23 Extended bufferpool 4K360448K memory, 86556 buffers ipcs: 0x52bd4813 32800 informix 666 348160 29 0x52bd4814 32801 informix 666 348160 29 0x52be4810 98338 informix 666 348160 28 0x52be4811 98339 informix 666 348160 28 0x52be4812 98340 informix 666 348160 28 0x52be4813 98341 informix 666 348160 28 0x52be4814 98342 informix 666 348160 28 0x52be4815 98343 informix 660 369098752 28 locked 0x52be4816 98344 informix 660 369098752 28 locked 0x52be4817 98345 informix 660 1073741824 28 locked 0x52be4818 98346 informix 660 1073741824 28 locked 0x52be4819 98347 informix 660 1073741824 28 locked 0x52be481a 98348 informix 660 369098752 28 locked 0x52be481b 98349 informix 660 2147483648 28 locked 0x52be4801 65593 root 660 708837376 28 0x52be4802 65594 root 660 25602031616 28 0x52be4803 65595 root 660 905969664 28 0x52be4804 65596 root 660 411041792 28 0x52be4805 65597 root 660 780140544 28 0x52be4806 65598 root 660 1115684864 28 0x52be4807 65599 informix 666 348160 28 As expected....this causes a significant performance problem. I already know how to work around it, but this isn't behaving the way I would expect it to. ------------------------------ Jared Heath ------------------------------

Jared: So, if you specify "memory" instead of "buffers" memory is the maximum buffer size and "start_memory" is the initial memory allocation which, since you did not specify it, defaults to 32K. When you specify "memory" then "extendable" defaults to '1' or ON, and the number of extensions defaults to '8'. So, except for the errors and huge pages issue, this is all expected behavior. If you wanted a 4GB fixed buffer pool you needed to set: BUFFERPOOL size=4k,start_memory=4gb,extendable=0,lrus=20,lru_min_dirty=30.00,lru_max_dirty=40.00 Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Thanks Art, I changed to that and it does make them full size at the start....execpt the biggest one... BUFFERPOOL size=16K,start_memory=12gb,extendable=0,lrus=20,lru_min_dirty=30.00,lru_max_dirty=40.00 Buffer pool page size: 16384 dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 185 1528 394 53.05 111388 14232264 24 0.00 bufwrits_sinceckpt bufwaits ovbuff flushes 0 0 0 1 Fg Writes LRU Writes Avg. LRU Time Chunk Writes Total Mem 0 0 -nan 6 8032Mb Bufferpool Segments id segment size # buffs 0 0xc46400000 8032Mb 507547 ------------------------------ Fast Cache Stats gets hits %hits puts 1 0 0.00 1 Does the server has a maximum buffer pool of 8gb?? ------------------------------ Jared Heath

Further looking at this...something weird happens when start_memory is there without memory. 16k (12gb) only got created 8gb, but the 4k (4gb) got created as 8gb (see onstat below). However, adding memory fixes it (see further down). BUFFERPOOL size=2K,start_memory=8gb,extendable=0,lrus=20,lru_min_dirty=30.00,lru_max_dirty=40.00 BUFFERPOOL size=4K,start_memory=4gb,extendable=0,lrus=20,lru_min_dirty=30.00,lru_max_dirty=40.00 BUFFERPOOL size=8K,start_memory=8gb,extendable=0,lrus=20,lru_min_dirty=30.00,lru_max_dirty=40.00 BUFFERPOOL size=16K,start_memory=12gb,extendable=0,lrus=20,lru_min_dirty=30.00,lru_max_dirty=40.00 [09:37 05/11/23 tba] root@perfinf33:/opt/informix/etc: onstat -g buf IBM Informix Dynamic Server Version 14.10.FC7 -- On-Line -- Up 00:03:24 -- 58598040 Kbytes 2023-05-11 09:37:42Profile Buffer pool page size: 2048 dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 330896 1047203 13479665 97.55 9760 14624 52788 81.51 bufwrits_sinceckpt bufwaits ovbuff flushes 52788 7062 0 1 Fg Writes LRU Writes Avg. LRU Time Chunk Writes Total Mem 0 0 -nan 11 8032Mb Bufferpool Segments id segment size # buffs 0 0x664400000 8032Mb 3724782 ------------------------------ Buffer pool page size: 4096 dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 52893 737544 4134472 98.72 6 12 12 50.00 bufwrits_sinceckpt bufwaits ovbuff flushes 12 96 0 1 Fg Writes LRU Writes Avg. LRU Time Chunk Writes Total Mem 0 0 -nan 6 8032Mb Bufferpool Segments id segment size # buffs 0 0x85a400000 8032Mb 1955442 ------------------------------ Buffer pool page size: 8192 dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 1759 54860 49687 96.46 1732 54752 28695 93.96 bufwrits_sinceckpt bufwaits ovbuff flushes 28671 0 0 1 Fg Writes LRU Writes Avg. LRU Time Chunk Writes Total Mem 0 0 -nan 24 8032Mb Bufferpool Segments id segment size # buffs 0 0xa50400000 8032Mb 1002386 ------------------------------ Buffer pool page size: 16384 dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 32581 2674648 1862857 98.25 6 48 12 50.00 bufwrits_sinceckpt bufwaits ovbuff flushes 12 0 0 1 Fg Writes LRU Writes Avg. LRU Time Chunk Writes Total Mem 0 0 -nan 6 8032Mb Bufferpool Segments id segment size # buffs 0 0xc46400000 8032Mb 507547 I did get it to create them exactly correctly with this setup....so something doesn't work right entirely with start_memory without memory. BUFFERPOOL size=2K,memory=8gb,start_memory=8gb,extendable=0,lrus=20,lru_min_dirty=30.00,lru_max_dirty=40.00 BUFFERPOOL size=4K,memory=4gb,start_memory=4gb,extendable=0,lrus=20,lru_min_dirty=30.00,lru_max_dirty=40.00 BUFFERPOOL size=8K,memory=8gb,start_memory=8gb,extendable=0,lrus=20,lru_min_dirty=30.00,lru_max_dirty=40.00 BUFFERPOOL size=16K,memory=12gb,start_memory=12gb,extendable=0,lrus=20,lru_min_dirty=30.00,lru_max_dirty=40.00 [09:44 05/11/23 tba] root@perfinf33:/var/log/informix: onstat -g buf IBM Informix Dynamic Server Version 14.10.FC7 -- On-Line -- Up 00:00:22 -- 59253400 Kbytes 2023-05-11 09:44:31Profile Buffer pool page size: 2048 dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 278 1436 2673 89.60 76 186 76 0.00 bufwrits_sinceckpt bufwaits ovbuff flushes 21 1 0 1 Fg Writes LRU Writes Avg. LRU Time Chunk Writes Total Mem 0 0 -nan 57 8192Mb Bufferpool Segments id segment size # buffs 0 0x664400000 8192Mb 3800492 ------------------------------ Buffer pool page size: 4096 dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 119 250 246 51.63 2 12 12 83.33 bufwrits_sinceckpt bufwaits ovbuff flushes 12 0 0 0 Fg Writes LRU Writes Avg. LRU Time Chunk Writes Total Mem 0 0 -nan 2 4096Mb Bufferpool Segments id segment size # buffs 0 0x864400000 4096Mb 997362 ------------------------------ Buffer pool page size: 8192 dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 6 40 48 87.50 4 96 24 83.33 bufwrits_sinceckpt bufwaits ovbuff flushes 0 0 0 0 Fg Writes LRU Writes Avg. LRU Time Chunk Writes Total Mem 0 0 -nan 0 8192Mb Bufferpool Segments id segment size # buffs 0 0x964400000 8192Mb 1022454 ------------------------------ Buffer pool page size: 16384 dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 184 1520 377 51.19 1 56 13 92.31 bufwrits_sinceckpt bufwaits ovbuff flushes 12 0 0 0 Fg Writes LRU Writes Avg. LRU Time Chunk Writes Total Mem 0 0 -nan 1 12288Mb Bufferpool Segments id segment size # buffs 0 0xb64400000 12288Mb 775899

Jared: I think the 8GB is my fault. You have to specify the "memory=<max>" in order for start_memory to be active. You can do "memory=12GB" or "memory=auto" in addition to the options you already have. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------