I have tried your recommendation:
It is recommending a memory of around 267GB! Am I missing something?
>> MySQLTuner 1.7.4 - Major Hayden <[email protected]>
>> 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] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 10.1.23-MariaDB
[OK] Operating on 64-bit architecture
...
...
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in InnoDB tables: 287G (Tables: 114)
[!!] Total fragmented tables: 3
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'readuser@%' hasn't specific host restriction.
[!!] User 'slave_user@%' hasn't specific host restriction.
[!!] User 'zabbix@%' hasn't specific host restriction.
[--] There are 612 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 10h 32m 14s (99M q [2K qps], 113K conn, TX: 107G, RX: 7G)
[--] Reads / Writes: 70% / 30%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory : 29.3G
[--] Max MySQL memory : 24.4G
[--] Other process memory: 201.3M
[--] Total buffers: 22.4G global + 3.8M per thread (550 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 24.0G (81.88% of installed RAM)
[OK] Maximum possible memory usage: 24.4G (83.24% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/99M)
[OK] Highest usage of available connections: 80% (442/550)
[OK] Aborted connections: 0.00% (0/113037)
[!!] 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 / 43M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (649 temp sorts / 60M sorts)
[!!] Joins performed without indexes: 17418
[OK] Temporary tables created on disk: 14% (184K on disk / 1M total)
[OK] Thread cache hit rate: 99% (442 created / 113K connections)
[OK] Table cache hit rate: 98% (340 open / 346 opened)
[OK] Open file limit used: 0% (63/16K)
[OK] Table locks acquired immediately: 100% (111M immediate / 111M locks)
[OK] Binlog cache memory access: 99.96% (17641632 Memory / 17648288 Total)
-------- 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.23-MariaDB)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (6M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/130.0K
[!!] Read Key buffer hit rate: 50.0% (8 cached / 4 reads)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 22.0G/287.3G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (4.54545454545455 %): 512.0M * 2/22.0G should be equal 25%
[!!] InnoDB buffer pool instances: 8
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (17306370030 hits/ 17306523210 total)
[!!] InnoDB Write Log efficiency: 29.56% (7956374 hits/ 26918821 total)
[OK] InnoDB log waits: 0.00% (0 waits / 34875195 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 100.0% (489M cached / 10K 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
[--] This server is acting as master for 1 server(s).
[--] No replication setup for this server.
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/log/mariadb/mariadb.log file
Run OPTIMIZE TABLE to defragment tables for better performance
OPTIMIZE TABLE `zabbix`.`events`; -- can free 1978 MB
OPTIMIZE TABLE `zabbix`.`history_log`; -- can free 87 MB
OPTIMIZE TABLE `zabbix`.`history_text`; -- can free 129 MB
Total freed space after theses OPTIMIZE TABLE : 2194 Mb
Restrict Host for user@% to user@SpecificDNSorIp
MySQL started within last 24 hours - recommendations may be inaccurate
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
Performance should be activated for better diagnostics
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Read this before changing innodb_log_file_size and/or innodb_log_files_in_group: http://bit.ly/2wgkDvS
Variables to adjust:
long_query_time (<= 10)
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
join_buffer_size (> 1.0M, or always use indexes with joins)
performance_schema = ON enable PFS
innodb_buffer_pool_size (>= 287G) if possible.
innodb_log_file_size should be (=2G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances(=22)