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 5.1.X Optimization

Discussion in 'Workarounds and Optimization' started by Makento, May 1, 2014.

  1. Makento

    Makento Registered

    Joined:
    May 1, 2014
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi,
    Centos 6.4
    Memory: 1.25GB
    Long query time = 10

    MYSQLTUNER
    Code:
     >>  MySQLTuner 1.3.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
    [OK] Currently running supported MySQL version 5.1.73
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +CSV +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 135M (Tables: 499)
    [--] Data in InnoDB tables: 857M (Tables: 1264)
    [!!] Total fragmented tables: 304
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 21d 19h 27m 34s (12M q [6.801 qps], 521K conn, TX: 70B, RX: 5B)
    [--] Reads / Writes: 50% / 50%
    [--] Total buffers: 421.0M global + 2.7M per thread (100 max threads)
    [OK] Maximum possible memory usage: 696.0M (54% of installed RAM)
    [OK] Slow queries: 0% (174/12M)
    [OK] Highest usage of available connections: 33% (33/100)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/28.4M
    [OK] Key buffer hit rate: 99.8% (28M cached / 56K reads)
    [OK] Query cache efficiency: 79.9% (6M cached / 8M selects)
    [!!] Query cache prunes per day: 2159
    [OK] Sorts requiring temporary tables: 0% (319 temp sorts / 163K sorts)
    [!!] Joins performed without indexes: 17612
    [!!] Temporary tables created on disk: 32% (92K on disk / 280K total)
    [OK] Thread cache hit rate: 99% (5K created / 521K connections)
    [!!] Table cache hit rate: 0% (400 open / 92K opened)
    [OK] Open file limit used: 1% (81/4K)
    [OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
    [!!] InnoDB  buffer pool / data size: 128.0M/857.5M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        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
        Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
    Variables to adjust:
        query_cache_size (> 96M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 64M)
        max_heap_table_size (> 64M)
        table_cache (> 400)
        innodb_buffer_pool_size (>= 857M)
    
    much appreciated.
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,814
    Likes Received:
    672
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    Thank you posting the output from the MySQL tuner. In addition to that, are you able to provide details about any particular issues you are having with MySQL? Or, are you just seeking to improve the overall speed/performance? Note that most of the advice on the "Optimization" forum will come from other users.

    Thank you.
     
  3. server10

    server10 Registered

    Joined:
    May 1, 2014
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I'd suggest you :

    1- Increase the value of tmp_table_size to a greater value.
    2- Increase the value of table_open_cache and open_files_limit variables, have in mind that you might need to change max open file size for mysql user.
    /http://duntuk.com/how-raise-ulimit-open-files-and-mysql-openfileslimit
     
  4. Makento

    Makento Registered

    Joined:
    May 1, 2014
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi,
    we running a drupal & civicrm website. Experiencing poor page loads hence looking to speed up. Thanks
     
  5. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    2 basics in your case that will make huge difference:


    table_cache = 2048
    innodb_buffer_pool_size = 900M

    and modify
    query_cache_size = 50M

    then restart
     
Loading...

Share This Page