Почему SQLite настолько медленный (~ 2 q/s) на конкретной машине?

На одном из моих серверов (i7 Ivy Core, 32 ГБ оперативной памяти, Debian 6 @64bit, PHP 5.4.10) У меня очень медленные вставки с SQLite. Следующая тестовая программа сообщает только 2.2 вставки/сек. (14 секунд для вставки 30 строк).

unlink("test.db");

$db = new PDO('sqlite:test.db');

$db->exec("CREATE TABLE test (dummy INT)");

$count = 30;

$t = microtime(true);
for ($i=0; $i<$count; $i++) {
  $db->exec("INSERT INTO test VALUES ($i)")
   or die("SQLite error: ".$db->errorInfo()[2]."\n");
}
$elapsed = microtime(true)-$t;
echo sprintf("%d inserts in %.3f secs (%.1f q/s)\n", 
  $count, $elapsed, $count/$elapsed);

Выход:

$ time php test.php
30 inserts in 13.911 secs (2.2 q/s)

real    0m14.634s
user    0m0.004s
sys     0m0.016s

Я знаю, что могу ускорить это, обернув BEGIN/END вокруг операторов insert (что дает мне 200000 q/s), но даже без транзакции это должно быть намного быстрее. На других (более старых) машинах (одна и та же версия PHP) я достигаю 300+ запросов/сек без явных транзакций.

Что может быть причиной этого? Должен ли я настраивать Sqlite или O/S?

Ответы

Ответ 1

Я сделал аналогичный тест на машине с 64-разрядным Linux, используя strace -C -tt, чтобы иметь представление о том, где SQLite3 занимает время.

% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
 99.03    0.004000          32       124           fsync
  0.64    0.000026           0       222           mprotect
  0.32    0.000013           0       216           munmap

Очевидная задержка заключается в функции fsync, которая:

  • конфигурируемый
  • зависит от общего ввода/вывода диска (проверьте iotop, iostat)
  • сильно зависит от IOSS (следовательно, файловая система и распределение диска - вы можете получить одно значение на ext3, другое на xfs и третье на btrfs)
  • зависит, конечно, косвенно, от основного оборудования и его особенностей или настроек.

Изменяя синхронизацию, моя производительность SQLite3 увеличивается примерно в три тысячи:

$db = new PDO('sqlite:test.db');

$db->exec('pragma synchronous = off;');

У меня тоже есть два разных значения на двух очень похожих машинах (один имеет ext4, другой XFS, но я не уверен, что это основная причина - их профили нагрузки также различны).

Кстати, используя подготовленные операторы примерно удваивает скорость выполнения на самом быстром уровне (от 45 тыс. до 110 тыс. INSERT, в партиях 3000, так как на этой скорости 30 INSERT обязаны давать ложные тайминги ) и поднимает самую низкую скорость от примерно 6 до примерно 150.

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

При проектировании системы с нуля, некоторые тесты на разных FS, безусловно, целесообразны.

Тесты на разных файловых системах (один и тот же компьютер)

ext4 (acl,user_xattr,data=order)         5.5 queries/s
using transactions                       170 queries/s
disabling fsync                        16000 queries/s
using transactions and disabling fsync 47200 queries/s

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

tmpfs                                  13700 queries/s
disabling fsync                        15350 queries/s
enabling transactions                  47900 queries/s
using transactions and disabling fsync 48200 queries/s

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

UPDATE: чтобы сжать еще больше производительности, можно также поместить журнал SQLite в память с помощью pragma journal_mode=MEMORY;