FreeBSD. Записки системного администратора. Innodb оптимизация

Плановая оптимизация таблиц в MySQL InnoDB

OPTIMIZE TABLE выполняет следующие шаги внутри на столе (mydb.mytable)

CREATE TABLE mydb.mytablenew LIKE mydb.mytable; INSERT INTO mydb.mytablenew SELECT * FROM mydb.mytable; ALTER TABLE mydb.mytable RENAME mydb.mytablezap; ALTER TABLE mydb.mytablenew RENAME mydb.mytable; DROP TABLE mydb.mytablezap;

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

Что вам нужно иметь MySQL Master/Мастер (ака Circular) Replication установить

Вы могли бы попробовать это:

для серверов M1 и M2 и DB VIP указывающей на М1

на М2, выполните следующие

STOP SLAVE; SET sql_log_bin = 0; Perform OPTIMIZE TABLE or ALTER TABLE ... ENGINE=InnoDB on all InnoDB tables START SLAVE; Wait for replication to catch (Seconds_Behind_Master = 0)

SET sql_log_bin = 0 предотвратит DDL команды от репликации над Master.

После того, как все эти шаги завершены, продвиньте подчиненное устройство к мастеру и понизите мастер на ведомый (можно сделать, просто переместив свой DB VIP с M1 на M2). Вы можете выполнять это обслуживание каждый день, и производство не будет ощущать никаких эффектов, за исключением Master Promotion и Slave Demotion.

Вы можете создать скрипт и запустить его на M2, как это:

echo "SET sql_log_bin = 0;" > InnoDBCompression.sql echo "STOP SLAVE;" >> InnoDBCompression.sql mysql -u... -p... -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBCompressionSQL FROM information_schema.tables WHERE engine='InnoDB' ORDER BY (data_length+index_length)" >> InnoDBCompression.sql echo "START SLAVE;" >> InnoDBCompression.sql mysql -u... -p... -A < InnoDBCompression.sql

Отсюда, просто ждать Seconds_Behind_Master быть 0 на М2, а затем переместить DBVIP от М1 до М2.Теперь, если вы знаете конкретные имена таблиц, которые хотите оптимизировать, вы можете настроить запрос для извлечения только этих таблиц.

Дайте ему попробовать !!!

CAVEAT

Вот справедливое предупреждение: If you have innodb_file_per_table disbaled, every time you run OPTIMIZE TABLE or ALTER TABLE ... ENGINE=InnoDB; the ibdata1 file just grows. You would need to cleanup the InnoDB infrastructure предотвратить ibdata1 расти из-под контроля.

dba.stackovernet.com

Оптимизация MySQL, optimize table — как не надо делать — Olunka ♥ layout of sites and emails

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

В одном из блогов встретила вот такой текст:

Конкретный пример из жизни: 2 таблицы по 50 000 записей, в которых постоянно идут update, insert, delete. Ясное дело, что при таком подходе данные фрагментируются. Поэтому, когда мы делаем JOIN, то запрос выполняется 0.2 секунды — довольно много для базы данных. После optimize table запрос стал выполнятся 0.015 сек. Никаких дополнительных индексов, покупки железа — просто упорядочили данные на диске. Команду можно поставить в cron раз в сутки, например.

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

И вот тысячи блогеров постят себе полезную команду optimize table и рекомендуют всем своим читателям пользоваться ею не реже раза в неделю. Кто-нибудь из них почитал зачем нужна эта команда, что она делает, чего она не может сделать и стоит ли ее применять для блогов?

Заглянем сюда — MySQL — справочное руководство на русском. И найдем такой пункт, касающийся optimize table, цитирую:

4.5.1. Синтаксис команды OPTIMIZE TABLEOPTIMIZE TABLE tbl_name[,tbl_name]…

Команда OPTIMIZE TABLE должна использоваться после удаления большей части таблицы или если в таблице было внесено много изменений в строки переменной длины (таблицы, в которых есть столбцы VARCHAR, BLOB или TEXT). Удаленные записи поддерживаются при помощи связного списка, и последующие операции INSERT повторно используют позиции старых записей. Чтобы перераспределить неиспользуемое пространство и дефрагментировать файл данных, можно воспользоваться командой OPTIMIZE TABLE.На данный момент команда OPTIMIZE TABLE работает только с таблицами MyISAM и BDB. Для таблиц BDB команда OPTIMIZE TABLE выполняет ANALYZE TABLE.Можно применить OPTIMIZE TABLE к таблицам других типов, запустив mysqld с параметром —skip-new или —safe-mode, но в этом случае OPTIMIZE TABLE лишь только выполняет ALTER TABLE.Команда OPTIMIZE TABLE работает следующим образом:* Если в таблице есть удаленные или разделенные строки, восстанавливает таблицу.* Если индексные страницы не отсортированы — сортирует их.* Если статистические данные не обновлены (и восстановление нельзя осуществить путем сортировки индексов), обновляет их.

Команда OPTIMIZE TABLE для MyISAM представляет собой эквивалент выполнения myisamchk —quick —check-only-changed —sort-index —analyze над таблицей.Обратите внимание: во время работы OPTIMIZE TABLE таблица заблокирована!

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

В определении все четко и ясно написано. Команда optimize table применяется для таблиц, в которые было внесено много изменений. Много — это не 12 комментариев и 4 поста, это несколько тысяч. А фраза про то, что во время выполнения команды optimize table таблица будет заблокирована нам о чем говорит? Если таблица заблокирована, значит и сайт, работающий с этой таблицей тоже будет не работоспособен. Таблица будет заблокирована полностью, даже если оптимизироваться будет только тот столбец, что отвечает за комментарии. Отсюда вопрос, зачем запускать эту команду каждый день?

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

Когда стоит применять команду optimize table для блога

1. Если у вас удаляется или изменяется большое количество статей или комментариев (большое количество — это несколько тысяч).2. В часы, когда посещаемость вашего сайта самая низкая.

P.S. Не бойтесь обращаться за советом к первоисточникам и профессиональной литературе.

Оптимизация 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, то надо повторить или отложить транзакцию! Поскольку эта ошибка де-факто не может быть воспроизведена в тестовых условиях, очень часто программисты не в курсе и бывают весьма удивлены, наблюдая пробелы в потоках данных.

freebsd-sysadm-notes.blogspot.com

mysql - Оптимизация Mysql InnoDB

У меня возникли проблемы с пониманием использования InnoDB - у нас есть DB на основе drupal (5: 1 read: write), работающий на mysql (версия сервера: 5.1.41-3ubuntu12.10-log (Ubuntu)). Наш текущий размер данных/индекса Innodb:

Текущее пространство индекса InnoDB = 196 M Текущее пространство данных InnoDB = 475 М

Оглядываясь на Интернет и читающие книги, такие как "Высокопроизводительный sql", предлагают увеличить размер данных на 10% - я установил буферный пул (data + index) + 10% и заметил, что пул буферов был на 100%... даже увеличивая примерно до 896 Мб, он все равно делает это на 100% (хотя индексы данных + составляют только ~ 671 Мб?

Я подключил вывод раздела innodb mysqlreport ниже. Страницы, свободные от 1, как представляется, также указывают на серьезную проблему. У параметра innodb_flush_method установлено значение по умолчанию - я буду исследовать установку этого параметра в O_DIRECT, но вы хотите решить эту проблему раньше.

__ InnoDB Buffer Pool __________________________________________________ Usage 895.98M of 896.00M %Used: 100.00 Read hit 100.00% Pages Free 1 %Total: 0.00 Data 55.96k 97.59 %Drty: 0.01 Misc 1383 2.41 Latched 0 0.00 Reads 405.96M 1.2k/s From file 15.60k 0.0/s 0.00 Ahead Rnd 211 0.0/s Ahead Sql 1028 0.0/s Writes 29.10M 87.3/s Flushes 597.58k 1.8/s Wait Free 0 0/s __ InnoDB Lock _________________________________________________________ Waits 66 0.0/s Current 0 Time acquiring Total 3890 ms Average 58 ms Max 3377 ms __ InnoDB Data, Pages, Rows ____________________________________________ Data Reads 21.51k 0.1/s Writes 666.48k 2.0/s fsync 324.11k 1.0/s Pending Reads 0 Writes 0 fsync 0 Pages Created 84.16k 0.3/s Read 59.35k 0.2/s Written 597.58k 1.8/s Rows Deleted 19.13k 0.1/s Inserted 6.13M 18.4/s Read 196.84M 590.6/s Updated 139.69k 0.4/s

Любая помощь по этому вопросу была бы значительно исправлена.

Спасибо!

задан DOS 29 марта '11 в 11:02 источник поделиться

qaru.site