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.

Need Help with mysql tweaking

Discussion in 'Workarounds and Optimization' started by bohra, May 22, 2014.

  1. bohra

    bohra Registered

    Joined:
    May 22, 2014
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    My current mysql config is

    Code:
    [mysqld]
    bind-address=127.0.0.1
    query_cache_type=1
    thread_cache_size=6
    innodb_file_per_table=1
    query_cache_size=24M
    tmp_table_size=256M
    open_files_limit=3186
    default-storage-engine=MyISAM
    max_heap_table_size="256M)"
    query_cache_limit=20M
    query_cache_size=20M
    key_buffer=500M
    my server specifications are below.

    Operating system - CentOs
    Hard Drive - 480 GB
    mEMORY - 32GB DDR3
    Processor - 3.5ghz Quad Core E3 Bridge Xeon
    64Bit


    Running a Wordpress site with with W3 Total cache, the site is high trafficked. i am experiencing a load increase of about 0.1 in a daily basis.. wanted to tweak my mysql to keep my server load to the lowest..

    Please provide optimum mysql config

    - - - Updated - - -

    Sorry forgot to mention that I am experiencing an increase in load of the Mysql CPU % of about 0.1 on a daily basis.

    Any help is appreciated, thanks
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  3. bohra

    bohra Registered

    Joined:
    May 22, 2014
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    i have run both the reports the mysqlmonlites mysql tuner and the full mysqlmymonlite script


    below is the output of the tuner

    Code:
     >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major[/url]
     >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.6.16
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 398M (Tables: 102)
    [--] Data in InnoDB tables: 1M (Tables: 72)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
    [!!] Total fragmented tables: 17
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 7h 57m 58s (1M q [49.860 qps], 107K conn, TX: 22B, RX: 147M)
    [--] Reads / Writes: 98% / 2%
    [--] Total buffers: 920.0M global + 1.1M per thread (151 max threads)
    [OK] Maximum possible memory usage: 1.1G (3% of installed RAM)
    [OK] Slow queries: 0% (0/1M)
    [OK] Highest usage of available connections: 11% (18/151)
    [OK] Key buffer size / total MyISAM indexes: 500.0M/69.6M
    [OK] Key buffer hit rate: 99.9% (1M cached / 1K reads)
    [OK] Query cache efficiency: 72.1% (642K cached / 891K selects)
    [!!] Query cache prunes per day: 159462
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 42K sorts)
    [!!] Temporary tables created on disk: 46% (6K on disk / 14K total)
    [OK] Thread cache hit rate: 99% (878 created / 107K connections)
    [OK] Table cache hit rate: 97% (283 open / 290 opened)
    [OK] Open file limit used: 6% (280/4K)
    [OK] Table locks acquired immediately: 99% (106K immediate / 106K locks)
    [OK] InnoDB buffer pool / data size: 128.0M/1.1M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        query_cache_size (> 20M)
    
    
    also attached is the complete report
     

    Attached Files:

    #3 bohra, May 23, 2014
    Last edited by a moderator: May 23, 2014
  4. cPanelJared

    cPanelJared Technical Analyst
    Staff Member

    Joined:
    Feb 25, 2010
    Messages:
    1,842
    Likes Received:
    18
    Trophy Points:
    38
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    Please use CODE tags, instead of QUOTE tags, when pasting output from the shell into a forum message. The reason this is helpful is it uses a fixed-width font, just like the terminal, so it is a more accurate representation of what you see in your SSH session.

    A shortcut button for the CODE tags is available if you click "Go Advanced" while you compose your reply. The button looks like a pound sign, #.
     
  5. bohra

    bohra Registered

    Joined:
    May 22, 2014
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    yes i by mistakenly clicked the wrong button.. please help me in optimization
     
Loading...

Share This Page