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 - high cpu usage

Discussion in 'General Discussion' started by hackboys, Oct 10, 2008.

  1. hackboys

    hackboys Active Member

    Joined:
    Feb 12, 2008
    Messages:
    31
    Likes Received:
    1
    Trophy Points:
    8
    This is my server info:
    2x Intel Xeon 5405 2.0 GHz Quad Core Processor 12 MB cache L2
    RAM: 4GB
    HDD: Seagate 146 GB 15 000 RPM SCSI

    It's very important for me to stop mysql eating CPU!!

    This is my my.cnf...


    PHP:
    [mysqld]
    local-infile=0
    datadir
    =/var/lib/mysql
    skip
    -locking
    skip
    -bdb
    skip
    -innodb
    skip
    -networking
    safe
    -show-database
    query_cache_limit
    =1M
    query_cache_size
    =128M ## 32MB for every 1GB of RAM
    query_cache_type=1
    max_user_connections
    =200
    max_connections
    =1500
    interactive_timeout
    =10
    long_query_time 
    2
    wait_timeout
    =20
    connect_timeout
    =20
    thread_cache_size
    =128
    key_buffer
    =512M ## 128MB for every 1GB of RAM
    join_buffer=1M
    max_connect_errors
    =999999
    max_allowed_packet
    =16M
    table_cache
    =1024
    record_buffer
    =4M
    key_buffer_size
    =4M
    tmp_table_size
    =16M
    sort_buffer_size
    =4M ## 1MB for every 1GB of RAM
    read_buffer_size=4M ## 1MB for every 1GB of RAM
    read_rnd_buffer_size=4M ## 1MB for every 1GB of RAM
    thread_concurrency=## Number of CPUs x 2
    myisam_sort_buffer_size=64M
    server
    -id=1
    log
    -slow-queries = /var/log/mysql/mysql-slow.log
    concurrent_insert
    =2
    old
    -passwords 1

    [mysql.server]
    user=mysql

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

    [mysqldump]
    quick
    max_allowed_packet
    =16M

    [mysql]
    no-auto-rehash
    #safe-updates

    [isamchk]
    key_buffer=64M
    sort_buffer
    =64M
    read_buffer
    =16M
    write_buffer
    =16M

    [myisamchk]
    key_buffer=64M
    sort_buffer
    =64M
    read_buffer
    =16M
    write_buffer
    =16M

    [mysqlhotcopy]
    interactive-timeout
    I tried everything... I changed my.cnf everytime and it does nothing... I need a solution.
    mysqld processes can reach sometime 80% of the CPU... It's NOT NORMAL!

    PLEASE HELP OPTIMIZE
     
  2. srsman

    srsman Registered

    Joined:
    Nov 1, 2008
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    use this
    Code:
    [mysqld] 
    local-infile=0 
    datadir=/var/lib/mysql 
    skip-locking 
    skip-bdb 
    skip-innodb 
    skip-networking 
    safe-show-database 
    query_cache_limit=1M 
    query_cache_size=256M ## 32MB for every 1GB of RAM 
    query_cache_type=1 
    max_user_connections=200 
    max_connections=1500 
    interactive_timeout=10 
    long_query_time = 2 
    wait_timeout=20 
    connect_timeout=20 
    thread_cache_size=128 
    key_buffer=512M ## 128MB for every 1GB of RAM 
    join_buffer=8M 
    max_connect_errors=999999 
    max_allowed_packet=16M 
    table_cache=1024 
    record_buffer=4M 
    key_buffer_size=256M 
    tmp_table_size=16M 
    sort_buffer_size=6M ## 1MB for every 1GB of RAM 
    read_buffer_size=6M ## 1MB for every 1GB of RAM 
    read_rnd_buffer_size=4M ## 1MB for every 1GB of RAM 
    thread_concurrency=8 ## Number of CPUs x 2 
    myisam_sort_buffer_size=64M 
    server-id=1 
    log-slow-queries = /var/log/mysql/mysql-slow.log 
    concurrent_insert=2 
    old-passwords = 1 
    
    [mysql.server] 
    user=mysql 
    
    [safe_mysqld] 
    err-log=/var/log/mysqld.log 
    pid-file=/var/lib/mysql/mysql.pid 
    open_files_limit=8192 
    
    [mysqldump] 
    quick 
    max_allowed_packet=16M 
    
    [mysql] 
    no-auto-rehash 
    #safe-updates 
    
    [isamchk] 
    key_buffer=64M 
    sort_buffer=64M 
    read_buffer=16M 
    write_buffer=16M 
    
    [myisamchk] 
    key_buffer=64M 
    sort_buffer=64M 
    read_buffer=16M 
    write_buffer=16M 
    
    [mysqlhotcopy] 
    interactive-timeout  
    
    
    
     
  3. SB-Nick

    SB-Nick Well-Known Member

    Joined:
    Aug 26, 2008
    Messages:
    134
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Hello,

    You need someone to fully optimize your server.
    Adding settings to a my.cnf is not that randomly, you need to look at your hardware specs and see what settings will fit better to the mysql server.
     
  4. thewebhosting

    thewebhosting Well-Known Member

    Joined:
    May 9, 2008
    Messages:
    1,201
    Likes Received:
    1
    Trophy Points:
    38
    Search for the users or databases those are using more mysql resources.

    You can check it via WHM->CPU/Memory/MySQL usage OR Mysql processes.
     
  5. cPanelNick

    cPanelNick Administrator
    Staff Member

    Joined:
    Mar 9, 2015
    Messages:
    3,426
    Likes Received:
    2
    Trophy Points:
    38
    cPanel Access Level:
    DataCenter Provider
    Try

    /usr/local/cpanel/bin/sqloptimizer
     
  6. nxweb

    nxweb Active Member

    Joined:
    Oct 29, 2008
    Messages:
    37
    Likes Received:
    0
    Trophy Points:
    6
    Using MySQL configs randomly suggested on a forum is not the best solution. Depends on server specs and what kind of real resource usage and applications that are running. You need to see what's doing what, make adjustments, monitor your changes and re-adjust as needed.
     
  7. ANewDay

    ANewDay Well-Known Member

    Joined:
    Sep 11, 2006
    Messages:
    86
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    USA
  8. hrockvn

    hrockvn Member

    Joined:
    Oct 10, 2007
    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    1
    After run this command. I have a error:

    How to config MySQL to log slow queries???
    Opps. I update this post.
    When I login to shell and run this command.


    I think that mean, my server is working with log_slow_queries. Why when I runing /usr/local/cpanel/bin/sqloptimizer has that error.
     
    #8 hrockvn, Nov 5, 2008
    Last edited: Nov 5, 2008
  9. cPanelNick

    cPanelNick Administrator
    Staff Member

    Joined:
    Mar 9, 2015
    Messages:
    3,426
    Likes Received:
    2
    Trophy Points:
    38
    cPanel Access Level:
    DataCenter Provider
    Please open a ticket @ https://tickets.cpanel.net/submit/
     
  10. isputra

    isputra Well-Known Member

    Joined:
    May 3, 2003
    Messages:
    576
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Mbelitar
    When i run above command, i have this result :

    mysqld is not configured to log slow queries.

    What does this mean ?
     
Loading...

Share This Page