Query casues excessive memory consumption

Bug #954262 reported by Peter (Stig) Edwards on 2012-03-13
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MariaDB
Fix Released
Critical
Igor Babaev

Bug 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:

# 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

Elena Stepanova (elenst) wrote :
Download full text (5.5 KiB)

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, ar...

Read more...

Changed in maria:
milestone: none → 5.3
Elena Stepanova (elenst) wrote :

Addition to the previous comment: not reproducible on MariaDB 5.2 or MySQL 5.6.4.

Elena Stepanova (elenst) on 2012-03-14
Changed in maria:
importance: Undecided → Critical
Elena Stepanova (elenst) on 2012-03-14
Changed in maria:
assignee: nobody → Michael Widenius (monty)

It dies in make_join_statistic during optimization

Changed in maria:
assignee: Michael Widenius (monty) → Oleksandr "Sanja" Byelkin (sanja-byelkin)
status: New → In Progress

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;

Sorry previous was wrong.

it is get_quick_record_count and second call of select->test_quick_select

It is first call of SQL_SELECT::test_quick_select and then get_mm_tree

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).

5.2 did not call creation of SEL_TREE:SEL_TREE and SEL_IMERGE::SEL_MERGE at all

BTW, All such recursive calls should check stack overflow (it is not cause of the problem but could be in theory).

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;
}

The above change was done by WL#24: "index_merge: fair choice between index_merge union and range access"

Changed in maria:
assignee: Oleksandr "Sanja" Byelkin (sanja-byelkin) → Igor Babaev (igorb-seattle)
Changed in maria:
status: In Progress → Fix Committed

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.

Changed in maria:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers