title: MySQL Tunner : High ratio of temporary tables created on disk - Website Slow response
body: <p>I'm using a dedicated server with bellow specification for a single website with joomla CMS that is News/blog based website:
  Intel(R) Xeon(R) CPU E5-2670 v2 @ 2.50GHz
  10 of Cores, 20 of Threads
  Ram: 60GB
  SSD:250 (OS and i think DB)
  HDD: 2TB</p>
  
  <p>PHP 7.3</p>
  
  <p>MySQL 5.7</p>
  
  <p>Website load time is very slow and i optimized page but the server response time is slow so i just installed Mysql-Tunner to check DB status. I just check the report but i don't know if there is something good or worst .
  Need your valuable and in-detail guide.</p>
  
  <pre><code> &gt;&gt;  MySQLTuner 1.7.15 - Major Hayden &lt;major@mhtx.net&gt;
   &gt;&gt;  Bug reports, feature requests, and downloads at http://mysqltuner.com/
   &gt;&gt;  Run with '--help' for additional options and output filtering
  [--] Skipped version check for MySQLTuner script
  [OK] Currently running supported MySQL version 5.7.27
  [OK] Operating on 64-bit architecture
  
  -------- Log file Recommendations ------------------------------------------------------------------
  [--] Log file: /var/log/mysqld.log(2M)
  [OK] Log file /var/log/mysqld.log exists
  [OK] Log file /var/log/mysqld.log is readable.
  [OK] Log file /var/log/mysqld.log is not empty
  [OK] Log file /var/log/mysqld.log is smaller than 32 Mb
  [!!] /var/log/mysqld.log contains 2231 warning(s).
  [!!] /var/log/mysqld.log contains 5891 error(s).
  
  -------- Storage Engine Statistics -----------------------------------------------------------------
  [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA 
  [--] Data in MyISAM tables: 431.8K (Tables: 60)
  [--] Data in InnoDB tables: 1.4G (Tables: 363)
  [--] Data in MEMORY tables: 0B (Tables: 8)
  [OK] Total fragmented tables: 0
  
  -------- Analysis Performance Metrics --------------------------------------------------------------
  [--] innodb_stats_on_metadata: OFF
  [OK] No stat updates during querying INFORMATION_SCHEMA.
  
  -------- CVE Security Recommendations --------------------------------------------------------------
  [OK] NO SECURITY CVE FOUND FOR YOUR VERSION
  
  -------- Performance Metrics     -----------------------------------------------------------------------
  [--] Up for: 12d 2h 14m 35s (153M q [146.876 qps], 4M conn, TX: 1441G, RX: 39G)
  [--] Reads / Writes: 92% / 8%
  [--] Binary logging is disabled
  [--] Physical Memory     : 55.5G
  [--] Max MySQL memory    : 8.2G
  [--] Other process memory: 0B
  [--] Total buffers: 8.1G global + 1.1M per thread (80 max threads)
  [--] P_S Max memory usage: 0B
  [--] Galera GCache Max memory usage: 0B
  [OK] Maximum reached memory usage: 8.1G (14.59% of installed RAM)
  [OK] Maximum possible memory usage: 8.2G (14.73% of installed RAM)
  [OK] Overall possible memory usage with other process is compatible with memory available
  [OK] Slow queries: 0% (0/153M)
  [OK] Highest usage of available connections: 12% (10/80)
  [OK] Aborted connections: 0.08%  (3729/4397307)
  [!!] Query cache may be disabled by default due to mutex contention.
  [!!] Query cache efficiency: 0.0% (0 cached / 119M selects)
  [OK] Query cache prunes per day: 0
  [OK] Sorts requiring temporary tables: 0% (23K temp sorts / 25M sorts)
  [!!] Joins performed without indexes: 52200
  [!!] Temporary tables created on disk: 92% (14M on disk / 15M total)
  [OK] Thread cache hit rate: 99% (10 created / 4M connections)
  [!!] Table cache hit rate: 1% (1K open / 101K opened)
  [OK] Open file limit used: 0% (11/10K)
  [OK] Table locks acquired immediately: 100% (107K immediate / 107K locks)
  
  -------- Performance schema ------------------------------------------------------------------------
  [--] Performance schema is disabled.
  [--] Memory used by P_S: 0B
  [--] Sys schema is installed.
  
  -------- ThreadPool Metrics ------------------------------------------------------------------------
  [--] ThreadPool stat is disabled.
  
  -------- MyISAM Metrics ----------------------------------------------------------------------------
  [!!] Key buffer used: 18.3% (1M used / 8M cache)
  [OK] Key buffer size / total MyISAM indexes: 8.0M/221.0K
  [OK] Read Key buffer hit rate: 99.2% (61K cached / 507 reads)
  [!!] Write Key buffer hit rate: 65.7% (105 cached / 69 writes)
  
  -------- InnoDB Metrics ----------------------------------------------------------------------------
  [--] InnoDB is enabled.
  [--] InnoDB Thread Concurrency: 0
  [OK] InnoDB File per table is activated
  [OK] InnoDB buffer pool / data size: 8.0G/1.4G
  [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (3.125 %): 128.0M * 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 &amp; Innodb_buffer_pool_instances
  [OK] InnoDB Read buffer efficiency: 100.00% (426722546739 hits/ 426722621278 total)
  [!!] InnoDB Write Log efficiency: 80.95% (42826081 hits/ 52905097 total)
  [OK] InnoDB log waits: 0.00% (0 waits / 10079016 writes) 
  
  [--] AriaDB is disabled.
  [--] TokuDB is disabled.
  [--] XtraDB is disabled.
  [--] Galera is disabled.
  
  -------- Replication Metrics -----------------------------------------------------------------------
  [--] Galera Synchronous replication: NO
  [--] No replication slave(s) for this server.
  [--] Binlog format: ROW
  [--] XA support enabled: ON
  [--] Semi synchronous replication Master: Not Activated
  [--] Semi synchronous replication Slave: Not Activated
  [--] This is a standalone server
  
  -------- Recommendations ---------------------------------------------------------------------------
  &lt;b&gt;General recommendations:&lt;/b&gt;
  Control warning line(s) into /var/log/mysqld.log file
  Control error line(s) into /var/log/mysqld.log file
  Adjust your join queries to always utilize indexes
  When making adjustments, make tmp_table_size/max_heap_table_size equal
  Reduce your SELECT DISTINCT queries which have no LIMIT clause
  Increase table_open_cache gradually to avoid file descriptor limits
  Read this before increasing table_open_cache over 64: 
  Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
  This is MyISAM only table_cache scalability problem, InnoDB not affected.
  See more details here: https://bugs.mysql.com/bug.php?id=49177
  This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
  Beware that open_files_limit (10000) variable 
  should be greater than table_open_cache (2000)
  Performance schema should be activated for better diagnostics
  Before changing innodb_log_file_size and/or innodb_log_files_in_group     read this:  
  &lt;b&gt;Variables to adjust:&lt;/b&gt;
  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; 64M)
  max_heap_table_size (&gt; 64M)
  table_open_cache (&gt; 2000)
  performance_schema = ON enable PFS
  innodb_log_file_size should be (=1G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
  </code></pre>
  
  <p>and my.cnf :</p>
  
  <pre><code>datadir=/var/lib/mysql
  socket=/var/lib/mysql/mysql.sock
  symbolic-links=0
  log-error=/var/log/mysqld.log
  pid-file=/var/run/mysqld/mysqld.pid  
  open_files_limit=10000
  default-storage-engine=InnoDB
  innodb_file_per_table=1                                                        
  max_allowed_packet=268435456
  wait_timeout = 18
  interactive_timeout=18
  thread_cache_size=10
  skip-name-resolve=1
  tmp_table_size=64M
  max_heap_table_size=64M
  innodb_buffer_pool_size=8G
  innodb_flush_method            = O_DIRECT
  innodb_log_files_in_group      = 2
  innodb_log_file_size           = 128M
  innodb_flush_log_at_trx_commit = 1
  </code></pre>