Тюнинг DBD::mysql

Ниже описаны два параметра соединения с сервером БД MySQL с помощью модуля DBD::mysql, которые могут использоваться для повышения производительности.

1. mysql_server_prepare - операторы сохраняются на стороне сервера.

Активируется с помощью добавления выражения mysql_server_prepare=1 к DSN, напр.:


$dbh = DBI->connect('DBI:mysql:db1:localhost;mysql_server_prepare=1', 'user', 'pass');


По идее такой подход должен повышать производительность массовых запросов. Проверяем на заведомо слабой машине (сервер и клиент на одном и том же компьютере). Миллион insert выполняется быстрее на 11%, миллион update - на 19%, миллион delete - на 11%. Стоило оно того? Не знаю. Но вроде противопоказаний не обнаружено.


2. mysql_use_result - результат запроса не сохраняется на клиенте перед использованием (умолчательное поведение называется mysql_store_result). Может быть применено:
   1. на уровне database handler: добавлением mysql_use_result=1 к DSN или оператором

      $dbh->{mysql_use_result}=1;

   2. или на уровне statement handler - в операторе:

      $dbh->prepare('select * from bigtable', {mysql_use_result => 1});

      или (после prepare и до execute) оператором:

      $sth->{mysql_use_result} = 1;

   Пользу от такого поведения можно почуствовать, если оператор возвращает очень много записей - порядка нескольких миллионов. При умолчательном mysql_store_result perl (по крайней мере, v 5.8.8 на FreeBSD 7.0) пожирает всю доступную память - и умирает, а при mysql_use_result - ничего, живет - и даже обходится не очень большим количеством памяти. К недостаткам этого метода работы с результатом man-страница DBD::mysql (3) относит

   tends to block other processes,

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

Транзакции в DBD::mysql

Движок INNODB в MySQL поддерживает транзакции, но по умолчанию DBI работает в режиме AutoCommit. Ниже описывается, как удалось заставить DBD::mysql работать с транзакциями.

Обнаружено два работоспособных пути:

• в DBI->connect включать атрибут {AutoCommit => 0} (в man DBI написано, что его значение будет сброшено на умолчательную единицу первым же COMMIT, но на практике такое поведение в данной конфигурации не наблюдалось). Транзакция начинается автоматически после DBI->connect - явная посылка BEGIN не требуется - так же успешно сами по себе начинаются все последующие транзакции - очевидно, первой командой транзакции считается следующая после $dbh->commit или $dbh->rollback; Вызов команды $dbh->begin_work при выключенным AutoCommit приводит к ошибке (если мы установили атрибут RaiseError => 1, рекомендуемый документацией, то эта ошибка станет фатальной).
• если необходимо явно указывать начало транзакции, то в DBI->connect оставляем AutoCommit по умолчанию равным 1, транзакцию же будем начинать коммандой $dbh->begin_work. После $dbh->commit значение $dbh->{AutoCommit} сбрасывается в 1 - в полном соответствии с документацией - т.е. мы продолжаем работать в режиме без транзакций.

Наличие таких двух вариантов поведения, вероятно, не является особенностью собственно DBI - в главе START TRANSACTION, COMMIT, and ROLLBACK Syntax документации по MySQL описаны такие же возможности - либо можно установить параметр сессии SET AUTOCOMMIT=0, и тогда транзакции будут неявно начинаться и записываться/сбрасыться по COMMIT/ROLBACK - либо можно при умолчательном AUTOCOMMIT=1 явно начинать транзакцию коммандой START TRANSACTION или BEGIN.