| Bug #71476 | InnoDB memory consumption with "bad" query | ||
|---|---|---|---|
| Submitted: | 25 Jan 2014 11:22 | Modified: | 26 Feb 2014 13:35 |
| Reporter: | Florian Gusinde | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: Errors | Severity: | S3 (Non-critical) |
| Version: | 5.6 | OS: | Linux (debian squeeze) |
| Assigned to: | CPU Architecture: | Any | |
[25 Jan 2014 13:22]
Florian Gusinde
I deleted *many* rows and converted to MyISAM. From > 5 mio. I deleted all entries older than 2014; now there are only 172k rows left. (all on my local VM). Same thing happens again. format.ibd has 653 MB (2.2 mio rows) content.ibd has 108 MB. (172k rows) VM has 2 GB of RAM; innodb_buffer_pool_size is 256 MB. On idle time mysqld is show with 359 MB RES, 909 MB VIRT, 9.8 MB SHR. After that I delete nearly all rows from format (I left 14 rows). format.ibd has 112 kb (14 rows) content.ibd has 108 MB. (172k rows) I recreated both tables with alter table x = InnoDB Statement is running > 2 sec. I ran my tests (created from slow-query-log) again, and, you know it, it crashed again. linux "top" tool showed me all memory was consumed from MySQL.
[26 Jan 2014 13:35]
Sveta Smirnova
Thank you for the report. Please specify exact minor version of MYSQL 5.6 server you use, provide output of SHOW CREATE TABLE, SHOW TABLE STATUS for all tables, used in the query, specify how many rows this query actually returns and provide output of following test: FLUSH STATUS; Run problematic query SHOW STATUS LIKE 'Handler_%';
[27 Feb 2014 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: Hi, server specs: HP G7, 32 GB RAM, Intel cpu, 8 discs in raid10 with 256 MB cache Debian Squeeze, MySQL 5.6/MariaDB 10 There's a table content (MyISAM) with 248 columns and another one "format" with 29 columns (yeah, that's sick but I'm not the developer, but the dba). I'm sure it's not table- or system-related. When "bad" queries are called behaviour on MyISAM is ok but locking slows down application when it's used parallel. Memory is fine and used as configured (key_buffer, etc.). Table size is < 1,5 GB (keys+data), about 5 mio. rows, some fulltext keys. I converted to InnoDB, everything seems fine. I gave 2 GB of innodb_buffer_pool (remember: I have 32 GB of RAM) and *_buffer_size with 2 MB. If I call the "bad" queries parallel (5 parallel requests) mysqld is running into swap; if I disable swap mysqld is going to die and will be restarted by mysqld_safe. application (or mysql-console-client) means "server has gone away", select uptime() confirms. tmpdir is a 2 GB tmpfs. I suspect InnoDB is consuming much more memory than configured; I suspect a bug in limiting memory consumption of InnoDB. I suspect there's no way to tell mysqld/InnoDB a hard memory limit. I think that's an issue with fulltext search. I can reproduce on local VM with debian wheezy and MariaDB 10. It's not clear for me why mysqld will allocate so much of memory because table size is that small. There's nothing else running on this system. A sample for the "bad" statement is: SELECT COUNT(*), GROUP_CONCAT(f.format_id) formats, GROUP_CONCAT(f.url) urls, f.content_id, c.media_kind FROM format f, content c WHERE f.content_id=c.content_id AND (f.status&(7<<(3*8)))>>(3*8)="!2" AND (f.status&(7<<(3*8)))>>(3*8)="!2" AND (MATCH(c.md_mandant) AGAINST("532153" IN BOOLEAN MODE) OR (c.md_mandant="" AND c.media_kind in ("Trailer","ABR"))) AND c.md_hd!="Y" AND c.media_kind in("Hauptfilm") AND f.format_name_id in (18) GROUP BY f.content_id; Explain looks "good": +------+-------------+-------+----------+----------------------+------------+---------+-------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+----------+----------------------+------------+---------+-------------------------+------+----------------------------------------------+ | 1 | SIMPLE | c | fulltext | PRIMARY,md_mandant | md_mandant | 0 | | 1 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | f | ref | content_id,FLOTMP001 | content_id | 4 | db_maxdome.c.content_id | 7 | Using where | +------+-------------+-------+----------+----------------------+------------+---------+-------------------------+------+----------------------------------------------+ 2 rows in set (0.00 sec) But this statements takes > 20 sec (both MyISAM and InnoDB). How to repeat: MyISAM-table with ~250 columns, 10 fulltext indexes, bad sql query. convert to InnoDB, start bad sql query again. Suggested fix: Hard memory limit for mysqld, garbage collection?