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.

my server get high load from mysql server

Discussion in 'Workarounds and Optimization' started by selvamurali, Aug 4, 2011.

  1. selvamurali

    selvamurali Active Member

    Joined:
    Jan 15, 2009
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    India
    cPanel Access Level:
    Root Administrator
    hi all i get mysql server load very high
    this is showing process list


    Pid Owner Priority CPU % Memory % Command
    26781 (Trace) (Kill) mytitbit 0 18.0 1.0 /usr/bin/php /disk1/mytitbit/public_html/user.php
    25381 (Trace) (Kill) mysql 0 17.9 2.5 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/vmclouds.vmclouds.net.pid --skip-external-locking
    26764 (Trace) (Kill) mytitbit 0 16.2 0.0
    PHP:
     <defunct>
    26802 (Trace) (Kill)    mytitbit    0      14.0         0.6    /usr/bin/php /disk1/mytitbit/public_html/404error.php
    26754 
    (Trace) (Kill)    mytitbit    0      12.1         1.4    /usr/bin/php /disk1/mytitbit/public_html/search.php

    what to 
    do now
    i am running mysql tunner it will given the followning result

    [QUOTE]>>  MySQLTuner 1.1.2 Major Hayden <major@mhtx.net>
     >>  
    Bug reportsfeature requests, and downloads at http://mysqltuner.com/
     
    >>  Run with '--help' for additional options and output filtering

    -------- General Statistics --------------------------------------------------
    [--] 
    Skipped version check for MySQLTuner script
    [OKCurrently running supported MySQL version 5.0.92-community
    [!!] Switch to 64-bit OS MySQL cannot currently use all of your RAM

    -------- Storage Engine Statistics -------------------------------------------
    [--] 
    Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables383M (Tables5179)
    [--] 
    Data in InnoDB tables13M (Tables804)
    [--] 
    Data in MEMORY tables0B (Tables1)
    [!!] 
    Total fragmented tables34

    -------- Performance Metrics -------------------------------------------------
    [--] 
    Up for: 3h 50m 57s (748K q [54.021 qps], 10K connTX860MRX76M)
    [--] 
    Reads Writes98% / 2%
    [--] 
    Total buffers34.0M global + 2.7M per thread (100 max threads)
    [
    OKMaximum possible memory usage302.7M (14of installed RAM)
    [
    OKSlow queries0% (8/748K)
    [
    OKHighest usage of available connections11% (11/100)
    [
    OKKey buffer size total MyISAM indexes8.0M/185.0M
    [OKKey buffer hit rate99.7% (261M cached 776K reads)
    [!!] 
    Query cache is disabled
    [OKSorts requiring temporary tables8% (3K temp sorts 39K sorts)
    [!!] 
    Joins performed without indexes2978
    [!!] Temporary tables created on disk39% (14K on disk 35K total)
    [!!] 
    Thread cache is disabled
    [!!] Table cache hit rate0% (64 open 42K opened)
    [
    OKOpen file limit used10% (107/1K)
    [
    OKTable locks acquired immediately99% (770K immediate 770K locks)
    [!!] 
    InnoDB data size buffer pool13.1M/8.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
        
    Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours 
    recommendations may be inaccurate
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        When making adjustments
    make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Set thread_cache_size to 4 
    as a starting value
        Increase table_cache gradually to avoid file descriptor limits

    Variables to adjust
    :

        
    query_cache_size (>= 8M)
        
    join_buffer_size (> 128.0K, or always use indexes with joins)
        
    tmp_table_size (> 32M)
        
    max_heap_table_size (> 16M)
        
    thread_cache_size (start at 4)
        
    table_cache (> 64)
        
    innodb_buffer_pool_size (>= 13M)[/QUOTE]

    kindle help
     
  2. selvamurali

    selvamurali Active Member

    Joined:
    Jan 15, 2009
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    India
    cPanel Access Level:
    Root Administrator
    can any one help me
     
  3. ramindia

    ramindia Well-Known Member

    Joined:
    Apr 3, 2011
    Messages:
    71
    Likes Received:
    0
    Trophy Points:
    6
    Hi

    kindly post your existing my.conf
     
  4. selvamurali

    selvamurali Active Member

    Joined:
    Jan 15, 2009
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    India
    cPanel Access Level:
    Root Administrator
    how i get the information of my.conf
     
  5. selvamurali

    selvamurali Active Member

    Joined:
    Jan 15, 2009
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    India
    cPanel Access Level:
    Root Administrator
    i seen my my.cnf via ssh

    its told only one line
    [mysqld]

    there is no line after that.
    pls guide me
     
  6. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Hello,

    If /etc/my.cnf only has that line in it, then put the following into that file:

    Code:
    [mysqld]
    innodb_buffer_pool_size = 13M
    max_heap_table_size = 48M
    query_cache_limit = 1M
    query_cache_size = 8M
    query_cache_type = 1
    table_cache = 80
    thread_cache_size = 4
    tmp_table_size = 48M
    After setting the above variables, you could then restart MySQL:

    Code:
    /etc/init.d/mysql restart
    Thanks!
     
  7. huggys

    huggys Registered

    Joined:
    Nov 6, 2009
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    I have the same issue and I was wondering if you could take the time to look at mine as well?

    Code:
    [mysqld]
    old_passwords=1
    datadir=/var/lib/mysql
    skip-locking
    safe-show-database
    tmp_table_size = 64M
    max_heap_table_size = 64M
    query_cache_limit=2M
    query_cache_size=32M ## 32MB for every 1GB of RAM
    query_cache_type=1
    max_user_connections=25
    max_connections=100
    
    collation_server=utf8_unicode_ci
    character_set_server=utf8
    
    delayed_insert_timeout=40
    
    interactive_timeout=10
    wait_timeout=3600
    connect_timeout=20
    thread_cache_size=128
    key_buffer=32M ## 32MB for every 1GB of RAM
    join_buffer=1M
    max_connect_errors=20
    max_allowed_packet=16M
    table_cache=1024
    record_buffer=1M
    sort_buffer_size=1M ## 1MB for every 1GB of RAM
    read_buffer_size=1M ## 1MB for every 1GB of RAM
    read_rnd_buffer_size=1M  ## 1MB for every 1GB of RAM
    thread_concurrency=2 ## Number of CPUs x 2
    myisam_sort_buffer_size=32M
    server-id=1
    
    [mysql.server]
    user=mysql
    
    [safe_mysqld]
    err-log=/var/log/mysqld.log
    pid-file=/var/lib/mysql/mysql.pid
    
    [mysqldump]
    quick
    max_allowed_packet=16M
    
    [mysql]
    no-auto-rehash
    log-slow-queries=/var/log/mysql_slow_queries.log
    
    Thank you!
     
  8. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Hello huggys,

    If you post it into your own thread, we could look at it. It isn't normally advisable to post your issue into another thread when it's a discussion on configuring specific server options.

    If you'd like, I would be happy to move your post into your own thread.

    Thanks!
     
Loading...

Share This Page