Обсуждение: COUNT(*) дофига весит
Подскажите пожалуйста какие ошибки
(можетбыть в настройках постгреса)
могли привести вот к такому эффекту:
personal=# explain ANALYZE SELECT count(*) from accounts;
QUERY PLAN
-------------------------------------------------------------
Aggregate (cost=43126.25..43126.26 rows=1 width=0) (actual
time=975.628..975.628 rows=1 loops=1)
-> Seq Scan on accounts (cost=0.00..43121.80 rows=1780 width=0)
(actual time=0.122..974.565 rows=1782 loops=1)
Total runtime: 975.687 ms
personal=# SELECT count(*) from accounts;
count
-------
1782
Всего-то 1782 записи ! и такая огромная цена.
и реальное время выполнения тоже неприемлемое.
vacuum analyze делается каждую ночь
Спасибо.
Попытаюсь ответить как можно точнее:
Maxim Boguk wrote:
> 1)Какая версия postgres?
8.2.6
> 2)Что стоит в настройках postgresql.conf для *_cost параметров?
# - Planner Cost Constants -
#seq_page_cost = 1.0 # measured on an arbitrary scale
#random_page_cost = 4.0 # same scale as above
#cpu_tuple_cost = 0.01 # same scale as above
#cpu_index_tuple_cost = 0.005 # same scale as above
#cpu_operator_cost = 0.0025 # same scale as above
#effective_cache_size = 1024MB
> 3)Если сделать 5 explain ANALYZE этого запроса подряд один за другим
> время будет постоянное или только первый запрос притормаживает?
Время выполнения постоянное между эксплэйнами
(но естественно зависит от загрузки сервера)
> 4)Что показывает SELECT pg_relation_size('accounts');
pg_relation_size
------------------
353107968
> 5)Насколько вообще сервер занят (load avg/нагрузка на диски)?
Total: 169 processes, 1706 lwps, load averages: 0.93, 1.11, 1.22
вот в текущий момент нагрузка на диск мизерная,
я запускаю больной запрос, запрос тормозит
iostat роста нагрузки на диск не показывает
> 6)Нету ли старых открытых транзакций (более 24х часов скажем)?
Была одна. (как могло влиять ?)
Закрыл. (что надо сделать чтобы это возымело эффект?)
>> Подскажите пожалуйста какие ошибки
>> (можетбыть в настройках постгреса)
>> могли привести вот к такому эффекту:
>>
>> personal=# explain ANALYZE SELECT count(*) from accounts;
>>
>> QUERY PLAN
>> -------------------------------------------------------------
>> Aggregate (cost=43126.25..43126.26 rows=1 width=0) (actual
>> time=975.628..975.628 rows=1 loops=1)
>> -> Seq Scan on accounts (cost=0.00..43121.80 rows=1780 width=0)
>> (actual time=0.122..974.565 rows=1782 loops=1)
>> Total runtime: 975.687 ms
>>
>>
>> personal=# SELECT count(*) from accounts;
>>
>> count
>> -------
>> 1782
>>
>>
>> Всего-то 1782 записи ! и такая огромная цена.
>> и реальное время выполнения тоже неприемлемое.
>>
>> vacuum analyze делается каждую ночь
>>
>
>
>> 4)Что показывает SELECT pg_relation_size('accounts');
>
> pg_relation_size
> ------------------
> 353107968
350mb для таблицы в 1700 записей это как то грустно.
Таблица распухла раз в 100-1000 (точнее будет ясно после vacuum full)
сканировать 350mb всегда медленно (даже если это на 99% пустое место).
>> 6)Нету ли старых открытых транзакций (более 24х часов скажем)?
>
> Была одна. (как могло влиять ?)
> Закрыл. (что надо сделать чтобы это возымело эффект?)
Открытая транзакция не дает возможность базе после vacuum повторно использовать пустое место.
Так что если транзакция длинная а поток обновлений большой то таблица может пухнуть до бесконечности.
Советы:
1)Настроить autovacuum (ночной vacuum analyze не самое удачное решение)
(http://www.postgresql.org/docs/8.2/interactive/routine-vacuuming.html#AUTOVACUUM)
и отключить ночные vacuum
2)любой ценой избегать длинных (более 1-2-3 часов транзакций)
Конкретно с этой таблицей на нее надо сделать:
vacuum full analyze accounts;
после чего ее размер вернется к норме и скорость count(*) станет выше.
(только надо не забывать что vacuum full блокирует любые операции с таблицей включая select и может работать заметное
время).
>>> Подскажите пожалуйста какие ошибки
>>> (можетбыть в настройках постгреса)
>>> могли привести вот к такому эффекту:
>>>
>>> personal=# explain ANALYZE SELECT count(*) from accounts;
>>>
>>> QUERY PLAN
>>> -------------------------------------------------------------
>>> Aggregate (cost=43126.25..43126.26 rows=1 width=0) (actual
>>> time=975.628..975.628 rows=1 loops=1)
>>> -> Seq Scan on accounts (cost=0.00..43121.80 rows=1780 width=0)
>>> (actual time=0.122..974.565 rows=1782 loops=1)
>>> Total runtime: 975.687 ms
>>>
>>>
>>> personal=# SELECT count(*) from accounts;
>>>
>>> count
>>> -------
>>> 1782
>>>
>>>
>>> Всего-то 1782 записи ! и такая огромная цена.
>>> и реальное время выполнения тоже неприемлемое.
>>>
>>> vacuum analyze делается каждую ночь
>>>
>>
>>
>
>
--
Maxim Boguk
Спасибо,
будем исправлять.
Maxim Boguk wrote:
>>> 4)Что показывает SELECT pg_relation_size('accounts');
>>
>> pg_relation_size
>> ------------------
>> 353107968
>
> 350mb для таблицы в 1700 записей это как то грустно.
> Таблица распухла раз в 100-1000 (точнее будет ясно после vacuum full)
> сканировать 350mb всегда медленно (даже если это на 99% пустое место).
>
>
>
>>> 6)Нету ли старых открытых транзакций (более 24х часов скажем)?
>>
>> Была одна. (как могло влиять ?)
>> Закрыл. (что надо сделать чтобы это возымело эффект?)
>
> Открытая транзакция не дает возможность базе после vacuum повторно
> использовать пустое место.
> Так что если транзакция длинная а поток обновлений большой то таблица
> может пухнуть до бесконечности.
>
> Советы:
> 1)Настроить autovacuum (ночной vacuum analyze не самое удачное решение)
> (http://www.postgresql.org/docs/8.2/interactive/routine-vacuuming.html#AUTOVACUUM)
>
> и отключить ночные vacuum
> 2)любой ценой избегать длинных (более 1-2-3 часов транзакций)
>
> Конкретно с этой таблицей на нее надо сделать:
> vacuum full analyze accounts;
> после чего ее размер вернется к норме и скорость count(*) станет выше.
> (только надо не забывать что vacuum full блокирует любые операции с
> таблицей включая select и может работать заметное время).
>
>
>>>> Подскажите пожалуйста какие ошибки
>>>> (можетбыть в настройках постгреса)
>>>> могли привести вот к такому эффекту:
>>>>
>>>> personal=# explain ANALYZE SELECT count(*) from accounts;
>>>>
>>>> QUERY PLAN
>>>> -------------------------------------------------------------
>>>> Aggregate (cost=43126.25..43126.26 rows=1 width=0) (actual
>>>> time=975.628..975.628 rows=1 loops=1)
>>>> -> Seq Scan on accounts (cost=0.00..43121.80 rows=1780 width=0)
>>>> (actual time=0.122..974.565 rows=1782 loops=1)
>>>> Total runtime: 975.687 ms
>>>>
>>>>
>>>> personal=# SELECT count(*) from accounts;
>>>>
>>>> count
>>>> -------
>>>> 1782
>>>>
>>>>
>>>> Всего-то 1782 записи ! и такая огромная цена.
>>>> и реальное время выполнения тоже неприемлемое.
>>>>
>>>> vacuum analyze делается каждую ночь
>>>>
>>>
>>>
>>
>>
>
>
Maxim Boguk wrote: > Советы: > 1)Настроить autovacuum (ночной vacuum analyze не самое удачное решение) > (http://www.postgresql.org/docs/8.2/interactive/routine-vacuuming.html#AUTOVACUUM) > и отключить ночные vacuum > 2)любой ценой избегать длинных (более 1-2-3 часов транзакций) Изучил инструкцию к autovacuum. и не понял, каким образом автовакуум избавит меня от необходимости FULL вакуума ? Объясните пожалуйста, в чем разница между авто и еженочным vaccuum analyze?
On Wed, 16 Apr 2008 13:48:42 +0400 silly_sad wrote: >и не понял, каким образом автовакуум избавит меня от необходимости >FULL вакуума ? тем, что он всё сделает за вас. причём при правильных настройках на производительности всего остального его работа не скажется. -- np: Alter Bridge - Coming Home
Вложения
On Wed, 16 Apr 2008 13:48:42 +0400 silly_sad wrote: >Объясните пожалуйста, в чем разница между авто и еженочным vaccuum >analyze? в том, что autovacuum проверяет количество измененных записей в таблицах, и запускается в случае, если это количество > некоего числа. что позволяет "размазать" вакуум по времени. по моему в документации это написано довольно подробно. цитата: "When enabled, the autovacuum daemon runs periodically and checks for tables that have had a large number of inserted, updated or deleted tuples." "Tables whose relfrozenxid value is more than autovacuum_freeze_max_age transactions old are always vacuumed. Otherwise, two conditions are used to determine which operation(s) to apply. If the number of obsolete tuples since the last VACUUM exceeds the "vacuum threshold", the table is vacuumed." -- np: Alter Bridge - Before Tomorrow Comes
Вложения
Alex Gorbachenko wrote: > silly_sad wrote: > >> и не понял, каким образом автовакуум избавит меня от необходимости >> FULL вакуума ? > тем, что он всё сделает за вас. причём при правильных настройках на > производительности всего остального его работа не скажется. делает ли он за меня FULL ? или каким-то образом делает FULL ненужным? >> Объясните пожалуйста, в чем разница между авто и еженочным vaccuum >> analyze? > > в том, что autovacuum проверяет количество измененных > записей в таблицах, и запускается в случае, если это количество > некоего числа. что позволяет "размазать" вакуум по времени. по моему в > документации это написано довольно подробно. Алгоритм запуска этой процедуры мне понятен. Мне непонятно каким образом частые вакуумы оказываются более эффективными в смысле очистки места чем ежесуточные. насколько я смог судить из документации автомат вызывает VACUUM. А меня сейчас спасает только VACUUM FULL (ежесуточного хватает). (Несколько таблиц очень интенсивно апдэйтятся)
silly_sad wrote: > Alex Gorbachenko wrote: > >> silly_sad wrote: > >> и не понял, каким образом автовакуум > избавит меня от необходимости > >> FULL вакуума ? > > > тем, что он всё сделает за вас. причём > при правильных настройках на > > производительности всего остального > его работа не скажется. > > делает ли он за меня FULL ? или каким-то > образом делает FULL ненужным? Основное направление разработки - как раз сделать FULL ненужным. По-сути обычный vacuum ничего не освобождает, он просто отмечает свободное место в таблице, состоящее из строк, которые уже не нужны (удалены и не видимы для транзакций). Т.е. после вызова VACUUM на диске таблица будет занимать столько же, сколько и до вызова, но дисковое пространство будет расходоваться более эффективно. > >>> Объясните пожалуйста, в чем разница >>> между авто и еженочным vaccuum >>> analyze? >> в том, что autovacuum проверяет количество >> измененных >> записей в таблицах, и запускается в >> случае, если это количество некоего >> числа. что позволяет "размазать" вакуум >> по времени. по моему в >> документации это написано довольно >> подробно. > > Алгоритм запуска этой процедуры мне > понятен. > Мне непонятно каким образом частые > вакуумы оказываются более эффективными > в смысле очистки места чем ежесуточные. Это достаточно просто показать на примере: Представь, что у тебя таблица занимает 100 Мб, из нее удаляется 50 мегабайт строк, после чего вставляется 100 мегабайт. Если сделать vacuum после последней вставки 100 мегабайт, то таблица будет занимать 200 мегабайт на диске (при удалении 50 мегабайт на самом деле никакого удаления не произошло, просто соотв. строки были отмечены особенным образом). Теперь во втором варианте, если вначале после удаления 50мб сделать vacuum, а потом вставить 100 мегабайт, то таблица будет занимать (условно говоря) 150 мегабайт, т.к. место, связанное с удаленными ячейками было отмечено командой vacuum как свободное и последующая вставка 100 мегабайт получила возможность его использовать. > > насколько я смог судить из документации > автомат вызывает > VACUUM. > А меня сейчас спасает только VACUUM FULL > (ежесуточного хватает). > (Несколько таблиц очень интенсивно > апдэйтятся) Попробуй сделать более агрессивный vacuum analyze. Вся прелесть autovacuum - в настраиваемых параметрах его работы, как уже было сказано вместо того, чтобы запускать обычный vacuum analyze _по_времени_ (например из cron-а) можно использовать количество "устаревших" строк таблицы и затюнить autovacuum под свои нужды. Вообще наиболее полно возможности autovacuum проявляются в 8.3: http://www.postgresql.org/docs/8.3/static/routine-vacuuming.html#AUTOVACUUM -- Alexey Klyukin http://www.commandprompt.com/ The PostgreSQL Company - Command Prompt, Inc.
silly_sad пишет: > Alex Gorbachenko wrote: > >> silly_sad wrote: >> > >> и не понял, каким образом автовакуум избавит меня от необходимости > >> FULL вакуума ? > > > тем, что он всё сделает за вас. причём при правильных настройках на > > производительности всего остального его работа не скажется. > > делает ли он за меня FULL ? или каким-то образом делает FULL ненужным? В нормальной ситуации правильно настроенный autovacuum действительно делает vacuum full ненужным. По одной причине: vacuum помечает удаленные записи как место которое можно повторно использовать. Если таблицу обновляется очень активно то за сутки между запусками vacuum full таблица успевает очень сильно разрастись. Делать же ежеминутный просто vacuum не выгодно так как он будет vacuum всего подряд а не только тех таблиц которые обновились. (а autovacuum отслеживает степень обновления таблиц). Autovacuum просто не дают таблице распухать выше определенного предела так как свободное место будет отмечено и повторноиспользовано. (как правило удается держать таблицы в неболее чем 2х кратном размере от минимально возможного). Если у вас есть активнообновляемые таблицы то настройки могут выглядеть приблизительно так: autovacuum = on autovacuum_naptime = 1min Остальные параметры пока можно не трогать. PS: если у вас есть активное обновление я бы рекомендовал раз в неделю или месяц делать REINDEX DATABASE (так как индексытоже имеют свойство распухать со временем) Но Reindex так же блокирует доступ к таблице как и vacuum full. PPS: надо понимать что просто vacuum (без full) не блокирует работу с таблицей и не сказывается на работоспособности сервиса... PPPS: для работоспособности autovacuum надо не забывать включать (пре 8.3): stats_start_collector = on stats_block_level = on stats_row_level = on (на 8.3 просто надо указать: track_counts = on) > >>> Объясните пожалуйста, в чем разница между авто и еженочным vaccuum >>> analyze? >> >> в том, что autovacuum проверяет количество измененных >> записей в таблицах, и запускается в случае, если это количество >> некоего числа. что позволяет "размазать" вакуум по времени. по моему в >> документации это написано довольно подробно. > > Алгоритм запуска этой процедуры мне понятен. > Мне непонятно каким образом частые вакуумы оказываются более > эффективными в смысле очистки места чем ежесуточные. > > насколько я смог судить из документации автомат вызывает > VACUUM. > А меня сейчас спасает только VACUUM FULL (ежесуточного хватает). > (Несколько таблиц очень интенсивно апдэйтятся) > > > -- Maxim Boguk
Maxim Boguk wrote: > PS: если у вас есть активное обновление я бы рекомендовал раз в неделю > или месяц делать REINDEX DATABASE (так как индексы тоже имеют свойство > распухать со временем) > Но Reindex так же блокирует доступ к таблице как и vacuum full. спасибо. я посмотрю с какой скоростью пухнут индексы. > PPS: надо понимать что просто vacuum (без full) не блокирует работу с > таблицей и не сказывается на работоспособности сервиса... Это я понимаю, но у нас дисковый накопитель является узким местом, поэтому осторожничаю.