Лекция
Привет, сегодня поговорим про блокировки в mysql innodb, обещаю рассказать все что знаю. Для того чтобы лучше понимать что такое блокировки в mysql innodb, myisam , настоятельно рекомендую прочитать все из категории Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL.
Одновременный доступ нескольких клиентов к хранилищу данных может приводить к ошибкам различного типа. Например, одновременное чтение одним клиентом и запись другим клиентом одной и той же строки таблицы с большой вероятностью приведет к сбою или чтению некорректных данных. Механизмы блокировок позволяют избежать ситуаций одновременного доступа к данным, регламентируя механизм взаимодействия пользователей между собой.
MySQL от имени одного из клиентов накладывает блокировку на определенный ресурс, при этом другие клиенты ждут освобождения блокировки. Блокировка может быть на уровне таблиц (блокируется таблица) или на уровне строк (блокируются определенные строки таблицы). В механизме хранения MyISAM (используемом по умолчанию) реализована табличная блокировка, а в механизме InnoDB построчная. Построчная блокировка достигается посредством усложнения структуры хранилища: в MyISAM структура файла с данными представляет собой простое перечисление строк таблицы, тогда как хранилище InnoDB структурировано и поддерживает мультиверсионность данных. Поэтому, InnoDB выигрывает в приложениях, в которых происходит многопоточное изменение данных в одну и ту же таблицу, несмотря на необходимые потери на обслуживание более сложного хранилища.
Блокировки бывают двух видов: на чтение и на запись.
Блокировка может быть наложена явно или неявно.
В случае явной блокировки можно получить выигрыш производительности за счет однократного назначения блокировки и задержки записи обновленных индексов до на время блокировки. При назначении явной блокировки, указываются имя таблицы и тип блокировки:LOCK TABLES Country READ, City WRITE;Оператор UNLOCK TABLES не имеет аргументов и снимает все блокировки, установленные явно в рамках текущей сессии.
Обратите внимание на следующие особенности явных блокировок:
Типы блокировок:
Примечание: системная переменная concurrent_inserts определяет возможность добавления данных в конец таблицы MyISAM, заблокированной для чтения. По умолчанию, эта переменная равна 1, что означает возможность добавления данных при отсутствии в таблице дыр. Таким образом, в случае неявной блокировки, для таблиц MyISAM устанавливается READ LOCAL, а не READ. Значение concurrent_inserts равное 0 запрещает добавление данных параллельно с чтением, а равное 2 разрешает вставку данных в конец таблицы даже при наличии в таблице дыр.
В статье рассмотрены лишь основные вопросы использования блокировок в MySQL. Не освещены транзакции, уровни изоляции, advisory lock и др. Напишите в комментариях какие из вопросов вам интересны.
Названием "баннерокрутилки на MySQL" озаглавлена заметка, посвященная примерам некорректного и неэффективного использования сервера MySQL. Типичным примером системы, работа которой с MySQL часто бывает неэффективной, является менеджер рекламных баннеров.
В качестве примера был проведен анализ программы PHP Ad Manager. Программа была выбрана случайно из десятков подобных ей на sourceforge.net.
Основные возможности программы перечислены ниже:
Ниже приведен сокращенный и упрощенный перечень запросов, который выполняется системой 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 использует блокировки на уровне таблиц для 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 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. Если это невозможно, постарайтесь максимально разнести эти операции во времени, либо использовать другие приемы, описанные выше.
Надеюсь, эта статья про блокировки в mysql innodb, была вам полезна, счастья и удачи в ваших начинаниях! Надеюсь, что теперь ты понял что такое блокировки в mysql innodb, myisam и для чего все это нужно, а если не понял, или есть замечания, то не стесняйся, пиши или спрашивай в комментариях, с удовольствием отвечу. Для того чтобы глубже понять настоятельно рекомендую изучить всю информацию из категории Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL
Ответы на вопросы для самопроверки пишите в комментариях, мы проверим, или же задавайте свой вопрос по данной теме.
Комментарии
Оставить комментарий
Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL
Термины: Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL