Wait on buffer log

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 ------------------------------ #Informix

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 ------------------------------

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 www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ 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.

... ------------------------------ Khurram Shahzad ------------------------------

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.

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 ------------------------------

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 ------------------------------