title: Drupal database performance tuning - switching particular tables to InnoDB from MyISAM
body: <p>I've got a drupal site that gets low traffic, but has tons of new content being added by a custom feed importer module. This module creates nodes and associated taxonomies for imported articles. </p>
  
  <p>Currently, I believe ALL our drupal tables are MyISAM. I was considering switching the heavy write tables:</p>
  
  <ol>
  <li>node</li>
  <li>watchdog</li>
  <li>sessions</li>
  <li>accesslog</li>
  <li>What other tables would you consider?</li>
  </ol>
  
  <p>To InnoDB.</p>
  
  <p>Do you think this is a good idea? Am I likely to see a performance gain? The reason I'm looking at this as part of my overall solution is that, on import, mysqld is running out of memory often and taking the entire system down. It ONLY happens on import.</p>
  
  <p>I'll end up seeing things like this:</p>
  
  <pre><code>xml import at [01/Jun/2011:13:26:38 -0400] "GET /import/xml_import HTTP/1.1" 200
  ....
  14:02:38 [ERROR] /usr/libexec/mysqld: Out of memory (Needed 1049152 bytes)
  </code></pre>
  
  <p>The box is x32 so we are limited to the amount of memory we can allocate to mySql. We also have PHP, JAVA, SVN and more running on this box ... it's taxed as is. heh.</p>
  
  <p>So, any input on performance tuning the db in general would be appreciated, I'm doing the research now.</p>
  
  <p>TIA.</p>
  
  <p>EDIT: (I've included my current my.cnf):</p>
  
  <pre>
  
  [mysqld]
  datadir=/var/lib/mysql
  socket=/var/lib/mysql/mysql.sock
  tmpdir=/var/lib/mysql/tmp
  #old_passwords=1
  skip-locking
  key_buffer = 2048M #doubled from 1024
  max_allowed_packet = 16M
  table_cache = 5000
  sort_buffer_size = 1M
  read_buffer_size = 1M
  read_rnd_buffer_size = 8M
  myisam_sort_buffer_size = 64M
  thread_cache_size = 64 #doubled from 32
  thread_concurrency = 8
  query_cache_size = 1024M #doubled from 512
  tmp_table_size=1024M
  max_heap_table_size=1024M
  back_log = 100
  max_connect_errors = 10000
  join_buffer_size=1M
  open-files = 20000
  
  interactive_timeout = 600
  wait_timeout = 600
  
  ft_min_word_len=3
  ft_stopword_file=''
  max_connections=1000
  
  #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
  #innodb_log_file_size = 100M
  
  #innodb_buffer_pool_size = 384M
  #innodb_additional_mem_pool_size = 20M
  
  
  #log-slow-queries=/var/lib/mysqllogs/slow-log
  #long_query_time=2   
  #log-queries-not-using-indexes
  
  #log-bin=/var/lib/mysqllogs/bin-log
  #log-slave-updates
  #expire_logs_days = 14
  server-id       = 1 
  
  [mysql.server]
  user=mysql
  #basedir=/var/lib  
  
  [mysqld_safe]
  err-log=/var/log/mysqld.log
  pid-file=/var/run/mysqld/mysqld.pid
  open_files_limit=65536
  </pre>