Mysql Tuner suggestions


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?

Infopro

May 24, 2016 09:09

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

norival1992

May 25, 2016 04:28

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

tank

May 29, 2016 16:59

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

May 29, 2016 17:02

I think i should wait for 24 up to 48 hours to get right results from tuner... Thanks for you help.

Mysql Tuner suggestions

Comments

Didn't find what you were looking for?