The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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:
    16
    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:
    691
    Likes Received:
    1
    Trophy Points:
    18
    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:
     
  4. haze

    haze Well-Known Member

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

    cPanel.net Support Ticket Number:
     
  5. Angel78

    Angel78 Well-Known Member

    Joined:
    May 9, 2002
    Messages:
    413
    Likes Received:
    1
    Trophy Points:
    16
    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:
    36
    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:
     
  7. equivity

    equivity Well-Known Member

    Joined:
    Aug 12, 2003
    Messages:
    57
    Likes Received:
    0
    Trophy Points:
    6
    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
     
  8. adapter

    adapter Well-Known Member
    PartnerNOC

    Joined:
    Sep 17, 2003
    Messages:
    391
    Likes Received:
    0
    Trophy Points:
    16
    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:
    6
    Same here, very curious why..
     
  10. Angel78

    Angel78 Well-Known Member

    Joined:
    May 9, 2002
    Messages:
    413
    Likes Received:
    1
    Trophy Points:
    16
    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:
    1
    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