Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

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,608
    Likes Received:
    32
    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!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #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,608
    Likes Received:
    32
    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!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #8 cPanelTristan, Aug 9, 2011
(You must log in or sign up to post here.)
Loading...
Similar Threads - server high load
  1. bdspice

    XML-API nvset causing high server load

    bdspice, Nov 5, 2018, in forum: General Discussion
    Replies:
    1
    Views:
    92
    cPanelLauren
    Nov 6, 2018
  2. cPanelResources

    Tutorial Troubleshooting high server loads on Linux servers

    cPanelResources, May 8, 2018, in forum: Workarounds and Optimization
    Replies:
    0
    Views:
    413
    cPanelResources
    May 8, 2018
  3. leoteoh

    Server load average high or overloaded?

    leoteoh, Apr 3, 2018, in forum: Workarounds and Optimization
    Replies:
    1
    Views:
    397
    cPanelMichael
    Apr 3, 2018
  4. rasanjanad

    High Server Load & Nobody Process

    rasanjanad, Nov 28, 2017, in forum: Workarounds and Optimization
    Replies:
    2
    Views:
    803
    cPanelMichael
    Nov 28, 2017
  5. gullableJoe

    High server load

    gullableJoe, Aug 29, 2017, in forum: Data Protection
    Replies:
    2
    Views:
    2,197
    cPanelMichael
    Aug 30, 2017

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Accept Learn More...
    Dismiss Notice