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

В последнее время приходится достаточно часто заниматься оптимизацией производительности сайтов. И как правило «бутылочным горлышком» в производительности работы этих сайтов является именно БД, ошибки как в архитектуре так и в выполнении запросов. Начиная от неправильной расстановки индексов, либо совершенным их отсутствием, неправильным (неэкономным) выбором типов данных под определенное поле, заканчивая абсолютно нелогичной архитектурой БД и такими же нелогичными запросами.

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

По заявкам трудящихся решил написать еще одну статью, посвященную оптимизации запросов в MySQL.

В прошлой статье habrahabr.ru/blogs/mysql/38907 рассматривались вопросы оптимизации LIMIT, GROUP BY, COUNT.

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

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

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

Рано или поздно многие крупные проекты сталкиваются с проблемами производительности при постраничной навигации по записям. Некоторые из них решают эту проблему ограничением количества доступных для просмотра записей (скажем, не больше 1000). Вполне приемлемое решение. Но в этом случаем могут возникнуть проблемы с индексированием сайта сторонними поисковиками, которые и представляют наибольшую угрозу. В этой статье я хотел бы отказаться от привычной для всех панели навигации вида «1..2..3..4..» в пользу простой «вперед… назад» (будет проще объяснить), но это не проблема реализовать подобное и с первым вариантом.
Более точно определить тему, назвав, какое количество записей считать достаточно большим для появления тормозов, не получится, так как эта цифра для всех разная и сильно зависит от того, насколько быстрые у Вас жесткие диски, сколько памяти, и какая часть Ваших данных уже закеширована в ней и тд. Но если Вы и Ваши сервера ощущают, что n-ная страница при выводе даётся тяжелее первой, и при этом не знаете, что с этим делать – статья для Вас. Но для начала, я хотел бы на пальцах объяснить, почему ОНО работает медленно.

Кстати, тест происходит на виртуальной машинке, работаю я с СУБД под рутом, версия MySQL – 5.0.32.

Поводом для написания данной статьи послужили некоторые дебаты в одной из групп linkedin, связанной с MySQL, а также общение с коллегами и хабролюдьми :-)

В данной статье хотел написать что такое вообще JOINы в MySQL и как можно оптимизировать запросы с ними.

На Хабре некоторое время назад спрашивали про курсы по оптимизации MySQL. Мы разработали такой курс и проводим его дистанционно через интернет. Вот программа курса.

Курс продолжается 2 месяца (начать можно в любое время) и построен на выполнении практических заданий под руководством преподавателя. Все задания взяты из реальной практики, преподаватель ответит на все возникающие вопросы. Действует гарантия 100% возврата средств в течение первых двух недель по первому требованию.

Кризис не самое оптимальное время, чтобы пиарить новую услугу, но это оптимальное время для получения новых знаний. Получить знания можно не только на нашем курсе, но и используя открытые источники:
I. Видеозаписи докладов на Highload++:

• Петр Зайцев, «Масштабирование InnoDB»
• Константин Осипов, «Масштабирование СУБД MySQL на системах с многоядерной архитектурой»,
• Ваш покорный слуга, «Архитектура MySQL Cluster»
• Василий Степанов, мастер-класс «Оптимизация работы с MySQL»

II. Статьи по оптимизации MySQL на webew.ru
III. Задать интересующий вопрос на форуме SQLinfo.ru

Сегодня был на конференции "Производительность MySQL". Докладчик был Дмитрий Кравчук. Спасибо maghamed, 60% конференции я уже знал.
Сама конференция была интересной, в хронографическом порядке зарождения MySQL. Начиная от 1995 года, когда собрались Монти и Дэвид, до сегодняшнего дня, MySQL Perf версии.
Что не понравилось:

• Sun имеет инсайдерскую версию MySQL Perf (производительность чуть выше чем в 5.4), которую не спешит выкатывать.
• Почти всю конференцию было слышно «Sun это, Sun то».
• Обделили maatkit вниманием (может потому что вражеская разработка?)
• Порадовал слушатель, в компании которого была «масштабируемая система» — 1500 запросов на 1 страницу. При этом их техлид считает memcache костылями.
• Не было бутербродов :(

Что понравилось:

• Докладчик :). Дмитрий отвечал на все вопросы, было живое обсуждение. В конце выступления пошли хинты, про которые я до этого не слышал и нигде не видел.
• Принцип «Доверяй, но проверяй». Дмитрий не верил никому, поэтому полностью сам тестил MySQL в производительности.
• В зале был человек от Percona, который иногда помогал Дмитрию с ответами.
• MySQL развивается! Не смотря на покупку Sun в последние годы много внимания было уделено производительности, что привело к появлению версии 5.4.

Пару хинтов для себя, чтобы не забыть:

• Каждая аппликация уникальная и сервер надо настраивать под конкретные нужды (ваш КО)
• Сейчас есть баг с innodb_max_dirty_pages_pct. Это значение просто игнорируется. Патч есть, в основную ветку еще вроде не внесли (могу ошибаться)
• Пока существует баг с innodb_max_dirty_pages_pct влиять на флаш «грязных страниц» можно через innodb_log_file (не спрашивайте почему, спросите у Димы)
• Интересная опция, про которую раньше не слышал — innodb_flush_log_trx_commit. Принимает значения 0, 1, 2. 0 — флаш каждую секунду (0 коммитов в сек = 1 флаш), 1 — флаш каждый коммит (10тыс коммитов в сек = 10тыс флашей), 2 — флаш каждую секунду, если был коммит (10 тыс коммитов в сек = 1 флаш). Оптимальный вариант для быстродействия естессно 2
• innodb_io_capacity — стоит выставить в зависимости от возможностей винчестера. Дмитрий предлагал 2000
• Query cache больше 20мб — зло
• С включенным double write buffer в некоторых случаях можно потерять до 30% производительности
• Redo log, bin log, Double Write buffer стоит хранить на разных винчестерах из-за random read для самой базы
• Иногда стоит поиграться с max_purge_log
• Блог Дмитрия dimitrik.free.fr

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

В чем же эта ошибка?

MySQL стал собственностью Oracle, есть ли альтернативы и как быстро движение вперед?.. Вроде как обобщающего обзорчика «who is who?» еще не было. Итак, обзорчик для тех кто «не в теме»

На AddConf будет мой доклад «Ускоряем MySQL в десятки раз с использованием HandlerSocket».

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

А так же будет обозрение и результаты использования совместно с технологией sphinx и в качестве модуля nginx и обзор аналогичной технологии innoDb-memcache.

Хочется услышать пожелания к материалам доклада (что добавить а что наф. не нужно), а так же приглашаю его прослушать в живую.

Презентация

PS. Графика при конвертации немного сбилась. В оригинале этого нет.

Промокод на 5% скидку «Александр Календарев — читаю» .

ссылки по теме:
Первый опыт работы с Handler Socket & php_handlersocket
Некоторые тонкости Update & Insert в Handler Socket
nginx модуль

Работая у крупнейшего провайдера РФ столкнулся с ситуацией, что происходит обращение абонента о проблемах в недалеком прошлом, т.е. вчера все было плохо, а сейчас заработало. Что делать в этом случае? Есть варианты использовать системы мониторинга, которые будут собирать ключевые параметры всех абонентских линий и хранить их некоторое время, и оператор ТП легко сможет получить доступ к этим данным для решения подобных ситуаций. Также, имея эти данные, можно давать автоматическую экспертную оценку по каждой абонентской линии, и при желании, на xDSL, автоматически подбирать наиболее подходящий профиль. Используя эти же данные, можно выявлять дефектные линии или линии с неудовлетворительными параметрами и устранять возможные проблемы абонентов еще до их обращения в ТП.
С первого взгляда задача не сложная, но когда количество оборудование легко перешагивает тысячи узлов доступа, а количество абонентских портов может исчисляться десятками тысяч появляются некоторые особенности настройки и запуска подобной системы, с максимальной автоматизацией всего.
Если интересно, добро пожаловать под кат

Начиная с версии 5.1 в MySQL появилась такая полезная фича как партиции. Конечно же большинство разработчиков БД сразу не побрезговали ей воспользоваться. Спустя пару лет работы я наконец пожал плоды всей ущербности реализации этой технологии специалистами MySQL AB …

Twitter опубликовала свои улучшения для MySQL.
Исходный код изменений распространяется под модифицированной лицензией BSD и располагается на GitHub
Более подробные изменения читайте под хабракатом.

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

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

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

Неожиданно поступила задача разобраться почему определенный сайт не работает столь быстро сколь хочется. В основе его CakePHP, в связке с Apache и MySQL. В статье описание процесса поиска узких мест и приведение в порядок на столько, на сколько это возможно.

Название сайта светить не буду — думаю, программисты сами узнают. Скажу лишь, что это приложение для социальной сети нагрузкой 70-150 тысяч посетителей в обычное время. Все усложняется тем, что периодически производится рекламная рассылка, которая привлекает около 200-300 тысяч посетителей за пару часов.

Итак, под катом описание всей борьбы на протяжении 4 дней.

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

После того, как ваши запросы оптимизированы, по идее у вас не должно возникать ситуаций, когда
1. Один запрос блокирует другие
2. Какие-то запросы блокируют друг друга
Мы стремимся к тому, чтобы таких ситуаций не возникало.

Потому хорошим «сторожем работоспособности» будет умный «Query killer»,
который будет отслеживать подозрительные ситуации и освобождать базу данных.

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

Мне часто задают один и тот же вопрос: как можно находить запросы, которые необходимо оптимизировать. Ведь, скажем, взглянув на отчет pt-query-digest, мы легко найдем медленные запросы или запросы, которые вызывают большую нагрузку на систему — но как мы поймем, существует или нет возможность сделать выполнение этого запроса быстрее? Полный ответ на этот вопрос определенно потребует комплексного анализа, так как существует много путей оптимизации запросов. Однако, существует одна очень полезная метрика, которую вы можете применить — соотношение между количество возвращенных запросом рядов и пройденными рядами.

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

Итак, я начал внедрять в своей организации Percona XtraDB Cluster — переводить базы данных с обычного MySQL сервера в кластерную архитектуру.

Коротко о задаче и вводные данные

В кластере нам нужно держать:

• БД нескольких веб-сайтов с пользователями
• БД со статистическими данными этих пользователей
• БД для тикет-систем, систем управления проектами и прочая мелочь

Иными словами, БД практически всех наших проектов, из тех что крутятся у нас на MySQL, теперь должны жить в кластере.

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

Что я успел понять про MySQL за несколько лет его разработки:

• развивать не ломая обратной совместимости его нельзя
• MySQL со сломанной обратной совместимостью никому не нужен.

Я опишу две серьёзные проблемы ДНК MySQL, с которыми косвенно сталкивается любой пользователь MySQL 5.1 и 5.5 (насчёт 5.6 не проверял, но не думаю, что этот момент поменялся).

Как вообще MySQL работает?