Bug #70329 excessive memory usage when querying INFORMATION_SCHEMA.INNODB_FT_INDEX
Submitted: 13 Sep 2013 11:35 Modified: 15 Oct 2013 18:09
Reporter: Sebastian Strzelczyk Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6.13 OS:Linux (2.6.32.12-0.7-default x86_64 GNU/Linux )
Assigned to: CPU Architecture:Any

[13 Sep 2013 11:35] Sebastian Strzelczyk
Description:

2013-09-11 14:05:55 4783 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.13-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2013-09-11 14:08:20 4783 [ERROR] InnoDB:  InnoDB: Unable to allocate memory of size 3192.

2013-09-11 14:08:20 7f8f7883a710  InnoDB: Assertion failure in thread 140254178944784 in file ha_innodb.cc line 16865
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
12:08:20 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=838860800
read_buffer_size=8388608
max_used_connections=3
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 5769217 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f8dc0005dc0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f8f78839e58 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x900575]
/usr/sbin/mysqld(handle_fatal_signal+0x401)[0x67b411]
/lib64/libpthread.so.0(+0xf5d0)[0x7f8f785225d0]
/lib64/libc.so.6(gsignal+0x35)[0x7f8f77225945]
/lib64/libc.so.6(abort+0x181)[0x7f8f77226f21]
/usr/sbin/mysqld[0x9a8e03]
/usr/sbin/mysqld[0x9f0bf7]
/usr/sbin/mysqld[0x9f0cc6]
/usr/sbin/mysqld[0xb1cf45]
/usr/sbin/mysqld[0xa8d193]
/usr/sbin/mysqld[0xb1d5ea]
/usr/sbin/mysqld[0xb1dc9e]
/usr/sbin/mysqld[0xa4d42b]
/usr/sbin/mysqld[0xa17f78]
/usr/sbin/mysqld[0xb293dd]
/usr/sbin/mysqld[0x9cba48]
/usr/sbin/mysqld(_Z24get_schema_tables_resultP4JOIN23enum_schema_table_state+0x2e1)[0x727611]
/usr/sbin/mysqld(_ZN4JOIN14prepare_resultEPP4ListI4ItemE+0x9d)[0x71b73d]
/usr/sbin/mysqld(_ZN4JOIN4execEv+0x13f)[0x6d8aaf]
/usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_P10SQL_I_ListI8st_orderESB_S7_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x198)[0x7217e8]
/usr/sbin/mysqld(_Z13handle_selectP3THDP13select_resultm+0x19f)[0x7219ef]
/usr/sbin/mysqld[0x6f982d]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x19d1)[0x6fc191]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x3a5)[0x6ff8c5]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x10b3)[0x700ef3]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x11f)[0x6cc44f]
/usr/sbin/mysqld(handle_one_connection+0x45)[0x6cc525]
/usr/sbin/mysqld(pfs_spawn_thread+0x126)[0x997c86]
/lib64/libpthread.so.0(+0x75f0)[0x7f8f7851a5f0]
/lib64/libc.so.6(clone+0x6d)[0x7f8f772c784d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (98518c0): select count(*) from INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE
Connection ID (thread ID): 20
Status: NOT_KILLED

How to repeat:
We have a very big table with a full text index, the table has 1.360.000 records and 1.3 GB data.

CREATE TABLE IF NOT EXISTS `searchindex` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `key` int(10) unsigned NOT NULL,
  `datatype_id` tinyint(3) unsigned NOT NULL,
  `searchcontent` longtext,
  `created` datetime NOT NULL,
  `created_by` varchar(50) NOT NULL,
  `modified` datetime NOT NULL,
  `modified_by` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_searchindex` (`key`,`datatype_id`),
  FULLTEXT KEY `fullindx_searchidenx` (`searchcontent`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

the server don't crash, when using smaller amount of records < 100.000 ... 

Server also crashes at the same point with queries like:

select * from INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE limit 10;
or
select * from INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE into DUMPFILE '/tmp/searchindex_inno_dump.txt'
[13 Sep 2013 12:20] Shane Bester
how much memory does mysqld consume before you access that table?
how much does is consume at the moment of crash?
how much memory do you have in total?
[16 Sep 2013 10:22] Sebastian Strzelczyk
Server has 

Mem:      9893M total
Swap:     4094M total

MySQL using 6GB in warm state ... I provide the globla variables, which may help you to see the whole configuration. If you need more, let me know.
[19 Sep 2013 18:39] Shane Bester
The question is, is the high memory usage acceptable? Is this something you should use with large data?  "This variable is intended for diagnostic purposes"

http://dev.mysql.com/doc/refman/5.6/en/innodb-ft-index-table-table.html
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_ft_aux_table

---
drop table if exists t1;
create table if not exists `t1` (`c` varchar(10),fulltext key (`c`)) engine=innodb  default charset=latin1;
replace into `t1`(`c`) values ('hello1'),('what what'),('ejgdut'),('nmnjvu');
replace into `t1`(`c`) select concat(rand(),'') from 
`t1` `a01`,`t1` `a02`,`t1` `a03`,`t1` `a04`,`t1` `a05`,`t1` `a06`,
`t1` `a07`,`t1` `a08`,`t1` `a09`,`t1` `a10`,`t1` `a11` limit 2000000;
select count(*) from `t1`;
set global innodb_ft_aux_table='test/t1';
##caution: select count(*) from information_schema.innodb_ft_index_table;
----
[19 Sep 2013 19:00] Shane Bester
1. too high memory usage
2. memory is not freed.

672,951,215 (29,588,496 direct, 643,362,719 indirect) bytes in 176,122 blocks are definitely lost in loss record 18 of 18
at: malloc (vg_replace_malloc.c:291)
by: mem_area_alloc (mem0pool.cc:381)
by: mem_heap_create_block (mem0mem.cc:334)
by: mem_heap_create_func (mem0mem.ic:434)
by: fts_word_init (fts0opt.cc:346)
by: fts_optimize_index_fetch_node (fts0opt.cc:453)
by: fetch_step (row0sel.cc:2165)
by: que_thr_step (que0que.cc:1089)
by: que_run_threads_low (que0que.cc:1167)
by: que_run_threads (que0que.cc:1208)
by: fts_eval_sql (fts0sql.cc:278)
by: i_s_fts_index_table_fill_selected (i_s.cc:3531)
by: i_s_fts_index_table_fill_one_index (i_s.cc:3597)
by: i_s_fts_index_table_fill (i_s.cc:3703)
by: do_fill_table (sql_show.cc:7184)
by: get_schema_tables_result (sql_show.cc:7285)
by: JOIN::prepare_result (sql_select.cc:823)
by: JOIN::exec (sql_executor.cc:116)
by: mysql_execute_select (sql_select.cc:1100)
by: mysql_select (sql_select.cc:1221)
by: handle_select (sql_select.cc:110)
by: execute_sqlcom_select (sql_parse.cc:5094)
by: mysql_execute_command (sql_parse.cc:2642)
by: mysql_parse (sql_parse.cc:6235)
by: dispatch_command (sql_parse.cc:1334)
by: do_handle_one_connection (sql_connect.cc:982)
by: handle_one_connection (sql_connect.cc:898)
[20 Sep 2013 14:11] Sebastian Strzelczyk
of course for diagnostic propuse it is helpful, when I know what I'm looking for ... but also a row limit in the query will cause that problem ... something like myisam_ftdump could also be helpfull, for dumping out and work with it ...
[20 Sep 2013 14:11] Sebastian Strzelczyk
but it should not crash the server ... stop with are warning would also be okay for me.
[15 Oct 2013 18:09] Bugs System
Noted in 5.6.15, 5.7.3 changelogs:

An excessive amount of memory would be consumed when querying
"INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE". The problem would occur for
very large full-text search indexes.

Thank you for the bug report.
[4 Dec 2013 11:57] Laurynas Biveinis
5.6$ bzr log -r 5515
------------------------------------------------------------
revno: 5515
committer: Shaohua Wang <shaohua.wang@oracle.com>
branch nick: mysql-5.6-bugfix1
timestamp: Sat 2013-10-12 11:09:38 +0800
message:
  BUG#17483582 - EXCESSIVE MEMORY USAGE WHEN QUERYING INFORMATION_SCHEMA.INNODB_FT_INDEX
  
  Analysis & Solution:
  We first fetch all fts index records into memory and then store them into myisam temp table,
  so it will consumes huge memory when the fts index is big enough.
  
  In this patch, we limit the memory usage by INNODB_FT_RESULT_CACHE_LIMIT.
  When we reach the limit, do more fetches until we have all records
  
  rb://3398 approved by Jimmy.Yang
[4 Dec 2013 11:58] Laurynas Biveinis
5.6$ bzr log -r 5517
------------------------------------------------------------
revno: 5517
committer: Shaohua Wang <shaohua.wang@oracle.com>
branch nick: mysql-5.6-bugfix1
timestamp: Mon 2013-10-14 18:12:00 +0800
message:
  Follow up fix for bug #17483582 - EXCESSIVE MEMORY USAGE WHEN QUERYING INFORMATION_SCHEMA.INNODB_FT_INDEX."
  initialize the total_memory in fts index optimization code path.
  
  Approved by Jimmy.Yang in IM.