Query casues excessive memory consumption

[Peter (Stig) Edwards]

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

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

[Elena Stepanova (elenst)]

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

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

It dies in make_join_statistic during optimization

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

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;

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

Sorry previous was wrong.

it is get_quick_record_count and second call of select->test_quick_select

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

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

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

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

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

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

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

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

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

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

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

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

[Peter (Stig) Edwards (thatsafunnyname)]

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.