>> MySQLTuner 1.8.3 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.pl/
>> 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: [!!] Your MySQL version 10.1.48-MariaDB is EOL software! Upgrade soon!
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[!!] Log file doesn't exist
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 166.2M (Tables: 164)
[--] Data in InnoDB tables: 609.2M (Tables: 45)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'root'@'127.0.0.1' has user name as password.
[!!] User 'root'@'192.168.0.202' has user name as password.
[!!] User 'root'@'::1' has user name as password.
[!!] User 'root'@'localhost' 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: 124d 6h 0m 10s (373M q [34.793 qps], 13M conn, TX: 714G, RX: 50G)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Physical Memory : 7.3G
[--] Max MySQL memory : 3.8G
[--] Other process memory: 0B
[--] Total buffers: 425.0M global + 22.9M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.0G (14.19% of installed RAM)
[OK] Maximum possible memory usage: 3.8G (51.69% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (31/373M)
[OK] Highest usage of available connections: 18% (28/151)
[OK] Aborted connections: 0.00% (3/13209533)
[!!] 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% (0 temp sorts / 35M sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 0% (8K on disk / 1M total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 63% (258 hits / 404 requests)
[OK] table_definition_cache(400) is upper than number of tables(370)
[OK] Open file limit used: 6% (276/4K)
[OK] Table locks acquired immediately: 100% (165M immediate / 165M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 4 thread(s).
[--] Using default value is good enough for your version (10.1.48-MariaDB)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 27.9% (37M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/20.6M
[OK] Read Key buffer hit rate: 100.0% (1B cached / 12K reads)
[OK] Write Key buffer hit rate: 100.0% (20K cached / 20K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M/609.2M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (800 %): 512.0M * 2/128.0M should be equal to 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: 99.95% (101175208988 hits/ 101224816893 total)
[!!] InnoDB Write Log efficiency: 67.58% (1036738 hits/ 1534061 total)
[OK] InnoDB log waits: 0.00% (0 waits / 497323 writes)
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/0B
[OK] Aria pagecache hit rate: 99.8% (4M cached / 7K reads)
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: STATEMENT
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Set up a Secure Password for 'root'@'127.0.0.1' user: SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('secure_password');
Set up a Secure Password for 'root'@'192.168.0.202' user: SET PASSWORD FOR 'root'@'192.168.0.202' = PASSWORD('secure_password');
Set up a Secure Password for 'root'@'::1' user: SET PASSWORD FOR 'root'@'::1' = PASSWORD('secure_password');
Set up a Secure Password for 'root'@'localhost' user: SET PASSWORD FOR 'root'@'localhost' = PASSWORD('secure_password');
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Set thread_cache_size to 4 as a starting value
Performance schema should be activated for better diagnostics
Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for MariaDB
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
thread_cache_size (start at 4)
performance_schema = ON enable PFS
innodb_buffer_pool_size (>= 609.2M) if possible.
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances (=1)
