В данной статье оценивается применение SQLite для PHP-проектов, рассматриваются особенности многопоточной работы и способы ускорения работы.
Начало
Не так давно мне захотелось сделать свой сервис промо-кодов для одного своего проекта. В выборе платформы я не долго сомневался. Раз у меня есть сервер с развернутым PHP-окружением, то и сам сервис смело можно писать на PHP (тем более, что я с ним знаком). В качестве СУБД мне захотелось попробовать SQLite (хотя на сервере развернуты MySQL и PostgreSQL). Причина — проще переносить на “боевое” окружение (просто скопировать папку), проще разворачивать на “голом” сервере (кроме PHP ничего не нужно). Дополнительно хотелось упростить бэкап (планировал использовать снэпшоты диска + простое копирование получившегося “среза”).
Но прежде, чем использовать, необходимо проверить применимость и производительность данного решения.
Железо
Тестирование происходило на 2х объектах:
- Рабочая станция. Intel i5. 12GB RAM. SSD + HDD (WD Black). Windows 8. Используется OpenServer для запуска PHP.
- Облако в Selectel. Debian 6. Тут будет стоять сервис. Установлен LAMP в дефолтной конфигурации. Разве что Apache еще сильнее урезал, в угоду уменьшения потребления памяти.
Предварительная оценка
Для начальной оценки производительности был написан простой скрипт:
<?php
// Инициализация БД
function initDB()
{
$guid = '';
// Имя строим по случайному ГУИДу
if (function_exists('com_create_guid') === true)
{
$guid = trim(com_create_guid(), '{}');
}
else
{
$guid = sprintf('%04X%04X-%04X-%04X-%04X-%04X%04X%04X', mt_rand(0, 65535), mt_rand(0, 65535), mt_rand(0, 65535), mt_rand(16384, 20479), mt_rand(32768, 49151), mt_rand(0, 65535), mt_rand(0, 65535), mt_rand(0, 65535));
}
// Определяем полное имя БД
$file = "_$guid.db";
// Открываем БД (или создаем)
$db = new SQLite3($file);
// Создаем таблицу
$db->exec('CREATE TABLE foo (i INT, i2 INT)');
return $db;
}
// Операция тестирования БД
function testDB($db, $count)
{
for ($i=0; $i<$count; $i++)
testIteration($db, $i);
}
// 1 итерация тестирования
function testIteration($db, $iteration)
{
$db->exec('INSERT INTO foo VALUES ('.$iteration.', 1)');
}
// ---------- ИНИЦИАЛИЗАЦИЯ И ЗАПУСК ТЕСТА ----------
// Определяем количество инсертов
$COUNT = 1000.0;
if(isset($_REQUEST['COUNT']))
$COUNT = $_REQUEST['COUNT'];
// Иницализируем БД
$db = initDB();
// Начинаем замер времени
$start = microtime(true);
// Запускаем тест
testDB($db, $COUNT);
// Замер окончен
$time = microtime(true) - $start;
// Вывод результата
echo "$COUNT inserts per " . number_format($time, 2) . "sec. " . number_format($COUNT / $time, 2) . " operation per second";
?>
Последовательно вставляем 1000 записей в таблицу. Замеряем затраченное время. Вычисляем количество операций в секунду.
В принципе, для грубой оценки большего и не надо.
Обращаю внимание, что я специально не использовал транзакции. Каждая операция пойдет в собственной транзакции. Этот режим работы ближе к реальному использованию базы в многопоточном режиме.
А теперь результаты:
Для SSD диска получили оценку в 365 операций в секунду. Неплохо.
Для HDD получили всего 13 операций. Печально. Но что же скажет сервер?
А сервер порадовал. 210 операций.
В целом, оценка производительности на сервере вполне приличная. Можно продолжать тестирование работы PHP с SQLite. Только сразу же делаем отметку, что для сервера необходимо использовать быструю файловую подсистему (SSD).
В нашем случае, как я уже говорил, используется облако в Selectel. SSD выступает там как кэш между дисками и облачными машинами (во всяком случае, так я понял по рассказам небезызвестного amarao).
Многопоточный режим работы
Теперь попробуем ответить на вопрос, сумеет ли база работать в многопоточном режиме.
Читаем замечательный цикл статей о SQLite: habrahabr.ru/post/149356/
И видим, что для многопоточного режима необходимо включать WAL-журнал. Добавим в инициализацию БД строчку (после открытия):
$db->exec('PRAGMA journal_mode=WAL;');
И сразу же прогоним тест:
SSD: 2000 транзакций в секунду. Показатель вырос более чем в 5 раз!
HDD: 42 операции. Рост в 3 раза.
Сервер: 1000 операций. Рост почти в 5 раз.
Очень неожиданные результаты. Простое изменение режима журнала дает рост в 5 раз.
Следующим этапом перепишем скрипт на многопоточность.
Многопоточность будем достигать через вызов PHP-скрипта несколько раз в нескольких потоках (ближе к реальной работе сайтов). Для этого воспользуемся утилитой AB (Apache Benchmark).
ab -n 1000 -c 10 "http://localhost:81/test/benc_sqlite.php"
Получившийся текст скрипта:
<?php
function openDB()
{
// Определяем полное имя БД
$file = "_TEMP.db";
// Открываем БД (или создаем)
$db = new SQLite3($file);
// Оптимизация БД
$db->busyTimeout(5000);
$db->exec('PRAGMA journal_mode=WAL;');
return $db;
}
// Инициализация БД
function initDB()
{
$db = openDB();
// Создаем таблицу
$db->exec('CREATE TABLE foo (i INT, i2 INT)');
return $db;
}
// Операция тестирования БД
function testDB($db, $count)
{
return testIteration($db, mt_rand(0, 1000));
}
// 1 итерация тестирования
function testIteration($db, $iteration)
{
return $db->exec('INSERT INTO foo VALUES ('.$iteration.', 1)');
}
// ---------- ИНИЦИАЛИЗАЦИЯ И ЗАПУСК ТЕСТА ----------
// Иницализируем БД
if(isset($_REQUEST['init']))
$db = initDB();
else
$db = openDB();
// Запускаем тест
if(testDB($db, 1))
echo 'OK';
else
echo 'FAIL';
?>
Из скрипта ушел замер скорости вставки, это нам даст AB. Теперь инициализация проводится только 1 раз, т.к. идет работа с одной БД.
Дополнительно, установил
$db->busyTimeout(5000);
Это необходимо для того, чтобы при попытке многопоточной записи, процесс ждал некоторое время (я задал 5 секунд) своей очереди, а не сразу падал с тем, что “SQLITE_BUSY”.
Результаты:
SSD: 970 операций в секунду. Более чем достаточно.
HDD: 35 операций.
Сервер: 90 операций при тесте с локальной машины и 210 при запуске AB с сервера. Очень большая разница с запуском на SSD. Судя по всему, она обусловлена различиями в настройке систем (на сервере Apache урезан до возможного минимума).
Дополнительно следует отметить, что локальные показатели очень сильно плавают, сказывается облачность сервера. От меня до сервера почти полторы тысячи километров.
В любом случае, даже если брать минимум, 90 операций в секунду, то этого более чем достаточно, на мой взгляд. К примеру, Хабр имеет в среднем 30 просмотров в секунду (конечно, сильные пики днем, но и до Хабра расти и расти).
В дальнейшем показатели для SSD и HDD берутся при локальном тесте. А для сервера — с удаленной машины.
Тестирование сервера и SSD происходит путем запуска 1000 запросов в 10 потоках. Для HDD — 100 запросов в 10 потоков.
Усложнение
Напомню, в тесте был 1 простой INSERT запрос. В принципе, хорошо иметь сервис, выполняющий 1 операцию на базе. Но это очень далеко от реальности. Усложним до трех запросов (1 SELECT, 1 UPDATE и 1 INSERT).
Итоговая функция testIteration:
function testIteration($db, $iteration)
{
$rez = $db->querySingle('SELECT count(*) FROM foo WHERE i='.$iteration);
if($rez > 0)
{
$db->exec('UPDATE foo SET i2 = i2+1 where i='.$iteration);
}
return $db->exec('INSERT INTO foo VALUES ('.$iteration.', 1)');
}
Понятно, что можно обойтись и без SELECT-запроса, но тем не менее, у нас тест, а не соревнования по оптимизации.
Показатели:
SSD: от 420 запросов. От запуска к запуску показатель падает.
HDD: от 20 запросов (и падает).
Сервер: от 65 запросов (и падает).
Оптимизация БД
Падение производительности мне не понравилось. Ну что же, попробуем исправить данную проблему. А конкретно — добавим индексы. Поиск у нас происходит всего по одному полю, так что его и добавим в индекс.
В инициализацию добавляем строчку
$db->exec('CREATE INDEX "idx_foo" ON "foo" ("i");');
Результаты:
SSD: 671 запрос. Очень неплохо.
HDD: от 25 до 12 запросов. Не знаю, в чем проблема, но результат не показателен. Возможно, связано с тем, что сервер и SSD я тестирую 1000 запросов в 10 потоках, а HDD — 100 запросов в 10 потоках. Там меньше данных на порядок. Но на такой разброс не должно было влиять.
Сервер: 60 запросов.
Ну что же, результаты меня вполне радуют. Вот только у нас уже 2 операции изменения базы, где моя транзакция? Добавим ее:
function testIteration($db, $iteration)
{
$rez = $db->querySingle('SELECT count(*) FROM foo WHERE i='.$iteration);
$db->exec('BEGIN;');
if($rez > 0)
{
$db->exec('UPDATE foo SET i2 = i2+1 where i='.$iteration);
}
$rez = $db->exec('INSERT INTO foo VALUES ('.$iteration.', 1)');
$db->exec('COMMIT;');
return $rez;
}
И опять результаты:
SSD: от 720 запросов.
HDD: от 30 запросов. Причем на этот раз показатель достаточно стабилен.
Сервер: от 70 запросов.
Оптимизация отказов БД
Apache Benchmark помимо выполнения запросов, следит и за успешностью запросов. Берется длина ответа и все ответы, длиннее или короче исходного, считаются ошибками. Интересно, что при тестировании уже давно стали выходить подобные проблемы. Пускай всего с десяток на 1000 запросов, но тем не менее.
В логах сообщение “SQLite3::exec(): database is locked”. Странно, WAL-режим, транзакции, и все равно “database is locked”. Выход нашелся в рассылке SQLIite: http://www.mail-archive.com/sqlite-users@sqlite.org/msg05525.html
В мультипоточном режиме необходимо использовать не “BEGIN”, а “BEGIN IMMEDIATE” или “BEGIN EXCLUSIVE”. Специальные транзакции для мультипоточной работы. Режим “BEGIN EXCLUSIVE” еще иногда давал мне эту проблему, а вот режим “BEGIN IMMEDIATE” ее решил полностью.
А теперь итоговые тесты:
SSD: от 700 запросов.
HDD: от 30 запросов. Причем показатель достаточно стабилен.
Сервер: от 53 запроса. Странно, ожидал около 70. Но это облако, о стабильности говорить не приходится.
Кстати, показатель количества запросов немного падает со временем. На чистой базе SSD выдает 1000 запросов. На “грязной” — до 350. При этом в базе скопилось около 50000 записей.
Стоит добавить, что если однопоточный тест усложнить, как мы усложнили многопоточный (1 SELECT, 1 UPDATE и 1 INSERT запрос + транзакция + индексы), то на SSD выдается результат в 1500 итераций в секунду. Есть, куда стремиться. При этом с ростом БД производительность падает примерно в 2 раза медленнее, чем для мультипоточного режима.
В принципе, тест показал достаточно хорошие результаты. Пусть он местами и противоречив, спорен и недостаточно научен. Но тем не менее, лично для меня, оценку работоспособности связки SQLite и PHP вполне показал.
Сравнение с другими СУБД
SQLite мы посмотрели. Но как ведут себя в данных условиях другие СУБД?
Данный тест будет происходить только на локальной машине и только на SSD. Причина — на локальной машине стоит больше СУБД, чем на сервере, и данный режим ближе к работе моего сервера.
Под рукой оказались: SQLite (v3.7.7.1), PostgreSQL (v9.2.1), MS SQL Server (v11.0.3000.0 (2012, Developer Edition)), MySQL (v5.5.28), MongoDB (v2.4.4).
В PHP использовался стандартный драйвер для SQLite и MongoDB. PostgreSQL, MySQL и MS SQL работали через PDO.
Дополнительно, решил немного изменить тест: выполняются 10000 запросов в 10 потоках. Для чистоты эксперимента база каждый раз была пустой.
Инициализация БД происходит в ручном режиме. К инициализации относится: создание таблицы и индекса по полю.
<?php
function openDB()
{
// Определяем полное имя БД
$file = "_TEMP.db";
// Открываем БД (или создаем)
$db = new SQLite3($file);
// Оптимизация БД
$db->busyTimeout(5000);
$db->exec('PRAGMA journal_mode=WAL;');
return $db;
}
// Инициализация БД
function initDB()
{
$db = openDB();
// Создаем таблицу
$db->exec('CREATE TABLE foo (i INT, i2 INT)');
$db->exec('CREATE INDEX "idx_foo" ON "foo" ("i");');
return $db;
}
// Операция тестирования БД
function testDB($db, $count)
{
return testIteration($db, mt_rand(0, 1000));
}
// 1 итерация тестирования
function testIteration($db, $iteration)
{
$rez = $db->querySingle('SELECT count(*) FROM foo WHERE i='.$iteration);
$db->exec('BEGIN IMMEDIATE;');
if($rez > 0)
{
$db->exec('UPDATE foo SET i2 = i2+1 where i='.$iteration);
}
$rez = $db->exec('INSERT INTO foo VALUES ('.$iteration.', 1)');
$db->exec('COMMIT;');
return $rez;
}
// ---------- ИНИЦИАЛИЗАЦИЯ И ЗАПУСК ТЕСТА ----------
// Иницализируем БД
if(isset($_REQUEST['init']))
$db = initDB();
else
$db = openDB();
if(testDB($db, 1))
echo 'OK';
else
echo 'FAIL';
?>
<?php
function openDB()
{
// MySQL
$db = new PDO('mysql:host=127.0.0.1;dbname=test', 'root', '');
// PosgreSQL
//$db = new PDO("pgsql:host=127.0.0.1;dbname=test", "postgres", "" );
// MS SQL
//$db = new PDO("sqlsrv:Database=test;Server=MAKC2SQL2012", "sa", "" );
return $db;
}
// Операция тестирования БД
function testDB($db, $count)
{
return testIteration($db, mt_rand(0, 1000));
}
// 1 итерация тестирования
function testIteration($db, $iteration)
{
$rez = $db->query('SELECT count(*) FROM foo WHERE i='.$iteration);
$rez2 = $rez->fetchAll();
$rez2 = $rez2[0][0];
$rez->closeCursor();
if(!$db->beginTransaction())
return false;
if($rez2 > 0)
{
$db->exec('UPDATE foo SET i2 = i2+1 where i='.$iteration);
}
$rez = $db->exec('INSERT INTO foo VALUES ('.$iteration.', 1)');
$db->commit();
return $rez;
}
// ---------- ИНИЦИАЛИЗАЦИЯ И ЗАПУСК ТЕСТА ----------
// Иницализируем БД
$db = openDB();
if(testDB($db, 1))
echo 'OK';
else
echo 'FAIL';
?>
<?php
function openDB()
{
$conn = new Mongo('localhost');
$db = $conn->test;
return $db;
}
// Операция тестирования БД
function testDB($db, $count)
{
return testIteration($db, mt_rand(0, 1000));
}
// 1 итерация тестирования
function testIteration($db, $iteration)
{
$foo = $db->foo;
$q = array('i' => $iteration);
$ch = $foo->find($q);
if($ch->count() > 0)
{
$foo->update(array('i' => $iteration), array('$inc' => array("i2" => 1)));
}
$rez = $foo->insert(array('i' => $iteration, 'i2' => 1));
return $rez;
}
// ---------- ИНИЦИАЛИЗАЦИЯ И ЗАПУСК ТЕСТА ----------
// Иницализируем БД
$db = openDB();
if(testDB($db, 1))
echo 'OK';
else
echo 'FAIL';
?>
Результаты:
SQLite: 993 запроса в секунду. Вполне ожидаемый результат.
PostgreSQL: 178 запросов в секунду. Довольно мало. Судя по всему, проблема в настройке по умолчанию.
MS SQL: 1400 запросов в секунду. Неплохо, очень неплохо.
MySQL: 1490 запросов в секунду. Вот тебе и дефолтные настройки :). Стоит отметить, что изначально было 9 запросов в секунду. Помогла замена обращения к localhost на 127.0.0.1. Насколько знаю, связано с активным IPv6 в системе.
MongoDB: 3173 запросов в секунду. Замечательный результат. Хотя от NoSQL я ожидал большего.
Прямо скажем, сравнение не самое честное. Тест происходил на дефолтных настройках. Я не обладаю необходимыми навыками администрирования, чтобы выровнять настройки СУБД.
MongoDB, в первую очередь, ориентирована на скорость. SQLIte ориентирован на надежность. А PostgeSQL в дефолтной настройке ориентирован на минимум потребления ресурсов. Возможно, в этом и причина провала.
Результаты достаточно интересные. Настолько, что я решил сравнить производительность разных СУБД на сервере. Напомню, что используется Debian 6. На сервере стоят: SQLite (v3.7.3), PostgreSQL (v9.0.4), MySQL (v5.1.66). Все в дефолтной настройке. AB запускался локально, на сервере. 10000 запросов в 10 потоках.
Результаты:
SQLite: 174 запроса в секунду.
PostgreSQL: 104 запроса в секунду.
MySQL: 167 запросов в секунду.
Как видим, такого большого разрыва, как для локальной машины, в показателях нет. Но не стоит забывать, что на сервере используется SSD (пусть и не в “чистом” виде, а как элемент облачной СХД).
Итог
Как итог можно сказать, что связка PHP и SQLite вполне работоспособна для невысоко нагруженных проектов. Достаточно придерживаться некоторых простых правил:
- Используем WAL-режим журналирования.
- Не забываем устанавливать Busy Timeout.
- Запись объединяем в транзакции. Причем не простые, а “BEGIN IMMEDIATE”.
Если же проект отнести к “небольшим” нельзя, то использование SSD для базы вполне может помочь и здесь.
Если же говорить о Hi-Load, то ребята, этим занимающиеся, знают что им делать и без моих советов.
Если же сравнивать SQLite с другими СУБД, то они обладают сравнимой производительностью на настройках по умолчанию. Во всяком случае при использовании SSD диска.
Вместе с тем, простота настройки и администрирования (ничего не надо настраивать и администрировать), простота переноса на другой сервер (простым копи-пастом) дают некое преимущество для SQLite. В первую очередь для разработчиков. Для работы SQLite с PHP ничего, кроме самого PHP и не надо.
Автор: SabMakc
