MySQLTuner工具--效果

在阿里云博客看到一款性能统计工具，实验了一把，感觉有几项可以关注下。

https://yq.aliyun.com/articles/52879?utm_content=m_14134

关注：

1、性能统计Performance Metrics

     可以看到物理内存大小，及MySQL最大内存大小，其他进程占用内存大以及其他的几个地方占用内存大小。

     还可以看到内存使用峰值。

     感觉这项对监控MySQL状态有很大帮助。下节看下源码，看他这些内存统计得到的值来源哪里。

[mysql26@localhost ~]$ ./tun --user root --socket /home/mysql26/bin/mysql.sock
 >>  MySQLTuner 1.7.2 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 5.6.26-debug
[OK] Operating on 32-bit architecture with less than 2GB RAM
 
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: (0B)
[!!] Log file  doesn't exist
[!!] Log file  isn't readable.
 
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA 
[--] Data in InnoDB tables: 368K (Tables: 9)
[OK] Total fragmented tables: 0
 
-------- Security Recommendations ------------------------------------------------------------------
[!!] User '@localhost' is an anonymous account.
[!!] User '@localhost.localdomain' is an anonymous account.
[!!] User '@localhost' has no password set.
[!!] User '@localhost.localdomain' has no password set.
[!!] User 'root@127.0.0.1' has no password set.
[!!] User 'root@::1' has no password set.
[!!] User 'root@localhost' has no password set.
[!!] User 'root@localhost.localdomain' has no password set.
[!!] User '@localhost' has user name as password.
[!!] User '@localhost.localdomain' has user name as password.
[!!] There is no basic password file list!
 
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
 
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 15d 1h 19m 25s (540 q [0.000 qps], 161 conn, TX: 624K, RX: 44K)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is disabled
[--] Physical Memory     : 1010.8M
[--] Max MySQL memory    : 400.1M
[--] Other process memory: 338.9M
[--] Total buffers: 169.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 70M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 241.8M (23.92% of installed RAM)
[OK] Maximum possible memory usage: 400.1M (39.58% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (4/540)
[OK] Highest usage of available connections: 1% (2/151)
[OK] Aborted connections: 0.00%  (0/161)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performanc
e
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 227 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 10 sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 0% (0 on disk / 100 total)
[OK] Thread cache hit rate: 98% (2 created / 161 connections)
[OK] Table cache hit rate: 54% (89 open / 164 opened)
[OK] Open file limit used: 4% (46/1K)
[OK] Table locks acquired immediately: 100% (106 immediate / 106 locks)
 
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 70.6M
[--] Sys schema isn't installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 11.5% (964K used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/105.0K
[OK] Read Key buffer hit rate: 96.0% (25 cached / 1 reads)
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/368.0K
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
[!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instances(!=1).
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 92.84% (3840 hits/ 4136 total)
[!!] InnoDB Write Log efficiency: 20.51% (8 hits/ 39 total)
[OK] InnoDB log waits: 0.00% (0 waits / 31 writes)
 
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
 
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
 
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
 
-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.
 
-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.
 
-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect 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:
    Remove Anonymous User accounts - there are 2 anonymous accounts.
    Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWO
RD('secure_password'); )
    Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password')
; )
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=64M) if possible.
    innodb_buffer_pool_instances (=1)

[END] 2017/5/29 16:59:30