MySQL *

В недрах документации MySQL на dev.mysql.com я как-то обнаружил упоминание о том, что в случае, если используется MyISAM, можно получить прирост в скорости чтения из таблицы в 5-7 раз, если читать данные из таблицы самостоятельно. Мне довольно долго хотелось проверить этот факт и вот, наконец, у меня дошли руки до того, чтобы это попробовать. Что из этого вышло, читайте под катом

Сегодня я расскажу как сделать вашу СУБД MySQL ближе к стандартам PCI DSS. Для начала вот что у нас получится:
Консоль админ пользователя mcshadow

mcshadow:~$mysql --user=mcshadow --password=mike
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| mike@localhost |
+----------------+
mcshadow:~$mysql --user=mcshadow --password=root
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+

Доступ возможен как с правами рута, так и с правами смертного пользователя mike.

---

Консоль смертного пользователя mike

mike:~$mysql --user=mcshadow --password=mike
ERROR 1698 (28000): Access denied for user 'mcshadow'@'localhost'

Доступ к БД под администратором невозможен.

---

А тем временем в syslog

^{mysqld: User:mcshadow TRY access from:localhost with privileges:mike
mysqld: User:mcshadow SUCCESS access from:localhost with privileges:mike
mysql: SYSTEM_USER:'mcshadow', MYSQL_USER:'mcshadow', CONNECTION_ID:5, DB_SERVER:'--', DB:'--', COMMAND_RESULT:SUCCESS, QUERY:'select current_user();'
mysqld: User:mcshadow TRY access from:localhost with privileges:root
mysqld: User:mcshadow SUCCESS access from:localhost with privileges:root
mysql: SYSTEM_USER:'mcshadow', MYSQL_USER:'mcshadow', CONNECTION_ID:6, DB_SERVER:'--', DB:'--', COMMAND_RESULT:SUCCESS, QUERY:'select current_user();'
mysqld: User:mcshadow TRY access from:localhost with privileges:mike
mysqld: User:mcshadow FAILED access from:localhost with privileges:mike}

Сегодня во время перевода одного сайта с таблиц MyISAM на InnoDB, у последних выяснилась одна интересна особенность. Запрос на изменение движка для двух таблиц возвращал странную ошибку «Got error 139 from storage engine». После поиска информации на эту тему, было выяснено, что данная ошибка возникает тогда, когда какая-либо строка таблицы не вмещается в половину страницы памяти, с которыми работает MySQL. Страницы эти равны 16 Кб, а половина, стало быть, 8 Кб.

Само по себе ограничение довольно странное, но на первый взгляд кажется трудно достижимым, ведь как известно, MySQL хранят текстовые данные в хранилище, отдельном от табличных строк. Оказалось, что это верно только на половину. На самом деле InnoDB хранит в отдельном хранилище только «излишки», к коим он не относит первые 768 байтов каждого текстового поля. Т.е. любой текст будет отъедать от длины строки столько байт, сколько он содержит, но не больше 768. Несложно подсчитать, что максимальное число текстовых полей длиной от 768 байт, которое можно безопасно хранить в одной таблице — 10. И действительно, если запустить пример, все пройдет гладко. Но стоит увеличить количество полей хотя бы на одно, и мы получим ту же ошибку, что и в начале.

Много камней было брошено в адрес MySQL, ввиду отсутствия возможности трассировки сессий и снятия stats pack отчетов, показывающих какие именно события нагружают базу данных. Начиная с версии 5.5 MySQL наконец-то озадачился необходимостью решения данной проблемы и выставил прототип, который в будущем, возможно, приведет к созданию аналогичных инструментов в MySQL. Сегодняшний мой рассказ будет о таком мощном (к сожалению пока только для разработчиков MySQL) инструменте как PERFORMANCE_SCHEMA. Итак выставляем performance_schema=ON в конфигурационном файле my.cnf, и приступаем к изучению её ограниченных, но уже крайне интересных возможностей.

Есть задачи, которые в рамках реляционных СУБД не имеют универсальных решений и для того чтобы получить хоть какой-то приемлемый результат, приходится придумывать целый набор костылей, который ты потом гордо называешь “Архитектура”. Не так давно мне как раз встретилась именно такая.

Предположим, имеется некоторые сущности А и Б, связанные между собой по принципу One-to-Many. Количество экземпляров данных сущностей достаточно велико. При отображении сущностей для пользователя необходимо применить ряд независимых критериев, как для сущности А так и для сущности Б. Причем результатом применения критериев являются множества достаточно большой мощности — порядка нескольких миллионов записей. Критерии фильтрации и принцип сортировки задается пользователем. Как (я бы ещё спросил: Зачем им миллионы записей на одном экране? — но говорят надо) показать все это пользователю за время 0 секунд?
Решать такие задачи всегда интересно, но их решение сильно зависит от СУБД, под управлением которой крутится твоя база данных. Если у тебя в рукаве козырной туз в виде Oracle, то есть шанс, что эти костыли он подставит сам. Но спустимся на землю — у нас есть только MySQL, так что придется почитать теорию.

Оптимизация явно не является коньком MySQL сервера. Цель данной статьи объяснить разработчикам, которые плотно не работают с базами данных и иногда не понимают, по какой причине запрос, который успешно отрабатывает в других СУБД, в MySQL безбожно тормозит, каким образом оптимизируется конструкция between в MySQL.
MySQL использует rule based оптимизатор. Зачатки cost based оптимизации в нем конечно присутствуют, но не в должной мере, в какой их хотелось бы видеть. По этой причине часто мощности получаемых после применения фильтров множеств вычисляются неверно. Это приводит к ошибкам оптимизатора и выбору неверного плана выполнения. При чем полученные between оптимизации невозможно изменить явным указанием: индексов для выполнения запроса и порядка соединения таблиц.

Как говорит народная мудрость, “админы делятся на две категории: те, которые делают бэкапы, и те, которые уже делают”. В моем случае ответственность за несделанный бэкап упала на разработчика, то есть на меня самого. Данная статья посвящена тому, как найти выход из ситуации, подобной описанной. Надеюсь она будет полезна тем, кто не имея такого опыта, может столкнуться с подобной ситуацией.

Для тех, кто активно пользуется MySQL, не секрет, что начиная с версии 5.1, MySQL поддерживает события (events). Если вам нужно выполнять запросы или отдельные процедуры по расписанию, а перейти с запуска консоли на встроенный функционал MySQL было лень не было времени,

У MySQL, как известно, есть два недостатка*. Во-первых, язык запросов — SQL. Это исправляется с помощью HandlerSocket, о котором уже были статьи на хабре. Во-вторых, у него нет встроенного яваскрипта.

_{* — на самом деле, это шутка. недостатков у MySQL вряд ли именно два, но и заключаются они совершенно не в том, в чём я написал. а отсутствие яваскрипта — это, конечно, не недостаток. однако, если представить, что у MySQL нет SQL интерфейса вообще, то сравнивать его (как NoSQL-решение) нам придётся, в частности, с MongoDB, в котором интерпретатор яваскрипта есть.}

Итак, я начал работы по второму направлению и уже получил кое-какой результат:

Началось все с того, что в один прекрасный момент ядро прибило демона mysqld и mysql_safe автоматом его перезапустил и все бы хорошо, да только таблицы в БД использовались MyISAM. В итоге пришлось воспользоваться myisamcheck но это совсем другая история. В процессе проверки и починки индексов пострадала одна таблица и было принято решение восстанавливать из бекапов, хорошо, что раз в сутки делаются.

Исходные данные:

• имеем сервер БД с MySQL на борту;
• поврежденную таблицу логов(статистики) чего угодно, что постоянно заполняется и может например не использоваться какое-то время;
• суточный бекап;
• бинарные логи с последнего суточного(полного) бекапа.

Задача:

• сервер должен быть доступен для работы;
• новые данные должны попадать в таблицу;
• восстановить целостность данных.

Ожидаемый результат:
данные в поврежденной таблицы восстановлены без останова базы дынных;
таблица содержит все данные включая текущие.

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

Давайте исследуем этот вопрос более подробно и посмотрим, действительно ли JOIN — это плохо, и когда вообще стоит задумываться об этом.

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

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

Господа, не знаю, как давно это произошло, но теперь для поиска по mysql.com (встроенной в документацию форме поиска, которая и так работала очень плохо в последнее время) теперь нужно авторизоваться в oracle networks.



Лично я считаю это совершенно глупым поступком.

PS: да, раньше пользовались гуглом с «site:dev.mysql.com» потому что оно искало лучше, а теперь — потому что никак иначе.

PPS: судя по всему эта «фича» работает очень избирательно. У меня есть аккаунт в их сети, но я сейчас разлогинен. Как результат — оно спрашивает авторизацию. Если потереть куки (или попросту запустить браузер в анонимном режиме) — тогда ищет нормально. Но всё равно легче не стало :-(

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

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

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

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

Презентация

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

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

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

В MySQL есть такой специальный режим, предназначенный для введения в базу неправильных данных. Например, чтобы вместо 20000000000 вставлять в INT-поле 2147483647. Или наполнять базу несуществующими датами. Или обрезанными строками. Ну или мало ли для чего этот режим может тебе пригодится.

Режим этот называется «обычный режим».

Офсайт СУБД MySQL вчера взломан двумя злоумышленниками через банальное SQL injection. По ссылке опубликован отчёт о взломе и выложены некоторые части внутренней структуры базы данных, дамп паролей и т.д.

Vulnerable Target : mysql.com/customers/view/index.html?id=1170
Host IP : 213.136.52.29
Web Server : Apache/2.2.15 (Fedora)
Powered-by : PHP/5.2.13
Injection Type : MySQL Blind
Current DB : web

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

Кстати, те же два злоумышленника одновременно взломали и Sun.com тем же способом.

В ходе разработки сервиса по расчете статистики по управлению запасами для интернет-магазинов возникла задача быстро организовать импорт/экспорт таблиц между разными MySQL серверами. Поскольку надо было сделать просто и прозрачно — оптимизация будет впереди — решил воспользоваться авторскими рекомендация из документации по MySQL 5.0.

Привет, Хабрачитатель!
Полнотекстовый поиск данных в InnoDB – это известная головная боль многих разработчиков под MySQL / InnoDB. Для тех, кто не в курсе дела я объясню. В типе таблиц MyISAM есть полноценный полнотекстовый поиск данных, однако сама таблица исторически имеет ограничения, которые являются принципиальными в отдельных проектах. В более «продвинутом» типе таблиц InnoDB полнотекстового поиска нет. Вот и приходится мириться бедным разработчикам либо с ограничениями MyISAM, либо с отсутствием поиска в InnoDB. Я хочу рассказать о том, какие есть способы организовать полноценный поиск в InnoDB без магии и исключительно штатными средствами. Также будет интересно сравнить скоростные характеристики каждого способа.

Столкнулся недавно с задачей показа кнопок [ВПЕРЕД] [НАЗАД] на странице просмотра. Но сложность задачи в том, что сортировка может быть по произвольному полю таблицы.

Постановка задачи:

CREATE TABLE `contacts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name_prefix` varchar(50) DEFAULT NULL,
`name` varchar(100) DEFAULT NULL,
`infix` varchar(100) DEFAULT NULL,
`surname` varchar(100) NOT NULL,
`primary_email` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

Для наглядности объясню где это все работает:
есть две страницы, работающие с этой таблицей:
— index, на ней отображаются все записи из таблицы contacts, есть фильтр и есть сортировка по столбцам
— view, просмотр текущей записи из таблицы contacts. На это странице есть кнопки [ВПЕРЕД] [НАЗАД], с учетом фильтра и сортировки заданной на странице index;
Трудность возникла в этих двух кнопках.

В этой статье будут описаны различные настройки MySQL, преимущественно те, которые влияют на производительность. Для удобства все переменные разделены по разделам (базовые настройки, ограничения, настройки потоки, кэширование запросов, тайминги, буферы, InnoDB). Сначала уточним имена некоторых переменных, которые изменились в версии 4 MySQL, а в сети продолжают встречаться и старые и новые варианты имен, что вызывает вопросы.