Bug #38002 table_cache consumes too much memory with blobs
Submitted: 9 Jul 2008 21:23 Modified: 14 Aug 2008 3:51
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.x, 6.x OS:Any
Assigned to: Kristofer Pettersson CPU Architecture:Any
Tags: table_cache

[9 Jul 2008 21:23] Shane Bester
Description:
the simple testcase in how to repeat section causes about 1GB of ram to be used until you  run 'flush tables' to get the tables out of table cache.  why are these huge rows being cached?  Here's a snippet from mysqladmin debug:

Warning: Not freed memory segments: 147
Warning: Memory that was not free'ed (1057202406 bytes):
..
104857608 bytes at 0x044a30098, allocated at line   51 in '.\sql_string.cc'
104857608 bytes at 0x03e620098, allocated at line   51 in '.\sql_string.cc'
104857608 bytes at 0x038210098, allocated at line   51 in '.\sql_string.cc'
104857608 bytes at 0x031e00098, allocated at line   51 in '.\sql_string.cc'
104857608 bytes at 0x02b9f0098, allocated at line   51 in '.\sql_string.cc'
...

How to repeat:
flush tables;
show open tables;
drop table if exists t0,t1,t2,t3,t4,t5,t6,t7,t8,t9;
set global table_cache=20;
set global max_allowed_packet=1024*1024*1024;
\r
create table t0(a longblob)engine=myisam;
create table t1 like t0;
create table t2 like t0;
create table t3 like t0;
create table t4 like t0;
create table t5 like t0;
create table t6 like t0;
create table t7 like t0;
create table t8 like t0;
create table t9 like t0;
insert into t0 values (repeat('a',100*1024*1024));
insert into t1 values (repeat('a',100*1024*1024));
insert into t2 values (repeat('a',100*1024*1024));
insert into t3 values (repeat('a',100*1024*1024));
insert into t4 values (repeat('a',100*1024*1024));
insert into t5 values (repeat('a',100*1024*1024));
insert into t6 values (repeat('a',100*1024*1024));
insert into t7 values (repeat('a',100*1024*1024));
insert into t8 values (repeat('a',100*1024*1024));
insert into t9 values (repeat('a',100*1024*1024));

Suggested fix:
this could be fixed, or documented in http://dev.mysql.com/doc/refman/5.0/en/table-cache.html

folks with large table cache and many tables with blobs (or not!) can see alot of wasted memory.
[9 Jul 2008 22:48] MySQL Verification Team
Thank you for the bug report.
[10 Jul 2008 11:34] MySQL Verification Team
maybe related to bug #37953 ?
[11 Jul 2008 19:12] Konstantin Osipov
Quick fix:
kostja@bodhi:~/work/mysql-6.0-runtime/sql$ bzr diff
=== modified file 'sql/sql_base.cc'
--- sql/sql_base.cc	2008-06-28 11:00:59 +0000
+++ sql/sql_base.cc	2008-07-11 19:09:30 +0000
@@ -1465,6 +1465,7 @@ bool close_thread_table(THD *thd, TABLE 
     table->file->extra(HA_EXTRA_DETACH_CHILDREN);
 
     /* Free memory and reset for next loop */
+    free_blobs(table);
     table->file->ha_reset();
     table_def_unuse_table(table);
   }

This patch is against 6.0-runtime.

A better fix is not much more  complex -- instead of freeing all blobs, we should free only those that are bigger than, say, 64k.
[23 Jul 2008 14:12] Bugs System
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/50331

2675 Kristofer Pettersson	2008-07-23
      Bug#38002 table_cache consumes too much memory with blobs
      
      Tables in the table definition cache are keeping a cache buffer for blob
      fields which can consume a lot of memory.
      
      This patch introduces a maximum size threshold for these buffers.
[24 Jul 2008 20:35] Bugs System
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/50456

2675 Kristofer Pettersson	2008-07-24
      Bug#38002 table_cache consumes too much memory with blobs
           
      Tables in the table definition cache are keeping a cache buffer for blob
      fields which can consume a lot of memory.
          
      This patch introduces a maximum size threshold for these buffers.
[12 Aug 2008 14:58] Bugs System
Pushed into 6.0.7-alpha  (revid:kpettersson@mysql.com-20080724203844-94kheffzey6znex1) (version source revid:davi.arnaut@sun.com-20080812141852-8e6knbqclpfd8irn) (pib:3)
[12 Aug 2008 15:23] Bugs System
Pushed into 5.1.28  (revid:kpettersson@mysql.com-20080724203844-94kheffzey6znex1) (version source revid:davi.arnaut@sun.com-20080812142843-he05ncsggstbn57z) (pib:3)
[14 Aug 2008 3:51] Paul DuBois
Noted in 5.1.28, 6.0.7 changelogs.

If the table definition cache contained tables with many BLOB 
columns, much memory could be allocated to caching BLOB values. Now a
size limit on the cached BLOB values is enforced.
[28 Aug 2008 20:16] Bugs System
Pushed into 6.0.7-alpha  (revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (version source revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (pib:3)
[14 Sep 2008 5:43] Bugs System
Pushed into 6.0.7-alpha  (revid:kpettersson@mysql.com-20080724203844-94kheffzey6znex1) (version source revid:john.embretsen@sun.com-20080724122511-9c0oudz1xrdrs6y6) (pib:3)