Maria DB Optimization ( for WordPress)

Hi all hoping someone can help we have a huge server for just a couple of sites but are not getting the performance we would expect (the site is a big one) we've run mySQL tuner and would love some input. The server we are on has 256 GB ram and SSD drives 10 core / 20 thread CPU Intel(R) Xeon(R) CPU E5-2640 v4 @ 2.40GHz Also running litespeed and LSCACHE [CODE=bash] >> MySQLTuner 1.7.19 - 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 [OK] Currently running supported MySQL version 10.3.23-MariaDB-log [OK] Operating on 64-bit architecture -------- Log file Recommendations ------------------------------------------------------------------ [OK] Log file /var/lib/mysql/magi.xxxxxxxx.com.err exists [--] Log file: /var/lib/mysql/magi.xxxxxxxx.com.err(1M) [OK] Log file /var/lib/mysql/magi.xxxxxxxx.com.err is readable. [OK] Log file /var/lib/mysql/magi.xxxxxxxx.com.err is not empty [OK] Log file /var/lib/mysql/magi.xxxxxxxx.com.err is smaller than 32 Mb [!!] /var/lib/mysql/magi.xxxxxxxx.com.err contains 7929 warning(s). [!!] /var/lib/mysql/magi.xxxxxxxx.com.err contains 5880 error(s). [--] 38 start(s) detected in /var/lib/mysql/magi.xxxxxxxx.com.err [--] 1) 2020-06-21 20:36:52 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 2) 2020-06-15 22:25:05 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 3) 2020-05-29 21:23:24 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 4) 2020-05-13 21:23:16 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 5) 2020-05-13 21:23:06 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 6) 2020-04-30 21:27:24 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 7) 2020-01-29 21:22:30 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 8) 2020-01-29 21:22:17 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 9) 2019-12-18 21:22:04 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 10) 2019-12-12 21:22:21 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 37 shutdown(s) detected in /var/lib/mysql/magi.xxxxxxxx.com.err [--] 1) 2020-06-21 20:36:51 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 2) 2020-06-15 22:25:04 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 3) 2020-05-29 21:23:23 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 4) 2020-05-13 21:23:14 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 5) 2020-05-13 21:23:04 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 6) 2020-04-30 21:27:22 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 7) 2020-01-29 21:22:28 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 8) 2020-01-29 21:22:16 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 9) 2019-12-18 21:22:02 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 10) 2019-12-12 21:22:20 0 [Note] /usr/sbin/mysqld: Shutdown complete -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE [--] Data in MyISAM tables: 272.3M (Tables: 105) [--] Data in InnoDB tables: 9.7G (Tables: 548) [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 [!!] There is no basic password file list! -------- CVE Security Recommendations -------------------------------------------------------------- [--] Skipped due to --cvefile option undefined -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 9d 0h 2m 51s (2B q [3K qps], 906K conn, TX: 2373G, RX: 624G) [--] Reads / Writes: 98% / 2% [--] Binary logging is disabled [--] Physical Memory : 251.6G [--] Max MySQL memory : 73.1G [--] Other process memory: 0B [--] Total buffers: 8.4G global + 258.9M per thread (256 max threads) [--] P_S Max memory usage: 0B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 23.3G (9.26% of installed RAM) [OK] Maximum possible memory usage: 73.1G (29.06% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (2K/2B) [OK] Highest usage of available connections: 23% (59/256) [OK] Aborted connections: 0.05% (421/906181) [!!] 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 / 2B selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (745 temp sorts / 2B sorts) [!!] Joins performed without indexes: 21052 [!!] Temporary tables created on disk: 72% (8M on disk / 11M total) [OK] Thread cache hit rate: 99% (59 created / 906K connections) [OK] Table cache hit rate: 38% (874 open / 2K opened) [!!] table_definition_cache(400) is lower than number of tables(918) [OK] Open file limit used: 0% (264/40K) [OK] Table locks acquired immediately: 99% (9M immediate / 9M locks) -------- Performance schema ------------------------------------------------------------------------ [--] Performance schema is disabled. [--] Memory used by P_S: 0B [--] Sys schema is installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is enabled. [--] Thread Pool Size: 40 thread(s). [--] Using default value is good enough for your version (10.3.23-MariaDB-log) -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 31.5% (42M used / 134M cache) [OK] Key buffer size / total MyISAM indexes: 128.0M/91.7M [OK] Read Key buffer hit rate: 100.0% (318M cached / 74K reads) [!!] Write Key buffer hit rate: 67.9% (4M cached / 3M writes) -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [!!] InnoDB buffer pool / data size: 8.0G/9.7G [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (50 %): 2.0G * 2/8.0G should be equal to 25% [OK] InnoDB buffer pool instances: 8 [--] Number of InnoDB Buffer Pool Chunk : 64 for 8 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: 100.00% (501040406306 hits/ 501040918568 total) [OK] InnoDB Write log efficiency: 99.63% (209546395 hits/ 210315550 total) [OK] InnoDB log waits: 0.00% (0 waits / 769155 writes) -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is enabled. [OK] Aria pagecache size / total Aria indexes: 128.0M/1B [OK] Aria pagecache hit rate: 98.4% (754M cached / 12M 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: Control warning line(s) into /var/lib/mysql/magi.mysticmedusa.com.err file Control error line(s) into /var/lib/mysql/magi.mysticmedusa.com.err file 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 Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU Variables to adjust: query_cache_size (=0) query_cache_type (=0) query_cache_limit (> 1M, or use smaller result sets) join_buffer_size (> 256.0K, or always use indexes with JOINs) tmp_table_size (> 128M) max_heap_table_size (> 128M) table_definition_cache(400) > 918 or -1 (autosizing if supported) performance_schema = ON enable PFS innodb_buffer_pool_size (>= 9.7G) if possible. innodb_log_file_size should be (=1G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.