Re: "Query failed. Got error 12 from storage engine" under heavy load.

[Ingo Strüwing]

I guess you hit the limits. Error 12 is ENOMEM. Your operating system refuses to give more memory to MySQL. Most probably this happens when the JOIN tries to allocate a temporary result table.

You didn't say explicitly, but I assume you run a 32-bit system. This means that your address space per process is limited to far below 4GB. You have the executable code, the data, shared segments (e.g. for shared libraries) and stack. These segments are arranged so that an avergage program can make best use of all these segments. Thus, the data segment cannot grow even close to 4GB. You may try the following small program:

#include <stdio.h>
#include <stdlib.h>

int main ( void )
{
size_t siz = 100 * 1024 * 1024 ;
size_t idx = 1 ;
void *ptr ;

for (;;)
{
ptr = malloc ( siz * idx );
if ( ! ptr )
break ;
free ( ptr );
idx ++ ;
}
printf ( "Max malloc %d * 100 MB \n", idx - 1 );
return ( 0 );
}

On my P4 system it results in "Max malloc 28 * 100 MB" which means 2.8 GB.

To get more out of your machine, you could either run a 64-bit operating system and 64-bit MySQL (if possible) or split your database system into multiple processes so that each of them can have that much of memory. Create a master-slave replication. Distribute the selects to the slaves and the modifications to the master.

Ingo Strüwing, Senior Software Developer - Storage Engines
MySQL AB, www.mysql.com