Senior System Administrator & LiveLinux.ru
0,0
рейтинг
24 марта 2015 в 17:08

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

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





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


Для начала стоит разобраться с тем, какой из реализацией технолгии 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 таблицы.


Если у вас возникнут вопросы, трудности или потребуется совет:
Мои контакты в профиле
Aleksey Zhadan @SyCraft
карма
33,0
рейтинг 0,0
Senior System Administrator & LiveLinux.ru
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Спецпроект

Самое читаемое Разработка

Комментарии (71)

  • 0
    Каковы потери производительности, которые уходят на согласование запроса со всеми мастерами(режим-то синхронный)? Желательно в процентах и в секундах.
    Соединены ли у Вас мастера в рамках ЛВС или через Интернет?
    • 0
      Кластер через интернет — очень плохая идея, не смотря на то что можно меня gmcast.segment=, проблемы будут. Этого делать не стоит. Разве что у Вас нет своей собственной оптики между ДЦ.
      Потери производительности безусловно есть, но в секундах они не измеряются точно, это мс. В процентах — не представляю как это вывести. Просто, считайте что сеть должна быть железобетонная и сервера одинаковые или близкие по конфигурации дисков и процессора, тогда потери будут минимальные.
      • 0
        А если очень хочется? Между разными ДЦ в разных странах. Нагрузка небольшая, каналы относительно стабильны и как правило плавают в диапазоне от 200 до 800 мегабит на коннект. Хочется мультимастер для биллинга и подобных штук.

        Каких стоит ожидать проблем?
        • 0
          По моим изысканиям всё упирается в RTT. Т.к. на каждый запрос(или коммит, точно не скажу) происходит согласование со всеми остальными мастерами, и когда они дадут согласие, тогда только сервер получивший запрос начнёт его выполнять. Посему маленький запрос на INSERT может вместо пары микросекунд выполняться до 200милисекунд из-за того что согласование произошло между серверами стоящими в России и Америке, например.

          А ещё всё это становится более шатким, если Вы хотите как я иметь 5,6,7… мастеров.
          • 0
            Особенно плохо, когда связь периодически рвется или затрудняется.
        • 0
          Нужно держать разные участки кластера в разных gmcast.segment, тогда что то еще получится.
          Подробнее стоит почитать о gmcast.segment в документации по galera 3
  • 0
    innodb_file_per_table = 1
    

    это разве хороший выбор в плане производительности на больших базах?

    При репликации подобной схемой, автоинкримент на мастерах не затрагивается auto_increment_increment?
    • 0
      Автоинкремент вычисляется автоматически, он равен числу членов кворума. Т.е. количество нод СУБД и арбиттраторов.

      innodb_file_per_table=1 нужен как минимум, для нормальной работы xtrabackup.
      • 0
        Т.е. я правильно понимаю, что автоинкримент сквозной будет между мастерами, без пропусков? Не совсем понял механизм какой будет назначения primary key если я на двух серверах с потеряной связностью вставлю 100 записей в таблицу.
        • 0
          не будет коммита, если не будет коммита на всех нодах кворума. Так что ничего не сможет вставится при потере связанности. Нода без кворума переходит в точно для чтения или вообще закрывается.
          • 0
            насколько я знаю, это не так
            по крайней мере было не так, когда я присматривадся galera
            нет никакого синхронного коммита, каждый writeset проходит некую сертификацию, проверку на отсутствие конфликтов, и если она пройдена, транзакция считается закоммиченной на всех нодах, что будет по факту на каждой из нод — как повезет
            что-то поменялось с тех пор?
  • 0
    А разве query_cache не рекомендуют отключать по той причине, что данные и так кешируются в буферном пуле innodb?
    • 0
      Нет его отключают совершенно по другой причине. Его не внедряли ранее, так как очень трудно согласовать кеш между всеми нодами и на первых порах это приводило к дедлоку по причине кеша. Те на одной ноде поменялась запись и значит кеш должен сбросится на всех. А допустим где то сервер под нагрузкой и кеш не сбрасывается и в итоге все ноды ждут сброса кеша на одной из нод. В итоге они напряглись и починили это. Теперь работает как часы, главное выполнять рекомендации.
      innodb
      • 0
        Как я понимаю, главная проблема как раз в том что он однопоточный, такая себе критическая секция. Изначально создавался более похоже на хак чтобы ускорить работу не слишком быстрых на то время подсистем хранения. При вытеснении из кеша он лочится целиком, потому для систем с частыми вставками/обновлениями скорость выборок может существенно падать и создаваться затыки.
        • 0
          Похоже на то. К сожаления, раскопать, как они починили это в текущей реализации, мне пока что не удалось. Но факт — работает.
      • 0
        Вроде «раньше» советовали его вырубать по той причине, что однопоточный он. И вся мощь расшибалась лбом об мьютексы…
        Но вроде в случае мастер-мастер это может быть не правдой, только как проверить?
        • 0
          опытным путем, так как это почти не документированная фича сейчас.
        • 0
          Не, там в другом проблема. То есть в мьютексах, конечно. В момент когда нужно его очистить, в случае, если содержимое таблиц, к которым обращаются хранящиеся в нём запросы, поменялось, он держит глобальный мьютекс и все остальные запросы стоят. Такое особенно заметно при высокой нагрузке и большом размере query cache. А пока он себя обновить не решил — всё многопоточно. Случай мастер-мастера здесь роли не играет. Его фиксили, но недофиксили: там с архитектурой какие-то проблемы. Поэтому лучше просто держать маленьким: 256 MB всяко быстрее обновится, чем 4G.
          • 0
            Согласен полностью!
  • 0
    А зачем согласовывать (или синхронизировать) кеш? Как это влияет на работу кластера и целостность данных?
    Возможен ведь вариант при котором с одной ноды будут читаться данные из одной таблицы, а на другой ноде будет чтение из других таблиц или например будет вестись только запись без чтения или чтения будет гораздо меньше.
    И все же, зачем query_cache если бд в innodb?
    Ну и для примера, допустим размер бд > 50 Гб, при этом активные данные составляют порядка 10%, при этом:
    600 r/s — selects,
    50 r/s — updates,
    20 r/s — inserts,
    20 r/s — delete.
    Как здесь быть с query_cache и как лучше в этом случае кешировать активные данные?
    • 0
      Если не согласовать кеш то прочитав данные из другой ноды ты получишь уже не актуальные на данный момент.
      Возможен любой вариант, это полноценный синхронный мульти-мастер. Во все ноды можно писать и читать одновременно.
      в query_cache хранятся хеши запросов и ответы на них, в innodb копии страниц данных, с диска.
      Те в одном случае результат в другом — исходные данные.
      innodb должен быть как можно больше, так что бы вместить данные базы и не ходить за ними на диск.
      query_cache должен быть минимально необходимым, до 512МБ максимум.
  • 0
    А можно позанудствовать чуть-чуть?

    У Оракла нет имплементации Galera — это совершенно независимый от него продукт.
    Federated была разработана ещё в MySQL AB и, к сожалению, до сих пор официально поддерживается. К сожалению, потому что если вы пойдёте на bugs.mysql.com и сравните как быстро устраняются баги, например, репликации и Federated — будет понятно что я имею в виду =)

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

    Он ещё самый важный с точки зрения сохранности данных
    • 0
      сохранность данных на диске конкретоной ноды, это не очень важный фактор. состояние будет передано при sst. а вот для ускорения работы кластера в целом, нужно минимизировать работу субд с диском до минимально необходимого
      • 0
        В общем согласна =)
    • 0
      Действительно интересно, MariaDB берет у Percona ее xtradb движек, а Percona берет у MariaDB — galera-у.
      • 0
        Так в итоге galera ok? Просто сейчас стоит задача по кластеру, раньше работал только с percona так как galera была еще beta.

        Еще тут в голове выросла такая схема, если можно покретекуйте.

        два роутера с keepalived, 4 ip адреса. Два из них между машинами могут мигрировать в случаи отказа.
        Там же HAProxy, доступ к нему по DNS-RR.
        HAPRoxy уже к web,mysql итд.

        Кстати пробовали ли IPVS вместо HAProxy?
      • 0
        Так Galera же Codership Oy, почему Maria?
        • 0
          а разве изначально не Майкл Видениус — Monty Program AB?
          • 0
            Хм… Честно говоря я не помню как Galera начиналась =)
  • 0
    Спасибо за статью, очень интересный материал.

    А насколько проблемно восстановление консистентности данных на всех узлах при выпадении одной из нод (скажем некий внеплановый ребут)? Каким образом осуществляется балансировка нагрузки в мульти мастер инстансах?
    • 0
      не сложно. Все происходит автоматически при запуске выпавшей ноды. Иногда нужно удалить все ее локальные данные и перезагрузить еще раз.
      Различают 2 варианта передачи состояний. Полное и инкрементальное. При повреждении локальных данных или первом старте, будет происходить полный SST.
      По балансировке — HAProxy или MaxScale. По первому, ссылка на статью в начале текста.
      • 0
        А можно подробнее про восстановление? Использую в продакшене два percona server в master-master репликации и любая перезагрузка одной из нод ведет к неизбежному геморою при восстановлении.

        Касательно HAProxy — при алгоритме с последовательным распределением запросов на ноды рано или поздно sql репликация может перестать успевать за балансировщиком и появляются duplicate entry. Как вы этого избегаете?

        Использую в связке с zabbix, поэтому особенно интересно :)
        • 0
          Ну класика же! Попробуйте использовать 3 ноды для кворума, при двух непонятно кто живой и кто прав.
          Правда когда я использовал 3 ноды, возникали вопросы с записью. См ниже.
          • 0
            Обязательно попробую, пока надо придумать как это сделать максимально безболезненно)
        • 0
          никакого гемороя не будет. Важно понимать что, 2 ноды это мало. нужно как минимум 2 и абраттратор. А лучше 3 или 4.
          Записывать нужно в один сервер или по крайней мере, что бы набор баз и таблиц в которую ведется запись была разной.
          MaxScale умеет отправлять запись в один набор нод а чтение в другой. Причем на уровне запросов а не соединений.
          • 0
            Круто, спасибо.
            Тогда если не затруднит, ещё пара вопросов:
            1. Умеет ли maxscale работу с двумя нодами (как в моем случае)?
            2. Есть ли рецепты по максимально безопасному и бастрому переезду с multi master схемы на кластер? И насколько мощным должен быть арбитратор?

            А то потихоньку подходим к необходимости масштабироваться:
            image
            • 0
              Арбитратор это минимально мощный сервер.
              что у вас за мульти-мастер?
              • 0
                2 инстанса percona server 5.6 с настроенной master-master репликацией расположенные на виртуалках с 8ядрами и 16гб оперативки + ucarp (т.е. по сути запись и чтение ведется только с одной ноды, вторая для горячего переключения в случае крэша).
                • 0
                  я не совсем понимаю как мастер-мастер? перекресная репликация?
                  • 0
                    Да, классическая перекрестная репликация.
                    • 0
                      ой. это жесть)
                      просто удаляешь оба инстанса и ставишь галеру. плюс арбиттратор.
                      ну и mysql_upgrade --force
                      • 0
                        а чем будет лучше два машины + арбитратор, чем например три машины в кластере?
                        Что будет если арбитратор выйдет из строя?
                        Еще непонятно почему арбитр должен быть мощным? Он же просто вроде как принимает роль, кто остается работать в случаи фрагментации.

                        • +1
                          Лучше 4 сервера в кластере, но если нужно сократить число серверов к минимуму и экономить их мощность. То можно использовать слабый сервер, как арбиттратор.
                          • 0
                            Не надо 4 сервера. Чуть ниже я описал почему.
                            • 0
                              Всем спасибо за ответы, за советы, а SyCraft ещё и за отличную статью!
          • 0
            А лучше 3 или 4
            не стоит… Лучше, это когда число голосов (ноды+арбитраторы) нечётное, иначе splitbrain более вероятен. Хотя это зависит от конкретной схемы включения, тем не менее нечётное число всегда предпочтительней.
            • 0
              Почему? откуда такие рекомендации, где почитать?.. Рекомендация лишь одна — нод должно быть более 2. А дальше без ограничений.
              • 0
                Что значит где почитать? Что по Вашему такое splitbrain и почему он происходит?
                Попробую найти где-нить строки хоть в чей-нибудь документации… Где-то явно оно есть.

                Тут указано про минимум 3 сервера, но они явно упустили момент с чётным числом узлов.
                • 0
                  splitbrain возможен когда ноды теряют согласование транзакций, так как они не знаю какая имеет верное состояние в данных момент. Это возможно, лишь когда нод 2. Все что выше, за счет других нод или арбитратторов, это кворум и splitbrain не может быть. Хоть четное их чесло, хоть нет. Речь ведь о Galera?
                  • 0
                    Речь о любом кластерном решении и Galera не исключение. Если у Вас будет кластер из 4-х нод, то в качестве необходимого для кворума, сколько нод Вы укажите? А теперь предположим что кластер развалился пополам. Если в качестве кворума было указано:
                    2 ноды — то каждая половина решит что она главная и будет существовать независимо. Вот и коллизия данных.
                    3 ноды — кластер просто перестанет существовать, т.к. каждая половина решит что её мало.

                    Вот у percona есть про это, нашёл.
                    • 0
                      я не буду указывать число нод в кворуме. Он будет выбирать это самостоятельно. Почему кластер должен развалится пополам? Если будет 4 ноды и они развалятся пополам, то будет 2 ноды по 2. Как вы сами и сказали, оба этих кластера не будут жизнеспособными.
                      Я бы еще согласился если бы нод было 5,6 или более.
                      • 0
                        Если число голосующих узлов будет нечётным, то не будет ситуации когда кластер развалится на две ЛЮБЫЕ части и при этом перестанет функционировать. И нету ни какой разницы произойдёт это при 2,4 или 6 узлах. Суть проблемы не меняется.

                        Если Вы считаете что конкретно Ваша инсталяция в силу расположения или схемы включения не способна развалиться на 2 половины, то наш разговор я считаю без полезным. Всё остальное я уже описал выше.
                        • 0
                          я уже выше написал, что не стоит делать кластер через интернет) думаю на этом уже стоило закончить.
                          • 0
                            Полагаться на одну локацию не комильфо.
                            • 0
                              это уже холивар)
                        • 0
                          Я не знаю как сейчас, но раньше можно было задать вес арбитру (ArbitrationRank).
                          0 — не станет арбитром, 1 — станет арбитром с высоким приоритетом; 2 — нода станет арбитром только если нет претендентов с высоким приритетом.
                          Как раз очень удобно когда у вас появлялось 4 машины. Еще я тут свои заметки полистал, арбитром вроде бы становилась одна из нод? Т.е это машина так же может заниматься хранением данных или это можно вынести отдельно как управляющию ноду?
                          • 0
                            Арбиттратор это служба galera без mysql )
                            те она просто поддерживает кворум, следит за порядком коммитов и составом участников. равнозначный член кластера без данных.
  • 0
    Еще хотел спросить, раньше была рекомендация писать в 1 ноду, иначе можно получить cluster-wide deadlocks.
    www.percona.com/blog/2012/08/17/percona-xtradb-cluster-multi-node-writing-and-unexpected-deadlocks/
    Это так же актуально?

    И еще, заготовка для конфигов — это хорошо. Но можно выложить рабочии конфиги для разных нод?
    Насколько я помню, надо указать для остальных нод, первую ноду, которая знает о других нодах.

    Пример:

    NODE1
    [mysqld_safe]
    wsrep_urls=gcomm://192.168.0.16:4567,gcomm://192.168.0.17:4567

    NODE2
    [mysqld_safe]
    wsrep_urls=gcomm://192.168.0.15:4567

    [mysqld]
    bind-address=192.168.0.16
    wsrep_node_name=node16
    wsrep_sst_donor=node15

    NODE3
    [mysqld_safe]
    wsrep_urls=gcomm://192.168.0.15:4567

    [mysqld]
    bind-address=192.168.0.17
    wsrep_node_name=node17
    wsrep_sst_donor=node15

    Так же надо было отдельно инициализировать кластер пустой строкой gcomm://.

    Это все еще актуально, или вопрос как то решается по другому?
    P.S. Пишу как из каменного века :)

    P.P.S.
    www.percona.com/doc/percona-xtradb-cluster/5.5/howtos/3nodesec2.html
    Раньше использовал.
    • 0
      Я выше ответил про cluster-wide deadlocks,
      так же там в параметрах есть тот, который отвечает за ретраи дедлока. Проблема решается при использовании maxscale.
      в заготовках для всех конфинах меняется только адрес ноды, имя ноды и список существущих нод. Текущая всегда будет последней в списке wsrep_urls
      Инициировать кластер пустой gcomm:// до сих пор нужно)
      • 0
        <в заготовках для всех конфинах меняется только адрес ноды, имя ноды и список существущих нод. Текущая всегда будет последней в списке wsrep_urls

        Что значит текущая? Речь идет о 1 ноде? Вроде бы как в wsrep_urls мы просто перечисляем список всех нод или нет?
        На других нодах мы пишет адрес первой ноды (да собственно пример выше) или я как то вас не пойму?

        Можно все же пример?
        • +1
          первая, ее адрес 192.168.0.76
          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.75
          wsrep_cluster_address = gcomm://192.168.0.30,192.168.0.40,192.168.0.41,192.168.0.74,192.168.0.76,192.168.0.75

          третья, ее адрес 192.168.0.74
          wsrep_cluster_address = gcomm://192.168.0.30,192.168.0.40,192.168.0.41,192.168.0.76,192.168.0.75,192.168.0.74
          итд
  • 0
    Я бы поставил innodb_locks_unsafe_for_binlog=false чтобы режим REPEATABLE-READ нормально работал. А также поставил бы innodb_flush_log_at_trx_commit=0.
    • 0
      innodb_flush_log_at_trx_commit=0 это перебор как мне кажется. innodb_locks_unsafe_for_binlog устарел уже в 5.6/10, но почему лучше в 0? что бы избежать фантомного чтения?
      • 0
        Насчет innodb_flush_log_at_trx_commit=0 может и перебор, если в датацентре свет отключат. А насчет innodb_locks_unsafe_for_binlog всё верно. Я конечно понимаю, что REPEATABLE-READ ведет к дедлокам и тормозам, но, блин, это так удобно, это как SERIALIZABLE в PostgreSQL.
        • 0
          Поясни почему. В MSSQL и Oracle этот режим по умолчанию, кроме того, так же как и в oracle, между снимками работает согласованное чтение. В чем минус?
          • 0
            Ты имеешь ввиду READ-COMMITTED по-умолчанию? При READ-COMMITTED мы можем прочитать данные согласовано только в пределах одного SQL запроса. А если запроса два — то между ними другая транзакция может навтыкать данные. И к каким последствиям это может привести — тяжело предсказать. Например в первом запросе извлек айдишники, а во втором запросе пытаешься подтянуть по ним данные (типа такой программный join осуществляешь). И всё, бабах. По некоторым айдишникам ниче не подтянулось, NullPointerException. Кто-то удалили эти записи между запросами. Придется предусматривать в коде этот случай.
            • 0
              READ COMMITTED Нечто похожее на уровень изоляции Oracle. Все выражения SELECT… FOR UPDATE и SELECT… LOCK IN SHARE MODE блокируют только индексные записи и не блокируют интервал перед ними. Поэтому они позволяют свободно добавлять новые записи после заблокированных. UPDATE и DELETE, которые используют уникальный индекс и уникальные условия поиска, блокируют только найденную индексную запись, и не блокируют интервал перед ней. Но в UPDATE и DELETE диапазонного типа в InnoDB должны установить блокировку следующего ключа или интервальную блокировку и блокировать добавления другими пользователями в интервал, покрытый диапазоном. Это необходимо, т.к. «фантомные строки» должны быть блокированы для успешной работы репликации и восстановления в MySQL. Согласованное чтение работает как и в Oracle: каждое согласованное чтение, даже внутри одной транзакции, устанавливает и читает свой собственный снимок.
  • 0
    Я бы поставил innodb_locks_unsafe_for_binlog=false чтобы режим REPEATABLE-READ нормально работал. А также поставил бы innodb_flush_log_at_trx_commit=0.

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