… memory for table cache, especially if you’re using large blobs. It is easy to check though. Run “FLUSH TABLES” and… MySQL to identify potential causes. Is it working with large blobs ? Using user variables ? Prepared Statements ? memory tables ? In a lot…
Comment: Finding out largest tables on MySQL Server
March 30, 2012 By 24 Comments
… ( id = primary key, categID = int index, subcategID = int index, content= blob [ this field has a serialized array - avg 9KB ] ) ; this table…
Post: Blob Storage in Innodb
February 9, 2010 By 13 Comments
… the row page itself. With two of 7KB blobs we will have one blob stored on the page completely while another will… in desire to keep code simple while implementing initial BLOB support for Innodb – BLOB can have prefix index and it was easier… to beware with Innodb BLOB storage is the fact external blob pages are not shared among the blobs. Each blob, even if it has…
Post: MySQL Blob Compression performance benefits
January 11, 2008 By 17 Comments
… to keep it compressed. Unfortunately MySQL does not provide compressed BLOB/TEXT columns (I would really love to have COMPRESSED attribute for the BLOB/TEXT columns which would make them transparently compressed) but you… and performance gains, especially for Innodb tables. With Innodb tables BLOB gets its own page (allocated outside of clustered index) if…
Post: Recover BLOB fields
July 1, 2010 By 4 Comments
… Tool. The reason consists in a special way InnoDB stores BLOBs. An InnoDB table is stored in a clustered index called… brings prerequisites to successfully recover a record with BLOB : all pieces of the BLOB field must be reachable by pointers. That means… tool outputs the recovered table in tab-separated values format. BLOBs are printed in a hex form – 0ACD86… To upload the…
Post: Bug#12704861
November 20, 2011 By 1 Comment
… issue in tablespace extension. If the request to allocate a BLOB page leads to the tablespace being extended, crash recovery could… of a mini-transaction allocated (nonfree=TRUE) before storing the BLOBs, or freed (nonfree=FALSE) before committing the mini-transaction. btr… issue in tablespace extension. If the request to allocate a BLOB page leads to the tablespace being extended, crash recovery could…
Comment: Blob Storage in Innodb
February 17, 2010 By 13 Comments
… the filesystem for that 1 InnoDB table that holds the blob. Queries against this table will use the primary index, and… for indexing or searching. If you think you need multiple blob columns, then as Peter suggested, consider combining them into 1 blob and on the application side splitting up the blobs. That might not so easy either…
Comment: Blob Storage in Innodb
February 10, 2010 By 13 Comments
… of difference. When it makes the difference is even larger blobs which can use less external pages. When compressed. Check this… some 50 times smaller 
Also note this less than 1MB blob takes about 1.5M this is because space allocation for… than 16K. This is actually a strange decision for blobs as Blobs never grow and could be allocated to exact size. If…
Comment: When should you store serialized objects in the database?
January 24, 2010 By 42 Comments
… time, or between customer installations; b) the data in the blob is almost always retrieved, read, and updated all-at-once…: 1) the above-mentioned update cost: update an entire 1K blob to change one value; 2) complete inability to enforce meaningful… required by application design changes. Generally, I only consider e-blob for non-essential data which is going to vary by…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 17 Comments
… another magic number to experiment with. Q17: How InnoBD handles blobs/text fields (needs more info) According to the documentation, InnoDB… 8100 bytes InnoDB will not allocate additional space for blobs, even if each blob is larger than 768. 2. InnoDB will allocate additional space outside of the page for _each_ blob (if we have 8 blobs 8xN bytes will be allocated) Question: How much…
