Тестирование PostgreSQL 8.3 на больших таблицах: 40М записей и ограничение ОЗУ

Продолжение статьи

Тестирование PostgreSQL 8.3 на больших таблицах: денормализация (40М записей).

В предыдущих заметках мы выполняли некоторые типичные выборки из таблиц с десятками миллионов записей в разных версиях PostgreSQL. Пожалуй, такие таблицы и в самом деле можно назвать большими. Но можно ли назвать нашу тестовую БД большой? Разумеется, нет. Нельзя потому, что вся наша БД умещалась не только в ОЗУ сервера, но даже и в буфере памяти PostgreSQL (shared memory). Да, объемы памяти растут, но растут и массивы информации, которые мы хотим обрабатывать, потому для работы с действительно большими БД мы должны быть готовы к тому, что Бд будет значительно больше, чем объем доступной памяти. Для того, чтобы получить удобные для сравнения результаты, мы воспользуемся той же тестовой БД, но объем памяти на запрос и для кэша уменьшим вдесятеро и теперь наша таблица вдесятеро превышает размер кэша постгреса. Для того, чтобы обнспечить работу с "горячим" кэшем (когда нужные данные закешированы), мы предварительно проводим запрос "select (*) ...", и только потом делаем "explain analyze ..." для того же запроса; такой подход гарантирует нам, что полученное время выполнения наименьшее из возможных. Также перед началом работы мы выполним команду "analyze".

Параметры сервера PostgreSQL:

shared_buffers = 160MB
work_mem = 10MB                        # min 64kB
maintenance_work_mem = 20MB            # min 1MB

Нормализованная таблица без справочников (40M записей)

select count(*) from tmp_facts7 where a1=2 and a2=2 and a3=2 and a4=2;
 count
-------
   391
(1 row)

explain analyze select * from tmp_facts7 where a1=2 and a2=2 and a3=2 and a4=2;
                                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tmp_facts7  (cost=102337.80..122391.31 rows=303 width=16) (actual time=7236.866..9634.813 rows=391 loops=1)
   Recheck Cond: ((a3 = 2) AND (a4 = 2) AND (a2 = 2))
   Filter: (a1 = 2)
   ->  BitmapAnd  (cost=102337.80..102337.80 rows=5749 width=0) (actual time=7225.881..7225.881 rows=0 loops=1)
         ->  Bitmap Index Scan on tmp_facts7_a3_idx  (cost=0.00..33790.48 rows=2075563 width=0) (actual time=2366.257..2366.257 rows=2223561 loops=1)
               Index Cond: (a3 = 2)
         ->  Bitmap Index Scan on tmp_facts7_a4_idx  (cost=0.00..34273.29 rows=2105271 width=0) (actual time=2367.129..2367.129 rows=2224826 loops=1)
               Index Cond: (a4 = 2)
         ->  Bitmap Index Scan on tmp_facts7_a2_idx  (cost=0.00..34273.29 rows=2105271 width=0) (actual time=2365.304..2365.304 rows=2221344 loops=1)
               Index Cond: (a2 = 2)
 Total runtime: 9635.849 ms
(11 rows)

Нормализованная таблица со справочниками (40M записей + 10,100,1k,10k записей в справочниках)

select count(*)
from tmp_facts7
join tmp_facts7_a1 on tmp_facts7.a1=tmp_facts7_a1.id
join tmp_facts7_a2 on tmp_facts7.a2=tmp_facts7_a2.id
join tmp_facts7_a3 on tmp_facts7.a3=tmp_facts7_a3.id
join tmp_facts7_a4 on tmp_facts7.a4=tmp_facts7_a4.id
where tmp_facts7_a1.name='00' and tmp_facts7_a2.name='00' and tmp_facts7_a3.name='00' and tmp_facts7_a4.name='00';
 count
-------
   391
(1 row)

explain analyze
select count(*)
from tmp_facts7
join tmp_facts7_a1 on tmp_facts7.a1=tmp_facts7_a1.id
join tmp_facts7_a2 on tmp_facts7.a2=tmp_facts7_a2.id
join tmp_facts7_a3 on tmp_facts7.a3=tmp_facts7_a3.id
join tmp_facts7_a4 on tmp_facts7.a4=tmp_facts7_a4.id
where tmp_facts7_a1.name='00' and tmp_facts7_a2.name='00' and tmp_facts7_a3.name='00' and tmp_facts7_a4.name='00';
                                                                               QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=124783.94..124783.95 rows=1 width=0) (actual time=9909.587..9909.588 rows=1 loops=1)
   ->  Nested Loop  (cost=104399.33..124783.94 rows=1 width=0) (actual time=7236.545..9909.143 rows=391 loops=1)
         Join Filter: (tmp_facts7.a1 = tmp_facts7_a1.id)
         ->  Nested Loop  (cost=104399.33..124782.80 rows=1 width=4) (actual time=7226.662..9871.196 rows=6768 loops=1)
               ->  Nested Loop  (cost=0.00..19.81 rows=1 width=12) (actual time=0.105..0.141 rows=1 loops=1)
                     ->  Nested Loop  (cost=0.00..11.53 rows=1 width=8) (actual time=0.079..0.110 rows=1 loops=1)
                           ->  Seq Scan on tmp_facts7_a2  (cost=0.00..3.25 rows=1 width=4) (actual time=0.011..0.039 rows=1 loops=1)
                                 Filter: (name = '00'::text)
                           ->  Index Scan using tmp_facts7_a4_name_idx on tmp_facts7_a4  (cost=0.00..8.27 rows=1 width=4) (actual time=0.065..0.066 rows=1 loops=1)
                                 Index Cond: (tmp_facts7_a4.name = '00'::text)
                     ->  Index Scan using tmp_facts7_a3_name_idx on tmp_facts7_a3  (cost=0.00..8.27 rows=1 width=4) (actual time=0.026..0.031 rows=1 loops=1)
                           Index Cond: (tmp_facts7_a3.name = '00'::text)
               ->  Bitmap Heap Scan on tmp_facts7  (cost=104399.33..124660.93 rows=5832 width=16) (actual time=7226.553..9865.072 rows=6768 loops=1)
                     Recheck Cond: ((tmp_facts7.a4 = tmp_facts7_a4.id) AND (tmp_facts7.a3 = tmp_facts7_a3.id) AND (tmp_facts7.a2 = tmp_facts7_a2.id))
                     ->  BitmapAnd  (cost=104399.33..104399.33 rows=5832 width=0) (actual time=7224.425..7224.425 rows=0 loops=1)
                           ->  Bitmap Index Scan on tmp_facts7_a4_idx  (cost=0.00..34273.29 rows=2105271 width=0) (actual time=2366.752..2366.752 rows=2224826 loops=1)
                                 Index Cond: (tmp_facts7.a4 = tmp_facts7_a4.id)
                           ->  Bitmap Index Scan on tmp_facts7_a3_idx  (cost=0.00..34273.29 rows=2105271 width=0) (actual time=2367.871..2367.871 rows=2223561 loops=1)
                                 Index Cond: (tmp_facts7.a3 = tmp_facts7_a3.id)
                           ->  Bitmap Index Scan on tmp_facts7_a2_idx  (cost=0.00..34273.29 rows=2105271 width=0) (actual time=2363.284..2363.284 rows=2221344 loops=1)
                                 Index Cond: (tmp_facts7.a2 = tmp_facts7_a2.id)
         ->  Seq Scan on tmp_facts7_a1  (cost=0.00..1.12 rows=1 width=4) (actual time=0.002..0.004 rows=1 loops=6768)
               Filter: (tmp_facts7_a1.name = '00'::text)
 Total runtime: 9910.388 ms
(24 rows)


select count(*)
from tmp_facts7
where 
tmp_facts7.a1 IN (select id from tmp_facts7_a1 where name='00') and
tmp_facts7.a2 IN (select id from tmp_facts7_a2 where name='00') and
tmp_facts7.a3 IN (select id from tmp_facts7_a3 where name='00') and
tmp_facts7.a4 IN (select id from tmp_facts7_a4 where name='00');
 count
-------
   391
(1 row)

explain analyze
select count(*)
from tmp_facts7
where 
tmp_facts7.a1 IN (select id from tmp_facts7_a1 where name='00') and
tmp_facts7.a2 IN (select id from tmp_facts7_a2 where name='00') and
tmp_facts7.a3 IN (select id from tmp_facts7_a3 where name='00') and
tmp_facts7.a4 IN (select id from tmp_facts7_a4 where name='00');
                                                                                  QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=298421.25..298421.26 rows=1 width=0) (actual time=10406.970..10406.970 rows=1 loops=1)
   ->  Nested Loop IN Join  (cost=34817.31..298421.24 rows=1 width=0) (actual time=2474.674..10406.279 rows=391 loops=1)
         Join Filter: (tmp_facts7.a2 = tmp_facts7_a2.id)
         ->  Hash IN Join  (cost=34817.31..298417.98 rows=1 width=4) (actual time=2445.150..10262.455 rows=6898 loops=1)
               Hash Cond: (tmp_facts7.a1 = tmp_facts7_a1.id)
               ->  Hash IN Join  (cost=34816.17..298416.82 rows=4 width=8) (actual time=2444.948..10200.662 rows=124045 loops=1)
                     Hash Cond: (tmp_facts7.a3 = tmp_facts7_a3.id)
                     ->  Nested Loop  (cost=34807.89..298393.49 rows=4000 width=12) (actual time=2444.714..9127.511 rows=2224826 loops=1)
                           ->  HashAggregate  (cost=8.28..8.29 rows=1 width=4) (actual time=0.067..0.068 rows=1 loops=1)
                                 ->  Index Scan using tmp_facts7_a4_name_idx on tmp_facts7_a4  (cost=0.00..8.27 rows=1 width=4) (actual time=0.062..0.063 rows=1 loops=1)
                                       Index Cond: (name = '00'::text)
                           ->  Bitmap Heap Scan on tmp_facts7  (cost=34799.61..272069.32 rows=2105271 width=16) (actual time=2444.642..7280.484 rows=2224826 loops=1)
                                 Recheck Cond: (tmp_facts7.a4 = tmp_facts7_a4.id)
                                 ->  Bitmap Index Scan on tmp_facts7_a4_idx  (cost=0.00..34273.29 rows=2105271 width=0) (actual time=2372.121..2372.121 rows=2224826 loops=1)
                                       Index Cond: (tmp_facts7.a4 = tmp_facts7_a4.id)
                     ->  Hash  (cost=8.27..8.27 rows=1 width=4) (actual time=0.051..0.051 rows=1 loops=1)
                           ->  Index Scan using tmp_facts7_a3_name_idx on tmp_facts7_a3  (cost=0.00..8.27 rows=1 width=4) (actual time=0.047..0.048 rows=1 loops=1)
                                 Index Cond: (name = '00'::text)
               ->  Hash  (cost=1.12..1.12 rows=1 width=4) (actual time=0.025..0.025 rows=1 loops=1)
                     ->  Seq Scan on tmp_facts7_a1  (cost=0.00..1.12 rows=1 width=4) (actual time=0.020..0.023 rows=1 loops=1)
                           Filter: (name = '00'::text)
         ->  Seq Scan on tmp_facts7_a2  (cost=0.00..3.25 rows=1 width=4) (actual time=0.003..0.019 rows=1 loops=6898)
               Filter: (tmp_facts7_a2.name = '00'::text)
 Total runtime: 10407.798 ms
(24 rows)


select count(*)
from tmp_facts7,tmp_facts7_a1,tmp_facts7_a2,tmp_facts7_a3,tmp_facts7_a4
where tmp_facts7.a1=tmp_facts7_a1.id and tmp_facts7.a2=tmp_facts7_a2.id and tmp_facts7.a3=tmp_facts7_a3.id and tmp_facts7.a4=tmp_facts7_a4.id
and tmp_facts7_a1.name='00' and tmp_facts7_a2.name='00' and tmp_facts7_a3.name='00' and tmp_facts7_a4.name='00';
 count
-------
   391
(1 row)

explain analyze
select count(*)
from tmp_facts7,tmp_facts7_a1,tmp_facts7_a2,tmp_facts7_a3,tmp_facts7_a4
where tmp_facts7.a1=tmp_facts7_a1.id and tmp_facts7.a2=tmp_facts7_a2.id and tmp_facts7.a3=tmp_facts7_a3.id and tmp_facts7.a4=tmp_facts7_a4.id
and tmp_facts7_a1.name='00' and tmp_facts7_a2.name='00' and tmp_facts7_a3.name='00' and tmp_facts7_a4.name='00';
                                                                               QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=124783.94..124783.95 rows=1 width=0) (actual time=9890.832..9890.832 rows=1 loops=1)
   ->  Nested Loop  (cost=104399.33..124783.94 rows=1 width=0) (actual time=7238.444..9890.397 rows=391 loops=1)
         Join Filter: (tmp_facts7.a1 = tmp_facts7_a1.id)
         ->  Nested Loop  (cost=104399.33..124782.80 rows=1 width=4) (actual time=7228.591..9852.343 rows=6768 loops=1)
               ->  Nested Loop  (cost=0.00..19.81 rows=1 width=12) (actual time=0.104..0.141 rows=1 loops=1)
                     ->  Nested Loop  (cost=0.00..11.53 rows=1 width=8) (actual time=0.077..0.109 rows=1 loops=1)
                           ->  Seq Scan on tmp_facts7_a2  (cost=0.00..3.25 rows=1 width=4) (actual time=0.011..0.040 rows=1 loops=1)
                                 Filter: (name = '00'::text)
                           ->  Index Scan using tmp_facts7_a4_name_idx on tmp_facts7_a4  (cost=0.00..8.27 rows=1 width=4) (actual time=0.064..0.065 rows=1 loops=1)
                                 Index Cond: (tmp_facts7_a4.name = '00'::text)
                     ->  Index Scan using tmp_facts7_a3_name_idx on tmp_facts7_a3  (cost=0.00..8.27 rows=1 width=4) (actual time=0.025..0.028 rows=1 loops=1)
                           Index Cond: (tmp_facts7_a3.name = '00'::text)
               ->  Bitmap Heap Scan on tmp_facts7  (cost=104399.33..124660.93 rows=5832 width=16) (actual time=7228.484..9846.196 rows=6768 loops=1)
                     Recheck Cond: ((tmp_facts7.a4 = tmp_facts7_a4.id) AND (tmp_facts7.a3 = tmp_facts7_a3.id) AND (tmp_facts7.a2 = tmp_facts7_a2.id))
                     ->  BitmapAnd  (cost=104399.33..104399.33 rows=5832 width=0) (actual time=7226.363..7226.363 rows=0 loops=1)
                           ->  Bitmap Index Scan on tmp_facts7_a4_idx  (cost=0.00..34273.29 rows=2105271 width=0) (actual time=2365.662..2365.662 rows=2224826 loops=1)
                                 Index Cond: (tmp_facts7.a4 = tmp_facts7_a4.id)
                           ->  Bitmap Index Scan on tmp_facts7_a3_idx  (cost=0.00..34273.29 rows=2105271 width=0) (actual time=2370.292..2370.292 rows=2223561 loops=1)
                                 Index Cond: (tmp_facts7.a3 = tmp_facts7_a3.id)
                           ->  Bitmap Index Scan on tmp_facts7_a2_idx  (cost=0.00..34273.29 rows=2105271 width=0) (actual time=2363.790..2363.790 rows=2221344 loops=1)
                                 Index Cond: (tmp_facts7.a2 = tmp_facts7_a2.id)
         ->  Seq Scan on tmp_facts7_a1  (cost=0.00..1.12 rows=1 width=4) (actual time=0.002..0.004 rows=1 loops=6768)
               Filter: (tmp_facts7_a1.name = '00'::text)
 Total runtime: 9891.620 ms
(24 rows)

Выводы

Что ж, пришла пора подвести итог нашему путешествию по миру баз данных PostgreSQL. Обсудим по отдельности следующие моменты:

1. Работа с небольшими БД, которые заведомо помещаются в кэш СУБД.

В версии 8.1 имеются большие проблемы планировщика (про ввод-вывод, проматывание счетчика читающих транзакций и т.п. говорить не будем, т.к. это выходит за рамки проведенных тестов). В версии 8.3 сделаны серьезные улучшения, теперь за разумное время можно осуществлять выборки из таблиц в миллионы и десятки миллионов записей. Отметим, что при группировке и работе с view есть другие проблемы, но это опять же нужно исследовать отдельно. Таким образом, СУБД обеспечивает хранилище данных с удобным доступом, а вот что касается обработки данных, то для получения приемлемой производительности многое придется это делать в хранимых процедурах или в приложении, поскольку планировщик зачастую не способен обеспечить эффективное выполнение даже достаточно простых запросов.

Значит, возможно использовать СУБД для создания хранилища данных, но не для систем анализа.

2. Работа с большими БД, которые заведомо не помещаются в кэш СУБД.

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

Внешняя система кэширования позволит во многом исправить ситуацию, но получится весьма громоздкое решение, к тому же, по производительности уступающее аналогичной системе на файловых СУБД berkeleydb или sqlite, которые способны делать настолько быстрые выборки, что дополнительное кэширование становится просто бесполезным. Соответствующие тесты для SQLite уже опубликованы (вероятно, в дальнейшем к ним добавятся и новые тесты).