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.

Help please in optimizing my.cnf

Discussion in 'Workarounds and Optimization' started by filth80, Jul 25, 2012.

  1. filth80

    filth80 Well-Known Member

    Joined:
    Dec 11, 2009
    Messages:
    89
    Likes Received:
    0
    Trophy Points:
    6
    Hello, i need desperate help in optimizing my.cnf. I tried different solutions for some days but no success. I post here as I have nowhere else to ask for help.

    Here is my problem server configuration:

    Total processors: 8 - 8 GB RAM

    Vendor
    GenuineIntel

    Name
    Intel(R) Xeon(R) CPU E5504 @ 2.00GHz

    Speed
    1600.000 MHz

    Cache
    4096 KB

    Here is my.cnf:

    Code:
    [mysqld]
    table_cache=256
    innodb_file_per_table=1
    max_connections=200
    query_cache_size=15M
    local-infile=0
    sort_buffer_size=3M
    thread_cache_size=4
    read_rnd_buffer_size=312
    log-slow-queries="/var/lib/mysql/slow.log"
    join_buffer_size=10M
    default-storage-engine=MyISAM
    log-queries-not-using-indexes
    mysqlth.png

    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.24-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 485M (Tables: 2547)
    [--] Data in InnoDB tables: 1M (Tables: 58)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 0B (Tables: 3)
    [!!] Total fragmented tables: 99
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 48m 0s (61K q [21.203 qps], 3K conn, TX: 529M, RX: 18M)
    [--] Reads / Writes: 71% / 29%
    [--] Total buffers: 183.0M global + 13.4M per thread (200 max threads)
    [OK] Maximum possible memory usage: 2.8G (35% of installed RAM)
    [OK] Slow queries: 5% (3K/61K)
    [OK] Highest usage of available connections: 4% (8/200)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/233.1M
    [OK] Key buffer hit rate: 100.0% (62M cached / 12K reads)
    [OK] Query cache efficiency: 50.3% (19K cached / 38K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 7% (230 temp sorts / 3K sorts)
    [!!] Joins performed without indexes: 677
    [OK] Temporary tables created on disk: 13% (415 on disk / 2K total)
    [OK] Thread cache hit rate: 99% (11 created / 3K connections)
    [!!] Table cache hit rate: 7% (256 open / 3K opened)
    [OK] Open file limit used: 48% (500/1K)
    [OK] Table locks acquired immediately: 99% (37K immediate / 37K locks)
    [OK] InnoDB data size / buffer pool: 1.0M/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
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        join_buffer_size (> 10.0M, or always use indexes with joins)
        table_cache (> 256)
    

    Please give me a my.cnf file based on these specifications. Yes I know I should wait 24 hours, etc. Reffering to join_buffer_size if I increase it, it eants more, and so on, until i receive the "ram memory" warning.
    I ask here for help, as i am out of solutions.

    P.S Sometiomes the value of "mysql" process increases even tu 150%.

    Thanks alot for your help.
     
  2. filth80

    filth80 Well-Known Member

    Joined:
    Dec 11, 2009
    Messages:
    89
    Likes Received:
    0
    Trophy Points:
    6
    Here is my.cnf after 24 hours
    >> 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.24-cll
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 499M (Tables: 2603)
    [--] Data in InnoDB tables: 1M (Tables: 57)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 0B (Tables: 3)
    [!!] Total fragmented tables: 78

    -------- Security Recommendations -------------------------------------------
    [OK] All database users have passwords assigned

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 1h 54m 17s (1M q [18.296 qps], 108K conn, TX: 9B, RX: 316M)
    [--] Reads / Writes: 72% / 28%
    [--] Total buffers: 418.0M global + 2.6M per thread (151 max threads)
    [OK] Maximum possible memory usage: 814.4M (10% of installed RAM)
    [OK] Slow queries: 0% (30/1M)
    [OK] Highest usage of available connections: 7% (11/151)
    [OK] Key buffer size / total MyISAM indexes: 250.0M/236.5M
    [OK] Key buffer hit rate: 100.0% (1B cached / 143K reads)
    [OK] Query cache efficiency: 53.2% (553K cached / 1M selects)
    [!!] Query cache prunes per day: 55025
    [OK] Sorts requiring temporary tables: 5% (3K temp sorts / 63K sorts)
    [!!] Joins performed without indexes: 14318
    [OK] Temporary tables created on disk: 11% (8K on disk / 70K total)
    [OK] Thread cache hit rate: 99% (245 created / 108K connections)
    [!!] Table cache hit rate: 2% (500 open / 21K opened)
    [OK] Open file limit used: 23% (964/4K)
    [OK] Table locks acquired immediately: 99% (895K immediate / 896K locks)
    [OK] InnoDB data size / buffer pool: 1.0M/128.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
    query_cache_size (> 8M)
    join_buffer_size (> 384B, or always use indexes with joins)
    table_cache (> 500)
     
  3. SB-Nick

    SB-Nick Well-Known Member

    Joined:
    Aug 26, 2008
    Messages:
    134
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Ask your developer (or hire one) to add indexes for your large database tables and run a mysqlcheck --optimize for the fragmented tables.
     
Loading...

Share This Page