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 Optimization

Discussion in 'Workarounds and Optimization' started by ddshadow, Nov 18, 2011.

  1. ddshadow

    ddshadow Member

    Joined:
    Nov 18, 2011
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    hello!


    I run the MySQLTuner and woudl like you guys could give me some advise what can i do to Tuneup the mysqlservers.

    I would love to hear some tips,


    Thanks

    Shadow DD
     
  2. storminternet

    storminternet Well-Known Member

    Joined:
    Nov 2, 2011
    Messages:
    462
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    #2 storminternet, Nov 18, 2011
    Last edited: Nov 18, 2011
  3. ddshadow

    ddshadow Member

    Joined:
    Nov 18, 2011
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello!

    Am aware there are several similar THreads like this one, however eachserver is a diferent case, Othrewise we all would use a set of templates and go from there.

    The idea of this post is try get some help to eliminate the vairables that aren´t need, and modify/add some other that need be change to suit the server needs.
     
  4. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,446
    Likes Received:
    195
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    And those default files on your system right now are called:

    /usr/share/mysql/my-huge.cnf Large site, fully dedicated mysql server, 1-2GB RAM
    /usr/share/mysql/my-large.cnf Large site, mostly dedicated mysql server, ~512MB RAM
    /usr/share/mysql/my-medium.cnf Medium site, shared server running mysql, > 64MB RAM
    /usr/share/mysql/my-small.cnf Small site, shared server running mysql, < 64MB RAM


    And some of those variables are right here, from your OP above:

    Code:
    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    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
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
    *** MySQL's maximum memory usage is dangerously high ***
    *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 32M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_cache (> 512)
    innodb_buffer_pool_size (>= 33M)
    
    Also, we have a member on these forums who goes by the name eva2000 who, while not posting on this forum for this sort of thing, has gone to great lengths for years posting Optimization tips to this forum: vBulletin Forums - Server Configuration where you can surely read up on some great tweaks for your my.cnf
    I mention this particular forum as it's been a great asset to me personally for many years. :cool:

    At the very least, using google to search for one or all of those file names mentioned above should get you going in the proper direction I would think.

    I'm not sure what sort of tips more than that can be given, no one here knows anything about your system, how much RAM, what you use the system for etc by your posts so far.

    The idea of this post is to let you know that posting to the cPanel Community Forums can be helpful surely, but this topic has been covered here a great deal already as far as cPanel servers go thanks to cPanelTristan's efforts, and the Web is chock full of more fine grained tweaks you can use as well.

    Its very easy to post your details and wait for someone else to find the sweet spots for you. It's more fun and educational though, I think, digging for those sweet spots on your own with the existing data available to us all, already.

    HTH!
     
  5. eva2000

    eva2000 Well-Known Member

    Joined:
    Aug 14, 2001
    Messages:
    322
    Likes Received:
    10
    Trophy Points:
    18
    Location:
    Brisbane, Australia
    cPanel Access Level:
    Root Administrator
    Twitter:
    Thanks for the mention and glad I could help :)

    FYI, vB server config forum is private forum which only vB license holders have access to to assist vB customers, so not sure if ddshadow would be able to access it.

    Back on topic, mysqltuner.pl while nice is a bit dated or incorrect on some recommendations so don't treat it as the ultimate bible to follow. I just use it for basic stats.

    I modified mysqltuner.pl to add a bit more explanation to some recommendations and use it for my mysqlmymonlite.sh stats script too which combines other tools as well including mysqlreport etc.

    You can grab a copy of modified version via

    Code:
    wget [url]http://vbtechsupport.com/mysqltuner/mysqltuner.txt[/url] -O mysqltuner.pl
    chmod +x mysqltuner.pl
    sample output

    Code:
    [root@centos57a ~]# ./mysqltuner.pl
    
     >>  MySQLTuner 1.2.0 mod - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Modified by George Liu (eva2000) at http://vbtechsupport.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.2.9-MariaDB-mariadb102
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [!!] InnoDB is enabled but isn't being used
    [OK] Total fragmented tables: 0
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 7h 10m 4s (29 q [0.001 qps], 8 conn, TX: 52K, RX: 1K)
    [--] Reads / Writes: 100% / 0%
    [--] Total buffers: 776.0M global + 1.3M per thread (300 max threads)
    [OK] Maximum possible memory usage: 1.1G (57% of installed RAM)
    [OK] Slow queries: 0% (0/29)
    [OK] Highest usage of available connections: 0% (1/300)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/91.0K
    [!!] Query cache efficiency: 0.0% (0 cached / 13 selects)
    [OK] Query cache prunes per day: 0
    [OK] Temporary tables created on disk: 0% (0 on disk / 9 total)
    [OK] Thread cache hit rate: 87% (1 created / 8 connections)
    [OK] Table cache hit rate: 53% (8 open / 15 opened)
    [OK] Open file limit used: 0% (19/8K)
    [OK] Table locks acquired immediately: 100% (18 immediate / 18 locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Add skip-innodb to MySQL configuration to disable InnoDB
        MySQL started within last 24 hours - recommendations may be inaccurate
        Enable the slow query log to troubleshoot bad queries.
        Don't forget to disable slow query logging after troubleshooting
       - For MySQL 5.0 http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html
       - For MySQL 5.1 http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
       - For MySQL 5.5 http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html
       - For MySQL 5.6 http://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html
    
    Variables to adjust:
        query_cache_limit (> 512K, or use smaller result sets)
     
  6. storminternet

    storminternet Well-Known Member

    Joined:
    Nov 2, 2011
    Messages:
    462
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Hi eva2000,

    Thanks very much for your valuable contribution regarding MySQL tuning and optimization.
     
  7. ddshadow

    ddshadow Member

    Joined:
    Nov 18, 2011
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello!


    Thank you all for the Imput,

    I did some o the recomendations done by the mysqltuner.pl but my idea was try get some more imput as some of you have much more experience with mysql and the tuning chime up some ideas about what can be remove Tweaked or add.

    So if you can give me some extra sugestions am really gretefull.


    Btw, the server has 4 GB memory
     
Loading...

Share This Page