MySQL *

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

После выхода новой версии MySQL в начале этого года, многие задумались о том стоит ли на неё переходить с более старых версий. Чтобы ответить на этот вопрос для себя, вначале необходимо понять, а что именно даст этот переход. В этой статье я постараюсь осветить новые, важные для меня, фичи, которые были включены в дистрибутив новой версии, анализ их производительности и работоспособность новой версии, а так же необходимость что-то менять в коде в связи в переписанным оптимизатором. Так как объем изменений действительно очень велик, для каждого пункта дам ссылку на оригинальную статью по тестирования производительности и исключу из описания воду.

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

Разбираясь как работает та или иная CMS приходится использовать различные инструменты, облегчающие работу.
Наиболее интересная тема — это работа с баз(ой|ами) данных. Естественно для изучения запросов и результатов запросов нужно использовать что-то универсальное. Что-то, что будет работать стабильно как с известным движком, так и с самописной системой.
Предположим у вас оказалась система управления контентом и вам необходимо посмотреть как реализовано добавление новых пользователей или смена паролей.

Большинство инструментов позволяющих мониторить работу с БД являются платными [раз, два]. Я хотел что-то более легкое и удобное, поэтому выбрал mysql-proxy. Хотя возможности утилиты гораздо шире чем мне требуется, я опишу лишь основное. Работает как под Windows, так и под Unix системами.

Цель данного поста — помочь начинающему разработчику быстро освоится и спроектировать простенькую базу с помощью инструмента для визуального проектирования баз данных MySQL Workbench от компании Oracle и получить её ER-модель и SQL-дамп.

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

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

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

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

5 февраля компания Oracle анонсировала выпуск стабильного релиза MySQL версии 5.6. В новой версии проделана огромная работа. Основные усилия были направлены на повышение производительности, масштабируемости и гибкости. Масштабным по значимости изменениям подвергся движок InnoDB.

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

Очень часто в интернете встречаюсь со статьями, в которых приводят кучу примеров с якобы странным поведением MySQL по сравнению с другими БД. Чтобы стало понятно, о чём я говорю, приведу несколько примеров:
1. Деление на ноль возвращает NULL вместо ошибки
2. Выход за диапазон допустимых значений неявно приводит число к допустимому значению, а не к ошибке и откату транзакции
3. Вставка неверного типа данных также приводит к неявному преобразованию и успешному выполнению операции
Таких примеров я могу привести огромное число, но цель статьи не сделать очередное собрание высосанных из пальца примеров, а объяснить, почему происходит то или иное действие. Вся эта мистика MySQL давно описана в документации и легко объяснима, в чём вы сможете убедиться сами, прочитав статью до конца.
Для меня эта первая статья на хабре, поэтому я старался писать дотошно подробно. Уверен, что она будет полезна всем, кто работает с MySQL. Большую помощь в написании статьи оказала подготовка к сдаче на сертификат разработчика MySQL, а точнее книга «MySQL Certification Study Guide».
Итак, мой друг, начнём!

Все мы знаем про то, что QUERY CACHE бывает очень полезен, если вам необходимо оптимизировать часто повторяющиеся запросы к базе данных, при условии низкого числа DML операций (если вы совсем не знакомы с данной функциональностью, перевод официальной документации по настройке этой структуры базы данных можно почитать тут). При настройке параметров кэширования запросов на сервере часто встает вопрос, а что же именно содержится в QUERY CACHE'е на данный момент. Поиск ответа на этот вопрос в свое время поставил меня в тупик. Единственный дельный ответ, который я посчитал в то время абсолютно верным, я нашел тут. Ответ гласил буквально следующее:

there is no way to find what queries are now cached

т.е. данного способа не существует. Лишь спустя некоторое время я понял, что это совсем не так, и существует вполне легальный способ получения содержимого данной структуры БД без патчинга исходных кодов. Этот способ — написание небольшого плагина для INFORMATION_SCHEMA.

В последнее время часто стал натыкаться на различные рассуждения людей, по поводу того, что MySQL — это плохо, это очень плохо — потому что… а вот дальше идут описания различных фич MySQL, которые четко документированы, но пользователь их просто не знает. Кто-то добавляет в БД данные без валидации и удивляется почему они сохранились в неверном формате, а кто-то описывает кучу особенностей myIsam движка, и на этих основаниях делает вывод, что MySQL это отстой — который невозможно использовать в реальных проектах. Всю документацию прочитать невозможно, и да — я с этим абсолютно согласен, но поверьте у нас есть куча других недокументированных и не менее интересных особенностей. Давайте начнем с малого, к примеру докажем, что NULL равно нулю.

На написание этого поста меня вдохновил мой друг Грег Янгблад, который показал мне на прошлой неделе одну интересную загадку в MySQL.

Даже если Вы используете GUI-инструменты для работы с MySQL, однажды Вам предстоит столкнуться с командной строкой. Поэтому полезно знать некоторые трюки, которые облегчают работу.

Примечание: эти команды работают только для Unix/Linux.

В связи с тем, что накопилось несколько вопросов и решений по работе со временем, решил сделать небольшой обзор.

Это совсем краткий пост о причинах возникновения Deadlock

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

«Deadlock found when trying to get lock; try restarting transaction»

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

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

Чтобы уменьшить нагрузку на диск и ФС, придумали partitioning, по простому — секционирование. Файл с данными таблицы разрезается по какому-то условию на несколько не больших файлов — партиций. Для случая с логами разумно партиционировать таблицы по полю, содержащему даты события. Часто бывает разумно резать таблицу на partition по году по месяцу или по дням месяца/недели.

Что-то подсказывает что резать придется по полю timestamp.

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

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

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

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

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

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

Несколько недель назад, я работал над проблемой клиента, который столкнулся с падением производительности БД и даже ее отказами, которые происходили приблизительно каждые 4 недели. Ничего особенного в окружении, в железе или запросах. В сущности, большей частью базы данных была одна таблица, в которой присутствовали, кроме прочего, INT AUTO_INCREMENT PRIMARY KEY и UNIQUE KEY.

Запросы, работающие с этой таблицей, почти все были типа INSERT ... ON DUPLICATE KEY UPDATE (далее — INSERT ODKU), где столбцы, перечисленные в INSERT, соответствовали столбцам с UNIQUE KEY. И выполнялись они с частотой, приблизительно 1500-2000 запросов в секунду, непрерывно 24 часа в сутки. Если вы хороши в математике, то наверное, уже догадались в чем дело.

В mysqlnd появилась возможность выполнять запросы к MySQL асинхронно, то есть продолжить работу скрипта не дожидаясь выполнения запроса и формирования результата. Преимущество такого подхода очевидно, ведь можно выполнить массу полезной работы во время ожидания запроса, но для начала я приведу немного другой пример:

Допустим у Вас есть 3 запроса (q1, q2, q3), каждый запрос выполняется за определенное время (t1, t2, t3), например так:

SELECT 1 AS val, SLEEP(1) AS sleep
SELECT 2 AS val, SLEEP(2) AS sleep
SELECT 3 AS val, SLEEP(3) AS sleep

В случае синхронного выполнения запросов, Вы сможете получить результаты их выполнения через t1 + t2 + t3 (ex: 6 секунд), а в случае асинхронного выполнения запросов уже за max(t1, t2, t3) (ex: 3 секунды)

Примеры работы с асинхронными запросами, а также другие примеры работы с mysqlnd можно найти на github

Что имеем?

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

Таблица(test) имеет следующую структуру:

• — pk_id ( первичный ключ )
• — id ( поле заполненное разными числами )
• — value ( поле заполненной с помощью rand() )

Первичный ключ не имеет дыр и начинается с 1.

mysqlnd — расширение PHP, которое является драйвером для работы с MySQL по умолчанию в PHP 5.4. Оно работает напрямую с MySQL сервером, а значит, MySQL клиент, а также оверхед на работу с ним, больше не требуется!