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

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

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

Для начала стоит разобраться с тем, какой из реализацией технолгии MySQL Galera, Вы будете пользоватся. Помимо Oracle, на рынке есть имплементации от 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 = 003 #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 = 3072 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 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 т.е. переход на снимочное выполнение транзакций. Каждая транзакция становится своего рода независимой песочницей. Снимком данных.
В большинстве случаев, переход дает прирост в скорости на конкурентной записи, но так же возможен эффект фантомного чтения. На своей практике я встречал лишь одно приложение, которое болело фантомностью. Т.е. это приложения использующие СУБД, нужно проверить на возможность работы в этом режиме.

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 таблицы.

На этом пока что все, и как всегда, напоследок:

Если у вас возникнут трудности или потребуется специальный человек, чтобы сделать сказку былью — всегда буду рад помочь! мои контакты — welcome

ссылка на оригинал статьи http://habrahabr.ru/post/253869/