Informix

nested-group-icon.png

DB2

Expand all | Collapse all

Logical Logs/Physical logs

  • 1.  Logical Logs/Physical logs

    Posted Fri June 05, 2020 05:21 PM
    IDS 12.10.FC12
    Solaris 10 1/13

    If you had to choose having a separate dbspace for logical logs or physical logs, but not both, which would you choose?

    I'm uncertain which is likely to generate more I/O.

    Thank you.

    Regards,

    DG

    ------------------------------
    David Grove
    ------------------------------


  • 2.  RE: Logical Logs/Physical logs

    Posted Fri June 05, 2020 05:37 PM
    David:

    By definition, the logical logs will tend to get more IO than the physical logs. There are exceptions such as when many smallish rows live on a page but only one row on each of many pages are being frequently updated. Then there will be one PL page for each LL entry and the former will be larger. But that's an edge condition that is rare in practice.

    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    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.








  • 3.  RE: Logical Logs/Physical logs

    Posted Fri June 05, 2020 05:44 PM
    Thank you, Art.

    I'm setting up a small test platform, and I was uncertain.  I have room for one more disk slice, and thinking maybe I had the right thinking, I actually already set it up as logical log space.  But, I was uncertain enough to check with the "big guns" first, and I had left things in an incomplete state so I could change, if I got feedback to the contrary.

    I think Andrew Ford has also posted some similar thinking in his blog at his site.

    Thank you.

    DG

    ------------------------------
    David Grove
    ------------------------------



  • 4.  RE: Logical Logs/Physical logs

    Posted Fri June 05, 2020 10:46 PM

    David,


    Definitely Logical logs. Logical logs contains all of the transactions, checkpoint information, and any chunk allocations if needed.

    If your database or databases are?? not logged, the Logical logs will not generate much IO since there are not transactions logged; in this case the logical logs dbspace will not be very important as far as IO.

    Most of the time the databases are logged, in this case you need to look at the logging mode (Buffered, unbuffered). The buffered mode generates far less IO in the logs since the IO are done when the log buffer is full and not at commit time (less secure in case of a crash). Small transactions generate a lot of IO. Large transactions generate less logs.

    I would say:

    - no logging databases ==> Physical logs should have a priority

    - buffered logging databases => it depends but I give the priority to the logical logs

    - unbuffered or ansi mode databases => logical logs should have a priority

    However, you should be aware that you can have a mixture of databases in the same instance and you can also have differents logging modes (1 database not logged, 1 database in buffered mode , 1 database in unbuffered mode, etc). Since there is one central logical log (spread in several logs),?? in this case the unbuffered database(s) will win since the commits provoke an IO in the log no matter what was in the log buffer. If you have several databases with differents logging mode, you could may be have different instances?? and group databases by logging mode.

    Overall, most OLTP applications should have a priority for the logical logs instead of the physical logs.

    Note: DSS databases that do not have transactions do not write in the logical logs if they are only SELECTS or mostly SELECTS. THese databases should be in a separate instance that do not give a priority to the logical logs.

    So the decision depends on several things that need to be analyzed.

    --   Khaled Bentebal  Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web:  www.consult-ix.fr  





  • 5.  RE: Logical Logs/Physical logs

    Posted Sat June 06, 2020 09:52 AM

    why not just monitor the chunk writes  to the phys & logi chunks with onstat -D for a definitive period of time ... should know very quickly.

     

     

     






  • 6.  RE: Logical Logs/Physical logs

    Posted Sun June 07, 2020 09:42 PM
    Thank you Khaled and Mark, too.

    1) All our databases use unbuffered logging.

    2) Indeed, I am curious, so I'll run onstat -D

    David Grove

    ------------------------------
    David Grove
    ------------------------------



  • 7.  RE: Logical Logs/Physical logs

    Posted Sun June 07, 2020 11:13 PM
    David,

    1/?? onstat -D will only work for you if you?? already have a setup
    running with a physical log totally by itself?? in a dbspace and you will
    have to add up the writes for all of the chunks of that dbspace AND
    another dbspace or dbspaces that include all of the logical logs and no
    other information shares these dbspaces ( such as other tables). If that
    is the case, onstat -D will be give you information that will allow you
    to compare and make your decision.

    2/ Otherwise, it will be difficult to?? know which?? consumes mores IO
    since your physical log could be in the rootdbs or elsewhere and the
    same for the logical logs that could also be in the rootdbs or spread on
    different dbspaces.

    I think that for your case, having all of the databases in unbuffered
    mode, you will most likely have more IO for the logical logs. So I would
    give the priority to the logical logs.

    --

    Khaled Bentebal

    Mobile: 33 (0) 6 07 78 41 97
    Email: khaled.bentebal@consult-ix.fr
    Site Web: www.consult-ix.fr




  • 8.  RE: Logical Logs/Physical logs

    Posted Mon June 08, 2020 01:57 AM
    Thank you, Khaled, for the follow-up.

    I had not yet refreshed myself on the documentation regarding onstat -D before I responded.
    I do not have the physical logs in a separate (since that was the essence of my question).

    Guess that will teach me (yet again!) to read first, before posting.

    Regards,

    DG

    ------------------------------
    David Grove
    ------------------------------



  • 9.  RE: Logical Logs/Physical logs

    Posted Mon June 08, 2020 12:03 PM

    David,

     

    The second part of my earlier reply was somehow omitted :

     

    onstat -l can also be used. numwrites are number of writes to disk regardless where their respective dbspaces reside.

    at the end of they day the logical log will be on top of the leader board most likely.

     

     

    onstat -l | head -20

     

    IBM Informix Dynamic Server Version 12.10.FC8 -- On-Line -- Up 17 days 21:47:24 -- 12729152 Kbytes

     

    Physical Logging

    Buffer bufused  bufsize  numpages   numwrits   pages/io

      P-2  28       32       66413      2098       31.66

          phybegin         physize    phypos     phyused    %used

          2:53             2097099    323694     1779       0.08

     

    Logical Logging

    Buffer bufused  bufsize  numrecs    numpages   numwrits   recs/pages pages/io

      L-2  0        16       1029758    76568      59184      13.4       1.3

            Subsystem    numrecs    Log Space used

            OLDRSAM      1014875    100174364

            SBLOB        13406      43302328

            CDR          1457       78112

            HA           20         880

     

     






  • 10.  RE: Logical Logs/Physical logs

    Posted Mon June 08, 2020 12:52 PM
    Thank you, Mark.

    This is something I should have thought of, as I use onstat -l frequently.

    A quick check verifies that there are about 3 orders of magnitude difference in writes between logical and physical logs!

    DG

    ------------------------------
    David Grove
    ------------------------------