Db2

 View Only

  • 1.  Not Logged Initially is running things differently?

    Posted Fri March 29, 2024 01:33 AM
    Hi,
    Activate not logged initially takes 22 minutes for completion of update query with 500 thousand index writes.
    If I run as a simple update query provides sufficient log space it takes 60 minutes with 2 million index writes.
     
    So the culprit here is the number of index writes for the same query execution under two different scenarios.
     
    Why is the question?
    The performance gain of NLI is not from less transaction logging but rather over head in index writes or the way the query is executing itself internally.
    I am yet to generate explain from section and compare the results.
    Please confirm if someone has faced similar issues.


    ------------------------------
    Harishkumar Pathangay
    ------------------------------


  • 2.  RE: Not Logged Initially is running things differently?

    IBM Champion
    Posted Fri March 29, 2024 03:33 PM
    Edited by Jan Nelken Fri March 29, 2024 03:45 PM

    I do not expect NLI insert behave differently - main difference is in COMMIT processing.

    In logged scenario each COMMIT - explicit or implicit (auto commit) - will cause inserted row (data or index) to be logged and externalized to log files;

    In NLI during COMMIT - explicit or implicit - processing all modified pages are flushed to disk.



    ------------------------------
    Jan Nelken
    ------------------------------

    Original Message


  • 3.  RE: Not Logged Initially is running things differently?

    Posted Sat March 30, 2024 04:11 AM
    I am definitely Missing something here. I have to properly track the time taken for transactional log writing.
    Is there any metrics in db2 which will tell this connection or application or database has spent so much time in pushing log records into transactional log files.?
    Can I monitor it at query level or application or agent level?

    Please let me know.

    Thanks
    Harish Kumar 



    Original Message


  • 4.  RE: Not Logged Initially is running things differently?

    IBM Champion
    Posted Sat April 06, 2024 08:50 AM

    Hi Harish,

    I think you found MON_GET_CONNECTION by now.

    https://www.ibm.com/docs/en/db2/11.5?topic=functions-mon-get-connection-get-connection-metrics

    Cheers
    Roland



    ------------------------------
    Roland Schock
    IBM Champion and IBM Gold Consultant
    ------------------------------

    Original Message


  • 5.  RE: Not Logged Initially is running things differently?

    Posted Thu April 11, 2024 10:08 AM

    that link contains 2 pdf files one with NLI ON and other with NLI OFF.

    summary:

    NLI ON average run time is 20 minutes; index writes 380 thousand

    NLI OFF average run time is 30 minutes; index writes 700 thousand

    how can it execute sample plan but different index writes and the claim that not logging is improving performance is not acceptable if double the amount of index writes are happening.

    conceptually it does not make sense. any clarifications inputs are welcome.

    https://drive.google.com/drive/folders/1guc_7NO_4b5n28pH0EPiRSaq9HoOKedC?usp=sharing

    thanks,

    harish pathnagay



    ------------------------------
    Harishkumar Pathangay
    ------------------------------

    Original Message


  • 6.  RE: Not Logged Initially is running things differently?

    Posted Tue April 16, 2024 12:51 PM

    There is not enough factual information in the question to make any sensible comment. You could append the db2exfmt (detailed plan) output for the query in both cases, and give the section-actuals information in both cases, along with the table and index DDL in full, and some details of how you are running the update (#transactions, isolation, concurrency etc).  Sometimes things are not what they seem.



    ------------------------------
    Mike O'Reilly
    ------------------------------

    Original Message


  • 7.  RE: Not Logged Initially is running things differently?

    Posted Sat April 20, 2024 10:33 AM
    Here is all the information i can give you.
    It contains files with commands i executed.
    Db cfg output
    Section Actuals Captured for both cases

    Please let me know if i need to summarize my activity so far.

    --
    Thanks,
    Harish P



    Original Message


  • 8.  RE: Not Logged Initially is running things differently?

    Posted 18 days ago

    Hi,

    Please have a look at video

    YouTube. Com/db2luwacademy

    https://youtu.be/jdOP3oNv5PE?si=HcQbyQFf78u42Dmk

    I had to struggle a lot and give my opinion on the issue I faced.  Please do not assume that I already knew it and trying to create some brand value. 

    Honestly I am learning db2 have very little real time experience. 

    Please help in spreading the word about how to improve it's performance,  one way is to perform direct io instead of synchronous io assuming that is the real cause of slow down.

    I cannot reach ibm as I am individual. If clients,  people with good reputation like you may discuss with your network  and real time experience bring it to attention it is well heard in the community. 

    Please also validate if my opinion is correct in that video. Do not mistake me for asking such questions or help. Just for db2 only I am doing this. Kindly I request you do not mistake me.

    Whenever you have free time you can have a look into the issue or questions raised. 


    Thanks very much sir.


    Harish Kumar. 






    Original Message