Skip to main content

Mysql Tuner suggestions

norival1992

root@domain [~]# perl mysqltuner.pl >> MySQLTuner 1.6.12 - Major Hayden >> 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
[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?

Comments

4 comments

Date Votes
  • Infopro
    Could anyone help me?

    What are you looking for exactly, instructions on how to interpret the results of MySQL Tuner?
    [--] Up for: 18s (1K q [70.611 qps], 43 conn, TX: 19M, RX: 8M)

    -------- 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
    0
  • norival1992
    What are you looking for exactly, instructions on how to interpret the results of MySQL Tuner?

    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
    0
  • tank
    Just do what it says.
    MySQL started within last 24 hours - recommendations may be inaccurate

    Wait more than 24 hours. Often times it is recommended to wait at least 48 hours.
    Temporary table size is already large - reduce result set size

    tmp_table_size = 256M

    I would suggest. tmp_table_size= 128M
    Reduce your SELECT DISTINCT queries without LIMIT clauses

    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
    0
  • norival1992
    I think i should wait for 24 up to 48 hours to get right results from tuner... Thanks for you help.
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post