Практическая оптимизация и маштабируемость MySQL InnoDB на больших объёмах данных. Оптимизация innodb

Основы оптимизации производительности InnoDB | MySQL | Статьи | Программирование Realcoding.Net

Автор: boombick.org

Проводя опрос среди посетителей раздела Job Opening я задавал им один простой вопрос: если бы у вас был сервер с 16GB RAM, который был бы предназначен для MySQL-сервера с очень большим объемом innodb-таблиц, работающий с стандартным веб-проектом, какие бы настройки вы скорректировали? И самое интересное, что большинство не смогло четко ответить. Поэтому и было принято решение опубликовать эту заметку, которая, возможно, расширит ваши знания об оптимизации программной и аппаратной частей сервера.Я назвал эту статью Основы оптимизации производительности InnoDB, желая подчеркнуть, что это именно основы. Это универсальные советы, работающие на большинстве систем. Но более точную настройку необходимо производить, исходя из конкретных поставленных задач.

HardwareЕсли у вас есть база данных innodb большого объема, и это действительно важные данные, то 16 - 32 GB оперативной памяти будут оптимальным решением. Процессоры - 2*DualCore подойдут для большой нагрузки, а два Quad Core помогут решить проблемы с дальнейшим масштабированием системы. Хотя имеется множество нюансов. Третий момент - это подсистема ввода/вывода. Напрямую подключенный DataStorage с большим количеством дисков и RAID с возможностью сохранения кэша будут отличным выбором. Обычно необходимо 6 - 8 жестких дисков в стандартный блок, но порой может понадобиться и больше. Также обратите внимание на новые 2.5” SAS диски. Они меньше, но часто работают гораздо быстрее, чем обычные HDD. RAID10 хорошо подходит как для хранения, так и для чтения данных, но в случае, если вы можете позволить некоторую избытычность. В противном случае можно сделать RAID5, но опасайтесь случайных записей.

Дя начала: установите 64-битную операционную систему. Часто можно увидеть 32-битный linux, или запущенный в режиме совместимости с 64-bit. Не делайте так. Если вы используете LVM для хранения базы данных, вы сможете более эффективно работать с резервными копиями. Файловая система Ext3 будет оптимальным выбором в большинстве случаев, но если вы запускаете particular roadblocks, то попробуйте XFS. Вы можете использовать опции noatime и nodiratime, если вы используете innodb_file_per_table и большое количество таблиц, но это, в принципе, не столь важно. Также убедитесь, что OS резервирует достаточно большое количество памяти для MySQL.Операционная>

Опции MySQL InnoDBВажнейшими опциями являются:innodb_buffer_pool_size - 70 - 80% оперативной памяти. Я ставлю это значение в 12G на системе с 16G RAMinnodb_log_file_size - зависит от необходимого вам объема данных для восстановления, но 256МБ будут разумным компромиссом между производительностью и рамером лог-файлаinnodb_log_buffer_size=4M - 4 мегабайта - нормальное значение, если вы не используете подачу больших блоков данных в InnoDB через каналы (pipes). Если используете, это значение лучше увеличить.innodb_flush_logs_at_trx_commit=2 - если вас не особо заботит ACID, и вы можете себе позволить потерять транзакции за последние секунду или две, в случае полного краха ОС, то установите это значение. Но это может повлечь печальные эффекты при коротких записях транзакций.innodb_thread_concurrency=8 - даже при имеющихся InnoDB Scalability Fixes будет совсем не лишним иметь ограниченное количество потоков. Значение может быть больше или меньше в зависомости от ваших потребностей, но 8 будет оптимальным значением для начала.innodb_flush_method=O_DIRECT - избегайте двойной буферизации и уменьшите активность swap, в большинстве случаев это увеличивает производительность. Но будьте осторожны, если у вас нет RAID с возможностью сохранения данных, операции ввода-вывода могут проходить некорректно и данные могут быть повреждены.innodb_file_per_table - если у вас немного таблиц, используйте эту опцию и рост занимаемого таблицами места не будет бесконтрольным. Эта опция добавлена в MySQL 4.1 и сейчас достаточно стабильна для использования.

Проверьте также, могут ли ваши приложения запускаться в режиме изоляции READ-COMMITED. Если это так, то установите опцию transaction-isolation=READ-COMITTED. Этот вариант увеличит производительность.

Есть еще немало опций, значения которых можно поменять для достижения лучшей производительности. Об этом можно прочитать в заметке Настройка опций mysql-server (перевод) или в одной из наших презентаций.

Настройка приложений для работы с InnoDBПри переходе с типа MyISAM, вам конечно будет интересно, что изменилось и какие новые возможности вам теперь доступны. Во-первых: убедитесь, что вы используете транзакции при обновлениях. Это необходимо для повышения производительности. Во-вторых: готово ли ваше приложение обрабатывать проблемы, которые могут произойти? И в-третьих: возможно вы захотите пересмотреть структуру своих таблиц и посмотреть как вы можете использовать свойства InooDB: распределение по первичному ключу, использование первичного ключа на всех индексах (это позволяет сократить первичеый ключ), быстрый просмотр по первичным ключам (попробуйте использовать это при запросах с JOIN) или большие несжаты индексы (облегчают индексирование).

При помощи этого краткого описания вы сможете провести первичную настройку InnoDB, которая повысит производительность на системах без battery backup, без изменения настроек ОС и не внося изменения в настройки приложений, до сих пор использующих MyISAM

Оригинал: http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics//

www.realcoding.net

Практическая оптимизация и масштабируемость MySQL InnoDB на больших объёмах данных

Данный пост не будет рассказывать про индексы, планы запросов, триггеры для построения агрегатов и прочие общие способы оптимизации запросов и структуры БД. Так же не будет рассказывать про оптимальные настройки с префиксом innodb_. Возможно прочитав текст ниже вы лучше поймёте смысл некоторых из них. В данном посте речь пойдёт об InnoDB и его функционирование.

Какие проблемы может помочь решить этот пост?

Что делать если у вас в списке процессов множественные селекты которым казалось бы никто не мешает?
Что делать если всё хорошо настроено, запросы пролетают как ракеты и список процессов постоянно пустой, но на сервере высокий LA и запросы начинают работать немного медленнее, ну например вместо 100мс получается 500мс ?
Как быстро масштабировать систему, когда нет возможности всё переделать?
У вас коммерческий проект в конкурентной среде и проблему надо решать немедленно?
Почему один и тот же запрос работает то быстро то медленно?
Как организовать быстрый кеш и поддерживать его в актуальном состояние?

Как обычно выглядит работа с БД

Приблизительно схема работы обычно такая

Запрос
План запроса
Поиск по индексу
Получение данных из таблиц
Отправка данных клиенту

Даже если вы не делали start transaction каждый ваш отдельный запрос будет являться по сути транзакцией из одного запроса. Как известно у транзакций есть уровень изолированности который у MySQL по умолчанию REPEATABLE READ. А что это значит для нас? А то, что когда вы в транзакции «касаетесь любой таблицы» её версия на тот момент фиксируется и вы перестаёте видеть изменения сделанные в других транзакциях. Чем длиннее ваш запрос или транзакция, тем больше «старых» данных продолжает накапливать MySQL и есть основания предполагать, что происходит это с активным использованием основного пула памяти. Т.е. каждый ваш безобидный селект объединяющий 10 таблиц по первичному ключу, в случае активной работы с БД, начинает иметь достаточно тяжёлый побочный эффект. У PostgreSQL как и у Oracle уровень изолированности по умолчанию READ COMMITTED который функционирует гораздо проще, чем REPEATABLE READ. Само собой с уровнем изоляции READ COMMITTED вам придётся использовать построчную репликацию. Вы можете легко проверить уровни изолированности транзакций просто подключивших двумя клиентами к БД и поделать select, delete и update на одной и той же таблице. Это и есть ответ на вопрос про зависающие селекты, попробуйте сменить уровень изоляции БД, это может вам помочь.

Как InnoDB работает с данными

InnoDB хранит данные на жёстком диске в страницах. При обращение к нужной странице, она загружается в оперативную память и затем уже с ней происходят различные действия, будь то чтение запись или что-то ещё. Вот именно этой памятью и является Innodb_buffer_pool размер которого вы выставляете в innodb_buffer_pool_size. Схема работы вполне классическая и ничего необычного в ней нет. Посмотреть отчёты о работе InnoDB можно следующим образом:

SHOW VARIABLES like 'Innodb%'; SHOW GLOBAL STATUS like 'Innodb%'; SHOW ENGINE INNODB STATUS;

Итого мы получаем следующие временные затраты на выполнение операции чтения или записи в БД

Время на загрузка данных в память с жёсткого диска
Время на обработку данных в памяти
Время на запись данных на жёсткий диск если это требуется (тут стоит отметить, что не все данные сразу пишутся на диск, главное чтобы они были зафиксированы в журнале)

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

Самый быстрый вариант работы БД это когда все данные и индексы легко помешаются в пул и по сути БД всегда работает из памяти. В версии 5.6.5 даже есть возможность сохранять весь пул на диск при перезапуске БД, что позволяет избежать холодного старта.

Теперь давайте рассмотрим немного другой вариант событий, когда объём данных на диске превышает размер пула памяти. Пусть размер пула у нас будет 4 страницы выглядеть это будет как [0,0,0,0] и 16 страниц данных 1..16 соответственно. Пользователи у нас чаще всего запрашивают страницы 15 и 16 т.к. в них самые свежие данные и они всегда находятся в памяти. Очевидно, что работает всё так же быстро как и в случае описанном выше.

Ну и неудачный вариант, когда у вас есть 2 страницы активно запрашиваемые пользователями и 8 страниц, которые постоянно используются внутренними скриптами и различными демонами. Таким образом за 4 странице в буфере постоянно идёт борьба которая превращается в вечное чтение с диска и замедление работы системы для пользователей т.к. демоны как правило гораздо активнее себя ведут.

В таком режиме вам может помочь настройка репликации с ещё одни МySQL сервером который может принять на себя часть запросов и снизить борьбу за пул памяти. Но как известно у репликации в MySQL есть существенный недостаток, а именно применение изменений в 1 поток. Т.е. при определённых условиях слейв у вас или начнёт отставать или даст совсем несущественный прирост производительности. В этой ситуации может помочь возможность создавать слейвов с ограниченным числом таблиц. Что позволит получить выигрыш как на применение изменений так и на использование пула памяти. Во многих случаях, когда известны данные которые пользователи запрашивают чаще всего, вы можете создать для них кеш хранящий данные только из необходимых таблиц. В удачном случае у вас получится кеш автоматически поддерживающий свою актуальность. Для тех кому интересно как быстро создать ещё одного слейва, предлагаю посмотреть в сторону

STOP SLAVE; SHOW SLAVE STATUS;//Master_Log_File и Exec_Master_Log_Pos Тут нужно сделать дамп таблиц которые вам понадобятся START SLAVE;

После того как вставите данные в новый слейв сервер, нужно будет только сделать

CHANGE MASTER TO ... ; START SLAVE;

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

Декомпозиция системы на модули используя частичную репликацию

И так мы можем создавать частичные реплики основной БД, что позволяет нам контролировать распределение памяти между определёнными группами данных. Какие возможности это перед нам открывает? Как вы можете выяснить опытным путём никто не мешает вам создавать свои таблицы на слейв сервере и даже создавать в них внешние ключи на реплицируемые данные. Т.е. вы можете иметь не только целостную основную БД, но и целостных слейвов с расширенным набором таблиц. Например, ваша главная БД содержит таблицу users и различные вспомогательные таблицы типа payments. Так же у вас есть сервис блогов, который позволяет пользователям писать сообщения. Вы реплицируете users в другую БД, в которой создаёте таблицу posts. Если на БД содержащую таблицу posts выпадает высокая нагрузка на чтение, вы создаёте реплики содержащие таблицы users и posts. Таким образом можно производить декомпозицию пока объём данных необходимого набора таблицы не станет превышать разумные пределы. В этом случае уже стоит посмотреть в сторону шардинга огромных таблиц, например по хешу идентификатора пользователя, а запросы к нужным воркерам направлять через MQ.

Итоги

MySQL предоставляет простой механизм репликации просто пишущий данные в указанные таблицы. Это и даёт широкие возможности по разворачиванию дополнительных сервисов содержащих целостные части БД.

UPD. В случае большого количества мелких транзакций в БД можно попробовать изменить значение innodb_flush_log_at_trx_commit, это обеспечит уменьшение нагрузки на жёсткий диск, но будьте внимательны! теоретически это может привести к потере данных. Увидеть результат работы этой переменной достаточно легко, т.к. можно установить её через SET GLOBAL.

UPD2. Удачная декомпозиция системы позволяет варьировать настройки отдельных серверов в зависимости от выполняемых ими задач. Например на сервере платежей можно иметь SERIALIZABLE и innodb_flush_log_at_trx_commit = 1. А на сервере постов READ COMMITTED и innodb_flush_log_at_trx_commit = 2. Всё зависит от выполняемых задач, критичности данных и вероятности сбоя.

habr.com

Основы оптимизации производительности InnoDB | MySQL | Статьи | Программирование Realcoding.Net

Автор: boombick.org

Оригинал: http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics//

www.realcoding.net

Оптимизация MySQL InnoDB на высоких нагрузках

Попытаюсь в этой статье рассказать об особенностях применения хранилища InnoDB в высоконагруженных проектах, а так же дать поверхностное сравнение MyISAM и InnoDB. Безусловно, MySQL не ограничивается только этими двумя типами хранилища данных, однако они являются подавляющими в своей распространенности использования.

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

Почему народ выбирает InnoDB? InnoDB обладает преимуществами перед MyISAM.

Транзакционная модель. Это конечно преимущество не столько для администратора, сколько для программиста. Программист может объединить операции с базой в транзакцию, с кучей вытекающих из этого профита. Это основная причина по которой архитекторы выбирают InnoDB.
Блокировка на уровне строки. В отличии от MyISAM, где идет блокировка на уровне таблицы, в InnoDB блокировка осуществляется на уровне строки. Проблема конкурентных блокировок стоит не так остро как в MyISAM, однако все таки присутствует. Но об этом ниже.
Защита от сбоев. InnoDB более устойчивая к сбоям, если сказать точнее, InnoDB намного лучше восстанавливается после сбоев и практически не теряет данные. Для восстановления же MyISAM таблиц зачастую требуется потушить MySQL сервер и вручную восстанавливать таблицы утилитой myisamchk. Результатом работы myisamchk зачастую может оказаться частичная или полная потеря данных в таблице. InnoDB восстанавливается автоматически.
Качественная работа с IO. InnoDB имеет свой собственный Buffer Pool в памяти, где держит таблицы. Для InnoDB можно отключить системную буферизацию IO при работе с таблицами InnoDB. Таким образом, можно сказать что в InnoDB нет двойной буферизации (как в MyISAM), следовательно, оперативная память разумно расходуется.

MyISAM конечно же тоже обладает преимуществами, в основном это простота и скорость. На небольших объемах данных и большом количестве операций чтения лучше хранилища не найти, если конечно вам не нужны транзакции. Но сейчас не об этом. На этой ноте про MyISAM больше ни слова.

InnoDB не готова корректно работать из коробки на высоких нагрузках. Надо хорошо понимать о происходящих в недрах InnoDB процессах дабы правильно настроить этот тип хранилища.

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

innodb_file_per_table

По умолчанию, InnoDB использует общее хранилище для всех таблиц и индексов. Данная опция позволяет содавать на каждую таблицу свой .ibd файл. Наиболее частая причина применения этой опции – раскидать отдельные таблицы по отдельным физическим устройствам.

Так же бывают определенные таблицы, в которые очень часто пишутся и удаляются данные. Это серъезно фрагментирует общее хранилище таблиц и от этого может пострадать производительность других таблиц. В этом случае имеет смысл разбивать общее хранилище на отдельные куски для каждой таблицы.

Если у вас таблицы уже созданы в общем хранилище и вы перезагружаете сервер MySQL с этой опцией, старые таблицы останутся в общем хранилище, а новые будут создаваться в отдельных хранилищах. Таким образом, чтобы поместить старые таблицы в раздельные файлы, нужно их пересоздать или переименовать.

Использование выделенных блочных устройств под хранилище

Киллер-фича InnoDB. Вы можете использовать целые партиции или физические устройства вместо файлов общего хранилища InnoDB. Это сразу убирает всякую системную буферизацию ввода-вывода и всякий оверхед файловой системы. В этом случае InnoDB пишет данные прямо на устройство. Конечно же, это создает ряд ньюансов в процедуре резервного копирования.

Для того чтобы использовать эту возможность, пропишите в конфигурации

[mysqld] innodb_data_home_dir= innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw

После старта InnoDB сделает инициализацию блочных устройств. Очень важно после этого остановить сервер и в конфигурации поменять «newraw» на «raw»:

[mysqld] innodb_data_home_dir= innodb_data_file_path=/dev/hdd1:3Graw;/dev/hdd2:2Graw

и перезапустить сервер. Иначе при следующем перезапуске, если InnoDB встретит «newraw», партиция будет заново отформатирована!

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

При использовании данной возможности, очевидно лучше для InnoDB выделять логические тома LVM. Это существенно упрощает бекап (по снятому снапшоту) и восстановление.

innodb_buffer_pool_size

Размер памяти, выделяемый под кеш данных и индексов. Строго говоря, чем больше таблиц сидит в этой памяти, тем лучше. Если есть возможность, размер этого буфера должен быть чуть больше общего размера innodb таблиц. Однако он не должен быть больше 80% объема ОЗУ.

innodb_log_file_size

Размер файла лога транзакций. Чем больше размер, тем реже InnoDB будет сбрасывать страницы Buffer Pool на диск, и тем больше требуется времени на восстановление после аварии. Размер варьируется от нескольких мегабайт до размера innodb_buffer_pool_size, но не более 4Gb суммарно во всех лог-файлах.

innodb_log_buffer_size

Размер буфера памяти для записи лога транзакций. Размер варьируется в пределах единиц-десятков мегабайт. Большой размер буфера позволяет запускать объемные транзакции без сброса лога на диск, что позволяет уменьшить IO при объемных транзакциях.

innodb_flush_log_at_trx_commit

Принимает одно из трех значений: 0, 1, 2. При значении 1, лог скидывается на диск при каждом коммите транзакции и буфер записи так же скидывается на диск. При 0 эта операция производится не при каждой транзакции а 1 раз в секунду. При значении 2, лог скидывается на диск при каждом коммите, но сброс буферов не производится.

Если вы ищете производительность в ущерб надежности – ставьте 0. Если наоборот – ставьте 1.

innodb_thread_concurrency

Количество рабочих тредов InnoDB. Начать надо с количества ядер CPU*2 + количество физических блочных устройств. Мне всегда этой формулы хватало. Официальная документация рекомендует поиграть с этим значением.

innodb_flush_method

Установка характера работы с файловой системой. Данная переменная не имеет эффекта при использовании выделенных блочных устройств под хранилище. Представляет из себя комбинацию значений O_DSYNC,O_DIRECT,fdatasync. Если вы используете большой размер innodb_buffer_pool_size, имеет смысл дать InnoDB доступ к файлам, минуя системные буфера с помощью опции O_DIRECT.

В официальном руководстве сказано, что при использовании определенных Storage Area Network, O_DIRECT может дать серъезный пенальти производительности.

Для OS GNU/Linux читайте про опции O_DSYNC (O_SYNC) и O_DIRECT на данной странице руководства open (2). FreeBSD очевидно имеет много сходств с GNU/Linux в этом вопросе.

Для OS MS Windows © ® ™ данная опция не имеет смысла, как и данная статья вообще.

innodb_locks_unsafe_for_binlog

Эта опция не может быть никак пропущена, если у вас серьезная нагрузка на конкурентную запись. Очень сложно это объяснить, да и не понимаю я этого до самой глубины, но если вкратце…

Если вам нужна полноценная изоляция транцакций, то эта опция не для вас. Тогда придется пренебрегать производительностью в пользу целостности транзакций.

Например, если вы используете чтение по диапазону (SELECT a FROM b WHERE c>100) внутри тразакции, то с включенной опцией innodb_locks_unsafe_for_binlog, следующий такой же запрос вернет тот же результат, даже если между ними кто то что то в эту таблицу пытался писать.

При выключенной (по умолчанию) опции innodb_locks_unsafe_for_binlog, во второй раз указанный запрос может вернуть отличный от первого раза результат в одной транзакции, поскольку пытающимся писать в эту таблицу процессам не было выставлено препятствий.

То есть, эта опция во включенном состоянии снимает туеву хучу локов при конкурентной записи-чтении в таблицы. Цена вопроса – не обеспечивается консистентный снапшот данных на время всей транзакции. Как то так в общем.

В моем случае, прирост производительности при включении этой опции был колоссальный. Однако это имеет смысл на реально массивных смешанных записях-удалениях-чтениях.

Ах да. И для репликации соответственно это не канает.

innodb_lock_wait_timeout

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

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

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

Программистов нужно предупреждать (если они не в курсе), что если они словили ошибку 1205 от InnoDB, то надо повторить или отложить транзакцию! Поскольку эта ошибка де-факто не может быть воспроизведена в тестовых условиях, очень часто программисты не в курсе и бывают весьма удивлены, наблюдая пробелы в потоках данных.

l.wzm.me

Практическая оптимизация и маштабируемость MySQL InnoDB на больших объёмах данных

Какие проблемы может помочь решить этот пост?

Что делать если у вас в списке процессов множественные селекты которым казалось бы никто не мешает?
Что делать если всё хорошо настроено, запросы пролетают как ракеты и список процессов постоянно пустой, но на сервере высокий LA и запросы начинают работать немного медленнее, ну например вместо 100мс получается 500мс ?
Как быстро масштабировать систему, когда нет возможности всё переделать?
У вас коммерческий проект в конкурентной среде и проблему надо решать немедленно?
Почему один и тот же запрос работает то быстро то медленно?
Как организовать быстрый кеш и поддерживать его в актуальном состояние?

Как обычно выглядит работа с БД

Приблизительно схема работы обычно такая

Запрос
План запроса
Поиск по индексу
Получение данных из таблиц
Отправка данных клиенту

Как InnoDB работает с данными

SHOW VARIABLES like 'Innodb%';SHOW GLOBAL STATUS like 'Innodb%';SHOW ENGINE INNODB STATUS;

Итого мы получаем следующие временные затраты на выполнение операции чтения или записи в БД

Время на загрузка данных в память с жёсткого диска
Время на обработку данных в памяти
Время на запись данных на жёсткий диск если это требуется (тут стоит отметить, что не все данные сразу пишутся на диск, главное чтобы они были зафиксированы в журнале)

STOP SLAVE;SHOW SLAVE STATUS;//Master_Log_File и Exec_Master_Log_PosТут нужно сделать дамп таблиц которые вам понадобятсяSTART SLAVE;

После того как вставите данные в новый слейв сервер, нужно будет только сделать

CHANGE MASTER TO ... ;START SLAVE;

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

Декомпозиция системы на модули используя частичную репликацию

Итоги

Автор: dgreen

www.pvsm.ru

performance - Оптимизация MySQL для ALTER TABLE of InnoDB

Вам нужно немного подумать о своих требованиях.

На простейшем уровне "самый быстрый" способ изменить таблицу состоит в том, чтобы сделать это как можно меньше инструкций ALTER TABLE, желательно один. Это связано с тем, что MySQL копирует данные таблицы, чтобы изменить схему и сделать пятнадцать изменений, в то время как сделать одну копию, очевидно (и на самом деле), быстрее, чем копировать таблицу пятнадцать раз, делая одно изменение за раз.

Но я подозреваю, что вы спрашиваете, как это сделать с минимальным временем простоя. Как бы я это сделал, вы в основном синтезируете способ работы неблокированного ALTER TABLE. Но у него есть некоторые дополнительные требования:

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

Основной метод, как вы предложили, то есть с помощью INSERT INTO ... SELECT .... По крайней мере, вы находитесь впереди, потому что вы начинаете с таблицы InnoDB, поэтому SELECT не будет блокироваться. Я рекомендую сделать ALTER TABLE в новой пустой таблице, которая сохранит MySQL, копируя все данные снова, что будет означать, что вам нужно правильно перечислить все поля в инструкции INSERT INTO ... SELECT .... Затем вы можете сделать простой оператор RENAME для его замены. Затем вам нужно сделать еще один INSERT INTO ... SELECT ... WHERE ... и, возможно, UPDATE ... INNER JOIN ... WHERE ..., чтобы захватить все измененные данные. Вам нужно быстро выполнить INSERT и UPDATE, или ваш код начнет добавлять новые строки и обновления к вашему снимку, что будет мешать вашему обновлению. (У вас не будет этой проблемы, если вы может помещать ваше приложение в режим обслуживания в течение нескольких минут до RENAME.)

Кроме того, существуют некоторые параметры, связанные с ключом и буфером, которые вы можете изменить только за один сеанс, который может помочь перемещению основных данных. Такие вещи, как read_rnd_buffer_size и read_buffer_size, будут полезны для увеличения.

qaru.site

: MySQL :: Базы данных :: Справочник

Почему народ выбирает InnoDB? InnoDB обладает преимуществами перед MyISAM.

Транзакционная модель. Это конечно преимущество не столько для администратора, сколько для программиста. Программист может объединить операции с базой в транзакцию, с кучей вытекающих из этого профита. Это основная причина по которой архитекторы выбирают InnoDB.
Блокировка на уровне строки. В отличии от MyISAM, где идет блокировка на уровне таблицы, в InnoDB блокировка осуществляется на уровне строки. Проблема конкурентных блокировок стоит не так остро как в MyISAM, однако все таки присутствует. Но об этом ниже.
Защита от сбоев. InnoDB более устойчивая к сбоям, если сказать точнее, InnoDB намного лучше восстанавливается после сбоев и практически не теряет данные. Для восстановления же MyISAM таблиц зачастую требуется потушить MySQL сервер и вручную восстанавливать таблицы утилитой myisamchk. Результатом работы myisamchk зачастую может оказаться частичная или полная потеря данных в таблице. InnoDB восстанавливается автоматически.
Качественная работа с IO. InnoDB имеет свой собственный Buffer Pool в памяти, где держит таблицы. Для InnoDB можно отключить системную буферизацию IO при работе с таблицами InnoDB. Таким образом, можно сказать что в InnoDB нет двойной буферизации (как в MyISAM), следовательно, оперативная память разумно расходуется.

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

innodb_file_per_table

Использование выделенных блочных устройств под хранилище

Для того чтобы использовать эту возможность, пропишите в конфигурации [mysqld] innodb_data_home_dir= innodb_data_file_path dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw

После старта InnoDB сделает инициализацию блочных устройств. Очень важно после этого остановить сервер и в конфигурации поменять «newraw» на «raw»: [mysqld] innodb_data_home_dir= innodb_data_file_path dev/hdd1:3Graw;/dev/hdd2:2Graw

innodb_buffer_pool_size

innodb_log_file_size

innodb_log_buffer_size

innodb_flush_log_at_trx_commit

Если вы ищете производительность в ущерб надежности – ставьте 0. Если наоборот – ставьте 1.

innodb_thread_concurrency

innodb_flush_method

Для OS GNU/Linux читайте про опции O_DSYNC (O_SYNC) и O_DIRECT на данной странице руководства open(2). FreeBSD очевидно имеет много сходств с GNU/Linux в этом вопросе.

innodb_locks_unsafe_for_binlog

Ах да. И для репликации соответственно это не канает.

innodb_lock_wait_timeout

ref.ysite.org

Практическая оптимизация и маштабируемость MySQL InnoDB на больших объёмах данных. Оптимизация innodb

Основы оптимизации производительности InnoDB | MySQL | Статьи | Программирование Realcoding.Net

Практическая оптимизация и масштабируемость MySQL InnoDB на больших объёмах данных

Какие проблемы может помочь решить этот пост?

Как обычно выглядит работа с БД

(adsbygoogle = window.adsbygoogle || []).push({}); (adsbygoogle = window.adsbygoogle || []).push({}); Как InnoDB работает с данными

Декомпозиция системы на модули используя частичную репликацию

(adsbygoogle = window.adsbygoogle || []).push({}); Итоги

Основы оптимизации производительности InnoDB | MySQL | Статьи | Программирование Realcoding.Net

Оптимизация MySQL InnoDB на высоких нагрузках

Оптимизация MySQL InnoDB на высоких нагрузках

Практическая оптимизация и маштабируемость MySQL InnoDB на больших объёмах данных

Какие проблемы может помочь решить этот пост?

Как обычно выглядит работа с БД

Как InnoDB работает с данными

Декомпозиция системы на модули используя частичную репликацию

Итоги

performance - Оптимизация MySQL для ALTER TABLE of InnoDB

: MySQL :: Базы данных :: Справочник

Как InnoDB работает с данными

Итоги