Add additional mysql tunables

Adding more mysql tuning options to better support production-like
environments. These additional attribues have been set to default
values that match the mysql defaults. Including spec tests.

Change-Id: Iadb0746354ea93a6beef9ecbae5d1bdc5cec1d48
Closes-Bug: #1436481
This commit is contained in:
Mark Gloshen 2015-03-25 16:42:10 -07:00
parent 9a864936c4
commit acc54b2680
5 changed files with 62 additions and 21 deletions

View File

@ -29,6 +29,10 @@ default['openstack']['mysql']['version'] = '5.5'
default['openstack']['mysql']['service_name'] = 'default'
# Storage engine, base OpenStack requires the InnoDB flavor
default['openstack']['mysql']['default-storage-engine'] = 'InnoDB'
# InnoDB lock mode for generating auto-increment values
default['openstack']['mysql']['innodb_autoinc_lock_mode'] = '1'
# InnoDB give each table its own file
default['openstack']['mysql']['innodb_file_per_table'] = 'OFF'
# InnoDB thread concurrency
default['openstack']['mysql']['innodb_thread_concurrency'] = '0'
# InnoDB commit concurrency
@ -36,10 +40,18 @@ default['openstack']['mysql']['innodb_commit_concurrency'] = '0'
# InnoDB number of read io threads
default['openstack']['mysql']['innodb_read_io_threads'] = '4'
# InnoDB number of commit transactions to flush log
default['openstack']['mysql']['innodb_flush_log_at_trx_commit'] = '2'
default['openstack']['mysql']['innodb_flush_log_at_trx_commit'] = '1'
# InnoDB memory buffer for caching table data and indexes
default['openstack']['mysql']['innodb_buffer_pool_size'] = '134217728'
# InnoDB size of each log file in a log group
default['openstack']['mysql']['innodb_log_file_size'] = '5242880'
# InnoDB size of buffer for logs
default['openstack']['mysql']['innodb_log_buffer_size'] = '8388608'
# Skip name resolution
default['openstack']['mysql']['skip-name-resolve'] = true
default['openstack']['mysql']['skip-name-resolve'] = false
# Character set
default['openstack']['mysql']['character-set-server'] = 'utf8'
default['openstack']['mysql']['character-set-server'] = 'latin1'
# Memory allocated for caching query results
default['openstack']['mysql']['query_cache_size'] = '0'
# Maximum number of connections
default['openstack']['mysql']['max_connections'] = '1024'
default['openstack']['mysql']['max_connections'] = '151'

View File

@ -19,11 +19,18 @@ describe 'openstack-ops-database::mariadb-server' do
)
expect(file).to notify('service[mysql]')
[/^default-storage-engine = InnoDB$/,
/^innodb_autoinc_lock_mode = 1$/,
/^innodb_file_per_table = OFF$/,
/^innodb_thread_concurrency = 0$/,
/^innodb_commit_concurrency = 0$/,
/^innodb_flush_log_at_trx_commit = 2$/,
/^skip-name-resolve$/,
/^character-set-server = utf8$/].each do |line|
/^innodb_read_io_threads = 4$/,
/^innodb_flush_log_at_trx_commit = 1$/,
/^innodb_buffer_pool_size = 134217728$/,
/^innodb_log_file_size = 5242880$/,
/^innodb_log_buffer_size = 8388608$/,
/^character-set-server = latin1$/,
/^query_cache_size = 0$/,
/^max_connections = 151$/].each do |line|
expect(chef_run).to render_config_file(file.name)\
.with_section_content('mysqld', line)
end

View File

@ -13,7 +13,7 @@ describe 'openstack-ops-database::mariadb-server' do
it 'overrides mariadb default attributes' do
expect(chef_run.node['mariadb']['mysqld']['bind_address']).to eq '127.0.0.1'
expect(chef_run.node['mariadb']['mysqld']['default_storage_engine']).to eq 'InnoDB'
expect(chef_run.node['mariadb']['mysqld']['max_connections']).to eq '1024'
expect(chef_run.node['mariadb']['mysqld']['max_connections']).to eq '151'
expect(chef_run.node['mariadb']['forbid_remote_root']).to be true
end
@ -31,13 +31,18 @@ describe 'openstack-ops-database::mariadb-server' do
)
expect(file).to notify('service[mysql]')
[/^default-storage-engine = InnoDB$/,
/^innodb_autoinc_lock_mode = 1$/,
/^innodb_file_per_table = OFF$/,
/^innodb_thread_concurrency = 0$/,
/^innodb_commit_concurrency = 0$/,
/^innodb_read_io_threads = 4$/,
/^innodb_flush_log_at_trx_commit = 2$/,
/^skip-name-resolve$/,
/^character-set-server = utf8$/,
/^max_connections = 1024$/].each do |line|
/^innodb_flush_log_at_trx_commit = 1$/,
/^innodb_buffer_pool_size = 134217728$/,
/^innodb_log_file_size = 5242880$/,
/^innodb_log_buffer_size = 8388608$/,
/^character-set-server = latin1$/,
/^query_cache_size = 0$/,
/^max_connections = 151$/].each do |line|
expect(chef_run).to render_config_file(file.name)\
.with_section_content('mysqld', line)
end

View File

@ -24,13 +24,24 @@ describe 'openstack-ops-database::mysql-server' do
)
end
it 'creates mysql openstack config and notifies server to restart' do
expect(chef_run).to create_mysql_config('openstack').with(
source: 'openstack.cnf.erb',
action: [:create]
)
resource = chef_run.find_resource('mysql_config', 'openstack')
expect(resource).to notify('mysql_service[default]').to(:restart).delayed
describe 'openstack.cnf' do
let(:file) { '/etc/mysql/conf.d/openstack.cnf' }
it 'creates mysql openstack config and notifies server to restart' do
expect(chef_run).to create_mysql_config('openstack').with(
source: 'openstack.cnf.erb',
action: [:create]
)
resource = chef_run.find_resource('mysql_config', 'openstack')
expect(resource).to notify('mysql_service[default]').to(:restart).delayed
end
# TODO: Verify contents of openstack.cnf. This cannot be done properly at
# present because the mysql_config LWRP comes from the mysql cookbook but
# does not expose a custom matcher for testing the templates contents.
# See ChefSpec docs on testing LWRPs from other cookbooks.
# See issue filed against mysql cookbook:
# https://github.com/chef-cookbooks/mysql/issues/322
end
end
end

View File

@ -1,12 +1,18 @@
<%= node['openstack']['db']['custom_template_banner'] %>
[mysqld]
default-storage-engine = <%= node['openstack']['mysql']['default-storage-engine'] %>
innodb_autoinc_lock_mode = <%= node['openstack']['mysql']['innodb_autoinc_lock_mode'] %>
innodb_file_per_table = <%= node['openstack']['mysql']['innodb_file_per_table'] %>
innodb_thread_concurrency = <%= node['openstack']['mysql']['innodb_thread_concurrency'] %>
innodb_commit_concurrency = <%= node['openstack']['mysql']['innodb_commit_concurrency'] %>
innodb_read_io_threads = <%= node['openstack']['mysql']['innodb_read_io_threads'] %>
innodb_flush_log_at_trx_commit = <%= node['openstack']['mysql']['innodb_flush_log_at_trx_commit'] %>
<% if node['openstack']['mysql']['skip-name-resolve'] %>
innodb_buffer_pool_size = <%= node['openstack']['mysql']['innodb_buffer_pool_size'] %>
innodb_log_file_size = <%= node['openstack']['mysql']['innodb_log_file_size'] %>
innodb_log_buffer_size = <%= node['openstack']['mysql']['innodb_log_buffer_size'] %>
<%- if node['openstack']['mysql']['skip-name-resolve'] %>
skip-name-resolve
<% end %>
<%- end %>
character-set-server = <%= node['openstack']['mysql']['character-set-server'] %>
query_cache_size = <%= node['openstack']['mysql']['query_cache_size'] %>
max_connections = <%= node['openstack']['mysql']['max_connections'] %>