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 my.cnf tuning

Discussion in 'Workarounds and Optimization' started by c4carab, Mar 31, 2013.

  1. c4carab

    c4carab Registered

    Joined:
    Mar 31, 2013
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello,

    Can any one recommend best my.cnf for CENTOS 6.4 x86_64 / ntel(R) Xeon(R) CPU E31230 @ 3.20GHz / 8gb ram

    was like that :
    Code:
    [mysqld]
    innodb_file_per_table=1
    innodb_buffer_pool_size=5G
    key_buffer_size = 128M
    max_allowed_packet=100M
    max_connections=400
    max_heap_table_size=256M
    open_files_limit=5000
    query_cache_size=4M
    query_cache_size = 512M
    join_buffer_size=128M
    table_cache = 8195
    thread_cache_size=8
    tmp_table_size=256M
    log-slow-queries=/var/lib/mysql/slow.log
    

    im change it to be like that :
    Code:
    [mysqld]
    max_connections=200
    low_priority_updates=1
    myisam-recover=backup,force
    thread_concurrency=8
    concurrent_insert=2
    thread_cache_size=32
    max_allowed_packet=8M
    
    innodb_buffer_pool_size=512M
    innodb_additional_mem_pool_size=10M
    innodb_flush_method=O_DIRECT
    
    interactive_timeout = 100
    connect_timeout = 60
    wait_timeout = 120
    
    tmp_table_size = 256M
    max_heap_table_size = 256M
    join_buffer_size=32M
    table_cache =2200
    read_buffer_size=4M
    sort_buffer_size=2M
    read_rnd_buffer_size=2M
    key_buffer_size=128M
    max_allowed_packet=8M
    max_connect_errors=10
    myisam_sort_buffer_size=64M
    query_cache_limit=3M
    query_cache_size=64M
    query_cache_type=1
    
    [mysqldump]
    quick
    max_allowed_packet=16M
    
    site run slow now with socialengine v4 and mysqltuner.pl give this
    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.68-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 127M (Tables: 120)
    [--] Data in InnoDB tables: 143M (Tables: 397)
    [--] Data in MEMORY tables: 0B (Tables: 2)
    [!!] Total fragmented tables: 32
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 3m 7s (32K q [172.364 qps], 743 conn, TX: 36M, RX: 6M)
    [--] Reads / Writes: 71% / 29%
    [--] Total buffers: 971.0M global + 40.2M per thread (200 max threads)
    [!!] Maximum possible memory usage: 8.8G (114% of installed RAM)
    [OK] Slow queries: 0% (0/32K)
    [OK] Highest usage of available connections: 7% (15/200)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/22.3M
    [OK] Key buffer hit rate: 98.7% (4K cached / 67 reads)
    [OK] Query cache efficiency: 74.4% (17K cached / 23K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 575 sorts)
    [!!] Joins performed without indexes: 70
    [!!] Temporary tables created on disk: 37% (331 on disk / 880 total)
    [OK] Thread cache hit rate: 97% (15 created / 743 connections)
    [OK] Table cache hit rate: 97% (252 open / 259 opened)
    [OK] Open file limit used: 0% (37/4K)
    [OK] Table locks acquired immediately: 99% (9K immediate / 9K locks)
    [OK] InnoDB data size / buffer pool: 143.4M/512.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce your overall MySQL memory footprint for system stability
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        join_buffer_size (> 32.0M, or always use indexes with joins)
    
    any help suggestion
     
  2. 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
    "Up for: 3m 7s"

    You really should allow 24 hours before running the script to get a more accurate recommendation. Post the results here again now assuming MySQL hasn't been restarted since opening this topic.
     
  3. caisc

    caisc Active Member

    Joined:
    Oct 5, 2011
    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Yes i agree with @HVH - George, you shud allow sometime to judge the performance after making changes to cnf file.
     
  4. chrismfz

    chrismfz Well-Known Member

    Joined:
    Jul 4, 2007
    Messages:
    109
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Greece
    cPanel Access Level:
    DataCenter Provider
    join_buffer_size=32M should be 1M, max 2.

    No matter how much you going to increase it, mysqltuner will say you need more.
    But that's not a problem.
    That's why it says:
    [!!] Maximum possible memory usage: 8.8G (114% of installed RAM)
    It calculates all buffers x number of connections. It shouldn't be so high.

    You have 3 times the same var, max_allowed_packet=8M
    and max_allowed_packet=16M and max_allowed_packet=8M
    again...

    Remove them, and maybe you need to increase this to something bigger for example 32M
    It depends if for example from a forum you upload a picture and it's larger than 8M.
    Not a problem but still, "clean" my.cnf a little.

    You should also lower these
    read_buffer_size=4M
    to 2M as well.

    It's not "the bigger the better" here :D

    And all these to something much smaller
    interactive_timeout = 100
    connect_timeout = 60
    wait_timeout = 120

    for example start with 20-30 seconds to all.


    THEN let it run at least one full day and re run mysqltuner.
    Again, ignore the join_buffer_size no matter what it says.
    Paste the rest of them again.


    You can always compile apache with xcache to cache queries and pages and see a difference.
    You shouldn't have issues with RAM. Got enough.
     
Loading...

Share This Page