Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Сравнение СУБД Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 Выбор БД для быстрой работы с большими таблицами.  [new]
Carrie
Member

Откуда:
Сообщений: 17
Есть postgresql 7.4.x, есть таблица, в которой кол-во записей ~35млн, одно из полей типа SMALLINT (2bytes) и по нему создан Btree индекс.
так вот запрос вида
select count(*) from tbl_name where это_поле>17::smallint;
выполняется ~40секунд при том что результат запроса - всего 9003 записей.
Explain показывает, что БД юзает индекс.

Потому есть такой вопрос: можно ли выбрать более производительную БД, чтобы запросы такого характера (по большим таблицам, где число записей удовлетворяющих условию очень мало) выполнялись быстрее? (ес-но имея индекс по соответствующим полям).

Так же тестировался mysql 4.0.18, где результаты существенно печальней. Конфиги для mysql и postgresql, как мне кажется, настроены сейчас оптимально; машинка с БД более чем приличная (2x2.4 Xeon, 2Гб RAM, 4x36Гб SCSI etc..)
25 авг 04, 16:01    [907657]     Ответить | Цитировать Сообщить модератору
 Re: Выбор БД для быстрой работы с большими таблицами.  [new]
ScareCrow
Member

Откуда: Белый город
Сообщений: 17472
а что счетчики показывают?
25 авг 04, 16:07    [907694]     Ответить | Цитировать Сообщить модератору
 Re: Выбор БД для быстрой работы с большими таблицами.  [new]
Yo!
Guest
ту которая позволяет парионинг ...
25 авг 04, 16:19    [907764]     Ответить | Цитировать Сообщить модератору
 Re: Выбор БД для быстрой работы с большими таблицами.  [new]
Carrie
Member

Откуда:
Сообщений: 17
v3=# \d exp
Table "public.exp"
Column | Type | Modifiers
--------+----------+-----------
...вырезано...
pr | smallint |
cost | bigint |

Indexes:
"exp_idx1" btree (pr)

v3=# select count(*) from exp;
count
----------
34700903
(1 row)

Time: 91860.301 ms
v3=# explain select count(*), sum(cost) from exp where pr>17::smallint;
QUERY PLAN
---------------------------------------------------------------------
Aggregate (cost=831801.15..831801.15 rows=1 width=8)
-> Seq Scan on exp (cost=0.00..773966.30 rows=11566968 width=8)
Filter: (pr > 17::smallint)
(3 rows)

Time: 243.354 ms
v3=# set enable_seqscan=off;
SET
Time: 1.307 ms
v3=# explain select count(*), sum(cost) from exp where pr>17::smallint;
QUERY PLAN
---------------------------------------------------------------------------------------
Aggregate (cost=4746664.26..4746664.26 rows=1 width=8)
-> Index Scan using exp_idx1 on exp (cost=0.00..4688829.42 rows=11566968 width=8)
Index Cond: (pr > 17::smallint)
(3 rows)

Time: 1.703 ms
3=# select count(*), sum(cost) from exp where pr>17::smallint;
count | sum
-------+------------
9008 | 2644131197
(1 row)

Time: 29359.521 ms

explain analyze выложу попозже, если интересно ... - закэшировалось все и timing'и не посмотреть)
25 авг 04, 16:25    [907789]     Ответить | Цитировать Сообщить модератору
 Re: Выбор БД для быстрой работы с большими таблицами.  [new]
ScareCrow
Member

Откуда: Белый город
Сообщений: 17472
все с тобой ясно...
25 авг 04, 16:37    [907849]     Ответить | Цитировать Сообщить модератору
 Re: Выбор БД для быстрой работы с большими таблицами.  [new]
Серега
Member

Откуда:
Сообщений: 887
Carrie
выполняется ~40секунд при том что результат запроса - всего 9003 записей.
Explain показывает, что БД юзает индекс.

машинка с БД более чем приличная (2x2.4 Xeon, 2Гб RAM, 4x36Гб SCSI etc..)

А сеть какая? 9000 записей протащить тоже время надо. Кстати зачем столько? Не в грид случаем?
25 авг 04, 16:41    [907862]     Ответить | Цитировать Сообщить модератору
 Re: Выбор БД для быстрой работы с большими таблицами.  [new]
LeXa NalBat
Member

Откуда: Москва
Сообщений: 2892
У меня постгрес 7.3 и оракл 8.1 на запросе аналогичном вашему показали примерно одинаковый результат, причем в 10 раз быстрее 40 секунд. Эксперимент проводил с незакэшированными данными, повторный запрос и на постгре и на оракле отрабатывает за пять сотых секунды. На одной и той же железке по мощности примерно такой же как ваша. В таблице plprice_00 немногим менее 3 миллионов строк.

update@thyella:~$ time echo "select count(*) from plprice_00 where plno > 170813001;" | sqlplus pl/pl@local

SQL*Plus: Release 8.1.7.0.0 - Production on Wed Aug 25 16:49:15 2004

(c) Copyright 2000 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production

SQL>
COUNT(*)
----------
9000

SQL> Disconnected from Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production

real 0m4.877s
user 0m0.030s
sys 0m0.020s
update@thyella:~$ time echo "select count(*) from plprice_00 where plno > 170813001;" | psql -U pl -d pl
count
-------
9000
(1 row)


real 0m4.991s
user 0m0.000s
sys 0m0.000s
25 авг 04, 17:13    [908030]     Ответить | Цитировать Сообщить модератору
 Re: Выбор БД для быстрой работы с большими таблицами.  [new]
Константин Лисянский
Member

Откуда: Москва
Сообщений: 902
У меня Teradata.

Таблица такая:


CREATE SET TABLE rpdm30.w_HOURLY_PRODUCT_SALES ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      DayDT INTEGER,
      HourID INTEGER,
      ItemID INTEGER,
      ItemRev FLOAT COMPRESS 0.00000000000000E 000 )
UNIQUE PRIMARY INDEX XPK_wHPS ( DayDT ,HourID ,ItemID )
PARTITION BY RANGE_N(DayDT  BETWEEN DATE '2001-01-01' AND DATE '2004-12-25' EACH INTERVAL '1' DAY )
INDEX w_hs_IDX ( ItemID );

Видно (в последней строке), что есть индекс по ItemID.


Запрос:
SELECT COUNT (*) FROM w_HOURLY_PRODUCT_SALES

Возвращает 257 730 888 строк (немного побольше, чем у Вас :).

Выполняется 12 секунд.


Запрос:
SELECT COUNT (*) FROM w_HOURLY_PRODUCT_SALES WHERE ItemID<1000

Выполняется 11 секунд.
Возвращает 8808 строк.

План запроса:

Explanation
  1) First, we lock a distinct RPDM30."pseudo table" for read on a
     RowHash to prevent global deadlock for
     RPDM30.w_HOURLY_PRODUCT_SALES. 
  2) Next, we lock RPDM30.w_HOURLY_PRODUCT_SALES for read. 
  3) We do an all-AMPs SUM step to aggregate from
     RPDM30.w_HOURLY_PRODUCT_SALES by way of a traversal of index # 4
     without accessing the base table with a residual condition of (
     "RPDM30.w_HOURLY_PRODUCT_SALES.ItemID < 1000").  Aggregate
     Intermediate Results are computed globally, then placed in Spool 3. 
     The input table will not be cached in memory, but it is eligible
     for synchronized scanning.  The size of Spool 3 is estimated with
     high confidence to be 1 row.  The estimated time for this step is
     2 minutes and 58 seconds. 
  4) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
     an all-rows scan into Spool 1 (group_amps), which is built locally
     on the AMPs.  The size of Spool 1 is estimated with high
     confidence to be 1 row.  The estimated time for this step is 0.03
     seconds. 
  5) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1. 


Железка такая:
2 XEON 2.8 ГГЦ, 4 ГБ ОЗУ. 10 зеркальных пар SCSI по 36 ГБ. Дисков побольше, чем у Вас, это существенно помогает.


Кстати, за то время, которое у Вас происходит сканирование индекса, у меня вся таблица сканируется.
По HourID у меня нету индекса.
Запрос:
SELECT COUNT (*) FROM w_HOURLY_PRODUCT_SALES WHERE HourID>15
Выполняется за 42 секунды.

Вот план запроса:
Explanation
  1) First, we lock a distinct RPDM30."pseudo table" for read on a
     RowHash to prevent global deadlock for
     RPDM30.w_HOURLY_PRODUCT_SALES. 
  2) Next, we lock RPDM30.w_HOURLY_PRODUCT_SALES for read. 
  3) We do an all-AMPs SUM step to aggregate from
     RPDM30.w_HOURLY_PRODUCT_SALES by way of an all-rows scan with a
     condition of ("RPDM30.w_HOURLY_PRODUCT_SALES.HourID > 15"). 
     Aggregate Intermediate Results are computed globally, then placed
     in Spool 3.  The input table will not be cached in memory, but it
     is eligible for synchronized scanning.  The size of Spool 3 is
     estimated with high confidence to be 1 row.  The estimated time
     for this step is 7 minutes and 59 seconds. 
  4) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
     an all-rows scan into Spool 1 (group_amps), which is built locally
     on the AMPs.  The size of Spool 1 is estimated with high
     confidence to be 1 row.  The estimated time for this step is 0.03
     seconds. 
  5) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1. 


Конечно, у меня данных побольше. Но если я уменьшу количество данных до 35 миллионов, время отклика пропорционально уменьшится (в силу особенностей СУБД Teredata). Соответственно, я ожидаю отклик за примерно 1,5 секунды.
Соответственно, запрос без индекса (фул-скан) выполнится примерно за 5,7 секунд.

Конечно, всё зависит от количества столбцов в таблице.
Например фул-скан таблицы с 14 млн. записей и с 34 столбцами занимает у меня 10 секунд.

А, если не секрет, что за задача?


Серёга
А сеть какая? 9000 записей протащить тоже время надо. Кстати зачем столько? Не в грид случаем?


Смотрите внимательнее текст запроса. Там стоит select count(*). Такой запрос возвращает всего одну строку.


С уважением,
Константин Лисянский
http://lissianski.narod.ru
25 авг 04, 17:36    [908136]     Ответить | Цитировать Сообщить модератору
 Re: Выбор БД для быстрой работы с большими таблицами.  [new]
Gold
Member

Откуда: Харьков
Сообщений: 2947
По-любому на версионнике SELECT COUNT(*) будет томозить больше чем на блокировочнике, так что такие запрося на блокировочниках выполняются быстрее однозначно.
25 авг 04, 18:34    [908348]     Ответить | Цитировать Сообщить модератору
 Re: Выбор БД для быстрой работы с большими таблицами.  [new]
Carrie
Member

Откуда:
Сообщений: 17
ScareCrow
все с тобой ясно...

? ээ...чувствуется насмешка. Ну так намекните просто. Не каждому системному Unix-прораммеру приходится вдруг иметь дело с задачей, которая ему явно не по зубам...Пока. Раньше только с mysql'ем работал, последние 2 месяца с postgres'ом. "Ничего слаще редьки не пробовал"

LeXa NalBat
В таблице plprice_00 немногим менее 3 миллионов строк.

На такой таблице тоже все "махом" работает. А ожидается, что таблица будет увеличиваться на 40-50млн строк в месяц. Причем данные после INSERT'а менятся не будут: по ним будут делатся многочисленные статистические запросы.
Решение необходимо под Linux.

Константин Лисянский
А, если не секрет, что за задача?

Да в общем не секрет. Небольшая провайдерская контора всегда предоставляла клиентам детальную статистику по трафику. Она представляет собой бинарный файл выдаваемый маршрутизатором раз в 15 минут, который содержит фиксированные (по 20 байт) агрегированные по полями src ip, dst ip, proto, src port, dst port, mark, bytes записи. Объемы трафика таковы, что хранить эти данные в одной таблице стало невозможно: выборки даже при наличии индексов (partial) на src ip, dst ip выполняются неимоверно долго. Потому вся статистика хранится в базе в виде blob'ов: один бинарный файл=один blob в табличке. И есть некая библиотечка, которая делает работу с этим хранилищем прозрачным для приложений: разворачивает каждый необходимый блоб по требованию в свою временную таблицу, если таковой не существует, удаляет автоматически такие таблицы, к которым давно не происходило обращений, создает таблицы с суммарными показателями трафика для каждого ip за день и прочее.
Все это все же накладывает определенные неудобства при работе с такими данными. Вот собственно хотелось бы пересмотреть "дизайн".
Если говорить о числе записей, выдаваемых маршрутизатором за месяц - это порядка 500млн. Если о суммарных показателях - такая таблица сейчас порядка 30млн записей/месяц.

P.S.: SELECT COUNT(*) - это как пример самого простого запроса, использующего индекс.
25 авг 04, 19:10    [908415]     Ответить | Цитировать Сообщить модератору
 Re: Выбор БД для быстрой работы с большими таблицами.  [new]
Константин Лисянский
Member

Откуда: Москва
Сообщений: 902
автор
На такой таблице тоже все "махом" работает. А ожидается, что таблица будет увеличиваться на 40-50млн строк в месяц. Причем данные после INSERT'а менятся не будут: по ним будут делатся многочисленные статистические запросы.
Решение необходимо под Linux.


А бюджет имеется или бесплатно хотите?
А можно пример одного "многочисленного статистического запроса"?


С уважением,
Константин Лисянский
http://lissianski.narod.ru
25 авг 04, 19:21    [908431]     Ответить | Цитировать Сообщить модератору
 Re: Выбор БД для быстрой работы с большими таблицами.  [new]
Carrie
Member

Откуда:
Сообщений: 17
Константин Лисянский

А бюджет имеется или бесплатно хотите?
А можно пример одного "многочисленного статистического запроса"?

Я думаю, если разница от использования коммерческой БД вместо postgresql/mysql/firebird будет значительна, то возможно "захотим" и платно. В общем не так все однозначно: нужно смотреть достоинства от такого перехода и TCO.

Запросы просты. К примеру расмотрим текущий вариант, когда netflow-статистика с маршрутизатора хранится в блобах, тогда бы в идеале "суммарная" таблица была бы такой (размер которой прогнозируется до 50млн/месяц; упрощенный вариант):

CREATE TABLE traffic_history (
id BIGSERIAL PRIMARY KEY,
ip INTEGER NOT NULL, //или INET, если угодно
dir boolean NOT NULL, //направление трафика: входящий или исходящий для ip
class SMALLINT NOT NULL, //класс трафика: http, smtp, pop3 etc
bytes INTEGER NOT NULL, //объем в байтах
blob_ref INTEGER NOT NULL REFERENCES blobs(id) //REFERENCE на блоб в табличке блобов, откуда была "сделана" эта запись
);

и табличка с блобами:
CREATE TABLE blobs (
id SERIAL PRIMARY KEY,
ts_start TIMESTAMP NOT NULL, //интервал, за который содержится статистика...
ts_stop TIMESTAMP NOT NULL,
blob_bin BYTEA //...в этом блобе
);

и индексы (для PRIMARY KEY создаются автоматически):
CREATE INDEX traffic_history_idx1 ON traffic_history (ip);
CREATE INDEX traffic_history_idx2 ON traffic_history (ip, id); //нужен для JOIN'а

CREATE INDEX blobs_idx1 ON blobs (ts_stop);
CREATE INDEX blobs_idx2 ON blobs (ts_stop, id); //нужен для JOIN'а

диапазон значений ip строго ограничен размерами адресного пространства, выданного нам РОСНИИРОС: 64 сети по 256 адресов, так что разброс значений составляет 64*256=16384.

запрос для выборки суммарной статистики по заданному ip и интервалу времени:
SELECT sum(a.bytes), a.class FROM traffic_history a LEFT JOIN blobs b ON b.id=a.blob_ref WHERE b.ts_stop BETWEEN '2004-08-01 00:00:00' AND '2004-09-01 00:00:00' AND a.ip='192.168.117.3' GROUP BY a.class;
25 авг 04, 20:01    [908461]     Ответить | Цитировать Сообщить модератору
 Re: Выбор БД для быстрой работы с большими таблицами.  [new]
LeXa NalBat
Member

Откуда: Москва
Сообщений: 2892
Каково распределение значений в этом_поле?

Вы пробовали vacuum analyze, reindex?
25 авг 04, 20:30    [908496]     Ответить | Цитировать Сообщить модератору
 Re: Выбор БД для быстрой работы с большими таблицами.  [new]
Carrie
Member

Откуда:
Сообщений: 17
LeXa NalBat
Каково распределение значений в этом_поле?

v3=# SELECT count(*) AS cnt, pr FROM exp GROUP BY pr ORDER by cnt DESC;
cnt | pr
----------+-------
28784676 | 6
3144128 | 17
2762907 | 1
4645 | 50
2692 | 47
1375 | 51
296 | 94
184 | 4

LeXa NalBat
Вы пробовали vacuum analyze, reindex?

Пробовал.
Пример из первого моего сообщения практической ценности не имеет. Просто хотел показать свое удивление скоростью выполнения этого запроса.
Реальная ситуация, описанная в моем сообщении выше, уже важна. Распределение ip-адресов в диапазоне от first до first+16384 равномерно и время выполнения указанного там же запроса порядка 30сек на таблице traffic_history в ~20-25млн записей.
25 авг 04, 20:46    [908505]     Ответить | Цитировать Сообщить модератору
 Re: Выбор БД для быстрой работы с большими таблицами.  [new]
LeXa NalBat
Member

Откуда: Москва
Сообщений: 2892
Проведите этот тест на вашей железке. (Однако в этом тесте корреляция test2 и i2 абсолютная, попробую завтра сделать test2 перемешанной.)

Странно что 1) у вас "rows=11566968", а у меня "rows=1"; 2) ">17 Total runtime: 223.41 msec" и ">=18 Total runtime: 25.37 msec"; 3) 40 секундного времени выполнения у меня не наблюдается.

pl=# create table test2 ( id smallint );
CREATE TABLE

$ cat test.pl
#!/usr/bin/perl
use strict;
use warnings;
my @data = (
 1  => 2762907,
 4  => 184,
 6  => 28784676,
 17 => 3144128,
 47 => 2692,
 50 => 4645,
 51 => 1375,
 94 => 296,
);
while ( (my $id = shift @data) && (my $num = shift @data) ) {
 for (my $i=0; $i<$num; $i++) {
  printf "%d\n", $id;
 }
}


$ time ./test.pl | psql -U pl -d pl -c "copy test2 from stdin"

real    2m43.970s
user    1m26.890s
sys     0m1.030s

pl=# create index i2 on test2 ( id );
CREATE INDEX

pl=# VACUUM ANALYZE test2;
VACUUM

pl=# explain select count(*) from test2 where id>17::smallint;
                              QUERY PLAN
----------------------------------------------------------------------
 Aggregate  (cost=2.02..2.02 rows=1 width=0)
   ->  Index Scan using i2 on test2  (cost=0.00..2.01 rows=1 width=0)
         Index Cond: (id > 17::smallint)
(3 rows)

pl=# explain analyze select count(*) from test2 where id>17::smallint;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2.02..2.02 rows=1 width=0) (actual time=347.51..347.51 rows=1 loops=1)
   ->  Index Scan using i2 on test2  (cost=0.00..2.01 rows=1 width=0) (actual time=307.59..326.69 rows=9008 loops=1)
         Index Cond: (id > 17::smallint)
 Total runtime: 361.96 msec
(4 rows)

pl=# explain analyze select count(*) from test2 where id>17::smallint;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2.02..2.02 rows=1 width=0) (actual time=223.36..223.36 rows=1 loops=1)
   ->  Index Scan using i2 on test2  (cost=0.00..2.01 rows=1 width=0) (actual time=197.67..216.35 rows=9008 loops=1)
         Index Cond: (id > 17::smallint)
 Total runtime: 223.41 msec

pl=# explain analyze select count(*) from test2 where id>=18::smallint;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2.02..2.02 rows=1 width=0) (actual time=25.31..25.31 rows=1 loops=1)
   ->  Index Scan using i2 on test2  (cost=0.00..2.01 rows=1 width=0) (actual time=0.03..18.59 rows=9008 loops=1)
         Index Cond: (id >= 18::smallint)
 Total runtime: 25.37 msec
(4 rows)
25 авг 04, 21:43    [908528]     Ответить | Цитировать Сообщить модератору
 Re: Выбор БД для быстрой работы с большими таблицами.  [new]
Nikolay Kulikov
Member

Откуда: Москва
Сообщений: 607
Произвоидельность тупого индекс скана зависит от подсистемы IO.
Что у вас c ней??? Опять же попробуй указывать >, a >=
Потому как > 17 и 18, и 19, и 20 etc...
A если укажешь >=18 то серверу будет понятно с какой ветки индекса начинать скан.
26 авг 04, 10:44    [909143]     Ответить | Цитировать Сообщить модератору
 Re: Выбор БД для быстрой работы с большими таблицами.  [new]
ScareCrow
Member

Откуда: Белый город
Сообщений: 17472
Carrie
Не каждому системному Unix-прораммеру приходится вдруг иметь дело с задачей, которая ему явно не по зубам
сами мы не местные, отстали от группы, помогите люди добрые , кто чем может....
напиши показатели основных счетчиков... поищем узкое место.. не может быть чтоб на такой железяке так все медленно делалось...
26 авг 04, 11:41    [909443]     Ответить | Цитировать Сообщить модератору
 Re: Выбор БД для быстрой работы с большими таблицами.  [new]
Carrie
Member

Откуда:
Сообщений: 17
ScareCrow

напиши показатели основных счетчиков... поищем узкое место.. не может быть чтоб на такой железяке так все медленно делалось...

Что вы под этим подразумеваете? содержимое pg_stat_user_tables, pg_stat_user_indexes, pg_statio_user_tables касательно этой таблицы?

to LeXa NalBat:
Тесты провел. Вот что получилось:
$ time ./test.pl | psql -U postgres -d network_v3 -c "copy test2 from stdin"

real    6m20.749s
user    2m48.610s
sys     1m12.810s
...
network_v3=# explain select count(*) from test2 where id>17::smallint;
                                QUERY PLAN
---------------------------------------------------------------------------
 Aggregate  (cost=186.56..186.56 rows=1 width=0)
   ->  Index Scan using i2 on test2  (cost=0.00..165.66 rows=8359 width=0)
         Index Cond: (id > 17::smallint)
(3 rows)

network_v3=# explain analyze select count(*) from test2 where id>17::smallint;
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=186.56..186.56 rows=1 width=0) (actual time=1527.160..1527.161 rows=1 loops=1)
   ->  Index Scan using i2 on test2  (cost=0.00..165.66 rows=8359 width=0) (actual time=1495.861..1516.354 rows=9008 loops=1)
         Index Cond: (id > 17::smallint)
 Total runtime: 1527.217 ms
(4 rows)

network_v3=# explain analyze select count(*) from test2 where id>17::smallint;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=186.56..186.56 rows=1 width=0) (actual time=269.735..269.736 rows=1 loops=1)
   ->  Index Scan using i2 on test2  (cost=0.00..165.66 rows=8359 width=0) (actual time=239.054..259.289 rows=9008 loops=1)
         Index Cond: (id > 17::smallint)
 Total runtime: 269.791 ms
(4 rows)

network_v3=# explain analyze select count(*) from test2 where id>=18::smallint;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=186.56..186.56 rows=1 width=0) (actual time=30.681..30.682 rows=1 loops=1)
   ->  Index Scan using i2 on test2  (cost=0.00..165.66 rows=8359 width=0) (actual time=0.041..20.075 rows=9008 loops=1)
         Index Cond: (id >= 18::smallint)
 Total runtime: 30.735 ms
(4 rows)

К сожалению сейчас занят работой, вечером позже гляну конфиг postgres'а на предмет почему наши explain analyze выдают разные результаты.
26 авг 04, 12:44    [909821]     Ответить | Цитировать Сообщить модератору
 Re: Выбор БД для быстрой работы с большими таблицами.  [new]
LeXa NalBat
Member

Откуда: Москва
Сообщений: 2892
1)
Мне кажется, что различие в выдачах explain у нас может объясняться разными значениями STATISTICS_LEVEL, который может быть установлен например командой "ALTER TABLE ... ALTER COLUMN ... SET STATISTICS ...". Что может сказаться лишь на выборе иного плана оптимизатором, но не на реальной скорости выполнения запроса при одинаковом выбранном плане (выбранном опримизатором самостоятельно или с помощью хинтов типа set enable_*).


2)
Разницу во времени выполнения запросов ">17" и ">=18" я не понимаю наверное потому что не владею мат-частью поиска по B-деревьям. Не знаю, подвержены ли этому другие БД, но думаю, что здесь мы можем взять на себя "помощь" постгресу переформулировав запрос на ">=18".


3)
40-секундного выполнения запроса в моем примере не наблюдалось именно из-за полной корреляции таблицы и индекса. Я провел следующий эксперимент. Создал таблицы test2, test3, test4 одинаковым способом:
pl=# create table test# ( id smallint );
$ time ./test#.pl | psql -c "copy test# from stdin"
pl=# create index i# on test# ( id );
pl=# ALTER TABLE test# ALTER COLUMN id SET STATISTICS 1000;
pl=# ANALYZE test#;
С использованием разных скриптов. В таблице test2 значения >17 расположены в ~1 дисковой странице, в test3 - в ~184 страницах, в test4 - размазаны по всей таблице, размер файла которой в директории постгреса равен ~1Gb.
#!/usr/bin/perl
# test2.pl
use strict;
use warnings;
my @data = (
 1  => 2762907,
 4  => 184,
 6  => 28784676,
 17 => 3144128,
 47 => 2692,
 50 => 4645,
 51 => 1375,
 94 => 296,
);
while ( (my $id = shift @data) && (my $num = shift @data) ) {
 for (my $i=0; $i<$num; $i++) {
  printf "%d\n", $id;
 }
}
#!/usr/bin/perl
# test3.pl
use strict;
use warnings;
my %data = (
 1 =>  15015,  # 2762907
 4 =>  1,      # 184
 6 =>  156438, # 28784676
 17 => 17087,  # 3144128
 47 => 14,     # 2692
 50 => 25,     # 4645
 51 => 7,      # 1375
 94 => 2,      # 296
);
my $min_cnt = 184;
for (my $j=0; $j<$min_cnt; $j++) {
 for my $id ( sort { rand()<=>0.5 } keys %data ) {
  for (my $i=0; $i<$data{$id}; $i++) {
   printf "%d\n", $id;
  }
 }
}
#!/usr/bin/perl
# test4.pl
use strict;
use warnings;
my @data = (
 [0.829508 => 6 ], # 28784676,
 [0.920114 => 17], # 3144128,
 [0.999735 => 1 ], # 2762907,
 [0.999869 => 50], # 4645,
 [0.999947 => 47], # 2692,
 [0.999986 => 51], # 1375,
 [0.999995 => 94], # 296,
 [1.000000 => 4 ], # 184,
);
my $num = scalar @data;
my $cnt = 34700903;
OU: for (my $j=0; $j<$cnt; $j++) {
 my $rnd = rand();
 IN: for (my $i=0; $i<$num; $i++) {
  if ($data[$i]->[0]>$rnd) {
   printf "%d\n", $data[$i]->[1];
   last IN;
  }
 }
}
После выполнения команд "analyze TABLE" очистил системный дисковый кэш. И провел одинаковый запрос:
pl=# explain analyze select count(*) from test2 where id>=47::smallint;
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=163.75..163.75 rows=1 width=0) (actual time=856.40..856.40 rows=1 loops=1)
   ->  Index Scan using i2 on test2  (cost=0.00..145.82 rows=7172 width=0) (actual time=183.32..849.58 rows=9008 loops=1)
         Index Cond: (id >= 47::smallint)
 Total runtime: 895.07 msec
(4 rows)

pl=# explain analyze select count(*) from test3 where id>=47::smallint;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=9208.74..9208.74 rows=1 width=0) (actual time=9472.92..9472.92 rows=1 loops=1)
   ->  Index Scan using i3 on test3  (cost=0.00..9185.89 rows=9138 width=0) (actual time=215.76..9465.89 rows=8832 loops=1)
         Index Cond: (id >= 47::smallint)
 Total runtime: 9472.98 msec
(4 rows)

pl=# explain analyze select count(*) from test4 where id>=47::smallint;
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=8962.73..8962.73 rows=1 width=0) (actual time=114142.94..114142.94 rows=1 loops=1)
   ->  Index Scan using i4 on test4  (cost=0.00..8940.46 rows=8907 width=0) (actual time=202.91..114125.40 rows=8937 loops=1)
         Index Cond: (id >= 47::smallint)
 Total runtime: 114143.01 msec
(4 rows)
Выборка из test4 - 114 секунд. :-( Это объясняется тем, что постгрес при поиске по индексу для каждого подходящего index_tuple выбирает соответствующую ему строку из таблицы. В отличие например от оракла, который в том случае если все поля указанные в "select ..." запроса присутствуют в индексе, в таблицу вообще не заглядывает, а берет данные из самого индекса. Думаю, что бороться с этим в постгресе можно либо с помощью команды "cluster"; либо делать partition tables записывая данные с часто встречающимися id в одну таблицу, а с редко встречающимися - в другую.
26 авг 04, 20:35    [912394]     Ответить | Цитировать Сообщить модератору
 Re: Выбор БД для быстрой работы с большими таблицами.  [new]
LeXa NalBat
Member

Откуда: Москва
Сообщений: 2892
1)
автор
CREATE INDEX traffic_history_idx2 ON traffic_history (ip, id); //нужен для JOIN'а
Здесь наверное опечатка. Вы имели в виду индекс traffic_history (ip, blob_ref)?


2)
Чем в этом запросе индекс blobs (ts_stop, id) может быть полезнее индекса blobs (ts_stop)? Ведь по первому аргументу ts_stop ограничение на интервал, а не на равенство.


3)
Какие проблемы с этим запросом в постгресе? :-) Какой план выполнения или скорость вы хотели бы иметь?


4)
Я не силен в теории, но чем-то эта задача мне напоминает OLAP. Может быть вам посмотреть в эту сторону? (Не обязательно на MS-OLAP или оракловый, я не так давно сваял что-то похожее на коленке на постгресе - в процессе массированной загрузки данных вычисляются всевозможные агрегаты, которые впоследствии используются при показе статистики.)
26 авг 04, 20:51    [912420]     Ответить | Цитировать Сообщить модератору
 Re: Выбор БД для быстрой работы с большими таблицами.  [new]
Зл0й
Member

Откуда: Северная Калифорния
Сообщений: 686
ИМХО здесь надо не заморачиваться переходом на другую СУБД, а начинать с тюнинга той которая уже есть. И только если есть твердая уверенность в том что "тормоз" именно данная СУБД (а не конфигурация дисковой подсистемы, железяки, конфигурация СУБД, криворукость разработчика...)

Если есть бюждет - можно поставить серьезную железяку с Ораклом. Для trickle feed (т.е. когда происходит одновременное чтение и запись) самое оно. Если же данные загружать по ночам, тогда Teradata. Но это уже совсем для взрослых, с бюждетом где-то в районе 200 тыщ убитых енотов. Плюс Teradata блокирует на уровне таблицы, а если использовать loader который не блокирует данные, тогда емеем dirty read. Правда, при условии что база insert-only это решается - просто смотрим только на данные которым от роду более 15 минут.
27 авг 04, 03:23    [912629]     Ответить | Цитировать Сообщить модератору
 Re: Выбор БД для быстрой работы с большими таблицами.  [new]
Константин Лисянский
Member

Откуда: Москва
Сообщений: 902
ИМХО здесь надо не заморачиваться переходом на другую СУБД, а начинать с тюнинга той которая уже есть.


Согласен, надо ещё понимать, насколько та, которая есть, сможет работать и в будущем при росте объёма данных.

Если есть бюждет - можно поставить серьезную железяку с Ораклом. Для trickle feed (т.е. когда происходит одновременное чтение и запись) самое оно. Если же данные загружать по ночам, тогда Teradata.


В случае Teradata не обязательно по ночам. Можно и в on-line. Есть соответствующие средства.

Но это уже совсем для взрослых, с бюждетом где-то в районе 200 тыщ убитых енотов


Не обязательно. Если железяка уже есть, то можно обойтись только софтом.
Если речь идёт о серьёзной железяке, то для Оракла она тоже немало будет стоить.

Плюс Teradata блокирует на уровне таблицы, а если использовать loader который не блокирует данные, тогда емеем dirty read.


Это не совсем так. Самый низкий уровень блокировки в Терадате - это ROW HASH.
Да, и как Вы в Оракле сможете обойтись без dirty read? Ведь, пока идёт загрузка, транзакция не закрыта, так, что если Вы что-то сможете прочитать во время загрузки, то Вы не имеете права этому верить - это тоже dirty read. Или я не прав?


С уважением,
Константин Лисянский
http://lissianski.narod.ru
27 авг 04, 11:38    [913459]     Ответить | Цитировать Сообщить модератору
 Re: Выбор БД для быстрой работы с большими таблицами.  [new]
gardenman
Member

Откуда: С-Петербург
Сообщений: 2347
Посмотрите пожалуйста вот эту статью:
MDC
27 авг 04, 11:50    [913522]     Ответить | Цитировать Сообщить модератору
 Re: Выбор БД для быстрой работы с большими таблицами.  [new]
gardenman
Member

Откуда: С-Петербург
Сообщений: 2347
еще информацию по MDC можно найти здесь здесь там есть tutorial - написана очень понятным популярным языком.
27 авг 04, 11:59    [913565]     Ответить | Цитировать Сообщить модератору
 Re: Выбор БД для быстрой работы с большими таблицами.  [new]
Зл0й
Member

Откуда: Северная Калифорния
Сообщений: 686
Oracle - версионник. Соотвественно пока объемов Rollback Segment'ов хватает, вы можете одновременно писать и читать одну и ту же таблицу в разных сессиях. Без взаимных блокировок и грязного чтения. Этим Оракл выгодно отличается от Teradata и DB2. Поясню примером

<момент времени>. <событие>

T1. Сессия S1 начала читать здоровенную таблицу my_table
T2. Сессия S2 изменила запись номер 123,456,789 еще не прочитанную сессией S1
Т3. Сессия S1 дошла до записи номер 123,456,789 и обнаружила что запись была изменена в момент времени Т2 > T1. Поскольку Оракл обязан выдать данные по состоянию на Т1, он залезет в rollback segment и разыщет там старое значение записи номер 123,456,789 на момент времени Т1. Это значение и будет использовано запросом из сессии S1.

Естественно "панацеи на бывает". Допустим в момент времени Т2 сессия S2 не только изменила запись, но и прибила ее с помощью commit. Тогда тогда место в rollback segment где лежит старое значение записи номер 123,456,789 на момент времени Т1, будет помечено как "available for reuse". Если в момент времени Т3 сессия S1 обнаружит что нужные ей данные отсутствуют (поверх них уже записали что-то другое) то она выдаст ошибку ORA-1555 Snapshot too old. Поэтому в системе где загрузка происходит одновременно с запросами требуется иметь rollback segment'ы соотвествующего размера.

В СУБД-блокировщиках (Teradata, DB2, Sybase, Informix) нам приходится выбирать между грязным чтением и ожиданием на блокировке. Выбор из "двух зол" и весьма неприятный. Я уж лучше прикуплю отдельный дисковый массив и целенаправленно забабахаю туда свои rollback segment'ы. Ибо железо дешевеет с каждым днем.

У Teradata есть свои плюсы - она умеет быстро просматривать всю таблицу. Делается это вот как: у таблицы есть primary index. Это - колонка группа колонок могущая совпадать (или не совпадать) с первичным ключем. По этой колонке или группе считается хэш-функция и данные раскидываются по узлам нашей MPP-системы в соответствии со значением хэш-функции. Соотвественно полный просмотр таблицы полностью распараллеливается по всему (памяти, диску, процессору). Это - идеология "грубой силы". И она неплохо работает, особенно в организациях где народ в СУБД особо не шарит, и не занимается оптимизацией хранения вручную, то есть всякими index-organized tables, colocated tables (в Оракле - Clusters), materialized views, индексами, итд. итп.
27 авг 04, 22:09    [915712]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Сравнение СУБД Ответить