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.

Please help us for my.cnf editing.

Discussion in 'Workarounds and Optimization' started by tefon, Apr 25, 2013.

  1. tefon

    tefon Registered

    Joined:
    Feb 4, 2013
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi they all

    We getting error mysql cpu %150, %250, %300 usage. :mad:

    =================================================================
    My.cnf
    =================================================================
    Code:
    [mysqld]
    innodb_file_per_table=1
    default-storage-engine=MyISAM
    open_files_limit=4394
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    
    [mysqld]
    log_slow_queries = /var/log/mysql-slow.log
    long_query_time = 1
    =================================================================
    Mysqltuner
    =================================================================
    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: 348M (Tables: 283)
    [--] Data in InnoDB tables: 1M (Tables: 75)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 15
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1h 24m 15s (306K q [60.726 qps], 7K conn, TX: 5B, RX: 18M)
    [--] Reads / Writes: 98% / 2%
    [--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
    [OK] Maximum possible memory usage: 583.2M (3% of installed RAM)
    [OK] Slow queries: 0% (1/306K)
    [OK] Highest usage of available connections: 9% (14/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/109.4M
    [OK] Key buffer hit rate: 99.9% (19M cached / 12K reads)
    [!!] Query cache is disabled
    [!!] Sorts requiring temporary tables: 36% (2K temp sorts / 6K sorts)
    [!!] Joins performed without indexes: 36
    [!!] Temporary tables created on disk: 27% (938 on disk / 3K total)
    [!!] Thread cache is disabled
    [OK] Table cache hit rate: 65% (400 open / 607 opened)
    [OK] Open file limit used: 13% (598/4K)
    [OK] Table locks acquired immediately: 99% (265K immediate / 265K locks)
    [OK] InnoDB data size / buffer pool: 1.2M/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
        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
    Variables to adjust:
        query_cache_size (>= 8M)
        sort_buffer_size (> 2M)
        read_rnd_buffer_size (> 256K)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        thread_cache_size (start at 4)
    
    We have 3 site and
    [B]Total Mysql Size : 2GB
    Total Hit : 8000[/B]
    
    We don't use innoDB and server configuration
    
    [B]CPU : Intel(R) Core(TM) i7-2600 CPU @ 3.40GHz
    RAM : 16GB
    OS : Centos 64[/B]
    Are you up or create my.cnf for us?

    Thank you.
     
    #1 tefon, Apr 25, 2013
    Last edited: Apr 25, 2013
  2. gopkris2005

    gopkris2005 Well-Known Member

    Joined:
    Jan 9, 2007
    Messages:
    61
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    INDIA
    cPanel Access Level:
    Root Administrator
    Twitter:
    Your can to optimize your my.cnf settings depending on your RAM Size. You have enabled innodb_file_per_table, this will cause database corruption in future.

    innodb_file_per_table=1
     
  3. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    nope, that only means that each innodb table gets its own file,
    It's quite good actually, especially for big tables


    @tefon, please add in my.cnf after
    [mysqld]
    innodb_file_per_table=1
    default-storage-engine=MyISAM
    open_files_limit=10000

    this:
    skip-name-resolve
    myisam_use_mmap=1

    join_buffer_size=4M
    read_buffer_size=256K
    sort_buffer_size=256K
    read_rnd_buffer_size=8M

    key_buffer_size = 1024M

    table_cache = 750
    thread_cache_size = 128

    query_cache_type = 1
    query_cache_size = 30M
    query_cache_limit = 1M

    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1

    mysql-slow.log will be now stored in /var/lib/mysql - main mysql folder

    remove those lines:
    [mysqld]
    log_slow_queries = /var/log/mysql-slow.log
    long_query_time = 1
     
Loading...

Share This Page