| Bug #27863 | excessive memory usage for many small queries in a multiquery packet | ||
|---|---|---|---|
| Submitted: | 16 Apr 2007 21:57 | Modified: | 20 May 2010 15:56 |
| Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
| Version: | 5.0.56,5.1.23 | OS: | Any |
| Assigned to: | Alexander Nozdrin | CPU Architecture: | Any |
| Tags: | DoS, Memory, multiquery | ||
[16 Apr 2007 22:00]
MySQL Verification Team
testcase. watch memory usage during execution
Attachment: bug27863.php (application/octet-stream, text), 970 bytes.
[24 Feb 2008 9:01]
MySQL Verification Team
This extravagant memory usage still exists in recent versions of 5.1.23 and 5.0.56 5.1.23 needed over 9GB of memory to execute a 1MB packet filled with 'select 1;'!!
[12 Aug 2008 5:41]
Sveta Smirnova
Bug #38006 was marked as duplicate of this one.
[18 Feb 2009 7:59]
Valeriy Kravchuk
Bug #42946 was marked as a duplicate of this one. Looks like impact of this bug is higher than I3.
[12 Jun 2009 15:17]
Guilhem Bichot
Staale proposed that I take it, for 5.4
[29 Jun 2009 9:23]
Guilhem Bichot
Here is the analysis.
A 4MB "multi-query statement", made of 10,000 40-byte INSERT queries, is sent to mysqld.
So we call mysql_parse() 10,000 times (in a loop in dispatch_command()).
Each mysql_parse() does this:
if (query_cache_send_result_to_client(thd, (char*) inBuf, length) <= 0)
{
...
Parser_state parser_state(thd, inBuf, length);
And the constructor of Parser_state does:
Parser_state(THD *thd, const char* buff, unsigned int length)
: m_lip(thd, buff, length), m_yacc()
{}
and the one of Parser_state::m_lip (which is a Lex_input_stream) does:
m_cpp_buf= (char*) thd->alloc(length + 1);
For the first mysql_parse(), length==4MB, so this allocates 4MB in the THD's MEM_ROOT. For the second mysql_parse(), length == 4MB-40bytes, and this allocates 40MB - 40bytes. The third, 40MB - 80bytes. Etc, allocates 2GB in total. And the memory is freed only in a final:
free_root(thd->mem_root,MYF(MY_KEEP_PREALLOC));
at the end of dispatch_command().
[29 Jun 2009 11:42]
Guilhem Bichot
correction to my previous post: not 2GB but 200GB (!); however not all of it is really accessed I guess, as true consumption according to "top" is 1GB.
[8 Jul 2009 8:52]
Konstantin Osipov
In my opinion we need to distinguish the following problems: 1) Lex_input_stream allocates more memory on its construction than it is ever going to need 2) Both Lex_input_stream and alloc_query() allocate a copy of the query, whereas only one allocation is sufficient for needs of execution. 3) Memory allocated during execution of a multi-query is only freed at the end of execution, i.e. when all queries are executed. 3) has been the case all along, since the point multi-queries were added to the system. We could try to fix it, but that would be a clean-up and optimization, i.e. will extend beyond the scope of the bugfix. 1) and 2) are regressions introduced by Marc in scope of the patch for Bug#11986. How we use thd->query and lex->m_cpp_buf ======================================== thd->query is used for general log and slow log, and also for show processlist. It is assigned in alloc_query(). lex->m_cpp_buf is used to get the SQL of a stored procedure in utf-8 and without version-specific comments, to store in .proc table. So it's only used during CREATE PROCEDURE/FUNCTION/EVENT/TRIGGER. Methods to fix ============== To fix the problem 1), I think, it's sufficient to introduce Lex_input_stream::start_new_statement() method, which will reset all Lex_input_stream members without re-allocating the buffer. The buffer allocated in the constructor can be reused. This new method will be called inside the multi-statement loop, and Lex_input_stream construction will be moved outside the loop. To fix problem 2), I believe, the proper victim is alloc_query(). What we need to do, is to remove an extra allocation there, and start assigning thd->query to point to lex->m_cpp_buf. But it's a separate large fix. Problem 3) is of a scale of a worklog task. There are many interdependencies that need to be solved to be able to fix it correctly.
[8 Jul 2009 9:17]
Guilhem Bichot
I'll implement 1), with details from Konstantin: <kostja> select 1; select 1; select 1; <kostja> the length of this thd->query is 27 <kostja> you allocate m_cpp_buf to 27 <kostja> you parse the first query. <kostja> there are pointers to the first 9 bytes of the cpp buff <kostja> now you call start_new_statement() <kostja> you make sure in it, that m_cpp_buf= m_cpp_buf_end <kostja> so instead of reallocating m_cpp_buff, you just advance it, knowing it is already sufficienty pre-allocated <kostja> so nobody interferes with anybody else <kostja> that is not strictly necessary, but will solve your concern that during re-use of Lex_input_stream there is some overlap
[25 Sep 2009 16:59]
Guilhem Bichot
not working on it at the moment (rather on Optimizer bugs)
[7 Apr 2010 16:27]
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/105171 3144 Alexander Nozdrin 2010-04-07 A patch for Bug#27863 (excessive memory usage for many small queries in a multiquery packet). This patch fixes issue #1 described in the bug report: 1) Lex_input_stream allocates more memory on its construction than it is ever going to need The fix is to allocate a buffer (cpp_buffer) once for a multiquery packet.
[28 Apr 2010 16:47]
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/106859 3144 Alexander Nozdrin 2010-04-28 A patch for Bug#27863 (excessive memory usage for many small queries in a multiquery packet). This patch fixes issue #1 described in the bug report: 1) Lex_input_stream allocates more memory on its construction than it is ever going to need The fix is to allocate a buffer (cpp_buffer) once for a multiquery packet.
[14 May 2010 18: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/108374 3044 Alexander Nozdrin 2010-05-14 Patch for Bug#27863 (excessive memory usage for many small queries in a multiquery packet). Background: - a query can contain multiple SQL statements; - the server frees resources allocated to process a query when the whole query is handled. In other words, resources allocated to process one SQL statement from a multi-statement query are freed when all SQL statements are handled. The problem was that the parser allocated a buffer of size of the whole query for each SQL statement in a multi-statement query. Thus, if a query had many SQL-statements (so, the query was long), but each SQL statement was short, ther parser tried to allocate huge amount of memory (number of small SQL statements * length of the whole query). The memory was allocated for a so-called "cpp buffer", which is intended to store pre-processed SQL statement -- SQL text without version specific comments. The fix is to allocate memory for the "cpp buffer" once for all SQL statements (once for a query).
[14 May 2010 19:21]
Alexander Nozdrin
Pushed into trunk-bugfixing.
[17 May 2010 12:11]
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/108443 3045 Alexander Nozdrin 2010-05-17 Follow-up patch for Bug#27863 (excessive memory usage for many small queries in a multiquery packet): fix NDB test failures.
[20 May 2010 10:03]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100520100225-oe4iuu5kuzsx0knq) (version source revid:alik@sun.com-20100520100057-rmn5y3o3ij726bm7) (merge vers: 6.0.14-alpha) (pib:16)
[20 May 2010 10:04]
Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100520100041-rf8ujv598zf86wjt) (version source revid:alik@sun.com-20100520100041-rf8ujv598zf86wjt) (merge vers: 5.5.5-m3) (pib:16)
[20 May 2010 10:05]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100520100140-5bzrtadw4w419i3m) (version source revid:alik@sun.com-20100520100049-1njm09rkvnhmysnr) (pib:16)
[20 May 2010 15:56]
Paul DuBois
Noted in 5.5.5, 6.0.14 changelogs. The parser allocated too much memory for a query containing multiple statements.
[20 Jul 2010 18:02]
Sveta Smirnova
Bug #55376 was marked as duplicate of this one.
[10 Aug 2011 10:48]
Sveta Smirnova
Bug #62117 was marked as duplicate of this one.
Description: A simple multi-query query sent to the server such as this, $sql= str_repeat("select '1';",95324); causes mysqld to use between 300MB and 500MB of memory. 4.1 used less, while 5.1 used more as expected. The problem is the above query can be run by many users simultaneously, and the overall effect can be devastating. The query fits into 1MB default max_allowed_packet. How to repeat: See uploaded php script. Suggested fix: Not sure. Feature request is to reduce the amount of memory used at any given time.