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 Optimize Help

Discussion in 'Workarounds and Optimization' started by saamxvr, Jan 18, 2014.

  1. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    Hello ,

    i need help to oprimiz my MySQL

    this is my server info :


    this is my currant my.cnf

    this is my tuner report



    Code:
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]major(7) - a social nerd - Linux man page[/url]
     >>  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.34-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 588M (Tables: 91)
    [--] Data in InnoDB tables: 1M (Tables: 89)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 2
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 15m 15s (19K q [21.802 qps], 319 conn, TX: 979M, RX: 4M)
    [--] Reads / Writes: 90% / 10%
    [--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
    [OK] Maximum possible memory usage: 583.2M (3% of installed RAM)
    [OK] Slow queries: 0% (0/19K)
    [OK] Highest usage of available connections: 2% (4/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/102.3M
    [OK] Key buffer hit rate: 99.5% (1M cached / 8K reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 4% (136 temp sorts / 2K sorts)
    [!!] Temporary tables created on disk: 30% (425 on disk / 1K total)
    [!!] Thread cache is disabled
    [OK] Table cache hit rate: 96% (226 open / 233 opened)
    [OK] Open file limit used: 22% (235/1K)
    [OK] Table locks acquired immediately: 99% (21K immediate / 21K locks)
    [OK] InnoDB data size / buffer pool: 1.8M/128.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Enable the slow query log to troubleshoot bad queries
        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
    Variables to adjust:
        query_cache_size (>= 8M)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        thread_cache_size (start at 4)




    Waiting for the suggest :
     
  2. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    add

    key_buffer_size = 150M
    query_cache_limit = 1M
    query_cache_size = 30M
    query_cache_type = 1
    table_open_cache = 1000
    thread_cache_size = 16
    tmp_table_size = 50M
    max_heap_table_size = 50M

    slow_query_log_file=mysql-slow.log
    long_query_time=0.1

    and later restart

    after several hours, or best 24h, run mysqltuner.pl again and post your result
     
  3. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    Thanks you .. and this is result


    Code:
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]major(7) - a social nerd - Linux man page[/url]
     >>  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.34-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 1G (Tables: 351)
    [--] Data in InnoDB tables: 2M (Tables: 86)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 60
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 3h 27m 33s (9M q [95.266 qps], 97K conn, TX: 129B, RX: 3B)
    [--] Reads / Writes: 89% / 11%
    [--] Total buffers: 374.0M global + 2.8M per thread (151 max threads)
    [OK] Maximum possible memory usage: 789.2M (4% of installed RAM)
    [OK] Slow queries: 0% (4K/9M)
    [OK] Highest usage of available connections: 78% (119/151)
    [OK] Key buffer size / total MyISAM indexes: 150.0M/387.7M
    [OK] Key buffer hit rate: 100.0% (1B cached / 478K reads)
    [OK] Query cache efficiency: 21.3% (1M cached / 8M selects)
    [!!] Query cache prunes per day: 1052942
    [OK] Sorts requiring temporary tables: 2% (17K temp sorts / 870K sorts)
    [!!] Temporary tables created on disk: 39% (297K on disk / 751K total)
    [OK] Thread cache hit rate: 99% (137 created / 97K connections)
    [OK] Table cache hit rate: 48% (667 open / 1K opened)
    [OK] Open file limit used: 43% (935/2K)
    [OK] Table locks acquired immediately: 99% (8M immediate / 8M locks)
    [OK] InnoDB data size / buffer pool: 2.5M/128.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        query_cache_size (> 30M)
        tmp_table_size (> 50M)
        max_heap_table_size (> 50M)
    
     
  4. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Much better, but since

    [!!] Temporary tables created on disk: 39% (297K on disk / 751K total)

    you should optimize your queries (especially the ones using temporary tables on disk)
    query can can be also increased
    query_cache_size = 70M

    generate review of current slow queries
    mkdir -p /root/install
    cd /root/install
    wget http://percona.com/get/pt-query-digest
    chmod +x pt-query-digest
    ./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt
    and upload slow.txt here in code brackets
     
  5. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    I am happy to see the optimization recommendations you received on this thread have been helpful so far. Feel free to let us know the outcome after the most recent suggestions.

    Thank you.
     
  6. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    Hello ,

    Thanks you Michael , thinkbot , i have fallow last suggest , and its take me forever to load slow.txt .. its not come out put ... any idea ?

    Thank you
     
  7. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    the generation of slow.txt can take a while, but during that you will receive percentage info of how much is finished
    just slow.txt file is not big, so it should not be a problem uploading it here
     
  8. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    This is my screen still like this last 1hours ... see the attachment .
     

    Attached Files:

  9. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Can you run du -sh /var/lib/mysql/mysql-slow.log
     
  10. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    I getting this error :

    root@srv [~]# du -sh /var/lib/mysql/mysql-slow.log
    du: cannot access `/var/lib/mysql/mysql-slow.log': No such file or directory
    root@srv [~]#
     
  11. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    Hello i didnt get any reply latly - however i get new result

    Code:
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]major(7) - a social nerd - Linux man page[/url]
     >>  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.34-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 3G (Tables: 694)
    [--] Data in InnoDB tables: 4M (Tables: 103)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 115
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 15m 38s (332K q [354.747 qps], 3K conn, TX: 1B, RX: 103M)
    [--] Reads / Writes: 86% / 14%
    [--] Total buffers: 414.0M global + 2.8M per thread (151 max threads)
    [OK] Maximum possible memory usage: 829.2M (5% of installed RAM)
    [OK] Slow queries: 0% (421/332K)
    [OK] Highest usage of available connections: 8% (13/151)
    [OK] Key buffer size / total MyISAM indexes: 150.0M/1.0G
    [OK] Key buffer hit rate: 99.9% (155M cached / 128K reads)
    [!!] Query cache efficiency: 18.6% (52K cached / 281K selects)
    [!!] Query cache prunes per day: 862249
    [OK] Sorts requiring temporary tables: 0% (164 temp sorts / 21K sorts)
    [!!] Temporary tables created on disk: 36% (4K on disk / 13K total)
    [OK] Thread cache hit rate: 99% (13 created / 3K connections)
    [OK] Table cache hit rate: 98% (402 open / 409 opened)
    [OK] Open file limit used: 14% (699/4K)
    [OK] Table locks acquired immediately: 99% (284K immediate / 284K locks)
    [OK] InnoDB data size / buffer pool: 4.5M/128.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Enable the slow query log to troubleshoot bad queries
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        query_cache_limit (> 1M, or use smaller result sets)
        query_cache_size (> 70M)
        tmp_table_size (> 50M)
        max_heap_table_size (> 50M)
    
    Any suggest ?
     
  12. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Its very good, but please generate slow log again

    ./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt
    before your slow log didnt exist
     
  13. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    im still getting

    root@srv [~]# ./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt
    -bash: ./pt-query-digest: No such file or directory


    any idea ?
     
  14. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1


    ohh forgot this one
    slow_query_log=1

    please add that and restart
     
  15. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    OK when im type this command i get this result

    root@srv [~]# du -sh /var/lib/mysql/mysql-slow.log
    21M /var/lib/mysql/mysql-slow.log

    but when im run this command

    root@srv [~]# ./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt
    -bash: ./pt-query-digest: No such file or directory

    any idea ?
     
  16. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    you previosuly installed pt-query-digest in /root/install

    cou first
    cd /root/install
    then
    ./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt
     
  17. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
  18. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    yep, now open /root/install/slow.txt and copy content here
     
  19. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    Humm ok but its big list .. how i copy all from ssh ?
     
  20. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    use sftp or scp to copy the file to PC,
    filezilla supports it
     
Loading...

Share This Page