| 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: | |
| 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
[13 Sep 2013 12:20]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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.
