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 Commented 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. Commented 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 Commented 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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.