MySQL Bugs: #59113: MySQL Cluster : Large Blob read/write operations cause excessive resource usage

Bug #59113	MySQL Cluster : Large Blob read/write operations cause excessive resource usage
Submitted:	22 Dec 2010 16:15	Modified:	6 Jan 2011 22:56
Reporter:	Frazer Clement	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	mysql-5.1-telco-7.0	OS:	Any
Assigned to:	Frazer Clement	CPU Architecture:	Any

Description:
Currently, MySQLD reads and writes all part rows associated with a single Blob value in a single batch.  This minimises latency for small Blobs, but can create excessive resource usage spikes for large Blobs.

Resources such as SendBuffer, LongMessageBuffer and JobBuffer can hit limits due to this behaviour. 

How to repeat:
Increase the MySQLD max packet size (effective limit on Blob size from MySQLD)

Create a table with a LONGTEXT column.

Insert a very large BLOB value into the table

Read it/update it etc.

Observe resource usage issues.

Suggested fix:
Implement a configurable Blob data 'batch size', allowing large Blobs to be read/written in multiple execute batches.

Default this to something reasonable.

Implemented at NdbApi level as it's generally useful, and easier to implement there.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/127519

4080 Frazer Clement	2010-12-22
      Bug#59113 MySQL Cluster : Large Blob read/write operations cause excessive resource usage
      
      NdbTransaction.hpp has two settings added :
      
        MaxPendingBlobReadBytes
        MaxPendingBlobWriteBytes
      
      When the volume of data being read/written to Blob column part tables in 
      the transaction exceeds these values, the transaction will implicitly execute
      the accumulated operations.
      
      This avoids an excessive build up of pending data which can result in resource
      exhaustion in the kernel.
      
      By default these parameters are set to 0, which means no limit.
      
      At the MySQLD level, two new session variables are added :
      
        ndb_blob_read_batch_bytes
        ndb_blob_write_batch_bytes
      
      These control the setting of the per-transaction values above.  
      They can be defaulted for all transactions on the MySQLD command line.
      
      When no default is given, they both default to 64kB.
      
      This patch should improve the stability of MySQL Cluster when large Blob 
      values are being read and written.

Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.21 (revid:frazer@mysql.com-20101222162332-fq0fosrp28n6gcgt) (version source revid:frazer@mysql.com-20101222162332-fq0fosrp28n6gcgt) (merge vers: 5.1.51-ndb-7.0.21) (pib:24)

Also pushed to 7.1.10

Documented as follows in the NDB-7.0.21 and 7.1.10 changelogs:

        In some circumstances, very large BLOB read and write operations
        in NDB applications can cause excessive resource usage
        and even exhaustion of memory. To fix this issue and to provide
        increased stability when performing such operations, it is now
        possible to set limits on the volume of, it is now possible to 
        set limits on the volume of BLOB data to be read or written 
        within a given transaction in such a way that when these limits 
        are exceeded, the current transaction implicitly executes any 
        accumulated operations. This avoids an excessive buildup of 
        pending data which can result in resource exhaustion in the 
        NDB kernel. The limits on the amount of data to be read and on 
        the amount of data to be written before this execution takes 
        place can be configured separately. (In other words, it is now 
        possible in MySQL Cluster to specify read batching and write 
        batching that is specific to BLOB data.) These limits can be 
        configured either on the NDB API level, or in the MySQL Server.

        On the NDB API level, four new methods are added to the
        NdbTransaction object. getMaxPendingBlobReadBytes() and
        setMaxPendingBlobReadBytes() can be used to get and to set,
        respectively, the maximum amount of BLOB data to be read that
        accumulates before this implicit execution is triggered.
        getMaxPendingBlobWriteBytes() and setMaxPendingBlobWriteBytes()
        can be used to get and to set, respectively, the maximum volume
        of BLOB data to be written that accumulates before implicit
        execution occurs.

        For the MySQL server, two new options are added. The
        --ndb-blob-read-batch-bytes option sets a limit on the amount of
        pending BLOB data to be read before triggering implicit
        execution, and the --ndb-blob-write-batch-bytes option controls
        the amount of pending BLOB data to be written. These limits can
        also be set using the mysqld configuration file, or read and set
        within the mysql client and other MySQL client applications
        using the corresponding server system variables.

Also added descriptions of the new mysqld options to the Cluster chapter in the Manual, and of the new NdbTransaction methods to the NDB API documentation.

Complete docs changes at http://lists.mysql.com/commits/128129

Closed.