Drupal database performance tuning - switching particular tables to InnoDB from MyISAM

Question

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.

Currently, I believe ALL our drupal tables are MyISAM. I was considering switching the heavy write tables:

node
watchdog
sessions
accesslog
What other tables would you consider?

To InnoDB.

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.

I'll end up seeing things like this:

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)

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.

So, any input on performance tuning the db in general would be appreciated, I'm doing the research now.

TIA.

EDIT: (I've included my current my.cnf):


[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

As an alternative, you could for example log watchdog messages to a different place, for example syslog (just enable syslog.module instead of dblog.module) or mongodb (drupal.org/project/mongodb). Also, try to pin-down which queries are actually slow. In another, similar question, it turned out to be a single missing index on a table from notifications module which extremely slowed down the whole import: drupal.stackexchange.com/questions/3021/… — Berdir

RolandoMySQLDBA RolandoMySQLDBA · Accepted Answer · 2011-06-14T18:39:41

If you know for a certainty that the website will be low trafficked, by all means remain in MyISAM.

However, should that pattern ever change and you become high traffic again, you could configure everything into InnoDB for two major reasons

REASON #1 : InnoDB does row level locking. As for MyISAM, any INSERT, UPDATE, or DELETE query executed will cause a full table lock. Even in a low trafficked website, the possiblility exists of two or more DB Connections Locking the Same Table. With InnoDB, that possibility is totally eliminated.

REASON #2 : InnoDB Caches Data and Indexes. MyISAM only caches Index Pages.

The key cache (sized by key_buffer_size) hold index pages for MyISAM tables. There is always disk I/O to read data from a MyISAM. A record read for the first time will cache the Index Pages used to find the row in to key cache. Subsequent lookups will find the key needed in the key cache, but there will always be mandatory disk I/O to get the data. With InnoDB, both Data and Index Pages Reside in the InnoDB Buffer Pool (size by innodb_buffer_pool_size). Disk I/O is dramatically reduced with everything cached in the Buffer Pool.

I have always recommended Drupal databases using InnoDB instead of MyISAM. It is actually easy to convert using straight MySQL.

Until then, MyISAM is all the firepower you need, especially on servers with modest RAM.

Drupal database performance tuning - switching particular tables to InnoDB from MyISAM

2 Answers