PLOGSPACE in 12.10.xC3 and higher / Physical log AUTO TUNING

mholzbauer's picture

from the manual:

 

Plogspace

 

A plogspace is a logical storage unit that is composed of one chunk that stores the

physical log. When the physical log is in the plogspace, the database server

increases the size of the physical log as needed to improve performance.

 

If you did not create a server during installation, the physical log is created in the

root dbspace. However, you can create the plogspace to move the physical log to a

different dbspace to prevent the physical log from filling the root dbspace. For

optimal performance, create the plogspace on a different disk from the root

dbspace or the location of the logical logs. If you created a server during

installation, the plogspace is created automatically with a default size that depends

on the value of the AUTO_TUNE_SERVER_SIZE configuration parameter.

 

By default, the chunk that you assign to the plogspace is extendable, therefore, the

initial size of the chunk can be small. The database server automatically expands

the chunk when the physical log requires more space.

 

The plogspace has the following restrictions:

- A database server instance can have only one plogspace.

- The plogspace can contain only the physical log.

- The plogspace can have only one chunk.

- The chunk must have the same page size as the root dbspace.

 

 

and more from manuals:

 

An instance can have only one plogspace. If a plogspace exists, when you create a

new plogspace, the physical log is moved to the new space and the old plogspace

is dropped.

 

The physical log must be stored on a single chunk. The chunk is marked as

extendable by default so that the database server can expand the plogspace if

necessary to expand the physical log. If you mirror the plogspace, the space cannot

expand because a mirror chunk cannot be extendable.

 

 

And here is how it works:

 

$ oncheck -pe rootdbs | head -20

DBspace Usage Report: rootdbs             Owner: informix  Created: 09/17/2015



 Chunk Pathname                             Pagesize(k)  Size(p)  Used(p)  Free(p)

     1 /chunks/rootdbs                               2  2000000   119570  1880430

 Description                                                   Offset(p)  Size(p)

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

 RESERVED PAGES                                                       0       12

 CHUNK FREELIST PAGE                                                 12        1

 rootdbs:'informix'.TBLSpace                                         13      250


 PHYSICAL LOG                                                       263    50000

 LOGICAL LOG: Log file 1                                          50263    10000

 LOGICAL LOG: Log file 2                                          60263    10000

 LOGICAL LOG: Log file 3                                          70263    10000

 LOGICAL LOG: Log file 4                                          80263    10000

 LOGICAL LOG: Log file 5                                          90263    10000

 LOGICAL LOG: Log file 6                                         100263    10000

 

$ onstat -l|head

IBM Informix Dynamic Server Version 12.10.FC5W1 -- On-Line -- Up 00:03:21 -- 1624976 Kbytes

Physical Logging

Buffer bufused  bufsize  numpages   numwrits   pages/io

  P-1  197      2048     327        9          36.33

      phybegin         physize    phypos     phyused    %used   

      1:263            50000      327        228        0.46    

 

$ touch /chunks/physdbs

$ chown informix:informix /chunks/physdbs

$ chmod 660 /chunks/physdbs

 

$ onspaces -c -P physdbs -o 0 -s 100000 -p /chunks/physdbs

Verifying physical disk space, please wait ...

Space successfully added.

** WARNING **  A level 0 archive of Root DBSpace will need to be done.

 

same can be done with sysadmin API:



$ echo 'EXECUTE FUNCTION task("create plogspace","physdbs","/chunks/physdbs",100000,0);' | dbaccess sysadmin -

 

 

$ onbar -b -F

$ ONLINELOG=`onstat -c | egrep "^MSGPATH" | awk '{print $2;exit}'`

$ tail -50 $ONLINELOG

.

.

10:41:32  Direct I/O cannot be used for the chunk file '/chunks/physdbs'.

10:41:32  Checkpoint Completed:  duration was 0 seconds.

10:41:32  Thu Sep 17 - loguniq 2, logpos 0xd2c2e4, timestamp: 0x10076 Interval: 7

10:41:32  Maximum server connections 1

10:41:32  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 228, Llog used 1244

10:41:32  Space 'physdbs' added.

.

.

10:41:34  Physical log location changed to dbspace physdbs.

10:41:34  Physical log size changed to 99894 KB.



$ oncheck -pe rootdbs|head -20

 

DBspace Usage Report: rootdbs             Owner: informix  Created: 09/17/2015



 Chunk Pathname                             Pagesize(k)  Size(p)  Used(p)  Free(p)

     1 /chunks/rootdbs                               2  2000000    69570  1930430

 Description                                                   Offset(p)  Size(p)

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

 RESERVED PAGES                                                       0       12

 CHUNK FREELIST PAGE                                                 12        1

 rootdbs:'informix'.TBLSpace                                         13      250

 FREE                                                               263    50000

 LOGICAL LOG: Log file 1                                          50263    10000

 LOGICAL LOG: Log file 2                                          60263    10000

 LOGICAL LOG: Log file 3                                          70263    10000

 LOGICAL LOG: Log file 4                                          80263    10000

 LOGICAL LOG: Log file 5                                          90263    10000

 LOGICAL LOG: Log file 6                                         100263    10000



$ oncheck -pe physdbs

 

DBspace Usage Report: physdbs             Owner: informix  Created: 09/17/2015



 Chunk Pathname                             Pagesize(k)  Size(p)  Used(p)  Free(p)

     2 /chunks/physdbs                               2    50000    50000        0

 Description                                                   Offset(p)  Size(p)

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

 RESERVED PAGES                                                       0        2

 CHUNK FREELIST PAGE                                                  2        1

 physdbs:'informix'.TBLSpace                                          3       50

 PHYSICAL LOG                                                        53    49947

 Total Used:    50000

 Total Free:        0

 

$ onstat -l | head

IBM Informix Dynamic Server Version 12.10.FC5W1 -- On-Line -- Up 00:11:10 -- 1624976 Kbytes

Physical Logging

Buffer bufused  bufsize  numpages   numwrits   pages/io

  P-1  20       2048     563        13         43.31

      phybegin         physize    phypos     phyused    %used   

      2:53             49947      10         20         0.04    

 

$ onstat -d

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

Dbspaces

address          number   flags      fchunk   nchunks  pgsize   flags    owner    name

47581028         1        0x4070001  1        1        2048     N  BA    informix rootdbs

49fa0618         2        0x5070001  2        1        2048     N PBA    informix physdbs

 2 active, 2047 maximum

Chunks

address          chunk/dbs     offset     size       free       bpages     flags pathname

47581258         1      1      0          2000000    1930430               PO-B-- /chunks/rootdbs

4985a028         2      2      0          50000      0                     PO-BE- /chunks/physdbs

 2 active, 32766 maximum

NOTE: The values in the "size" and "free" columns for DBspace chunks are

      displayed in terms of "pgsize" of the DBspace to which they belong.



Expanded chunk capacity mode: always

 

 

physdbs chunk has Flag E (Expandable)

 

 

$ onstat -c | egrep "^PHYS"

PHYSFILE        99894           

PHYSBUFF        4096



$ oncheck -pr

.

.

    Validating PAGE_1CKPT & PAGE_2CKPT...

          Using check point page PAGE_2CKPT.

    Time stamp of checkpoint       0x100c4

    Time of checkpoint             09/17/2015 10:42:13

    Physical log begin address     2:53

    Physical log size              49947 (p)

    Physical log position at Ckpt  10

    Logical log unique identifier  2

    Logical log position at Ckpt   0xd39098 (Page 3385, byte 152)

    Checkpoint Interval            12

    DBspace descriptor page        1:4

    Chunk descriptor page          1:6

    Mirror chunk descriptor page   1:8

.

.

.

    DBspace number                 2

    DBspace name                   physdbs

    Flags                          0x5070001  No mirror chunks

    &                                         Physical/logical log changed

    Number of chunks               1

    First chunk                    2

    Date/Time created              09/17/2015 10:41:32

    Partition table page number    4

    Pagesize (k)                   2

    Logical Log Unique Id          2

    Logical Log Position           0xd2c050

    Oldest Logical Log Unique Id   2

    Last Logical Log Unique Id     0

    Expand Size (Chunk Create)     0.0%

    Expand size (Chunk Extend)     10000k


    DBspace archive status        

          Archive Level            0

          Real Time Archive Began  09/17/2015 10:42:12

          Time Stamp Archive Began 65710

          Logical Log Unique Id    2

          Logical Log Position     0xd37018

.

.

    Chunk number                   2

    Flags                          0x32044    Chunk resides in UNIX file

    &                                         Chunk is online

    &                                         Chunk is extendable

    Chunk path                     /chunks/physdbs

    Chunk offset                   0 (p)

    Chunk size                     50000 (p)

    Number of free pages           0

    DBspace number                 2

.

.

 



CHUNK EXTENDABLE CAN BE TURNED OFF FOR PHYSDBSPACE:



$ echo 'EXECUTE FUNCTION task("modify chunk extendable off", 2);' | dbaccess sysadmin -

Database selected.

 



(expression)  Chunk 2 is no longer extendable.

1 row(s) retrieved.



$ onstat -d

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

Dbspaces

address          number   flags      fchunk   nchunks  pgsize   flags    owner    name

47581028         1        0x4070001  1        1        2048     N  BA    informix rootdbs

49fa0618         2        0x5070001  2        1        2048     N PBA    informix physdbs

 2 active, 2047 maximum

Chunks

address          chunk/dbs     offset     size       free       bpages     flags pathname

47581258         1      1      0          2000000    1930430               PO-B-- /chunks/rootdbs

4985a028         2      2      0          50000      0                     PO-B-- /chunks/physdbs

 2 active, 32766 maximum

NOTE: The values in the "size" and "free" columns for DBspace chunks are

      displayed in terms of "pgsize" of the DBspace to which they belong.



Expanded chunk capacity mode: always

 

 



and back extendable on:

 

$ echo 'EXECUTE FUNCTION task("modify chunk extendable", 2);' | dbaccess sysadmin -



Database selected.

 



(expression)  Chunk 2 is now extendable.

1 row(s) retrieved.

 

 

next extend size

 

$ echo 'SELECT name[1,12],HEX(flags) AS flags, create_size, extend_size, max_size FROM sysdbstab;' | dbaccess sysmaster -



Database selected.

 

name         flags         create_size    extend_size             max_size

rootdbs      0x04070001 10.00000000000 10000.00000000                    0

physdbs      0x05070001           0.00 10000.00000000                    0

2 row(s) retrieved.



$ echo 'SELECT name[1,12],DBINFO("utc_to_datetime",created) AS created FROM sysdbstab;' | dbaccess sysmaster -

Database selected.

 

name         created             

rootdbs      2015-09-17 10:36:29

physdbs      2015-09-17 10:41:32

2 row(s) retrieved.

 

 

Limit the size of plogspace



$ echo 'EXECUTE FUNCTION task("modify space sp_sizes","physdbs","0","10000","100000");' | dbaccess sysadmin -

Database selected.

 



(expression)  Extend size change failed. New max size must be larger than curre

              nt size

1 row(s) retrieved.

$ echo 'EXECUTE FUNCTION task("modify space sp_sizes","physdbs","0","10000","200000");' | dbaccess sysadmin -

Database selected.

 



(expression)  Succeeded: Create size changed to 0, Extend size

              changed to 10000, Max size changed to 200000, for DBspace physdbs

              .

1 row(s) retrieved.

$ echo 'SELECT name[1,12],HEX(flags) AS flags, create_size, extend_size, max_size FROM sysdbstab;' | dbaccess sysmaster -

Database selected.

 

name         flags         create_size    extend_size             max_size

rootdbs      0x04070001 10.00000000000 10000.00000000                    0

physdbs      0x05070001           0.00 10000.00000000                  195

2 row(s) retrieved.

 

 

NOTE:



The units of max_size in "modify space sp_sizes" are kByte.

The units in sysmaster:sysdbstab are MByte.

200000/1024 = 195

 

 

You can set the max_size back to unlimited with:

 

$ echo 'EXECUTE FUNCTION task("modify space sp_sizes","physdbs","0","10000","0");' | dbaccess sysadmin -

Database selected.

 



(expression)  Succeeded: Create size changed to 0, Extend size

              changed to 10000, Max size changed to 0, for DBspace physdbs.

1 row(s) retrieved.



$ echo 'SELECT name[1,12],HEX(flags) AS flags, create_size, extend_size, max_size FROM sysdbstab;' | dbaccess sysmaster -

Database selected.

 

name         flags         create_size    extend_size             max_size

rootdbs      0x04070001 10.00000000000 10000.00000000                    0

physdbs      0x05070001           0.00 10000.00000000                    0

2 row(s) retrieved.

 

 



Physical log auto tuning

 

If you have created this new plogspace (expandable) and your physical log is to small, than

the physical log will be automatic extended (if AUTO_TUNE is set to 1)

For a little test I have modified the size of the physical log to 1000 kbyte.

 

$ onstat -g cfg AUTO_TUNE

IBM Informix Dynamic Server Version 12.10.FC5W1 -- On-Line -- Up 00:04:16 -- 1624976 Kbytes

name                      current value

AUTO_TUNE                 1

 

$ onparams -p -s 1000 -d physdbs -y

Log operation started. To monitor progress, use the onstat -l command.

** WARNING ** Because the physical log has been modified, a level 0 archive

must be taken of the following space before an incremental archive will be

permitted for it: physdbs

(see Dynamic Server Administrator's manual)

 

$ n=0

$ while [ $n -le 100 ]

do

n=`expr $n + 1`

echo "n" | dbaccessdemo -nots >/dev/null 2>&1

done

 

$ tail -100 $ONLINELOG

12:03:10  Physical log size changed to 1000 KB.

12:03:17  Performance Advisory: The physical log is running out of room during checkpoint processing.

12:03:17   Results: Transactions are being blocked until the checkpoint is complete.

12:03:17   Action: Increase the physical log size.

.

.

12:03:18  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 0, Llog used 2

12:03:19  ** AUTO TUNING - Physical Log extended by 100Kb. - New size 1100Kb.

12:03:19  Performance Advisory: The physical log is running out of room during checkpoint processing.

12:03:19   Results: Transactions are being blocked until the checkpoint is complete.

12:03:19   Action: Increase the physical log size.

12:03:19  Checkpoint Completed:  duration was 0 seconds.

.

.

12:03:22  ** AUTO TUNING - Physical Log extended by 110Kb. - New size 1210Kb.

12:03:23  Logical Log 28 Complete, timestamp: 0xf08ee.

12:03:25  Logical Log 29 Complete, timestamp: 0xf97e0.

12:03:27  Logical Log 30 Complete, timestamp: 0x102b3c.

12:03:29  Logical Log 31 Complete, timestamp: 0x10baf1.

.

.

12:03:33  ** AUTO TUNING - Physical Log extended by 121Kb. - New size 1330Kb.

12:03:33  Performance Advisory: The physical log is running out of room during checkpoint processing.

12:03:33   Results: Transactions are being blocked until the checkpoint is complete.

12:03:33   Action: Increase the physical log size.

12:03:33  Checkpoint Completed:  duration was 0 seconds.

.

.

12:03:35  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 0, Llog used 2

12:03:36  ** AUTO TUNING - Physical Log extended by 133Kb. - New size 1462Kb.

12:03:36  Performance Advisory: The physical log is running out of room during checkpoint processing.

12:03:36   Results: Transactions are being blocked until the checkpoint is complete.

12:03:36   Action: Increase the physical log size.

12:03:36  Checkpoint Completed:  duration was 0 seconds.

.

.

.

.

12:04:13  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 0, Llog used 2

12:04:14  ** AUTO TUNING - Physical Log extended by 258Kb. - New size 2842Kb.

12:04:15  Logical Log 46 Complete, timestamp: 0x18d752.



$ tail -1000 $ONLINELOG | grep "AUTO TUNE"

12:03:19  ** AUTO TUNING - Physical Log extended by 100Kb. - New size 1100Kb.

12:03:22  ** AUTO TUNING - Physical Log extended by 110Kb. - New size 1210Kb.

12:03:33  ** AUTO TUNING - Physical Log extended by 121Kb. - New size 1330Kb.

12:03:36  ** AUTO TUNING - Physical Log extended by 133Kb. - New size 1462Kb.

12:03:39  ** AUTO TUNING - Physical Log extended by 146Kb. - New size 1608Kb.

12:03:42  ** AUTO TUNING - Physical Log extended by 160Kb. - New size 1768Kb.

12:03:45  ** AUTO TUNING - Physical Log extended by 176Kb. - New size 1944Kb.

12:03:48  ** AUTO TUNING - Physical Log extended by 194Kb. - New size 2138Kb.

12:03:51  ** AUTO TUNING - Physical Log extended by 213Kb. - New size 2350Kb.

12:04:11  ** AUTO TUNING - Physical Log extended by 235Kb. - New size 2584Kb.

12:04:14  ** AUTO TUNING - Physical Log extended by 258Kb. - New size 2842Kb.

 



So, the physical log each time is growing by 10%.

And this seems to be triggered by the (Plog) checkpoint:

 

$ onstat -g ckp

IBM Informix Dynamic Server Version 12.10.FC5W1 -- On-Line -- Up 00:54:17 -- 1624976 Kbytes

AUTO_CKPTS=Off   RTO_SERVER_RESTART=Off   

                                                                    Critical Sections                          Physical Log    Logical Log    

           Clock                                  Total Flush Block #      Ckpt  Wait  Long  # Dirty   Dskflu  Total    Avg    Total    Avg   

Interval   Time      Trigger    LSN               Time  Time  Time  Waits  Time  Time  Time  Buffers   /Sec    Pages    /Sec   Pages    /Sec  

68         12:03:42  Plog       35:0x7d8018       0.0   0.0   0.0   1      0.0   0.0   0.0   1212      1212    663      663    1550     1550  

69         12:03:43 *Admin      35:0x161b594      0.0   0.0   0.0   0      0.0   0.0   0.0   1514      1514    648      648    3651     3651  

70         12:03:44 *Admin      35:0x161d044      0.0   0.0   0.0   0      0.0   0.0   0.0   0         0       25       25     2        2     

71         12:03:45  Plog       35:0x18971dc      0.0   0.0   0.0   1      0.0   0.0   0.0   721       721     729      729    634      634   

72         12:03:45  Plog       35:0x24b34d8      0.0   0.0   0.0   1      0.0   0.0   0.0   1502      1502    729      729    3100     3100  

73         12:03:45  Plog       36:0x16c29c       0.0   0.0   0.0   1      0.0   0.0   0.0   868       868     729      729    969      969   

74         12:03:46 *Admin      36:0x3961b4       0.0   0.0   0.0   1      0.0   0.0   0.0   444       444     72       72     554      554   

75         12:03:47 *Admin      36:0x398044       0.0   0.0   0.0   0      0.0   0.0   0.0   0         0       0        0      2        2     

76         12:03:48  Plog       36:0x9d6018       0.0   0.0   0.0   1      0.0   0.0   0.0   1261      1261    801      801    1598     1598  

77         12:03:49 *Admin      36:0x176f1d4      0.0   0.0   0.0   0      0.0   0.0   0.0   1514      1514    771      771    3481     3481  

78         12:03:50 *Admin      36:0x1771044      0.0   0.0   0.0   0      0.0   0.0   0.0   0         0       0        0      2        2     

79         12:03:58  Llog       41:0x164          0.0   0.0   0.0   1      0.0   0.0   0.0   1576      1576    235      29     43999    5499  

80         12:04:08  Llog       46:0x60           0.0   0.0   0.0   1      0.0   0.0   0.0   1628      1628    842      84     50000    5000  

81         12:04:08  Plog       46:0x6b0018       0.0   0.0   0.0   1      0.0   0.0   0.0   1309      1309    881      881    1712     1712  

82         12:04:09 *Admin      46:0x12422a0      0.0   0.0   0.0   0      0.0   0.0   0.0   1510      1510    847      847    2962     2962  

83         12:04:10 *Admin      46:0x1244044      0.0   0.0   0.0   0      0.0   0.0   0.0   0         0       23       23     2        2     

84         12:04:11  Plog       46:0x1780018      0.0   0.0   0.0   1      0.0   0.0   0.0   1075      1075    969      969    1340     1340  

85         12:04:11  Plog       46:0x1f8d018      0.0   0.0   0.0   1      0.0   0.0   0.0   1500      1500    969      969    2061     2061  

86         12:04:12 *Admin      46:0x2610018      0.0   0.0   0.0   1      0.0   0.0   0.0   1261      1261    688      688    1667     1667  

87         12:04:13 *Admin      46:0x2612044      0.0   0.0   0.0   0      0.0   0.0   0.0   0         0       0        0      2        2     

Max Plog       Max Llog       Max Dskflush   Avg Dskflush   Avg Dirty      Blocked      

pages/sec      pages/sec      Time           pages/sec      pages/sec      Time         

200            200            0              1068           782            0