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
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.
| | Reply to Group Online View Thread Recommend Forward |
Original Message: Sent: 11/23/2020 8:19:00 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.