MySQL Bugs: #32836: InnoDB dictionary memory too big(Not fixed #20877?)

Bug #32836	InnoDB dictionary memory too big(Not fixed #20877?)
Submitted:	29 Nov 2007 5:03	Modified:	16 Dec 2007 17:51
Reporter:	kenichi yonekawa	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.0.46,5.1.22-rc	OS:	Linux
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	cache, CREATE TABLE, innodb

Description:
This bug seems not to be fixed by 5.1.22-rc.
I tried debugging while watching the dict_sys->size.
dict_sys->size seemed not to be improved. 

My environments:
C compiler:    gcc (GCC) 3.4.3 20050227 (Asianux 2.0 3.4.3-22.1.1)
C++ compiler:  gcc (GCC) 3.4.3 20050227 (Asianux 2.0 3.4.3-22.1.1)
Architecture: i686

5.0.46 compile options:
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-java-awt=gtk --host=i386-asianux-linux
Thread model: posix
gcc version 3.4.3 20050227 (Asianux 2.0 3.4.3-22.1.1)
Compilation info: CC='gcc'  CFLAGS='-g -O0 -fno-omit-frame-pointer'  CXX='gcc'  CXXFLAGS='-felide-constructors -fno-exceptions -fno-rtti -g -O0 -fno-omit-frame-pointer'  LDFLAGS=''  ASFLAGS=''
Configure command: ./configure '--prefix=/home/yonekawa/workspace/project/mysql5.0' '--localstatedir=/home/yonekawa/workspace/project/mysql5.0/data' '--libexecdir=/home/yonekawa/workspace/project/mysql5.0/bin' '--with-comment=MySQL mysql no cache non tritonn (Commercial) - Cybozu' '--with-server-suffix=-enterprise-cybozu-nocache' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--with-pic' '--with-fast-mutexes' '--with-client-ldflags=-static' '--with-mysqld-ldflags=-static' '--with-zlib-dir=bundled' '--with-big-tables' '--with-yassl' '--with-libedit' '--with-federated-storage-engine' '--with-innodb' '--with-extra-charsets=complex' '--with-charset=utf8' '--with-collation=utf8_general_cs' '--with-tcp-port=3770' 'CC=gcc' 'CFLAGS=-g -O0 -fno-omit-frame-pointer' 'CXX=gcc' 'CXXFLAGS=-felide-constructors -fno-exceptions -fno-rtti -g -O0 -fno-omit-frame-pointer' 'CXXLDFLAGS='

5.1.22-rc compile options:
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-java-awt=gtk --host=i386-asianux-linux
Thread model: posix
gcc version 3.4.3 20050227 (Asianux 2.0 3.4.3-22.1.1)
Compilation info: CC='gcc'  CFLAGS='-g -O0 -fno-omit-frame-pointer'  CXX='gcc'  CXXFLAGS='-felide-constructors -fno-exceptions -fno-rtti -g -O0 -fno-omit-frame-pointer'  LDFLAGS=''  ASFLAGS=''
Configure command: ./configure '--prefix=/home/yonekawa/workspace/project/mysql5.1' '--localstatedir=/home/yonekawa/workspace/project/mysql5.1/data' '--libexecdir=/home/yonekawa/workspace/project/mysql5.1/bin' '--with-comment=MySQL mysql no cache non tritonn (Commercial) - Cybozu' '--with-server-suffix=-enterprise-cybozu-nocache' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--with-pic' '--with-fast-mutexes' '--with-client-ldflags=-static' '--with-mysqld-ldflags=-static' '--with-zlib-dir=bundled' '--with-big-tables' '--with-libedit' '--with-federated-storage-engine' '--with-innodb' '--with-extra-charsets=complex' '--with-charset=utf8' '--with-collation=utf8_general_cs' '--with-tcp-port=3770' 'CC=gcc' 'CFLAGS=-g -O0 -fno-omit-frame-pointer' 'CXX=gcc' 'CXXFLAGS=-felide-constructors -fno-exceptions -fno-rtti -g -O0 -fno-omit-frame-pointer' 'CXXLDFLAGS='

How to repeat:
How to repeat:
1. Starting mysqld from GDB
2. Execute create table query.
3. Watching dict_sys->size value.

MySQL5.0(5.0.46)
 before)
 p dict_sys->size => 19160
 
 after table create)
 p dict_sys->size => 45960

 45960 - 19160 = 26800(heap size of tab_mail_message)

MySQL5.1(5.1.22-rc)
 before)
 p dict_sys->size => 20888

 after table create)
 p dict_sys->size => 82264

 82264 - 20888 = 61376(heap size of tab_mail_message)

The query I tried =>
CREATE TABLE  `test`.`tab_mail_message` (
  `_id` bigint(20) NOT NULL auto_increment,
  `col_abstract_data` char(128) collate utf8_general_ci default NULL,
  `col_abstract_from` char(128) collate utf8_general_ci default NULL,
  `col_abstract_from_email` char(128) collate utf8_general_ci default NULL,
  `col_abstract_from_name` char(128) collate utf8_general_ci default NULL,
  `col_abstract_subject` char(128) collate utf8_general_ci default NULL,
  `col_abstract_to` char(128) collate utf8_general_ci default NULL,
  `col_abstract_to_email` char(128) collate utf8_general_ci default NULL,
  `col_abstract_to_name` char(128) collate utf8_general_ci default NULL,
  `col_action` char(32) collate utf8_general_ci default NULL,
  `col_attached` int(11) default NULL,
  `col_bcc` text collate utf8_general_ci,
  `col_cc` text collate utf8_general_ci,
  `col_confirmation_final_recipient` text collate utf8_general_ci,
  `col_confirmation_org_message_id` char(255) collate utf8_general_ci default NULL,
  `col_confirmation_request` int(11) default NULL,
  `col_confirmation_response` int(11) default NULL,
  `col_confirmation_status` char(32) collate utf8_general_ci default NULL,
  `col_confirmation_to` text collate utf8_general_ci,
  `col_content_type` char(255) collate utf8_general_ci default NULL,
  `col_creator` bigint(20) default NULL,
  `col_creator_foreign_key` char(255) collate utf8_general_ci default NULL,
  `col_creator_name` char(100) collate utf8_general_ci default NULL,
  `col_ctime` int(11) default NULL,
  `col_data` longtext collate utf8_general_ci,
  `col_date` text collate utf8_general_ci,
  `col_draft` int(11) default NULL,
  `col_dtime` int(11) default NULL,
  `col_folder` bigint(20) default NULL,
  `col_from` text collate utf8_general_ci,
  `col_html_data` longtext collate utf8_general_ci,
  `col_in_reply_to` text collate utf8_general_ci,
  `col_message_id` char(255) collate utf8_general_ci default NULL,
  `col_modifier` bigint(20) default NULL,
  `col_modifier_foreign_key` char(255) collate utf8_general_ci default NULL,
  `col_modifier_name` char(100) collate utf8_general_ci default NULL,
  `col_mtime` int(11) default NULL,
  `col_origin_mail` bigint(20) default NULL,
  `col_read_ts` int(11) NOT NULL,
  `col_references` text collate utf8_general_ci,
  `col_reply_to` text collate utf8_general_ci,
  `col_reserve_blob1` blob,
  `col_reserve_blob2` blob,
  `col_reserve_blob3` blob,
  `col_reserve_int1` int(11) default NULL,
  `col_reserve_int2` int(11) default NULL,
  `col_reserve_int3` int(11) default NULL,
  `col_reserve_text1` char(100) collate utf8_general_ci default NULL,
  `col_reserve_text2` char(100) collate utf8_general_ci default NULL,
  `col_reserve_text3` char(100) collate utf8_general_ci default NULL,
  `col_send_ts` int(11) NOT NULL,
  `col_sent` int(11) NOT NULL,
  `col_sign_data` text collate utf8_general_ci,
  `col_signature` bigint(20) default NULL,
  `col_size` int(11) default NULL,
  `col_status` char(64) collate utf8_general_ci NOT NULL,
  `col_subject` text collate utf8_general_ci,
  `col_timestamp` int(11) NOT NULL,
  `col_to` text collate utf8_general_cs,
  `col_unsent` int(11) default NULL,
  `col_user` bigint(20) default NULL,
  PRIMARY KEY  (`_id`),
  KEY `cni_folder` (`col_folder`),
  KEY `cni_origin_mail` (`col_origin_mail`),
  KEY `cni_signature` (`col_signature`),
  KEY `idx_folder_data_n` (`col_folder`,`col_abstract_data`,`_id`),
  KEY `idx_folder_dts_n` (`col_folder`,`col_dtime`,`_id`),
  KEY `idx_folder_from_email_n` (`col_folder`,`col_abstract_from_email`,`_id`),
  KEY `idx_folder_from_n` (`col_folder`,`col_abstract_from`,`_id`),
  KEY `idx_folder_from_name_n` (`col_folder`,`col_abstract_from_name`,`_id`),
  KEY `idx_folder_rts_n` (`col_folder`,`col_read_ts`,`_id`),
  KEY `idx_folder_status` (`col_folder`,`col_status`),
  KEY `idx_folder_sts_n` (`col_folder`,`col_send_ts`,`_id`),
  KEY `idx_folder_subject_n` (`col_folder`,`col_abstract_subject`,`_id`),
  KEY `idx_folder_to_email_n` (`col_folder`,`col_abstract_to_email`,`_id`),
  KEY `idx_folder_to_n` (`col_folder`,`col_abstract_to`,`_id`),
  KEY `idx_folder_to_name_n` (`col_folder`,`col_abstract_to_name`,`_id`),
  KEY `idx_folder_ts_n` (`col_folder`,`col_timestamp`,`_id`),
  KEY `idx_user_messageid` (`col_user`,`col_message_id`),
  KEY `idx_user_status_ts` (`col_user`,`col_status`,`col_timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Sorry, the bug is here.
http://bugs.mysql.com/bug.php?id=20877

Thank you for a bug report. Bug #20877 is Open now, so would you mind if I'll mark this report as a duplicate. We will have a note about your new findings in the original bug report instead.

Valeriy,

OK, This report is duplicate.
I didn't notice it bug was reopened.
Sorry,thanks.

Inaam verified that 5.1.23 uses 80 % more than 5.1.11.

Kenichi, Inaam,

please check with 100,000 tables.

The memory consumption for a single table can be somewhat random.

Regards,

Heikki

Heikki.

I tried to create the table to see whether the size is random.
As a result, if it was the same table, the dict_sys->size was same. 

As far as I know, the memory consumption depends on the number of indexes of 
the table and the number of columns.

So, I didn't check with 100,000 tables.

Is the answer good in this?
Regards.

I have tried 5.1.11 and 5.1.23 with 10,000 tables. The table definition was same as posted in this report.
There is an increase in dict_sys->size (i.e.: Dictionary Memory Allocated in show innodb status) but the increase is not to the tune of 80% (it is more like 10 to 15%). 
I am changing severity of this bug to S4(Feature Request). I'll start investigating the cause of this increase in coming days.

Number of Tables = 10000

From 5.1.11
===========
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1168809562; in additional pool allocated 446827648
Dictionary memory allocated 260817496
Buffer pool size   6400
Free buffers       0
Database pages     6399
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 59, created 200056, written 200759
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout

From 5.1.23
===========
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1168127400; in additional pool allocated 377640448
Dictionary memory allocated 296341752
Buffer pool size   6400
Free buffers       0
Database pages     6393
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 203244, created 0, written 39
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout

Another thing to notice is that though the dict_sys->size increased from nearly 260MB to 296MB the reserved memory from the common pool actually went down from 446MB to 377MB from 5.1.11 to 5.1.23.

Thus, in this case, the overall memory footprint from 5.1.11 to 5.1.23 has decreased considerably .

This is a duplicate of http://bugs.mysql.com/bug.php?id=20877