ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Быстрый запрос в explain analyze но медленный по факту
65 сообщений из 65, показаны все 3 страниц
  все  
Быстрый запрос в explain analyze но медленный по факту
    #38917216
VerusK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VerusK
Гость
Добрый день. Не могу понять в чем дело, копаюсь уже неделю, наверное. Есть БД в которой сейчас примерно 260 таблиц, есть такие таблицы как статистика, они унаследованы от основной таблицы статистики, т.е. в базе реализовано партицирование по времени и id юзера, получаются таблицы вида stats_y2015_m3_u1(год 2015, месяц 3, юзер 1), в каждой таблице есть CHECK CONSTRAINT, на основной таблице висит триггер на insert который проверяет есть ли такая таблица в которую надо сделать вставку, если нет, то создает ее и делает insert. Обновление примерно 1к строк для одного юзера занимает примерно 160сек(секунд), что как бы ооочень долго, я считаю. explain analyze показывает такие данные - http://explain.depesz.com/s/fnz т.е. выполняется запрос 0.115мсек(миллисекунд), если же я запускаю вставку на сервере, то по монитору вижу, что каждый такой запрос выполняется 0.1-0.2..сек(секунд). Postgresql 9.4.0,пробовал запускать на 9.3 и на 9.2 - результат такой же. Причем заметил, что если база чистая и партицированных таблиц нет, то вставка происходит намного быстрее.
...
Рейтинг: 0 / 0
26.03.2015, 08:10
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38917239
Лопата
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Лопата
Участник
VerusK,
поветрие пошло -- гребсти слова в кучку. И все -- ниочом.

приведите именно тот код, который, по вашему мнению, тормозит. Весь. будем посмотреть.

И да, в обновлении (партицирующих триггерах) предусмотрена возможность смены партиции ? или по месту не двигаетесь ?
...
Рейтинг: 0 / 0
26.03.2015, 08:30
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38917247
VerusK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VerusK
Гость
Код который выполняется:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
UPDATE stats 
SET 
actions=(stats.actions + 0), offer_clicks=(stats.offer_clicks + 0), uniq=(stats.uniq + 0), raw=(stats.raw + 1), 
tb_uniq=(stats.tb_uniq + 0), tb_raw=(stats.tb_raw + 0), leads=(stats.leads + 0), subs=(stats.subs + 0), unsubs=(stats.unsubs + 0), 
rebills=(stats.rebills + 0), sales=(stats.sales + 0), holds=(stats.holds + 0), rejects=(stats.rejects + 0), trashes=(stats.trashes + 0),
user_lead_income=(stats.user_lead_income + 0.0), user_subs_income=(stats.user_subs_income + 0.0), 
user_rebill_income=(stats.user_rebill_income + 0.0), user_sales_income=(stats.user_sales_income + 0.0), 
user_hold_income=(stats.user_hold_income + 0.0), system_lead_income=(stats.system_lead_income + 0.0), 
system_subs_income=(stats.system_subs_income + 0.0), system_rebill_income=(stats.system_rebill_income + 0.0), 
system_sales_income=(stats.system_sales_income + 0.0), system_hold_income=(stats.system_hold_income + 0.0) 
WHERE 
stats.ts_spawn = 1426960800 AND stats.user_id = 1 AND stats.offer_targeting_id = 132 AND stats.offer_id = 123 AND 
stats.traff_type = 4 AND stats.operator_id = 0 AND stats.country_id = 113 AND 
stats.subacc_1 = '' AND stats.subacc_2 = '' AND stats.subacc_3 = '' AND stats.subacc_4 = '' AND stats.utm_source = '' AND
stats.utm_medium = '' AND stats.utm_term = '' AND stats.utm_content = '' AND stats.utm_campaign = '' AND 
stats.prelanding_id = 203 AND stats.landing_id = 0 AND stats.platform_id = 6 AND stats.platform_out_id = 76 AND 
stats.os_id = 9 AND stats.browser_id = 34
...
Рейтинг: 0 / 0
26.03.2015, 08:36
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38917286
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
VerusKКод который выполняется:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
UPDATE stats 
SET 
actions=(stats.actions + 0), offer_clicks=(stats.offer_clicks + 0), uniq=(stats.uniq + 0), raw=(stats.raw + 1), 
tb_uniq=(stats.tb_uniq + 0), tb_raw=(stats.tb_raw + 0), leads=(stats.leads + 0), subs=(stats.subs + 0), unsubs=(stats.unsubs + 0), 
rebills=(stats.rebills + 0), sales=(stats.sales + 0), holds=(stats.holds + 0), rejects=(stats.rejects + 0), trashes=(stats.trashes + 0),
user_lead_income=(stats.user_lead_income + 0.0), user_subs_income=(stats.user_subs_income + 0.0), 
user_rebill_income=(stats.user_rebill_income + 0.0), user_sales_income=(stats.user_sales_income + 0.0), 
user_hold_income=(stats.user_hold_income + 0.0), system_lead_income=(stats.system_lead_income + 0.0), 
system_subs_income=(stats.system_subs_income + 0.0), system_rebill_income=(stats.system_rebill_income + 0.0), 
system_sales_income=(stats.system_sales_income + 0.0), system_hold_income=(stats.system_hold_income + 0.0) 
WHERE 
stats.ts_spawn = 1426960800 AND stats.user_id = 1 AND stats.offer_targeting_id = 132 AND stats.offer_id = 123 AND 
stats.traff_type = 4 AND stats.operator_id = 0 AND stats.country_id = 113 AND 
stats.subacc_1 = '' AND stats.subacc_2 = '' AND stats.subacc_3 = '' AND stats.subacc_4 = '' AND stats.utm_source = '' AND
stats.utm_medium = '' AND stats.utm_term = '' AND stats.utm_content = '' AND stats.utm_campaign = '' AND 
stats.prelanding_id = 203 AND stats.landing_id = 0 AND stats.platform_id = 6 AND stats.platform_out_id = 76 AND 
stats.os_id = 9 AND stats.browser_id = 34



для начала сделайте
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
explain (analyze, costs, buffers) select * from stats 
WHERE 
stats.ts_spawn = 1426960800 AND stats.user_id = 1 AND stats.offer_targeting_id = 132 AND stats.offer_id = 123 AND 
stats.traff_type = 4 AND stats.operator_id = 0 AND stats.country_id = 113 AND 
stats.subacc_1 = '' AND stats.subacc_2 = '' AND stats.subacc_3 = '' AND stats.subacc_4 = '' AND stats.utm_source = '' AND
stats.utm_medium = '' AND stats.utm_term = '' AND stats.utm_content = '' AND stats.utm_campaign = '' AND 
stats.prelanding_id = 203 AND stats.landing_id = 0 AND stats.platform_id = 6 AND stats.platform_out_id = 76 AND 
stats.os_id = 9 AND stats.browser_id = 34



и пришлите его сюда.

--Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
26.03.2015, 09:08
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38917289
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
VerusKДобрый день. Не могу понять в чем дело, копаюсь уже неделю, наверное. Есть БД в которой сейчас примерно 260 таблиц, есть такие таблицы как статистика, они унаследованы от основной таблицы статистики, т.е. в базе реализовано партицирование по времени и id юзера, получаются таблицы вида stats_y2015_m3_u1(год 2015, месяц 3, юзер 1), в каждой таблице есть CHECK CONSTRAINT, на основной таблице висит триггер на insert который проверяет есть ли такая таблица в которую надо сделать вставку, если нет, то создает ее и делает insert. Обновление примерно 1к строк для одного юзера занимает примерно 160сек(секунд), что как бы ооочень долго, я считаю. explain analyze показывает такие данные - http://explain.depesz.com/s/fnz т.е. выполняется запрос 0.115мсек(миллисекунд), если же я запускаю вставку на сервере, то по монитору вижу, что каждый такой запрос выполняется 0.1-0.2..сек(секунд). Postgresql 9.4.0,пробовал запускать на 9.3 и на 9.2 - результат такой же. Причем заметил, что если база чистая и партицированных таблиц нет, то вставка происходит намного быстрее.

А если тоже самое сделать на схеме без партиций (прямо указав с какой таблицей работаете)?
Тут есть 4 теории:

1)время планирования запроса (сколько у вас партиций сейчас)?
2)время на commit транзакции (вы все 1000 строк в одной транзакции обновляете или каждый update в своей транзакции)?
3)сетевые задержки (у вас приложение и база на одном сервере живут или на разных)?
4)диски перегруженные на запись (explain analyze никак время на commit транзакции и запись wal не учитывает).

--Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
26.03.2015, 09:13
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38917297
VerusK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VerusK
Гость
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
"Append  (cost=0.00..7.35 rows=2 width=410) (actual time=0.015..0.015 rows=0 loops=1)"
"  Buffers: shared hit=2"
"  ->  Seq Scan on stats  (cost=0.00..0.00 rows=1 width=664) (actual time=0.001..0.001 rows=0 loops=1)"
"        Filter: ((ts_spawn = 1426960800) AND (user_id = 1) AND (offer_targeting_id = 132) AND (offer_id = 123) AND (traff_type = 4) AND (operator_id = 0) AND (landing_id = 0) AND (country_id = 113) AND ((subacc_1)::text = ''::text) AND ((subacc_2)::text =  (...)"
"  ->  Index Scan using stats_y2015_m3_u1_pk on stats_y2015_m3_u1  (cost=0.28..7.35 rows=1 width=155) (actual time=0.013..0.013 rows=0 loops=1)"
"        Index Cond: ((ts_spawn = 1426960800) AND (user_id = 1) AND (offer_targeting_id = 132) AND (offer_id = 123) AND (traff_type = 4) AND (operator_id = 0) AND (country_id = 113) AND ((subacc_1)::text = ''::text) AND ((subacc_2)::text = ''::text) AND ((s (...)"
"        Buffers: shared hit=2"
"Planning time: 47.748 ms"
"Execution time: 0.164 ms"



Ответ на второй пост:
Если выполнять просто на таблице:
Код: plsql
1.
2.
3.
4.
5.
"Update on stats_y2015_m3_u1 stats  (cost=0.28..7.42 rows=1 width=161) (actual time=0.016..0.016 rows=0 loops=1)"
"  ->  Index Scan using stats_y2015_m3_u1_pk on stats_y2015_m3_u1 stats  (cost=0.28..7.42 rows=1 width=161) (actual time=0.015..0.015 rows=0 loops=1)"
"        Index Cond: ((ts_spawn = 1426960800) AND (user_id = 1) AND (offer_targeting_id = 132) AND (offer_id = 123) AND (traffic_manager_id = 0) AND (traff_type = 4) AND (operator_id = 0) AND (country_id = 113) AND ((subacc_1)::text = ''::text) AND ((subacc (...)"
"Planning time: 0.675 ms"
"Execution time: 0.166 ms"



1. Партиций сейчас именно по таблице stats около 100
2. делаю общий commit, я засекал время его выполнения в коде, выполняется моментально
3. на разных серверах, но я же смотрю время выполнения запроса непосредственно на сервере.
4. если верить pg_activity диски вообще не используются, памяти на сервере 128Гб, вся база весит 35, пробовал отключить fsync - не помогло вообще никак.
...
Рейтинг: 0 / 0
26.03.2015, 09:22
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38917608
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
VerusK
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
"Append  (cost=0.00..7.35 rows=2 width=410) (actual time=0.015..0.015 rows=0 loops=1)"
"  Buffers: shared hit=2"
"  ->  Seq Scan on stats  (cost=0.00..0.00 rows=1 width=664) (actual time=0.001..0.001 rows=0 loops=1)"
"        Filter: ((ts_spawn = 1426960800) AND (user_id = 1) AND (offer_targeting_id = 132) AND (offer_id = 123) AND (traff_type = 4) AND (operator_id = 0) AND (landing_id = 0) AND (country_id = 113) AND ((subacc_1)::text = ''::text) AND ((subacc_2)::text =  (...)"
"  ->  Index Scan using stats_y2015_m3_u1_pk on stats_y2015_m3_u1  (cost=0.28..7.35 rows=1 width=155) (actual time=0.013..0.013 rows=0 loops=1)"
"        Index Cond: ((ts_spawn = 1426960800) AND (user_id = 1) AND (offer_targeting_id = 132) AND (offer_id = 123) AND (traff_type = 4) AND (operator_id = 0) AND (country_id = 113) AND ((subacc_1)::text = ''::text) AND ((subacc_2)::text = ''::text) AND ((s (...)"
"        Buffers: shared hit=2"
"Planning time: 47.748 ms"
"Execution time: 0.164 ms"



Ответ на второй пост:
Если выполнять просто на таблице:
Код: plsql
1.
2.
3.
4.
5.
"Update on stats_y2015_m3_u1 stats  (cost=0.28..7.42 rows=1 width=161) (actual time=0.016..0.016 rows=0 loops=1)"
"  ->  Index Scan using stats_y2015_m3_u1_pk on stats_y2015_m3_u1 stats  (cost=0.28..7.42 rows=1 width=161) (actual time=0.015..0.015 rows=0 loops=1)"
"        Index Cond: ((ts_spawn = 1426960800) AND (user_id = 1) AND (offer_targeting_id = 132) AND (offer_id = 123) AND (traffic_manager_id = 0) AND (traff_type = 4) AND (operator_id = 0) AND (country_id = 113) AND ((subacc_1)::text = ''::text) AND ((subacc (...)"
"Planning time: 0.675 ms"
"Execution time: 0.166 ms"



1. Партиций сейчас именно по таблице stats около 100
2. делаю общий commit, я засекал время его выполнения в коде, выполняется моментально
3. на разных серверах, но я же смотрю время выполнения запроса непосредственно на сервере.
4. если верить pg_activity диски вообще не используются, памяти на сервере 128Гб, вся база весит 35, пробовал отключить fsync - не помогло вообще никак.

1)это в пределах разумного
2)это хорошо
4)тоже очень хорошо...

а вот про 3 - а как собственно вы смотрите время выплнения запроса непосредственно на сервере?
попробуйте включить log_min_duration_statement=0 и прислать то что записалось в лог для одного из ваших проблемных запросов.

--Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
26.03.2015, 13:03
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38917679
VerusK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VerusK
Гость
Maxim Boguk, вот, выдернул из лога только что

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
015-03-26 10:16:04 UTC, LOG:  duration: 150.878 ms  statement: 
UPDATE stats SET actions=(stats.actions + 0), 
offer_clicks=(stats.offer_clicks + 0), uniq=(stats.uniq + 1), raw=(stats.raw + 0), tb_uniq=(stats.tb_uniq +0), 
tb_raw=(stats.tb_raw + 0), leads=(stats.leads + 0), subs=(stats.subs + 0), unsubs=(stats.unsubs + 0), 
rebills=(stats.rebills + 0), sales=(stats.sales + 0), 
holds=(stats.holds + 0), rejects=(stats.rejects + 0), trashes=(stats.trashes + 0), user_lead_income=(stats.user_lead_income + 0.0), 
user_subs_income=(stats.user_subs_income + 0.0), user_rebill_income=(stats.user_rebill_income + 0.0), 
user_sales_income=(stats.user_sales_income + 0.0), user_hold_income=(stats.user_hold_income + 0.0), 
system_lead_income=(stats.system_lead_income + 0.0), system_subs_income=(stats.system_subs_income + 0.0), 
system_rebill_income=(stats.system_rebill_income + 0.0), system_sales_income=(stats.system_sales_income + 0.0), 
system_hold_income=(stats.system_hold_income + 0.0) 
WHERE 
stats.ts_spawn = 1427364000 AND stats.user_id = 1 AND stats.offer_targeting_id = 124 AND stats.offer_id = 135 AND stats.traffic_manager_id = 0 AND 
stats.traff_type = 2 AND stats.operator_id = 0 AND stats.country_id = 192 AND 
stats.subacc_1 = '' AND stats.subacc_2 = '' AND stats.subacc_3 = '' AND 
stats.subacc_4 = '' AND stats.utm_source = '' AND stats.utm_medium = '' AND stats.utm_term = '' AND 
stats.utm_content = '' AND stats.utm_campaign = '' AND stats.prelanding_id = 223 AND stats.landing_id = 345 AND 
stats.platform_id = 10 AND stats.platform_out_id = 15 AND stats.os_id = 5 AND stats.browser_id = 34
...
Рейтинг: 0 / 0
26.03.2015, 13:41
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38917842
Ivan Durak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan Durak
Участник
у тебя для апдейта 1000 строк, вызывается 100 апдейтов???
нельзя пакетно?
...
Рейтинг: 0 / 0
26.03.2015, 15:44
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38917845
Ivan Durak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan Durak
Участник
Ivan Durakу тебя для апдейта 1000 строк, вызывается 100 апдейтов???
нельзя пакетно?
в смысле всю свою статистику инсертишь в лог. А раз в N секунд из лога пакетно группируешь и обновляешь таблицы статистики,
а лог очищаешь
...
Рейтинг: 0 / 0
26.03.2015, 15:47
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38917924
VerusK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VerusK
Гость
Ivan Durak, это 1000 уже сгруппированных уникальных по PK строк, я выполняю update, insert, update на случай если строки не было.
...
Рейтинг: 0 / 0
26.03.2015, 16:38
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38918447
Ivan Durak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan Durak
Участник
VerusKIvan Durak, это 1000 уже сгруппированных уникальных по PK строк, я выполняю update, insert, update на случай если строки не было.
Где же тут твоя тысяча???
Есть есть уже 1000 сгруппированных то апдейт будет такой:

UPDATE stats s set actions = s.actions + coalesce(t.actions, 0), ... etc
FROM TableWith1000Rows t
WHERE s.pk = t.pk;
----------------------
конечно тут даже не 1000, на 1000 врядли будет выйгрыш, тут еще лучше сразу миллионами обновлять - чем больше тем лучше
...
Рейтинг: 0 / 0
27.03.2015, 09:38
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38918456
VerusK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VerusK
Гость
Ivan Durak, этих строк может не быть в базе. Если нет хотя бы одной, значит надо сделать insert, но сфейлится весь запрос. Как сделать upsert?
...
Рейтинг: 0 / 0
27.03.2015, 09:43
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38918603
?Ы
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
?Ы
Гость
VerusK Как сделать upsert?
Код: sql
1.
2.
with upd AS (update ... returning ...)
INSERT INTO .... WHERE NOT EXISTS (SELECT 1 FROM upd WHERE ...)
...
Рейтинг: 0 / 0
27.03.2015, 10:59
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38918762
Ivan Durak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan Durak
Участник
VerusKIvan Durak, этих строк может не быть в базе. Если нет хотя бы одной, значит надо сделать insert, но сфейлится весь запрос. Как сделать upsert?

не сфейлится с чего вдруг? Обновит что есть.

А после апдейта делай инсерт, хочешь как в посте выше,
хочешь отдельно

Insert into stats
select t.*
from TableWith1000Rows t
left join stats s on s.pk=t.pk
where s.pk is null;
...
Рейтинг: 0 / 0
27.03.2015, 11:47
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38921040
VerusK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VerusK
Гость
Ivan Durak, проблема то в том что именно запрос на апдейт одной строки выполняется долго(см. первый пост), тут экономия получится только на больших объемах, но в целом хочется понять почему такая проблема с апдейтом. Из-за большого кол-ва партиций или дело еще в чем-то?
...
Рейтинг: 0 / 0
30.03.2015, 12:52
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38921086
Ivan Durak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan Durak
Участник
VerusKIvan Durak, проблема то в том что именно запрос на апдейт одной строки выполняется долго(см. первый пост), тут экономия получится только на больших объемах, но в целом хочется понять почему такая проблема с апдейтом. Из-за большого кол-ва партиций или дело еще в чем-то?
duration: 150.878 ms
Куда быстрее-то!?
Проблема таки в том что не надо апдейтить по одной!
...
Рейтинг: 0 / 0
30.03.2015, 13:18
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38921268
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
VerusKIvan Durak, проблема то в том что именно запрос на апдейт одной строки выполняется долго(см. первый пост), тут экономия получится только на больших объемах, но в целом хочется понять почему такая проблема с апдейтом. Из-за большого кол-ва партиций или дело еще в чем-то?

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

Попробуйте (временно) поставить deadlock_timeout=100ms + log_lock_waits=on
и посмотрите на логи на предмет не были ли ожиданий локов на ваших update.


--Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
30.03.2015, 15:12
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38921669
VerusK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VerusK
Гость
Maxim Boguk, была такая мысль уже, проверял, локов нет. Думаю остановлюсь на идее делать только insert, буду группировать и каждые n минут сбрасывать в базу записи. Получится больше строк, но думаю это не критично в данный момент.
...
Рейтинг: 0 / 0
30.03.2015, 21:20
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38921672
VerusK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VerusK
Гость
Maxim Boguk, вопрос в догонку - вы тоже считаете, что 150мсек это нормально на такие запросы и на мою структуру? :)
...
Рейтинг: 0 / 0
30.03.2015, 21:25
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38921682
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
VerusK,

Вы делаете UPDATE на мастер-таблицу, так?
А в партиции запрос спускается через триггер, да?
...
Рейтинг: 0 / 0
30.03.2015, 21:39
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38921720
VerusK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VerusK
Гость
vyegorov, в партиции спускается только insert, триггер стоит на insert, а update понимает через check constraint по полям в PK
...
Рейтинг: 0 / 0
30.03.2015, 22:53
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38921740
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
VerusK,

Чудно.
Попробуйте поставить auto_explain и включить его кратковременно.
...
Рейтинг: 0 / 0
30.03.2015, 23:30
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38921793
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
VerusKMaxim Boguk, вопрос в догонку - вы тоже считаете, что 150мсек это нормально на такие запросы и на мою структуру? :)

Ненормально по времени на пару порядков.

--Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
31.03.2015, 02:56
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38921839
VerusK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VerusK
Гость
vyegorov,

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

Код: plsql
1.
2.
3.
4.
5.
6.
7.
2015-03-31 05:02:31 UTC 64255 LOG:  duration: 151.565 ms  plan:
        Query Text: UPDATE stats SET actions=(stats.actions + 0), offer_clicks=(stats.offer_clicks + 0), uniq=(stats.uniq + 1), raw=(stats.raw + 0), tb_uniq=(stats.tb_uniq + 0), tb_raw=(stats.tb_raw + 0), leads=(stats.leads + 0), subs=(stats.subs + 0), unsubs=(stats.unsubs + 0), rebills=(stats.rebills + 0), sales=(stats.sales + 0), holds=(stats.holds + 0), rejects=(stats.rejects + 0), trashes=(stats.trashes + 0), user_lead_income=(stats.user_lead_income + 0.0), user_subs_income=(stats.user_subs_income + 0.0), user_rebill_income=(stats.user_rebill_income + 0.0), user_sales_income=(stats.user_sales_income + 0.0), user_hold_income=(stats.user_hold_income + 0.0), system_lead_income=(stats.system_lead_income + 0.0), system_subs_income=(stats.system_subs_income + 0.0), system_rebill_income=(stats.system_rebill_income + 0.0), system_sales_income=(stats.system_sales_income + 0.0), system_hold_income=(stats.system_hold_income + 0.0) WHERE stats.ts_spawn = 1427778000 AND stats.user_id = 8 AND stats.offer_targeting_id = 124 AND stats.offer_id = 135 AND stats.traffic_manager_id =0 AND stats.traff_type = 2 AND stats.operator_id = 0 AND stats.country_id = 192 AND stats.subacc_1 = '' AND stats.subacc_2 = '' AND stats.subacc_3 = '' AND stats.subacc_4 = '' AND stats.utm_source = '' AND stats.utm_medium = '' AND stats.utm_term = '' AND stats.utm_content = '' AND stats.utm_campaign = '' AND stats.prelanding_id = 222 AND stats.landing_id = 345 AND stats.platform_id = 10 AND stats.platform_out_id = 15 AND stats.os_id = 5 AND stats.browser_id = 34
        Update on stats  (cost=0.00..7.76 rows=2 width=420) (actual time=151.564..151.564 rows=0 loops=1)
          ->  Seq Scan on stats  (cost=0.00..0.07 rows=1 width=670) (actual time=0.000..0.000 rows=0 loops=1)
                Filter: ((ts_spawn = 1427778000) AND (user_id = 8) AND (offer_targeting_id = 124) AND (offer_id = 135) AND (traffic_manager_id = 0) AND (operator_id = 0) AND (traff_type = 2) AND (country_id = 192) AND ((subacc_1)::text = ''::text) AND ((subacc_2)::text = ''::text) AND ((subacc_3)::text = ''::text) AND ((subacc_4)::text = ''::text) AND ((utm_source)::text = ''::text) AND ((utm_medium)::text = ''::text) AND ((utm_term)::text = ''::text) AND ((utm_content)::text = ''::text) AND ((utm_campaign)::text = ''::text) AND (prelanding_id = 222) AND (landing_id = 345) AND (platform_id = 10) AND (platform_out_id = 15) AND (os_id = 5) AND (browser_id = 34))
          ->  Index Scan using stats_y2015_m3_u8_pk on stats_y2015_m3_u8  (cost=0.55..7.69 rows=1 width=171) (actual time=0.218..0.221 rows=1 loops=1)
                Index Cond: ((ts_spawn = 1427778000) AND (user_id = 8) AND (offer_targeting_id = 124) AND (offer_id = 135) AND (traffic_manager_id = 0) AND (traff_type = 2) AND (operator_id = 0) AND (country_id = 192) AND ((subacc_1)::text = ''::text) AND ((subacc_2)::text = ''::text) AND ((subacc_3)::text = ''::text) AND ((subacc_4)::text = ''::text) AND ((utm_source)::text = ''::text) AND ((utm_medium)::text = ''::text) AND ((utm_term)::text = ''::text) AND ((utm_content)::text = ''::text) AND ((utm_campaign)::text = ''::text) AND (prelanding_id = 222) AND (landing_id = 345) AND (platform_id = 10) AND (platform_out_id = 15) AND (os_id = 5) AND (browser_id = 34))



и побыстрее:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
2015-03-31 05:02:31 UTC 64264 LOG:  duration: 62.925 ms  plan:
        Query Text: UPDATE stats SET actions=(stats.actions + 0), offer_clicks=(stats.offer_clicks + 0), uniq=(stats.uniq + 1), raw=(stats.raw + 0), tb_uniq=(stats.tb_uniq + 0), tb_raw=(stats.tb_raw + 0), leads=(stats.leads + 0), subs=(stats.subs + 0), unsubs=(stats.unsubs + 0), rebills=(stats.rebills + 0), sales=(stats.sales + 0), holds=(stats.holds + 0), rejects=(stats.rejects + 0), trashes=(stats.trashes + 0), user_lead_income=(stats.user_lead_income + 0.0), user_subs_income=(stats.user_subs_income + 0.0), user_rebill_income=(stats.user_rebill_income + 0.0), user_sales_income=(stats.user_sales_income + 0.0), user_hold_income=(stats.user_hold_income + 0.0), system_lead_income=(stats.system_lead_income + 0.0), system_subs_income=(stats.system_subs_income + 0.0), system_rebill_income=(stats.system_rebill_income + 0.0), system_sales_income=(stats.system_sales_income + 0.0), system_hold_income=(stats.system_hold_income + 0.0) WHERE stats.ts_spawn = 1427778000 AND stats.user_id = 32 AND stats.offer_targeting_id = 82 AND stats.offer_id = 22 AND stats.traffic_manager_id = 0 AND stats.traff_type = 2 AND stats.operator_id = 0 AND stats.country_id = 192 AND stats.subacc_1 = '' AND stats.subacc_2 = '' AND stats.subacc_3 = '' ANDstats.subacc_4 = '' AND stats.utm_source = '' AND stats.utm_medium = '' AND stats.utm_term = '' AND stats.utm_content = '' AND stats.utm_campaign = '4763' AND stats.prelanding_id = 34 AND stats.landing_id = 66 AND stats.platform_id = 16 AND stats.platform_out_id = 68 AND stats.os_id = 4 AND stats.browser_id = 34
        Update on stats  (cost=0.00..7.76 rows=2 width=418) (actual time=62.923..62.923 rows=0 loops=1)
          ->  Seq Scan on stats  (cost=0.00..0.07 rows=1 width=670) (actual time=0.002..0.002 rows=0 loops=1)
                Filter: ((ts_spawn = 1427778000) AND (user_id = 32) AND (offer_targeting_id = 82) AND (offer_id = 22) AND (traffic_manager_id = 0) AND (operator_id = 0) AND (traff_type = 2) AND (country_id = 192) AND ((subacc_1)::text = ''::text) AND ((subacc_2)::text = ''::text) AND ((subacc_3)::text = ''::text) AND ((subacc_4)::text = ''::text) AND ((utm_medium)::text = ''::text) AND ((utm_term)::text = ''::text) AND ((utm_content)::text = ''::text) AND ((utm_source)::text = ''::text) AND ((utm_campaign)::text = '4763'::text) AND (prelanding_id = 34) AND (browser_id = 34) AND (landing_id = 66) AND (platform_id = 16) AND (platform_out_id = 68) AND (os_id = 4))
          ->  Index Scan using stats_y2015_m3_u32_pk on stats_y2015_m3_u32  (cost=0.55..7.69 rows=1 width=166) (actual time=0.219..0.222 rows=1 loops=1)
                Index Cond: ((ts_spawn = 1427778000) AND (user_id = 32) AND (offer_targeting_id = 82) AND (offer_id = 22) AND (traffic_manager_id = 0) AND(traff_type = 2) AND (operator_id = 0) AND (country_id = 192) AND ((subacc_1)::text = ''::text) AND ((subacc_2)::text = ''::text) AND ((subacc_3)::text = ''::text) AND ((subacc_4)::text = ''::text) AND ((utm_source)::text = ''::text) AND ((utm_medium)::text = ''::text) AND ((utm_term)::text = ''::text) AND ((utm_content)::text = ''::text) AND ((utm_campaign)::text = '4763'::text) AND (prelanding_id = 34) AND (landing_id = 66) AND (platform_id = 16) AND (platform_out_id = 68) AND (os_id = 4) AND (browser_id = 34))



Меня смущает, что в обоих случаях actual time=0.218..0.221, вот такое время на апдейт меня устраивает. Видимо все остальное время работает планировщик? о_О
...
Рейтинг: 0 / 0
31.03.2015, 08:18
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38921848
йоксель
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
йоксель
Гость
VerusK,
"остальное время" может работать планировщик, если запросы редкие, партиций много, и всё для планирования ему приходится поднимать в кеш по новой с диска. у меня например были случаи стабильного планирования по ~15 сек. (в холодном состоянии)

думаю, надо поискать у вас триггера, чеки, FK-и и прочие триггерные (по факту) ограничения, нет ли там чего-то неподобающего.
...
Рейтинг: 0 / 0
31.03.2015, 08:29
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38921851
VerusK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VerusK
Гость
йоксель,

запросы совсем не редкие, один из самых частых запросов, на самом деле. FK а базе нет, ну и триггер срабатывает только на insert(кстати замерял только вставку - отрабатывает моментально), а выполняется долго update.
...
Рейтинг: 0 / 0
31.03.2015, 08:34
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38921859
йоксель
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
йоксель
Гость
VerusK,

какие ограничения висят на таблице ? [цах]
какие из них -- пользуются планировщиком для отброса партиций при планировании ?

и вообще, все, что можете -- про таблицу расскажите.
...
Рейтинг: 0 / 0
31.03.2015, 08:59
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38921890
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
VerusK,

Да, похоже на планировщик — это известная фишка Постгреса при партиционировании.

Есть возможность использовать подготовленные запросы ?
При инициализации сессии подготовьте запрос и потом его только исполняйте.
...
Рейтинг: 0 / 0
31.03.2015, 09:22
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38921908
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
vyegorovVerusK,

Да, похоже на планировщик — это известная фишка Постгреса при партиционировании.

Есть возможность использовать подготовленные запросы ?
При инициализации сессии подготовьте запрос и потом его только исполняйте.

При использовании autoexplain (да и обычного explain analyze) - время планирование в actual time НЕ ИДЕТ (его вообще до 9.4 версии нигде не видно).
Тем более что планирование всегда занимает более менее одно и тоже время а тут то тормозит то нет.
Ну и надо учитывать что по словам автора топика у него вся база в памяти и поднимать с диска вроде бы ничего не надо.
Всетаки мне кажется что блокировки.


--Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
31.03.2015, 09:34
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38921914
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
VerusK

а сколько у вас на базе shared buffers стоит?
и как настроены checkpoints?

И просто для очистки совести посмотрите iostat -x -m -d 10 пару-тройку минут на предмет загрузки дисковой подсистемы.
И что у вас за дисковая подсистема?

--Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
31.03.2015, 09:39
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38921920
PgSQLAnonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PgSQLAnonymous
Гость
vyegorovVerusK,
Да, похоже на планировщик — это известная фишка Постгреса при партиционировании.


Ага, вот, например: http://www.postgresql.org/message-id/2371.1234539121@sss.pgh.pa.us]http://www.postgresql.org/message-id/2371.1234539121@sss.pgh.pa.us

vyegorovЕсть возможность использовать подготовленные запросы ?
При инициализации сессии подготовьте запрос и потом его только исполняйте.
Вот это хороший совет, IMHO, стоит попробовать.
...
Рейтинг: 0 / 0
31.03.2015, 09:42
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38921922
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
VerusK и остальные...

Снимаю свои возражения про планировщик, был невнимателен.
Это явно оно судя по:
"Planning time: 47.748 ms"

Теперь вопрос к автору топика покажите вывод \d+ stats.
Что-то у меня большие сомнения на счет 100 дочерних таблиц... такое время планирования скорее будет на 10000 дочерних таблиц.

PS: prepared запросы тут только хуже сделают... так как база будет во всех дочерних таблицах пытаться update строки а не в одной.

--Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
31.03.2015, 09:43
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38921928
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
PgSQLAnonymousvyegorovЕсть возможность использовать подготовленные запросы ?
При инициализации сессии подготовьте запрос и потом его только исполняйте.
Вот это хороший совет, IMHO, стоит попробовать.

Во избежание запутывания уважаемой аудитории и распостранения городских легенд позволю внести себе ясность в вопрос:

1)И так... prepared запрос планируется на этапе когда параметры запроса еще не известны.
2)В случае партиционирования это приводит к тому что план ВСЕГДА будет включать в себя обработку всех партиций,
так как построить универсальный план по другому - невозможно.
3)Это приводит к тому что в данной конкретной задаче будет произведена попытка поиска строк ВО ВСЕХ наличных партициях.
Что приведет еще более печальным результатам чем у автора топика наблюдаются.

Все вышеприведенное относится и к хранимкам.
По факту при использовании партиционирования приходится делать следующие вещи:
1)в prepared запросах надо ЯВНО (не параметрами) указывать условия по которым партиционируем.
2)в хранимках при работе с партиционированной таблицей использовать execute для перепланирования при каждом вызове.

--Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
31.03.2015, 09:49
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38921932
Ivan Durak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan Durak
Участник
vacuum full всех системных таблиц попробуй
...
Рейтинг: 0 / 0
31.03.2015, 09:50
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38921969
PgSQLAnonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PgSQLAnonymous
Гость
Maxim BogukPgSQLAnonymousпропущено...

Вот это хороший совет, IMHO, стоит попробовать.

Во избежание запутывания уважаемой аудитории и распостранения городских легенд позволю внести себе ясность в вопрос:

1)И так... prepared запрос планируется на этапе когда параметры запроса еще не известны.
2)В случае партиционирования это приводит к тому что план ВСЕГДА будет включать в себя обработку всех партиций,
так как построить универсальный план по другому - невозможно.
3)Это приводит к тому что в данной конкретной задаче будет произведена попытка поиска строк ВО ВСЕХ наличных партициях.
Что приведет еще более печальным результатам чем у автора топика наблюдаются.

Да, не подумал. :(
Maxim Boguk прав, не делайте этого --- станет только хуже. ;(
Сколько у Вас партиций, в самом деле?
...
Рейтинг: 0 / 0
31.03.2015, 10:11
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38921997
йоксель
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
йоксель
Гость
Maxim BogukVerusK и остальные...
<>
Что-то у меня большие сомнения на счет 100 дочерних таблиц... такое время планирования скорее будет на 10000 дочерних таблиц.
<>если много сложных индексов -- и все примерно про одно и то же, и примерно вдоль плана -- то очень даже бывает.

1. можно попробовать генерить sql динамически, с генерацией полного имени партиции -- должно спасти
2. попробовать (FK на бд по утверждению автора нет) заменить апдейт на delete + insert. (insert, по тому же утверждению, не тормозит).


3. у автора двухкомпонентное партицирование, кажется (по 2-м параметрам) -- можно попробовать перестроить иерархию -- от корня унаследовать пустышки по одному ключу, а от них -- по обоим, на обоих уровнях inherit констрайнт по первому ключу партицирования -- число переборов планером может сократится (хотя не верю - в случае наличия стопящего констрайнта типа {CHECK (FALSE) NO INHERIT} на головной именно в плане update оно зачем то аппендит такую "головную")
...
Рейтинг: 0 / 0
31.03.2015, 10:28
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38922021
йоксель
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
йоксель
Гость
Maxim Boguk
2)В случае партиционирования это приводит к тому что план ВСЕГДА будет включать в себя обработку всех партиций,
так как построить универсальный план по другому - невозможно.
это зависит от того, что такое "универсальный план в СУБД". кажется оракел умеет пользоваться значением бинд переменной для конкретизации плана "в момент исполнения". могу врать.
...
Рейтинг: 0 / 0
31.03.2015, 10:40
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38922022
VerusK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VerusK
Гость
конфиг PG:

shared_buffers = 32GB
# - Checkpoints -
checkpoint_segments = 64
checkpoint_completion_target = 0.9

\d+ stats - https://gist.github.com/anonymous/77e4c1000ae39f1fffb1 таблиц даже меньше 100 :)
iostat - https://gist.github.com/anonymous/50d9041f145fe2cf12a4

йоксель,
1. Первое, что пришло в голову - не помогает :(
2. Попробую сейчас поиграться
3. Все верно, партицирование идет по 2м параметрам, по ts_spawn и по user_id - планировалось так
чтобы у пользователей стата не тормозила(на самом деле это помогло, но зато столкнулся с проблемой записи этой самой статы)
...
Рейтинг: 0 / 0
31.03.2015, 10:40
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38922042
йоксель
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
йоксель
Гость
VerusK
1. Первое, что пришло в голову - не помогает :(
тогда проблема не в планировании.

тосты к партициям толстые ли ? индексы на них не протухшие ли?
я бы туда таки посмотрел, кто из них уникъю, и т.п. -- индексов то у вас просто немеряно. Если есть сильно протухшие уникъю индексы -- они будут тормозить проверкой. все время апаемая табличка нуждается в регулярных реиндексах, пичалька.
...
Рейтинг: 0 / 0
31.03.2015, 10:50
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38922050
йоксель
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
йоксель
Гость
PS
ddl ЧЕК-ов мы так и не увидели.
...
Рейтинг: 0 / 0
31.03.2015, 10:54
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38922076
VerusK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VerusK
Гость
Вот код триггера, тут как раз создание дочерних таблиц - https://gist.github.com/anonymous/79d37e631892b4c2048f
...
Рейтинг: 0 / 0
31.03.2015, 11:07
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38922107
йоксель
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
йоксель
Гость
VerusKВот код триггера, тут как раз создание дочерних таблиц - https://gist.github.com/anonymous/79d37e631892b4c2048f страшноватый. у вас д.б. гарантированно один писатель в stats, без конкурентов . например джоб с контролем уникальности себя в моменте. иначе -- будете все в белом.


есть надежда, что у вас сильно протухшие ваши пк (было слишком много апдейтов). Но как их неконкурентно поджать -- м.б. Максимовой утилиткой ?
...
Рейтинг: 0 / 0
31.03.2015, 11:25
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38922135
VerusK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VerusK
Гость
йоксель,

я провел эксперимент, сделал полный дамп базы и развернул его на версиях 9.2 и 9.3 - результат точно такой же, апдейт медленный. При разворачивании дампа он ведь строит индексы заново, верно?
...
Рейтинг: 0 / 0
31.03.2015, 11:38
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38922163
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
Maxim Boguk1)И так... prepared запрос планируется на этапе когда параметры запроса еще не известны.
2)В случае партиционирования это приводит к тому что план ВСЕГДА будет включать в себя обработку всех партиций,
так как построить универсальный план по другому - невозможно.

Начиная с версии 9.2 PostgreSQL поддерживает параметризованные подготовленные запросы (вторая звездочка).

Другое дело, что были жалобы (я не смогу найти быстро), что первые 4 исполнения проходят быстро, а потом планировщик переклинивает и он выбирает не оптимальные планы. Вроде эта тема была закрыта (патчами).

В связи с этим 2 вопроса:
какие конкретно версии (интересуют все 3 цифры) вы тестировали? в смысле — все ли апдейты стоят?

каким будет поведение на 9.1?
...
Рейтинг: 0 / 0
31.03.2015, 11:53
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38922191
VerusK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VerusK
Гость
vyegorov,

тестировал конкретно 9.2.4, 9.3, 9.4, 9.4.1
...
Рейтинг: 0 / 0
31.03.2015, 12:04
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38922196
йоксель
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
йоксель
Гость
VerusK,
верно

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

авторОтвет на второй пост:
Если выполнять просто на таблице:
Код: sql
1.
2.
3.
<>
"Planning time: 0.675 ms"
"Execution time: 0.166 ms"




-- в первом случае есть какое-то отягощение ?
...
Рейтинг: 0 / 0
31.03.2015, 12:07
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38922222
VerusK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VerusK
Гость
йоксель,

действительно, что-то я сам запутался в экспериментах. Если указать конкретную партицию, то работает очень быстро, наверное, как и должно. 1000 строк проапдейтилась за 10сек на локале
...
Рейтинг: 0 / 0
31.03.2015, 12:20
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38922353
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
VerusK,

А покажите вывод:
Код: sql
1.
SELECT name,setting FROM pg_settings WHERE source NOT IN ('default','override');



Код: sql
1.
SELECT * FROM pg_stat_user_tables WHERE relname ~ '^stats';



Код: sql
1.
SELECT * FROM pg_stat_bgwriter;
...
Рейтинг: 0 / 0
31.03.2015, 13:24
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38922390
VerusK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VerusK
Гость
vyegorov,

все запросы - https://gist.github.com/anonymous/90e84a06ab8bbe60f37e
...
Рейтинг: 0 / 0
31.03.2015, 13:39
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38922488
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
VerusK,

Я бы понизил shared_buffers до 2Гб, work_mem до 8Мб, effective_cache_size до 64Гб и пересобрал бы статистики.

Также я бы оставил commit_delay + commit_siblings по умолчанию.
...
Рейтинг: 0 / 0
31.03.2015, 14:15
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38922503
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
VerusK,

Если читаете по-английски, то посмотрите этот пост .
...
Рейтинг: 0 / 0
31.03.2015, 14:23
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38922673
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
VerusKВот код триггера, тут как раз создание дочерних таблиц - https://gist.github.com/anonymous/79d37e631892b4c2048f

а вот что попробуйте
вместо
Код: plaintext
1.
CHECK ( ts_spawn >= ' || ts_start || ' AND ts_spawn < ' || ts_end || '),
            CONSTRAINT ' || table_part || '_user_id CHECK (user_id = ' || NEW.user_id || ')

сделать один общий CHECK:
Код: plaintext
CHECK ( (ts_spawn >= ' || ts_start || ' AND ts_spawn < ' || ts_end || ') AND user_id = ' || NEW.user_id || '),

(причем на всех уже созданных партициях сменить тоже).

И посмотрите не исправилась ли ситуация.

Так как единственное отличие от нормальных схем у вас это два независимых условия партиционирования.
Может там что то не так работает.

--Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
31.03.2015, 15:26
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38922719
PgSQLAnonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PgSQLAnonymous
Гость
VerusKvyegorov,

все запросы - https://gist.github.com/anonymous/90e84a06ab8bbe60f37e
Вам бы следовало дать хоть какой-то SQL-script для воспроизведения ситуации. :(

Я вот попробовал воспроизвести (упрощённо): http://sqlfiddle.com/#!15/9a9c2/2

Неудобство в том, что PostgreSQL там максимум 9.3, зато оттуда можно скопировать и попробовать у себя.
...
Рейтинг: 0 / 0
31.03.2015, 15:42
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38922848
VerusK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VerusK
Гость
Maxim Boguk, попробовать можно, но не скоро, надо подумать как этот процесс автоматизировать. Руками нереально :)

PgSQLAnonymous, это если только дать дамп всех таблиц, там много записей уже, sqlfiddle отказывается столько заполнять :)
...
Рейтинг: 0 / 0
31.03.2015, 16:33
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38922858
VerusK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VerusK
Гость
А если попробовать написать триггер на update? Т.е. в триггере указывать в какой конкретно таблице надо проапдейтить запись. Наткнулся вот на такое обсуждение - http://postgresql.nabble.com/Query-plan-for-partitioned-UPDATE-DELETE-slow-and-swaps-vmem-compared-to-SELECT-td3291659.html
может с 2010 в этом плане ничего и не изменилось? :(
...
Рейтинг: 0 / 0
31.03.2015, 16:37
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38922920
йоксель
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
йоксель
Гость
VerusKА если попробовать написать триггер на update? Т.е. в триггере указывать в какой конкретно таблице надо проапдейтить запись. Наткнулся вот на такое обсуждение - http://postgresql.nabble.com/Query-plan-for-partitioned-UPDATE-DELETE-slow-and-swaps-vmem-compared-to-SELECT-td3291659.html
может с 2010 в этом плане ничего и не изменилось? :(как вы себе представляете событие данных в таблице, в которой их нет ?

или вы на стейтмент хотите триггер повесить ?

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

а обход иерархий скриптом -- стандартная задача
...
Рейтинг: 0 / 0
31.03.2015, 17:01
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38923111
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
vyegorovMaxim Boguk1)И так... prepared запрос планируется на этапе когда параметры запроса еще не известны.
2)В случае партиционирования это приводит к тому что план ВСЕГДА будет включать в себя обработку всех партиций,
так как построить универсальный план по другому - невозможно.

Начиная с версии 9.2 PostgreSQL поддерживает параметризованные подготовленные запросы (вторая звездочка).

Другое дело, что были жалобы (я не смогу найти быстро), что первые 4 исполнения проходят быстро, а потом планировщик переклинивает и он выбирает не оптимальные планы. Вроде эта тема была закрыта (патчами).

В связи с этим 2 вопроса:
какие конкретно версии (интересуют все 3 цифры) вы тестировали? в смысле — все ли апдейты стоят?

каким будет поведение на 9.1?


Тут коротко на этот вопрос не ответишь.

Часть1:
Как работают параметризованные prepared запросы - строятся 2 плана общий в начале и частный с учетом параметров при первом вызове.
Далее пока данный prepared запрос работает только с теми параметрами которые подходят под частный план - все в теории хорошо, но стоит 1 раз вызвать этот же prepared запрос с другими параметрами как он НЕОБРАТИМО переключится на generic plan и будет его использовать все время жизни prepared запроса. Иногда это приводит к милым эффектам - вот есть большое java приложение, вот оно сделало prepared запрос для какой то большой партиционированной таблицы. Далее 99.999% случаев запросы работают с текущим месяцем данные которого успешно закешированны в базе, потом какой то менеджер решает случайно посмотреть данные за прошлый месяц и тут наступает ад, так как этот prepared запрос (а они в java Дооолго живут) начинает перебирать архив партиций с данными за последние 10 лет которые лежат на медленных SAS дисках просто для истории, а он может вызываться часто. Вот так сервер работал-работал и упс. Я такую проблему как то 2 недели ловил.

Часть2:
Касательно " в теории хорошо" - на практике в 9.2.10 (последнем) поведение следующее у меня получается:

Код: 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.
prepare test(timestamp, integer) as select * from resstat where to_date=$1  and lbill_id=$2 limit 10;
PREPARE

 explain analyze execute test('2015-03-03 00:00:00'::timestamp, 1000);
                                                                                QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..20.24 rows=2 width=100) (actual time=0.088..0.088 rows=0 loops=1)
   ->  Result  (cost=0.00..20.24 rows=2 width=100) (actual time=0.085..0.085 rows=0 loops=1)
         ->  Append  (cost=0.00..20.24 rows=2 width=100) (actual time=0.084..0.084 rows=0 loops=1)
               ->  Seq Scan on resstat  (cost=0.00..0.00 rows=1 width=116) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((to_date = '2015-03-03 00:00:00'::timestamp without time zone) AND (lbill_id = 1000))
               ->  Index Scan using resstat_2015_03_lbill_id_idx on resstat_2015_03 resstat  (cost=0.00..20.24 rows=1 width=83) (actual time=0.082..0.082 rows=0 loops=1)
                     Index Cond: (lbill_id = 1000)
                     Filter: (to_date = '2015-03-03 00:00:00'::timestamp without time zone)
 Total runtime: 9.192 ms

так повторяется  5 раз... на 6 раз получается план перебтирающий все партции (6 лет архива), и остается таким до победы.
billing=# explain analyze execute test('2015-03-03 00:00:00'::timestamp, 1000);
                                                                                QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..184.76 rows=10 width=83) (actual time=1.570..1.570 rows=0 loops=1)
   ->  Result  (cost=0.00..2900.68 rows=157 width=83) (actual time=1.566..1.566 rows=0 loops=1)
         ->  Append  (cost=0.00..2900.68 rows=157 width=83) (actual time=1.565..1.565 rows=0 loops=1)
               ->  Seq Scan on resstat  (cost=0.00..0.00 rows=1 width=116) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: ((to_date = $1) AND (lbill_id = $2))
               ->  Index Scan using idx_resstat_2009_01_lbill_id on resstat_2009_01 resstat  (cost=0.00..23.30 rows=1 width=80) (actual time=0.035..0.035 rows=0 loops=1)
                     Index Cond: (lbill_id = $2)
                     Filter: (to_date = $1)
               ->  Index Scan using idx_resstat_2009_02_lbill_id on resstat_2009_02 resstat  (cost=0.00..23.59 rows=1 width=80) (actual time=0.013..0.013 rows=0 loops=1)
                     Index Cond: (lbill_id = $2)
                     Filter: (to_date = $1)
....

Так что проблему не исправили. Что в сочетании с изложенным в Часть1 делает лобовое использование партиций в сочетании с prepared запросами наработоспособным. Увы.

PS: пойду bug report напишу.

--Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
31.03.2015, 19:40
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38923146
PgSQLAnonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PgSQLAnonymous
Гость
VerusKPgSQLAnonymous, это если только дать дамп всех таблиц, там много записей уже, sqlfiddle отказывается столько заполнять :)

А я вот погонял тот fiddle, на который давал ссылку, на своём Postgres-е, и у меня Ваша проблема-то воспроизводится. ;)

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
EXPLAIN ANALYZE 
 UPDATE stats SET actions = 2
  WHERE stats.ts_spawn = 1400000000 AND stats.user_id = 2
    AND stats.offer_targeting_id = 132 AND stats.offer_id = 123
    AND stats.traff_type = 4 AND stats.operator_id = 0
    AND stats.country_id = 113 AND stats.subacc_1 = '' AND stats.subacc_2 = ''
    AND stats.subacc_3 = '' AND stats.subacc_4 = '' AND stats.utm_source = ''
    AND stats.utm_medium = '' AND stats.utm_term = ''
    AND stats.utm_content = '' AND stats.utm_campaign = ''
    AND stats.prelanding_id = 203 AND stats.landing_id = 0
    AND stats.platform_id = 6 AND stats.platform_out_id = 76 
    AND stats.os_id = 9 AND stats.browser_id = 34;


Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
 Update on stats  (cost=0.00..8.21 rows=2 width=666) (actual time=0.030..0.030 rows=0 loops=1)
   ->  Seq Scan on stats  (cost=0.00..0.00 rows=1 width=666) (actual time=0.003..0.003 rows=0 loops=1)
         Filter: ((ts_spawn = 1400000000) AND (user_id = 2) AND (offer_targeting_id = 132) AND (offer_id = 123) AND (traff_type = 4) AND (operator_id = 0) AND (landing_id = 0) AND (country_id = 113) AND ((subacc_1)::text = ''::text) AND ((subacc_2)::text = ''::text) AND ((subacc_3)::text = ''::text) AND ((subacc_4)::text = ''::text) AND ((utm_source)::text = ''::text) AND ((utm_medium)::text = ''::text) AND ((utm_term)::text = ''::text) AND ((utm_content)::text = ''::text) AND ((utm_campaign)::text = ''::text) AND (prelanding_id = 203) AND (platform_id = 6) AND (platform_out_id = 76) AND (os_id = 9) AND (browser_id = 34))
   ->  Index Scan using idx_stats_y2014_m5_u2_browser_id on stats_y2014_m5_u2  (cost=0.14..8.21 rows=1 width=666) (actual time=0.017..0.017 rows=0 loops=1)
         Index Cond: (browser_id = 34)
         Filter: ((ts_spawn = 1400000000) AND (user_id = 2) AND (offer_targeting_id = 132) AND (offer_id = 123) AND (traff_type = 4) AND (operator_id = 0) AND (landing_id = 0) AND (country_id = 113) AND ((subacc_1)::text = ''::text) AND ((subacc_2)::text = ''::text) AND ((subacc_3)::text = ''::text) AND ((subacc_4)::text = ''::text) AND ((utm_source)::text = ''::text) AND ((utm_medium)::text = ''::text) AND ((utm_term)::text = ''::text) AND ((utm_content)::text = ''::text) AND ((utm_campaign)::text = ''::text) AND (prelanding_id = 203) AND (platform_id = 6) AND (platform_out_id = 76) AND (os_id = 9))
  Planning time: 139.502 ms 
 Execution time: 0.504 ms

Это при том, что у stats всего 61 partition и по одной записи в каждой из них.

Это PostgreSQL 9.4.1.
...
Рейтинг: 0 / 0
31.03.2015, 20:23
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38923152
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
PgSQLAnonymous,

Текущая теория что партиционирование по 2м чекам приводит к каким то проблемам с планированием.
Попробую сделать короткий test case и зарепортить bug (если test case получится конечно)


--Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
31.03.2015, 20:30
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38923157
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
Maxim Boguk,

Спасибо за подробный ответ. Давно думаю, что отчасти из-за таких же проблем оракл свой шаред_пул сделал (там, правда, свои грабли).

Эх, не исправят они эту фишку пока полноценные партиции не прикрутят.
...
Рейтинг: 0 / 0
31.03.2015, 20:37
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38923163
PgSQLAnonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PgSQLAnonymous
Гость
Maxim BogukPgSQLAnonymous,
Текущая теория что партиционирование по 2м чекам приводит к каким то проблемам с планированием.
Попробую сделать короткий test case и зарепортить bug (если test case получится конечно)


Хм... А чем Вам этот fiddle не подходит как test case? Кстати, при 155 таблицах уже:
Planning time: 534.810 ms
...
Рейтинг: 0 / 0
31.03.2015, 20:47
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38923164
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Maxim BogukPgSQLAnonymous,

Текущая теория что партиционирование по 2м чекам приводит к каким то проблемам с планированием.
Попробую сделать короткий test case и зарепортить bug (если test case получится конечно)


--Maxim Boguk
www.postgresql-consulting.ru

по моим экспериментам получилось что проблема не столько в партициях сколько в 60*25 = 1500 индексах в плане.
без 25 индексов на каждой таблице запрос планируется раз в 10 быстрее что уже ближе к правде.

--Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
31.03.2015, 20:47
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38923218
VerusK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VerusK
Гость
Кстати, в другом проекте использовал партиционирование по одному чеку - таких проблем там не наблюдается, проверил(PG 9.3). Убрать тут чеки для тестов со всех таблиц еще не успел :)
...
Рейтинг: 0 / 0
31.03.2015, 22:25
| Ответить | Цитировать | Написать  
Быстрый запрос в explain analyze но медленный по факту
    #38923664
VerusK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VerusK
Гость
Пока остановился на решении в приложении указывать нужную партицию если это update. Теперь вставка происходит значительно быстрее.
...
Рейтинг: 0 / 0
01.04.2015, 12:08
| Ответить | Цитировать | Написать  
65 сообщений из 65, показаны все 3 страниц
  все  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Быстрый запрос в explain analyze но медленный по факту
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=53&tid=1998077]:
 0ms
get settings:
 6ms
get forum list:
 12ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 250ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 102ms
get tp. blocked users:
 1ms
others: 232ms
total:  617ms
созд. / загр.: 617ms
x
x
Закрыть


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