Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / тормоза непонятно из за чего / 5 сообщений из 5, страница 1 из 1
05.07.2005, 16:33
    #33150592
user1
user1
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
тормоза непонятно из за чего
Привет всем!

Скриптом автоматически генерируется страшный запрос, который выполняется слишком долго. Вопрос: это нормальный показатель или можно что-то улучшить?

Ниже приведен запрос, определения таблиц и explain запроса. Может, кому-нибудь не лень будет посмотреть, возможно по explain сразу видно будет слабые места (например какой-нибудь индекс нужно еще создать или буфера там какие-нибудь увеличить...).

Заранее благодарю за ответ.

Время выполнения запроса: ~75 мин.
Дисковая активность в это время: ~0.7 Mb/s
Индексы созданы для всех полей.
PgSQL версии 7.4.5
Машина: 2x Pentium-2 266 МГц, 128 RAM, HDD SCSI 10K 20G, ОС FreeBSD 5.3

Число записей в таблицах на данный момент:
flows - 620861
flows_counter - 659848

Запрос:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
SELECT sum("in") AS in, sum(out) AS out, flow_kind
FROM (SELECT sum(to_octets) AS in, sum(from_octets) AS out, flow_kind
FROM flows, flows_counter
	WHERE flows.flow_index = flows_counter.flow_index
	AND flows.start_time = flows_counter.start_time
	AND flows.flow_ruleset = flows_counter.flow_ruleset AND (flow_kind =  1 )
	AND (dest_peer_address <<= '172.16.48.241')
	AND end_time = ( SELECT max(end_time)
		FROM flows_counter
		WHERE flow_index = flows.flow_index AND start_time = flows.start_time
		AND flow_ruleset = flows.flow_ruleset AND end_time >= ('2005-6-1')
		AND end_time <= (timestamp '2005-6-30'  + interval '1 day') )
	GROUP BY flow_kind
	UNION ALL
	SELECT sum(from_octets) AS in, sum(to_octets) AS out,
	flow_kind FROM flows, flows_counter
	WHERE flows.flow_index = flows_counter.flow_index
	AND flows.start_time = flows_counter.start_time
	AND flows.flow_ruleset = flows_counter.flow_ruleset AND (flow_kind =  1 )
	AND (source_peer_address <<= '172.16.48.241')
	AND end_time = ( SELECT max(end_time)
		FROM flows_counter
		WHERE flow_index = flows.flow_index
		AND start_time = flows.start_time
		AND flow_ruleset = flows.flow_ruleset
		AND end_time >= ('2005-6-1')
		AND end_time <= (timestamp '2005-6-30'  + interval '1 day') )
	GROUP BY flow_kind) AS subselect
GROUP BY flow_kind;

Таблица flows
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
CREATE TABLE flows
(
  source_peer_address inet NOT NULL,
  dest_peer_address inet NOT NULL,
  source_trans_address int4 NOT NULL,
  dest_trans_address int4 NOT NULL,
  source_trans_type int4 NOT NULL,
  flow_kind int4 NOT NULL,
  flow_index int4 NOT NULL,
  start_time timestamp( 2 ) NOT NULL,
  flow_ruleset int4 NOT NULL,
  CONSTRAINT flows_pkey PRIMARY KEY (flow_index, flow_ruleset, start_time)
)

Таблица flows_counter
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
CREATE TABLE flows_counter
(
  flow_index int4 NOT NULL,
  flow_ruleset int4 NOT NULL,
  start_time timestamp( 2 ) NOT NULL,
  end_time timestamp( 2 ) NOT NULL,
  "time" timestamp( 0 ) NOT NULL,
  to_octets int8 NOT NULL,
  from_octets int8 NOT NULL,
  to_pdus int8 NOT NULL,
  from_pdus int8 NOT NULL,
  CONSTRAINT flows_counter_pkey PRIMARY KEY (flow_index, flow_ruleset, start_time, "time"),
  CONSTRAINT in_flow FOREIGN KEY (flow_index, flow_ruleset, start_time) REFERENCES flows (flow_index, flow_ruleset, start_time) ON UPDATE CASCADE ON DELETE CASCADE
)

EXPLAIN ANALYZE ....
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
HashAggregate  (cost= 581837 . 37 .. 581837 . 38  rows= 2  width= 68 ) (actual time= 4624094 . 632 .. 4624094 . 650  rows= 1  loops= 1 )
   ->  Subquery Scan subselect  (cost= 290918 . 65 .. 581837 . 36  rows= 2  width= 68 ) (actual time= 2265868 . 202 .. 4624059 . 776  rows= 2  loops= 1 )
         ->  Append  (cost= 290918 . 65 .. 581837 . 34  rows= 2  width= 20 ) (actual time= 2265868 . 051 .. 4624059 . 513  rows= 2  loops= 1 )
               ->  Subquery Scan "*SELECT* 1"  (cost= 290918 . 65 .. 290918 . 67  rows= 1  width= 20 ) (actual time= 2265868 . 029 .. 2265868 . 058  rows= 1  loops= 1 )
                     ->  HashAggregate  (cost= 290918 . 65 .. 290918 . 66  rows= 1  width= 20 ) (actual time= 2265863 . 997 .. 2265864 . 005  rows= 1  loops= 1 )
                           ->  Merge Join  (cost= 74152 . 74 .. 290918 . 65  rows= 1  width= 20 ) (actual time= 63926 . 206 .. 2265835 . 039  rows= 52  loops= 1 )
                                 Merge Cond: (("outer".flow_index = "inner".flow_index) AND ("outer".flow_ruleset = "inner".flow_ruleset) AND ("outer".start_time = "inner".start_time))
                                 Join Filter: ("inner".end_time = (subplan))
                                 ->  Index Scan using flows_pkey on flows  (cost= 0 . 00 .. 210044 . 17  rows= 208876  width= 20 ) (actual time= 305 . 284 .. 36690 . 208  rows= 5365  loops= 1 )
                                       Filter: ((flow_kind =  1 ) AND (dest_peer_address <<= '172.16.48.241'::inet))
                                 ->  Sort  (cost= 74152 . 74 .. 75495 . 44  rows= 537079 
width= 40 ) (actual time= 57047 . 149 .. 64682 . 610  rows= 639247  loops= 1 )
                                       Sort Key: flows_counter.flow_index, flows_counter.flow_ruleset, flows_counter.start_time
                                       ->  Seq Scan on flows_counter  (cost= 0 . 00 .. 11689 . 79  rows= 537079  width= 40 ) (actual time= 5 . 206 .. 18183 . 221  rows= 659848  loops= 1 )
                                 SubPlan
                                   ->  Aggregate  (cost= 5 . 14 .. 5 . 14  rows= 1  width= 8 ) (actual time= 378 . 019 .. 378 . 025  rows= 1  loops= 5706 )
                                         ->  Index Scan using flows_counter_end_time_idx on flows_counter  (cost= 0 . 00 .. 5 . 13  rows= 1  width= 8 ) (actual time= 376 . 655 .. 377 . 942  rows= 0  loops= 5706 )
                                               Index Cond: ((end_time >= '2005-06-01 00:00:00'::timestamp without time zone) AND (end_time <= '2005-07-01 00:00:00'::timestamp without time zone))
                                               Filter: ((flow_index = $ 0 ) AND (start_time = $ 1 ) AND (flow_ruleset = $ 2 ))
               ->  Subquery Scan "*SELECT* 2"  (cost= 290918 . 65 .. 290918 . 67  rows= 1  width= 20 ) (actual time= 2358191 . 351 .. 2358191 . 380  rows= 1  loops= 1 )
                     ->  HashAggregate  (cost= 290918 . 65 .. 290918 . 66  rows= 1  width= 20 ) (actual time= 2358157 . 427 .. 2358157 . 437  rows= 1  loops= 1 )
                           ->  Merge Join  (cost= 74152 . 74 .. 290918 . 65  rows= 1  width= 20 ) (actual time= 64008 . 949 .. 2358117 . 805  rows= 53  loops= 1 )
                                 Merge Cond: (("outer".flow_index = "inner".flow_index) AND ("outer".flow_ruleset = "inner".flow_ruleset) AND ("outer".start_time = "inner".start_time))
                                 Join Filter: ("inner".end_time = (subplan))
                                 ->  Index Scan using flows_pkey on flows  (cost= 0 . 00 .. 210044 . 17  rows= 208876  width= 20 ) (actual time= 275 . 993 .. 34295 . 966  rows= 5676  loops= 1 )
                                       Filter: ((flow_kind =  1 ) AND (source_peer_address <<= '172.16.48.241'::inet))
                                 ->  Sort  (cost= 74152 . 74 .. 75495 . 44  rows= 537079 
width= 40 ) (actual time= 54869 . 888 .. 62503 . 805  rows= 639238  loops= 1 )
                                       Sort Key: flows_counter.flow_index, flows_counter.flow_ruleset, flows_counter.start_time
                                       ->  Seq Scan on flows_counter  (cost= 0 . 00 .. 11689 . 79  rows= 537079  width= 40 ) (actual time= 8 . 689 .. 17650 . 425  rows= 659848  loops= 1 )
                                 SubPlan
                                   ->  Aggregate  (cost= 5 . 14 .. 5 . 14  rows= 1  width= 8 ) (actual time= 371 . 902 .. 371 . 908  rows= 1  loops= 6060 )
                                         ->  Index Scan using flows_counter_end_time_idx on flows_counter  (cost= 0 . 00 .. 5 . 13  rows= 1  width= 8 ) (actual time= 370 . 602 .. 371 . 823  rows= 0  loops= 6060 )
                                               Index Cond: ((end_time >= '2005-06-01 00:00:00'::timestamp without time zone) AND (end_time <= '2005-07-01 00:00:00'::timestamp without time zone))
                                               Filter: ((flow_index = $ 0 ) AND (start_time = $ 1 ) AND (flow_ruleset = $ 2 ))
 Total runtime:  4624411 . 715  ms
( 34  rows)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.07.2005, 17:15
    #33150739
mwolf
mwolf
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
тормоза непонятно из за чего
Дружище, тебе не кажется, что сам запрос непонятный?
Если я правильно понимаю, то этот запрос эквивалентен твоему:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
SELECT sum(from_octets)+sum(to_octets) AS in, sum(from_octets)+sum(to_octets) AS out, flow_kind
FROM
  flows, flows_counter
WHERE
  flows.flow_index = flows_counter.flow_index
  AND flows.start_time = flows_counter.start_time
  AND flows.flow_ruleset = flows_counter.flow_ruleset
  AND (flow_kind =  1 )
  AND (source_peer_address <<= '172.16.48.241')
  AND end_time = (SELECT max(end_time)
		FROM flows_counter
		WHERE flow_index = flows.flow_index
		AND start_time = flows.start_time
		AND flow_ruleset = flows.flow_ruleset
		AND end_time >= ('2005-6-1')
		AND end_time <= (timestamp '2005-6-30'  + interval '1 day') )
GROUP BY flow_kind
И это уже будет в 2 раза быстрее. Это навскидку
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.07.2005, 17:27
    #33150787
mwolf
mwolf
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
тормоза непонятно из за чего
Далее.
Строка
-> Index Scan using flows_pkey on flows (cost=0.00..210044.17 rows=208876 width=20) (actual time=305.284..36690.208 rows=5365 loops=1)
Filter: ((flow_kind = 1) AND (dest_peer_address <<= '172.16.48.241'::inet))
наводит на мысль об создании индекса flows(flow_index, flow_ruleset, start_time, flow_kind, dest_peer_address)
-----------------------
А строка
-> Index Scan using flows_counter_end_time_idx on flows_counter (cost=0.00..5.13 rows=1 width=8) (actual time=376.655..377.942 rows=0 loops=5706)
Index Cond: ((end_time >= '2005-06-01 00:00:00'::timestamp without time zone) AND (end_time <= '2005-07-01 00:00:00'::timestamp without time zone))
Filter: ((flow_index = $0) AND (start_time = $1) AND (flow_ruleset = $2))

на индекс flows_counter(flow_index, start_time, flow_ruleset, end_time)
-----------------------
При этом подзапрос
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
SELECT max(end_time)
FROM flows_counter
WHERE flow_index = flows.flow_index
  AND start_time = flows.start_time
  AND flow_ruleset = flows.flow_ruleset
  AND end_time >= ('2005-6-1')
  AND end_time <= (timestamp '2005-6-30'  + interval '1 day'
можно попробовать заменить на
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
SELECT end_time
FROM flows_counter
WHERE flow_index = flows.flow_index
  AND start_time = flows.start_time
  AND flow_ruleset = flows.flow_ruleset
  AND end_time >= ('2005-6-1')
  AND end_time <= (timestamp '2005-6-30'  + interval '1 day'
ORDER BY end_time DESC
LIMIT  1
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.07.2005, 10:55
    #33151650
LeXa NalBat
LeXa NalBat
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
тормоза непонятно из за чего
user1
Код: plaintext
1.
2.
3.
4.
SubPlan
  ->  Aggregate  (cost= 5 . 14 .. 5 . 14  rows= 1  width= 8 ) (actual time= 378 . 019 .. 378 . 025  rows= 1  loops= 5706 )
        ->  Index Scan using flows_counter_end_time_idx on flows_counter  (cost= 0 . 00 .. 5 . 13  rows= 1  width= 8 ) (actual time= 376 . 655 .. 377 . 942  rows= 0  loops= 5706 )
              Index Cond: ((end_time >= '2005-06-01 00:00:00'::timestamp without time zone) AND (end_time <= '2005-07-01 00:00:00'::timestamp without time zone))
              Filter: ((flow_index = $ 0 ) AND (start_time = $ 1 ) AND (flow_ruleset = $ 2 ))
Тормоза видимо из-за этого (и второго, аналогичного этому) куска: 378.025 ms * 5706 ~= 36 min. Попробуйте по совету mwolf заменить max на order by desc limit 1. Или другое более кардинальное решение - избавиться от SubPlan заменой end_time = ( ... subselect ... ) на что-то типа этого:
join ( select flow_index, start_time, flow_ruleset, max(end_time) as max_end_time from flows_counter where end_time >= ('2005-6-1') and end_time <= (timestamp '2005-6-30' + interval '1 day') group by flow_index, start_time, flow_ruleset ) as a using ( flow_index, start_time, flow_ruleset ) where end_time=max_end_time
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.07.2005, 15:12
    #33152423
user1
user1
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
тормоза непонятно из за чего
mwolfДалее.
Строка
-> Index Scan using flows_pkey on flows (cost=0.00..210044.17 rows=208876 width=20) (actual time=305.284..36690.208 rows=5365 loops=1)
Filter: ((flow_kind = 1) AND (dest_peer_address <<= '172.16.48.241'::inet))
наводит на мысль об создании индекса flows(flow_index, flow_ruleset, start_time, flow_kind, dest_peer_address)
-----------------------
А строка
-> Index Scan using flows_counter_end_time_idx on flows_counter (cost=0.00..5.13 rows=1 width=8) (actual time=376.655..377.942 rows=0 loops=5706)
Index Cond: ((end_time >= '2005-06-01 00:00:00'::timestamp without time zone) AND (end_time <= '2005-07-01 00:00:00'::timestamp without time zone))
Filter: ((flow_index = $0) AND (start_time = $1) AND (flow_ruleset = $2))
на индекс flows_counter(flow_index, start_time, flow_ruleset, end_time)



Создал - он их не пытается юзать почему-то.
А вот если заменить операцию <<= на = - начинает юзать индекс flows_dest_peer_address_idx.

mwolf

-----------------------
При этом подзапрос
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
SELECT max(end_time)
FROM flows_counter
WHERE flow_index = flows.flow_index
  AND start_time = flows.start_time
  AND flow_ruleset = flows.flow_ruleset
  AND end_time >= ('2005-6-1')
  AND end_time <= (timestamp '2005-6-30'  + interval '1 day'
можно попробовать заменить на
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
SELECT end_time
FROM flows_counter
WHERE flow_index = flows.flow_index
  AND start_time = flows.start_time
  AND flow_ruleset = flows.flow_ruleset
  AND end_time >= ('2005-6-1')
  AND end_time <= (timestamp '2005-6-30'  + interval '1 day'
ORDER BY end_time DESC
LIMIT  1


Заменил - теперь выполняется примерно за 4 мин.
А вот если еще заменить <<= на = то вообще за 20 сек. Хотя хотелось бы все-таки <<= ...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / тормоза непонятно из за чего / 5 сообщений из 5, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]