Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2006
    Posts
    516

    my.cnf optimization question

    MySQL keep giving me error like this:

    Warning: mysql_connect() [function.mysql-connect]: 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 in /home/user/public_html/file.php on line 400
    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


    My server has 5GB SWAP, 2GB RAM

    [my.cnf]
    key buffer = 512m
    read buffer = 4m
    sort buffer = 4m
    max connection = 500

    i learn using a formula key buffer + (read+sort)*max connection

    But what is the best way to solve this problem? Which one should I increase?

  2. #2
    You shouldn't count on swap memory. Make sure you fit into 2Gb, less is better, I believe MySQL is not alone running on your system.

  3. #3
    Join Date
    Apr 2006
    Posts
    516
    mind to guide me how i can make changes to solve this sql problem?

    my current settings are:

    key buffer = 512m
    read buffer = 4m
    sort buffer = 4m
    max connection = 500

  4. #4
    I don't know the nature of your system, whether it requires large key buffer or 500 connections simultaneously. But give a try by reducing key buffer to 128m and max connections to 100.

  5. #5
    Join Date
    Mar 2007
    Location
    Dublin
    Posts
    17
    Quote Originally Posted by iliya428 View Post
    I don't know the nature of your system, whether it requires large key buffer or 500 connections simultaneously. But give a try by reducing key buffer to 128m and max connections to 100.
    The general formula is

    key_buffer + ( (read_buffer + sort_buffer) * max_connections)

    i.e. in your case:

    512M + ( ( 4 + 4 ) * 500) = 4512M

    That's the available memory you're telling MySQL you have. In general, swapping is _not_ a good idea.

    Bearing in mind that your OS itself and whatever else is running on the machine needs memory, you either need to:

    1) increase the amount of physical memory available

    2) refactor / rework some of your code (and SQL statements) so MySQL is less memory intensive. Some things you can look into here are persistent connections and your SQL statements (are you doing a 'SELECT *' where it's not needed? Are you paginating data? etc. etc.)

    3) reduce these settings. iliya428's value are a little conservative, but a pretty good starting point. I'd always start a little lower and increase sparingly as opposed to the other way around. This depends what else is running on the system obviously.

    Phil.
    Want me for contracting? Please read

    http://www.brassy.net/contracting/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •