Extendable BUFFERPOOLs in 12.10.xC3 and higher..

mholzbauer's picture

starting with 12.10.xC3 the BUFFERPOOL configuration parameter has now two formats:

 

1) Use the BUFFERPOOL configuration parameter with the memory field if you

     want to specify the size of your buffer pool in units of memory like KB, MB or GB.

2) Use the BUFFERPOOL configuration parameter with the buffers field if you

     want to specify the size of your buffer pool in units of pages, or to retain

     settings from a previous release.

 



Example Format 1):

BUFFERPOOL      size=2K,start_memory=32mb,memory=32mb,extendable=1,cache_hit_ratio=98,lrus=8,lru_min_dirty=50,lru_max_dirty=60

BUFFERPOOL      size=16K,start_memory=32mb,memory=128mb,extendable=1,cache_hit_ratio=98,lrus=8,lru_min_dirty=50,lru_max_dirty=60

 

Example Format 2):

BUFFERPOOL      size=2K,buffers=2000,next_buffers=2000,max_extends=8,extendable=1,cache_hit_ratio=98,lrus=8,lru_min_dirty=50,lru_max_dirty=60

BUFFERPOOL      size=16K,buffers=1000,next_buffers=1000,max_extends=8,extendable=1,cache_hit_ratio=98,lrus=8,lru_min_dirty=50,lru_max_dirty=60

 



Note:

You can not mix both BUFFERPOOL Formats (memory / buffers):

BUFFERPOOL      default,buffers=auto,lrus=8,lru_min_dirty=50,lru_max_dirty=60.5

BUFFERPOOL      size=2K,start_memory=4mb,memory=32mb,extendable=1,cache_hit_ratio=98,lrus=8,lru_min_dirty=50,lru_max_dirty=60

BUFFERPOOL      size=16K,start_memory=16mb,memory=128mb,extendable=1,cache_hit_ratio=98,lrus=8,lru_min_dirty=50,lru_max_dirty=60


 

$ oninit -v

.

.

ERROR: Cannot mix buffer arguments with memory arguments. (BUFFERPOOL)

WARNING: server initialization failed or timed out.

Check the message log, online.log, for errors.

 

 

The problem here is the first BUFFERPOOL line in ONCONFIG (buffers=auto).

 

After changing this to:

BUFFERPOOL      default,start_memory=auto,lrus=8,lru_min_dirty=50,lru_max_dirty=60.5

the instance starts fine:

 

$ oninit -v

.

.

Allocating and attaching to shared memory...succeeded

.

.

Verbose output complete: mode = 5

 



The new BUFFERPOOL fields:

 

The fields in the BUFFERPOOL entries are not case-sensitive and the fields can be listed in any order.



extendable

 

Default is 1 if the memory field is set.

Default is 0 if the buffers field is set.

Whether the database server can extend the size of the buffer pool:

0 = Disabled. The buffer pool cannot grow.

1 = Enabled. The buffer pool can grow.

 

 

Format 1) - memory format:

 

start_memory

 

Default is 32 MB.

The start_size value represents the initial size of the buffer pool when the database server starts:



- An integer that represents 32 MB through the maximum amount of shared memory that is available.

  You can specify the size units of KB, MB, or GB. If you do not specify units, the default units are KB.

  The initial size of the buffer pool might be larger than the value of start_size because the size must be

  a multiple of the size of a shared memory segment.

- auto = The database server determines the initial amount of shared memory to allocate to the buffer pool.

  If you do not set the start_memory field, the initial size of the buffer pool is equal to the value of the memory field.

  The start_memory field is valid only if the memory field is set.



memory

 

Default is auto.

The max_size value represents the maximum size of the buffer pool.

The range of values for max_size is:



- An integer that represents 32 MB - 4 TB. You can specify the size units of KB, MB, or GB.

  If you do not specify units, the default units are KB.

- auto = The database server determines the maximum amount of shared memory to allocate to the buffer pool.

  The value of the AUTO_TUNE_SERVER_SIZE configuration parameter, if it is set, controls the maximum size

  of the buffer pool.

 

 



Format 2) - buffers format:

 

next_buffers

 

Default is 1000.

The number_buffers value is an integer >= 1000 that specifies the number of shared-memory buffers by which

the database server extends the buffer pool. The maximum value of number_buffers is limited by the amount of

virtual shared memory.

The number_buffers value is doubled every four extensions.

The next_buffers field is valid only if buffers and extendable=1 are set.



max_extends

 

Default is 8.

The extends value represents the maximum number of times that the database server can extend the buffer pool.

The value of extends is 0 through the maximum number of segments, which depends on the

operating system and bit size:

- 32 bit = 16

- UNIX 64 bit = 24

- Windows 64 bit = 8

 

 



Format 1) + 2):

 

cache_hit_ratio (only valid if extendable is set to 1)



Default is 90.

The ratio value is an integer 0 - 100 that represents the threshold below which the buffer pool is extended. When the

average read cache hit ratio remains below the value of ratio for approximately five minutes, the database server

extends the buffer pool. The cache_hit_ratio field is valid only if extendable=1 is set.

You can use onstat -g buf to monitor buffer pool statistics, including the read-cache rate of the buffer pool.

This rate represents the percentage of database pages that are already present in a shared-memory buffer when

a query requests a page. (If a page is not already present, the database server must copy it into memory from disk.)

If the database server finds the page in the buffer pool, it spends less time on disk I/O. Therefore, you want a high

read-cache rate for good performance.

For OLTP applications where many users read small sets of data, the goal is to achieve a read cache rate of 95 percent or better.

If the buffer pool is extendable, you can specify the read cache hit ratio below which the database server extends the buffer pool.

 

 



Little test:

 

$ onstat -g cfg bufferpool

IBM Informix Dynamic Server Version 12.10.FC5W1 -- On-Line -- Up 00:37:30 -- 1115288 Kbytes

name          current value

BUFFERPOOL   default,buffers=10000

BUFFERPOOL   size=2K,buffers=2000,lrus=8,lru_min_dirty=50,lru_max_dirty=60,extendable=1,next_buffers=2000,max_extends=8,cache_hit_ratio=90

 



First we put some load on the database:

 

$ (while true;do echo "select * from sysrawdsk" | dbaccess sysmaster - >/dev/null 2>&1;done) &

[1] 3422

$ (while true;do echo "select * from sysslttab" | dbaccess sysmaster - >/dev/null 2>&1;done) &

[2] 3444

$ (while true;do echo "select * from syssltdat" | dbaccess sysmaster - >/dev/null 2>&1;done) &

[3] 3482

 

 

Now we look at the (read) buffer cache:

 

$ while true;do onstat -g buf;sleep 5;done

IBM Informix Dynamic Server Version 12.10.FC5W1 -- On-Line -- Up 00:00:18 -- 1067688 Kbytes

Profile

Buffer pool page size: 2048

dskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached

651        3056       6324       89.71   22         38         617        96.43  

bufwrits_sinceckpt  bufwaits   ovbuff     flushes   

617                 26         0          1         

Fg Writes     LRU Writes    Avg. LRU Time Chunk Writes  Total Mem

0             0             -nan          7             5Mb     

                                      cache                 

# extends  max extends  next buffers  hit ratio   last      

0          8            2000          90          12:07:11  

Bufferpool Segments

id segment      size     # buffs  

0  0x84b7b000   5Mb      2001     

----------------------------------

Fast Cache Stats

gets       hits       %hits   puts      

1172       1042       88.91   1387      



.

.

.

.

IBM Informix Dynamic Server Version 12.10.FC5W1 -- On-Line -- Up 00:05:40 -- 1072024 Kbytes

Profile

Buffer pool page size: 2048

dskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached

20360983   20522263   45840299   55.58   35         53         686        94.90  

bufwrits_sinceckpt  bufwaits   ovbuff     flushes   

686                 55         0          1         

Fg Writes     LRU Writes    Avg. LRU Time Chunk Writes  Total Mem

0             0             -nan          7             9Mb     

                                      cache                 

# extends  max extends  next buffers  hit ratio   last      

1          8            2000          90          12:12:18  

Bufferpool Segments

id segment      size     # buffs  

0  0x84b7b000   5Mb      2001     

1  0x852aa000   4Mb      2001     

----------------------------------

Fast Cache Stats

gets       hits       %hits   puts      

1710       1454       85.03   2789      



.

.

.

IBM Informix Dynamic Server Version 12.10.FC5W1 -- On-Line -- Up 00:40:38 -- 1132560 Kbytes

Profile

Buffer pool page size: 2048

dskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached

186277525  186330239  400483700  53.49   56         102        1353       95.86  

bufwrits_sinceckpt  bufwaits   ovbuff     flushes   

1353                103        0          1         

Fg Writes     LRU Writes    Avg. LRU Time Chunk Writes  Total Mem

0             0             -nan          7             68Mb    

                                      cache                 

# extends  max extends  next buffers  hit ratio   last      

8          8            8000          90          12:47:18  

Bufferpool Segments

id segment      size     # buffs  

0  0x84b7b000   5Mb      2001     

1  0x852aa000   4Mb      2001     

2  0x856e6000   4Mb      2001     

3  0x85b22000   4Mb      2001     

4  0x85f5e000   8Mb      4001     

5  0x867d0000   8Mb      4001     

6  0x87042000   8Mb      4001     

7  0x878b4000   8Mb      4001     

8  0x88126000   16Mb     8000     

----------------------------------

Fast Cache Stats

gets       hits       %hits   puts      

3273       2679       81.85   9760      

 

$ onstat -g seg

IBM Informix Dynamic Server Version 12.10.FC5W1 -- On-Line -- Up 00:40:53 -- 1132560 Kbytes

Segment Summary:

id         key        addr             size             ovhd     class blkused  blkfree

11370551   52d34801   44000000         37203968         874360   R*    9083     0       

11403320   52d34802   4637b000         1048576000       12289752 V     11979    244021  

12451928   52d34822   84b7b000         5337088          1        B*    1303     0       

12484697   52d34823   85092000         2195456          27096    M     535      1       

12517468   52d34824   852aa000         4440064          1        B     1084     0       

12550237   52d34825   856e6000         4440064          1        B     1084     0       

12583006   52d34826   85b22000         4440064          1        B     1084     0       

12615775   52d34827   85f5e000         8855552          1        B     2162     0       

12714032   52d34828   867d0000         8855552          1        B     2162     0       

12746848   52d34829   87042000         8855552          1        B     2162     0       

12812385   52d3482a   878b4000         8855552          1        B     2162     0       

12845154   52d3482b   88126000         17686528         1        B     4318     0       

Total:     -          -                1159741440       -        -     39118    244022  

   (* segment locked in memory)

No reserve memory is allocated

 

 

$ tail -1000 `onstat -c | egrep "^MSGPATH" | awk '{printf("%s",$2);exit}'`

.

.

12:12:18  ** AUTO TUNING - Extending bufferpool 2K.

12:12:18  Requested shared memory segment size rounded from 4332KB to 4336KB

12:12:18  Extended bufferpool 2K

    4332K memory, 2001 buffers

.

.

12:17:18  ** AUTO TUNING - Extending bufferpool 2K.

12:17:18  Requested shared memory segment size rounded from 4332KB to 4336KB

12:17:18  Extended bufferpool 2K

    4332K memory, 2001 buffers

.

.

12:22:18  ** AUTO TUNING - Extending bufferpool 2K.

12:22:18  Requested shared memory segment size rounded from 4332KB to 4336KB

12:22:18  Extended bufferpool 2K

    4332K memory, 2001 buffers

.

.

12:27:18  ** AUTO TUNING - Extending bufferpool 2K.

12:27:18  Requested shared memory segment size rounded from 8645KB to 8648KB

12:27:18  Extended bufferpool 2K

    8645K memory, 4001 buffers

.

.

12:32:18  ** AUTO TUNING - Extending bufferpool 2K.

12:32:18  Requested shared memory segment size rounded from 8645KB to 8648KB

12:32:18  Extended bufferpool 2K

    8645K memory, 4001 buffers

.

.

12:37:18  ** AUTO TUNING - Extending bufferpool 2K.

12:37:18  Requested shared memory segment size rounded from 8645KB to 8648KB

12:37:18  Extended bufferpool 2K

    8645K memory, 4001 buffers

.

.

12:42:18  ** AUTO TUNING - Extending bufferpool 2K.

12:42:18  Requested shared memory segment size rounded from 8645KB to 8648KB

12:42:18  Extended bufferpool 2K

    8645K memory, 4001 buffers

.

.

12:47:18  ** AUTO TUNING - Extending bufferpool 2K.

12:47:18  Requested shared memory segment size rounded from 17270KB to 17272KB

12:47:18  Extended bufferpool 2K

    17270K memory, 8000 buffers

.

.

12:52:18  Performance Advisory: Unable to extend bufferpool 2K.

12:52:18   Results: Bufferpool has reached the # of extends '8' allowed.

12:52:18   Action: Increase the amount of memory the bufferpool can utilize.

 

 

So it do what it should do:

8 additional buffer segments are added (while the read buffer cache

was under 90 %) - until bufferpool...max_extents is reached.



 

Don't forget to cleanup that 3 load processes:

 

$ kill -9 3422 3444 3482

[1]   killed                ( while true; do

    echo "select * from sysrawdsk" | dbaccess sysmaster - > /dev/null 2>&1;

done )

[informix@oc2448803284 ~]$

[2]-  killed                ( while true; do

    echo "select * from sysslttab" | dbaccess sysmaster - > /dev/null 2>&1;

done )

[3]+  killed                ( while true; do

    echo "select * from syssltdat" | dbaccess sysmaster - > /dev/null 2>&1;

done )



$ onstat -g sql

IBM Informix Dynamic Server Version 12.10.FC5W1 -- On-Line -- Up 01:28:08 -- 1132560 Kbytes



Sess       SQL            Current            Iso Lock       SQL  ISAM F.E.

Id         Stmt type      Database           Lvl Mode       ERR  ERR  Vers  Explain    

252        SELECT         sysmaster          CR  Not Wait   0    0    9.24  Off        

250        SELECT         sysmaster          CR  Not Wait   0    0    9.24  Off        

31                        sysadmin           DR  Wait 5     0    0    -     Off        

30                        sysadmin           DR  Wait 5     0    0    -     Off        

28                        sysadmin           DR  Wait 5     0    0    -     Off        

27                        sysadmin           CR  Not Wait   0    0    -     Off        

4          SELECT         sysmaster          CR  Not Wait   0    0    9.24  Off        

$ onmode -z 252

$ onmode -z 250

$ onmode -z 4

$ onstat -g sql

IBM Informix Dynamic Server Version 12.10.FC5W1 -- On-Line -- Up 01:28:30 -- 1132560 Kbytes



Sess       SQL            Current            Iso Lock       SQL  ISAM F.E.

Id         Stmt type      Database           Lvl Mode       ERR  ERR  Vers  Explain    

31                        sysadmin           DR  Wait 5     0    0    -     Off        

30                        sysadmin           DR  Wait 5     0    0    -     Off        

28                        sysadmin           DR  Wait 5     0    0    -     Off        

27                        sysadmin           CR  Not Wait   0    0    -     Off     

 



sysmaster

 

$ dbaccess sysmaster -

> SELECT seg_osshmid::CHAR(10) AS id,

       RIGHT(LOWER(HEX(seg_osshmkey)),10)::CHAR(9) AS key,

       RIGHT(LOWER(HEX(seg_address)),8)::CHAR(8) AS addr,

       seg_size::CHAR(12) AS size,

       seg_ovhd::CHAR(8) AS ovhd,

       CASE WHEN (seg_class = 1) then 'R'::CHAR(1)

            WHEN (seg_class = 2) then 'V'::CHAR(1)

            WHEN (seg_class = 3) then 'M'::CHAR(1)

            WHEN (seg_class = 4) then 'B'::CHAR(1)

       END AS cls,

       seg_blkused::CHAR(9) AS blkused,

       seg_blkfree::CHAR(9) AS blkfree

 FROM sysseglst;

id         key       addr     size         ovhd     cls blkused   blkfree   

11370551   0x52d3480 44000000 37203968     874360   R   9083      0        

11403320   0x52d3480 4637b000 1048576000   12289752 V   12163     243837   

12451928   0x52d3482 84b7b000 5337088      1        B   1303      0        

12484697   0x52d3482 85092000 2195456      27096    M   535       1        

12517468   0x52d3482 852aa000 4440064      1        B   1084      0        

12550237   0x52d3482 856e6000 4440064      1        B   1084      0        

12583006   0x52d3482 85b22000 4440064      1        B   1084      0        

12615775   0x52d3482 85f5e000 8855552      1        B   2162      0        

12714032   0x52d3482 867d0000 8855552      1        B   2162      0        

12746848   0x52d3482 87042000 8855552      1        B   2162      0        

12812385   0x52d3482 878b4000 8855552      1        B   2162      0        

12845154   0x52d3482 88126000 17686528     1        B   4318      0        

12 row(s) retrieved.



> SELECT * FROM sysbufpool;

indx                0

address             2226634752

bufsize             2048

nbuffs              32008

buff_header         2227532752

nlrus               16

mindirty            50.00000000000

maxdirty            60.00000000000

dskreads            230576822

pagreads            230702380

bufreads            495521103

dskwrites           56

pagwrites           102

bufwrites           1354

bufwrites_sinceck+  1354

bufwaits            107

ovbuff              0

flushes             1

fgwrites            0

lruwrites           0

chunkwrites         7

lru_time_total      0.00

lru_calls           0

1 row(s) retrieved.

 

 

> SELECT * FROM sysseglst WHERE seg_class = 4

seg_address    2226630656

seg_next       2231967744

seg_prev       1178054656

seg_class      4

seg_size       5337088

seg_osshmid    12451928

seg_osmaxsize  8388608

seg_osshmkey   1389578274

seg_procid     3314

seg_userid     200

seg_shmaddr    2226630656

seg_ovhd       1

seg_lock       2226631216

seg_nextid     12484697

seg_bmapsz     96

seg_blkused    1303

seg_blkfree    0

.

.

.

.

seg_address    2282905600

seg_next       1140850688

seg_prev       2274050048

seg_class      4

seg_size       17686528

seg_osshmid    12845154

seg_osmaxsize  33554432

seg_osshmkey   1389578283

seg_procid     3336

seg_userid     200

seg_shmaddr    2282905600

seg_ovhd       1

seg_lock       2282906160

seg_nextid     11370551

seg_bmapsz     96

seg_blkused    4318

seg_blkfree    0

9 row(s) retrieved.