GrAndSE's blog

Порівняння швидкодії БД (PostgreSQL, MySQL, SQLite)

Думаю, найбільш популярною на сьогодняшній день серед розробників є БД MySQL. Досить простий у використанні, розповсюджений серед хостерів, більшість CMS орієнтовані саме на нього, а починаючи з 5ої версії в цій системі з'явилось багато можливостей, притаманних корпоративним БД. І список можливостей продовжує рости. Розробники та прихильники MySQL досить часто стверджують, що він дуже швидкий, гнучкий та здатний до масштабування.

Однак, не дивлячись на те з MySQL все так добре та чудово, самеPostgreSQL використовується для збереження данних в більшості великих соціальних мереж, саме нею скористалось Yahoo! для побудови найбільшої в світі БД. Цікаво, що прихильники PostgreSQL з гордістю кажуть, що їх улюбленець значно швидший за MySQL, ну а його прихильники в свою чергу доволі часто кажуть протилежне.

А тут ще SQLite, що займає доволі потужні позиції в desktop-орієнтованому софті, а ще дехто каже про чудові можливості Firebird. І це тільки найпопулярніші з opensource баз данних.

Втомившись читати купу різних порівнянь, вирішив провести для себе самого ряд простих тестів та нашвидкоруч оцінити як поводять себе деякі БД

"Методика" тестування

Мій спосіб перевірки швидкодії не претендує ні на звання "серйозної методики". Мене просто цікавило, скільки запитів того чи іншого виляду витримає та чи інша БД. Причому для тесту було обрано найпримітивніші запити на запис та читання. Це синтетичний тест, погляд лише з одного боку. Однак чим є БД за своєю суттю? Інструментом для збереження данних. А які операції є основними? Читання та запис. Тому не дивлячись на свою синтетичність, леяку інформацію про БД він надає.

Оскільки в мене завжди на робочому місті встановлено MySQL та PostgreSQL, а для роботи з SQLite практично ніякого додаткого інструментарію непотрібно, то для тесту було обрано саме ці БД. Причому ніякої додаткової конфігурації ні для MySQL ні для PostgreSQL не проводилось. Хоча б тому що я не вважаю себе знавцем тонкого налаштування серверів БД і мої маніпуляції могли б зробити тільки гірше.

Для уніфікації тестів для кожної з БД я написав ось такий абстрактний клас, що є прототипом для тесту БД:

/**
* Abstract class for test running
*/
abstract class AbstractDatabaseTest {
protected $connection;
/**
* Creates test suite, connect to database and create table
*/
public function __construct($host, $login, $password, $database) {
$this->connect($host, $login, $password, $database);
$this->createTable();
}
/**
* Close drop table
*/
public function __destruct() {
$this->query('DROP TABLE test');
$this->disconnect();
}
/**
* Abstract function for query executing
*/
public abstract function query($query);
/**
* Abstract function fot table creation
*/
public abstract function createTable();
/**
* Abstract method for creating database connection
*/
public abstract function connect($host, $login, $password, $database);
/**
* Abstract method for closing connection
*/
public abstract function disconnect();
/**
* Method for running test
*/
public function runTest($query, $times) {
for ($i = 0; $i < $times; $i++)
$this->query(preg_replace("/[\?]/", $i, $query));
}
/**
* Create single query for all requests
*/
public function runSingleTest($query, $times, $exec = true) {
$q = '';
for ($i = 0; $i < $times; $i++)
$q .= preg_replace("/[\?]/", $i, $query).";\n";
if ($exec)
$this->query($q, $i);
else
return $q;
}
/**
* Run test as transaction
*/
public function runTransactionTest($query, $times) {
$q = 'BEGIN;'."\n";
$q .= $this->runSingleTest($query, $times, false);
$q .= 'COMMIT;';
}
}


Цей класс декларує конструктор та деструктор, що в свою чергу звертаються до абстрактний функцій connect() та disconnect(), які мають бути перевантажені в нащадках з метою отримання підключення до серверу БД та його безпечного закриття. Також предокларовані методи для виконання тестів (runTest(), runSingleTest(), tunTransactionTest()), які будуть звертатись до низькорівневих функцій роботи з підключенням через абстрактний метод query(). Ну і залишається ще одна функція, що створює тестову таблицю.

Тоді для тесту наприклад PostgreSQL достатньо описати такий клас:

/**
* Class for working with PostgreSQL database
*/
class PostgreSQLTest extends AbstractDatabaseTest {
/**
* Creates database object
*/
public function connect($host, $login, $password, $database) {
$this->connection = pg_connect('host='.$host.' dbname='.$database.' user='.$login.' password='.$password)
or die('Could not connect to database: '.pg_last_error($this->connection));
}
/**
* Close connection
*/
public function disconnect() {
pg_close($this->connection);
}
/**
* Create database table
*/
public function createTable() {
pg_query($this->connection, 'CREATE TABLE test
(
id serial,
"name" character varying(255),
o integer,
CONSTRAINT id_k PRIMARY KEY (id)
)');
}
/**
* Execute query
* @param query query for execution
*/
public function query($query) {
pg_query($this->connection, $query)
or die('Could not execute query: '.pg_last_error($this->connection)."\n".$query."\n");
}
}


Як можна побачити, для тестування створюється таблиця, що містить три поля: id (ключ), name (текстове поле), o (цілочислене поле). Таким чином можна протестувати вибірки за різним типом порівнянь. Аналогічні класи я створив для SQLite та два для MySQL (InnoDB та MyISAM).

Тепер сам тест:

</?php
**
>runSingleTest($query, $times);
} elseif ($testType == 'trans') {
$msg = ' in transaction';
$test->runTransactionTest($query, $times);
} else {
$test->runTest($query, $times);
}
$end = microtime(true);
$summary += ($end-$begin);
}
// Put time
$summary = $summary/$iters; // Try to get average time
echo $type, ' (', $times, ' times'.$msg.') takes ', "\t\t", $summary, " sec.\n";
return array($type => $summary);
}
/**
* Static method for test running
*/
public static function run($className, $host, $user, $pass, $database, $count = 1000) {
$results = array();
// Usual test
$test = new $className($host, $user, $pass, $database);
$results[] = self::singleTest($test, 'INSERT INTO test (name, o) VALUES (\'record?\', ?)', $count, 'INSERT');
$results[] = self::singleTest($test, 'SELECT * FROM test WHERE id = ?', $count, 'SELECT ID');
$results[] = self::singleTest($test, 'SELECT * FROM test WHERE o = ?', $count, 'SELECT INT');
$results[] = self::singleTest($test, 'SELECT * FROM test WHERE name = \'record?\'', $count, 'SELECT TEXT');
$results[] = self::singleTest($test, 'SELECT * FROM test WHERE name like \'%?\'', $count, 'SELECT LIKE');
$results[] = self::singleTest($test, 'DELETE FROM test WHERE id = ?', $count, 'DELETE');
unset($test);
// Prepare for output
$prepared = array();
foreach ($results as $res)
foreach ($res as $key => $value)
$prepared[$key] = $value;
return $prepared;
}
}
< * Class for running tests
*/
class Test {
/**
* Test for running multiply-query, signle-query and transtactional test on demand
* @param test test connection
* @param query query fo execution
* @param times how many requests will be transmitted
* @param type additional message about request type
*/
public static function singleTest(AbstractDatabaseTest $test, $query, $times, $type, $testType = 'usual') {
// Select iterations count
/* TODO: uncomment it later
if ($times = 1000)
$iters = 50;
elseif ($items = 5000)
$iters = 10;
else */
$iters = 1;
// Evaluate time
$summary = 0;
for ($i = 0; $i $iters; $i++) {
$begin = microtime(true);
$msg = '';
if ($testType == 'single') {
$msg = ' in single query';
$test-/code>
Як видно з коду спочатку виконуються вставки в БД, потім вибірки за ключем, цілочисельним полем, повним порівнянням текстового поля та шаблонним порівнянням через <strong>like</strong>.
Для збірки всього до купи, читання конфігураційного файлу та збереження результатів у вигляді html було написано ось такий код:
<code class="code-block">
// Get configuration file
$config = (isset($args[1]))? $args[1]: 'config.conf';
if (!file_exists($config))
die('Error:'."\t".'file '.$config.' does not exists'."\n");
// Read configuration
$configuration = array();
if (!($file = fopen($config, 'r')))
die('Error:'."\t".'can open config file for reading: '.$config."\n");
while ($parsed = fgetcsv($file, 1000, '=')) {
// Check format
if (count($parsed) != 2) {
var_dump($parsed);
die('Error:'."\t".'wrong configuration file format '."\n");
}
// Parse line
if (preg_match("/,/", $parsed[1]))
$vals = explode(",", $parsed[1]);
else
$vals = array($parsed[1]);
// Add parameters
if (!isset($configuration[$parsed[0]]))
$configuration[$parsed[0]] = array();
foreach ($vals as $val)
$configuration[$parsed[0]][] = $val;
}
fclose($file);
// Load classes
include 'lib/abstract.php';
include 'lib/test.php';
foreach ($configuration['libs'] as $lib) {
include 'lib/'.$lib.'.php';
}
// Output file
$result = (isset($args[2]))? $args[2]: 'results.html';
$output = fopen($result, "w");
// Counts
fwrite($output, '
<html>
<head>
<title>Database test</title>
</head>
<body>
<h1>Database test</h1>
'
);
$types = array('INSERT', 'SELECT ID', 'SELECT INT', 'SELECT TEXT', 'SELECT LIKE', 'DELETE');
foreach ($configuration['count'] as $count) {
fwrite($output, '<h2>'.$count.' items</h2>
<table>
<tr>
<th> </th>
');
$results = array();
foreach ($configuration['database'] as $db) {
fwrite($output, "\t\t\t<th>$db</th>\n");
$results[$db] = Test::run($db, $configuration['host'][0], $configuration['user'][0],
$configuration['pass'][0], $configuration['db'][0], $count);
}
fwrite($output, "\t\t</tr>\n");
foreach ($types as $type) {
fwrite($output, "\t\t<tr>\n\t\t\t<td>$type</td>\n");
foreach ($configuration['database'] as $db)
fwrite($output, "\t\t\t<td>".$results[$db][$type]."</td>\n");
fwrite($output, "\t\t</tr>\n");
}
fwrite($output, "\t</table>\n");
}
fwrite($output, '</body>
</html>');


Настав час погратись.

Результати тестів

Спочатку я провів тестування на старенькій машинці з Amd Sempron 2500+ 1 Gb DDR з файловою системою raiserfs під управлінням Ubuntu 9.04 для 1000, 5000 та 10000 елементів. Отримав такі результати:

1000 items

	SQLiteTest	MySQLTestIsam	MySQLTestInno	PostgreSQLTest
INSERT	8.46631503105	0.182038068771	0.922454833984	1.3453848362
SELECT ID	0.140038013458	0.401347875595	0.375024080276	1.090269804
SELECT INT	0.825850963593	1.01993608475	1.38959908485	1.50171399117
SELECT TEXT	0.847486019135	1.13318300247	1.46317386627	1.63713502884
SELECT LIKE	1.00699400902	1.10880994797	1.43363499641	1.72174596786
DELETE	8.6017370224	0.277512073517	1.48435211182	1.46303582191

5000 items

	SQLiteTest	MySQLTestIsam	MySQLTestInno	PostgreSQLTest
INSERT	48.9740638733	0.897409915924	4.29658889771	6.77726101875
SELECT ID	0.521908998489	1.68041110039	1.66038298607	5.13580799103
SELECT INT	18.8576068878	17.7570590973	26.9309771061	16.0441360474
SELECT TEXT	18.7875778675	19.5907330513	28.7952878475	19.5119550228
SELECT LIKE	23.2714881897	19.961373806	28.3384401798	20.7058980465
DELETE	44.6153440475	1.21550679207	7.36333107948	7.01828098297

10000 items

	SQLiteTest	MySQLTestIsam	MySQLTestInno	PostgreSQLTest
INSERT	96.4258060455	1.78123998642	8.63258886337	13.5855839252
SELECT ID	0.983808994293	3.49806404114	3.36594605446	10.2117769718
SELECT INT	78.3077938557	65.505810976	95.4381818771	53.9780220985
SELECT TEXT	72.8628439903	103.212995052	71.3834159374	69.0468411446
SELECT LIKE	89.72281003	107.325515985	72.6874220371	71.0193040371
DELETE	88.7086949348	2.29328393936	15.0134780407	13.882789135

Цього мені виявилось замало, і я вирішив спробувати провести для більшої кількості елементів. Хоча б ще для 50 та 100 тис. Оскільки тести на такій кількості елементів займають доволі багато часу, то їх я провів на іншій машині: Intel Atom, 1 Gb DDR2, ext3 і тією самою Ubuntu 9.04 з ядром 2.6.30. І отримав ось такі результати:

1000 items

	SQLiteTest	MySQLTestIsam	MySQLTestInno	PostgreSQLTest
INSERT	3.42623877525	0.485679149628	1.01362395287	1.63590288162
SELECT ID	0.519760131836	1.09079289436	0.573807001114	1.17772603035
SELECT INT	2.56960511208	2.94936800003	2.23329114914	1.9480099678
SELECT TEXT	2.33323192596	3.13713407516	2.38640213013	2.154296875
SELECT LIKE	2.18005108833	2.80790996552	2.43208599091	2.29287910461
DELETE	3.46106314659	0.81547498703	0.937149047852	1.56592297554

5000 items

	SQLiteTest	MySQLTestIsam	MySQLTestInno	PostgreSQLTest
INSERT	16.860270977	3.05509400368	4.44511294365	8.22729110718
SELECT ID	1.90315794945	3.12027382851	2.6133749485	6.00253605843
SELECT INT	35.5371229649	30.0901350975	44.3228580952	25.8845059872
SELECT TEXT	32.2936480045	33.9316368103	48.9920039177	29.3765230179
SELECT LIKE	41.3553678989	35.6514999866	49.3500881195	33.2532720566
DELETE	17.458812952	2.04428100586	5.16926288605	8.14243984222

10000 items

	SQLiteTest	MySQLTestIsam	MySQLTestInno	PostgreSQLTest
INSERT	35.5141940117	5.62814283371	8.56925797462	16.4258570671
SELECT ID	3.68663096428	7.88118100166	5.21180105209	11.7483720779
SELECT INT	130.809201956	116.798990965	169.440524817	90.0897068977
SELECT TEXT	125.267574072	128.374459982	188.359474897	105.278270006
SELECT LIKE	160.688622952	135.743841887	188.13747716	117.56459713
DELETE	34.9218218327	3.94423508644	10.6728608608	16.3871839046

50000 items

	SQLiteTest	MySQLTestIsam	MySQLTestInno	PostgreSQLTest
INSERT	186.580451012	27.5768818855	42.6071000099	78.3421108723
SELECT ID	17.9766161442	38.9252171516	25.6919820309	55.8121678829
SELECT INT	3373.11729312	2714.17641687	4097.4976809	1971.63789201
SELECT TEXT	3319.83222294	3077.92565894	4539.87472916	2321.9553771
SELECT LIKE	4336.35408092	3266.41011596	4621.69911408	2826.56725407
DELETE	167.727796078	19.2260329723	47.8846509457	79.9886329174

100000 items

	SQLiteTest	MySQLTestIsam	MySQLTestInno	PostgreSQLTest
INSERT	380.183732986	57.4253020287	87.9900531769	154.548943996
SELECT ID	37.0985400677	65.9570541382	54.8159809113	114.572535038
SELECT INT	13562.0152919	11314.5157359	19920.4457819	8018.85069585
SELECT TEXT	15053.0083721	12835.214098	21606.1742871	9508.44170213
SELECT LIKE	19123.6498909	13622.2726769	18889.7097459	11153.777102
DELETE	353.978772879	40.8035359383	96.7261219025	192.977801085

Ну і хто ж кращий?

Перше, на чому зверну увагу: SQLite дуже швидко вибирає елементи за первинним ключем. Причому навіть при великій кількості елементів в БД. Також при обсязі записів до 5 тис. елементів його швидкодія для простих вибірок йде на рівні або навіть випереджає інши БД. Однак швидкість запису та видалення елементів є для нього не простою задачею, внаслідок того, що вся БД знаходиться в одному єдиному файлі. Однак враження від цієї БД в мене залишилось дуже гарне і я при необхідності зберігати дані в програмах, що мають працювати локально, з великим задоволенням скористаюсь цією БД. Дарма я не звернува уваги на цю БД раніше, колись би зберіг обі дуже багато часу.

Тепер про MySQL та PostgreSQL. Другий виявився повільнішим на вставках та видаленні і що не дуже приємно при виборі за первинним ключем. Повільні вставки мене не лякають, а от швидкість виборок за ключем мене дійсно не радує.

Що означає на практиці повільніша швидкість вставок? Приведу приклад в рамках абстрактного сайту: один перегляд сторінки вимагає вибрати список коментарів, список статей, список тегів, якісь налаштування, посилання, коментарі. Наприклад, CMS Joomla 1.5 виконує десь 13 запитів при перегляді головнох сторінки (жахливо, чи не так). Нехай, відвідуваність у проекту буде така як і у мого блога, тобто хочаб 10 уніків щодня, які роблять хоча б 20 переглядів. Тобто близько 250-260 запитів на вибірку до БД. А скільки коментарів залишать ці читачі? Добре, якщо 10% читачів будуть окментувати. Тобто за день середньостатистично буде 1 запит на вставку коментаря, ну і якби я писав кожного дня то зі збереженнями тексту в процесі написання вийшло б десь 10 запитів на запис. Тобто в такому ідеальному варіанті дуже активного автора та користувачів добре якщо кількість записів у бд досягне 5% від загального числа запитів. Коли запис йде швидко, то це добре, однак значно критичніше для реальних задач швидко вибирати дані.

І тут PostgreSQL тримається досить добре: на 1000 елементів він йде поряд з MySQL InndoDB, на 5000 наздоганяє MySQL MyIsam, ну а на 10 тис. починає показувати свою високу здатність витримувати навантаження. Швикдість виконання запитів вища ща відповідну у двигунця MyISAM відсотків на 20, не кажучи про InnoDB, який повільно плентається інколи відстаючи в два рази. Хочу хвернути увагу на також на те, що робота з різними типами данних відбувається в PostgreSQL по різному. За цілими числами вибірка відбувається швидше (за різницею швидкодії між MySQL та PostgreSQL).

В чому різниця між MyISAM та InnoDB. У другого можливості ширші. Тільки він дозволяє вводити в БД додаткову логіку, таку як вторинні ключі, що є на мою думку важливим для побудови серйозних проектів. Фактично InnoDB і мав би бути конкурентом для PostgreSQL. Тобто за результатами тесту MySQL в якості корпоративної БД програє за всіма параметрами PostgreSQL: функціонал обмежений, швидкість роботи нижча.

Що ж обрати?

Думали буду кричати: "Обирайте PostgreSQL"? Не буду. Оскільки вибір цієї БД є виправданим коли кількість записів в БД сягає хоча б 10 тис на таблицю. Для якогось персонального сайту, сайту-візитки, простого не навантаженого проекту цілком вистачить MySQL, оскільки там кількість записів у БД на таблицю навряд сягне хоча б однієї тисячі записів. Та й для багатьох навантажених проетів важливими є ще багато факторів, окрім швидкодії БД (наприклад, як саме було спроектовано БД, як написано код, чи кешуються якось дані). Значно важливішим при виборі PostgreSQL є необхідність включати якусь логіку чи складні типи збереження данних на рівень БД. Тут і за швидкодією і за можливостями кращим вибором буде саме PostgreSQL.

Головне при виборі БД керуватись здоровим глуздом. Ще хочу додати два посилання на інші тести, що проводили порівняння PostgreSQL та MySQL. Першим буде порівняння навантаження що витримують MySQL та PostgreSQL в рамках блогохостингу, в якому досить детально описується і методика тестування і пояснюються результати. Ну а тест за кількістю конкуруючих запитів від Tweacers.net є досить відомим, і хоча й досить застарілим, однак добре показує як справляється з високими навантаженнями різни БД.

Дякую за увагу.