Привет всем! подскажите пожалуйста, есть ли возможность ускорить процесс обновления

ссылок в таблице links?
Задача следующая, есть файл, в котором содержатся ссылки вида: old_url и new_url их в общей сложности 6000 ссылок. Следующим запросом в скрипте я их обновляю:

psql -h "$db_host" -p "$db_port" -d "$db_name" -U "$db_user" -c "UPDATE \"$table\" SET \"$column\" = REPLACE(\"$column\", '$old_url', '$new_url
') WHERE \"$column\" ~ '$old_url(\W|$)';"

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

если у тебя есть конкретный список, чего надо обноить, то и делай update links set link=newlink where link=oldlink

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

А индекс есть?

можно индекс на поле добавить. вопрос только в количестве данных в таблице.

6 к вроде по условию

сам я индекс не создавал, это сервис confluence. данных в таблице много.

это изменений, а в таблице может быть и больше. на 6к не должно быть таких тормозов

А шо там таки нет возможности создать индекс?

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

Покажите результат SELECT version(); и EXPLAIN (VERBOSE, BUFFERS, SETTINGS) UPDATE... любого из этих запросов (Вам придётся подставить туда конкретные значения параметров), \d+ links и \dt+ links .

PostgreSQL 15.2 (Ubuntu 15.2-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit Update on public.links (cost=0.00..39716.91 rows=0 width=0) -> Seq Scan on public.links (cost=0.00..39716.91 rows=79 width=522) Output: replace((destpagetitle)::text, '$old_url'::text, '$new_url'::text), ctid Filter: ((links.destpagetitle)::text ~ '$old_url(\\W|$)'::text) Settings: search_path = 'public, public, "$user"' Planning: Buffers: shared hit=9

Вы не заменили значения параметров, мне кажется... можете подставить конкретные константы и показать план ещё раз? И покажите остальные две вещи (\d+ и \dt+).

я там поправил, а куда эти параметры подставлять \d+ links и \dt+ links .?

Вобще-то если там именно 7к строк в таблицэ — оно менее 100мс выполняться будет. psql дольшэ запускается.

Мы не знаем. не предоставили данных :) 6 к строк меняетс, а сколько всего сие неведомо нам .

Это метакоманды psql, каждая вводится / выполняется отдельной строкой (без ; в конце), нам нужно увидеть результат их выполнения.

Table "public.links" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description --------------------+-----------------------------+-----------+----------+---------+----------+-------------+--------------+------------- linkid | bigint | | not null | | plain | | | destpagetitle | character varying(255) | | | | extended | | | lowerdestpagetitle | character varying(255) | | | | extended | | | destspacekey | character varying(255) | | not null | | extended | | | lowerdestspacekey | character varying(255) | | | | extended | | | contentid | bigint | | not null | | plain | | | creator | character varying(255) | | | | extended | | | creationdate | timestamp without time zone | | | | plain | | | lastmodifier | character varying(255) | | | | extended | | | lastmoddate | timestamp without time zone | | | | plain | | | Indexes: "links_pkey" PRIMARY KEY, btree (linkid) "l_contentid_idx" btree (contentid) "l_destpgtitle_idx" btree (destpagetitle) "l_destspacekey_idx" btree (destspacekey) "l_ldestpgtitle_idx" btree (lowerdestpagetitle) "l_ldestspacekey_idx" btree (lowerdestspacekey) "links_creator_idx" btree (creator) "links_lastmodifier_idx" btree (lastmodifier) Foreign-key constraints: "fk_links_creator" FOREIGN KEY (creator) REFERENCES user_mapping(user_key) "fk_links_lastmodifier" FOREIGN KEY (lastmodifier) REFERENCES user_mapping(user_key) "fkn8mycko8frerne7brh5nr1csr" FOREIGN KEY (contentid) REFERENCES content(contentid) Access method: heap List of relations Schema | Name | Type | Owner | Persistence | Access method | Size | Description --------+-------+-------+------------+-------------+---------------+--------+------------- public | links | table | confluence | permanent | heap | 231 MB | (1 row)

это еще маленькая таблица, мне надо будет обновлять другую, она весит 11G, даже боюсь представить насколько это все затятнется..

А это насколько частое будет развлечение?

не часто

Так, а план выполнения реального UPDATE Вы можете показать? Это делается так (в сессии psql): BEGIN TRANSACTION; -- скопируйте результат выполнения следующего: EXPLAIN (ANALYZE, VERBOSE, BUFFERS, SETTINGS) UPDATE ...; ROLLBACK; Осторожно — при выполнении параллельно тому script это может вызывать deadlock (а обработки исключительных ситуаций в показанном script вроде как нет, поэтому он может "свалиться").

Update on public.links (cost=0.00..39718.24 rows=0 width=0) -> Seq Scan on public.links (cost=0.00..39718.24 rows=79 width=522) Output: replace((destpagetitle)::text, 'net.ru/shr/employees/9998'::text, 'profile.ru/emp/c36619a2-f143-4244-9d44'::text), ctid Filter: ((links.destpagetitle)::text ~ 'net.ru/shr/employees/9998(\\W|$)'::text) Planning: Buffers: shared hit=197 (6 rows)

Нет, Вы сделали не то, что я просил. EXPLAIN (ANALYZE, VERBOSE, BUFFERS, SETTINGS)

QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Update on public.links (cost=0.00..39718.24 rows=0 width=0) (actual time=2785.446..2785.447 rows=0 loops=1) Buffers: shared hit=16155 read=13413 -> Seq Scan on public.links (cost=0.00..39718.24 rows=79 width=522) (actual time=2785.443..2785.443 rows=0 loops=1) Output: replace((destpagetitle)::text, 'net.ru/shr/employees/9998'::text, 'profile.ru/emp/c36619a2-f143-4244-9d44'::text), ctid Filter: ((links.destpagetitle)::text ~ 'net.ru/shr/employees/9998(\\W|$)'::text) Rows Removed by Filter: 810917 Buffers: shared hit=16155 read=13413 Planning Time: 1.373 ms Execution Time: 2785.496 ms (9 rows)

Спасибо! Проблем тут несколько: 1. Этот PostgreSQL наверняка не настраивался — а очень зря. Вот эти shared ... read=13413 означают запрос дисковых блоков у OS (но не обязательно настоящее чтение с диска — может, какая-то их часть кеширована). По этому поводу — покажите результат \dconfig (можете из него убрать всё, что Вам кажется секретным), и расскажите, какое там "железо" (хотя бы примерно) и конкурентная нагрузка (если знаете). 2. Здесь нет подходящего для запроса индекса, поэтому PostgreSQL вынужден выполнять полное сканирование таблицы каждый раз, что, как видите ( actual time=2785.446..2785.447 ), занимает почти 3 секунды на каждый UPDATE. И с такими WHERE создать эффективный для этого индекс Вам, вполне возможно, не удастся* . Может быть, само это условие WHERE destpagetitle ~ 'net.ru/shr/employees/9998(\\W|$)' можно как-то изменить (например, переписать на точное совпадение, или на что-то вроде ~ '^net.ru/shr/employees/9998(\\W|$)' , т.е. сопоставление с фиксированным префиксом)? И да, если есть вариант сделать то, что советовал https://t.me/pgsql/489632 — подумайте об этом, в самом деле. * Другим участникам, которые тут же подумали про GIN/pg_trgm : подумайте о том, как он отразится на самих UPDATE. ;)

List of non-default configuration parameters Parameter | Value ----------------------------+----------------------------------------- TimeZone | Europe application_name | psql client_encoding | UTF8 cluster_name | 15/main config_file | /etc/postgresql/15/main/postgresql.conf data_directory | /var/lib/postgresql/15/main default_text_search_config | pg_catalog.english external_pid_file | /var/run/postgresql/15-main.pid hba_file | /etc/postgresql/15/main/pg_hba.conf ident_file | /etc/postgresql/15/main/pg_ident.conf lc_messages | en_US.UTF-8 lc_monetary | en_US.UTF-8 lc_numeric | en_US.UTF-8 lc_time | en_US.UTF-8 listen_addresses | 127.0.0.1 log_line_prefix | %m [%p] %q%u@%d log_timezone | Europe max_connections | 120 ssl | on ssl_cert_file | /etc/ssl/certs/cert.pem ssl_key_file | /etc/ssl/private/cert.key 4 -CPU 8G -RAM Load average: 0.00 0.03 0.18 ВМ не нагружена

"l_destpgtitle_idx" btree (destpagetitle) а этот индекс он не используется в данном случае?

В explain жэ написано, что нет. (Да и почти никак он не можэт помочь).

Да, тут действительно не было tuning. Если хотите сделать это "наскоро" (результат будет "так себе", но лучше, чем ничего) — попробуйте хоть https://pgconfigurator.cybertec.at/ (сервер PostgreSQL придётся перезапустить). Советую потому, что Вы a) показали не всё "железо" и б) чтобы с этим подсказать, нам нужно знать размер баз, "горячих" данных, типичную нагрузку и т.п. А если уж заниматься всерьёз, то надо начинать с настройки OS (и предоставления всей нужной информации, ясное дело). Кстати (забыл написать) — обновили бы Вы PostgreSQL до последней версии (15.3). > "l_destpgtitle_idx" btree (destpagetitle) а этот индекс он не используется в данном случае? См. https://t.me/pgsql/489650 Так что же, есть возможность как-то изменить условие выборки / переписать этот запрос?

спасибо огромное за информацию! 🤝 скажите пожалуйста, что имеете ввиду под настройкой OS? OS 20.04.5 LTS 5.4.0-153-generic. Сейчас там все настройки по умолчанию. Установлен postgresql 15, confluence, nginx, java-11-openjdk-amd64. Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian Address sizes: 43 bits physical, 48 bits virtual CPU(s): 4 On-line CPU(s) list: 0-3 Thread(s) per core: 1 Core(s) per socket: 1 Socket(s): 4 NUMA node(s): 1 Vendor ID: GenuineIntel CPU family: 6 Model: 79 Model name: Intel(R) Xeon(R) CPU E5-2660 v4 @ 2.00GHz Stepping: 0 CPU MHz: 1999.998 BogoMIPS: 3999.99 Hypervisor vendor: VMware fs ext4

> скажите пожалуйста, что имеете ввиду под настройкой OS? Например (начиная с раздела по ссылке и до конца страницы): https://www.postgresql.org/docs/current/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT Ну и тут https://www.enterprisedb.com/postgres-tutorials/introduction-postgresql-performance-tuning-and-optimization было [куда] больше, если хочется погрузиться. ;) > fs ext4 А диск-то какой (SSD/HDD, сколько IOPS)? ;) > БД < 100G А сколько там "горячих" данных, можете примерно оценить?

Timing buffered disk reads: 1254 MB in 3.00 seconds = 417.79 MB/sec, какая сейчас там политика включена ssd или sata затрудняюсь ответить. Горячих данных там нет, ВМ не нагружена совсем, это тестовый стенд.

Но потом-то какая-то нагрузка ожидается... С другой стороны, под неё можно перенастроить потом — см. все эти ссылки, только "выкрутите" shared_buffers на максимум (можно 6-7 GB поставить, если в этой VM больше нет чего-то, чему нужна RAM, очень желательно с huge_pages в OS).

Большое спасибо!

Вы хоть потом расскажите / покажите, что получилось-то. ;) И да, про условия (изменение запросов) и индексы, возможно, всё же стоит подумать...

спасибо, как закончу с тюнингом - маякну.