После Vacuum (autovacuum) таблица продолжает расти / PostgreSQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / После Vacuum (autovacuum) таблица продолжает расти

25 сообщений из 29, страница 1 из 2

все

После Vacuum (autovacuum) таблица продолжает расти

#39778944

maxski

Участник

Откуда: Москва-Лимассол

Сообщения: 154

Рейтинг: 0 / 0

Мне тут досталась одна база на postgresql. Уже под 3ТБ.
Самая большая таблица - уже 2.7ТБ. На ней был отключен автовакуум. Я его включил с настройками по умолчанию в 2016 году.
Тогда рост остановился. И вот таблица снова начала расти в размере.
Я настроил автовакуум для нескольких крупных таблиц другие коэффициенты:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

(postgres@[local]:5432) [rtdb] > select relname, reloptions from pg_class where reloptions is not null;
      relname       |                                     reloptions                                     
--------------------+------------------------------------------------------------------------------------
 transactions       | {autovacuum_analyze_scale_factor=0.00001,autovacuum_vacuum_scale_factor=0.00001}
 links              | {autovacuum_vacuum_scale_factor=0.0001,autovacuum_analyze_scale_factor=0.0001}
 principals         | {autovacuum_vacuum_scale_factor=0.00001,autovacuum_analyze_scale_factor=0.00001}
 tickets            | {autovacuum_analyze_scale_factor=0.00001,autovacuum_vacuum_scale_factor=0.00001}
 cachedgroupmembers | {autovacuum_vacuum_scale_factor=0.000003,autovacuum_analyze_scale_factor=0.000003}
 attachments        | {autovacuum_vacuum_scale_factor=0.000003,autovacuum_analyze_scale_factor=0.000003}
 sessions           | {autovacuum_vacuum_scale_factor=0.00001,autovacuum_analyze_scale_factor=0.00001}
 groupmembers       | {autovacuum_analyze_scale_factor=0.00001}
 groups             | {autovacuum_vacuum_scale_factor=0.00001,autovacuum_analyze_scale_factor=0.00001}
(9 rows)

Time: 1.228 ms

Теперь автовакуум отрабатывает примерно 1 раз в час.

Код: plsql

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.

(postgres@[local]:5432) [rtdb] > SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum, last_vacuum
FROM pg_stat_all_tables where schemaname = 'public'
ORDER BY n_dead_tup
    / (n_live_tup
       * current_setting('autovacuum_vacuum_scale_factor')::float8
          + current_setting('autovacuum_vacuum_threshold')::float8)
     DESC
LIMIT 50;
 schemaname |         relname         | n_live_tup | n_dead_tup |        last_autovacuum        |          last_vacuum          
------------+-------------------------+------------+------------+-------------------------------+-------------------------------
 public     | users                   |       3128 |          7 | NULL                          | 2019-02-25 00:19:42.367853+02
 public     | sessions                |     121690 |         55 | 2019-02-25 10:47:45.367452+02 | 2019-02-25 00:19:44.009534+02
 public     | attachments             |   30375032 |       2529 | 2019-02-25 09:19:42.6018+02   | 2019-02-25 00:09:10.565634+02
 public     | tickets                 |    8415258 |        497 | 2019-02-25 10:47:51.725835+02 | 2019-02-25 00:05:01.451179+02
 public     | cachedgroupmembers      |   50626375 |       1677 | 2019-02-25 10:45:50.509443+02 | 2019-02-25 09:06:08.994997+02
 public     | principals              |   33695907 |        164 | 2019-02-25 11:03:27.842071+02 | 2019-02-25 00:04:21.729717+02
 public     | transactions            |   66811706 |         83 | 2019-02-22 20:46:14.270108+02 | 2019-02-25 00:03:59.712033+02
 public     | groupmembers            |   16928716 |         21 | NULL                          | 2019-02-25 00:20:03.327576+02
 public     | groups                  |   33676158 |          4 | NULL                          | 2019-02-25 00:23:41.552003+02
 public     | templates               |        586 |          0 | NULL                          | 2019-02-25 00:19:42.029181+02
 public     | customfields            |          3 |          0 | NULL                          | 2019-02-25 00:19:42.613545+02
 public     | attributes              |      15237 |          0 | NULL                          | 2019-02-25 00:19:42.754415+02
 public     | articles                |          0 |          0 | NULL                          | 2019-02-25 00:19:43.583219+02
 public     | objectcustomfields      |          3 |          0 | NULL                          | 2019-02-25 00:19:42.632417+02
 public     | topics                  |          1 |          0 | NULL                          | 2019-02-25 00:19:43.592342+02
 public     | customfieldvalues       |          3 |          0 | NULL                          | 2019-02-25 00:19:42.646212+02
 public     | queues                  |        250 |          0 | 2017-11-25 10:18:32.693162+02 | 2019-02-25 00:04:01.778919+02
 public     | scripconditions         |         16 |          0 | NULL                          | 2019-02-25 00:04:01.735116+02
 public     | objectcustomfieldvalues |       1541 |          0 | NULL                          | 2019-02-25 00:19:42.189948+02
 public     | classes                 |          1 |          0 | NULL                          | 2019-02-25 00:19:43.578746+02
 public     | scrips                  |        673 |          0 | NULL                          | 2019-02-25 00:04:02.343555+02
 public     | scripactions            |         18 |          0 | NULL                          | 2019-02-25 00:19:42.008968+02
 public     | objectclasses           |          0 |          0 | NULL                          | 2019-02-25 00:24:47.396724+02
 public     | links                   |      64691 |          0 | NULL                          | 2019-02-25 00:04:00.57868+02
 public     | objecttopics            |          0 |          0 | NULL                          | 2019-02-25 00:19:43.595186+02
 public     | acl                     |      11295 |          0 | NULL                          | 2019-02-25 00:04:01.921562+02
(26 rows)

Time: 13.025 ms

После Vacuum (autovacuum) таблица продолжает расти. Снова по 1 ГБ в день отжирает.

При этом в страницах полно свободного места:

Код: plsql

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.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.

(postgres@[local]:5432) [rtdb] > SELECT * FROM heap_page_items(get_raw_page('attachments', 10008));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 |   t_ctid   | t_infomask2 | t_infomask | t_hoff |                                  t_bits                                  | t_oid 
----+--------+----------+--------+--------+--------+----------+------------+-------------+------------+--------+--------------------------------------------------------------------------+-------
  1 |    576 |        1 |    512 |      2 |      0 |        0 | (10008,1)  |          13 |      10503 |     32 | 111111111111000000000000000000000000000000000000000000000000000000000000 |  NULL
  2 |      0 |        0 |      0 |   NULL |   NULL |     NULL | NULL       |        NULL |       NULL |   NULL | NULL                                                                     |  NULL
  3 |      0 |        0 |      0 |   NULL |   NULL |     NULL | NULL       |        NULL |       NULL |   NULL | NULL                                                                     |  NULL
  4 |      0 |        0 |      0 |   NULL |   NULL |     NULL | NULL       |        NULL |       NULL |   NULL | NULL                                                                     |  NULL
  5 |   6704 |        1 |   1484 |      2 |      0 |        0 | (10008,5)  |          13 |      10503 |     32 | 111110111111100000000000000000000000000000000000000000000000000000000000 |  NULL
  6 |   4848 |        1 |   1850 |      2 |      0 |        0 | (10008,6)  |          13 |      10499 |     32 | 111110111111100000000000000000000000000000000000000000000000000000000000 |  NULL
  7 |      0 |        0 |      0 |   NULL |   NULL |     NULL | NULL       |        NULL |       NULL |   NULL | NULL                                                                     |  NULL
  8 |   3840 |        1 |   1002 |      2 |      0 |        0 | (10008,8)  |          13 |      10503 |     32 | 111110111111100000000000000000000000000000000000000000000000000000000000 |  NULL
  9 |      0 |        0 |      0 |   NULL |   NULL |     NULL | NULL       |        NULL |       NULL |   NULL | NULL                                                                     |  NULL
 10 |      0 |        0 |      0 |   NULL |   NULL |     NULL | NULL       |        NULL |       NULL |   NULL | NULL                                                                     |  NULL
 11 |      0 |        0 |      0 |   NULL |   NULL |     NULL | NULL       |        NULL |       NULL |   NULL | NULL                                                                     |  NULL
 12 |      0 |        0 |      0 |   NULL |   NULL |     NULL | NULL       |        NULL |       NULL |   NULL | NULL                                                                     |  NULL
 13 |   2784 |        1 |   1050 |      2 |      0 |        0 | (10008,13) |          13 |      10503 |     32 | 111110111111100000000000000000000000000000000000000000000000000000000000 |  NULL
 14 |   1736 |        1 |   1042 |      2 |      0 |        0 | (10008,14) |          13 |      10503 |     32 | 111110111111100000000000000000000000000000000000000000000000000000000000 |  NULL
 15 |   1088 |        1 |    642 |      2 |      0 |        0 | (10008,15) |          13 |      10503 |     32 | 111110111111100000000000000000000000000000000000000000000000000000000000 |  NULL
 16 |      0 |        0 |      0 |   NULL |   NULL |     NULL | NULL       |        NULL |       NULL |   NULL | NULL                                                                     |  NULL
(16 rows)

Time: 0.491 ms
(postgres@[local]:5432) [rtdb] > SELECT * FROM heap_page_items(get_raw_page('attachments', 10009));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 |   t_ctid   | t_infomask2 | t_infomask | t_hoff |                                  t_bits                                  | t_oid 
----+--------+----------+--------+--------+--------+----------+------------+-------------+------------+--------+--------------------------------------------------------------------------+-------
  1 |   6216 |        1 |   1970 |      2 |      0 |        0 | (10009,1)  |          13 |      10499 |     32 | 111110111111100000000000000000000000000000000000000000000000000000000000 |  NULL
  2 |      0 |        0 |      0 |   NULL |   NULL |     NULL | NULL       |        NULL |       NULL |   NULL | NULL                                                                     |  NULL
  3 |      0 |        0 |      0 |   NULL |   NULL |     NULL | NULL       |        NULL |       NULL |   NULL | NULL                                                                     |  NULL
  4 |      0 |        0 |      0 |   NULL |   NULL |     NULL | NULL       |        NULL |       NULL |   NULL | NULL                                                                     |  NULL
  5 |   5112 |        1 |   1098 |      2 |      0 |        0 | (10009,5)  |          13 |      10503 |     32 | 111110111111100000000000000000000000000000000000000000000000000000000000 |  NULL
  6 |   4024 |        1 |   1082 |      2 |      0 |        0 | (10009,6)  |          13 |      10503 |     32 | 111110111111100000000000000000000000000000000000000000000000000000000000 |  NULL
  7 |      0 |        0 |      0 |   NULL |   NULL |     NULL | NULL       |        NULL |       NULL |   NULL | NULL                                                                     |  NULL
  8 |      0 |        0 |      0 |   NULL |   NULL |     NULL | NULL       |        NULL |       NULL |   NULL | NULL                                                                     |  NULL
  9 |   2248 |        1 |   1774 |      2 |      0 |        0 | (10009,9)  |          13 |      10499 |     32 | 111110111111100000000000000000000000000000000000000000000000000000000000 |  NULL
 10 |   1696 |        1 |    552 |      2 |      0 |        0 | (10009,10) |          13 |      10503 |     32 | 111111111111000000000000000000000000000000000000000000000000000000000000 |  NULL
 11 |   1120 |        1 |    576 |      2 |      0 |        0 | (10009,11) |          13 |      10499 |     32 | 111110100111000000000000000000000000000000000000000000000000000000000000 |  NULL
 12 |    104 |        1 |   1010 |      2 |      0 |        0 | (10009,12) |          13 |      10503 |     32 | 111110111111100000000000000000000000000000000000000000000000000000000000 |  NULL
(12 rows)

Time: 0.442 ms
(postgres@[local]:5432) [rtdb] > SELECT * FROM heap_page_items(get_raw_page('attachments', 100009));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 |   t_ctid    | t_infomask2 | t_infomask | t_hoff |                                  t_bits                                  | t_oid 
----+--------+----------+--------+--------+--------+----------+-------------+-------------+------------+--------+--------------------------------------------------------------------------+-------
  1 |      0 |        0 |      0 |   NULL |   NULL |     NULL | NULL        |        NULL |       NULL |   NULL | NULL                                                                     |  NULL
  2 |   6440 |        1 |   1746 |      2 |      0 |        0 | (100009,2)  |          13 |      10503 |     32 | 111110111111100000000000000000000000000000000000000000000000000000000000 |  NULL
  3 |   4744 |        1 |   1690 |      2 |      0 |        0 | (100009,3)  |          13 |      10503 |     32 | 111110111111100000000000000000000000000000000000000000000000000000000000 |  NULL
  4 |   2912 |        1 |   1826 |      2 |      0 |        0 | (100009,4)  |          13 |      10503 |     32 | 111110111111100000000000000000000000000000000000000000000000000000000000 |  NULL
  5 |     13 |        2 |      0 |   NULL |   NULL |     NULL | NULL        |        NULL |       NULL |   NULL | NULL                                                                     |  NULL
  6 |     15 |        2 |      0 |   NULL |   NULL |     NULL | NULL        |        NULL |       NULL |   NULL | NULL                                                                     |  NULL
  7 |     14 |        2 |      0 |   NULL |   NULL |     NULL | NULL        |        NULL |       NULL |   NULL | NULL                                                                     |  NULL
  8 |     16 |        2 |      0 |   NULL |   NULL |     NULL | NULL        |        NULL |       NULL |   NULL | NULL                                                                     |  NULL
  9 |      0 |        0 |      0 |   NULL |   NULL |     NULL | NULL        |        NULL |       NULL |   NULL | NULL                                                                     |  NULL
 10 |      0 |        0 |      0 |   NULL |   NULL |     NULL | NULL        |        NULL |       NULL |   NULL | NULL                                                                     |  NULL
 11 |      0 |        0 |      0 |   NULL |   NULL |     NULL | NULL        |        NULL |       NULL |   NULL | NULL                                                                     |  NULL
 12 |   1568 |        1 |   1338 |      2 |      0 |        0 | (100009,12) |          13 |      10503 |     32 | 111110111111100000000000000000000000000000000000000000000000000000000000 |  NULL
 13 |   1352 |        1 |    216 |      2 |      0 |        0 | (100009,13) |       32781 |      10499 |     32 | 111110100111000000000000000000000000000000000000000000000000000000000000 |  NULL
 14 |   1104 |        1 |    248 |      2 |      0 |        0 | (100009,14) |       32781 |      10503 |     32 | 111111111111000000000000000000000000000000000000000000000000000000000000 |  NULL
 15 |    848 |        1 |    256 |      2 |      0 |        0 | (100009,15) |       32781 |      10503 |     32 | 111111111111000000000000000000000000000000000000000000000000000000000000 |  NULL
 16 |    584 |        1 |    264 |      2 |      0 |        0 | (100009,16) |       32781 |      10503 |     32 | 111111111111000000000000000000000000000000000000000000000000000000000000 |  NULL
(16 rows)

Time: 2.102 ms

Почему не заполняются пустые строки в страницах, а вместо этого сервер требует нового места на файловой системе?

Версия 9.1.

Простой крайне не желателен. Т.е остановить запросы, сделать дамп и восстановить с дампа (на обновленную версию) не дадут.

...

Рейтинг:

0 / 0

25.02.2019, 17:50

| Ответить | Цитировать | Написать

После Vacuum (autovacuum) таблица продолжает расти

#39778948

maxski

Участник

Откуда: Москва-Лимассол

Сообщения: 154

Рейтинг: 0 / 0

Вообщем, задача:

Как остановить рост размера бд, ввиду свободного места в таблицах - это по минимуму.
В идеале сжать таблицы, вернув место файловой системе.

Есть ли какие-н идеи? )))

...

Рейтинг:

0 / 0

25.02.2019, 17:54

| Ответить | Цитировать | Написать

После Vacuum (autovacuum) таблица продолжает расти

#39779124

gav21

Участник

Сообщения: 126

Рейтинг: 0 / 0

maxski,

1.включите логирование автовакума (log_autovacuum_min_duration=0), и посмотрите реально сколько он вычищает страниц.
2. может у вас длинные транзакции \ idle_in_transaction сессии которые ему мешают работать.
3. включенный hot_standby_feedback на реплике, на которой работают длинные \ idle_in_transaction сессии, которые также могут мешать работе АВ

...

Рейтинг:

0 / 0

26.02.2019, 09:12

| Ответить | Цитировать | Написать

После Vacuum (autovacuum) таблица продолжает расти

#39779128

gav21

Участник

Сообщения: 126

Рейтинг: 0 / 0

maxski,
Увидел, АВ работает судя по n_dead_tup
Может растет не таблица а индексы в ней? Блоатинг проверяли по индексам?

...

Рейтинг:

0 / 0

26.02.2019, 09:23

| Ответить | Цитировать | Написать

После Vacuum (autovacuum) таблица продолжает расти

#39779150

maxski

Участник

Откуда: Москва-Лимассол

Сообщения: 154

Рейтинг: 0 / 0

gav21,

Да, действительно индексы распухли:

Код: plsql

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.

 schemaname |                          tablename                          |                            iname                            | totalwastedbytes 
------------+-------------------------------------------------------------+-------------------------------------------------------------+------------------
 public     | attachments                                                 | attachments1_                                               |       3517489152
 public     | attachments                                                 | attachments_id_where_contentinde_not_null_content_type_text |       3517489152
 public     | attachments                                                 | attachments3_                                               |       3517489152
 public     | attachments                                                 | attachments_pkey                                            |       3517489152
 public     | attachments                                                 | contentindex_idx_                                           |       3517489152
 public     | attachments                                                 | attachments2_                                               |       3517489152
 public     | groups                                                      | idx_groups_instance_domain_type                             |        730185728
 public     | cachedgroupmembers                                          | disgroumem                                                  |        273481728
 public     | groups                                                      | groups1_                                                    |        214605824
 public     | cachedgroupmembers                                          | idx_via_id                                                  |        214163456
 public     | cachedgroupmembers                                          | cachedgroupmembers_pkey                                     |        214163456
 public     | cachedgroupmembers                                          | cgm_immediateparentid_memberid_idx                          |        214163456
 public     | cachedgroupmembers                                          | cachedgroupmembers3_                                        |        214163456
 public     | cachedgroupmembers                                          | cachedgroupmembers2                                         |        214163456
 public     | principals                                                  | principals2                                                 |         82829312
 public     | principals                                                  | principals_pkey                                             |         82829312
 public     | tickets                                                     | idx_tickets_id_owner_nobody                                 |         52813824
 public     | tickets                                                     | idx_tickets_id_where_status_not_deleted_                    |         44187648
 public     | tickets                                                     | tickets2_                                                   |         44187648
 public     | tickets                                                     | tickets1_                                                   |         44187648
 public     | tickets                                                     | idx_tickets_id_status_queue_type_effectiveid_               |         44187648
 public     | tickets                                                     | tickets_pkey                                                |         44187648
 public     | tickets                                                     | tickets5_                                                   |         44187648
 public     | tickets                                                     | tickets4_                                                   |         44187648
 public     | tickets                                                     | tickets3_                                                   |         44187648
 public     | tickets                                                     | idx_status_where_not_deleted                                |         44187648
 public     | groupmembers                                                | idx_memberid                                                |         10805248
 public     | groupmembers                                                | groupmembers1                                               |         10805248
 public     | groupmembers                                                | groupmembers_pkey                                           |         10805248
 public     | sessions                                                    | sessions_pkey                                               |           835584
 public     | links                                                       | links1                                                      |           598016
 public     | links                                                       | links_pkey                                                  |           598016
 public     | links                                                       | links4                                                      |           598016
 public     | acl                                                         | acl1                                                        |           188416

Похоже у 9.1 нет опции 'rebuild online'?

...

Рейтинг:

0 / 0

26.02.2019, 10:00

| Ответить | Цитировать | Написать

После Vacuum (autovacuum) таблица продолжает расти

#39779187

maxski

Участник

Откуда: Москва-Лимассол

Сообщения: 154

Рейтинг: 0 / 0

После автовакуума, размер "распухания" индексов уменьшается, но не до конца:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.

 schemaname |                           tablename                            |                             iname                              | totalwastedbytes 
------------+----------------------------------------------------------------+----------------------------------------------------------------+------------------
 public     | attachments                                                    | contentindex_idx_                                              |       1868824576
 public     | attachments                                                    | attachments_pkey                                               |       1868824576
 public     | attachments                                                    | attachments_id_where_contentinde_not_null_content_type_text    |       1868824576
 public     | attachments                                                    | attachments1_                                                  |       1868824576
 public     | attachments                                                    | attachments2_                                                  |       1868824576
 public     | attachments                                                    | attachments_id_where_contentinde_not_null_content_type_text_v2 |       1868824576
 public     | attachments                                                    | attachments3_                                                  |       1868824576

Создал вторую версию индекса с опцией CONCURRENTLY. Постгрес взял и создал копию индекса, т.е. он не читал таблицу и не строил по ней индекс.

...

Рейтинг:

0 / 0

26.02.2019, 10:47

| Ответить | Цитировать | Написать

После Vacuum (autovacuum) таблица продолжает расти

#39779194

maxski

Участник

Откуда: Москва-Лимассол

Сообщения: 154

Рейтинг: 0 / 0

gav21,

чего-то все равно не так. Индексы чистятся автовакуумом. А пустые места в страницах не заполняются.

Может нужно как-то пересоздать Visibility Map и(или) Free Space Map?

...

Рейтинг:

0 / 0

26.02.2019, 11:00

| Ответить | Цитировать | Написать

После Vacuum (autovacuum) таблица продолжает расти

#39779197

Melkij

Участник

Откуда: Санкт-Петербург

Сообщения: 1 447

Рейтинг: 0 / 0

maxskiПосле автовакуума, размер "распухания" индексов уменьшается
Автовакуум не может уменьшать индекс. Вычищать и отдавать страницы в переиспользование - это возможно например для btree. Возвращать место ОС - нет.

maxskiПостгрес взял и создал копию индекса, т.е. он не читал таблицу и не строил по ней индекс.
Не умеет.
Построение индекса всегда идёт с нуля по таблице, что для create index, что create index concurrently, что reindex, vacuum full или некоторые формы alter table.

maxskiТ.е остановить запросы, сделать дамп и восстановить с дампа (на обновленную версию) не дадут.
А зачем вам дамп данных для pg_upgrade?

maxskiПохоже у 9.1 нет опции 'rebuild online'?
Нет такой опции. Если повезёт - сделаем в pg12. По крайней мере мне более сломать этот патч не удалось.

...

Рейтинг:

0 / 0

26.02.2019, 11:02

| Ответить | Цитировать | Написать

После Vacuum (autovacuum) таблица продолжает расти

#39779232

gav21

Участник

Сообщения: 126

Рейтинг: 0 / 0

maxski,

вот тут подробно разжевали механизм блоатинга индексов
https://www.sql.ru/forum/1290216/puhnet-indeks?hl=

...

Рейтинг:

0 / 0

26.02.2019, 12:04

| Ответить | Цитировать | Написать

После Vacuum (autovacuum) таблица продолжает расти

#39779262

maxski

Участник

Откуда: Москва-Лимассол

Сообщения: 154

Рейтинг: 0 / 0

MelkijmaxskiПосле автовакуума, размер "распухания" индексов уменьшается
Автовакуум не может уменьшать индекс. Вычищать и отдавать страницы в переиспользование - это возможно например для btree. Возвращать место ОС - нет.

Я имел в виду вот это "totalwastedbytes" в выводе выше

MelkiНе умеет.
Построение индекса всегда идёт с нуля по таблице, что для create index, что create index concurrently, что reindex, vacuum full или некоторые формы alter table.

Вот создал за 65 секунд. Таблица 'attachments' под 2 ТБ. Что он по ней за 65 секунд построил? )))
Он мог только скопировать другой индекс за это время. Или вы другое имели в виду? ))

Код: plsql

1.
2.
3.

(postgres@[local]:5432) [rtdb] > CREATE INDEX CONCURRENTLY attachments_id_where_contentinde_not_null_content_type_text_v2 ON attachments USING btree (id DESC NULLS LAST) WHERE ((contentindex IS NOT NULL) AND ((contenttype)::text = 'text/plain'::text));
CREATE INDEX
Time: 65575.659 ms

MelkijА зачем вам дамп данных для pg_upgrade?

c pg_upgrade попробую разобраться ))

...

Рейтинг:

0 / 0

26.02.2019, 13:09

| Ответить | Цитировать | Написать

После Vacuum (autovacuum) таблица продолжает расти

#39779268

Melkij

Участник

Откуда: Санкт-Петербург

Сообщения: 1 447

Рейтинг: 0 / 0

maxskiMelkijпропущено...

Автовакуум не может уменьшать индекс. Вычищать и отдавать страницы в переиспользование - это возможно например для btree. Возвращать место ОС - нет.

Я имел в виду вот это "totalwastedbytes" в выводе выше
Ни малейшей идеи что бы это могло значить.

Код: plaintext

1.
2.
3.

melkij@melkij:~/tmp/srcs/postgresql$ grep -ri totalwastedbytes | wc -l
0
melkij@melkij:~/tmp/srcs/postgresql$ git branch 
* (HEAD отделён на REL9_1_24)

В ядре такого нет, так что что вы там считаете - не представляю.

maxskiВот создал за 65 секунд. Таблица 'attachments' под 2 ТБ. Что он по ней за 65 секунд построил? )))
Он мог только скопировать другой индекс за это время. Или вы другое имели в виду? ))
Покажите pg_size_pretty(pg_table_size(pg_class.oid)) AS size, pg_size_pretty(pg_indexes_size(pg_class.oid)) AS idxsize, pg_size_pretty(pg_total_relation_size(pg_class.oid)) as "total" для этой таблицы.
Как вы изначально привели n_live_tup в 30 лямов - это вполне можно пройти за 65 секунд.

...

Рейтинг:

0 / 0

26.02.2019, 13:19

| Ответить | Цитировать | Написать

После Vacuum (autovacuum) таблица продолжает расти

#39779390

maxski

Участник

Откуда: Москва-Лимассол

Сообщения: 154

Рейтинг: 0 / 0

MelkijВ ядре такого нет, так что что вы там считаете - не представляю.

так вот же, в "выводе выше 21819384
Я не стал там приводить весь скрипт (на 1.5 экрана), чтобы не отвлекать внимание. Но вообщем, "totalwastedbytes" - это степень "пухлости". И я там нигде не говорил, что автовакуум возвращает место файловой системе.

MelkijmaxskiВот создал за 65 секунд. Таблица 'attachments' под 2 ТБ. Что он по ней за 65 секунд построил? )))
Он мог только скопировать другой индекс за это время. Или вы другое имели в виду? ))
Покажите pg_size_pretty(pg_table_size(pg_class.oid)) AS size, pg_size_pretty(pg_indexes_size(pg_class.oid)) AS idxsize, pg_size_pretty(pg_total_relation_size(pg_class.oid)) as "total" для этой таблицы.
Как вы изначально привели n_live_tup в 30 лямов - это вполне можно пройти за 65 секунд.

Привожу: весь размер таблицы оказался в pg_toast_*

Код: plsql

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.
40.
41.
42.
43.
44.
45.
46.
47.

SELECT  relname AS "relation",
    pg_size_pretty(pg_relation_size(pg_class.oid)) AS "size", pg_size_pretty(pg_indexes_size(pg_class.oid)) AS idxsize, pg_size_pretty(pg_total_relation_size(pg_class.oid)) as "total"
  FROM pg_class 
  ORDER BY pg_relation_size(pg_class.oid) DESC
  LIMIT 40;
                          relation                           |  size   | idxsize |  total  
-------------------------------------------------------------+---------+---------+---------
 pg_toast_16388                                              | 2566 GB | 29 GB   | 2596 GB
 attachments                                                 | 36 GB   | 23 GB   | 2654 GB
 pg_toast_16388_index                                        | 29 GB   | 0 bytes | 29 GB
 contentindex_idx_                                           | 19 GB   | 0 bytes | 19 GB
 transactions                                                | 6031 MB | 4823 MB | 11 GB
 groups                                                      | 3174 MB | 10 GB   | 13 GB
 idx_groups_instance_domain_type                             | 2791 MB | 0 bytes | 2791 MB
 cachedgroupmembers                                          | 2722 MB | 8070 MB | 11 GB
 groups1_                                                    | 2299 MB | 0 bytes | 2299 MB
 tickets                                                     | 2004 MB | 3075 MB | 5079 MB
 disgroumem                                                  | 1800 MB | 0 bytes | 1800 MB
 groups2_                                                    | 1785 MB | 0 bytes | 1785 MB
 principals                                                  | 1755 MB | 1560 MB | 3316 MB
 pg_toast_16691                                              | 1663 MB | 93 MB   | 1757 MB
 transactions_pkey                                           | 1493 MB | 0 bytes | 1493 MB
 transactions_objectid_idx_                                  | 1490 MB | 0 bytes | 1490 MB
 idx_groups_domain_type_                                     | 1428 MB | 0 bytes | 1428 MB
 cachedgroupmembers2                                         | 1386 MB | 0 bytes | 1386 MB
 idx_groups_domain_name                                      | 1357 MB | 0 bytes | 1357 MB
 cachedgroupmembers_pkey                                     | 1295 MB | 0 bytes | 1295 MB
 cgm_immediateparentid_memberid_idx                          | 1222 MB | 0 bytes | 1222 MB
 attachments_pkey                                            | 1216 MB | 0 bytes | 1216 MB
 cachedgroupmembers3_                                        | 1191 MB | 0 bytes | 1191 MB
 idx_via_id                                                  | 1176 MB | 0 bytes | 1176 MB
 groupmembers                                                | 1111 MB | 1328 MB | 2438 MB
 transactions_id_object_type_where_objecttype_rt_ticket      | 1066 MB | 0 bytes | 1066 MB
 attachments3_                                               | 872 MB  | 0 bytes | 872 MB
 principals_pkey                                             | 838 MB  | 0 bytes | 838 MB
 attachments1_                                               | 824 MB  | 0 bytes | 824 MB
 attachments2_                                               | 791 MB  | 0 bytes | 791 MB
 transactions_id_where_objecttype                            | 775 MB  | 0 bytes | 775 MB
 groups_pkey                                                 | 722 MB  | 0 bytes | 722 MB
 principals2                                                 | 722 MB  | 0 bytes | 723 MB
 tickets_pkey                                                | 634 MB  | 0 bytes | 634 MB
 idx_tickets_id_status_queue_type_effectiveid_               | 598 MB  | 0 bytes | 598 MB
 groupmembers1                                               | 521 MB  | 0 bytes | 521 MB
 groupmembers_pkey                                           | 430 MB  | 0 bytes | 430 MB
 idx_memberid                                                | 377 MB  | 0 bytes | 377 MB
 tickets4_                                                   | 358 MB  | 0 bytes | 358 MB
 attachments_id_where_contentinde_not_null_content_type_text | 316 MB  | 0 bytes | 316 MB

И определение таблички:

Код: plsql

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.

(postgres@[local]:5432) [rtdb] > \d+ attachments
                                                            Table "public.attachments"
     Column      |            Type             |                        Modifiers                         | Storage  | Stats target | Description 
-----------------+-----------------------------+----------------------------------------------------------+----------+--------------+-------------
 id              | integer                     | not null default nextval('attachments_id_seq'::regclass) | plain    |              | 
 transactionid   | integer                     | not null                                                 | plain    |              | 
 parent          | integer                     | not null default 0                                       | plain    |              | 
 messageid       | character varying(160)      |                                                          | extended |              | 
 subject         | character varying(255)      |                                                          | extended |              | 
 filename        | character varying(255)      |                                                          | extended |              | 
 contenttype     | character varying(80)       |                                                          | extended |              | 
 contentencoding | character varying(80)       |                                                          | extended |              | 
 content         | text                        |                                                          | extended |              | 
 headers         | text                        |                                                          | extended |              | 
 creator         | integer                     | not null default 0                                       | plain    |              | 
 created         | timestamp without time zone |                                                          | plain    |              | 
 contentindex    | tsvector                    |                                                          | extended |              | 
Indexes:
    "attachments_pkey" PRIMARY KEY, btree (id)
    "attachments1_" btree (parent), tablespace "tbs1"
    "attachments2_" btree (transactionid), tablespace "tbs1"
    "attachments3_" btree (parent, transactionid), tablespace "tbs1"
    "attachments_id_where_contentinde_not_null_content_type_text" btree (id DESC NULLS LAST) WHERE contentindex IS NOT NULL AND contenttype::text = 'text/plain'::text
    "contentindex_idx_" gin (contentindex), tablespace "tbs1"
Has OIDs: no
Options: autovacuum_vacuum_scale_factor=0.0003, autovacuum_analyze_scale_factor=0.003

...

Рейтинг:

0 / 0

26.02.2019, 15:51

| Ответить | Цитировать | Написать

После Vacuum (autovacuum) таблица продолжает расти

#39779454

maxski

Участник

Откуда: Москва-Лимассол

Сообщения: 154

Рейтинг: 0 / 0

MelkijА зачем вам дамп данных для pg_upgrade?

Для pg_upgrade надо останавливать instance.

Выше уже говорилось, что "Простой крайне не желателен."

...

Рейтинг:

0 / 0

26.02.2019, 17:16

| Ответить | Цитировать | Написать

После Vacuum (autovacuum) таблица продолжает расти

#39779477

Melkij

Участник

Откуда: Санкт-Петербург

Сообщения: 1 447

Рейтинг: 0 / 0

maxskiВыше уже говорилось, что "Простой крайне не желателен."
Раз в год планово на 10 минут можно остановить что угодно.
Потому что если нельзя - сделайте чтобы было можно. Раз уж вы готовы к затратам на столько девяток.

maxskiПривожу: весь размер таблицы оказался в pg_toast_*
Ожидаемо.
Вот для toast и смотрите, есть ли там пустое место, а не в attachments

...

Рейтинг:

0 / 0

26.02.2019, 17:40

| Ответить | Цитировать | Написать

После Vacuum (autovacuum) таблица продолжает расти

#39779492

maxski

Участник

Откуда: Москва-Лимассол

Сообщения: 154

Рейтинг: 0 / 0

Melkij,

в pg_toast лезет vacuum (по таблице). И там ничего особо свободного нет:

Я выполнял это ранее:

Код: plsql

(postgres@[local]:5432) [rtdb] > vacuum verbose attachments;
INFO:  vacuuming "public.attachments"
INFO:  scanned index "attachments_pkey" to remove 1765 row versions
DETAIL:  CPU 0.76s/1.80u sec elapsed 12.59 sec.
INFO:  scanned index "attachments_id_where_contentinde_not_null_content_type_text" to remove 1765 row versions
DETAIL:  CPU 0.13s/0.54u sec elapsed 0.77 sec.
INFO:  scanned index "attachments2_" to remove 1765 row versions
DETAIL:  CPU 0.49s/1.71u sec elapsed 7.30 sec.
INFO:  scanned index "attachments1_" to remove 1765 row versions
DETAIL:  CPU 0.42s/1.70u sec elapsed 10.39 sec.
INFO:  scanned index "attachments3_" to remove 1765 row versions
DETAIL:  CPU 0.49s/1.83u sec elapsed 9.84 sec.
INFO:  scanned index "contentindex_idx_" to remove 1765 row versions
DETAIL:  CPU 8.79s/91.96u sec elapsed 143.58 sec.
INFO:  "attachments": removed 1765 row versions in 794 pages
DETAIL:  CPU 0.00s/0.01u sec elapsed 0.05 sec.
INFO:  index "attachments_pkey" now contains 31838952 row versions in 155582 pages
DETAIL:  1309 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "attachments_id_where_contentinde_not_null_content_type_text" now contains 9723683 row versions in 40455 pages
DETAIL:  269 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "attachments2_" now contains 31838955 row versions in 101159 pages
DETAIL:  1757 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "attachments1_" now contains 31838955 row versions in 105381 pages
DETAIL:  1765 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "attachments3_" now contains 31838955 row versions in 111450 pages
DETAIL:  1765 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "contentindex_idx_" now contains 30057889 row versions in 2458699 pages
DETAIL:  106376 index row versions were removed.
214 index pages have been deleted, 882 are currently reusable.
CPU 5.79s/2.56u sec elapsed 10.03 sec.
INFO:  "attachments": found 0 removable, 17106 nonremovable row versions in 2843 out of 4686545 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 4868 unused item pointers.
0 pages are entirely empty.
CPU 16.92s/102.25u sec elapsed 194.70 sec.
INFO:  vacuuming "pg_toast.pg_toast_16388"
INFO:  scanned index "pg_toast_16388_index" to remove 1163 row versions
DETAIL:  CPU 19.02s/75.70u sec elapsed 475.68 sec.
INFO:  "pg_toast_16388": removed 1163 row versions in 294 pages
DETAIL:  CPU 0.01s/0.00u sec elapsed 0.22 sec.
INFO:  index "pg_toast_16388_index" now contains 1355089027 row versions in 3733038 pages
DETAIL:  1163 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.01s/0.00u sec elapsed 0.16 sec.
INFO:  "pg_toast_16388": found 1108 removable, 60332 nonremovable row versions in 15207 out of 336068358 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 30 unused item pointers.
0 pages are entirely empty.
CPU 19.18s/80.65u sec elapsed 481.66 sec.
VACUUM
Time: 688993.666 ms

Если убрать ограничение schemaname='public', то появляется pg_toast со своими жалкими 338 dead tuples:

Код: plsql

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.

(postgres@[local]:5432) [rtdb] > SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum, last_vacuum
FROM pg_stat_all_tables 
ORDER BY n_dead_tup
    / (n_live_tup
       * current_setting('autovacuum_vacuum_scale_factor')::float8
          + current_setting('autovacuum_vacuum_threshold')::float8)
     DESC
LIMIT 20;
 schemaname |      relname       | n_live_tup | n_dead_tup |        last_autovacuum        |          last_vacuum          
------------+--------------------+------------+------------+-------------------------------+-------------------------------
 pg_toast   | pg_toast_2619      |         33 |         32 | 2019-02-26 15:29:53.804574+02 | 2019-02-26 00:03:02.482548+02
 pg_catalog | pg_statistic       |        865 |         97 | 2019-02-26 00:25:25.369116+02 | 2019-02-26 00:03:02.297599+02
 pg_catalog | pg_index           |        195 |          2 | NULL                          | 2019-02-26 00:03:02.785048+02
 pg_catalog | pg_class           |        439 |          2 | NULL                          | 2019-02-26 00:03:02.72939+02
 public     | users              |       3129 |          9 | NULL                          | 2019-02-26 00:19:41.194727+02
 pg_catalog | pg_attribute       |       3349 |          6 | NULL                          | 2019-02-26 00:03:02.567422+02
 pg_catalog | pg_depend          |       6398 |          8 | NULL                          | 2019-02-26 00:03:03.214598+02
 pg_catalog | pg_proc            |       2362 |          2 | NULL                          | 2019-02-26 00:03:02.6413+02
 pg_toast   | pg_toast_16691     |     616083 |        459 | 2019-02-26 17:03:03.063998+02 | 2019-02-26 00:19:43.282626+02
 public     | sessions           |     121745 |         61 | 2019-02-26 16:51:41.825151+02 | 2019-02-26 00:19:42.837306+02
 public     | cachedgroupmembers |   50657089 |      11313 | 2019-02-26 09:05:51.345983+02 | 2019-02-26 00:04:51.930723+02
 public     | attachments        |   30431981 |       6030 | 2019-02-26 06:14:25.75911+02  | 2019-02-26 00:07:40.555306+02
 public     | attributes         |      15237 |          2 | NULL                          | 2019-02-26 00:19:41.633998+02
 public     | tickets            |    8424092 |        332 | 2019-02-26 17:02:39.110509+02 | 2019-02-26 00:04:30.830933+02
 public     | principals         |   33704579 |        539 | 2019-02-26 16:09:10.195494+02 | 2019-02-26 00:03:46.398902+02
 public     | groupmembers       |   16932710 |        189 | NULL                          | 2019-02-26 00:20:04.626071+02
 public     | transactions       |   66894205 |        676 | 2019-02-25 22:53:14.030828+02 | 2019-02-26 00:03:37.803715+02
 pg_toast   | pg_toast_16388     | 1351944425 |        338 | NULL                          | 2019-02-26 00:17:16.032376+02
 public     | groups             |   33703502 |          2 | NULL                          | 2019-02-26 00:24:09.46511+02
 pg_catalog | pg_language        |          4 |          0 | NULL                          | 2019-02-26 00:03:02.891071+02
(20 rows)

Time: 12.541 ms

...

Рейтинг:

0 / 0

26.02.2019, 18:18

| Ответить | Цитировать | Написать

После Vacuum (autovacuum) таблица продолжает расти

#39779734

Alexius

Участник

Сообщения: 749

Рейтинг: 0 / 0

maxski,

попробуйте посмотреть более корректную оценку bloat в attachments запросом:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.

select nspname,
relname,
pg_size_pretty(relation_size + toast_relation_size) as total_size,
pg_size_pretty(toast_relation_size) as toast_size,
round(((relation_size - (relation_size - free_space)*100/fillfactor)*100/greatest(relation_size, 1))::numeric, 1) table_waste_percent,
pg_size_pretty((relation_size - (relation_size - free_space)*100/fillfactor)::bigint) table_waste,
round(((toast_free_space + relation_size - (relation_size - free_space)*100/fillfactor)*100/greatest(relation_size + toast_relation_size, 1))::numeric, 1) total_waste_percent,
pg_size_pretty((toast_free_space + relation_size - (relation_size - free_space)*100/fillfactor)::bigint) total_waste
from (
    select nspname, relname,
    (select free_space from pgstattuple(c.oid)) as free_space,
    pg_relation_size(c.oid) as relation_size,
    (case when reltoastrelid = 0 then 0 else (select free_space from pgstattuple(c.reltoastrelid)) end) as toast_free_space,
    coalesce(pg_relation_size(c.reltoastrelid), 0) as toast_relation_size,
    coalesce((SELECT (regexp_matches(reloptions::text, E'.*fillfactor=(\\d+).*'))[1]),'100')::real AS fillfactor
    from pg_class c
    left join pg_namespace n on (n.oid = c.relnamespace)
    where nspname not in ('pg_catalog', 'information_schema')
    and nspname !~ '^pg_toast' and nspname !~ '^pg_temp' and relkind in ('r', 'm') and (relpersistence = 'p' or not pg_is_in_recovery())
    --put your table name/mask here
    and relname ~ 'attachments'
) t
order by (toast_free_space + relation_size - (relation_size - free_space)*100/fillfactor) desc
limit 20;

скорей всего bloat там нет заметного судя по выводу вакуума и наблюдаемому росту размеров таблицы. т.е. пишутся какие-то длинные строки в тосты. для запроса нужен экстеншн pgstattuple (есть в контрибе) и запрос потребует полного чтения этих почти 3ТБ (т.е. это будет долго и нагрузка на диски будет).

покажите еще вывод

Код: sql

1.
2.

select attname, avg_width, null_frac from pg_stats where tablename = 'attachments' order by 2 desc;
select * from pg_stat_user_tables where relname = 'attachments';

...

Рейтинг:

0 / 0

27.02.2019, 12:56

| Ответить | Цитировать | Написать

После Vacuum (autovacuum) таблица продолжает расти

#39779931

maxski

Участник

Откуда: Москва-Лимассол

Сообщения: 154

Рейтинг: 0 / 0

Alexius,

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.

(postgres@[local]:5432) [rtdb] > select attname, avg_width, null_frac from pg_stats where tablename = 'attachments' order by 2 desc;
     attname     | avg_width | null_frac 
-----------------+-----------+-----------
 headers         |       526 |         0
 content         |       344 |  0.173267
 contentindex    |       258 |    0.3294
 subject         |        33 |         0
 messageid       |        27 |         0
 filename        |        17 |     0.844
 contenttype     |        12 |         0
 contentencoding |         9 |  0.173267
 created         |         8 |         0
 creator         |         4 |         0
 id              |         4 |         0
 parent          |         4 |         0
 transactionid   |         4 |         0
(13 rows)

Time: 11.177 ms
(postgres@[local]:5432) [rtdb] > select * from pg_stat_user_tables where relname = 'attachments';
 relid | schemaname |   relname   | seq_scan | seq_tup_read  | idx_scan  | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup |          last_vacuum          |        last_autovacuum        |         last_analyze          |       last_autoanalyze        | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count 
-------+------------+-------------+----------+---------------+-----------+---------------+-----------+-----------+-----------+---------------+------------+------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+--------------+------------------+---------------+-------------------
 16388 | public     | attachments |   248681 | 6703384021494 | 150916989 | 2019745856451 |  12270764 |  11426155 |    943377 |       1646805 |   31867655 |       1362 | 2019-02-27 00:06:53.808098+02 | 2019-02-27 14:22:12.054521+02 | 2019-02-27 00:19:04.587021+02 | 2019-02-27 14:28:02.701592+02 |         1559 |               36 |          1554 |                78
(1 row)

А первый запрос еще не выполнился.

...

Рейтинг:

0 / 0

27.02.2019, 15:50

| Ответить | Цитировать | Написать

После Vacuum (autovacuum) таблица продолжает расти

#39780104

maxski

Участник

Откуда: Москва-Лимассол

Сообщения: 154

Рейтинг: 0 / 0

Alexius,

Первый запрос выполнился:

Код: plsql

1.
2.
3.
4.
5.
6.

 nspname |   relname   | total_size | toast_size | table_waste_percent | table_waste | total_waste_percent | total_waste 
---------+-------------+------------+------------+---------------------+-------------+---------------------+-------------
 public  | attachments | 2604 GB    | 2568 GB    |                 4.6 | 1699 MB     |                 0.5 | 12 GB
(1 row)

Time: 17063993.135 ms

...

Рейтинг:

0 / 0

27.02.2019, 20:41

| Ответить | Цитировать | Написать

После Vacuum (autovacuum) таблица продолжает расти

#39780109

maxski

Участник

Откуда: Москва-Лимассол

Сообщения: 154

Рейтинг: 0 / 0

Alexius,

cмысл в том, что страницы полупустые:

Код: plsql

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.

(postgres@[local]:5432) [rtdb] > SELECT *  FROM pg_freespace('attachments') where avail between 4000 and 5000 limit 10;
  blkno  | avail 
---------+-------
 4671993 |  4192
 4690938 |  4160
 4691444 |  4736
 4691478 |  4800
 4691499 |  4448
 4691635 |  4064
 4691719 |  4128
 4691823 |  4512
 4691849 |  4672
 4691853 |  4384
(10 rows)

Time: 4295.628 ms

(postgres@[local]:5432) [rtdb] > SELECT * FROM heap_page_items(get_raw_page('attachments',4691444));
 lp | lp_off | lp_flags | lp_len |  t_xmin   | t_xmax | t_field3 |   t_ctid    | t_infomask2 | t_infomask | t_hoff |                                  t_bits                                  | t_oid 
----+--------+----------+--------+-----------+--------+----------+-------------+-------------+------------+--------+--------------------------------------------------------------------------+-------
  1 |      0 |        0 |      0 |      NULL |   NULL |     NULL | NULL        |        NULL |       NULL |   NULL | NULL                                                                     |  NULL
  2 |      0 |        0 |      0 |      NULL |   NULL |     NULL | NULL        |        NULL |       NULL |   NULL | NULL                                                                     |  NULL
  3 |      0 |        0 |      0 |      NULL |   NULL |     NULL | NULL        |        NULL |       NULL |   NULL | NULL                                                                     |  NULL
  4 |   6168 |        1 |   2018 | 330603866 |      0 |        0 | (4691444,4) |          13 |      10503 |     32 | 111110111111100000000000000000000000000000000000000000000000000000000000 |  NULL
  5 |      0 |        0 |      0 |      NULL |   NULL |     NULL | NULL        |        NULL |       NULL |   NULL | NULL                                                                     |  NULL
  6 |   4808 |        1 |   1360 | 330607737 |      0 |       33 | (4691444,6) |          13 |       2307 |     32 | 111110100111000000000000000000000000000000000000000000000000000000000000 |  NULL
  7 |      0 |        0 |      0 |      NULL |   NULL |     NULL | NULL        |        NULL |       NULL |   NULL | NULL                                                                     |  NULL
(7 rows)

Time: 8.836 ms

И что эти полупустые страницы числятся как занятые. Вот в этом вопрос.

...

Рейтинг:

0 / 0

27.02.2019, 20:53

| Ответить | Цитировать | Написать

После Vacuum (autovacuum) таблица продолжает расти

#39780115

Alexius

Участник

Сообщения: 749

Рейтинг: 0 / 0

maxski,

полупустые страницы в самой таблице, и не так их и много, суммарно 4.6% пустого места. учитывая что toast'ов почти на 2 порядка больше, это как-то вообще не должно волновать. а в toast таблице 0.5% свободного места. т.е. сжимать тут нечего совсем.

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

...

Рейтинг:

0 / 0

27.02.2019, 21:21

| Ответить | Цитировать | Написать

После Vacuum (autovacuum) таблица продолжает расти

#39780204

maxski

Участник

Откуда: Москва-Лимассол

Сообщения: 154

Рейтинг: 0 / 0

Alexius,
Интересно vacuum full attachments будет перемещать строки заполняя полупустые страницы или будет перемещать в новое место?

...

Рейтинг:

0 / 0

28.02.2019, 10:05

| Ответить | Цитировать | Написать

После Vacuum (autovacuum) таблица продолжает расти

#39780254

Melkij

Участник

Откуда: Санкт-Петербург

Сообщения: 1 447

Рейтинг: 0 / 0

maxski,

vacuum full сделает копию таблицы, затем удалит старую.

Повторю ещё раз: то что вы сожмёте саму attachments размером в 36гб никак вам не поможет с её toast частью, где неиспользуемого места почти нет и потому база запрашивает новое место на диске у ОС.

...

Рейтинг:

0 / 0

28.02.2019, 11:26

| Ответить | Цитировать | Написать

После Vacuum (autovacuum) таблица продолжает расти

#39780396

maxski

Участник

Откуда: Москва-Лимассол

Сообщения: 154

Рейтинг: 0 / 0

Melkijmaxski,
....

Повторю ещё раз: то что вы сожмёте саму attachments размером в 36гб никак вам не поможет с её toast частью, где неиспользуемого места почти нет и потому база запрашивает новое место на диске у ОС.

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

Код: plsql

 SELECT count(1)  FROM pg_freespace('attachments') where avail between var1 and var2

Выяснил, что от attachments (36GB) - примерно 30% незаполнено, т.е те же 12GB, что потом (вчера) подтвердил мега-запрос на полдня исполнения.
Что про это повторять? Я же про это не спрашивал.
Оракл может перемещать строки в блоках. Хотя быстрее можно ужать через смену tablespace:

Код: plsql

alter table .. move tablespace <другой tablespace> ...

...

Рейтинг:

0 / 0

28.02.2019, 14:19

| Ответить | Цитировать | Написать

После Vacuum (autovacuum) таблица продолжает расти

#39780402

Melkij

Участник

Откуда: Санкт-Петербург

Сообщения: 1 447

Рейтинг: 0 / 0

maxski,

тогда зачем спрашивать про vacuum full?

...

Рейтинг:

0 / 0

28.02.2019, 14:27

| Ответить | Цитировать | Написать

После Vacuum (autovacuum) таблица продолжает расти

#39780469

maxski

Участник

Откуда: Москва-Лимассол

Сообщения: 154

Рейтинг: 0 / 0

Melkij,

"Интересно vacuum full attachments будет перемещать строки заполняя полупустые страницы или будет перемещать в новое место?"

Что такого в этом вопросе? Его нельзя задавать? Я же привел пример сравнения с Оракл в аналогичной ситуации.

...

Рейтинг:

0 / 0

28.02.2019, 15:40

| Ответить | Цитировать | Написать

25 сообщений из 29, страница 1 из 2

все

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / После Vacuum (autovacuum) таблица продолжает расти

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=53&fpage=24&tid=1994518]:	0ms
get settings:	10ms
get forum list:	14ms
check forum access:	4ms
check topic access:	4ms
track hit:	39ms
get topic data:	14ms
get forum data:	3ms
get page messages:	98ms
get tp. blocked users:	2ms
others:	70ms

total:	258ms