Announcement Module
No announcement yet.

Optimal Memory settings

Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Optimal Memory settings

    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.