«Ідеальний» кластер. Частина 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МБ — це дуже багато, реально потрібно менше. І ось чому:

Якщо в кожній з таблиць, вибірка з якої є в кеші, відбуваються зміни (вставка або зміна рядків), 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 - c | 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 write to double 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 and writes 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

Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.