2

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
Improve this question
1
  • 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, 2009 at 18:22

2 Answers 2

Reset to default
3

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
Improve this answer
2
  • 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, 2009 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, 2009 at 6:09
2

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .