search

hashbangcode / vlad

Vlad - Vagrant LAMP Ansible Drupal
173 stars 53 forks source link

MySQL default settings tweak(s) #123

Closed kevinquillen closed 9 years ago

kevinquillen commented 9 years ago

I've found that when using Vlad for larger Drupal sites (300+ db tables, hundreds of thousands of entity records) the VM seems to slow down over time, particularly when it has 'idled' for a bit. The result is requests go from a few seconds up to 30-60 for reloading pages.

I think I have narrowed this down to default MySQL settings, and offer these settings improvements in settings.yml:

# MySQL my.cnf settings
  mysql_max_allowed_packet: 128M
  innodb_buffer_pool_size: 2G
  innodb_file_per_table: OFF
  mysql_character_set_server: utf8
  mysql_collation_server: utf8_general_ci
  innodb_log_file_size: 512M
  skip_name_resolve: TRUE

The main offender might have been innodb_file_per_table, but I added skip_name_resolve for good measure as we are dealing with a VM. So far, my performance has been better than before.

philipnorton42 commented 9 years ago

Interesting settings you have there. I've added them to my local copy but I'll need to test them a little more before pushing them back up. Thanks for the input! :)

philipnorton42 commented 9 years ago

I have added these to the dev branch now, with different values as a pool size of 2G seems a little big as a default value :) Tested with Ubuntu and Centos box images but I'm keen to get a second pair of eyes on it.

kevinquillen commented 9 years ago

I forgot to note that setting. My laptop has 16gb of ram, I could afford it haha. 512MB is probably a good starting point for smaller sites.

On Sunday, January 25, 2015, Philip Norton notifications@github.com wrote:

I have added these to the dev branch now, with different values as a pool size of 2G seems a little big as a default value :) Tested with Ubuntu and Centos box images.

— Reply to this email directly or view it on GitHub https://github.com/hashbangcode/vlad/issues/123#issuecomment-71378233.

philipnorton42 commented 9 years ago

I've been tweaking these default settings recently and they seem a lot more stable, especially on smaller boxes. Easy to increase these values via the settings.yml file.