ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / опять оптимизация запроса
25 сообщений из 43, страница 1 из 2
  1  все
опять оптимизация запроса
    #34239188
akimovp
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
akimovp
Гость
Здравствуйте Господа. Помогите чайнику оптимизировать запрос в PostgreSQL 8.1. Задача у меня усложняется тем что нет полной возможности менять текст приложения (т.е. сам SQL запрос) а хотелось путем настройки параметров базы и создания необходимых индексов увеличить производительность конкретного запроса. Почитал много веток по оптимизации в этом форуме помучился с индексами и кластеризацией таблиц - а результата практически нет.

Есть 2 таблицы (привожу их в сокращенном виде)
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
CREATE TABLE payment
(
  id int8 NOT NULL,
  sum numeric( 10 , 2 ),
  datetime timestamptz,
  debit varchar( 255 ),
  credit varchar( 255 ),
  terminalnumber int4,
  provider varchar( 255 ),
  CONSTRAINT payment_pkey PRIMARY KEY (id),
  CONSTRAINT fkd11c32062c10c1b8 FOREIGN KEY (terminalnumber)
      REFERENCES terminal (terminal_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fkd11c320636173a84 FOREIGN KEY (provider)
      REFERENCES provider (name) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
)
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
CREATE TABLE dealerpayment
(
  paymentid int8 NOT NULL,
  agent varchar( 255 ),
  dealersum numeric( 10 , 2 ),
  idx int4 NOT NULL,
  CONSTRAINT dealerpayment_pkey PRIMARY KEY (paymentid, idx),
  CONSTRAINT fk404a35cd9dbac004 FOREIGN KEY (paymentid)
      REFERENCES payment (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
в первой примерно 100т. записей в месяц, во второй на каждую запись в первой от 2 до 5 записей.
Есть запрос от приложения который группирует платежи по дням за определенный период (например за месяц) - запрос тоже в обрезанном виде (хотя он и так 20 секунд вылолняется)

Код: 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.
38.
39.
select
	extract(year 
    from
        payment0_.datetime) as col_1_0_,
        extract(month 
    from
        payment0_.datetime) as col_2_0_,
        extract(day 
    from
        payment0_.datetime) 
    from
        payment payment0_, 
        dealerpayment comissions1_ 
    where
        (
            payment0_.datetime > '2006-12-01' and payment0_.datetime < '2006-12-31'
        )
        and comissions1_.paymentid=payment0_.id  
        and comissions1_.agent='xxx'

    group by
        extract(year 
    from
        payment0_.datetime) ,
        extract(month 
    from
        payment0_.datetime) ,
        extract(day 
    from
        payment0_.datetime) 
    order by
        extract(year 
    from
        payment0_.datetime),
        extract(month 
    from
        payment0_.datetime),
        extract(day 
    from
        payment0_.datetime)
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
Group  (cost= 54983 . 64 .. 56523 . 77  rows= 88007  width= 8 ) (actual time= 19998 . 108 .. 20629 . 179  rows= 30  loops= 1 )
   ->  Sort  (cost= 54983 . 64 .. 55203 . 66  rows= 88007  width= 8 ) (actual time= 19998 . 098 .. 20316 . 174  rows= 89988  loops= 1 )
         Sort Key: date_part('year'::text, payment0_.datetime), date_part('month'::text, payment0_.datetime), date_part('day'::text, payment0_.datetime)
         ->  Merge Join  (cost= 12050 . 87 .. 47755 . 92  rows= 88007  width= 8 ) (actual time= 13587 . 315 .. 18831 . 487  rows= 89988  loops= 1 )
               Merge Cond: ("outer".paymentid = "inner".id)
               ->  Index Scan using dealerpayment_pid on dealerpayment comissions1_  (cost= 0 . 00 .. 32935 . 51  rows= 305065  width= 8 ) (actual time= 18 . 016 .. 13866 . 880  rows= 288160  loops= 1 )
                     Filter: ((agent)::text = 'pinpay'::text)
               ->  Sort  (cost= 12050 . 87 .. 12284 . 27  rows= 93360  width= 16 ) (actual time= 2429 . 535 .. 2780 . 620  rows= 90645  loops= 1 )
                     Sort Key: payment0_.id
                    ->  Index Scan using payment_datetime on payment payment0_  (cost= 0 . 00 .. 4343 . 76  rows= 93360  width= 16 ) (actual time= 95 . 541 .. 1846 . 492  rows= 90645  loops= 1 )
                           Index Cond: ((datetime > '2006-12-01 00:00:00+06'::timestamp with time zone) AND (datetime < '2006-12-31 00:00:00+06'::timestamp with time zone))

Total runtime:  20696 . 255  ms


индексы разные пробовал создавать - в delarpayment и по agent и по paymentid и совместный и кластерный по paymentid, делал отдельные поля year, month, day в таблице payment -
Большого результата нет.
Postgres shared_buffers = 3000 (с большим значением почему-то PG не запускается ),temp_buffers = 3000, work_mem = 4096, effective_cache_size = 6000.
Машина вроде не сильно слабая - P4 3200 HT , 1Гиг RAM , SATA RAID, Linux 2.6 kernel.
Сейчас планируется увеличение объема записей в таблицах и я с ужасом представляю что будет когда одновременно много пользвателей запустят этот отчет.
На Windows при примерно тех же условиях результат чуть лучше, но все равно не впечатляет.
...
Рейтинг: 0 / 0
06.01.2007, 09:50
| Ответить | Цитировать | Написать  
опять оптимизация запроса
    #34241401
Jelis
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Jelis
Гость
Ну по меньшей мере напрашиваються индексы по

payment0_.datetime
comissions1_.paymentid
comissions1_.agent='xxx'
extract(year from payment0_.datetime)
extract(month from payment0_.datetime)
extract(day from payment0_.datetime)

Потом можно рассмотреть их комбинации (типа составные индексы)
Есил в WHERE тут надо просто взять за какойто месац то с такими индексами можно писать
Код: plaintext
1.
 WHERE extract(year from payment0_.datetime) =  2007  AND extract(month from payment0_.datetime)= 1

Так как extract возвращает double precision можно еще и в индексах, и в запросе приводить к integer.
И не забудьте стделать vacuum analyze

P.S. Ну и, чисто отвлеченно, выходит так, что у вас тут нарушение 1НФ, потому как части даты вам нужны отдельно. Правда это еще спорно. И зачем вообще группировать и сортировать отдельно по году, месяцу, дню? Почему бы не сделать поле просто date и не группировать/сортировать по нему одному, а выводить (если это реально надо) части отдельно?
...
Рейтинг: 0 / 0
08.01.2007, 18:20
| Ответить | Цитировать | Написать  
опять оптимизация запроса
    #34241517
akimovp
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
akimovp
Гость
Ну по меньшей мере напрашиваються индексы по
payment0_.datetime

Этот есть.


comissions1_.paymentid

Этот создавал , толку нет.


comissions1_.agent='xxx'

этот есть



extract(year from payment0_.datetime)
extract(month from payment0_.datetime)
extract(day from payment0_.datetime)

От этих индексов толку нет. Я делал отдельные поля year, month, day и исключал extract - не помогает. А вообще такой индекс не всегда создается я даже не понимаю почему - иногда выдает ошибку - functions in index expression must be marked IMMUTABLE



Потом можно рассмотреть их комбинации (типа составные индексы)

делал составной индекс по comissions1_.agent и comissions1_.paymentid - по ровну.



Есил в WHERE тут надо просто взять за какойто месац то с такими индексами можно писать

Так усложнится логика приложения потому что в один отчет может попасть нескольно месяцев и 2 года на стыке .



И не забудьте стделать vacuum analyze

каждый раз делаю



P.S. Ну и, чисто отвлеченно, выходит так, что у вас тут нарушение 1НФ, потому как части даты вам нужны отдельно. Правда это еще спорно. И зачем вообще группировать и сортировать отдельно по году, месяцу, дню? Почему бы не сделать поле просто date и не группировать/сортировать по нему одному, а выводить (если это реально надо) части отдельно?
Я же говорю что делал эти поля - выигрышь в быстродействии минимальный - основное время я так понял уходит на join таблиц payment и dealerpayment а не на работу с датами.
...
Рейтинг: 0 / 0
08.01.2007, 21:05
| Ответить | Цитировать | Написать  
опять оптимизация запроса
    #34245033
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
akimovpделал составной индекс по comissions1_.agent и comissions1_.paymentid - по ровну.Приведите пожалуйста explain analyze при наличии индекса по (agent,paymentid).

akimovpосновное время я так понял уходит на join таблиц payment и dealerpayment а не на работу с датами.14 секунд на сканирование таблицы dealerpayment

-> Index Scan using dealerpayment_pid on dealerpayment comissions1_ (cost=0.00..32935.51 rows=305065 width=8) (actual time=18.016..13866.880 rows=288160 loops=1)

akimovpиногда выдает ошибку - functions in index expression must be marked IMMUTABLEтолько по immutable-функциям можно строить индексы. см доку: CREATE INDEX CREATE FUNCTION
...
Рейтинг: 0 / 0
10.01.2007, 11:38
| Ответить | Цитировать | Написать  
опять оптимизация запроса
    #34246898
akimovp
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
akimovp
Гость
LeXa NalBat akimovpделал составной индекс по comissions1_.agent и comissions1_.paymentid - по ровну.Приведите пожалуйста explain analyze при наличии индекса по (agent,paymentid).

akimovpосновное время я так понял уходит на join таблиц payment и dealerpayment а не на работу с датами.14 секунд на сканирование таблицы dealerpayment

-> Index Scan using dealerpayment_pid on dealerpayment comissions1_ (cost=0.00..32935.51 rows=305065 width=8) (actual time=18.016..13866.880 rows=288160 loops=1)

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Group  (cost= 39862 . 54 .. 40728 . 10  rows= 49461  width= 8 ) (actual time= 22306 . 093 .. 22436 . 817  rows= 20  loops= 1 )
  ->  Sort  (cost= 39862 . 54 .. 39986 . 19  rows= 49461  width= 8 ) (actual time= 22306 . 083 .. 22373 . 895  rows= 52032  loops= 1 )
        Sort Key: date_part('year'::text, payment0_.datetime), date_part('month'::text, payment0_.datetime), date_part('day'::text, payment0_.datetime)
        ->  Hash Join  (cost= 2872 . 43 .. 35500 . 80  rows= 49461  width= 8 ) (actual time= 17398 . 174 .. 21930 . 065  rows= 52032  loops= 1 )
              Hash Cond: ("outer".paymentid = "inner".id)
              ->  Seq Scan on dealerpayment comissions1_  (cost= 0 . 00 .. 24608 . 53  rows= 251616  width= 8 ) (actual time= 14 . 059 .. 18613 . 972  rows= 250203  loops= 1 )
                    Filter: ((agent)::text = 'pinpay'::text)
              ->  Hash  (cost= 2454 . 78 .. 2454 . 78  rows= 49861  width= 16 ) (actual time= 1165 . 378 .. 1165 . 378  rows= 52418  loops= 1 )
                    ->  Index Scan using payment_datetime on payment payment0_  (cost= 0 . 00 .. 2454 . 78  rows= 49861  width= 16 ) (actual time= 0 . 195 .. 1045 . 582  rows= 52418  loops= 1 )
                          Index Cond: ((datetime > '2006-12-01 00:00:00+06'::timestamp with time zone) AND (datetime < '2006-12-31 00:00:00+06'::timestamp with time zone))
Total runtime:  22438 . 428  ms


akimovpиногда выдает ошибку - functions in index expression must be marked IMMUTABLE

только по immutable-функциям можно строить индексы. см доку: CREATE INDEX CREATE FUNCTION
Теперь разобрался. по timestamp with timezome extract получается не IMMUTABLE а по простому
timestamp IMMUTABLE. Поэтому и сразу не понял почему то создается индекс extract то нет.
...
Рейтинг: 0 / 0
10.01.2007, 20:13
| Ответить | Цитировать | Написать  
опять оптимизация запроса
    #34246922
akimovp
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
akimovp
Гость
И еще. Если hash join отключить получается все гораздо веселей но индекс этот все равно не используется.
Как же заставить одновременно и по dealerpayment.agent и по delerpayment.paymentid использовать индексы?
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
Group  (cost= 65527 . 93 .. 66393 . 50  rows= 49461  width= 8 ) (actual time= 9344 . 778 .. 9650 . 996  rows= 20  loops= 1 )
  ->  Sort  (cost= 65527 . 93 .. 65651 . 58  rows= 49461  width= 8 ) (actual time= 9344 . 770 .. 9507 . 629  rows= 52032  loops= 1 )
        Sort Key: date_part('year'::text, payment0_.datetime), date_part('month'::text, payment0_.datetime), date_part('day'::text, payment0_.datetime)
        ->  Merge Join  (cost= 58793 . 64 .. 61166 . 19  rows= 49461  width= 8 ) (actual time= 6866 . 263 .. 8462 . 611  rows= 52032  loops= 1 )
              Merge Cond: ("outer".id = "inner".paymentid)
              ->  Sort  (cost= 6969 . 07 .. 7093 . 72  rows= 49861  width= 16 ) (actual time= 924 . 036 .. 1121 . 940  rows= 52418  loops= 1 )
                    Sort Key: payment0_.id
                    ->  Index Scan using payment_datetime on payment payment0_  (cost= 0 . 00 .. 2454 . 78  rows= 49861  width= 16 ) (actual time= 0 . 116 .. 355 . 278  rows= 52418  loops= 1 )
                          Index Cond: ((datetime > '2006-12-01 00:00:00+06'::timestamp with time zone) AND (datetime < '2006-12-31 00:00:00+06'::timestamp with time zone))
              ->  Sort  (cost= 51824 . 57 .. 52453 . 61  rows= 251616  width= 8 ) (actual time= 4898 . 755 .. 5686 . 855  rows= 250203  loops= 1 )
                    Sort Key: comissions1_.paymentid
                    ->  Seq Scan on dealerpayment comissions1_  (cost= 0 . 00 .. 24608 . 53  rows= 251616  width= 8 ) (actual time= 0 . 063 .. 2676 . 469  rows= 250203  loops= 1 )
                          Filter: ((agent)::text = 'pinpay'::text)
Total runtime:  9660 . 342  ms
...
Рейтинг: 0 / 0
10.01.2007, 20:23
| Ответить | Цитировать | Написать  
опять оптимизация запроса
    #34247977
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
akimovpиндекс этот все равно не используется. Как же заставить одновременно и по dealerpayment.agent и по delerpayment.paymentid использовать индексы?Должно работать.
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
create table dealerpayment
(
  paymentid int8 not null,
  agent varchar( 255 )
);
create index dealerpayment_agent_paymentid on dealerpayment ( agent, paymentid );

explain select * from dealerpayment where agent='foo' order by paymentid;

Index Scan using dealerpayment_agent_paymentid on dealerpayment  (cost= 0 . 00 .. 4 . 66  rows= 2  width= 153 )
  Index Cond: ((agent)::text = 'foo'::text)
Попобуйте попинать как-нибудь, set enable seqscan to off,..

akimovpИ еще. Если hash join отключить получается все гораздо веселей

-> Seq Scan on dealerpayment comissions1_ (cost=0.00..24608.53 rows=251616 width=8) (actual time=14.059..18613.972 rows=250203 loops=1)
Filter: ((agent)::text = 'pinpay'::text)

-> Seq Scan on dealerpayment comissions1_ (cost=0.00..24608.53 rows=251616 width=8) (actual time=0.063..2676.469 rows=250203 loops=1)
Filter: ((agent)::text = 'pinpay'::text)Нет. Видимо это ошибка тестирования. Обратите внимание, одинаковые seqscan-ы (совпадают предположения планировщика, actual rows) выполняются разное время - 19 и 3 секунды. Возможно второй запрос выполнялся, когда ось/постгрес закэшировали данные с жесткого диска в память. При тестировании можно выполнять два одинаковых запроса подряд и смотреть на тайминги второго. Или шаманством очищать кэш в памяти.
...
Рейтинг: 0 / 0
11.01.2007, 11:28
| Ответить | Цитировать | Написать  
опять оптимизация запроса
    #34248423
Funny_Falcon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Funny_Falcon
Участник
Попробуй. Вряд ли намного быстрее, но всё же попробуй
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
select
	min(extract(year from payment0_.datetime)) as col_1_0_,
        min(extract(month from payment0_.datetime)) as col_2_0_,
        min(extract(day from payment0_.datetime)) as col_3_0_
    from
        payment payment0_, 
        dealerpayment comissions1_ 
    where
        (
            payment0_.datetime > '2006-12-01' and payment0_.datetime < '2006-12-31'
        )
        and comissions1_.paymentid=payment0_.id  
        and comissions1_.agent='xxx'
    group by date_trunc('day', payment0_.datetime)
    order by date_trunc('day', payment0_.datetime)
...
Рейтинг: 0 / 0
11.01.2007, 13:06
| Ответить | Цитировать | Написать  
опять оптимизация запроса
    #34248478
Funny_Falcon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Funny_Falcon
Участник
LeXa NalBat правильно говорит: нужен составной индекс по (agent,paymentid) или (paymentid, agent).
...
Рейтинг: 0 / 0
11.01.2007, 13:18
| Ответить | Цитировать | Написать  
опять оптимизация запроса
    #34250199
akimovp
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
akimovp
Гость
LeXa NalBatcreate index dealerpayment_agent_paymentid on dealerpayment ( agent, paymentid );

Такой индекс pg начинает использовать (хотя и непонятно почему мой первоначальный не нравился по тем же полям, может конечно я прогнал в прошлый раз).
Но результат только немного лучше первоначального:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
Group  (cost= 81416 . 47 .. 82982 . 42  rows= 89483  width= 8 ) (actual time= 18032 . 817 .. 18629 . 497  rows= 30  loops= 1 )
  ->  Sort  (cost= 81416 . 47 .. 81640 . 17  rows= 89483  width= 8 ) (actual time= 18032 . 807 .. 18333 . 398  rows= 89988  loops= 1 )
        Sort Key: date_part('year'::text, payment0_.datetime), date_part('month'::text, payment0_.datetime), date_part('day'::text, payment0_.datetime)
        ->  Merge Join  (cost= 70405 . 63 .. 74056 . 79  rows= 89483  width= 8 ) (actual time= 15148 . 099 .. 17009 . 866  rows= 89988  loops= 1 )
              Merge Cond: ("outer".id = "inner".paymentid)
              ->  Sort  (cost= 11760 . 06 .. 11988 . 21  rows= 91260  width= 16 ) (actual time= 1809 . 229 .. 2177 . 636  rows= 90622  loops= 1 )
                    Sort Key: payment0_.id
                    ->  Index Scan using payment_datetime on payment payment0_  (cost= 0 . 00 .. 4241 . 29  rows= 91260  width= 16 ) (actual time= 60 . 771 .. 1238 . 758  rows= 90622  loops= 1 )
                          Index Cond: ((datetime > '2006-12-01 00:00:00'::timestamp without time zone) AND (datetime < '2006-12-31 00:00:00'::timestamp without time zone))
              ->  Sort  (cost= 58645 . 57 .. 59460 . 25  rows= 325872  width= 8 ) (actual time= 12108 . 220 .. 13070 . 176  rows= 288189  loops= 1 )
                    Sort Key: comissions1_.paymentid
                    ->  Bitmap Heap Scan on dealerpayment comissions1_  (cost= 2570 . 55 .. 25816 . 95  rows= 325872  width= 8 ) (actual time= 1715 . 778 .. 10016 . 919  rows= 327725  loops= 1 )
                          Recheck Cond: ((agent)::text = 'pinpay'::text)
                          ->  Bitmap Index Scan on dealerpayment_agent_paymentid  (cost= 0 . 00 .. 2570 . 55  rows= 325872  width= 0 ) (actual time= 1679 . 287 .. 1679 . 287  rows= 327725  loops= 1 )
                                Index Cond: ((agent)::text = 'pinpay'::text)
Total runtime:  18648 . 135  ms

Bitmap Heap Scan on dealerpayment comissions1_ - занимает очень много времени.
Похоже что он этот индекс только для фильтра agent='xxx' использует pg , а для comissions1_.paymentid=payment0_.id не использует.
...
Рейтинг: 0 / 0
11.01.2007, 21:45
| Ответить | Цитировать | Написать  
опять оптимизация запроса
    #34250205
akimovp
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
akimovp
Гость
Funny_FalconПопробуй. Вряд ли намного быстрее, но всё же попробуй
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
select
	min(extract(year from payment0_.datetime)) as col_1_0_,
        min(extract(month from payment0_.datetime)) as col_2_0_,
        min(extract(day from payment0_.datetime)) as col_3_0_
    from
        payment payment0_, 
        dealerpayment comissions1_ 
    where
        (
            payment0_.datetime > '2006-12-01' and payment0_.datetime < '2006-12-31'
        )
        and comissions1_.paymentid=payment0_.id  
        and comissions1_.agent='xxx'
    group by date_trunc('day', payment0_.datetime)
    order by date_trunc('day', payment0_.datetime)

Вообще не заметил разницы. Да и не заставишь разработчиков вносить DB зависимые конструкции в SQL. Оригинальный SQL генерит ORM Hibernate.
...
Рейтинг: 0 / 0
11.01.2007, 21:54
| Ответить | Цитировать | Написать  
опять оптимизация запроса
    #34250863
Andrey Daeron
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey Daeron
Участник
автор
Postgres shared_buffers = 3000 (с большим значением почему-то PG не запускается ),temp_buffers = 3000, work_mem = 4096, effective_cache_size = 6000.

Может нужно ему памяти поднять? А то 327725 строк - это немало, а 3000*8Кб=24Мб - маловато-с.
Да и остальные памяти - тоже было бы неплохо поднять.
На счет PG не запускается -дык надо в ОСи разрешить больше шаред памяти использовать (что-то есть в доках, что-то на формуе).
...
Рейтинг: 0 / 0
12.01.2007, 10:44
| Ответить | Цитировать | Написать  
опять оптимизация запроса
    #34250908
Andrey Daeron
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey Daeron
Участник
Мда, прикинул 24Метра - совсем мало, в 8.2 по умолчанию идет 32. Желательно хотя бы до 100-150 метров поднять. Не поможет - попробовать до 300-400. И рабочую память тоже бы где-то до 100 метров.
...
Рейтинг: 0 / 0
12.01.2007, 10:57
| Ответить | Цитировать | Написать  
опять оптимизация запроса
    #34251180
Funny_Falcon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Funny_Falcon
Участник
Andrey DaeronМда, прикинул 24Метра - совсем мало, в 8.2 по умолчанию идет 32. Желательно хотя бы до 100-150 метров поднять. Не поможет - попробовать до 300-400. И рабочую память тоже бы где-то до 100 метров.
Я могу ошибаться, но по-моему рабочую память (work_mem) до 100 метров - это бред. В доке сказано - этот размер может несколько раз выделиться для одного запроса - на каждый sort, join и т.д.
Кто реально замечал разницу между 16MB workmem и 100MB, просветите меня глупого, я сам с такими значениями не игрался.

Но даже если разница есть, по-моему лучше поднять её в отдельном коннекте/запросе (set work_mem = 100000; по-моему).

А общего кэша (shared_buffers) и правда маловато.
...
Рейтинг: 0 / 0
12.01.2007, 12:05
| Ответить | Цитировать | Написать  
опять оптимизация запроса
    #34251258
Andrey Daeron
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey Daeron
Участник
Funny_Falcon
Я могу ошибаться, но по-моему рабочую память (work_mem) до 100 метров - это бред. В доке сказано - этот размер может несколько раз выделиться для одного запроса - на каждый sort, join и т.д.

Код: plaintext
1.
Specifies the amount of memory to be used by internal sort operations and hash tables before switching to temporary disk files.
Т.е. если я правильно понял - то эта цифирка - это тот предел, выше которого все будет кешироваться на винт, но не факт что этот объем _обязательно_ выделяется каждый раз, это скорее граница.

Если для запросов не нужно больше 16Мб - то увеличение этого значения до 100Мб ситуацию не улучшит, но и ухудшить тоже не должно, за то если это значение является "бутылочным горлышком", то его увеличение может показать путь решения.

ЗЫ У меня объем общего кеша = 160МБ для объемов в 1-2 милиона записей и группировке по ним.
...
Рейтинг: 0 / 0
12.01.2007, 12:23
| Ответить | Цитировать | Написать  
опять оптимизация запроса
    #34251352
Funny_Falcon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Funny_Falcon
Участник
Andrey DaeronЕсли для запросов не нужно больше 16Мб - то увеличение этого значения до 100Мб ситуацию не улучшит, но и ухудшить тоже не должно, за то если это значение является "бутылочным горлышком", то его увеличение может показать путь решения.Я верю, что теоретически это верно.
Но хотелось бы узнать, сталкивался ли кто-нить с этим на практике: выиграл ли кто-нибудь от увеличения work_mem до 100Мб.
Если есть такие, поделитесь, ваш опыт будет интересен всем.

Andrey DaeronЗЫ У меня объем общего кеша = 160МБ для объемов в 1-2 милиона записей и группировке по ним.Общего кэша(shared_buffers) или work_mem? У меня общего кэша столько-же. Видимо это тот размер, когда нужные страницы индексов начинают умещаться в кэше.
...
Рейтинг: 0 / 0
12.01.2007, 12:44
| Ответить | Цитировать | Написать  
опять оптимизация запроса
    #34251434
Andrey Daeron
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey Daeron
Участник
Funny_Falcon
Andrey DaeronЗЫ У меня объем общего кеша = 160МБ для объемов в 1-2 милиона записей и группировке по ним.Общего кэша(shared_buffers) или work_mem? У меня общего кэша столько-же. Видимо это тот размер, когда нужные страницы индексов начинают умещаться в кэше.
Общего кеша (shared_buffers). Work_mem у меня вообще был 200МБ.
Попробую поковыряться, поуменьшать для определения минимально-нужных объемов. И, если получится, попробую сравнить по производительности с 8.2.
...
Рейтинг: 0 / 0
12.01.2007, 13:06
| Ответить | Цитировать | Написать  
опять оптимизация запроса
    #34251455
akimovp
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
akimovp
Гость
Первоначальные данные устарели. Я уже разобрался с оганичением в OS.
сейчас уже у меня стоит
shared_buffers = 6000
temp_buffers = 3000
work_mem = 4096
effective_cache_size = 6000

Сегодня ночью попрообую еще увеличить эти значения и привести результат explain analyze.
...
Рейтинг: 0 / 0
12.01.2007, 13:10
| Ответить | Цитировать | Написать  
опять оптимизация запроса
    #34251833
Andrey Daeron
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey Daeron
Участник
akimovpПервоначальные данные устарели. Я уже разобрался с оганичением в OS.
сейчас уже у меня стоит
shared_buffers = 6000
temp_buffers = 3000
work_mem = 4096
effective_cache_size = 6000

Сегодня ночью попрообую еще увеличить эти значения и привести результат explain analyze.
Было бы интерестно посмотреть на время выполнения при:
Код: plaintext
1.
2.
3.
4.
shared_buffers =  20000  (потом,  30000 ,  40000 )
temp_buffers =  3000 
work_mem =  16000  (потом  32000 ,  64000 ,  128000 )
effective_cache_size =  6000
Если на каком-то из вариантов сильно попустит, тогда уже более точные значения подбирать можно.
Сразу максимум тоже ставить не кузяво - можно выпасть в swap, а это быстродействия не добавит :)
...
Рейтинг: 0 / 0
12.01.2007, 14:34
| Ответить | Цитировать | Написать  
опять оптимизация запроса
    #34253051
akimovp
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
akimovp
Гость
Andrey DaeronБыло бы интерестно посмотреть на время выполнения при:
Код: plaintext
1.
2.
3.
4.
shared_buffers =  20000  (потом,  30000 ,  40000 )
temp_buffers =  3000 
work_mem =  16000  (потом  32000 ,  64000 ,  128000 )
effective_cache_size =  6000

Привожу результаты. Проводил изменения всегда на 3 раз, все другие ресурсоемкие процесы загасил.
shared_buffers=3000
work_mem=3000
Total runtime: 5223.187 ms

shared_buffers=10000
work_mem=10000
Total runtime: 5119.872 ms

shared_buffers=40000
work_mem=40000
Total runtime: 4624.605 ms


Зависимость от shared_buffers при постоянном work_mem=10000
shared_buffers=3000
Total runtime: 5094.348 ms

shared_buffers=30000
Total runtime: 4868.673 ms

shared_buffers=60000
Total runtime: 4881.001 ms

Зависимость от work_mem при постоянном shared_buffers=10000

work_mem=3000
Total runtime: 5118.737 ms

work_mem=30000
Total runtime: 4863.079 ms

Нормальная запуск всех необходимых приложений на сервере и запуск с параметрами
shared_buffers=10000
work_mem=10000
Total runtime: 5957.512 ms

В принципе какого-то кардинального влиния shared_buffers work_mem не видно (все в пределах 10%). Оставил их по 10000 и пошел спать.
...
Рейтинг: 0 / 0
12.01.2007, 22:27
| Ответить | Цитировать | Написать  
опять оптимизация запроса
    #34253296
Andrey Daeron
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey Daeron
Участник
akimovp Andrey DaeronБыло бы интерестно посмотреть на время выполнения при:
Код: plaintext
1.
2.
3.
4.
shared_buffers =  20000  (потом,  30000 ,  40000 )
temp_buffers =  3000 
work_mem =  16000  (потом  32000 ,  64000 ,  128000 )
effective_cache_size =  6000

Привожу результаты. Проводил изменения всегда на 3 раз, все другие ресурсоемкие процесы загасил.
shared_buffers=3000
work_mem=3000
Total runtime: 5223.187 ms

shared_buffers=10000
work_mem=10000
Total runtime: 5119.872 ms

shared_buffers=40000
work_mem=40000
Total runtime: 4624.605 ms


Зависимость от shared_buffers при постоянном work_mem=10000
shared_buffers=3000
Total runtime: 5094.348 ms

shared_buffers=30000
Total runtime: 4868.673 ms

shared_buffers=60000
Total runtime: 4881.001 ms

Зависимость от work_mem при постоянном shared_buffers=10000

work_mem=3000
Total runtime: 5118.737 ms

work_mem=30000
Total runtime: 4863.079 ms

Нормальная запуск всех необходимых приложений на сервере и запуск с параметрами
shared_buffers=10000
work_mem=10000
Total runtime: 5957.512 ms

В принципе какого-то кардинального влиния shared_buffers work_mem не видно (все в пределах 10%). Оставил их по 10000 и пошел спать.
...
Рейтинг: 0 / 0
13.01.2007, 11:05
| Ответить | Цитировать | Написать  
опять оптимизация запроса
    #34253356
Andrey Daeron
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey Daeron
Участник
akimovp
В принципе какого-то кардинального влиния shared_buffers work_mem не видно (все в пределах 10%). Оставил их по 10000 и пошел спать.
Ну, на самом деле до 20% :)

Можно еще глянуть на
Код: plaintext
1.
2.
3.
4.
 SELECT count(*) a, extract(month from payment.datetime) 
  FROM payment
  GROUP BY extract(month from payment.datetime) 
  ORDER BY extract(month from payment.datetime)
есть подозрение что там не 100тыков а 300 тыков в месяц.

И заодно:
Код: plaintext
1.
2.
3.
4.
5.
6.
SELECT c2.relname, pg_size_pretty(pg_relation_size(c2.relname)), c2.relpages
    FROM pg_class c, pg_class c2, pg_index i
    WHERE c.relname = 'payment'
        AND c.oid = i.indrelid
        AND c2.oid = i.indexrelid
    ORDER BY c2.relname;

Код: plaintext
1.
2.
3.
4.
5.
6.
SELECT c2.relname, pg_size_pretty(pg_relation_size(c2.relname)), c2.relpages
    FROM pg_class c, pg_class c2, pg_index i
    WHERE c.relname = 'dealerpayment'
        AND c.oid = i.indrelid
        AND c2.oid = i.indexrelid
    ORDER BY c2.relname;

Я бы предложил следующие направления копания дальше:
1. Поиграться с партиционированием (должно сильно помочь). Плюсы - должно круто помочь, минусы - есть малость гемороя.
2. Написать в рассылку Постгреса pgsql-performance@postgresql.org. Плюсы - если там не помогут - нигде не помогут :) Минусы - иногда игнорируют, иногда аглицкого не хвататет для конструктивных диалогов.
3. Поставить 8.2 где-то слева и поигратся на нем - могет быть попустит.
...
Рейтинг: 0 / 0
13.01.2007, 12:23
| Ответить | Цитировать | Написать  
опять оптимизация запроса
    #34254327
akimovp
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
akimovp
Гость
Andrey Daeron
Можно еще глянуть на
Код: plaintext
1.
2.
3.
4.
 SELECT count(*) a, extract(month from payment.datetime) 
  FROM payment
  GROUP BY extract(month from payment.datetime) 
  ORDER BY extract(month from payment.datetime)
есть подозрение что там не 100тыков а 300 тыков в месяц.

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
 count | date_part
-------+----------- 
 52353  |          1     
 35  |          4    
 509  |          5   
 1238  |          6   
 4560  |          7  
 24698  |          8  
 36230  |          9  
 58604  |         10  
 75361  |         11  
 96472  |         12 
( 10  rows)


И заодно:
Код: plaintext
1.
2.
3.
4.
5.
6.
SELECT c2.relname, pg_size_pretty(pg_relation_size(c2.relname)), c2.relpages
    FROM pg_class c, pg_class c2, pg_index i
    WHERE c.relname = 'payment'
        AND c.oid = i.indrelid
        AND c2.oid = i.indexrelid
    ORDER BY c2.relname;


Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
      relname            | pg_size_pretty | relpages
-------------------------------+----------------+---------- 
payment_datetime              |  9032  kB        |       941  
payment_datetime_day          |  8928  kB        |       941  
payment_datetime_month        |  8928  kB        |       941  
payment_datetime_year         |  8952  kB        |       941  
payment_pkey                  |  9056  kB        |       941  
payment_terminalpaymentid_key |  12  MB          |      1126  
payment_to_credit             |  12  MB          |      1323  
payment_to_debit              |  10  MB          |      1129  
payment_to_provider           |  9904  kB        |      1043  
paymenttoterminal             |  7480  kB        |       752 
( 10  rows)


Код: plaintext
1.
2.
3.
4.
5.
6.
SELECT c2.relname, pg_size_pretty(pg_relation_size(c2.relname)), c2.relpages
    FROM pg_class c, pg_class c2, pg_index i
    WHERE c.relname = 'dealerpayment'
        AND c.oid = i.indrelid
        AND c2.oid = i.indexrelid
    ORDER BY c2.relname;


Код: plaintext
1.
2.
3.
4.
    relname       | pg_size_pretty | relpages
---------------------+----------------+---------- 
dealerpayment_agent |  27  MB          |      3301  
dealerpayment_pid   |  22  MB          |      2760  
dealerpayment_pkey  |  27  MB          |      3313


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

Неохота иметь этот геморой. Тем более при этом придется менять код приложения - а это совсем тяжело и еще будет DB зависимые конструкции в нем.

2. Написать в рассылку Постгреса pgsql-performance@postgresql.org. Плюсы - если там не помогут - нигде не помогут :) Минусы - иногда игнорируют, иногда аглицкого не хвататет для конструктивных диалогов.

Да написание понятных текстов на английском занимает у меня много времени. Я думаю будет лучше потратить это время на изучение доков по Oracle, установка его, перенос базы на него , тестирование - наверняка там таких проблем меньше будет.


3. Поставить 8.2 где-то слева и поигратся на нем - могет быть попустит.
На Винде локально стоит 8.2, особого ускорения не наблюдаю по сравнения с 8.1

В любом случае спасибо за помощ.
...
Рейтинг: 0 / 0
14.01.2007, 19:18
| Ответить | Цитировать | Написать  
опять оптимизация запроса
    #34256988
Andrey Daeron
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey Daeron
Участник
akimovp
Да написание понятных текстов на английском занимает у меня много времени. Я думаю будет лучше потратить это время на изучение доков по Oracle, установка его, перенос базы на него , тестирование - наверняка там таких проблем меньше будет.

В любом случае спасибо за помощ.
Было бы очень интересно посмотреть на результат такого перехода, на сколько стало лучше и за счет чего.

Впрочем на ответ из мейллиста Постгреса тоже.
...
Рейтинг: 0 / 0
15.01.2007, 19:53
| Ответить | Цитировать | Написать  
опять оптимизация запроса
    #34257115
akimovp
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
akimovp
Гость
Andrey DaeronБыло бы очень интересно посмотреть на результат такого перехода, на сколько стало лучше и за счет чего.
Обязательно напишу о результате такого перехода, только вот быстро ли он получитсся - не знаю.
...
Рейтинг: 0 / 0
15.01.2007, 21:27
| Ответить | Цитировать | Написать  
25 сообщений из 43, страница 1 из 2
  1  все
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / опять оптимизация запроса
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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