PHP PDO Mysql Вставить производительность

Question

PHP PDO Mysql Вставить производительность

Обычно я не использую много SQL-кода в PHP, но недавно один из друзей заставил меня его отладить.

Я использую PDO с PHP для вставки некоторых данных, но производительность вставки ужасна. Простой цикл из 151 вставки занимает почти 6 секунд! и я заблудился относительно того, почему.

Вот мой код:

<?php

$database='XXXXXX';
$username='XXXXXX';
$password='XXXXXX';
$hostname='127.0.0.1';

$inserted=0;
$counted=0;
$dsn = 'mysql:dbname='.$database.';host='.$hostname.'';
$start=microtime(true);
try {
$data = new PDO($dsn, $username, $password, array(PDO::ATTR_EMULATE_PREPARES => false));
} catch (PDOException $e) {
echo('Connection failed: ' . $e->getMessage());
}
for($i=1;$i<=150;$i++) {
$time=time();
$query=$data->prepare("INSERT INTO `tbl_temp` (aid, bid) VALUES (?, ?)");
$query->execute(array($i, $time));
}
$data=null;
print "Took: ".(microtime(true)-$start)." S to insert $i records\n";

// Took: 5.569482088089 S to insert 151 records <--- result
?>

Я пробовал тот же код с использованием bindParam, и скорость примерно одинакова. Сервер имеет 8-ядерный процессор Xeon и 64 ГБ оперативной памяти. Сценарий запускается из командной строки (php-cgi), а база данных и таблица новые и пустые. Тип базы данных — InnoDB. Кто-нибудь может указать мне правильное направление, где искать, почему это так медленно? Потому что я уверен, что MySQL никогда не был таким медленным!

0

innodb mysql pdo php prepared-statement

Решение

Другие решения

Как сказал Саймон в комментарии, нет смысла делать подготовку в цикле. Это приведет к отправке 300 запросов в базу данных, каждый раз для подготовки и включения для фактической вставки. Использование оператора prepare раньше вызовет только 151 запрос:

$query = $data->prepare("INSERT INTO `tbl_temp` (aid, bid) VALUES (?, ?)");
for ($i = 1; $i <= 150; $i++) {
$time = time();
$query->execute(array($i, $time));
}

Другая идея может заключаться в том, чтобы использовать вместо этого комбинированный оператор множественной вставки. Я думаю, что это может иметь лучшую производительность, но я не совсем уверен:

$query = 'INSERT INTO `tbl_temp` (aid, bid) VALUES';
for ($i = 1; $i <= 150; $i++) {
if ($i == 1) {
$query .= ' ('.$i.', '.time().')';
} else {
$query .= ', ('.$i.', '.time().')';
}
}
$data->exec($query);

0

Оказывается, что параметр внутри сервера SQL является причиной его возникновения. Насколько я могу судить, фиксация транзакции была сброшена на диск при каждой записи:

innodb_flush_log_at_trx_commit=0

Это ACID-совместимые настройки по умолчанию для новой установки.

Я изменил свои настройки на это

innodb_flush_log_at_trx_commit=2

Это позволяет ТОЛЬКО отключение питания или сбой ОС, чтобы стереть буфер транзакций / журнал, а не сбой mysqld.

Для некоторых людей, которым нужно, чтобы значение «D» ACID было на 100% истинным, вам следует оставить этот параметр в покое.

0

Источник

Accepted Answer

нашел этот. Производительность в PDO / PHP / MySQL: транзакция или прямое выполнение. так что попробуй это.

$query = $data->prepare("INSERT INTO `tbl_temp` (aid, bid) VALUES (?, ?)");
try {
$data->beginTransaction();
for($i=0; $i < 150; $i++) {
$time = time();
$query->bindValue(1, $i, PDO::PARAM_INT);
$query->bindValue(2, $time, PDO::PARAM_STR);
$query->execute();
}
$data->commit();
} catch(PDOException $e) {
$data->rollBack();
}

2