MySQL *

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

Здравствуйте.

Хочу поделиться опытом миграции боевой базы данных с MySQL 5.0 на Percona Server 5.5 под нагрузкой почти без отрыва от производства.

Опишу вкратце эволюцию нашей базы до текущего состояния

База у нас древняя, пережила несколько апгрейдов MySQL. Начинали с MySQL 3.x. С ростом нагрузки, уже на MySQL 5.0, настроили репликацию и подключили еще один сервер для чтения. Тогда мы это делали стандартными средствами MySQL, без привлечения xtrabackup — полностью блокировали сервер на время создания мастер-дампа и вывешивали на сайтах заглушки.

Затем встала следующая проблема — на томе с данными стало заканчиваться место. Плюс InnoDB-хранилище исторически располагалось в одном файле. Было рассмотрено много вариантов решения. Начиная от размещения базы на iSCSI-томе и заканчивая перетыканием в рейд более емких дисков, расширением на них volume group / logical volume с последующим расширением файловой системы.

В качестве временного варианта решили подключить iSCSI-том из виртуалки под VMWare vCloud (не реклама, честно!). vCloud стоит у нас под боком.

Дело было вечером, делать было нечего, но дурная голова уркам покоя не давала… Данный пост создан как результат чисто-академического интереса. А началось все с того, что при разработке небольшого клиентского приложения для своих нужд, реализованного на Javascript, появилась необходимость взаимодействовать с уже существующей базой, где хранятся искомые данные. База — MySQL. Один из простых способов — реализация серверного скрипта (на PHP или еще каком языке), который по входящим параметрам делает нужный запрос и возвращает результат в JSON виде.

Другой вариант — это DBSlayer-прокси для MySQL. Кто про него не слышал, рассказываю в крадце: был создан в недрах New York Times как средство абстракции и балансирования нагрузки на БД. Подробнее можно почитать на сайте code.nytimes.com/projects/dbslayer/wiki/WhyUseIt. DBSlayer предоставляет API на основе JSON, известен в кругу NodeJS разработчиков.

Но это тоже не наш метод. Под катом приведено простое решение данной задачи на BASH.

Введение

MySQL — весьма противоречивый продукт. С одной стороны, он имеет несравненное преимущество в скорости перед другими базами данных на простейших операциях/запросах. С другой стороны, он имеет настолько неразвитый (если не сказать недоразвитый) оптимизатор, что на сложных запросах проигрывает вчистую.

Прежде всего хотелось бы ограничить круг рассматриваемых проблем оптимизации «широкими» и большими таблицами. Скажем до 10m записей и размером до 20Gb, с большим количеством изменяемых запросов к ним. Если в вашей в таблице много миллионов записей, каждая размером по 100 байт, и пять несложных возможных запросов к ней — это статья не для Вас. NB: Рассматривается движок MySQL innodb/percona — в дальнейшем просто MySQL.

Предисловие

Скорей всего, матерым системным администраторам статья будет не очень интересна. В первую очередь она ориентирована на новичков, а также на людей, которые столкнулись с подобной проблемой — необходимостью удалить огромное количество файлов из одной папки в ОС Linux (Debian в моем случае), а также с закончившимся местом на диске, когда df -h выдает что почти 30% свободно.

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

Общий вид: \033[Xm, где X — это значение параметра (цифра). Например, echo -ne "\033[34mHELLO" выведет синим цветом «HELLO». Таблицу цветов и других доступных параметров (подчеркивание, мигание и т.п.) можно получить в документации man console_codes в разделе «ECMA-48 Set Graphics Rendition». Обычно поддержка цвета интегрирована в само приложение, но mysql-client не входит в число таких программ.

В интернете не раз был встречен вопрос о разукрашивании консоли mysql, но нигде не нашлось рецепта. Только общие слова «может быть состряпать обертку» или «посмотрите в исходном коде». Такой вопрос на StackOverflow жил без ответа более 2 лет! «Жил» было специально употреблено в прошедшем времени, потому что ответ нашелся.

Поможет нам утилита grc. Она доступна в большинстве дистрибутивов и о ней многие знают. Но как обернуть в нее вывод mysql-client?

Решил разобраться в вопросе блокировок в InnoDB. Получилась такая вот краткая шпаргалка. Может кому пригодится. Буду благодарен сообществу за найденные неточности

И так, в пределах одной транзакции, после…

Это перевод заметки Исчезновение набора тестов или очередная часть MySQL стала закрытой? (Disappearing test cases or did another part of MySQL just become closed source?)

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

Update: из заголовка статьи убрано слово «неудачной». Подробности ниже!

Рассказывая в своей статье о типичных заблуждениях, связанных с защитой от SQL инъекций, среди прочих я отметил тот факт, что серверные подготовленные выражения не работают в PHP по заявленному эффективному сценарию — 1 раз prepare(), потом 1000 раз executе().

Ну, то есть, в теории-то они работают — в пределах одного запуска скрипта. Но много ли вы знаете скриптов (написанных профессиональными программистами), которые выполняют кучу одинаковых запросов? Вот я тоже не знаю. Повторяющихся запросов (каких-нибудь множественных апдейтов) — доли процента, а в массе своей запросы уникальные (в пределах одного скрипта).
Соответственно, для нашего уникального запроса сначала выполняется prepare(), потом — execute(), потом скрипт благополучно умирает, чтобы, запустившись для обработки следующего HTTP запроса, заново выполнять prepare()… Как-то не слишком похоже на оптимизацию. Скорее — наоборот.
Как верно заметили в комментариях, я должен был упомянуть исключения в виде консольных скриптов и демонов, которые долго держат соединение с БД. Однако основная масса PHP скриптов всё же работает на фронтенде, умирая после выполнения пары десятков запросов.

Но неужели нет способа как-то закэшировать подготовленный запрос между запусками?

Я думаю, что в процессе изучения той или иной СУБД каждый из вас не раз изобретал велосипеды для решения своих задач, не зная о существовании той или иной функции или приема, которые бы могли в разы ускорить выполнение запросов и уменьшить объем кода. В данной статье я хочу поделиться с вами своим опытом работы с очень «добрым» и «отзывчивым» MySQL, часто позволяющему программисту делать вещи, которые другие СУБД переварить бы не смогли. Материал будет полезен скорее тем, кто только решил углубиться в чудесный мир запросов, но возможно и опытные программисты найдут тут что-то интересное.