Тестирование 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

Post a Comment

Popular posts from this blog

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

Задачки у меня бывают разные, кратенькое описание того, что может потребоваться я приводил здесь: Софт для спектрального анализа/численного моделирования/etc. Дело происходит в Linux Debian, соответственно, интересует софт, который устанавливается из стандартного репозитория (что подразумевает опенсорсность).  Для начала я протестировал двумерное Фурье преобразование (прямое и обратное) на широко известном тестовом изображении Lenna. Заодно в процессе поиска нужных функций увидел заметки об истории этого изображения  Просто Лена  и  Обработка изображений: кто такая Лена Выбор софта для такой типичной задачки вполне себе наличествует: Scientific Computing with Free software on GNU/Linux HOWTO Пакеты прикладных программ Инструменты визуализации данных для Linux На практике же оказалось не так радужно, как хотелось бы. Из того, что есть в дебиане, работает "из коробки" только пакет octave . Зато для него можно установить множество расширений - нап
Read more

Счетчики в SQLite

Недавно ко мне обратился прикладной программист, работающий с моим фрэймворком для AOLServer и SQLite с вопросом, как ему сделать счетчик записей, возвращенных запросом. Разумеется, можно создать временную таблицу или забиндить тиклевскую функцию, но есть и более простой способ - использовать написанный мною модуль key или tempkey (отличаются только тем, что последний использует временную таблицу, таким образом, при хранении временных объектов в ОЗУ не требуется запись на диск). Модуль key создает при инициализации таблицу keys (а модуль tempkey - временную таблицу tempkeys), в которой и будут храниться все ключи; таким образом, можно использовать практически неограниченное количество ключей без ущерба для производительности и не нуждаясь в большом количестве ОЗУ. Рассмотрим на примере неименованного временного счетчика: create table colors(name text); insert into colors values ('Red'); insert into colors values ('Green'); insert into colors values ('Blue'); sel
Read more

Кольцевые структуры в геофизике

Опубликовал теоретическое обоснование и результаты численной модели для появления кольцевых структур в гравитационном поле. Поскольку найденные структуры существуют после применения фильтра высоких частот к полю, из этого следуют разные интересные вещи. Например, эти же структуры проявляются в цифровой модели рельефа и на космических снимках. Таким образом, подтверждаются практические работы Ю.И. Фивенского о существовании разномасштабных кольцевых структур, их видимости на рельефе и на снимках и пропорциональности их радиусов с глубиной залегания вызывающих их источников таких, как ловушки углеводородов, с коэффициентом около 0.7. Также можно предположить, что снежный покров работает как фильтр высокой частоты, что и позволяет лучше проявиться паттернам на поверхности - для тех конфигураций источников, которые иначе без фильтрации гравитационного поля не различимы. Ссылка на гитхаб-репозиторий со статьями: https://github.com/mobigroup/articles/  Первые две работы вводные, интересны
Read more