Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

IMPORTANT: MAX CONNECTIONS USED (mySQL)

Discussion in 'General Discussion' started by AlaskanWolf, Mar 4, 2003.

  1. AlaskanWolf

    AlaskanWolf Well-Known Member

    Joined:
    Aug 11, 2001
    Messages:
    537
    Likes Received:
    0
    Trophy Points:
    316
    Location:
    Fremont CA
    For a while now we were struggling with a few of our machines, and boy i mean 5am restarting mysql struggling

    I had max_user_connections in there as with max_connections, but no matter what i did, it always seemed to crash due to a few big users

    Keep in mind, that max came before max users, so tonight, on another admin's suggestion, we switched em, and ran a test, and sure enough, the 1 user got the max user connect, while the system stayed alive!

    Heres what we use as our /etc/my.cnf on all our servers, we chattr it so nothing overwrites it



    [mysqld]
    skip-locking
    set-variable = max_user_connections=45
    set-variable = max_connections=500
    set-variable = key_buffer=16M
    set-variable = join_buffer=4M
    set-variable = record_buffer=1M
    set-variable = sort_buffer=5M
    set-variable = table_cache=1024
    set-variable = myisam_sort_buffer_size=32M
    set-variable = thread_cache_size=128
    set-variable = wait_timeout=300
    set-variable = connect_timeout=10
    set-variable = max_allowed_packet=16M
    set-variable = max_connect_errors=10
    set-variable = flush_time=1800

    [mysqldump]
    quick
    set-variable= max_allowed_packet=16M

    [mysqlhotcopy]
    interactive-timeout

    [isamchk]
    set-variable = key=16M
    set-variable = key_buffer=20M
    set-variable = sort_buffer=20M
    set-variable = read_buffer=2M
    set-variable = write_buffer=2M

    [myisamchk]
    set-variable = key_buffer=20M
    set-variable = sort_buffer=20M
    set-variable = read_buffer=2M
    set-variable = write_buffer=2M

    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates
     
  2. ozzi4648

    ozzi4648 Guest

    Try this one. Note the name of the pid file. Make sure you replace mine with yours. Also, i dont see your interactive_timeout so your connections are left open for the default time instead of shutting down the connection in a reasonable time.

    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    skip-locking
    skip-innodb
    set-variable = max_connections=500
    set-variable = interactive_timeout=100
    set-variable = wait_timeout=100
    set-variable = connect_timeout=10
    set-variable = thread_cache_size=8
    set-variable = key_buffer=384M
    set-variable = max_allowed_packet=16M
    set-variable = table_cache=1024
    set-variable = sort_buffer=2M
    set-variable = record_buffer=2M
    set-variable = thread_cache=8
    # Try number of CPU's*2 for thread_concurrency
    set-variable = thread_concurrency=8
    set-variable = myisam_sort_buffer_size=64M
    log-bin
    server-id = 1

    [mysql.server]
    user=mysql
    basedir=/var/lib

    [safe_mysqld]
    err-log=/var/log/mysqld.log
    pid-file=/var/lib/mysql/srv05.primenet.cc.pid
    open_files_limit=8192

    [mysqldump]
    quick
    set-variable = max_allowed_packet=16M

    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates

    [isamchk]
    set-variable = key_buffer=256M
    set-variable = sort_buffer=256M
    set-variable = read_buffer=2M
    set-variable = write_buffer=2M

    [myisamchk]
    set-variable = key_buffer=256M
    set-variable = sort_buffer=256M
    set-variable = read_buffer=2M
    set-variable = write_buffer=2M

    [mysqlhotcopy]
    interactive-timeout
     
    #2 ozzi4648, Mar 4, 2003
    Last edited by a moderator: Mar 4, 2003
  3. myusername

    myusername Well-Known Member
    PartnerNOC

    Joined:
    Mar 6, 2003
    Messages:
    693
    Likes Received:
    1
    Trophy Points:
    168
    Location:
    chown -R us.*yourbase*
    cPanel Access Level:
    DataCenter Provider
    Twitter:
    Will cPanel overwrite these configurations? Meaning does one have to chattr +i the /etc/my.cnf file after it has been PICO'd?

    cPanel.net Support Ticket Number:
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  4. haze

    haze Well-Known Member

    Joined:
    Dec 21, 2001
    Messages:
    1,550
    Likes Received:
    3
    Trophy Points:
    318
    cPanel has never overwritten my my.cnf settings.

    cPanel.net Support Ticket Number:
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  5. Angel78

    Angel78 Well-Known Member

    Joined:
    May 9, 2002
    Messages:
    413
    Likes Received:
    1
    Trophy Points:
    318
    no. it want, or at least it didnt use to do it.

    cPanel.net Support Ticket Number:
     
  6. ciphervendor

    ciphervendor Well-Known Member

    Joined:
    Aug 26, 2002
    Messages:
    1,052
    Likes Received:
    0
    Trophy Points:
    166
    The value of:

    set-variable = wait_timeout=300

    seems a little high. There is no need to wait 5 minutes for another connection on that MySQL process. I would set this to 8 seconds...if not something less...and then adjust your connect timeout variable accordingly.

    Further to this, if you're running MySQL 4 the "set-variable =" syntax has been depreciated and is no longer required.

    cPanel.net Support Ticket Number:
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  7. equivity

    equivity Well-Known Member

    Joined:
    Aug 12, 2003
    Messages:
    57
    Likes Received:
    0
    Trophy Points:
    156
    Location:
    Daytona Beach, Florida
    I am a newbie to this, but I seem to have the same problem with max connections on mysql.


    Will PICO the above file into the .cnf file help to fix this, and do I copy and paste it and save it (after making the minor changes the above post suggests?)


    Any insight and help would be very appreciated. Thanks
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  8. adapter

    adapter Well-Known Member
    PartnerNOC

    Joined:
    Sep 17, 2003
    Messages:
    391
    Likes Received:
    0
    Trophy Points:
    166
    Hi,

    my.cnf on my box only have this:

    [mysqld]
    set-variable = max_connections=500


    i need to add also the other line that i see in this topic?
     
  9. TheSpidre

    TheSpidre Active Member

    Joined:
    Mar 10, 2003
    Messages:
    36
    Likes Received:
    0
    Trophy Points:
    156
    Same here, very curious why..
     
  10. Angel78

    Angel78 Well-Known Member

    Joined:
    May 9, 2002
    Messages:
    413
    Likes Received:
    1
    Trophy Points:
    318
    well if you need to optimize your mysql then for sure, but dont take someone elses my.cnf as "the best" as they may have dual xeon / amd duron with 4 GB / 128 mb ram, you should test it yourself.
     
  11. Norcomp

    Norcomp Member

    Joined:
    Jul 20, 2003
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    151
    Suggestion on a optimal my.cnf setting with this spec?

    Processor #1 Vendor: GenuineIntel
    Processor #1 Name: Intel(R) Pentium(R) 4 CPU 2.80GHz
    Processor #1 speed: 2793.060 MHz
    Processor #1 cache size: 512 KB

    Processor #2 Vendor: GenuineIntel
    Processor #2 Name: Intel(R) Pentium(R) 4 CPU 2.80GHz
    Processor #2 speed: 2793.060 MHz
    Processor #2 cache size: 512 KB

    2Gb mem, raid 1
     
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice