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 146% CPU usage

Discussion in 'Workarounds and Optimization' started by gtraxx, Apr 15, 2013.

  1. gtraxx

    gtraxx Member

    Joined:
    Nov 30, 2011
    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi, we have a VPS with 2.5 GHz and 3 G RAM
    We will expand to 4.5 GHz and 7.5 G RAM as the CPU Mysql comes up 146%
    MySQLTuner Report with 2.5 GHz and 3 G RAM

    How better to change the configuration of power and the actual power

    Code:
    >>  MySQLTuner 1.2.0 - 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.5.30-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 64M (Tables: 71)
    [--] Data in InnoDB tables: 167M (Tables: 1021)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 261
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 53m 13s (1M q [358.405 qps], 5K conn, TX: 2B, RX: 504M)
    [--] Reads / Writes: 99% / 1%
    [--] Total buffers: 168.0M global + 2.8M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.5G (51% of installed RAM)
    [OK] Slow queries: 0% (496/1M)
    [OK] Highest usage of available connections: 24% (121/500)
    [!!] Key buffer size / total MyISAM indexes: 8.0M/16.4M
    [!!] Key buffer hit rate: 91.3% (24K cached / 2K reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (3 temp sorts / 306K sorts)
    [!!] Joins performed without indexes: 7384
    [OK] Temporary tables created on disk: 12% (15K on disk / 123K total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (512 open / 71K opened)
    [OK] Open file limit used: 0% (22/4K)
    [OK] Table locks acquired immediately: 100% (4M immediate / 4M locks)
    [!!] InnoDB data size / buffer pool: 167.6M/128.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Adjust your join queries to always utilize indexes
        Set thread_cache_size to 4 as a starting value
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        key_buffer_size (> 16.4M)
        query_cache_size (>= 8M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        thread_cache_size (start at 4)
        table_cache (> 512)
        innodb_buffer_pool_size (>= 167M)
    
    nano /etc/my.conf :

    Code:
    [mysqld]
    # safe-show-database
    bind-address=127.0.0.1
    log-slow-queries
    max_connections=500
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    open_files_limit=4300
    table_cache=512
    #log=/var/log/mysql.general.log
    
    Best regards
     
    #1 gtraxx, Apr 15, 2013
    Last edited: Apr 15, 2013
  2. kernow

    kernow Well-Known Member

    Joined:
    Jul 23, 2004
    Messages:
    865
    Likes Received:
    9
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    You really should leave Mysql running at least 48 hours before running the tune up script.
    Install "mytop" its a Mysql version of the basic top script.
     
  3. Eric

    Eric Administrator
    Staff Member

    Joined:
    Nov 25, 2007
    Messages:
    746
    Likes Received:
    11
    Trophy Points:
    18
    Location:
    Texas
    cPanel Access Level:
    Root Administrator
    Howdy,

    You might add

    Code:
    0 2 * * * mysqlcheck -oA
    
    to your cron jobs. So that at least once a day the db's are not fragmented. It will also help your backup speeds.

    Thanks!
     
  4. gtraxx

    gtraxx Member

    Joined:
    Nov 30, 2011
    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi,
    mytop ? I find it or ?
    The task improves performance ?
    And how can I improve the configuration file my.conf

    Best regards
     
  5. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    1
    Trophy Points:
    16
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    mysqlcheck -Ao will optimise all database tables
     
  6. Eric

    Eric Administrator
    Staff Member

    Joined:
    Nov 25, 2007
    Messages:
    746
    Likes Received:
    11
    Trophy Points:
    18
    Location:
    Texas
    cPanel Access Level:
    Root Administrator
    Howdy,

    mytuner.pl will help you with your configuration file. But you get to a point where you reach diminishing returns. Optimizing the tables really helps on busy databases. Think of it as defragmenting tablespace. Already optimized tables are skipped when using this command.

    Thanks!
     
Loading...

Share This Page