Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- >> MySQLTuner 1.7.9 - 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] Currently running supported MySQL version 10.1.26-MariaDB-0+deb9u1
- [OK] Operating on 64-bit architecture
- -------- Log file Recommendations ------------------------------------------------------------------
- [--] Log file: /var/log/mysql/error.log(8K)
- [OK] Log file /var/log/mysql/error.log exists
- [OK] Log file /var/log/mysql/error.log is readable.
- [OK] Log file /var/log/mysql/error.log is not empty
- [OK] Log file /var/log/mysql/error.log is smaller than 32 Mb
- [!!] /var/log/mysql/error.log contains 68 warning(s).
- [OK] /var/log/mysql/error.log doesn't contain any error.
- [--] 0 start(s) detected in /var/log/mysql/error.log
- [--] 0 shutdown(s) detected in /var/log/mysql/error.log
- -------- Storage Engine Statistics -----------------------------------------------------------------
- [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
- [--] Data in InnoDB tables: 32.8G (Tables: 666)
- [--] Data in MEMORY tables: 6.3M (Tables: 3)
- [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 'shopping-fuchs24@%' 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: 7d 22h 54m 59s (62M q [91.078 qps], 303K conn, TX: 784G, RX: 19G)
- [--] Reads / Writes: 95% / 5%
- [--] Binary logging is disabled
- [--] Physical Memory : 62.9G
- [--] Max MySQL memory : 38.1G
- [--] Other process memory: 676.3M
- [--] Total buffers: 35.2G global + 19.2M per thread (151 max threads)
- [--] P_S Max memory usage: 92M
- [--] Galera GCache Max memory usage: 0B
- [OK] Maximum reached memory usage: 36.4G (57.82% of installed RAM)
- [OK] Maximum possible memory usage: 38.1G (60.62% of installed RAM)
- [OK] Overall possible memory usage with other process is compatible with memory available
- [OK] Slow queries: 0% (0/62M)
- [OK] Highest usage of available connections: 37% (57/151)
- [!!] Aborted connections: 4.32% (13117/303717)
- [!!] 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% (142 temp sorts / 19M sorts)
- [!!] Temporary tables created on disk: 92% (17M on disk / 19M total)
- [OK] Thread cache hit rate: 98% (3K created / 303K connections)
- [OK] Table cache hit rate: 99% (2K open / 2K opened)
- [OK] Open file limit used: 0% (84/16K)
- [OK] Table locks acquired immediately: 99% (121M immediate / 121M locks)
- -------- Performance schema ------------------------------------------------------------------------
- [--] Memory used by P_S: 92.7M
- [--] Sys schema isn't installed.
- -------- ThreadPool Metrics ------------------------------------------------------------------------
- [--] ThreadPool stat is enabled.
- [--] Thread Pool Size: 24 thread(s).
- [--] Using default value is good enough for your version (10.1.26-MariaDB-0+deb9u1)
- -------- MyISAM Metrics ----------------------------------------------------------------------------
- [!!] Key buffer used: 19.1% (1B used / 5B cache)
- [OK] Key buffer size / total MyISAM indexes: 5.0G/123.0K
- [OK] Read Key buffer hit rate: 97.9% (1K cached / 25 reads)
- -------- InnoDB Metrics ----------------------------------------------------------------------------
- [--] InnoDB is enabled.
- [--] InnoDB Thread Concurrency: 0
- [OK] InnoDB File per table is activated
- [!!] InnoDB buffer pool / data size: 30.0G/32.8G
- [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (6.66666666666667 %): 1.0G * 2/30.0G should be equal 25%
- [!!] InnoDB buffer pool instances: 15
- [--] InnoDB Buffer Pool Chunk Size not used or defined in your version
- [OK] InnoDB Read buffer efficiency: 100.00% (411255144335 hits/ 411255887458 total)
- [!!] InnoDB Write Log efficiency: 85.63% (11243174 hits/ 13130255 total)
- [OK] InnoDB log waits: 0.00% (0 waits / 1887081 writes)
- -------- AriaDB Metrics ----------------------------------------------------------------------------
- [--] AriaDB is enabled.
- [OK] Aria pagecache size / total Aria indexes: 128.0M/1B
- [!!] Aria pagecache hit rate: 94.0% (278M cached / 16M reads)
- -------- 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.
- [--] Binlog format: STATEMENT
- [--] ON
- [--] Semi synchronous replication Master: Not Activated
- [--] Semi synchronous replication Slave: Not Activated
- [--] This is a standalone server
- -------- Recommendations ---------------------------------------------------------------------------
- General recommendations:
- Control warning line(s) into /var/log/mysql/error.log file
- Restrict Host for user@% to user@SpecificDNSorIp
- Reduce or eliminate unclosed connections and network issues
- 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
- When making adjustments, make tmp_table_size/max_heap_table_size equal
- Reduce your SELECT DISTINCT queries which have no LIMIT clause
- Consider installing Sys schema from https://github.com/mysql/mysql-sys
- Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: http://bit.ly/2wgkDvS
- Variables to adjust:
- join_buffer_size (> 8.0M, or always use indexes with JOINs)
- tmp_table_size (> 64M)
- max_heap_table_size (> 64M)
- innodb_buffer_pool_size (>= 32.8G) if possible.
- innodb_log_file_size should be (=3G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
- innodb_buffer_pool_instances(=30)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement