Баннерокрутилки на MySQL
Названием “баннерокрутилки на MySQL” озаглавлена заметка, посвященная примерам некорректного и неэффективного использования сервера MySQL. Типичным примером системы, работа которой с MySQL часто бывает неэффективной, является менеджер рекламных баннеров.
В качестве примера был проведен анализ программы PHP Ad Manager. Программа была выбрана случайно из десятков подобных ей на sourceforge.net.
Основные возможности программы перечислены ниже:
- Управление рекламными баннерами на нескольких доменах
- Подсчет количества хитов (показов) и кликов
- Хранение статистики в MySQL
- Онлайн-обновление статистики
Запросы, которые выполняются баннерокрутилкой
Ниже приведен сокращенный и упрощенный перечень запросов, который выполняется системой PHP Ad Manager при каждом показе баннера.
Получение домена, для которого выполняется прокручивание баннера
select * from domains where ...
Получение списка рекламных блоков, которые можно пользователю на этом домене показать
select * from ads
where active = 'Y'
and expiredate > 'ТЕКУЩЕЕ-ВРЕМЯ'
and domains LIKE '%ДОМЕН%'
order by lastdisplay;
Обновление информации о моменте последнего показа этой рекламы:
update ads set lastdisplay = 'ТЕКУЩЕЕ-ВРЕМЯ', hits='КОЛИЧЕСТВО ХИТОВ' WHERE adid = 'ИДЕНТИФИКАТОР'
Запись информации в лог
insert into adlog
SET adid = 'ИД-БАННЕРА',
type = 'hit',
remotehost = '....',
remoteaddr = '....',
site = '.....',
entrydate = '....';
Блокировки таблиц в MySQL
Для того, чтобы разобраться с причинами возможных проблем, необходимо несколько более детально осветить вопрос работы с блокировками таблиц в MySQL.
В соответствии с документацией к MySQL, MySQL использует блокировки на уровне таблиц для MyISAM, и блокировки на уровне строк для таблиц InnoDB. MySQL поддерживает два типа блокировок: на запись и на чтение. Блокировки на запись имеют преимущество перед блокировками на чтение. Это с одной стороны, приводит к тому, что запросы на вставку/обновление данных не “зависают” при большом количестве запросов на чтение. С другой стороны, при большом количестве запросов на обновление данных запросы на чтение могут дожидаться своей очереди очень долго.
Для демонстрации проблемы рассмотрим два примера. Первый пример приведен на рисунке, представленном ниже. В этом случае к серверу MySQL почти одновременно приходит четыре запроса на выборку (SELECT). Видно, что поскольку запросы SELECT имеют возможность одновременного выполнения, время ожидания результата каждого запроса фактически зависит только от времени выполнения непосредственно запроса.
Во втором случае схематично представлена ситуация, в которой к одной и той же таблице MySQL почти одновременно обращаются 4 запроса, из которых два запроса - это запросы на выборку, а другие два - запросы на обновление данных. Из-за того, что запросы на обновление встают в очередь на исполнение перед запросами на выборку, а также потому, что в MyISAM-таблице они не могут выполняться одновременно с другими запросами, время ожидания результата SELECT-а существенно увеличивается.
Дополнительно нужно отметить, что если бы первый запрос оказался “тяжелым” (с длительным временем исполнения), это еще более усугубило бы ситуацию второго случая. Если бы ни UPDATE, все SELECT-ы бы выполнялись одновременно, однако наличие одного UPDATE приводит к тому, что он разбивает очередь запросов на две: до себя и после, и SELECT-ы на третьем этапе не выполнятся до завершения первых двух этапов.
Оценка количества заблокированных запросов может быть выполнена так:
mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited | 15324 |
+-----------------------+---------+
Первая строка таблицы выводит количество запросов на блокировку, которые были удовлетворены сразу же, а вторая строка – количество запросов, которым пришлось ожидать перед получением требуемого доступа к таблице.
Утилита тестирования производительности
Для тестирования производительности можно воспользоваться программой mysqlslap, которая входит в состав MySQL 5.1.4 и выше (для lenny доступен в репозитории dotdeb).
Способы оптимизации производительности
Понижение приоритета UPDATE
Из приведенного выше описания видно, что проблемы возникают в том числе и из-за
того, что приоритет операций UPDATE выше приоритета операций на выборку данных.
Простое решение, которое, вероятно, временно поможет снизить остроту проблемы
состоит в том, чтобы использовать вместо запроса UPDATE запрос UPDATE
LOW_PRIORITY. Подробное описание синтаксиса команды доступно в официальной
документации
Использование временной таблицы
В ситуации большого количества запросов INSERT, которые могут блокировать запросы SELECT, разработчики MySQL рекомендуют использовать временную таблицу, данные из которой могут переноситься в основную с определенной периодичностью:
mysql> LOCK TABLES real_table WRITE, temp_table WRITE;
mysql> INSERT INTO real_table SELECT * FROM temp_table;
mysql> DELETE FROM temp_table;
mysql> UNLOCK TABLES;
Использование нереляционных баз данных
Нереляционные базы данных (по крайней мере, по заявлениям их разработчиков) в большей степени подходят для работы с информацией в режиме “количество обновлений сопоставимо с количеством выборок”, чем MySQL. В качестве примера для такого использования может подойти mongodb.
mongodb умеет выполнять дешевые операции обновления “in-place” (инкрементирование счетчиков и т.п.) без реальной передачи данных по сети, а также имеет режим вставки “upsert” (обновить объект, либо создать, если такой объект еще не найден). За подробностяим по использованию этих команд лучше всего обратиться к документации.
Ниже приведены две ссылки на статьи в блоге MongoDB (на англ.):
При разработке новых систем
При разработке новых систем старайтесь избегать такой архитектуры, при которой количество запросов на обновление данных сопоставимо с количеством запросов SELECT. Если это невозможно, постарайтесь максимально разнести эти операции во времени, либо использовать другие приемы, описанные выше.
наших серверов хостинга
