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 Tuner suggestions

Discussion in 'Workarounds and Optimization' started by norival1992, May 23, 2016.

  1. norival1992

    norival1992 Registered

    Joined:
    Feb 18, 2013
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Code:
    root@domain [~]# perl mysqltuner.pl
    >>  MySQLTuner 1.6.12 - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at MySQLTuner-perl by major
    >>  Run with '--help' for additional options and output filtering
    
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.49-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -----------------------------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
    [--] Data in MyISAM tables: 476M (Tables: 84)
    [--] Data in InnoDB tables: 912K (Tables: 15)
    [!!] Total fragmented tables: 2
    
    -------- Security Recommendations ------------------------------------------------------------------
    [OK] There are no anonymous accounts for any database users
    [OK] All database users have passwords assigned
    [!!] There is no basic password file list!
    
    -------- CVE Security Recommendations --------------------------------------------------------------
    [--] Skipped due to --cvefile option undefined
    
    -------- Performance Metrics -----------------------------------------------------------------------
    [--] Up for: 18s (1K q [70.611 qps], 43 conn, TX: 19M, RX: 8M)
    [--] Reads / Writes: 84% / 16%
    [--] Binary logging is disabled
    [--] Physical Memory     : 40.2G
    [--] Max MySQL memory    : 5.9G
    [--] Other process memory: 2.9G
    [--] Total buffers: 408.0M global + 2.8M per thread (2048 max threads)
    [--] P_S Max memory usage: 0B
    [--] Galera GCache Max memory usage: 0B
    [OK] Maximum reached memory usage: 421.8M (1.03% of installed RAM)
    [OK] Maximum possible memory usage: 5.9G (14.68% of installed RAM)
    [OK] Overall possible memory usage with other process is compatible with memory available
    [OK] Slow queries: 0% (0/1K)
    [OK] Highest usage of available connections: 0% (5/2048)
    [OK] Aborted connections: 0.00%  (0/43)
    [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
    [OK] Sorts requiring temporary tables: 0% (2 temp sorts / 240 sorts)
    [OK] No joins without indexes
    [!!] Temporary tables created on disk: 61% (58 on disk / 95 total)
    [OK] Table cache hit rate: 88% (52 open / 59 opened)
    [OK] Open file limit used: 0% (64/10K)
    [OK] Table locks acquired immediately: 99% (1K immediate / 1K locks)
    
    -------- ThreadPool Metrics ------------------------------------------------------------------------
    [--] ThreadPool stat is disabled.
    
    -------- Performance schema ------------------------------------------------------------------------
    [--] Performance schema is disabled.
    
    -------- MyISAM Metrics ----------------------------------------------------------------------------
    [!!] Key buffer used: 48.7% (4M used / 8M cache)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/66.7M
    [OK] Read Key buffer hit rate: 99.5% (459K cached / 2K reads)
    [!!] Write Key buffer hit rate: 18.9% (762 cached / 618 writes)
    
    -------- AriaDB Metrics ----------------------------------------------------------------------------
    [--] AriaDB is disabled.
    
    -------- InnoDB Metrics ----------------------------------------------------------------------------
    [--] InnoDB is enabled.
    [OK] InnoDB buffer pool / data size: 128.0M/912.0K
    [OK] InnoDB buffer pool instances: 1
    [!!] InnoDB Used buffer: 4.57% (374 used/ 8192 total)
    [OK] InnoDB Read buffer efficiency: 95.83% (8587 hits/ 8961 total)
    [!!] InnoDB Write Log efficiency: 0% (1 hits/ 0 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 1 writes)
    
    -------- TokuDB Metrics ----------------------------------------------------------------------------
    [--] TokuDB is disabled.
    
    -------- Galera Metrics ----------------------------------------------------------------------------
    [--] Galera is disabled.
    
    -------- Replication Metrics -----------------------------------------------------------------------
    [--] Galera Synchronous replication: NO
    [--] No replication slave(s) for this server.
    [--] This is a standalone server.
    
    -------- 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
    
    

    This my log. And here is my my.cnf

    Code:
    [mysqld]
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    max_allowed_packet=268435456
    open_files_limit=10000
    max_connections = 2048
        query_cache_type =0
        tmp_table_size = 256M
        max_heap_table_size = 256M
        table_open_cache = 600
    log_slow_queries = /var/log/mysql/mysql-slow.log
    
    Looks like there is many [!!] here. Could anyone help me?
     
    #1 norival1992, May 23, 2016
    Last edited by a moderator: May 24, 2016
  2. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,453
    Likes Received:
    195
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    What are you looking for exactly, instructions on how to interpret the results of MySQL Tuner?

     
    norival1992 likes this.
  3. norival1992

    norival1992 Registered

    Joined:
    Feb 18, 2013
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    I think [!!] is not good, because it is warning with ! word.

    I want to optimze with general recommendations

    -------- 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
     
  4. tank

    tank Well-Known Member

    Joined:
    Apr 12, 2011
    Messages:
    236
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Chicago, IL
    cPanel Access Level:
    Root Administrator
    Just do what it says.

    Wait more than 24 hours. Often times it is recommended to wait at least 48 hours.

    I would suggest.

    tmp_table_size= 128M

    Found this explanation:
    Not much you can do now. Mysql only performs as well as how your queries are develop. What mysqltuner is saying is that you have too many queries without limits.

    MySQL optimize help on Xeon
     
  5. norival1992

    norival1992 Registered

    Joined:
    Feb 18, 2013
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    I think i should wait for 24 up to 48 hours to get right results from tuner... Thanks for you help.
     
Loading...

Share This Page