Query causes RAM usage spike

[Andrew Hutchings]

The following query spikes to 1.8G resident RAM usage (assumes you have test.t1 existing):

SELECT * FROM DATA_DICTIONARY.COLUMNS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1' ORDER BY ORDINAL_POSITION;

This is only when using the ORDER BY, otherwise it stays at 50MB.

[Padraig O'Sullivan (posulliv)]

This bug is because of the position function in the FunctionCursor class. This function looks like:

void FunctionCursor::position(const unsigned char *record)
{
  if (row_cache.size() <= record_id * table->getShare()->getRecordLength())
  {
    row_cache.resize(row_cache.size() + table->getShare()->getRecordLength() * 100); // Hardwired at adding an additional 100 rows of storage
  }
  memcpy(&row_cache[record_id * table->getShare()->getRecordLength()], record, table->getShare()->getRecordLength());
  internal::my_store_ptr(ref, ref_length, record_id);
  record_id++;
}

For the columns table in the data dictionary, the getRecordLength() function returns the value 81132. The columns table in drizzle has 510 rows by default. Thus, if you issue this query:

select * from data_dictionary.columns order by ordinal_position

the row_cache vector will get to be of size:

81132 * 100 * 5 = 40566000

The vector is of unsigned chars so we multiply by 8 to get bytes:

40566000 * 8 = 324528000 bytes = 324 MB

Thus, the query requires 324 MB of memory to be allocated when an ORDER BY clause is used.

[Stewart Smith (stewart)]

wow... there should sort of be a limit here.

Also note that this will be the unpacked record size, so it's generally huge.

[Lee Bieber (kalebral-deactivatedaccount)]

From Andrew - I believe krow has a solution to this one and that it is at least partially fixed already. It certainly shouldn't be critical any more

[Andrew Hutchings (linuxjedi)]

Moving this back up to High, going to cause big issues for the INNODB_REPLICATION_LOG table now that the row length has dramatically increased.

[David Shrewsbury (dshrews)]

Coincidentally, while running under the debugger on OS X, I saw this warning: (note - must start debugger with dtr --start-and-exit --gdb). All I did was connect and execute "use test". 105MB for "use test"?????

(gdb) c
Continuing.
GuardMalloc[drizzled-64640]: Attempting excessively large memory allocation: 105395200 bytes
GuardMalloc[drizzled-64640]: If you really wanted to allocate so much memory, launch your executable with the environment variable MALLOC_PERMIT_INSANE_REQUESTS set to any value to circumvent this check.
GuardMalloc[drizzled-64640]: Explicitly trapping into debugger!!!

Program received signal SIGTRAP, Trace/breakpoint trap.
0x00000001012eed92 in GMmalloc_zone_malloc_internal ()
(gdb) bt
#0 0x00000001012eed92 in GMmalloc_zone_malloc_internal ()
#1 0x00007fff87bb8f05 in operator new ()
#2 0x000000010034bf17 in __gnu_cxx::new_allocator<unsigned char*>::allocate (this=0x147d00fd0, __n=13174400) at new_allocator.h:91
#3 0x000000010034bf3f in std::_Vector_base<unsigned char*, std::allocator<unsigned char*> >::_M_allocate (this=0x147d00fd0, __n=13174400) at stl_vector.h:131
#4 0x000000010034c1fa in std::vector<unsigned char*, std::allocator<unsigned char*> >::_M_fill_insert (this=0x147d00fd0, __position={_M_current = 0x14efdb000}, __n=1646800, __x=@0x1068d8c08) at vector.tcc:354
#5 0x000000010034c397 in std::vector<unsigned char*, std::allocator<unsigned char*> >::insert (this=0x147d00fd0, __position={_M_current = 0x14efdb000}, __n=1646800, __x=@0x1068d8c08) at stl_vector.h:656
#6 0x000000010034c413 in std::vector<unsigned char*, std::allocator<unsigned char*> >::resize (this=0x147d00fd0, __new_size=8234000, __x=0x0) at stl_vector.h:424
#7 0x000000010034b226 in FunctionCursor::position (this=0x147d00e50, record=0x147d02f48 "òÿ\001\005") at plugin/function_engine/cursor.cc:107

[Stewart Smith (stewart)]

to start up drizzled with dtr --start-and-exit and connect:
8.9MB in FunctionCursor ::position memory

and run "SELECT * FROM DATA_DICTIONARY.COLUMNS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1' ORDER BY ORDINAL_POSITION;"
45MB

If we used packed rows like ARCHIVE does (not compressed, just using the same row packing that the archive engine uses before compressing), we could be down to: 51K and 78K instead (plus array of number of records length to map record_id to offset in the buffer)

[Stewart Smith (stewart)]

running information_schema_dictionary test suite:

BEFORE:

    MB
77.57^ #
     | #
     | #
     | #: :::
     | #:::::
     | #:::::
     | #:::::
     | #:::::
     | @:@@:#:::::
     | @:@@:#:::::
     | :::::@:::::::::@:::::::::::::::::::::::::::::@:::::@::@:@@:#:::::
     | :::::@:: ::: ::@ ::: ::: :::: ::: :::: ::::::@:::::@::@:@@:#:::::
     | ::::::@:: ::: ::@ ::: ::: :::: ::: :::: ::::::@:::::@::@:@@:#:::::
     | ::::::@:: ::: ::@ ::: ::: :::: ::: :::: ::::::@:::::@::@:@@:#:::::
     | ::::::::@:: ::: ::@ ::: ::: :::: ::: :::: ::::::@:::::@::@:@@:#::::::@:
     | ::::::::@:: ::: ::@ ::: ::: :::: ::: :::: ::::::@:::::@::@:@@:#::::::@:
     | ::::::::@:: ::: ::@ ::: ::: :::: ::: :::: ::::::@:::::@::@:@@:#::::::@:
     | ::::::::@:: ::: ::@ ::: ::: :::: ::: :::: ::::::@:::::@::@:@@:#::::::@:
     | ::::::::@:: ::: ::@ ::: ::: :::: ::: :::: ::::::@:::::@::@:@@:#::::::@:
     | ::::::::@:: ::: ::@ ::: ::: :::: ::: :::: ::::::@:::::@::@:@@:#::::::@:
   0 +----------------------------------------------------------------------->Gi
     0 8.797

AFTER:

    MB
33.78^ #
     | @:#::
     | @@ @:::::::@::::@@@@:#:::::
     | : :@ :::::@:::::::::@::::::::::::::@:::@:::::::@::::@@@@:#:::::
     | ::::@:@ :::::@::::::: :@::::::::: ::::@:::@:::::::@::::@@@@:#:::::
     | : ::@:@ :::::@::::::: :@::::::::: ::::@:::@:::::::@::::@@@@:#:::::@::
     | ::: ::@:@ :::::@::::::: :@::::::::: ::::@:::@:::::::@::::@@@@:#:::::@::
     | ::: ::@:@ :::::@::::::: :@::::::::: ::::@:::@:::::::@::::@@@@:#:::::@::
     | ::: ::@:@ :::::@::::::: :@::::::::: ::::@:::@:::::::@::::@@@@:#:::::@::
     | ::: ::@:@ :::::@::::::: :@::::::::: ::::@:::@:::::::@::::@@@@:#:::::@::
     | ::: ::@:@ :::::@::::::: :@::::::::: ::::@:::@:::::::@::::@@@@:#:::::@::
     | ::: ::@:@ :::::@::::::: :@::::::::: ::::@:::@:::::::@::::@@@@:#:::::@::
     | ::: ::@:@ :::::@::::::: :@::::::::: ::::@:::@:::::::@::::@@@@:#:::::@::
     | ::: ::@:@ :::::@::::::: :@::::::::: ::::@:::@:::::::@::::@@@@:#:::::@::
     | ::: ::@:@ :::::@::::::: :@::::::::: ::::@:::@:::::::@::::@@@@:#:::::@::
     | ::: ::@:@ :::::@::::::: :@::::::::: ::::@:::@:::::::@::::@@@@:#:::::@::
     | ::: ::@:@ :::::@::::::: :@::::::::: ::::@:::@:::::::@::::@@@@:#:::::@::
     | ::: ::@:@ :::::@::::::: :@:...

[Andrew Hutchings (linuxjedi)]

nice one! :)

[Stewart Smith (stewart)]

So my little experiment with packing the rows was pretty easy to turn into something that was actually fully functional and would pass tests.

commit log kinda explaining it:

  Replace FunctionCursor row_cache of full rows with one that is the
  packed row (using similar routines as ARHCIVE does before
  compression). This means we use minimal memory for each row. The ref
  for ::position() is now a offset into the row_cache buffer instead of
  an incrementing index into an array.

  This change GREATLY reduces the memory required for various queries on
  table function tables.

  e.g. (measured using valgrind --tool=massif) running the
  information_schema_dictionary test suite
  BEFORE: peaked at 77.5MB heap usage
  AFTER: peaked at 33.7MB heap usage

  for schema_dictionary suite:
  BEFORE: peaked at 782.6MB heap usage
  AFTER: peaked at 31.05MB heap usage

  (i.e. memory requirements can be reduced by moer than an order of
  magnitude)

  A future patch may want to also overflow to disk if needed.