[MDEV-2682] LP:954262 - Query casues excessive memory consumption Created: 2012-03-13  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Critical
Reporter: Peter (Stig) Edwards (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug954262.xml     File LPexportBug954262_20120313_mariadb_5_3_5_out_of_mem.sql    

 Description   

Thank you for MariaDB 5.3.5-ga

The attached file contains a reproducer.
Running against 5.3.5-ga and a pull from bzr against 5.3 yesterday I am able to get mysqld to consume a great deal of memory by running the attached file thus:
mysql -D test < 20120313_mariadb_5_3_5_out_of_mem.sql

When memory is exhausted the error reported to the client is:

  1. ERROR 5 (HY000): Out of memory (Needed [digit] bytes)

and the server error log has:

[ERROR] mysqld: Out of memory (Needed [digit] bytes)
[ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space

(I am aware the query should avoid using single quotes around ints.)

Running bzr pull on:
Linux 2.6.32-131.6.1.el6.x86_64 #1 SMP x86_64 x86_64 x86_64 GNU/Linux

Using mariadb-5.3.5-ga-Linux-x86_64.tar.gz on:
Linux l2.6.32-220.el6.x86_64 #1 SMP x86_64 x86_64 x86_64 GNU/Linux

Cheers

 Comments   
Comment by Peter (Stig) Edwards (Inactive) [ 2012-03-13 ]

Re: Query casues excessive memory consumption

Comment by Peter (Stig) Edwards (Inactive) [ 2012-03-13 ]

20120313_mariadb_5_3_5_out_of_mem.sql
LPexportBug954262_20120313_mariadb_5_3_5_out_of_mem.sql

Comment by Elena Stepanova [ 2012-03-14 ]

Re: Query casues excessive memory consumption
Reproducible on 5.3.5 and 5.5.20, also on the current 5.3 tree revno 3457.
Reproducible even with all values OFF in optimizer_switch.
EXPLAIN causes the same effect.

Stack trace from the active thread if I kill the server in the process:

#0 0x087f6dba in db_doprnt (format=0x89e272c "root: 0x%lx") at dbug.c:1359
#1 0x087c2d61 in alloc_root (mem_root=0x9d4f7a00, length=308) at my_alloc.c:185
#2 0x08268c81 in sql_alloc (Size=308) at thr_malloc.cc:72
#3 0x081e86cf in Sql_alloc::operator new (size=308) at sql_list.h:32
#4 0x0840220a in SEL_IMERGE::SEL_IMERGE (this=0x6f191f80, arg=0x7721e1d0, cnt=0, param=0x9d4f5608)
at opt_range.cc:1372
#5 0x084020b2 in SEL_TREE::SEL_TREE (this=0x6f191e48, arg=0x7721e098, without_merges=false, param=0x9d4f5608)
at opt_range.cc:1327
#6 0x08402234 in SEL_IMERGE::SEL_IMERGE (this=0x6f191e10, arg=0x7721e060, cnt=0, param=0x9d4f5608)
at opt_range.cc:1372
#7 0x084020b2 in SEL_TREE::SEL_TREE (this=0x6f190e30, arg=0x7721d080, without_merges=false, param=0x9d4f5608)
at opt_range.cc:1327
#8 0x08402234 in SEL_IMERGE::SEL_IMERGE (this=0x6f190df8, arg=0x7721d048, cnt=0, param=0x9d4f5608)
at opt_range.cc:1372
#9 0x084020b2 in SEL_TREE::SEL_TREE (this=0x6f18ce38, arg=0x77219088, without_merges=false, param=0x9d4f5608)
at opt_range.cc:1327
#10 0x08402234 in SEL_IMERGE::SEL_IMERGE (this=0x6f18ce00, arg=0x77219050, cnt=0, param=0x9d4f5608)
at opt_range.cc:1372
#11 0x084020b2 in SEL_TREE::SEL_TREE (this=0x6f18ccc8, arg=0x77218f18, without_merges=false, param=0x9d4f5608)
at opt_range.cc:1327
#12 0x08402234 in SEL_IMERGE::SEL_IMERGE (this=0x6f18cc90, arg=0x77218ee0, cnt=0, param=0x9d4f5608)
at opt_range.cc:1372
#13 0x084020b2 in SEL_TREE::SEL_TREE (this=0x6f16cf48, arg=0x771f9198, without_merges=false, param=0x9d4f5608)
at opt_range.cc:1327
#14 0x08402234 in SEL_IMERGE::SEL_IMERGE (this=0x6f16cf10, arg=0x771f9160, cnt=0, param=0x9d4f5608)
at opt_range.cc:1372
#15 0x084020b2 in SEL_TREE::SEL_TREE (this=0x6f16cdd8, arg=0x771f9028, without_merges=false, param=0x9d4f5608)
at opt_range.cc:1327
#16 0x08402234 in SEL_IMERGE::SEL_IMERGE (this=0x6f16cda0, arg=0x771f8ff0, cnt=0, param=0x9d4f5608)
at opt_range.cc:1372
#17 0x084020b2 in SEL_TREE::SEL_TREE (this=0x6f3dbf90, arg=0x773df110, without_merges=false, param=0x9d4f5608)
at opt_range.cc:1327
#18 0x08402234 in SEL_IMERGE::SEL_IMERGE (this=0x6f3dbf58, arg=0x773df0d8, cnt=0, param=0x9d4f5608)
at opt_range.cc:1372
#19 0x084020b2 in SEL_TREE::SEL_TREE (this=0x6f7b3b88, arg=0x776a4bb0, without_merges=false, param=0x9d4f5608)
at opt_range.cc:1327
#20 0x08402234 in SEL_IMERGE::SEL_IMERGE (this=0x6f7b3b50, arg=0x776a4b78, cnt=0, param=0x9d4f5608)
at opt_range.cc:1372
#21 0x084020b2 in SEL_TREE::SEL_TREE (this=0x706c3408, arg=0x78807360, without_merges=false, param=0x9d4f5608)
at opt_range.cc:1327
#22 0x08402234 in SEL_IMERGE::SEL_IMERGE (this=0x706c33d0, arg=0x78807328, cnt=0, param=0x9d4f5608)
at opt_range.cc:1372
#23 0x084020b2 in SEL_TREE::SEL_TREE (this=0x767f85a8, arg=0x7e7176c8, without_merges=false, param=0x9d4f5608)
at opt_range.cc:1327
#24 0x08402234 in SEL_IMERGE::SEL_IMERGE (this=0x767f8570, arg=0x7e717690, cnt=2, param=0x9d4f5608)
at opt_range.cc:1372
#25 0x084024fb in imerge_list_or_tree (param=0x9d4f5608, merges=0x9d4f53f0, tree=0x7e717558)
at opt_range.cc:1565
#26 0x084105af in tree_or (param=0x9d4f5608, tree1=0x827a80e8, tree2=0x7e717258) at opt_range.cc:8423
#27 0x0840d930 in get_mm_tree (param=0x9d4f5608, cond=0x94ca970) at opt_range.cc:7208
#28 0x084059fe in SQL_SELECT::test_quick_select (this=0x94ea0a0, thd=0x949e66d8, keys_to_use=...,
prev_tables=0, limit=18446744073709551615, force_quick_range=false, ordered_output=false)
at opt_range.cc:3052
#29 0x083595c4 in get_quick_record_count (thd=0x949e66d8, select=0x94ea0a0, table=0x94dbc70, keys=0x94e8668,
limit=18446744073709551615) at sql_select.cc:3001
#30 0x0835b1f6 in make_join_statistics (join=0x94dfcc8, tables_list=..., conds=0x94ca970,
keyuse_array=0x94e6170) at sql_select.cc:3532
#31 0x08353469 in JOIN::optimize (this=0x94dfcc8) at sql_select.cc:1150
#32 0x083592e4 in mysql_select (thd=0x949e66d8, rref_pointer_array=0x949e8274, tables=0x94c8390, wild_num=0,
fields=..., conds=0x94ca970, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
select_options=2147764736, result=0x94d6508, unit=0x949e7e58, select_lex=0x949e8138) at sql_select.cc:2938
#33 0x08350fe7 in handle_select (thd=0x949e66d8, lex=0x949e7dfc, result=0x94d6508, setup_tables_done_option=0)
at sql_select.cc:285
#34 0x082ec085 in execute_sqlcom_select (thd=0x949e66d8, all_tables=0x94c8390) at sql_parse.cc:5151
#35 0x082e3461 in mysql_execute_command (thd=0x949e66d8) at sql_parse.cc:2284
#36 0x082ee6c0 in mysql_parse (thd=0x949e66d8,
rawbuf=0x94c6928 "SELECT \n t1.k9_corp_id,\n t1.barks,\n t1.birthday\nFROM\n t1\nWHERE (\n (\n t1.k9_corp_id = '21750' \n AND t1.barks = '1' \n AND t1.birthday = '2007-03-16'\n ) \n OR (\n t1.k9_cor"..., length=3036, found_semicolon=0x9d4f9234) at sql_parse.cc:6152
#37 0x082e10a1 in dispatch_command (command=COM_QUERY, thd=0x949e66d8,
packet=0x9d422021 "SELECT \n t1.k9_corp_id,\n t1.barks,\n t1.birthday\nFROM\n t1\nWHERE (\n (\n t1.k9_corp_id = '21750' \n AND t1.barks = '1' \n AND t1.birthday = '2007-03-16'\n ) \n OR (\n t1.k9_cor"..., packet_length=3036) at sql_parse.cc:1228
#38 0x082e054b in do_command (thd=0x949e66d8) at sql_parse.cc:923
#39 0x082dd4d1 in handle_one_connection (arg=0x949e66d8) at sql_connect.cc:1193

Comment by Elena Stepanova [ 2012-03-14 ]

Re: Query casues excessive memory consumption
Addition to the previous comment: not reproducible on MariaDB 5.2 or MySQL 5.6.4.

Comment by Oleksandr Byelkin [ 2012-03-15 ]

Re: Query casues excessive memory consumption
It dies in make_join_statistic during optimization

Comment by Oleksandr Byelkin [ 2012-03-15 ]

Re: Query casues excessive memory consumption
It is update_ref_and_keys call:

if (conds || outer_join)
{
if (update_ref_and_keys(join->thd, keyuse_array, stat, join->table_count,
conds, ~outer_join, join->select_lex, &sargables))
goto error;

Comment by Oleksandr Byelkin [ 2012-03-15 ]

Re: Query casues excessive memory consumption
Sorry previous was wrong.

it is get_quick_record_count and second call of select->test_quick_select

Comment by Oleksandr Byelkin [ 2012-03-15 ]

Re: Query casues excessive memory consumption
It is first call of SQL_SELECT::test_quick_select and then get_mm_tree

Comment by Oleksandr Byelkin [ 2012-03-15 ]

Re: Query casues excessive memory consumption
the problem is that tree_or() triggers nested calls of SEL_TREE:SEL_TREE and SEL_IMERGE::SEL_MERGE. Then far we moves by list of 27 elements then more nested calls (looks like it is exponential).

Comment by Oleksandr Byelkin [ 2012-03-15 ]

Re: Query casues excessive memory consumption
5.2 did not call creation of SEL_TREE:SEL_TREE and SEL_IMERGE::SEL_MERGE at all

Comment by Oleksandr Byelkin [ 2012-03-15 ]

Re: Query casues excessive memory consumption
BTW, All such recursive calls should check stack overflow (it is not cause of the problem but could be in theory).

Comment by Oleksandr Byelkin [ 2012-03-15 ]

Re: Query casues excessive memory consumption
in 5.2 tree_or() looks like this:

tree_or()
{
...
if (sel_trees_can_be_ored())

{ ... }
else
{ // everything else }
return;
}

in 5.3 it is
tree_or()
{
...
if (sel_trees_can_be_ored())
{ ... }

// everything else including allocating new objects
return;
}

Comment by Oleksandr Byelkin [ 2012-03-15 ]

Re: Query casues excessive memory consumption
The above change was done by WL#24: "index_merge: fair choice between index_merge union and range access"

Comment by Peter (Stig) Edwards (Inactive) [ 2012-03-21 ]

Re: Query casues excessive memory consumption
Thank you very much. I can confirm the fix stops the problem using the isolated reproducers I have. I am holding off retesting MariaDB 5.3 with the actual database where this problem was found because https://bugs.launchpad.net/maria/+bug/954900 was found in the same database.

Comment by Rasmus Johansson (Inactive) [ 2012-04-03 ]

Launchpad bug id: 954262

Generated at Thu Feb 08 06:43:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.