title: MySQL my.cnf performance tuning recommendations
body: <p>I am kind of hoping that someone might be able to offer some assistance with optimizing a my.cnf file for an extremely high volume mysql database server.</p>
  
  <pre><code>Our architecture is as follows:
  
  Memory     : 96GB
  CPUs       : 12
  OS &amp; Mysql : 64-bit
  Disk Space : 1.2 TB
  DB Engine  : MyISAM
  </code></pre>
  
  <p>Our web application is used by roughly 300 client simultaneously. We need our my.cnf tuned to give the best possible performance for this infrastructure.</p>
  
  <p>I am fully aware that indexes and optimized queries are a major factor in this, but we would like to start with a system that is configured properly and then follow that up with systematically re-engineering our queries accordingly.</p>
  
  <pre><code>Here is our current my.cnf file content:
  
  [mysqld]
  datadir=/home/mysql
  socket=/home/mysql/mysql.sock
  user=mysql
  
  log-bin=mysql-bin
  server-id=1
  # Disabling symbolic-links is recommended to prevent assorted security risks
  symbolic-links=1
  
  log-slow-queries = /var/log/mysqld_slow_queries.log
  long_query_time = 10
  
  max_connections = 500
  
  key_buffer_size = 32768M
  #max_allowed_packet = 2M
  #table_open_cache = 128
  #sort_buffer_size = 1024K
  #net_buffer_length = 64K
  #read_buffer_size = 1024K
  #read_rnd_buffer_size = 1024K
  #myisam_sort_buffer_size = 8M
  query_cache_size = 128M
  query_cache_limit = 128M
  
  interactive_timeout = 300
  wait_timeout = 300
  
  # Added values after load testing
  thread_cache_size = 8
  #tmp_table_size = 256M
  #max_heap_table_size = 256M
  #table_cache = 512
  #join_buffer_size = 512
  
  log-error=/var/log/mysqld.log
  
  innodb_buffer_pool_size=128M
  #innodb_file_per_table
  #innodb_log_file_size=250M
  ##innodb_buffer_pool_size=64M
  #innodb_buffer_pool_size=1024M
  #innodb_log_buffer_size=4M
  ##log-bin=mysql-bin
  
  [mysqld_safe]
  log-error=/var/log/mysqld.log
  pid-file=/var/run/mysqld/mysqld.pid
  
  #[myisamchk]
  #key_buffer = 64M
  #sort_buffer = 64M
  #read_buffer = 16M
  #write_buffer = 16M
  </code></pre>
  
  <p>Any suggestions?
  Thanks folks.</p>
  
  <p>Edit by RolandoMySQLDBA</p>
  
  <p>Since all you data is MyISAM, please run this query and show the output</p>
  
  <pre><code>SELECT CONCAT(ROUND(KBS/POWER(1024,
  IF(PowerOf1024&lt;0,0,IF(PowerOf1024&gt;3,0,PowerOf1024)))+0.4999),
  SUBSTR(' KMG',IF(PowerOf1024&lt;0,0,
  IF(PowerOf1024&gt;3,0,PowerOf1024))+1,1))
  recommended_key_buffer_size FROM
  (SELECT LEAST(POWER(2,32),KBS1) KBS
  FROM (SELECT SUM(index_length) KBS1
  FROM information_schema.tables
  WHERE engine='MyISAM' AND
  table_schema NOT IN ('information_schema','mysql')) AA ) A,
  (SELECT 3 PowerOf1024) B;
  </code></pre>
  
  <p>@ Rolando - Thanks...the results of that query was 4G.</p>