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 4K
        360448K 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
------------------------------

Original Message:
Sent: Tue May 09, 2023 06:29 AM
From: Art Kagel
Subject: BUFFERPOOL with memory vs pages strange behavior

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
------------------------------

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
------------------------------

Original Message:
Sent: Thu May 11, 2023 10:31 AM
From: Jared Heath
Subject: BUFFERPOOL with memory vs pages strange behavior

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
------------------------------


Original Message:
Sent: Tue May 09, 2023 06:29 AM
From: Art Kagel
Subject: BUFFERPOOL with memory vs pages strange behavior

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

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:42

Profile

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:31

Profile

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