Pull to refresh

Тюнинг SQLite для PHP

Reading time 11 min
Views 29K

В данной статье оценивается применение SQLite для PHP-проектов, рассматриваются особенности многопоточной работы и способы ускорения работы. «На десерт» — небольшое сравнение с другими СУБД (MySQL, PosgreSQL, MS SQL, MongoDB).

Начало


Не так давно мне захотелось сделать свой сервис промо-кодов для одного своего проекта. В выборе платформы я не долго сомневался. Раз у меня есть сервер с развернутым 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”. Выход нашелся в рассылке SQLite: 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 относительно многопоточного.

В принципе, тест показал достаточно хорошие результаты. Пусть он местами и противоречив, спорен и недостаточно научен. Но тем не менее, лично для меня, оценку работоспособности связки 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 потоках. Для чистоты эксперимента база каждый раз была пустой.
Инициализация БД происходит в ручном режиме. К инициализации относится: создание таблицы и индекса по полю.
Итоговый скрипт для SQLite
<?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';
?>

Скрипт для PDO (меняется строка подключения для PostgreSQL, MySQL и MS SQL)
<?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=127.0.0.1\SQL2012", "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';
?>

Скрипт для MongoDB
<?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 202 запроса в секунду. Довольно мало. Судя по всему, проблема в настройке по умолчанию.
MS SQL: 1400 запросов в секунду. Неплохо, очень неплохо.
MySQL: 1490 запросов в секунду. Вот тебе и дефолтные настройки :). Стоит отметить, что изначально было 9 запросов в секунду. Помогла замена обращения к localhost на 127.0.0.1. Насколько знаю, связано с активным IPv6 в системе.
MongoDB: 3173 запросов в секунду. Замечательный результат. Хотя от NoSQL я ожидал большего.

Прямо скажем, сравнение не самое честное. Тест происходил на дефолтных настройках. Я не обладаю необходимыми навыками администрирования, чтобы выровнять настройки СУБД.
MongoDB, в первую очередь, ориентирована на скорость. SQLIte ориентирован на надежность. А PostgeSQL в дефолтной настройке ориентирован на минимум потребления ресурсов. Возможно, в этом и причина провала.

UPD: Мне тут по «большому секрету» подсказали, что PostgreSQL не любит переподключения. Поэтому я провел однопоточные тесты для каждой СУБД.
Тест замеряет время работы в однопточном режиме. 10000 операций, чистая БД. Операция вида 1 SELECT, 1 UPDATE и 1 INSERT в транзакции. Замер скорости в скрипте (как в 1м тесте), без времени подключения к БД.
Результаты следующие:
PostgreSQL: 1403. Рост в 7 раз. Что подтверждает нелюбовь PostgreSQL к переподключениям.
PostgreSQL (fsync = off): 1766. Рост на четверть относительно дефолтного режима. В многопоточном режиме прирост не проявился.
SQLite: 938. Странно, раньше 1500 было. Наверное, в тесте была ошибка.
MS SQL: 1993. Рост есть, но незначительный. Где-то на 40% (рост в 1,4 раза).
MySQL: 2514. Рост есть, в 1,7 раза.
MongoDB: 7603. Рост почти в 2,5 раза.


Результаты достаточно интересные. Настолько, что я решил сравнить производительность разных СУБД на сервере. Напомню, что используется 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 вполне работоспособна для невысоко нагруженных проектов. Достаточно придерживаться некоторых простых правил:
  1. Используем WAL-режим журналирования.
  2. Не забываем устанавливать Busy Timeout.
  3. Запись объединяем в транзакции. Причем не простые, а “BEGIN IMMEDIATE”.

Если же проект отнести к “небольшим” нельзя, то использование SSD для базы вполне может помочь и здесь.

Если же говорить о Hi-Load, то ребята, этим занимающиеся, знают что им делать и без моих советов.

Если же сравнивать SQLite с другими СУБД, то они обладают сравнимой производительностью на настройках по умолчанию. Во всяком случае при использовании SSD диска.
Вместе с тем, простота настройки и администрирования (ничего не надо настраивать и администрировать), простота переноса на другой сервер (простым копи-пастом) дают некое преимущество для SQLite. В первую очередь для разработчиков. Для работы SQLite с PHP ничего, кроме самого PHP и не надо.
Tags:
Hubs:
+17
Comments 14
Comments Comments 14

Articles