mysql-sys VS mariadb-sys

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

[root@MIG-10373 ~]# perl mysqltuner.pl >> MySQLTuner 2.0.12 * Jean-Marie Renouard * Major Hayden >> 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 [!!] Successfully authenticated with no password - SECURITY RISK! [OK] Currently running supported MySQL version 10.5.11-MariaDB [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: 3.2G (Tables: 229) [--] Data in InnoDB tables: 7.5G (Tables: 363) [!!] Total fragmented tables: 1 -------- Analysis Performance Metrics -------------------------------------------------------------- [--] innodb_stats_on_metadata: OFF [OK] No stat updates during querying INFORMATION_SCHEMA. -------- Views Metrics ----------------------------------------------------------------------------- -------- Triggers Metrics -------------------------------------------------------------------------- -------- Routines Metrics -------------------------------------------------------------------------- -------- Security Recommendations ------------------------------------------------------------------ [OK] There are no anonymous accounts for any database users [OK] All database users have passwords assigned [!!] There is no basic password file list! -------- CVE Security Recommendations -------------------------------------------------------------- [OK] NO SECURITY CVE FOUND FOR YOUR VERSION -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 1h 23m 55s (3M q [692.745 qps], 27K conn, TX: 13G, RX: 480M) [--] Reads / Writes: 98% / 2% [--] Binary logging is disabled [--] Physical Memory : 15.5G [--] Max MySQL memory : 7.1G [--] Other process memory: 0B [--] Total buffers: 4.3G global + 18.8M per thread (151 max threads) [--] Performance_schema Max memory usage: 0B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 4.9G (31.64% of installed RAM) [OK] Maximum possible memory usage: 7.1G (45.48% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (5/3M) [OK] Highest usage of available connections: 22% (34/151) [OK] Aborted connections: 0.07% (18/27088) [!!] Name resolution is active: a reverse name resolution is made for each new connection which 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 / 408K sorts) [!!] Joins performed without indexes: 180 [!!] Temporary tables created on disk: 47% (37K on disk / 78K total) [OK] Thread cache hit rate: 99% (34 created / 27K connections) [OK] Table cache hit rate: 92% (3M hits / 3M requests) [!!] table_definition_cache (400) is less than number of tables (783) [OK] Open file limit used: 1% (590/32K) [OK] Table locks acquired immediately: 99% (3M immediate / 3M locks) -------- Performance schema ------------------------------------------------------------------------ [!!] Performance_schema should be activated. [--] Sys schema is not installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is disabled. -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 66.2% (84.7M used / 128.0M cache) [OK] Key buffer size / total MyISAM indexes: 128.0M/1.2G [OK] Read Key buffer hit rate: 99.9% (59M cached / 62K reads) [OK] Write Key buffer hit rate: 99.5% (67K cached / 67K writes) -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [!!] InnoDB buffer pool / data size: 4.0G / 7.5G [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (1.5625%): 64.0M * 1 / 4.0G should be equal to 25% [--] Number of InnoDB Buffer Pool Chunk: 32 for 1 Buffer Pool Instance(s) [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances [OK] InnoDB Read buffer efficiency: 99.97% (268509802 hits / 268598865 total) [!!] InnoDB Write Log efficiency: 1617.6% (36299 hits / 2244 total) [OK] InnoDB log waits: 0.00% (0 waits / 38543 writes) -------- Aria Metrics ------------------------------------------------------------------------------ [--] Aria Storage Engine is enabled. [OK] Aria pagecache size / total Aria indexes: 128.0M/336.0K [!!] Aria pagecache hit rate: 93.3% (315K cached / 21K 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: MIXED [--] XA support enabled: ON [--] Semi synchronous replication Master: OFF [--] Semi synchronous replication Slave: OFF [--] This is a standalone server -------- Recommendations --------------------------------------------------------------------------- General recommendations: Run ALTER TABLE ... FORCE or OPTIMIZE TABLE to defragment tables for better performance OPTIMIZE TABLE `c2stickdb`.`wp_options`; -- can free 74.958984375 MiB Total freed space after defragmentation : 74.958984375 MiB MySQL was started within the last 24 hours: recommendations may be inaccurate Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1 We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found. See https://dev.mysql.com/doc/internals/en/join-buffer-size.html (specially the conclusions at the bottom of the page). When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries which have no LIMIT clause Performance schema should be activated for better diagnostics 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: skip-name-resolve=1 join_buffer_size (> 256.0K, or always use indexes with JOINs) tmp_table_size (> 16M) max_heap_table_size (> 16M) table_definition_cache (400) > 783 or -1 (autosizing if supported) performance_schema=ON key_buffer_size (~ 88M) innodb_buffer_pool_size (>= 7.5G) if possible. innodb_log_file_size should be (=1G) if possible, so InnoDB total log files size equals 25% of buffer pool size.

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