[root@db2 ~]# ./1.pl >> MySQLTuner 1.7.9 - Major Hayden >> 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 Please enter your MySQL administrative login: root Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.6.33-log [OK] Operating on 64-bit architecture -------- Log file Recommendations ------------------------------------------------------------------ [--] Log file: /var/log/mysqld.log(51K) [OK] Log file /var/log/mysqld.log exists [OK] Log file /var/log/mysqld.log is readable. [OK] Log file /var/log/mysqld.log is not empty [OK] Log file /var/log/mysqld.log is smaller than 32 Mb [!!] /var/log/mysqld.log contains 234 warning(s). [!!] /var/log/mysqld.log contains 11 error(s). [--] 5 start(s) detected in /var/log/mysqld.log [--] 1) 2018-07-05 02:15:44 1601 [Note] /usr/sbin/mysqld: ready for connections. [--] 2) 2018-07-05 01:37:56 23535 [Note] /usr/sbin/mysqld: ready for connections. [--] 3) 2018-02-08 11:25:09 48359 [Note] /usr/sbin/mysqld: ready for connections. [--] 4) 2018-02-08 11:01:24 24651 [Note] /usr/sbin/mysqld: ready for connections. [--] 5) 2018-02-08 10:11:42 47125 [Note] /usr/sbin/mysqld: ready for connections. [--] 4 shutdown(s) detected in /var/log/mysqld.log [--] 1) 2018-07-05 01:43:24 23535 [Note] /usr/sbin/mysqld: Shutdown complete [--] 2) 2018-04-26 01:49:31 48359 [Note] /usr/sbin/mysqld: Shutdown complete [--] 3) 2018-02-08 11:19:37 24651 [Note] /usr/sbin/mysqld: Shutdown complete [--] 4) 2018-02-08 10:23:30 47125 [Note] /usr/sbin/mysqld: Shutdown complete -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA [--] Data in MyISAM tables: 96.5G (Tables: 3562) [--] Data in InnoDB tables: 5.6G (Tables: 455) [--] Data in MEMORY tables: 0B (Tables: 2) [OK] Total fragmented tables: 0 -------- Security Recommendations ------------------------------------------------------------------ [OK] There are no anonymous accounts for any database users [OK] All database users have passwords assigned [!!] User 'training@%' has user name as password. [!!] User 'training@%' does not specify hostname restrictions. [!!] There is no basic password file list! -------- CVE Security Recommendations -------------------------------------------------------------- [--] Skipped due to --cvefile option undefined -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 15d 2h 55m 25s (548M q [419.538 qps], 17M conn, TX: 308G, RX: 94G) [--] Reads / Writes: 60% / 40% [--] Binary logging is enabled (GTID MODE: OFF) [--] Physical Memory : 15.3G [--] Max MySQL memory : 6.6G [--] Other process memory: 857.7M [--] Total buffers: 5.6G global + 1.1M per thread (550 max threads) [--] P_S Max memory usage: 468M [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 6.6G (43.08% of installed RAM) [OK] Maximum possible memory usage: 6.6G (43.44% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (94/548M) [!!] Highest connection usage: 91% (501/550) [OK] Aborted connections: 0.02% (3656/17346835) [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines. [OK] Sorts requiring temporary tables: 0% (469 temp sorts / 3M sorts) [!!] Joins performed without indexes: 1816173 [OK] Temporary tables created on disk: 0% (735K on disk / 161M total) [OK] Thread cache hit rate: 99% (20K created / 17M connections) [!!] Table cache hit rate: 0% (2K open / 14M opened) [OK] Open file limit used: 29% (2K/10K) [OK] Table locks acquired immediately: 98% (655M immediate / 666M locks) [OK] Binlog cache memory access: 100.00% (12132475 Memory / 12132475 Total) -------- Performance schema ------------------------------------------------------------------------ [--] Memory used by P_S: 468.3M [--] Sys schema is installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is disabled. -------- MyISAM Metrics ---------------------------------------------------------------------------- [OK] Key buffer used: 100.0% (8M used / 8M cache) [OK] Key buffer size / total MyISAM indexes: 8.0M/27.0G [OK] Read Key buffer hit rate: 96.7% (5B cached / 182M reads) [!!] Write Key buffer hit rate: 45.0% (907M cached / 408M writes) -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 96 [OK] InnoDB File per table is activated [!!] InnoDB buffer pool / data size: 5.0G/5.6G [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (120 %): 3.0G * 2/5.0G should be equal 25% [OK] InnoDB buffer pool instances: 5 [--] InnoDB Buffer Pool Chunk Size not used or defined in your version [OK] InnoDB Read buffer efficiency: 99.99% (4574449427 hits/ 4574787520 total) [OK] InnoDB Write log efficiency: 91.08% (7132406 hits/ 7831323 total) [OK] InnoDB log waits: 0.00% (0 waits / 698917 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 [--] This server is acting as master for 1 server(s). [--] Binlog format: MIXED [--] ON [--] Semi synchronous replication Master: Not Activated [--] Semi synchronous replication Slave: Not Activated [!!] This replication slave is running with the read_only option disabled. [OK] This replication slave is up to date with master. -------- Recommendations --------------------------------------------------------------------------- General recommendations: Control warning line(s) into /var/log/mysqld.log file Control error line(s) into /var/log/mysqld.log file Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); ) Restrict Host for user@% to user@SpecificDNSorIp Reduce or eliminate persistent connections to reduce connection usage Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1 Adjust your join queries to always utilize indexes Increase table_open_cache gradually to avoid file descriptor limits Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C This is MyISAM only table_cache scalability problem, InnoDB not affected. See more details here: https://bugs.mysql.com/bug.php?id=49177 This bug already fixed in MySQL 5.7.9 and newer MySQL versions. Beware that open_files_limit (10000) variable should be greater than table_open_cache (2000) Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: http://bit.ly/2wgkDvS Variables to adjust: max_connections (> 550) wait_timeout (< 28800) interactive_timeout (< 28800) join_buffer_size (> 256.0K, or always use indexes with JOINs) table_open_cache (> 2000) innodb_buffer_pool_size (>= 5.6G) if possible. innodb_log_file_size should be (=640M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.