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.cnf optimization request :)

Discussion in 'Workarounds and Optimization' started by rabee2006, Jan 6, 2013.

  1. rabee2006

    rabee2006 Registered

    Joined:
    Dec 9, 2012
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi,
    i have a big problem with my mysql 5.5 load is up to 90% of cpu and i need some one to help me optimize my.cnf file

    here is my VPS information:


    Code:
    Processor Information
    Total processors: 3
    
    Processor #1
    
        Vendor
            GenuineIntel
    
        Name
            Intel(R) Xeon(R) CPU E5506 @ 2.13GHz
    
        Speed
            2133.408 MHz
    
        Cache
            4096 KB
    
    Processor #2
    
        Vendor
            GenuineIntel
    
        Name
            Intel(R) Xeon(R) CPU E5506 @ 2.13GHz
    
        Speed
            2133.408 MHz
    
        Cache
            4096 KB
    
    Processor #3
    
        Vendor
            GenuineIntel
    
        Name
            Intel(R) Xeon(R) CPU E5506 @ 2.13GHz
    
        Speed
            2133.408 MHz
    
        Cache
            4096 KB
    
    
    Memory Information
    
    Memory: 4776936k/4907008k available (2577k kernel code, 121408k reserved, 1492k data, 208k init)
    
    
    System Information
    
    Linux 2.6.18-408.8.2.el5.lve0.8.61.3xen #1 SMP Wed Jul 11 06:58:44 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux
    
    
    Physical Disks
    
    
    
    Current Memory Usage
    
                 total       used       free     shared    buffers     cached
    Mem:       4898816    3222228    1676588          0     194456    2097112
    -/+ buffers/cache:     930660    3968156
    Swap:      1048568          0    1048568
    Total:     5947384    3222228    2725156
    
    
    Current Disk Usage
    
    Filesystem            Size  Used Avail Use% Mounted on
    /dev/sda1             123G   72G   45G  62% /
    tmpfs                 2.4G     0  2.4G   0% /dev/shm
    /usr/tmpDSK           4.0G  154M  3.6G   5% /tmp
    


    my.cnf right now is:

    Code:
    [mysqld]
    log-slow-queries
    max_connections=500
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    query_cache_size = 50M

    any one can help me?

    thanks and best regards
     
  2. SB-Nick

    SB-Nick Well-Known Member

    Joined:
    Aug 26, 2008
    Messages:
    134
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Hi,

    The my.cnf file isnt optimized at all.
    Try downloading MySQLTuner from rackerhacker and try to adjust the config based on the settings provided by the script.
     
  3. rabee2006

    rabee2006 Registered

    Joined:
    Dec 9, 2012
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    thanks a lot for the replay, here is the result of mysqltuner what should i write at the my.cnf ?

    Code:
    perl mysqltuner.pl
    perl: warning: Setting locale failed.
    perl: warning: Please check that your locale settings:
    	LANGUAGE = (unset),
    	LC_ALL = (unset),
    	LC_CTYPE = "UTF-8",
    	LANG = "en_US.utf-8"
        are supported and installed on your system.
    perl: warning: Falling back to the standard locale ("C").
    
     >>  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.28-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 3G (Tables: 8131)
    [--] Data in InnoDB tables: 12M (Tables: 180)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 1M (Tables: 35)
    [!!] Total fragmented tables: 809
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 14d 20h 33m 21s (117M q [91.261 qps], 1M conn, TX: 584B, RX: 17B)
    [--] Reads / Writes: 97% / 3%
    [--] Total buffers: 168.0M global + 2.8M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.5G (32% of installed RAM)
    [OK] Slow queries: 0% (1K/117M)
    [!!] Highest connection usage: 100%  (501/500)
    [!!] Key buffer size / total MyISAM indexes: 8.0M/615.5M
    [!!] Key buffer hit rate: 86.0% (298B cached / 41B reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (60K temp sorts / 16M sorts)
    [!!] Joins performed without indexes: 192849
    [!!] Temporary tables created on disk: 35% (7M on disk / 20M total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (400 open / 6M opened)
    [OK] Open file limit used: 26% (662/2K)
    [OK] Table locks acquired immediately: 99% (141M immediate / 141M locks)
    [OK] InnoDB data size / buffer pool: 12.6M/128.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce or eliminate persistent connections to reduce connection usage
        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:
        max_connections (> 500)
        wait_timeout (< 28800)
        interactive_timeout (< 28800)
        key_buffer_size (> 615.5M)
        query_cache_size (>= 8M)
        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)
        table_cache (> 400)
    
    thanks for your help
     
  4. SB-Nick

    SB-Nick Well-Known Member

    Joined:
    Aug 26, 2008
    Messages:
    134
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Hey,

    The Recommendation are pretty much self explanatory, try by adding the following settings to /etc/my.cnf, restart the mysql daemon after,

    thread_cache_size=4
    wait_timeout = 60
    interactive_timeout = 60
    key_buffer_size = 256M
    table_cache=1024
    query_cache_limit = 1M
    query_cache_size = 32M
    query_cache_type = 1
    join_buffer_size=2M
    tmp_table_size=128M
    sort_buffer=2M

    - Start a "screen" over SSH and as root run "mysqlcheck --optimize -A"
    - Go to Home ┬╗Server Status ┬╗Service Status and see if you are using Swap.

    I would also hire a developer and pass these recommendations so he can tune up your website code,

    - Reduce or eliminate persistent connections to reduce connection usage
    - Adjust your join queries to always utilize indexes
    - Reduce your SELECT DISTINCT queries without LIMIT clauses
     
Loading...

Share This Page