MySQL config for drupal

drupalinnodbMySQL

I am running MySQL 5.0.75 on Ubuntu on a dual core machine with 8 gigs of ram. I am running a Drupal 5 and Drupal 6 site of the database.

I am sure my MySQL config is not optimal but I am wondering if there is any low hanging fruit I can address. Here is the heart of the current setup.

New current setup (original one at the bottom). This is a dedicated machine, btw:

key_buffer                      = 2G
innodb_buffer_pool_size         = 6G
innodb_additional_mem_pool_size = 16M
max_allowed_packet              = 16M
sort_buffer_size                = 128M
myisam_sort_buffer_size         = 128M
read_buffer_size                = 128M
join_buffer_size                = 128M
read_rnd_buffer_size            = 512M

thread_stack                    = 192K
thread_cache_size               = 10
thread_concurrency              = 8
innodb_thread_concurrency       = 8

table_cache                     = 2048
query_cache_limit               = 2M
query_cache_size                = 128M

Old:

key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 128K
thread_cache_size       = 8
query_cache_limit       = 1M
query_cache_size        = 16M
expire_logs_days        = 10
max_binlog_size         = 100M
skip-federated
default-storage-engine  = InnoDB
default-character-set   = utf8
default-collation       = utf8_general_ci

Best Answer

It looks like you are using InnoDB as your primary database engine type, but you configuration has no InnoDB configuration tuning options, which means you are using the defaults. These defaults, out of the box are sized for an 8Mb machine!

I recommend these settings as a basis and work from there

innodb_file_per_table
set-variable = innodb_buffer_pool_size=2G
set-variable = innodb_log_files_in_group=4
set-variable = innodb_log_file_size=128M
set-variable = thread_cache_size=10
set-variable = table_cache=512
set-variable = query_cache_size=8M

You didn't say if the php site was sharing the same machine as your db server, so you will have to be careful that MySQL doesn't consume so much physical ram that it causes the machine to swap.

I'd also recommend that you add these settings to low slow queries and queries with a poor query plan.

log_slow_queries
log-queries-not-using-indexes
set-variable = long_query_time=1
Related Topic