Optimal Memory settings

MySQL & MariaDB Other MySQL® Questions
1

So we have been getting sig 11 crashes with our 5.0.36 mysql setup with the last query that runs before it crashes returning: Can’t create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

This makes me suspect that we have misconfigured our memory settings in my.cnf

The manual states that you should follow this rule.

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = mem_used

We run an entirley innodb load (except mysql table itself) so I figure this calculation needs to be modified to take into account the various innodb memory spaces.

Does this appear to be proper formula to use with innodb:

innodb_buffer_pool_size + innodb_additional_mem_pool_size + key_buffer_size + innodb_log_buffer_size + (read_buffer_size + sort_buffer_size + join_buffer_size + read_rnd_buffer_size)*max_connections = mem_used

Does the read_buffer_size and sort_buffer size matter to innodb?

2

The read_buffer_size and sort_buffer_size do apply even though you are using InnoDB.

The read_buffer is used when a query has to performa a sequential scan of a table and is not bound to a specific storage engine.

The sort_buffer is always used for cases when you have an ORDER BY or a GROUP BY where it needs to perform a sort of the data to perform these operations.

And yes I think that your formula is pretty accurate.

But if you get these strange out of memory errors. Remember that sometimes you have ulimit settings that limit the memory that a single process is allowed to use.

That has been a problem for several people before you.