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,8 @@
roles:
- devserver
- saltmaster
+ 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,50 @@
+# -------------------------------------------------------------
+# 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:
+ 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
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,124 @@
+# -------------------------------------------------------------
+# 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
+binlog_cache_size = 1M
+max_binlog_size = 1000M
+bin_log_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/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,53 @@
+# -------------------------------------------------------------
+# 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['pillar.get']("nodes:" + grains['id'] + ":zfs:pool", '') %}
+
+# -------------------------------------------------------------
+# Mounting point directories
+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
+
+{% for mysqldir in ['innodb-data', 'innodb-logs', 'root'] %}
+/var/db/mysql/mysql-{{ mysqldir }}:
+ file.directory:
+ - user: mysql
+ - group: mysql
+ - dir_mode: 711
+{% endfor %}
+
+# -------------------------------------------------------------
+# ZFS volumes
+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
+
+{{ 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
+
+{{ tank }}/mysql-root:
+ zfs.filesystem_present:
+ # This one is optimized for Aria/MyISAM
+ - properties:
+ mountpoint: /var/db/mysql/mysql-root
+ compression: lz4
+ recordsize: 8k
+
+{% 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