PDO DELETE неожиданно замедляется при работе с миллионами строк

Я работаю с таблицей MYISAM, содержащей около 12 миллионов строк. Метод используется для удаления всех записей старше указанной даты. Таблица индексируется в поле даты. При запуске in-code журнал показывает, что это занимает около 13 секунд, когда нет записей для удаления и около 25 секунд, когда есть записи за 1 день. Когда тот же запрос запускается в mysql-клиенте (принимая запрос из SHOW PROCESSLIST при запуске кода), у него нет времени вообще без записей и около 16 секунд для дневных записей.

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

Выдержка метода:

    try {
        $smt = DB::getInstance()->getDbh()->prepare("DELETE FROM " . static::$table . " WHERE dateSent < :date");
        $smt->execute(array(':date' => $date));
        return true;
    } catch (\PDOException $e) {
        // Some logging here removed to ensure a clean test
    }

Результаты журнала, когда 0 строк для удаления:

    [debug] ScriptController::actionDeleteHistory() success in 12.82 seconds

mysql client, когда 0 строк для удаления:

    mysql> DELETE FROM user_history WHERE dateSent < '2013-05-03 13:41:55';
    Query OK, 0 rows affected (0.00 sec)

Лог результатов, когда 1 день результаты для удаления:

    [debug] ScriptController::actionDeleteHistory() success in 25.48 seconds

mysql клиент, когда результаты для удаления: 1 день:

    mysql> DELETE FROM user_history WHERE dateSent < '2013-05-05 13:41:55';
    Query OK, 672260 rows affected (15.70 sec)

Есть ли причина, по которой PDO медленнее?

Приветствия.

Ответы на комментарии:

Это тот же запрос для обоих, поэтому индекс либо подбирается, либо нет. И это так.

EXPLAIN SELECT * FROM user_history WHERE dateSent < '2013-05-05 13:41:55' 
1   SIMPLE  user_history range  date_sent   date_sent   4   NULL    4   Using where

MySQL и Apache работают на одном сервере для целей этого теста. Если вы сталкиваетесь с проблемой загрузки, то mysql делает 100% за 13 секунд в запросе in-code. В запросе клиента mysql у него нет возможности зарегистрироваться сверху до завершения запроса. Я не вижу, как это не то, что PHP/PDO добавляет к уравнению, но я открыт для всех идей.

: date - это заполнитель PDO, а имя поля - дата, поэтому нет конфликта с ключевыми словами mysql. Тем не менее, использование: dateSent вместо этого вызывает задержку.

И уже пробовал без использования заполнителей, но пренебрегал упоминанием этого так хорошего звонка, спасибо! Вдоль линий этого. Еще одна задержка с PHP/PDO.

DB::getInstance()->getDbh()->query(DELETE FROM user_history WHERE dateSent < '2013-05-03 13:41:55')

И использование заполнителей в mysql-клиенте по-прежнему не вызывает задержки:

PREPARE test from 'DELETE FROM user_history WHERE dateSent < ?';
SET @datesent='2013-05-05 13:41:55';
EXECUTE test USING @datesent;
Query OK, 0 rows affected (0.00 sec)

Это таблица MYISAM, поэтому в ней нет транзакций.

Значение параметра $date отличается проверкой на отсутствие исключений или удалением одного дня, как показано в запросе на клиентском компьютере mysql, который берется из SHOW PROCESSLIST во время работы кода. В этом случае он не передается методу и выводится из:

    if (!isset($date)) {
        $date = date("Y-m-d H:i:s", strtotime(sprintf("-%d days", self::DELETE_BEFORE)));
    }

И в этот момент схема таблицы может быть подвергнута сомнению, поэтому:

CREATE TABLE IF NOT EXISTS `user_history` (
  `userId` int(11) NOT NULL,
  `asin` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `dateSent` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`userId`,`asin`),
  KEY `date_sent` (`dateSent`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Это приличный размерный сайт с большим количеством вызовов БД. Я не вижу никаких доказательств в том, как сайт работает в любом другом отношении, что предполагает, что это сводится к изворотливой маршрутизации. Тем более, что я вижу, что этот запрос на SHOW PROCESSLIST медленно проползает до 13 секунд при запуске в PHP/PDO, но при запуске в mysql не требуется никакого времени (в частности, ссылаясь на то, где не нужно удалять записи, что занимает 13 секунд в PHP/PDO).

В настоящее время речь идет только об этом конкретном запросе DELETE. Но у меня нет другого подробного оператора DELETE, подобного этому в этом проекте, или любого другого моего проекта, о котором я могу думать. Таким образом, вопрос особенно относится к запросам PDO DELETE в больших таблицах.

"Разве это не твой ответ?" - Нет. Вопрос в том, почему это происходит значительно дольше в PHP/PDO по сравнению с mysql-клиентом. SHOW PROCESSLIST показывает только этот запрос, требующий времени в PHP/PDO (без удаления записей). В mysql-клиенте совсем нет времени. Это точка.

Пробовал запрос PDO без блока try-catch, и все еще есть задержка.

И попытки с функциями mysql_ * показывают те же тайминги, что и при непосредственном использовании клиента mysql. Таким образом, палец прямо указывает на PDO прямо сейчас. Это может быть мой код, который взаимодействует с PDO, но поскольку никакие другие запросы не имеют неожиданной задержки, это выглядит менее вероятным:

Метод:

    $conn = mysql_connect(****);
    mysql_select_db(****);

    $query = "DELETE FROM " . static::$table . " WHERE dateSent < '$date'";
    $result = mysql_query($query);

Журналы для отсутствия записей, которые нужно удалить:

Fri May 17 15:12:54 [verbose] UserHistory::deleteBefore() query: DELETE FROM user_history WHERE dateSent < '2013-05-03 15:12:54'
Fri May 17 15:12:54 [verbose] UserHistory::deleteBefore() result: 1
Fri May 17 15:12:54 [verbose] ScriptController::actionDeleteHistory() success in 0.01 seconds

Журналы на один день, которые нужно удалить:

Fri May 17 15:14:24 [verbose] UserHistory::deleteBefore() query: DELETE FROM user_history WHERE dateSent < '2013-05-07 15:14:08'
Fri May 17 15:14:24 [verbose] UserHistory::deleteBefore() result: 1
Fri May 17 15:14:24 [debug] ScriptController::apiReturn(): {"message":true}
Fri May 17 15:14:24 [verbose] ScriptController::actionDeleteHistory() success in 15.55 seconds

И попробовал снова избегать вызовов в одноэлементный БД, создав PDO-соединение в методе и используя это, и это снова задерживается. Хотя нет других задержек с другими запросами, которые используют один и тот же сингл БД, поэтому стоит попробовать, но на самом деле не ожидали никакой разницы:

    $connectString = sprintf('mysql:host=%s;dbname=%s', '****', '****');
    $dbh = new \PDO($connectString, '****', '****');
    $dbh->exec("SET CHARACTER SET utf8");
    $dbh->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

    $smt = $dbh->prepare("DELETE FROM " . static::$table . " WHERE dateSent < :date");
    $smt->execute(array(':date' => $date));

Метод вызова с регистратором времени:

    $startTimer = microtime(true);
    $deleted = $this->apiReturn(array('message' => UserHistory::deleteBefore()));
    $timeEnd = microtime(true) - $startTimer;
    Logger::write(LOG_VERBOSE, "ScriptController::actionDeleteHistory() success in " . number_format($timeEnd, 2) . " seconds");

Добавлен PDO/ATTR_EMULATE_PREPARES в DB:: connect(). Все еще имеет задержку при удалении записей вообще. Я не использовал это раньше, но он выглядит как правильный формат:

   $this->dbh->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);

Current DB:: connect(), хотя, если были общие проблемы с этим, наверняка это повлияло бы на все запросы?

public function connect($host, $user, $pass, $name)
{
    $connectString = sprintf('mysql:host=%s;dbname=%s', $host, $name);
    $this->dbh = new \PDO($connectString, $user, $pass);
    $this->dbh->exec("SET CHARACTER SET utf8");
    $this->dbh->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
 }

Индексы показаны выше в схеме. Если он был непосредственно связан с перестройкой индексов после удаления записи, то mysql будет использоваться в то же время, что и PHP/PDO. Это не так. Это проблема. Это не то, что этот запрос медленный - он ожидал, что потребуется некоторое время. Это то, что PHP/PDO заметно медленнее запросов, выполняемых в клиенте mysql, или запросов, которые используют mysql lib в PHP.

MYSQL_ATTR_USE_BUFFERED_QUERY попытался, но все же задержка

DB - стандартный одноэлементный шаблон. DB:: getInstance() → getDbh() возвращает объект соединения PDO, созданный в методе DB:: connect(), показанном выше, например: DB:: dbh. Я считаю, что я доказал, что одноэлементная БД не является проблемой, поскольку при создании PDO-соединения все еще существует задержка в том же методе, что и запрос (6 изменений выше).

Я нашел, что это вызывает, но я не знаю, почему это происходит в эту минуту.

Я создал тестовый SQL, который создает таблицу с 10 миллионами случайных строк в правильном формате и PHP script, который запускает оскорбительный запрос. И это совсем не время в PHP/PDO или mysql-клиенте. Затем я изменяю сортировку БД по умолчанию по умолчанию latin1_swedish_ci на utf8_unicode_ci, и она занимает 10 секунд в PHP/PDO и совсем не время в mysql-клиенте. Затем я меняю его на latin1_swedish_ci, и он снова не требует времени в PHP/PDO.

Тада!

Теперь, если я удалю это из соединения с БД, он отлично работает либо в сортировке. Итак, здесь есть какая-то проблема:

 $dbh->exec("SET CHARACTER SET utf8");

Я буду исследовать больше, затем последую позже.

Ответ 1

Итак...

В этой статье объясняется, где был недостаток.

Является ли "SET CHARACTER SET utf8" " необходимо?

По сути, это было использование:

$this->dbh->exec("SET CHARACTER SET utf8");

который должен был быть указан в DB:: connect()

$this->dbh->exec("SET NAMES utf8");

Моя ошибка полностью.

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

Если кто-то нуждается в подтверждении моих результатов, эта серия SQL-запросов будет устанавливать тестовую базу данных и позволит вам проверять себя. Просто убедитесь, что индексы корректно включены после ввода тестовых данных, потому что по какой-то причине мне пришлось сбросить и повторно добавить их. Он создает 10 миллионов строк. Может быть, меньше будет достаточно, чтобы доказать смысл.

DROP DATABASE IF EXISTS pdo_test;
CREATE DATABASE IF NOT EXISTS pdo_test;
USE pdo_test;

CREATE TABLE IF NOT EXISTS test (
  `userId` int(11) NOT NULL,
  `asin` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `dateSent` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`userId`,`asin`),
  KEY `date_sent` (`dateSent`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

drop procedure if exists load_test_data;

delimiter #
create procedure load_test_data()
    begin
        declare v_max int unsigned default 10000000;
        declare v_counter int unsigned default 0;

        while v_counter < v_max do
            INSERT INTO test (userId, asin, dateSent) VALUES (FLOOR(1 + RAND()*10000000), SUBSTRING(MD5(RAND()) FROM 1 FOR 10), NOW());
            set v_counter=v_counter+1;
        end while;
    end #
delimiter ;

ALTER TABLE test DISABLE KEYS;
call load_test_data();
ALTER TABLE test ENABLE KEYS;

# Tests - reconnect to mysql client after each one to reset previous CHARACTER SET

# Right collation, wrong charset - slow
SET CHARACTER SET utf8;
ALTER DATABASE pdo_test COLLATE='utf8_unicode_ci';
DELETE FROM test  WHERE dateSent < '2013-01-01 00:00:00';

# Wrong collation, no charset - fast
ALTER DATABASE pdo_test COLLATE='latin1_swedish_ci';
DELETE FROM test  WHERE dateSent < '2013-01-01 00:00:00';

# Right collation, right charset - fast
SET NAMES utf8;
ALTER DATABASE pdo_test COLLATE='utf8_unicode_ci';
DELETE FROM test  WHERE dateSent < '2013-01-01 00:00:00';

Ответ 2

Попробуйте проанализировать и оптимизировать таблицы:

http://dev.mysql.com/doc/refman/5.5/en/optimize-table.html

http://dev.mysql.com/doc/refman/5.5/en/analyze-table.html