«Идеальный» кластер. Часть 3.1 Внедрение MySQL Multi-Master кластера

В продолжение цикла статей об «Идеальном» кластере хочу поделиться моим опытом развертывания и настройки Multi-Master кластеров MySQL.

Другие мои публикации на тему «Идеального» кластера

• Ускорение и оптимизация PHP-сайта. Какие технологии стоит выбирать при настройке сервера под PHP
• «Идеальный» кластер. Часть 3.1 Внедрение MySQL Multi-Master кластера
• «Идеальный» кластер. Часть 2.2: Высокодоступный и масштабируемый web-сервер, лучшие технологии на страже вашего бизнеса
• О виртуальном кластере на hetzner
• Frontend: NGINX + Keepalived (vrrp) на CentOS
• HAPRoxy для Percona или Galera на CentOS. Его настройка и мониторинг в Zabbix
• Zabbix 2.2 верхом на nginx + php-fpm и mariadb

Для начала стоит разобраться с тем, какой из реализацией технолгии MySQL Galera, Вы будете пользоватся. На рынке есть имплементации Galera от Percona и MariaDB. Именно эти две реализации и поделили львиную долю внедрений MySQL Galera.

Оба форка используют в качестве плагина InnoDB, движок Percona XtraDB Storage Engine.

Этот движок основан на коде InnoDB-plugin и полностью совместимый с ним, но отличается заметно более высокой производительностью, благодаря интеграции патчей от компаний Google и Percona.
В частности, в XtraDB улучшен механизм работы с памятью, улучшена работа подсистемы ввода/вывода InnoDB, добавлена поддержка нескольких потоков чтения и записи, поддержка управления пропускной способностью,
реализация упреждающей выборкой данных (read-ahead), адаптивная установка контрольных точек (adaptive checkpointing), расширены возможности по масштабированию для больших проектов, система организации блокировок адаптирована
для работы на системах с большим числом CPU, добавлены дополнительные возможности для накопления и анализа статистики.

При этом, MariaDB Galera отличается целым рядом других улучшений и возможностей.

• Aria (ранее Maria) — основанное на MyISAM высоконадежное хранилище, отличающееся повышенной устойчивостью и сохранению целостности данных после краха, при полной совместимости с MyISAM. Благодаря ведению лога операций, в случае краха производится откат результатов выполнения текущей операции. Также поддерживается возможность восстановления состояния из любой точки в логе операций (включая поддержку CREATE/DROP/RENAME/TRUNCATE).
• PBXT (PrimeBase XT) — хранилище, разработанное с нуля и поддерживающее мультиверсионный метод организации хранения данных MVCC (multi-version concurrency control), позволяющий избавиться от блокировок при выполнении операций чтения.
  PBXT поддерживает ACID-совместимые транзакции, быстрый откат транзакций и восстановление после некорректного завершения работы сервера. Имеются средства для обеспечения ссылочной целостности данных, поддержка определения внешних ключей (foreign key), каскадных обновлений и удалений данных. Поддерживается возможность прямого потокового ввода и вывода бинарных данных (BLOB) в БД;
• FederatedX — позиционируется в качестве замены разработанного в Sun Microsystems и уже не поддерживаемого хранилища Federated. FederatedX позволяет организовать обращение к удаленным таблицам как к локальным.
  Имеется поддержка транзакций, одновременной установки нескольких соединений к удаленной СУБД, использования операций «LIMIT»;
• OQGRAPH — хранилище для организации иерархических (древовидных) структур и сложных графов (узлов, имеющих множество связей);
• Sphinx — хранилище для построения поисковых движков. Встроенный Sphinx-клиент позволяет MariaDB обмениваться данными с searchd, выполнять поисковые запросы и получать результаты поиска;

Кроме того, в MariaDB Galera 10 появился целый ряд улучшений по сравнению с версией 5.5:

• Новое хранилище Connect, позволяющее организовать доступ к произвольным локальным или удалённым данным, в виде, как если бы они были сохранены в таблице. Например можно ассоциировать содержимое виртуальной таблицы с данными из файла в определённом формате;
• Новое хранилище Cassandra Storage Engine (SE), добавляющее в MariaDB и MySQL поддержку средств для доступа к данным, хранимым в распределённой БД Apache Cassandra. Используя Cassandra SE разработчики получают возможность обращаться к данным и добавлять данные в БД Cassandra при помощи обычных SQL-запросов. При этом используемая в Cassandra модель хранения данных в виде семейства столбцов (ColumnFamily) отображается в форме свойственных для MariaDB/MySQL таблиц, для которых можно применять стандартные SQL-директивы SELECT, INSERT, DELETE и UPDATE, а также выполнять операции объединения (JOIN) с другими таблицами.
• Интеграция хранилища SPIDER с реализацией системы шардинга, позволяющей разносить большие таблицы на несколько серверов. С точки зрения формирования запросов такие таблицы не отличаются от обычных локальных таблиц, но фактически при использовании SPIDER разные порции данных, составляющих одну таблицу, хранятся на разных серверах. Для обеспечения высокой доступности таблиц, распределённых по серверам при помощи SPIDER, могут применяться новые средства репликации.
• Хранилище Sequence для формирования виртуальных таблиц, заполненных возрастающими или убывающими последовательностями (например, seq_1_to_5 или seq_5_to_1_step_2).
• Улучшенная реализация динамических столбцов, позволяющих получить различный набор «виртуальных столбцов» для каждой строки в таблице.
• Добавлена поддержка запросов в формате JSON и возможность интеграции с БД Cassandra;
• Многочисленные оптимизации производительности, позволяющие в MariaDB 10 добиться многократного ускорения некоторых операций по сравнению с MySQL и прошлыми ветками MariaDB. Среди ключевых оптимизаций отмечается поддержка параллельной репликации и развитие системы групповых коммитов. Добавлены дополнительные оптимизации выполнения вложенных запросов, например преобразование выражений «NOT EXISTS» в блоки «IN»;
• Улучшены средства репликации. Обеспечена защита работы реплицируемых slave-серверов от проблем в случае краха.
• Добавлена поддержка репликации данных от нескольких master-серверов (multi-source репликации). Из примеров использования multi-source репликации упоминается решение задач сбора в одном месте данных, разнесённых на разные машины, с целью выполнения аналитических запросов или для создания резервной копии;
• Поддержка глобальных идентификаторов транзакций;
• Возможность использования проверки IF (NOT) EXIST для выражений ALTER TABLE;
• Улучшенный вывод сообщений об ошибках. Все числовые номера ошибок теперь сопровождаются пояснительными текстами.
• Поддержка выражения «SHOW EXPLAIN FOR thread_id» для анализа запроса, выполняемого в заданной нити. Так как «SHOW EXPLAIN» учитывает план выполнения оптимизатором реального запроса, он позволяет получить более близкие к реальности показатели, чем выполнение запроса внутри «EXPLAIN»;
• В InnoDB добавлены дополнительные оптимизации, позволяющие зметно ускорить выполнения транзакций, не выполняющих операции записи и изменения данных. Для выполнения транзакций в режиме чтения добавлена новая команда «TRANSACTION READ ONLY»;
• Оптимизировано выполнение конструкции «LIMIT… ORDER BY»;
• Поддержка автоматического обновления времени (timestamp) в DATETIME;
• Хранимые в памяти таблицы с эффективной поддержкой типов VARCHAR и BLOB;
• Универсальная система накопления статистики об активности и наполнении таблиц для использования оптимизатором запросов, реализованная без привязки к конкретным движкам хранения;
• Поддержка анализа потребления памяти в привязке к отдельной нити;
• Значительное ускорение работы конструкций ALTER TABLE для хранилищ Aria и MyISAM при наличии проверки уникальных ключей;

Улучшения портированные из MySQL 5.6:

• Обновлённый вариант хранилища InnoDB.
• Поддержка движка PERFORMANCE_SCHEMA и связанной с ним базы performance_schema, предоставляющей низкоуровневые средства для мониторинга за выполнением запросов и различными событиями при работе СУБД;
• Режим только для чтения для транзакций в InnoDB, поддержка выражения «TRANSACTION READ ONLY»;
• Оптимизации скорости выполнения запросов вида «ORDER BY… LIMIT».
• Поддержка "--plugin-load-add";
• Возможность выполнения «ALTER TABLE» на лету;
• Установка привилегий для временных таблиц;
• Расширения, связанные с поддержкой кодировок;
• Выражение «GET DIAGNOSTICS»;
• Временные литералы (например, TIME'12:34:56').

От себя хочу добавить, что оба форка так же поддерживают HandlerSocket и Memcached plugin

Более подробное описание стабильного выпуска СУБД MariaDB 10.0, можно найти в источнике на opennet

Почему я выбрал MariaDB Galera 10?

.

MariaDB Galera 10 поддерживает MySQL Query Cache из коробки. Любая инструкция по установке любой из имплементаций MySQL Galera, явно указывает о необходимости отключения Query Cache. В итоге, при переходе с одиночного сервера баз данных на кластерный вариант, скорость чтения сложных запросов падает в разы. А нагрузка на сервер, соизмеримо возрастает.
Percona XtraDB Cluster в версии 5.6 так же приблизились к внедрению полноценного поддержки Query Cache, но тут требуется включать его на «живую», уже после запуска ноды при помощи запросов:

SET GLOBAL query_cache_size =128*1024*1024;
SET GLOBAL query_cache_type = 1;

При включенном Query Cache, до 95% запросов возвращают результат из кеша вместо того что бы выполняются снова.

Хочу сразу дать пару своих замечаний.

Кеша не должно быть много. Самый большой размер, который вообще стоит устанавливать, это не более 512МБ. Даже 512МБ — это очень много, реально нужно меньше. И вот почему:

Если в любой из таблиц, выборка из которой есть в кеше, проиcходят изменения (вставка или изменение строк), то MySQL удаляет из кеша такие выборки. Такой подход ускоряет работу MySQL, но может быть неэффективным для систем с большим количеством запросов на изменение таблиц. Это приводит к тому, что таблицы просто блокируются в режиме Waiting for query cache lock.

Кеш запросов можно представлять себе как хеш, ключами которого являются запросы, а значениями — результаты запросов.
Если использование кеша запросов включено, то при получении запроса MySQL определяет, равны ли первые три символа запроса «SEL». Если да, то MySQL смотрит, есть ли в кеше запросов запись с ключом, равным запросу.

Отсюда следуют два важных правила:

• MySQL выполняет побайтовое сравнение, поэтому запросы, имеющие отличие хотя бы в одном символе (например, SELECT * FROM table и select * from table) будут рассматриваться как два разных запроса. Поэтому необходимо писать запросы в едином стиле;
• В MySQL до версии 5.0 запросы, в начале которых есть пробел или написан комментарий никогда не будут браться из кеша.

Кроме результатов, MySQL хранит в кеше список таблиц, выборка из которых закеширована.

Подробнее о кеше запросов, можно прочитать в источнике на habrahabr

От слов к делу

Думаю, что Вам использовать, Вы уже разобрались. Дальше по тексту я описываю работу с MariaDB Galera 10, но практически все описанное, справедливо и для Percona XtraDB Cluster 5.6.

Если мы переводим одиночную инсталяцию MySQL в кластерное исполнение:

• Убедимся что все наши базы данных не содержат таблиц с движком MyISAM
  

  SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'myisam';

• Убедимся что у всех таблиц в наших базах данных есть первичные ключи:
  
  

  SELECT table_catalog, table_schema, table_name, engine
  	FROM information_schema.tables
  	WHERE (table_catalog, table_schema, table_name) NOT IN
  	(SELECT table_catalog, table_schema, table_name
  	FROM information_schema.table_constraints
  	WHERE constraint_type = 'PRIMARY KEY')
  	AND table_schema NOT IN ('information_schema', 'pg_catalog');

Для решения первой проблемы есть 2 пути:

# Вариант 1

mysql имя_базы_данных -e "show table status where Engine='MyISAM';" | awk 'NR>1 {print "ALTER TABLE "$1" ENGINE = InnoDB;"}' | mysql имя_базы_данных

# Вариант 2

mysql имя_базы_данных -e "show table status where Engine='MyISAM';" | awk '{print $1}' | xargs -t -i pt-online-schema-change --alter-foreign-keys-method=auto --alter "ENGINE=InnoDB" --execute --statistics --set-vars="wait_timeout=10000,innodb_lock_wait_timeout=10,lock_wait_timeout=180" --progress=time,1 D=имя_базы_данных,t={}

Для небольших таблиц первый вариант срабатывает довольно-таки быстро. А вот с большими таблицами возникают проблемы. Так как конвертация будет выполняться долго, таблица будет заблокирована и все операции с ней станут невозможными, что непременно скажется на оказании услуг/сервисов. Для решения этой проблемы нам поможет утилита pt-online-schema-change из комплекта percona-toolkit.

Ставится эта утилита из репозитария для CentOS:

rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

Важно Необходимо, чтобы у конвертируемой таблицы был или первичный (PRIMARY), или уникальный (UNIQUE) ключ, иначе выдаст ошибку, например такую:

Cannot chunk the original table `database`.`NAMETABLE01_NOKEY`: There is no good index and the table is oversized. at /usr/bin/pt-online-schema-change line 5442.

Для решения второй проблемы, увы, путь только один — добавить PRIMARY или UNIQUE ключ через ALTER.

All tables should have a primary key (multi-column primary keys are supported). DELETE operations are unsupported on tables without a primary key. Also, rows in tables without a primary key may appear in a different order on different nodes.

Т.е. возможны выпадения нод, дедлоки и прочие проблемы. Плюс сбивается порядок строк. Это нужно чинить в первую очередь.

Если эти проблемы мы оставили позади, то перейдем к установке и настройке самого сервера БД.

cat > /etc/yum.repos.d/MariaDB.repo << EOL
[mariadb]
# MariaDB 10.0 CentOS repository list - created 2015-02-18 14:04 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOL

yum install MariaDB-Galera-server MariaDB-client rsync galera ntp nscd

chkconfig nscd on $$ /etc/init.d/nscd start

# Нужно отключить selinux, это требование разработчиков MariaDB

sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
echo 0 > /selinux/enforce

# На всех нодах, должно быть правильно установленно время, это обязательно. Иначе вы столкнётесь с тем что, при SST ноды с донора, синхронизируемая нода будет просто чего-то ждать, без каких бы то ни было признаком активности.

yum install ntp -y
chkconfig ntpd on
/etc/init.d/ntpd stop
ntpdate  165.193.126.229 0.ru.pool.ntp.org 1.ru.pool.ntp.org 2.ru.pool.ntp.org 3.ru.pool.ntp.org
/etc/init.d/ntpd start

Для настройки серверов MariaDB и кластеров Galera, я написал скрипт, он создает заготовку конфигурационного файла, индивидуально для каждого сервера.

Хочу еще раз сказать, на выходе мы получаем заготовку, требующую последущего редактирования.

#!/bin/sh

# wget --no-check-certificate -q -O - 'https://cloud.sycraft.info/index.php/s/7bf49db6da59f6d48d61abcb2c4b4791/download' | bash -x -

# fetch -o mysqld_config.sh 'https://cloud.sycraft.info/index.php/s/7bf49db6da59f6d48d61abcb2c4b4791/download'
# sh mysqld_config.sh

if [ "$(uname)" == 'Linux' ]; then
    IBS=innodb_buffer_pool_size\ \=\ $((`free -m | grep Mem | awk '{print $2}'`*60/100000))G;
    socket=socket=\/var\/lib\/mysql\/mysql.sock;
    DB=datadir=\/var\/lib\/mysql;
    conf=\/etc;
    cpu=$((`cat /proc/cpuinfo | grep -c processor`*2))
else
    IBS=innodb_buffer_pool_size\ \=\ $((`dmesg |grep real\ memory | awk '{print $5}' |cut -c 2- | tail -1`*60/100000))G;
    conf=\/var\/db\/mysql;
    cpu=$((`sysctl hw.ncpu | awk '{print $2}'`*2))
fi

mkdir -p ~/backup/mysql > /dev/null 2>&1
mkdir $conf/mysql.d > /dev/null 2>&1
mkdir $conf/mysql.d/ssl > /dev/null 2>&1
mkdir /var/log/mysql > /dev/null 2>&1

chown mysql:mysql $conf/mysql.d
chown mysql:mysql $conf/mysql.d/ssl
chown -R mysql:mysql /var/log/mysql

if [ -f $conf/my.cnf ]; then
cp $conf/my.cnf ~/backup/mysql/my.cnf.`date +%Y-%m-%d_%H-%M`
fi

if [ -f $conf/mysql.d/000-galera.cnf ]; then
cp $conf/mysql.d/000-galera.cnf ~/backup/mysql/000-galera.cnf.`date +%Y-%m-%d_%H-%M`
fi

if [ -f $conf/mysql.d/001-server.cnf ]; then
cp $conf/mysql.d/001-server.cnf ~/backup/mysql/001-server.cnf.`date +%Y-%m-%d_%H-%M`
fi

if [ -f $conf/mysql.d/002-myisam.cnf ]; then
cp $conf/mysql.d/002-myisam.cnf ~/backup/mysql/002-myisam.cnf.`date +%Y-%m-%d_%H-%M`
fi

if [ -f $conf/mysql.d/003-rep-master.cnf ]; then
cp $conf/mysql.d/003-rep-master.cnf ~/backup/mysql/003-rep-master.cnf.`date +%Y-%m-%d_%H-%M`
fi

if [ -f $conf/mysql.d/004-rep-slave.cnf ]; then
cp $conf/mysql.d/004-rep-slave.cnf ~/backup/mysql/004-rep-slave.cnf.`date +%Y-%m-%d_%H-%M`
fi

if [ -f $conf/mysql.d/005-mariadb-opt.cnf ]; then
cp $conf/mysql.d/005-mariadb-opt.cnf ~/backup/mysql/005-mariadb-opt.cnf.`date +%Y-%m-%d_%H-%M`
fi

if [ -f $conf/mysql.d/006-ssl.cnf ]; then
cp $conf/mysql.d/006-ssl.cnf ~/backup/mysql/006-ssl.cnf.`date +%Y-%m-%d_%H-%M`
fi

if [ -f $conf/mysql.d/007-handlersocket.cnf ]; then
cp $conf/mysql.d/007-handlersocket.cnf ~/backup/mysql/007-handlersocket.cnf.`date +%Y-%m-%d_%H-%M`
fi

if [ -f $conf/mysql.d/008-threadpool.cnf ]; then
cp $conf/mysql.d/008-threadpool.cnf ~/backup/mysql/008-threadpool.cnf.`date +%Y-%m-%d_%H-%M`
fi

cat > $conf/my.cnf << EOL
!includedir $conf/mysql.d/
EOL

# galera-only
cat > $conf/mysql.d/000-galera.cnf << EOL
[mysqld]
wsrep_provider = /usr/lib64/galera/libgalera_smm.so

wsrep_cluster_address = gcomm://192.168.0.30,192.168.0.40,192.168.0.41,192.168.0.74,192.168.0.75,192.168.0.76,192.168.0.161

# Node4 address
wsrep_node_address = 192.168.0.161

# Cluser name
wsrep_cluster_name = cluster
wsrep_node_name = prod-db-new-04

#wsrep_slave_threads = $cpu
innodb_autoinc_lock_mode = 2

# SST method
wsrep_sst_method = xtrabackup
wsrep_sst_auth = "sstuser:s3cretPass"
##wsrep_sst_method = rsync

wsrep_retry_autocommit = 3
wsrep_provider_options = "gcache.size=5G; repl.commit_order=1; gmcast.segment=2"
EOL

cat > $conf/mysql.d/001-server.cnf << EOL
[mysqld]
symbolic-links=0
default_storage_engine = InnoDB
innodb_file_per_table = 1
event_scheduler=on
#character-set-server = utf8

$DB
$socket

# network
connect_timeout = 600000
wait_timeout = 28800
max_connections = 600
max_allowed_packet = 512M
max_connect_errors = 10000
net_read_timeout = 600000
connect_timeout = 600000
net_write_timeout = 600000

# innodb engine settings
innodb_open_files = 512
$IBS
innodb_buffer_pool_instances = 2
innodb_file_format = barracuda
innodb_locks_unsafe_for_binlog = 1
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
transaction-isolation = READ-COMMITTED
innodb-data-file-path = ibdata1:10M:autoextend
innodb-log-file-size = 256M
innodb_log_buffer_size = 8M

# performance settings
skip-name-resolve
skip-external-locking
skip-innodb_doublewrite

query_cache_size                = 128M
query_cache_type                = 1
query_cache_min_res_unit        = 2K

join_buffer_size        = 8M
read_rnd_buffer_size    = 3M
table_definition_cache  = 2048
table_open_cache        = 2048
thread_cache_size       = 128
tmp_table_size          = 2048M
max_heap_table_size     = 2048M

log_error	=	/var/log/mysql/mysql-error.log
#slow_query_log_file	=	/var/log/mysql/mysql-slow.log
EOL

# myisam
cat > $conf/mysql.d/002-myisam.cnf << EOL
[mysqld]
key_buffer_size = 512M
EOL

# rep-master
cat > $conf/mysql.d/003-rep-master.cnf << EOL
[mysqld]
#log-bin = /var/log/mysql/mysql-bin

# cluster
# binlog_format=ROW

# single installation
binlog_format=MIXED

server_id = 226
sync-binlog = 0
expire-logs_days = 3
max-binlog-size = 1G
log-slave-updates
EOL

# rep-slave
cat > $conf/mysql.d/004-rep-slave.cnf << EOL
[mysqld]
slave-skip-errors = 1062
log_slave_updates = 1
slave_type_conversions=ALL_NON_LOSSY
relay-log = /var/log/mysql/mysql-relay-bin
relay-log-index = /var/log/mysql/mysql-relay-bin.index
relay-log-info-file = /var/log/mysql/mysql-relay-log.info
skip-slave-start
# replicate-rewrite-db=from_name->to_name
# replicate-ignore-table=db_name.table_name
# replicate-wild-ignore-table=db_name.table_name
EOL

# mariadb-opt
cat > $conf/mysql.d/005-mariadb-opt.cnf << EOL
[mysqld]
optimizer_switch='derived_merge=off,derived_with_keys=off'
EOL

# ssl
cat > $conf/mysql.d/006-ssl.cnf << EOL
#[mysqld]
#ssl-ca          =  $conf/mysql.d/ssl/ca-cert.pem
#ssl-cert        =  $conf/mysql.d/ssl/server-cert.pem
#ssl-key         =  $conf/mysql.d/ssl/server-key.pem
EOL

# handlersocket
cat > $conf/mysql.d/007-handlersocket.cnf << EOL
[mysqld]
#handlersocket_address=127.0.0.1
#handlersocket_port=9998
#handlersocket_port_wr=9999
EOL

# threadpool
cat > $conf/mysql.d/008-threadpool.cnf << EOL
[mysqld]
thread_handling = pool-of-threads
thread_pool_size = $cpu
EOL

Жизнь не стоит на месте и я так же как и вы, продолжаю непрерывно развиваться, последнюю версию скрипта, лучше брать сразу c ее постоянной страницы, вероятно с момента написания статьи, многое в нем уже изменилось.

Пояснения к конфигу и скрипту генерации

wsrep_sst_method=xtrabackup

Если использовать режим rsync, то в момент синхронизации ноды с донора, донор будет полностью блокирован на запись. В режиме xtrabackup же, блокировка будет длиться лишь несколько секунд, пока xtrabackup «прицепится» к базе.
Если вы используете HAProxy как это описано тут HAPRoxy для Percona или Galera на CentOS. Его настройка и мониторинг в Zabbix то что бы работать с сервером, пока тот находится в режиме донора, нам нужно отредактировать скрипт clustercheck на нодах.

# Заменив строку

AVAILABLE_WHEN_DONOR=${3:-0}

# на строку

AVAILABLE_WHEN_DONOR=1

В таком режиме, в случае полного падения всех нод кластера, мы сможем сократить простой на синхронизацию нод до минимума.

transaction_isolation=REPEATABLE-READ

стоит попробовать поменять на transaction-isolation = READ-COMMITTED т.е. переход на снимочное выполнение транзакций. Каждая транзакция становится своего рода независимой песочницей. Снимком данных.
Это нечто похожее на уровень изоляции Oracle. Все выражения SELECT… FOR UPDATE и SELECT… LOCK IN SHARE MODE блокируют только индексные записи и не блокируют интервал перед ними. Поэтому они позволяют свободно добавлять новые записи после заблокированных. UPDATE и DELETE, которые используют уникальный индекс и уникальные условия поиска, блокируют только найденную индексную запись, и не блокируют интервал перед ней. Но в UPDATE и DELETE диапазонного типа в InnoDB должны установить блокировку следующего ключа или интервальную блокировку и блокировать добавления другими пользователями в интервал, покрытый диапазоном. Это необходимо, т.к. «фантомные строки» должны быть блокированы для успешной работы репликации и восстановления в MySQL. Согласованное чтение работает как и в Oracle: каждое согласованное чтение, даже внутри одной транзакции, устанавливает и читает свой собственный снимок.
В большинстве случаев, переход дает прирост в скорости на конкурентной записи, но так же возможен эффект фантомного чтения. На своей практике я встречал лишь одно приложение, которое болело фантомностью. Т.е. это приложения использующие СУБД, нужно проверить на возможность работы в этом режиме.

innodb_flush_log_at_trx_commit = 2

Значение «1» означает, что любая завершенная транзакция будет синхронно сбрасывать лог на диск. Это вариант по умолчанию, он является самым надежным с точки зрения сохранности данных, но самым медленным по скорости работы.
Значение «2» делает то же самое, только сбрасывает лог не на диск, а в кеш операционной системы (т.е. не происходит flush после каждой операции). Это значение подойдет в большинстве случаев, т.к. не выполняет дорогой операции записи после каждой транзакции. При этом лог пишется на диск с задержкой в несколько секунд, что весьма безопасно с точки зрения сохранности данных.
Но у нас кластер и в случае краха, данные все равно будут переданы с донора. Главное что бы транзакция закомитилась на других нодах. Тогда данные мы получим при SST

innodb_buffer_pool_instances = 2

По умолчанию InnoDB использует для Buffer Pool один инстанс.
При этом есть возможность выделить несколько блоков — и работает с ними MySQL в InnoDB в ряде случаев гораздо эффективнее. Это связанно с меньшими блокировками кеша при записи данных.

innodb_file_format = barracuda

Этот формат самый «новый» и поддерживает компрессию. Это позволяет снизить нагрузку на IO (диски) путём использования сжатия. Так же как рекомендация можно использовать размер блока записи 16КБ.

Вот пример alter’a:

ALTER TABLE `t1` ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16;

Вот результаты тестирования скорости и размера данных при сжатии.
Но есть минусы сжатия. На сжатых таблицах ALTER-ы будут происходить значительно дольше, как известно ALTER, как и любой DDL, блокирует таблицу и вместе с ней весь кластер. ALTER — это не транзакционная инструкция, а значит не реплицируются ROW-бинарными diff-ами а передаются в виде запосов. И пока этот запрос не исполнится на всех нодах кластера, все коммиты будут заморожены.
Т.е. сжатие имеет смысл делать либо на больших таблицах, где не планируются DDL в принципе, либо на одиночных инстансах mysql.

innodb_flush_method = O_DIRECT

Сброс данных минуя дисковый кеш. Это нужно для исключения двойной буферизации данных в кеше innodb_buffer_pool и кеше файловой системы. Позволит более рационально использовать оперативную память.

Стоит добавить важный с точки зрения производительности параметр skip-innodb_doublewrite

Even though double write requires each page written twice its overhead is far less than double. Write to double write buffer is sequential so it is pretty cheap. It also allows Innodb to save on fsync()s – instead of calling fsync() for each page write Innodb submits multiple page writes and calls fsync() which allows Operating System to optimize in which order writes are executed and use multiple devices in parallel. This optimization could be used without doublewrite though, it was just implemented at the same time. So in general I would expect no more than 5-10% performance loss due to use of doublewrite.

tmp_table_size = 2048M
max_heap_table_size = 2048M

Очень интересные параметры, их значения нужно выжать на максимум. Это позволит сократить количество создаваемых на диске временных таблиц. Как правило, именно создаение временных таблиц на диске, занимает большее количество времени на сортировках, группировках и других сложных select.

optimizer_switch='derived_merge=off,derived_with_keys=off'

Бывают проблемы с совместимостью приложения с базой, после перехода на percona 5.6 и galera 10. Наиболее значительные из них стоит сразу предупредить параметром

thread_handling = pool-of-threads
thread_pool_size = количество_ядер

Так же, стоит использовать thread_pool

wsrep_retry_autocommit = 3

Важно! Если в базе дедлок, коммиты будут ретраиться, т.е. нода не будет выпадать из кластера при первом же чихе, а будет дальше работать и мы не теряем коммит.

wsrep_provider_options = «gcache.size=5G; repl.commit_order=1; gmcast.segment=2»

Вот подробное описание, эти параметры я обычно ставлю по умолчанию всегда.

Параметр wsrep_replicate_myisam=1 это почти 100% гарантия смерти кластера если там появится хоть одна боевая myisam таблица.

Данная фича до сих пор экспериментальная и ее включение добавляет к ROW (на базе бинарных diff снимков) репликации еще и statement, те как и при репликации DDL команд. Это значит постоянные конфликты, блокировки и развал кластера после любого дедока myisam таблицы.

Если у вас возникнут вопросы, трудности или потребуется совет:
Мои контакты в профиле