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 <[e-mail]> >>  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 '[e-mail]' 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 performance[!!] 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' = PASSWORD('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-sysVariables 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