Informix

nested-group-icon.png

DB2

Expand all | Collapse all

Wait on buffer log

  • 1.  Wait on buffer log

    Posted Mon November 23, 2020 10:58 AM
    We have a situation where we are storing PDF files in a table each file size is more than 100mb and log buffer size is 1mb.

    We are observing wait on buffer logs and slow response on other OLTP transactions. We have also  HDR and two RSS nodes .


    ------------------------------
    Khurram Shahzad
    ------------------------------


  • 2.  RE: Wait on buffer log

    Posted Tue November 24, 2020 01:04 PM
    Khurram:

    I just remembered the Large Object Locator Function extensions which make it easy to manage large files as external objects in the filesystem but treat them as if they are BLOB or CLOB type large objects! You will still have to store copies on each host (primary, HDR, & RSS) but at least you can easily work with them. See the link here for details:

    https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.dbext.doc/ids_dbxt_453.htm

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: Wait on buffer log

    Posted Wed November 25, 2020 11:30 AM
    Khurram:

    IB that you posted this to the WhatsApp group last week. For anyone else reading this thread, the problem is that every document takes over 100 logical log buffers tying up the three physical log buffers preventing any other transactions from being written to the buffers 12K at a time (you noted that the dbspace has 12K pages) until the entire blob has been written. This is an issue for larger logged dumb blobs (so BYTE and TEXT type columns created IN TABLE) whether they replicate or not, but becomes a major issue when you have HDR and/or RSS secondaries to which all of that data has to be transmitted. Using smart blob columns (BLOB or CLOB) is a bit better as the smart blob is transmitted separately from the rest of the transaction using separate buffering, but it is still not great.

    The best solution is to move the documents onto a filesystem that is either shared between the primary and secondaries (dangerous I know and not really workable with remote secondaries) or replicated at the storage level using the SAN's technology. If you do this then the table will only contain the file path or even just the file name.

    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.








  • 4.  RE: Wait on buffer log

    Posted Mon November 30, 2020 09:50 AM

    Hi,

    Thanks for your reply.

    If I increase log buffer size to 64 MB , Would it help?

    Secondly what would be the impact on OLTP .

    Regards,
    **************************
    Khurram Shahzad
    Sr. Architect
    Database Operations
    i2c Inc.

    1300 Island Drive, Suite 105
    Redwood City, CA 94065-5170
    +1 650.593.5400, ext. 4126

    www.i2cinc.com
    **************************
    CONFIDENTIALITY CAUTION
    This communication (including any accompanying documents) is intended only for the use of the addressee(s) and contains information that is PRIVILEGED AND CONFIDENTIAL. Unauthorized reading, dissemination, distribution, or copying of this communication is prohibited. If you have received this communication in error,please notify us immediately by e-mail, telephone, or fax, and promptly destroy the original communication. Thank you for your cooperation.



    On 11/25/2020 9:32 PM, Art Kagel via IBM Community wrote:
    01000176003fbd9b-f180736f-679e-4e52-9ba4-2403f8689953-000000@email.amazonses.com">
    Khurram: IB that you posted this to the WhatsApp group last week. For anyone else reading this thread, the problem is that every document takes... -posted to the "Informix" group
    IBM Community

    Informix

    Post New Message
    Re: Wait on buffer log
    Reply to Group Reply to Sender
    Art Kagel
    Nov 25, 2020 11:30 AM
    Art Kagel
    Khurram:

    IB that you posted this to the WhatsApp group last week. For anyone else reading this thread, the problem is that every document takes over 100 logical log buffers tying up the three physical log buffers preventing any other transactions from being written to the buffers 12K at a time (you noted that the dbspace has 12K pages) until the entire blob has been written. This is an issue for larger logged dumb blobs (so BYTE and TEXT type columns created IN TABLE) whether they replicate or not, but becomes a major issue when you have HDR and/or RSS secondaries to which all of that data has to be transmitted. Using smart blob columns (BLOB or CLOB) is a bit better as the smart blob is transmitted separately from the rest of the transaction using separate buffering, but it is still not great.

    The best solution is to move the documents onto a filesystem that is either shared between the primary and secondaries (dangerous I know and not really workable with remote secondaries) or replicated at the storage level using the SAN's technology. If you do this then the table will only contain the file path or even just the file name.

    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.





      Reply to Group Online   View Thread   Recommend   Forward  




     
    You are receiving this notification because you followed the 'Wait on buffer log' message thread. If you do not wish to follow this, please click here.

    Update your email preferences to choose the types of email you receive

    Unsubscribe from all participation emails

    Emerge with Resilency



    Original Message:
    Sent: 11/23/2020 1:03:00 PM
    From: Art Kagel
    Subject: RE: Wait on buffer log

    Khurram:

    IB that you posted this to the WhatsApp group last week. For anyone else reading this thread, the problem is that every document takes over 100 logical log buffers tying up the three physical log buffers preventing any other transactions from being written to the buffers 12K at a time (you noted that the dbspace has 12K pages) until the entire blob has been written. This is an issue for larger logged dumb blobs (so BYTE and TEXT type columns created IN TABLE) whether they replicate or not, but becomes a major issue when you have HDR and/or RSS secondaries to which all of that data has to be transmitted. Using smart blob columns (BLOB or CLOB) is a bit better as the smart blob is transmitted separately from the rest of the transaction using separate buffering, but it is still not great.

    The best solution is to move the documents onto a filesystem that is either shared between the primary and secondaries (dangerous I know and not really workable with remote secondaries) or replicated at the storage level using the SAN's technology. If you do this then the table will only contain the file path or even just the file name.

    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.







  • 5.  RE: Wait on buffer log

    Posted Mon November 30, 2020 09:53 AM
    Thanks for your reply.

    If I increase log buffer size to 64 MB , Would it help? my database mode is unbuffered logging and in many tables we are also using text and lvarchar columns.

    Secondly what would be the implications of big log buffer as we are running OLTP system.

    ------------------------------
    Khurram Shahzad
    ------------------------------



  • 6.  RE: Wait on buffer log

    Posted Fri November 27, 2020 04:51 AM
    ...

    ------------------------------
    Khurram Shahzad
    ------------------------------



  • 7.  RE: Wait on buffer log

    Posted Mon November 30, 2020 10:08 AM
    Khurram,

    I would surmise you are storing the PDFs as byte blobs in the tablespace. Hence the buffer waits.  Please check the schema for this.  Blobs should generally be stored in a blobspace and I think this is not the case in your situation.

    Run from the shell:
    dbschema -ss -d <your database> -t <table with the blob column>
    and post that output here.  Confidentiality issue?  Just post the complete column definition - the column[s] where you are storing the PDFs - from the resulting schema output.

    If this is indeed the problem, you solution is to:
    1. Create a new table with identical schema but with BLOB columns defined in a blobspace
    2. Copy the old table into the new table
    3a. Rename the old table <table>_old
    3b. Rename the new table to the original table name

    How to copy the table? Well If you have enough log space perhaps you can try a simple:
    insert into <new table> select * from <old_table>
    but chances are you don't have log space for that.  So you need a table-copy utility.  This is more work:

    Both Art and I have posted table-copy utilities.  Art's is in one of the utils_ak posts and is written is C so it will require compiling.  Mine is called fcopy_table; it's written in Perl and requires that you have both DBI and DBD::Informix (Perl packages) installed.

    Hey, let us know how it works out.

    ------------------------------
    Jacob Salomon
    ---
    Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra
    ------------------------------