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


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
default-storage-engine  = InnoDB
default-character-set   = utf8
default-collation       = utf8_general_ci
  • If you have 8 gig of ram total then your machine will page with those settings. you've allocated 8 gb of ram to the innodb and myisam caches which leaves nothing for the connections themselves let alone the rest of the os – Dave Cheney Jun 27 '09 at 18:22

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

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.

set-variable = long_query_time=1
  • 1
    IIRC mysql can't change log files configuration dynamically. After changes in logfile configuration you need to stop mysqld and manually delete old log files, then it can start. – SaveTheRbtz Jun 23 '09 at 2:49
  • That is correct, you'll also need to dump and restore your database to switch to innodb_file_per_table – Dave Cheney Jun 23 '09 at 6:09

You're using InnoDB so your innodb_buffer_pool should be as big as you can make it (key_buffer is the equivalent for MyISAM). 6GB should be an okay value on a 8GB machine.

If you're doing lots of complex queries, sort_buffer and tmp_table_size may also be worth increasing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.