title: mysqltuner recommendation suggestion
body: <p>Question: whenever i run <code>mysqltuner</code> it always complain about three components even i change their values to high but still showing need more, they are following, I have both table <code>MyISAM</code> and <code>InnoDB</code> and we do join etc also but i heard it is not good to set <code>join_buffer_size</code> value higher, following is detail of box, please suggest if my box anything more to tune?  </p>
  
  <pre><code>Variables to adjust:
      query_cache_size (&gt; 128M)
      join_buffer_size (&gt; 4.0M, or always use indexes with joins)
      table_cache (&gt; 512)
  </code></pre>
  
  <p><strong>Hardware:</strong> <code>40 CPUs</code> and <code>64 GB Memory</code> </p>
  
  <p><strong>MySQL:</strong> <code>mysql-5.1.73-3.el6_5.x86_64</code></p>
  
  <p><strong>my.cnf</strong> </p>
  
  <pre><code>[mysqld]
  datadir=/var/lib/mysql
  socket=/var/lib/mysql/mysql.sock
  user=mysql
  symbolic-links=0
  bind-address    = 0.0.0.0
  
  max_connections = 1000
  
  
  max_allowed_packet=32M
  join_buffer_size=4M
  thread_cache_size=8
  
  query_cache_limit=4M
  query_cache_size=128M
  thread_cache_size=4
  table_cache=512
  key_buffer_size=2G
  
  innodb-buffer-pool-size = 16G
  open-files-limit = 35535
  sort_buffer_size = 6M
  query-cache-type = 1
  
  tmp_table_size=1G
  innodb_log_buffer_size=1G
  
  server-id=1
  binlog-format   = mixed
  log-bin=mysql-bin
  datadir=/var/lib/mysql
  innodb_flush_log_at_trx_commit=1
  sync_binlog=1
  
  [mysqld_safe]
  log-error=/var/log/mysqld.log
  pid-file=/var/run/mysqld/mysqld.pid
  </code></pre>
  
  <p><strong>mysqltunner</strong> </p>
  
  <pre><code>-------- Performance Metrics -------------------------------------------------
  [--] Up for: 18h 33m 52s (121M q [1K qps], 4M conn, TX: 32B, RX: 16B)
  [--] Reads / Writes: 99% / 1%
  [--] Total buffers: 19.1G global + 10.6M per thread (1000 max threads)
  [OK] Maximum possible memory usage: 29.5G (46% of installed RAM)
  [OK] Slow queries: 0% (4/121M)
  [OK] Highest usage of available connections: 8% (88/1000)
  [OK] Key buffer size / total MyISAM indexes: 2.0G/1.7G
  [OK] Key buffer hit rate: 99.4% (15M cached / 90K reads)
  [OK] Query cache efficiency: 71.1% (75M cached / 106M selects)
  [!!] Query cache prunes per day: 27253282
  [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 21M sorts)
  [!!] Joins performed without indexes: 12267
  [OK] Temporary tables created on disk: 3% (1K on disk / 49K total)
  [OK] Thread cache hit rate: 99% (1K created / 4M connections)
  [!!] Table cache hit rate: 1% (365 open / 21K opened)
  [OK] Open file limit used: 1% (449/35K)
  [OK] Table locks acquired immediately: 99% (30M immediate / 30M locks)
  [OK] InnoDB data size / buffer pool: 115.6M/16.0G
  
  -------- Recommendations -----------------------------------------------------
  General recommendations:
      Run OPTIMIZE TABLE to defragment tables for better performance
      MySQL started within last 24 hours - recommendations may be inaccurate
      Enable the slow query log to troubleshoot bad queries
      Adjust your join queries to always utilize indexes
      Increase table_cache gradually to avoid file descriptor limits
  Variables to adjust:
      query_cache_size (&gt; 128M)
      join_buffer_size (&gt; 4.0M, or always use indexes with joins)
      table_cache (&gt; 512)
  </code></pre>
  
  <p><strong>Status</strong></p>
  
  <pre><code>mysql&gt; show status LIKE '%open_%';
  +--------------------------+-------+
  | Variable_name            | Value |
  +--------------------------+-------+
  | Com_show_open_tables     | 0     |
  | Open_files               | 449   |
  | Open_streams             | 0     |
  | Open_table_definitions   | 169   |
  | Open_tables              | 365   |
  | Opened_files             | 7436  |
  | Opened_table_definitions | 0     |
  | Opened_tables            | 0     |
  | Slave_open_temp_tables   | 0     |
  +--------------------------+-------+
  9 rows in set (0.00 sec)
  
  mysql&gt; show status LIKE '%key_%';
  +------------------------+----------+
  | Variable_name          | Value    |
  +------------------------+----------+
  | Com_assign_to_keycache | 0        |
  | Com_preload_keys       | 0        |
  | Com_show_keys          | 0        |
  | Key_blocks_not_flushed | 0        |
  | Key_blocks_unused      | 1617830  |
  | Key_blocks_used        | 96906    |
  | Key_read_requests      | 15325797 |
  | Key_reads              | 91041    |
  | Key_write_requests     | 497292   |
  | Key_writes             | 421837   |
  +------------------------+----------+
  10 rows in set (0.00 sec)
  </code></pre>
  
  <p><strong>Notes:</strong> We are running nightly mysqldump. </p>