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.

MySQL gone crazy

Discussion in 'Workarounds and Optimization' started by desiguru, Nov 4, 2010.

  1. desiguru

    desiguru Well-Known Member

    Joined:
    Aug 27, 2008
    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    6
    I have been looking at my shared server's "top" or "Show Current CPU Usage" for quite sometime now and the one thing that is always on top is the MySQL error pid. which is something like this


    Code:
     /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/etc/httpd/logs/mysqld.err --pid-file=/var/lib/mysql/server.server.com.pid 
    Here is my my.cnf

    Code:
    [mysqld]
    max_allowed_packet=100M
    set-variable = max_connections=600
    safe-show-database
    # Connection timeout
    connect_timeout                   = 20
    
    # Timeout for inactive connections
    wait_timeout                      = 60
    
    # Network buffer length (I think this is the linux default)
    net_buffer_length                 = 16K
    log-error                         = /etc/httpd/logs/mysqld.err
    binlog_cache_size                 = 2M
    table_cache                       = 6588
    join_buffer_size                  = 320M
    tmp_table_size                    = 512M
    sort_buffer_size                  = 128M
    thread_cache_size                 = 6
    max_heap_table_size               = 320M
    query_cache_size                  = 312M
    key_buffer_size                   = 128M
    [client]
    socket=/var/lib/mysql/mysql.sock
    

    How can I optimize mySQL so that it doesnt end up using a lot of my CPU resources and slowing down a lot of work for my users?

    Thanks a lot for anyone who helps.
     
  2. rlshosting

    rlshosting Well-Known Member

    Joined:
    Apr 23, 2009
    Messages:
    170
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    United States
    cPanel Access Level:
    Root Administrator
  3. johnburk

    johnburk Well-Known Member

    Joined:
    Jun 23, 2006
    Messages:
    241
    Likes Received:
    0
    Trophy Points:
    16
  4. desiguru

    desiguru Well-Known Member

    Joined:
    Aug 27, 2008
    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    6
    Thank you. That script is really awesome.

    How can I fix this? I got everything else working fine now.

    Code:
    MEMORY USAGE
    Max Memory Ever Allocated : 11.39 G
    Configured Max Per-thread Buffers : 262.86 G
    Configured Max Global Buffers : 450 M
    Configured Max Memory Limit : 263.30 G
    Physical Memory : 7.78 G
    
    nMax memory limit exceeds 90% of physical memory
    
     
  5. GaryT

    GaryT Well-Known Member

    Joined:
    May 19, 2010
    Messages:
    321
    Likes Received:
    3
    Trophy Points:
    16
    Lower your max connections, That will remove the error, Its cause each connection is allocated a set amount of memory and your exceeding that.
     
Loading...

Share This Page