Назад | Перейти на главную страницу

Производительность репликации MySQL

У меня серьезная проблема с производительностью репликации MySQL 5.5 между двумя машинами, в основном с таблицами myISAM с репликацией на основе операторов. Бинарные журналы и каталог данных mysql находятся на одном и том же Fusion ioDrive.

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

Как я могу увеличить производительность репликации? Машина B в основном простаивает (мало, ввод-вывод, 2 ядра из 16 с максимальной нагрузкой, много свободной оперативной памяти), так как только 1 поток mySQL записывал данные. Вот несколько идей, которые у меня были:

Основная проблема - что если после паузы на 3 часа требуется 10 часов, это означает, что репликация записывает 13 часов данных за 10 часов или может записывать со скоростью 130% от скорости поступающих данных. Я ищу хотя бы двойную запись на главной машине в ближайшем будущем, поэтому отчаянно нужен способ улучшить производительность репликации.

Машина А:

my.cnf:

[mysqld]
server-id=71
datadir=/data_fio/mysqldata
socket=/var/lib/mysql/mysql.sock
tmpdir=/data_fio/mysqltmp

log-error = /data/logs/mysql/error.log
log-slow-queries = /data/logs/mysql/stats03-slowquery.log
long_query_time = 2
port=3306

log-bin=/data_fio/mysqlbinlog/mysql-bin.log
binlog-format=STATEMENT
replicate-ignore-db=mysql

log-slave-updates = true

# Performance Tuning
max_allowed_packet=16M
max_connections=500
table_open_cache = 2048
max_connect_errors=1000
open-files-limit=5000

# mem = key_buffer + ( sort_buffer_size + read_buffer_size ) * max_connections
key_buffer=4G
max_heap_table_size = 1G
tmp_table_size = 4G
myisam_sort_buffer_size = 256M
sort_buffer_size=4M
read_buffer_size=2M
query_cache_size=16M
query_cache_type=2
thread_concurrency=32

user=mysql

symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysql]
socket=/var/lib/mysql/mysql.sock

[client]
socket=/var/lib/mysql/mysql.sock

Машина B:

my.cnf:

[mysqld]
server-id=72
datadir=/data_fio/mysqldata
socket=/var/lib/mysql/mysql.sock
tmpdir=/data_fio/mysqltmp

log-error = /data/logs/mysql/error.log
log-slow-queries = /data/logs/mysql/stats03-slowquery.log
long_query_time = 2
port=3306

# Performance Tuning
max_allowed_packet=16M
max_connections=500
table_open_cache = 2048
max_connect_errors=1000
open-files-limit=5000

# mem = key_buffer + ( sort_buffer_size + read_buffer_size ) * max_connections
key_buffer=4G
max_heap_table_size = 1G
tmp_table_size = 4G
myisam_sort_buffer_size = 256M
sort_buffer_size=4M
read_buffer_size=2M
query_cache_size=16M
query_cache_type=2
thread_concurrency=32

user=mysql

symbolic-links=0

plugin-load=archive=ha_archive.so;blackhole=ha_blackhole.so

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysql]
socket=/var/lib/mysql/mysql.sock

[client]
socket=/var/lib/mysql/mysql.sock

Вау, у вас есть ужасно мощное оборудование для решения этой проблемы. В плане аппаратного обеспечения вы не так уж много чего можете сделать, за исключением обновления, возможно, до процессоров Sandy / Ivy Bridge для повышения производительности поиска Btree на 20-50% и т. Д.

Обратите внимание, что моя сильная сторона - Innodb, поэтому я собираюсь

  1. Не обращайте внимания на то, что вы myisam, и действуйте так, как будто это не имеет значения.
  2. Предположим, этой проблемы достаточно, чтобы заставить вас перейти на новую версию. Да, это апгрейд.

Innodb может помочь в полной мере использовать всю эту память, сохраняя эти часто используемые строки в своем буферном пуле. Вы можете настроить его настолько большим, насколько хотите (скажем, 80% памяти), и новые операции чтения / записи останутся в памяти до тех пор, пока ему не потребуется переместить их на диск, чтобы освободить место для последних данных, к которым осуществляется доступ. В памяти на порядок быстрее, чем у ваших FusionIO.

Есть еще много других функций Innodb, таких как адаптивные хэши, механизмы автоматической блокировки и т. Д., Которые могут быть благом для вашей среды. Однако вы знаете свои данные лучше, чем я.

В мире innodb хорошим краткосрочным решением является оптимизация вашего ведомого устройства - действительно ли вам нужен каждый индекс на вашем ведомом устройстве, который у вас есть на вашем главном устройстве? Индексы представляют собой клубок на вставках / обновлениях / удалениях, ЧЕТНЫЙ с картами Fusion IO. IOPS - это еще не все. Протоколы Sandy / Ivy Bridge имеют гораздо лучшую пропускную способность памяти и вычислительную производительность - они могут иметь огромное значение по сравнению с Westmeres, которые у вас есть сейчас. (Диаграмма 20-50% в целом). удалять все индексы, которые вам не нужны на ведомом устройстве!

Во-вторых, и почти наверняка это применимо только к innodb, mk-prefetch может знать, какие обновления и до того, как ведомое устройство их запишет. Это позволяет mk-prefetch сначала выполнить запрос чтения, тем самым заставляя данные находиться в памяти к тому времени, когда одиночный ответ выполняет запрос записи. Это означает, что данные находятся в памяти, а не в fusionIO, что дает быстрый прирост производительности на порядок. Это делает ОГРОМНЫЙ разница, больше, чем можно было ожидать. Многие компании используют это как постоянное решение. Узнайте больше, просмотрев Набор инструментов Percona.

В-третьих, и это наиболее важно, после того, как вы перешли на Innodb, обязательно проверьте Tokutek. У этих парней есть ужасно крутые вещи, которые намного превосходят Innodb по производительности записи / обновления / удаления. Они рекламируют повышенную скорость репликации как одно из ключевых преимуществ, и вы можете увидеть из их тестов, почему Fusions сумасшедшие IOPS все еще не поможет вам в случае Btrees. (Примечание. Независимо не проверено мною.) Они используют заменяющую замену индекса btree, которая, хотя и является ужасно более сложной, улучшает многие алгоритмические ограничения скорости индексов btree.

Я рассматриваю возможность принятия Tokutek. Если они высвободят такую ​​большую скорость записи, это позволит мне добавить больше индексов. Поскольку они сжимают данные и индексы в таких прекрасных пропорциях (25x, по их словам), вы даже не платите (производительность, обслуживание) цену за увеличение объема данных. Вы платите ($) за их движок, 2500 $ в год за предварительно сжатый ГБ, IIRC. У них есть скидки, если у вас есть реплицируемые данные, но вы даже можете просто установить Tokutek на своем ведомом устройстве и оставить свой главный как есть. Ознакомьтесь с техническими деталями в Лекция MIT Algoritms Open Courseware. Кроме того, в их блогах есть масса технических материалов и обычные технические документы для тех, у кого нет 1:20 для просмотра видео. Я считаю, что это видео также дает формулу Big-O для определения скорости чтения. я иметь предположить, что чтение происходит медленнее (всегда есть компромисс!), но формула слишком сложна, чтобы я мог оценить, насколько. Они утверждают, что это примерно то же самое, но я бы предпочел разбираться в математике (вряд ли!). Возможно, вам будет удобнее это обнаружить, чем мне.

P.s. Я не связан с Tokutek, я никогда не запускал их продукт, и они даже не знают, что я смотрю на них.

Обновить:

Я вижу, что у вас есть еще вопросы на этой странице, и подумал, что отвечу:

Во-первых, предварительная выборка ведомого почти наверняка не будет работать для myisam, если у вас нет исключительной среды. В основном это связано с тем, что предварительная выборка будет блокировать те самые таблицы, в которые вы собираетесь записывать, или подчиненный поток заблокировал таблицу, которая необходима демону предварительной выборки. Если ваши таблицы очень хорошо сбалансированы для репликации и в разные таблицы записываются циклически, это может сработать, но имейте в виду, что это очень теоретически. Книга «Высокая производительность MySQL» содержит дополнительную информацию в разделе «Проблемы репликации».

Во-вторых, предположительно ваше ведомое устройство имеет нагрузку 1,0-1,5, она может быть выше, если у вас есть другие процессы или запросы, но базовый уровень равен 1,0. Это означает, что вы, вероятно, связаны с процессором, что, вероятно, с вашим FusionIO на борту. Как я уже упоминал ранее, Sandy / Ivy Bridge даст немного больше удовольствия, но, вероятно, этого будет недостаточно, чтобы вы смогли пройти через тяжелые времена с минимальной задержкой. Если нагрузка на это ведомое устройство в основном предназначена только для записи (т.е. не так много операций чтения), ваш ЦП почти наверняка тратит время на вычисление позиций для вставок / удалений btree. Это должно укрепить мою точку зрения об удалении некритических индексов - вы всегда можете повторно добавить их позже. Отключить гиперпоточность не получится, больше ЦП вам не враг. Как только вы получите более 32 ГБ оперативной памяти, скажем, 64 ГБ, вам нужно беспокоиться о распределение барана, но даже тогда симптомы разные.

Наконец, что наиболее важно (не пропускайте эту часть;)), я предполагаю, что вы сейчас используете RBR (репликация на основе строк), потому что вы упомянули нетривиальное увеличение производительности при переключении. Однако - может быть способ отомстить Больше производительность здесь. Ошибка MySQL 53375 может проявиться, если у вас есть таблицы, которые реплицируются без первичного ключа. Подчиненное устройство в основном недостаточно умен, чтобы использовать что-либо, кроме первичного ключа, поэтому его отсутствие вынуждает поток репликации выполнять полное сканирование таблицы для каждое обновление. Исправление - это просто добавление суррогатного суррогатного первичного ключа с автоинкрементом. Я бы сделал это, только если бы таблица была большой (скажем, несколько десятков тысяч строк или больше). Это, конечно, происходит за счет наличия другого индекса в таблице, который увеличивает цену, которую вы платите в ЦП. Обратите внимание, что существует очень мало теоретических аргументов против этого, поскольку InnoDB добавляет один за кулисами, если вы этого не сделаете. Фантомный, однако, не является полезной защитой от 53375. Вольфрам также может решить эту проблему, но вы необходимость чтобы быть уверенным при использовании Tungsten, что у вас есть правильная кодировка. В последний раз, когда я играл с ним, он ужасно умирал, когда требовалась репликация любой строки, отличной от UTF8. Примерно в то время я отказался от этого.

не ответ, но вы можете подумать вольфрамовый репликатор и их коммерческие продукты для большей гибкости. 100% использование процессора на одном ядре является узким местом?

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