title: Mysqltuner suggestions and changes to my.cnf
body: <p>Had this question on Serverfault for a few days with no luck.</p>
  
  <p>I've run mysqltuner.pl on a VPS and have a bunch of questions as to the suggestions on variables to change. I'm sure these are general questions with complex answers.</p>
  
  <p>I'm not knowledgable enough to write queries and test them against the server, but am just trying to get a bit more performance out of the server that runs five WordPress sites with >200,000 page views/month.</p>
  
  <p>I've optimized the database via phpmyadmin (and do that regularly), but the tuner still says there are fragmented tables. And because this is WordPress, I can't change queries in core code.</p>
  
  <p>But how much should I increase the variables like query_cache_size and  innodb_buffer_pool_size? What about the other innodb variables?</p>
  
  <p>Some of the variables suggested don't exist in my.cnf, like table_cache, and are flagged in tuner report, etc. Can I add them to my.cnf?</p>
  
  <blockquote>
    <p><em>(And why is this block duplicated in my.cnf? Can I delete the duplicate?)</em></p>
  
  <pre><code>set-variable = innodb_buffer_pool_size=2M
  set-variable = innodb_additional_mem_pool_size=500K
  set-variable = innodb_log_buffer_size=500K
  set-variable = innodb_thread_concurrency=2
  </code></pre>
  </blockquote>
  
  <p>Below is the my.cnf and the output of mysqltuner:</p>
  
  <p><strong>Contents of my.cnf:</strong></p>
  
  <pre><code>query-cache-type = 1
  query-cache-size = 8M
  
  set-variable=local-infile=0
  datadir=/var/lib/mysql
  socket=/var/lib/mysql/mysql.sock
  user=mysql
  
  old_passwords=1
  
  skip-bdb
  
  set-variable = innodb_buffer_pool_size=2M
  set-variable = innodb_additional_mem_pool_size=500K
  set-variable = innodb_log_buffer_size=500K
  set-variable = innodb_thread_concurrency=2
  
  [mysqld_safe]
  log-error=/var/log/mysqld.log
  pid-file=/var/run/mysqld/mysqld.pid
  skip-bdb
  
  set-variable = innodb_buffer_pool_size=2M
  set-variable = innodb_additional_mem_pool_size=500K
  set-variable = innodb_log_buffer_size=500K
  set-variable = innodb_thread_concurrency=2
  </code></pre>
  
  <p><strong>Output of mysqltuner:</strong></p>
  
  <pre><code>------- General Statistics --------------------------------------------------
  [--] Skipped version check for MySQLTuner script
  [OK] Currently running supported MySQL version 5.0.45
  [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
  
  -------- Storage Engine Statistics -------------------------------------------
  [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
  [--] Data in MyISAM tables: 133M (Tables: 637)
  [--] Data in InnoDB tables: 10M (Tables: 344)
  [--] Data in MEMORY tables: 126K (Tables: 2)
  [!!] Total fragmented tables: 69
  
  -------- Security Recommendations  -------------------------------------------
  [OK] All database users have passwords assigned
  
  -------- Performance Metrics -------------------------------------------------
  [--] Up for: 1d 6h 24m 13s (2M q [22.135 qps], 116K conn, TX: 4B, RX: 530M)
  [--] Reads / Writes: 97% / 3%
  [--] Total buffers: 35.0M global + 2.7M per thread (100 max threads)
  [OK] Maximum possible memory usage: 303.7M (8% of installed RAM)
  [OK] Slow queries: 0% (4/2M)
  [OK] Highest usage of available connections: 53% (53/100)
  [OK] Key buffer size / total MyISAM indexes: 8.0M/46.1M
  [OK] Key buffer hit rate: 99.6% (749M cached / 2M reads)
  [OK] Query cache efficiency: 32.2% (685K cached / 2M selects)
  [!!] Query cache prunes per day: 948863
  [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 660K sorts)
  [!!] Temporary tables created on disk: 46% (400K on disk / 869K total)
  [!!] Thread cache is disabled
  [!!] Table cache hit rate: 0% (64 open / 24K opened)
  [OK] Open file limit used: 10% (109/1K)
  [OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
  [!!] InnoDB data size / buffer pool: 10.6M/2.0M
  
  -------- Recommendations -----------------------------------------------------
  General recommendations:
      Run OPTIMIZE TABLE to defragment tables for better performance
      Enable the slow query log to troubleshoot bad queries
      When making adjustments, make tmp_table_size/max_heap_table_size equal
      Reduce your SELECT DISTINCT queries without LIMIT clauses
      Set thread_cache_size to 4 as a starting value
      Increase table_cache gradually to avoid file descriptor limits
  Variables to adjust:
      query_cache_size (&gt; 8M)
      tmp_table_size (&gt; 32M)
      max_heap_table_size (&gt; 16M)
      thread_cache_size (start at 4)
      table_cache (&gt; 64)
      innodb_buffer_pool_size (&gt;= 10M)
  </code></pre>