title: Optimal setting for mysql (mariadb optimization 10.5) in my.cnf (Centos 8)
body: <p>Here are my mysqltuner results:</p>
  
  <pre><code> [--] Skipped version check for MySQLTuner script
      [!!] Successfully authenticated with no password - SECURITY RISK!
      [!!] Your MySQL version 10.5.0-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 InnoDB tables: 4.4G (Tables: 227)
      [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: 50m 57s (911K q [298.114 qps], 2K conn, TX: 3G, RX: 104M)
      [--] Reads / Writes: 99% / 1%
      [--] Binary logging is disabled
      [--] Physical Memory     : 31.2G
      [--] Max MySQL memory    : 3.2G
      [--] Other process memory: 0B
      [--] Total buffers: 417.0M global + 18.9M per thread (151 max threads)
      [--] P_S Max memory usage: 0B
      [--] Galera GCache Max memory usage: 0B
      [OK] Maximum reached memory usage: 568.3M (1.78% of installed RAM)
      [OK] Maximum possible memory usage: 3.2G (10.24% of installed RAM)
      [OK] Overall possible memory usage with other process is compatible with memory available
      [OK] Slow queries: 0% (0/911K)
      [OK] Highest usage of available connections: 5% (8/151)
      [OK] Aborted connections: 0.00%  (0/2906)
      [!!] 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 / 888K selects)
      [OK] Query cache prunes per day: 0
      [OK] Sorts requiring temporary tables: 0% (286 temp sorts / 50K sorts)
      [!!] Joins performed without indexes: 18
      [!!] Temporary tables created on disk: 81% (15K on disk / 18K total)
      [OK] Thread cache hit rate: 99% (8 created / 2K connections)
      [OK] Table cache hit rate: 93% (93 open / 99 opened)
      [OK] table_definition_cache(400) is upper than number of tables(391)
      [OK] Open file limit used: 0% (28/4K)
      [OK] Table locks acquired immediately: 100% (143 immediate / 143 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: 32 thread(s).
      [--] Using default value is good enough for your version (10.5.0-MariaDB)
  
      -------- MyISAM Metrics ----------------------------------------------------------------------------
      [!!] Key buffer used: 18.2% (24M used / 134M cache)
      [!!] Cannot calculate MyISAM index size - re-run script as root user
  
      -------- InnoDB Metrics ----------------------------------------------------------------------------
      [--] InnoDB is enabled.
      [--] InnoDB Thread Concurrency: 0
      [OK] InnoDB File per table is activated
      [!!] InnoDB buffer pool / data size: 128.0M/4.4G
      [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 96.0M * 1/128.0M should be equal to 25%
      [OK] InnoDB buffer pool instances: 1
      [--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
      [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size &amp; Innodb_buffer_pool_instances
      [OK] InnoDB Read buffer efficiency: 95.00% (920105182 hits/ 968548737 total)
      [!!] InnoDB Write Log efficiency: 33.6% (1776 hits/ 5285 total)
      [OK] InnoDB log waits: 0.00% (0 waits / 7061 writes)
  
      -------- AriaDB Metrics ----------------------------------------------------------------------------
      [--] AriaDB is enabled.
      [OK] Aria pagecache size / total Aria indexes: 128.0M/320.0K
      [OK] Aria pagecache hit rate: 98.6% (936K cached / 13K 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:
      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/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: 
  
  Variables to adjust:
      query_cache_size (=0)
      query_cache_type (=0)
      query_cache_limit (&gt; 1M, or use smaller result sets)
      join_buffer_size (&gt; 256.0K, or always use indexes with JOINs)
      tmp_table_size (&gt; 16M)
      max_heap_table_size (&gt; 16M)
      performance_schema = ON enable PFS
      innodb_buffer_pool_size (&gt;= 4.4G) if possible.
      innodb_log_file_size should be (=32M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
  </code></pre>
  
  <p>Who can optimize this what should i do ? to make things faster ?</p>
  
  <p>I want to optimise for better CPU usage.</p>
  
  <p>Server Specs:
  16 core 32 threads
  32 GB RAM</p>
  
  <p>Need a optimal configuration for my mysql settings</p>