Marostegui has uploaded a new change for review. (
https://gerrit.wikimedia.org/r/364150 )
Change subject: mariadb: Create sanitarium3 role
......................................................................
mariadb: Create sanitarium3 role
As we spoke last week, it might be easier to create a sanitarium3 role
to support the new case of MariaDB 10.1 + multi-instance + sanitarium
features.
This might not be the best way of doing this as probably a more deep
refactor is needed, but it is a quick way of advancing on leaving db1102
as set up as sanitarium3 with the pending shards replicating to the new
labs infra.
This host differs from db1069 as it has 10.1 and replicates to 10.1, so
we need the rbr triggers.
sanitarium role cannot be used here for db1102 as db1069 does not have
10.1 and will be hard to upgrade to 10.1 as the old labs hosts replicate 10.0.
sanitarium2 role doesn't apply here as well because it doesn't run multi
instance, it runs multi-source.
Change-Id: I7eca200ec1f90712eec65ee592ac070fa78c7168
---
M manifests/site.pp
A modules/role/manifests/mariadb/sanitarium3.pp
A modules/role/templates/mariadb/mysqld_config/sanitarium3.my.cnf.erb
A modules/role/templates/mariadb/sanitarium3.sysvinit.erb
4 files changed, 319 insertions(+), 1 deletion(-)
git pull ssh://gerrit.wikimedia.org:29418/operations/puppet
refs/changes/50/364150/1
diff --git a/manifests/site.pp b/manifests/site.pp
index 93fe113..5c78b5f 100644
--- a/manifests/site.pp
+++ b/manifests/site.pp
@@ -633,7 +633,7 @@
}
node 'db1102.eqiad.wmnet' {
- role(mariadb::sanitarium)
+ role(mariadb::sanitarium3)
include ::base::firewall
}
diff --git a/modules/role/manifests/mariadb/sanitarium3.pp
b/modules/role/manifests/mariadb/sanitarium3.pp
new file mode 100644
index 0000000..22f86b4
--- /dev/null
+++ b/modules/role/manifests/mariadb/sanitarium3.pp
@@ -0,0 +1,105 @@
+# sanitarium 3 role: it replicates from all core shards (except x1), and
+# sanitizes most data on production on 7 shards, before the data arrives to
+# labs
+# This role installs a 10.1 version which is needed for rbr triggers for
+# the new sanitarium3 server, which runs multi-instance and mariadb 10.1
+
+class role::mariadb::sanitarium3 {
+
+ system::role { 'mariadb::sanitarium':
+ description => 'Sanitarium DB Server',
+ }
+
+ include ::standard
+ include passwords::misc::scripts
+ class { 'role::mariadb::groups':
+ mysql_group => 'labs',
+ mysql_role => 'slave',
+ socket => '/tmp/mysql.s1.sock',
+ }
+
+
+ class {'mariadb::packages_wmf':
+ package => 'wmf-mariadb101',
+ }
+
+ class { 'mariadb::config':
+ basedir => '/opt/wmf-mariadb101',
+ socket => '/tmp/mysql.sock',
+ config => 'role/mariadb/mysqld_config/sanitarium3.my.cnf.erb',
+ ssl => 'puppet-cert',
+ }
+
+ class {'mariadb::service':
+ package => 'wmf-mariadb101',
+ }
+
+ include role::labs::db::common
+ include role::labs::db::check_private_data
+
+ ferm::service { 'mysqld_sanitarium':
+ proto => 'tcp',
+ port => '3311:3317',
+ srange => '$PRODUCTION_NETWORKS',
+ }
+
+ ferm::service { 'gmond_udp':
+ proto => 'udp',
+ port => '8649',
+ srange => '$PRODUCTION_NETWORKS',
+ }
+
+ ferm::service { 'gmond_tcp':
+ proto => 'tcp',
+ port => '8649',
+ srange => '$PRODUCTION_NETWORKS',
+ }
+
+ # One instance per shard using mysqld_multi.
+ # This allows us to send separate replication channels downstream.
+ $folders = [
+ '/srv/sqldata.s1',
+ '/srv/sqldata.s2',
+ '/srv/sqldata.s3',
+ '/srv/sqldata.s4',
+ '/srv/sqldata.s5',
+ '/srv/sqldata.s6',
+ '/srv/sqldata.s7',
+ '/srv/tmp.s1',
+ '/srv/tmp.s2',
+ '/srv/tmp.s3',
+ '/srv/tmp.s4',
+ '/srv/tmp.s5',
+ '/srv/tmp.s6',
+ '/srv/tmp.s7',
+ ]
+
+ file { $folders:
+ ensure => directory,
+ owner => 'mysql',
+ group => 'mysql',
+ mode => '0755',
+ }
+
+ # mysqld_multi wrapper
+ file { '/etc/init.d/mariadb':
+ owner => 'root',
+ group => 'root',
+ mode => '0755',
+ content => template('role/mariadb/sanitarium3.sysvinit.erb'),
+ }
+ file { '/etc/init.d/mysql':
+ ensure => link,
+ target => '/etc/init.d/mariadb',
+ }
+
+ class { 'mariadb::monitor_disk':
+ contact_group => 'admins',
+ }
+
+ class { 'mariadb::monitor_process':
+ process_count => 7,
+ contact_group => 'admins',
+ }
+}
+
diff --git
a/modules/role/templates/mariadb/mysqld_config/sanitarium3.my.cnf.erb
b/modules/role/templates/mariadb/mysqld_config/sanitarium3.my.cnf.erb
new file mode 100644
index 0000000..a11de1a
--- /dev/null
+++ b/modules/role/templates/mariadb/mysqld_config/sanitarium3.my.cnf.erb
@@ -0,0 +1,188 @@
+# Sanitarium -- multiple instances!
+
+[client]
+port = 3311
+socket = /tmp/mysql.s1.sock
+
+[mysqld]
+
+user = mysql
+read_only = 1
+
+# enable socket authentication
+plugin-load = unix_socket=auth_socket.so
+
+skip-external-locking
+skip-name-resolve
+skip-slave-start
+log-slave-updates
+temp-pool
+
+secure_file_priv = /dev/null
+max_connections = 100
+max_connect_errors = 1000000000
+max_allowed_packet = 32M
+connect_timeout = 3
+query_cache_size = 0
+query_cache_type = 0
+event_scheduler = 1
+userstat = 0
+log-warnings = 0
+thread_stack = 192K
+thread_cache_size = 300
+interactive_timeout = 28800
+wait_timeout = 3600
+plugin-load = ha_tokudb
+transaction-isolation = REPEATABLE-READ
+slave_transaction_retries = 4294967295
+slave_parallel_threads = 2
+slave_parallel_max_queued = 16M
+binlog-format = ROW
+expire_logs_days = 7
+sql-mode = IGNORE_BAD_TABLE_OPTIONS
+
+# Needed when using ROW based replication and altering the width of a column,
if not set, the ALTER will fail
+#
https://dev.mysql.com/doc/mysql-replication-excerpt/5.7/en/replication-features-different-data-types.html
+# T73563#3117924
+slave_type_conversions = ALL_NON_LOSSY
+
+table_open_cache = 10000
+table_definition_cache = 50000
+open-files-limit = 400000
+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.05).round %>G
+innodb_log_file_size = 2G
+innodb_flush_log_at_trx_commit = 1
+innodb_flush_method = O_DIRECT
+tokudb_cache_size = <%= (Float(@memorysize.split[0]) *
0.05).round %>G
+tokudb_lock_timeout = 50000
+tokudb_empty_scan = disabled
+tokudb_read_buf_size = 256K
+tokudb_pk_insert_mode = 2
+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 %>
+
+# index_condition_pushdown=off https://github.com/Tokutek/mariadb-5.5/issues/39
+optimizer_switch =
'mrr=on,mrr_cost_based=on,mrr_sort_keys=on,optimize_join_buffer_size=on,engine_condition_pushdown=on,index_condition_pushdown=off'
+
+replicate-wild-ignore-table = mysql.%
+replicate-wild-ignore-table = oai.%
+
+<% scope.lookupvar("::private_wikis").each do |name| -%>
+replicate-wild-ignore-table = <%= name %>.%
+<% end -%>
+
+<% scope.lookupvar("::private_tables").each do |name| -%>
+replicate-wild-ignore-table = %.<%= name %>
+<% end -%>
+
+[mysqld_multi]
+log = /var/log/mysqld_multi.log
+mysqld = /opt/wmf-mariadb10/bin/mysqld_safe
+mysqladmin = /opt/wmf-mariadb10/bin/mysqladmin
+
+[mysqld1]
+
+port = 3311
+socket = /tmp/mysql.s1.sock
+log-bin = s1-bin
+relay-log = s1-rel
+datadir = /srv/sqldata.s1
+pid-file = /srv/sqldata.s1/pid
+tmpdir = /srv/tmp.s1
+server_id = <%= @server_id %>3311
+gtid_domain_id = <%= @gtid_domain_id %>
+
+[mysqld2]
+
+port = 3312
+socket = /tmp/mysql.s2.sock
+log-bin = s2-bin
+relay-log = s2-rel
+datadir = /srv/sqldata.s2
+pid-file = /srv/sqldata.s2/pid
+tmpdir = /srv/tmp.s2
+server_id = <%= @server_id %>3312
+gtid_domain_id = <%= @gtid_domain_id %>
+
+[mysqld3]
+
+port = 3313
+socket = /tmp/mysql.s3.sock
+log-bin = s3-bin
+relay-log = s3-rel
+datadir = /srv/sqldata.s3
+pid-file = /srv/sqldata.s3/pid
+tmpdir = /srv/tmp.s3
+server_id = <%= @server_id %>3313
+gtid_domain_id = <%= @gtid_domain_id %>
+
+[mysqld4]
+
+port = 3314
+socket = /tmp/mysql.s4.sock
+log-bin = s4-bin
+relay-log = s4-rel
+datadir = /srv/sqldata.s4
+pid-file = /srv/sqldata.s4/pid
+tmpdir = /srv/tmp.s4
+server_id = <%= @server_id %>3314
+gtid_domain_id = <%= @gtid_domain_id %>
+
+[mysqld5]
+
+port = 3315
+socket = /tmp/mysql.s5.sock
+log-bin = s5-bin
+relay-log = s5-rel
+datadir = /srv/sqldata.s5
+pid-file = /srv/sqldata.s5/pid
+tmpdir = /srv/tmp.s5
+server_id = <%= @server_id %>3315
+gtid_domain_id = <%= @gtid_domain_id %>
+
+[mysqld6]
+
+port = 3316
+socket = /tmp/mysql.s6.sock
+log-bin = s6-bin
+relay-log = s6-rel
+datadir = /srv/sqldata.s6
+pid-file = /srv/sqldata.s6/pid
+tmpdir = /srv/tmp.s6
+server_id = <%= @server_id %>3316
+gtid_domain_id = <%= @gtid_domain_id %>
+
+[mysqld7]
+
+port = 3317
+socket = /tmp/mysql.s7.sock
+log-bin = s7-bin
+relay-log = s7-rel
+datadir = /srv/sqldata.s7
+pid-file = /srv/sqldata.s7/pid
+tmpdir = /srv/tmp.s7
+server_id = <%= @server_id %>3317
+gtid_domain_id = <%= @gtid_domain_id %>
+
+[mysqldump]
+
+quick
+max_allowed_packet = 32M
diff --git a/modules/role/templates/mariadb/sanitarium3.sysvinit.erb
b/modules/role/templates/mariadb/sanitarium3.sysvinit.erb
new file mode 100644
index 0000000..dc42cb4
--- /dev/null
+++ b/modules/role/templates/mariadb/sanitarium3.sysvinit.erb
@@ -0,0 +1,25 @@
+#!/bin/bash
+
+INSTALL_DIR=/opt/wmf-mariadb101
+
+case "$1" in
+ start)
+ pushd $INSTALL_DIR
+ $INSTALL_DIR/bin/mysqld_multi start 1-7
+ popd
+ ;;
+ stop)
+ pushd $INSTALL_DIR
+ $INSTALL_DIR/bin/mysqld_multi stop 1-7
+ popd
+ ;;
+ status)
+ pushd $INSTALL_DIR
+ $INSTALL_DIR/bin/mysqld_multi report
+ popd
+ ;;
+ *)
+ echo $"Usage: $0 {start|stop|status}"
+ exit 1
+esac
+exit 0
--
To view, visit https://gerrit.wikimedia.org/r/364150
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I7eca200ec1f90712eec65ee592ac070fa78c7168
Gerrit-PatchSet: 1
Gerrit-Project: operations/puppet
Gerrit-Branch: production
Gerrit-Owner: Marostegui <maroste...@wikimedia.org>
_______________________________________________
MediaWiki-commits mailing list
MediaWiki-commits@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
