Тестирование SQLite 3.6.17-mobigroup.2 на больших таблицах

Цель данного тестирования - проверить работу в SQLite тестов, написанных при тестировании PostgreSQL и убедиться, какая из СУБД более подходит для работы с большими БД.

Параметры сервера/Server parameters:

Intel(R) Core(TM)2 Quad CPU Q6700 @ 2.66GHz
MemTotal: 8310900 kB


Параметры БД SQLite/SQLite parameters:

sqlite> pragma page_size;
4096
sqlite> pragma cache_size;
400000

Размер кэша выбран равный использованному при тестировании PostgreSQL (в рассматриваемых СУБД используются разные размеры страниц, поэтому кэш в обоих случаях 1,6 Gb, хотя абсолютные значения параметров отличаются). Функция intrange2table, используемая для создания тестового распределения, предоставляется одним из расширений, доступных в deb-пакете, а также в исходных текстах.

The SQLite cache size is equal to PostgreSQL cache size. The PostgreSQL databases are using 8 kB page size by default. SQlite cache size is measured in pages (4kB in my configuration) but not on kilobytes. The intrange2table() function for SQLite may be downloaded by links above.

Размеры тестовых БД/Test DB sizes

ls -lah ~|grep test|grep db
-rw-r--r-- 1 veter veter 73M 2009-09-12 14:35 test6.db
-rw-r--r-- 1 veter veter 759M 2009-09-12 14:46 test7.db
-rw-r--r-- 1 veter veter 7.8G 2009-09-12 15:10 test8.db


Тест базы 1 М записей./1 M records

sqlite3 ~/test6.db

create temp table tmp_facts(rowid int);
select intrange2table (1,1000000,1,'tmp_facts');
create table facts(a1 int,a2 int,a3 int,a4 int);

insert into facts
select
cast(abs(random())/9223372036854775807.*10 as int),
cast(abs(random())/9223372036854775807.*10 as int),
cast(abs(random())/9223372036854775807.*10 as int),
cast(abs(random())/9223372036854775807.*10 as int)
from tmp_facts;

create index facts_a1_idx on facts(a1);
create index facts_a2_idx on facts(a2);
create index facts_a3_idx on facts(a3);
create index facts_a4_idx on facts(a4);

select count(*) from facts where a1=2 and a2=2 and a3=2 and a4=2;
92
CPU Time: user 0.152010 sys 0.000000

explain query plan select count(*) from facts where a1=2 and a2=2 and a3=2 and a4=2;
0|0|TABLE facts WITH INDEX facts_a4_idx

С композитным индексом/With composite index:

create index facts_complex_idx on facts(a1,a2,a3,a4);

select count(*) from facts where a1=2 and a2=2 and a3=2 and a4=2;
92
CPU Time: user 0.000000 sys 0.000000

explain query plan select count(*) from facts where a1=2 and a2=2 and a3=2 and a4=2;
0|0|TABLE facts WITH INDEX facts_complex_idx

select count(*)
from facts
join (select 2 as a1) as x1 on x1.a1=facts.a1
join (select 2 as a2) as x2 on x2.a2=facts.a2
join (select 2 as a3) as x3 on x3.a3=facts.a3
join (select 2 as a4) as x4 on x4.a4=facts.a4;
92
CPU Time: user 0.000000 sys 0.000000

explain query plan
select count(*)
from facts
join (select 2 as a1) as x1 on x1.a1=facts.a1
join (select 2 as a2) as x2 on x2.a2=facts.a2
join (select 2 as a3) as x3 on x3.a3=facts.a3
join (select 2 as a4) as x4 on x4.a4=facts.a4;
0|1|TABLE AS x1
1|2|TABLE AS x2
2|3|TABLE AS x3
3|4|TABLE AS x4
4|0|TABLE facts WITH INDEX facts_complex_idx


Тест базы 10 М записей./10 M records

sqlite3 ~/test7.db

create temp table tmp_facts(rowid int);
select intrange2table (1,10000000,1,'tmp_facts');
create table facts(a1 int,a2 int,a3 int,a4 int);

insert into facts
select
cast(abs(random())/9223372036854775807.*18 as int),
cast(abs(random())/9223372036854775807.*18 as int),
cast(abs(random())/9223372036854775807.*18 as int),
cast(abs(random())/9223372036854775807.*18 as int)
from tmp_facts;

create index facts_a1_idx on facts(a1);
create index facts_a2_idx on facts(a2);
create index facts_a3_idx on facts(a3);
create index facts_a4_idx on facts(a4);

select count(*) from facts where a1=2 and a2=2 and a3=2 and a4=2;
97
CPU Time: user 0.924057 sys 0.000000

explain query plan select count(*) from facts where a1=2 and a2=2 and a3=2 and a4=2;
0|0|TABLE facts WITH INDEX facts_a4_idx

С композитным индексом/With composite index:

create index facts_complex_idx on facts(a1,a2,a3,a4);

select count(*) from facts where a1=2 and a2=2 and a3=2 and a4=2;
97
CPU Time: user 0.000000 sys 0.000000

explain query plan select count(*) from facts where a1=2 and a2=2 and a3=2 and a4=2;
0|0|TABLE facts WITH INDEX facts_complex_idx

select count(*)
from facts
join (select 2 as a1) as x1 on x1.a1=facts.a1
join (select 2 as a2) as x2 on x2.a2=facts.a2
join (select 2 as a3) as x3 on x3.a3=facts.a3
join (select 2 as a4) as x4 on x4.a4=facts.a4;
97
CPU Time: user 0.000000 sys 0.000000

explain query plan
select count(*)
from facts
join (select 2 as a1) as x1 on x1.a1=facts.a1
join (select 2 as a2) as x2 on x2.a2=facts.a2
join (select 2 as a3) as x3 on x3.a3=facts.a3
join (select 2 as a4) as x4 on x4.a4=facts.a4;
0|1|TABLE AS x1
1|2|TABLE AS x2
2|3|TABLE AS x3
3|4|TABLE AS x4
4|0|TABLE facts WITH INDEX facts_complex_idx


Тест базы 100 М записей./100 M records

sqlite3 ~/test8.db

create temp table tmp_facts(rowid int);
select intrange2table (1,100000000,1,'tmp_facts');
create table facts(a1 int,a2 int,a3 int,a4 int);

insert into facts
select
cast(abs(random())/9223372036854775807.*33 as int),
cast(abs(random())/9223372036854775807.*33 as int),
cast(abs(random())/9223372036854775807.*33 as int),
cast(abs(random())/9223372036854775807.*33 as int)
from tmp_facts;

create index facts_a1_idx on facts(a1);
create index facts_a2_idx on facts(a2);
create index facts_a3_idx on facts(a3);
create index facts_a4_idx on facts(a4);

select count(*) from facts where a1=2 and a2=2 and a3=2 and a4=2;
95
CPU Time: user 6.244390 sys 0.004000

explain query plan select count(*) from facts where a1=2 and a2=2 and a3=2 and a4=2;
0|0|TABLE facts WITH INDEX facts_a4_idx

С композитным индексом/With composite index:

create index facts_complex_idx on facts(a1,a2,a3,a4);

select count(*) from facts where a1=2 and a2=2 and a3=2 and a4=2;
95
CPU Time: user 0.000000 sys 0.000000

explain query plan select count(*) from facts where a1=2 and a2=2 and a3=2 and a4=2;
0|0|TABLE facts WITH INDEX facts_complex_idx

select count(*)
from facts
join (select 2 as a1) as x1 on x1.a1=facts.a1
join (select 2 as a2) as x2 on x2.a2=facts.a2
join (select 2 as a3) as x3 on x3.a3=facts.a3
join (select 2 as a4) as x4 on x4.a4=facts.a4;
95
CPU Time: user 0.000000 sys 0.000000

explain query plan
select count(*)
from facts
join (select 2 as a1) as x1 on x1.a1=facts.a1
join (select 2 as a2) as x2 on x2.a2=facts.a2
join (select 2 as a3) as x3 on x3.a3=facts.a3
join (select 2 as a4) as x4 on x4.a4=facts.a4;
0|1|TABLE AS x1
1|2|TABLE AS x2
2|3|TABLE AS x3
3|4|TABLE AS x4
4|0|TABLE facts WITH INDEX facts_complex_idx


Обсуждение:
Используется только 1 индекс, это плохо. Но с этим одним индексом выборка из таблицы 1 M работает быстрее, чем в PostgreSQL с 3-мя индексами.

Композитный индекс позволяет сделать время выборки исчезающе малым, независимо от размера таблицы (когда-то гонял тесты на таблицах до 100 Гб, регрессии не заметил). Притом JOIN-ы используют композитный индекс, тогда как PostgreSQL этого не умеет.

Для практических целей достаточно композитного индекса не по всем столбцам. Например, использование в данном тесте индекса по 2-м столбцам позволит на порядок обогнать постгрес.

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

Размер идентичной БД SQLite ровно вдвое меньше, чем в PostgreSQL - база 1 М занимает 73 и 144 Мб соответственно. Кроме того, PostgreSQL хранит дополнительные логи и журналы транзакций, что также требует дополнительного места.

Выборки SQLite меньше зависят от размеров доступного пространства ОЗУ, большой кэш требуется только при модификации больших объемов данных. Таким образом, когда размер БД превышает размер ОЗУ, деградации производительности не происходит.

R-tree индекс не рассматриваем, т.к. сравнение выполнялось только для стандартного b-tree индекса в PostgreSQL и SQLite.

Резюме:

В SQLite не хватает аналога постгресного bitmap index scan, позволяющего слияние одинарных индексов при выборках и сортировках. Выборка по одному индексу (простому или композитному) работает лучше, чем в PostgreSQL.

bitmap index scan зачастую лучше заменять на композитный индекс даже в PostgreSQL, т.к. производительность первого намного уступает второму решению. Будет разумным полагать, что эта техника пригодна для некритичных к скорости выборки БД, когда можно пожертвовать преимуществами композитного индекса взамен простоты разработки и построения запросов - фактически, к этой категории можно отнести значительную часть всех применений БД. Для интересующих автора OLPT-систем с реалтайм отчетностью по актуальным данным и в PostgreSQl и в SQLite пригодны только композитные индексы.

В процессе тестирования от нашего взгляда ускользнула методика измерения времени запроса. Все запросы выполнялись при активном кэшировании файла БД операционной системой, об этом свидетельствуют сообщения вида "sys 0.000000". С учетом этого параметра можно многое узнать и при первом выполнении запроса на БД, не находящейся в кэше ОС, но это выходит за рамки нашего рассмотрения.

Comments

Popular posts from this blog

Открытый софт для научных расчетов

Счетчики в SQLite

Обработка email-сообщений