Volans has uploaded a new change for review. https://gerrit.wikimedia.org/r/284666
Change subject: MariaDB: separate external storage my.cnf ...................................................................... MariaDB: separate external storage my.cnf - Increased max_connection=10000 - Set thread_pool_max_threads=2000 (was the default 500) - Removed thread_pool_size, it's default is the number of CPU cores, expect for https://jira.mariadb.org/browse/MDEV-7806 Bug: T133265 Change-Id: I56da6dd098106888a5f8832ed975681fb38ac601 --- M manifests/role/mariadb.pp A templates/mariadb/production-es.my.cnf.erb 2 files changed, 170 insertions(+), 1 deletion(-) git pull ssh://gerrit.wikimedia.org:29418/operations/puppet refs/changes/66/284666/1 diff --git a/manifests/role/mariadb.pp b/manifests/role/mariadb.pp index 924cb4c..46ce2ba 100644 --- a/manifests/role/mariadb.pp +++ b/manifests/role/mariadb.pp @@ -511,9 +511,15 @@ mariadb10 => true, } + if has_key(['es1', 'es2', 'es3'], $shard) { + $config = 'mariadb/production-es.my.cnf.erb' + } else { + $config = 'mariadb/production.my.cnf.erb' + } + class { 'mariadb::config': prompt => "PRODUCTION ${shard}", - config => 'mariadb/production.my.cnf.erb', + config => $config, password => $passwords::misc::scripts::mysql_root_pass, datadir => '/srv/sqldata', tmpdir => '/srv/tmp', diff --git a/templates/mariadb/production-es.my.cnf.erb b/templates/mariadb/production-es.my.cnf.erb new file mode 100644 index 0000000..175ce57 --- /dev/null +++ b/templates/mariadb/production-es.my.cnf.erb @@ -0,0 +1,163 @@ +# Warning: This file is managed by Puppet! +# Production + +[client] +port = 3306 +socket = /tmp/mysql.sock +<% if @ssl == 'on' %> +# ssl +ssl-ca=/etc/mysql/ssl/cacert.pem +ssl-cert=/etc/mysql/ssl/server-cert.pem +ssl-key=/etc/mysql/ssl/server-key.pem +# skip server cert validation until we generate one cert per server +# it would check the cert's common name against the host +# ssl-verify-server-cert +<% elsif @ssl == 'puppet-cert' %> +# ssl +ssl-ca=/etc/ssl/certs/Puppet_Internal_CA.pem +ssl-cert=/etc/mysql/ssl/cert.pem +ssl-key=/etc/mysql/ssl/server.key +ssl-verify-server-cert +<% elsif @ssl == 'multiple-ca' %> +# ssl +ssl-ca=/etc/mysql/ssl/ca.crt +ssl-cert=/etc/mysql/ssl/cert.pem +ssl-key=/etc/mysql/ssl/server.key +# skip server cert validation until we generate one cert per server +# it would check the cert's common name against the host +# ssl-verify-server-cert +<% end %> + +[mysqld] + +user = mysql +socket = /tmp/mysql.sock +port = 3306 +extra_port = 3307 +datadir = <%= @datadir %> +tmpdir = <%= @tmpdir %> +server_id = <%= @server_id %> + +# Always start in read_only mode. +# Master selection will be handled by cluster control. +read_only = on + +log_bin +log_slave_updates +skip-external-locking +skip-name-resolve +temp-pool + +thread_handling = pool-of-threads +thread_pool_stall_limit = 100 +max_connections = 10000 +thread_pool_max_threads = 2000 +back_log = 500 +extra_max_connections = 10 +max_connect_errors = 1000000000 +max_allowed_packet = 32M +connect_timeout = 3 +query_cache_size = 0 +query_cache_type = 0 +event_scheduler = 1 +log-warnings = 0 +thread_stack = 192K +thread_cache_size = 300 +interactive_timeout = 28800 +wait_timeout = 3600 +transaction-isolation = REPEATABLE-READ +use_stat_tables = preferably + +sync_binlog = 1 +binlog_cache_size = 1M +max_binlog_size = 1000M +binlog_format = <%= @binlog_format %> +expire_logs_days = 7 +slave_transaction_retries = 4294967295 +sql-mode = IGNORE_BAD_TABLE_OPTIONS +tmp_table_size = 64M +max_heap_table_size = 64M + +table_open_cache = 50000 +table_definition_cache = 40000 +open-files-limit = 200000 +character_set_server = binary +character_set_filesystem = binary +collation_server = binary + +default-storage-engine = InnoDB +innodb_file_per_table = 1 +innodb_buffer_pool_size = <%= (Float(@memorysize.split[0]) * 0.75).round %>G +innodb_log_file_size = 2G +innodb_flush_log_at_trx_commit = 1 +innodb_flush_method = O_DIRECT +innodb_thread_concurrency = 0 +innodb_io_capacity = 1000 +innodb_stats_sample_pages = 16 +innodb_stats_method = nulls_unequal +aria_pagecache_buffer_size = 1G +join_cache_level = 8 + +# dump and load innodb buffer at start and stop +innodb_buffer_pool_load_at_startup = 1 +innodb_buffer_pool_dump_at_shutdown = 1 + +<% if @kernelversion < "3.19" %> +# Until kernel 3.16 http://www.spinics.net/lists/stable/msg61873.html +# At least, I think so. For now we need to avoid the an assertion failure on Trusty w/ 3.13 +innodb_use_native_aio = 0 +innodb_read_io_threads = 16 +innodb_write_io_threads = 8 +<% end %> +<% if @ssl == 'on' %> +# ssl +ssl-ca=/etc/mysql/ssl/cacert.pem +ssl-cert=/etc/mysql/ssl/server-cert.pem +ssl-key=/etc/mysql/ssl/server-key.pem +ssl-cipher=TLSv1.2 +<% elsif @ssl == 'puppet-cert' %> +# ssl +ssl-ca=/etc/ssl/certs/Puppet_Internal_CA.pem +ssl-cert=/etc/mysql/ssl/cert.pem +ssl-key=/etc/mysql/ssl/server.key +ssl-cipher=TLSv1.2 +<% elsif @ssl == 'multiple-ca' %> +# ssl +ssl-ca=/etc/mysql/ssl/ca.crt +ssl-cert=/etc/mysql/ssl/cert.pem +ssl-key=/etc/mysql/ssl/server.key +ssl-cipher=TLSv1.2 +<% end %> +<% if @p_s == 'on' %> +# Enabling performance_schema (disabled by default in MariaDB10) +performance_schema = 1 +# downsizing performance schema memory usage: T99485 +performance_schema_digests_size = -1 +performance_schema_max_thread_instances = 500 +performance_schema_max_cond_instances = 1000 +performance_schema_accounts_size = 300 +performance_schema_hosts_size = 300 +performance_schema_events_statements_history_size = 10 +performance_schema_events_statements_history_long_size = 1000 +performance_schema_events_waits_history_size = 10 +performance_schema_events_waits_history_long_size = 1000 +performance_schema_events_stages_history_size = 10 +performance_schema_events_stages_history_long_size = 1000 +performance_schema_max_mutex_instances = 5000 +performance_schema_max_rwlock_instances = 2000 +performance_schema_max_socket_instances = 500 +performance_schema_max_table_instances = 1000 +<% else %> +# only enable userstat if p_s is disabled +performance_schema = 0 +userstat = 1 +<% end %> + +optimizer_switch = 'mrr=on,mrr_cost_based=on,mrr_sort_keys=on,optimize_join_buffer_size=on' + +[mysqldump] + +quick +max_allowed_packet = 32M + +#!includedir /etc/mysql/conf.d/ -- To view, visit https://gerrit.wikimedia.org/r/284666 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I56da6dd098106888a5f8832ed975681fb38ac601 Gerrit-PatchSet: 1 Gerrit-Project: operations/puppet Gerrit-Branch: production Gerrit-Owner: Volans <[email protected]> _______________________________________________ MediaWiki-commits mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
