diff --git a/map.jinja b/map.jinja --- a/map.jinja +++ b/map.jinja @@ -6,6 +6,7 @@ 'lib': '/usr/lib', 'man': '/usr/share/man', 'sbin': '/usr/sbin', + 'share': '/usr/share', }, 'FreeBSD' : { 'etc': '/usr/local/etc', @@ -14,6 +15,7 @@ 'lib': '/usr/local/lib', 'man': '/usr/local/man', 'sbin': '/usr/local/sbin', + 'share': '/usr/local/share', }, }, default='Debian') %} @@ -132,7 +134,7 @@ 'emacs': 'emacs-nox11', 'exiftool': 'p5-Image-ExifTool-devel', 'imagemagick': 'ImageMagick', - 'mariadb': 'mariadb101-server', + 'mariadb': 'mariadb102-server', 'node': 'node', 'pear': 'pear', 'phpcs': 'pear-PHP_CodeSniffer', diff --git a/pillar/nodes/nodes.sls b/pillar/nodes/nodes.sls --- a/pillar/nodes/nodes.sls +++ b/pillar/nodes/nodes.sls @@ -32,6 +32,9 @@ roles: - devserver - saltmaster + - dbserver-mysql + zfs: + pool: arcology network: ipv4_interface: igb0 ipv4_address: 163.172.49.16 diff --git a/roles/dbserver-mysql/init.sls b/roles/dbserver-mysql/init.sls new file mode 100644 --- /dev/null +++ b/roles/dbserver-mysql/init.sls @@ -0,0 +1,10 @@ +# ------------------------------------------------------------- +# Salt — Database server — MySQL +# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +# Project: Nasqueron +# Created: 2017-10-27 +# License: Trivial work, not eligible to copyright +# ------------------------------------------------------------- + +include: + - .mysql-server diff --git a/roles/dbserver-mysql/mysql-server/cnf.sls b/roles/dbserver-mysql/mysql-server/cnf.sls new file mode 100644 --- /dev/null +++ b/roles/dbserver-mysql/mysql-server/cnf.sls @@ -0,0 +1,58 @@ +# ------------------------------------------------------------- +# Salt — Database server — MySQL +# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +# Project: Nasqueron +# Created: 2017-10-27 +# License: Trivial work, not eligible to copyright +# ------------------------------------------------------------- + +{% from "map.jinja" import dirs with context %} + +{% set use_zfs = salt['node.has']('zfs:pool') %} + +# ------------------------------------------------------------- +# Required directories +# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + +/var/run/mysqld: + file.directory: + - user: mysql + - group: mysql + - dir_mode: 755 + +/var/db/mysql: + file.directory: + - user: mysql + - group: mysql + - dir_mode: 755 + +{{ dirs.etc }}/mysql: + file.directory: + - user: root + - group: mysql + - dir_mode: 755 + +# ------------------------------------------------------------- +# Configuration files +# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + +{{ dirs.etc }}/my.cnf: + file.managed: + - source: salt://roles/dbserver-mysql/mysql-server/files/my.cnf + - template: jinja + - context: + nodename: {{ grains['id'] }} + etc: {{ dirs.etc }} + share: {{ dirs.share }} + use_zfs: {{ use_zfs }} + +{{ dirs.etc }}/mysql/stopwords.txt: + file.managed: + - source: salt://roles/dbserver-mysql/mysql-server/files/stopwords.txt + +/etc/rc.conf.d/mysql: + file.managed: + - source: salt://roles/dbserver-mysql/mysql-server/files/mysql.rc + - template: jinja + - context: + use_zfs: {{ use_zfs }} diff --git a/roles/dbserver-mysql/mysql-server/files/my.cnf b/roles/dbserver-mysql/mysql-server/files/my.cnf new file mode 100644 --- /dev/null +++ b/roles/dbserver-mysql/mysql-server/files/my.cnf @@ -0,0 +1,125 @@ +# ------------------------------------------------------------- +# MySQL configuration +# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +# Project: Nasqueron +# Created: 2015-04-21 +# License: Trivial work, not eligible to copyright +# ------------------------------------------------------------- + +# ------------------------------------------------------------- +# Clients +# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + +[client] +port = 3306 +socket = /var/run/mysqld/mysqld.sock + +[mysql] +prompt = \u@\h [\d]>\_ + +[mysqldump] +quick +max_allowed_packet = 32M + +# ------------------------------------------------------------- +# Server wrapper +# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + +[mysqld_safe] +pid-file = /var/run/mysqld/mysqld.pid +socket = /var/run/mysqld/mysqld.sock +nice = 0 + +# ------------------------------------------------------------- +# Generic server settings +# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + +[mysqld] +user = mysql +pid-file = /var/run/mysqld/mysqld.pid +socket = /var/run/mysqld/mysqld.sock +port = 3306 +basedir = /usr +tmpdir = /tmp +lc-messages-dir = {{ share }}/mysql + +sql_mode = STRICT_ALL_TABLES +explicit_defaults_for_timestamp + +log_bin +log_basename = {{ nodename }} +binlog_cache_size = 1M +max_binlog_size = 1000M +binlog_format = MIXED + +{% if use_zfs %} +datadir = /var/db/mysql/mysql-root +{% else %} +datadir = /var/db/mysql +{% endif %} + +# ------------------------------------------------------------- +# InnoDB / XtraDB configuration +# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + +default-storage-engine = InnoDB +innodb_buffer_pool_size = 410M + +{% if use_zfs %} +innodb_data_home_dir = /var/db/mysql/mysql-innodb-data +innodb_log_group_home_dir = /var/db/mysql/mysql-innodb-logs + +innodb_file_per_table = off + +skip-innodb_doublewrite +{% endif %} + +# ------------------------------------------------------------- +# Performance schema +# +# Wikimedia production servers on s3 have this configuration +# with only 100MB of overhead. This is an acceptable cost +# for a valuable information. +# +# Reference: https://phabricator.wikimedia.org/T99485 +# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + +performance_schema = 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 + +# ------------------------------------------------------------- +# Applications configuration +# +# :: MediaWiki +# :: Phabricator +# :: OTRS +# +# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + +# MediaWiki +character_set_server = binary +character_set_filesystem = binary +collation_server = binary + +# Phabricator +max_allowed_packet = 32M +ft_stopword_file = {{ etc }}/mysql/stopwords.txt +ft_min_word_len = 3 +ft_boolean_syntax = ' |-><()~*:""&^' + +# OTRS +key_buffer_size = 32M diff --git a/roles/dbserver-mysql/mysql-server/files/mysql.rc b/roles/dbserver-mysql/mysql-server/files/mysql.rc new file mode 100644 --- /dev/null +++ b/roles/dbserver-mysql/mysql-server/files/mysql.rc @@ -0,0 +1,16 @@ +# ------------------------------------------------------------- +# Database server — MySQL — rc configuration +# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +# Project: Nasqueron +# Created: 2017-11-05 +# License: Trivial work, not eligible to copyright +# ------------------------------------------------------------- + +mysql_enable="YES" +mysql_pidfile="/var/run/mysqld/mysqld.pid" + +{% if use_zfs %} +mysql_dbdir=/var/db/mysql/mysql-root +{% else %} +mysql_dbdir=/var/db/mysql +{% endif %} diff --git a/roles/dbserver-mysql/mysql-server/files/stopwords.txt b/roles/dbserver-mysql/mysql-server/files/stopwords.txt new file mode 100644 --- /dev/null +++ b/roles/dbserver-mysql/mysql-server/files/stopwords.txt @@ -0,0 +1,50 @@ +the +be +and +of +a +in +to +have +to +it +I +that +for +you +he +with +on +do +say +this +they +at +but +we +his +from +that +not +by +or +as +what +go +their +can +who +get +if +would +all +my +will +as +up +there +so +its +us +in +on diff --git a/roles/dbserver-mysql/mysql-server/init.sls b/roles/dbserver-mysql/mysql-server/init.sls new file mode 100644 --- /dev/null +++ b/roles/dbserver-mysql/mysql-server/init.sls @@ -0,0 +1,12 @@ +# ------------------------------------------------------------- +# Salt — Database server — MySQL +# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +# Project: Nasqueron +# Created: 2017-10-27 +# License: Trivial work, not eligible to copyright +# ------------------------------------------------------------- + +include: + - .software + - .zfs + - .cnf diff --git a/roles/dbserver-mysql/mysql-server/software.sls b/roles/dbserver-mysql/mysql-server/software.sls new file mode 100644 --- /dev/null +++ b/roles/dbserver-mysql/mysql-server/software.sls @@ -0,0 +1,18 @@ +# ------------------------------------------------------------- +# Salt — Database server — MySQL +# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +# Project: Nasqueron +# Created: 2017-10-27 +# License: Trivial work, not eligible to copyright +# ------------------------------------------------------------- + +{% from "map.jinja" import packages with context %} + +# ------------------------------------------------------------- +# MySQL server +# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + +mysql_server_software: + pkg.installed: + - pkgs: + - {{ packages.mariadb }} diff --git a/roles/dbserver-mysql/mysql-server/zfs.sls b/roles/dbserver-mysql/mysql-server/zfs.sls new file mode 100644 --- /dev/null +++ b/roles/dbserver-mysql/mysql-server/zfs.sls @@ -0,0 +1,45 @@ +# ------------------------------------------------------------- +# Salt — Database server — MySQL +# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +# Project: Nasqueron +# Created: 2017-10-27 +# License: Trivial work, not eligible to copyright +# ------------------------------------------------------------- + +{% if salt['node.has']('zfs:pool') %} + +{% set tank = salt['node.get']("zfs:pool") %} + +{{ tank }}/mysql-root: + zfs.filesystem_present: + # This one is optimized for MyISAM + - properties: + mountpoint: /var/db/mysql + compression: lz4 + recordsize: 8K + +{% for mysqldir in ['innodb-data', 'innodb-logs'] %} +/var/db/mysql/mysql-{{ mysqldir }}: + file.directory: + - user: mysql + - group: mysql + - dir_mode: 711 +{% endfor %} + +{{ tank }}/mysql-innodb-data: + zfs.filesystem_present: + - properties: + mountpoint: /var/db/mysql/mysql-innodb-data + compression: lz4 + recordsize: 16K + primarycache: metadata + +{{ tank }}/mysql-innodb-logs: + zfs.filesystem_present: + - properties: + mountpoint: /var/db/mysql/mysql-innodb-logs + compression: lz4 + recordsize: 128K + primarycache: metadata + +{% endif %} diff --git a/top.sls b/top.sls --- a/top.sls +++ b/top.sls @@ -19,6 +19,7 @@ - roles/saltmaster 'ysul': - roles/paas-jails + - roles/dbserver-mysql - roles/webserver-core - roles/webserver-legacy - roles/webserver-varnish