Re: [pgsql-ru-general] Re: [pgsql-ru-general] Чистка таблиц

Имеются таблицы

|t1_id|...|
|t2_id|t1_id|...|
|t3_id|...|
|t4_id|t1_id|t3_id|...|

То есть таблички с форейгнами.

Объявлены столбики связей так:

t1_id INTEGE REFERENCES t1 (t1_id) ON DELETE CASCADE ON UPDATE
CASCADE;

в одной из таблиц ON DELETE SET NULL;

Ну и значит в таблице

t1 ~ 2.5 млн записей
t2 ~ 0.5 млн записей
t3 - 10 записей
t4 ~ 1 млн записей

теперь удаляем

DELETE FROM t1 WHERE id = 2919364;

запрос выполняется немерянное количество времени.

План показывает примерно такой:

QUERY PLAN
----------------------------------------------------------------------------------------
Delete on t1 (cost=0.00..8.59 rows=1 width=6)
-> Index Scan using t1_pkey on t1 (cost=0.00..8.59 rows=1 width=6)
Index Cond: (id = 2919364)
(3 rows)

Памяти на инстансе мало. да. 1Гиг всего. Таблицы занимают примерно 2.5 Гиг.

Получается что добавление записей в эти таблицы (это таблицы с логами)
работает без задержек. А удаление записей - примерно одна в три
минуты. Причем удаление по PRIMARY KEY.

Вопрос что можно сделать/посмотреть/переделать, чтобы можно было
нормально чистить логи в такой таблице?
--

. ''`. Dmitry E. Oboukhov
: :’ : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
`. `~’ GPGKey: 1024D / F8E26537 2006-11-21
`- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537

С Рождеством !

7 января 2012 г. 3:04 пользователь Dmitry E. Oboukhov <unera(at)debian(dot)org>написал:

> Имеются таблицы
>
> |t1_id|...|
> |t2_id|t1_id|...|
> |t3_id|...|
> |t4_id|t1_id|t3_id|...|
>
> То есть таблички с форейгнами.
>
> Объявлены столбики связей так:
>
> t1_id INTEGE REFERENCES t1 (t1_id) ON DELETE CASCADE ON UPDATE
> CASCADE;
>
>
> в одной из таблиц ON DELETE SET NULL;
>
> Ну и значит в таблице
>
> t1 ~ 2.5 млн записей
> t2 ~ 0.5 млн записей
> t3 - 10 записей
> t4 ~ 1 млн записей
>
> теперь удаляем
>
> DELETE FROM t1 WHERE id = 2919364;
>
> запрос выполняется немерянное количество времени.
>
> План показывает примерно такой:
>
> QUERY PLAN
>
> ----------------------------------------------------------------------------------------
> Delete on t1 (cost=0.00..8.59 rows=1 width=6)
> -> Index Scan using t1_pkey on t1 (cost=0.00..8.59 rows=1 width=6)
> Index Cond: (id = 2919364)
> (3 rows)
>
> Памяти на инстансе мало. да. 1Гиг всего. Таблицы занимают примерно 2.5 Гиг.
>
> Получается что добавление записей в эти таблицы (это таблицы с логами)
> работает без задержек. А удаление записей - примерно одна в три
> минуты. Причем удаление по PRIMARY KEY.
>
> Вопрос что можно сделать/посмотреть/переделать, чтобы можно было
> нормально чистить логи в такой таблице?
>
Можно попробовать принцип "разделяй и властвуй" -
воспользоваться разбиением дочерних таблиц (t2 и t4),
например, по диапазонам значений их внешних ключей
(t1 и t3), как рассказывается здесь:
http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html

--
// Dmitriy.

> Можно попробовать принцип "разделяй и властвуй" -
> воспользоваться разбиением дочерних таблиц (t2 и t4),
> например, по диапазонам значений их внешних ключей
> (t1 и t3), как рассказывается здесь:
> http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html

Партицирование имеет смысл, если хочется данные именно сохранить. а
тут я хочу банально удалять все что старше двух недель (логи).

и наткнулся на вот такие вот тормоза
--

. ''`. Dmitry E. Oboukhov
: :’ : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
`. `~’ GPGKey: 1024D / F8E26537 2006-11-21
`- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537

7 января 2012 г. 14:58 пользователь Dmitry E. Oboukhov
<unera(at)debian(dot)org>написал:

> > Можно попробовать принцип "разделяй и властвуй" -
> > воспользоваться разбиением дочерних таблиц (t2 и t4),
> > например, по диапазонам значений их внешних ключей
> > (t1 и t3), как рассказывается здесь:
> > http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html
>
> Партицирование имеет смысл, если хочется данные именно сохранить. а
> тут я хочу банально удалять все что старше двух недель (логи).
>
Ну и удаляйте :-)
Только ведь Вы хотите ускорения и не за счёт обновления
оборудования? Ускорить можно за счёт разбиения одного
индекса на множество, что позволит механизму исключения
ограничений работать с более мелкими индексами и
использовать меньший объём памяти. Смысл разбиения в этом.

>
> и наткнулся на вот такие вот тормоза
> --
>
> . ''`. Dmitry E. Oboukhov
> : :’ : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
> `. `~’ GPGKey: 1024D / F8E26537 2006-11-21
> `- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537
>

--
// Dmitriy.

> Ну и удаляйте :-)
> Только ведь Вы хотите ускорения и не за счёт обновления
> оборудования? Ускорить можно за счёт разбиения одного
> индекса на множество, что позволит механизму исключения
> ограничений работать с более мелкими индексами и
> использовать меньший объём памяти. Смысл разбиения в этом.

Мне вот непонятно, почему
1. выборка происходит быстро
2. добавление записей происходит быстро
3. удаление происходит медленно

может можно просто тюнингом индексов играть как-то?

--

. ''`. Dmitry E. Oboukhov
: :’ : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
`. `~’ GPGKey: 1024D / F8E26537 2006-11-21
`- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537

7 января 2012 г. 14:58 пользователь Dmitry E. Oboukhov
<unera(at)debian(dot)org>написал:

> > Можно попробовать принцип "разделяй и властвуй" -
> > воспользоваться разбиением дочерних таблиц (t2 и t4),
> > например, по диапазонам значений их внешних ключей
> > (t1 и t3), как рассказывается здесь:
> > http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html
>
> Партицирование имеет смысл, если хочется данные именно сохранить. а
> тут я хочу банально удалять все что старше двух недель (логи).
>
Да и вообще, если Вы знаете точные сроки, то как раз
разбиение на части по периодам и ротация этих частей -
это как раз то, что Вам нужно. Удаление будет осуществляться
через DROP TABLE ...

// Dmitriy.

7 января 2012 г. 15:06 пользователь Dmitry E. Oboukhov
<unera(at)debian(dot)org>написал:

>
> > Ну и удаляйте :-)
> > Только ведь Вы хотите ускорения и не за счёт обновления
> > оборудования? Ускорить можно за счёт разбиения одного
> > индекса на множество, что позволит механизму исключения
> > ограничений работать с более мелкими индексами и
> > использовать меньший объём памяти. Смысл разбиения в этом.
>
> Мне вот непонятно, почему
> 1. выборка происходит быстро
> 2. добавление записей происходит быстро
> 3. удаление происходит медленно
>
> может можно просто тюнингом индексов играть как-то?
>
Удаление на самом деле обновляет каждую запись
для последующего VACUUM. В этом плане, предпочтительнее
использовать TRUNCATE там где это возможно, или же
DROP TABLE ... на отдельную часть (в Вашем случае дочерней) таблицы.

--
// Dmitriy.

В данной ситуации действительно лучше всего использовать
партицирование. Тогда медленный DELETE заменяется просто на DROP.

--
Best regards,
Anton Krasikov

2012/1/7 Dmitriy Igrishin <dmitigr(at)gmail(dot)com>:
>
>
> 7 января 2012 г. 15:06 пользователь Dmitry E. Oboukhov <unera(at)debian(dot)org>
> написал:
>
>>
>> > Ну и удаляйте :-)
>> > Только ведь Вы хотите ускорения и не за счёт обновления
>> > оборудования? Ускорить можно за счёт разбиения одного
>> > индекса на множество, что позволит механизму исключения
>> > ограничений работать с более мелкими индексами и
>> > использовать меньший объём памяти. Смысл разбиения в этом.
>>
>> Мне вот непонятно, почему
>> 1. выборка происходит быстро
>> 2. добавление записей происходит быстро
>> 3. удаление происходит медленно
>>
>> может можно просто тюнингом индексов играть как-то?
>
> Удаление на самом деле обновляет каждую запись
> для последующего VACUUM. В этом плане, предпочтительнее
> использовать TRUNCATE там где это возможно, или же
> DROP TABLE ... на отдельную часть (в Вашем случае дочерней) таблицы.
>
> --
> // Dmitriy.
>
>

"Dmitry E. Oboukhov" <unera(at)debian(dot)org> writes:

> Имеются таблицы
>
> |t1_id|...|
> |t2_id|t1_id|...|
> |t3_id|...|
> |t4_id|t1_id|t3_id|...|
>
> То есть таблички с форейгнами.
>
> Объявлены столбики связей так:
>
> t1_id INTEGE REFERENCES t1 (t1_id) ON DELETE CASCADE ON UPDATE
> CASCADE;

foreign key не создают индексы, у Вас созданы на каждой таблице индексы по foreign key?

> План показывает примерно такой:
>
> QUERY PLAN
> ----------------------------------------------------------------------------------------
> Delete on t1 (cost=0.00..8.59 rows=1 width=6)
> -> Index Scan using t1_pkey on t1 (cost=0.00..8.59 rows=1 width=6)
> Index Cond: (id = 2919364)
> (3 rows)

А какая версия сервера? Здесь скорее всего не показываются вызовы служебных триггеров foreign key
в которых возможно при отсутствии индексов по t1_id проверка и обновление внешних ключей идёт
по seq scan.

--
С уважением, Сергей Бурладян