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:
    56
    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 reports, feature requests, and downloads at http://mysqltuner.com/
         >>  Run with '--help' for additional options and output filtering

    -------- General Statistics --------------------------------------------------
    [--]     Skipped version check for MySQLTuner script
    [OK] Currently 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 tables: 383M (Tables: 5179)
    [--]     Data in InnoDB tables: 13M (Tables: 804)
    [--]     Data in MEMORY tables: 0B (Tables: 1)
    [!!]     Total fragmented tables: 34

    -------- Performance Metrics -------------------------------------------------
    [--]     Up for: 3h 50m 57s (748K q [54.021 qps], 10K conn, TX: 860M, RX: 76M)
    [--]     Reads / Writes: 98% / 2%
    [--]     Total buffers: 34.0M global + 2.7M per thread (100 max threads)
    [    OK] Maximum possible memory usage: 302.7M (14% of installed RAM)
    [    OK] Slow queries: 0% (8/748K)
    [    OK] Highest usage of available connections: 11% (11/100)
    [    OK] Key buffer size / total MyISAM indexes: 8.0M/185.0M
    [OK] Key buffer hit rate: 99.7% (261M cached / 776K reads)
    [!!]     Query cache is disabled
    [OK] Sorts requiring temporary tables: 8% (3K temp sorts / 39K sorts)
    [!!]     Joins performed without indexes: 2978
    [!!] Temporary tables created on disk: 39% (14K on disk / 35K total)
    [!!]     Thread cache is disabled
    [!!] Table cache hit rate: 0% (64 open / 42K opened)
    [    OK] Open file limit used: 10% (107/1K)
    [    OK] Table locks acquired immediately: 99% (770K immediate / 770K locks)
    [!!]     InnoDB data size / buffer pool: 13.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
     
    #1 selvamurali, Aug 4, 2011
  2. selvamurali

    selvamurali Active Member

    Joined:
    Jan 15, 2009
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    56
    Location:
    India
    cPanel Access Level:
    Root Administrator
    can any one help me
     
    #2 selvamurali, Aug 4, 2011
  3. ramindia

    ramindia Well-Known Member

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

    kindly post your existing my.conf
     
    #3 ramindia, Aug 4, 2011
  4. selvamurali

    selvamurali Active Member

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

    selvamurali Active Member

    Joined:
    Jan 15, 2009
    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    56
    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
     
    #5 selvamurali, Aug 6, 2011
  6. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,622
    Likes Received:
    25
    Trophy Points:
    238
    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!
     
    #6 cPanelTristan, Aug 8, 2011
  7. huggys

    huggys Registered

    Joined:
    Nov 6, 2009
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    51
    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!
     
    #7 huggys, Aug 8, 2011
  8. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,622
    Likes Received:
    25
    Trophy Points:
    238
    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!
     
    #8 cPanelTristan, Aug 9, 2011
(You must log in or sign up to post here.)
Loading...
Similar Threads - server high load
  1. lambov

    server load very high suddenly

    lambov, Feb 16, 2017, in forum: EasyApache
    Replies:
    2
    Views:
    852
    cPanelMichael
    Feb 17, 2017
  2. morrisj88

    High Server Load from checkpassword-reply

    morrisj88, Feb 7, 2017, in forum: General Discussion
    Replies:
    5
    Views:
    311
    cPanelMichael
    Feb 9, 2017
  3. geekcohen

    SOLVED High Load - lvestats-server

    geekcohen, Dec 18, 2016, in forum: CloudLinux
    Replies:
    3
    Views:
    1,208
    cPanelMichael
    Dec 18, 2016
  4. serverxy

    High Server Load Issue

    serverxy, Nov 3, 2016, in forum: General Discussion
    Replies:
    4
    Views:
    442
    Infopro
    Nov 3, 2016
  5. nithushan8

    High Server Load issue

    nithushan8, Mar 24, 2016, in forum: Database Discussions
    Replies:
    3
    Views:
    864
    Infopro
    Mar 27, 2016

Share This Page