Thread
-
Batch update million records in prd DB
Yi Sun <yinan81@gmail.com> — 2021-02-24T13:01:24Z
Hello, Now need to update several million records in a table in prd DB, if can use batch update 1000 records and commit each time, if it will affect prd application like below sample script please? Sample script: DO $MAIN$ DECLARE affect_count integer; chunk_size CONSTANT integer :=1000; sleep_sec CONSTANT numeric :=0.1; BEGIN loop exit when affect_count=0; UPDATE tbl a SET name = '' WHERE a.id IN (SELECT id FROM tbl b WHERE name IS NULL LIMIT chunk_size); GET DIAGNOSTICS affect_count = ROW_COUNT; commit; PERFORM pg_sleep(sleep_sec); end loop; END; $MAIN$; Thanks and best regards -
Re: Batch update million records in prd DB
Michael Lewis <mlewis@entrata.com> — 2021-02-24T15:47:35Z
Of course it will impact a system using that table, but not significant I expect and the production system should handle it. If you are committing like this, then you can kill the script at any time and not lose any work. The query to find the next IDs to update is probably the slowest part of this depending on what indexes you have.
-
Re: Batch update million records in prd DB
Yi Sun <yinan81@gmail.com> — 2021-02-25T12:36:26Z
Hi Michael, Thank you for your reply We found that each loop take time is different, it will become slower and slower, as our table is big table and join other table, even using index the last 1000 records take around 15 seconds, will it be a problem? Will other concurrent update have to wait for 15 second until lock release? Thanks and best regards Michael Lewis <mlewis@entrata.com> 于2021年2月24日周三 下午11:47写道： > Of course it will impact a system using that table, but not significant I > expect and the production system should handle it. If you are committing > like this, then you can kill the script at any time and not lose any work. > The query to find the next IDs to update is probably the slowest part of > this depending on what indexes you have. >
-
Re: Batch update million records in prd DB
Michael Lewis <mlewis@entrata.com> — 2021-02-26T17:45:59Z
It might be a concern, but generally that should be a row level lock and only block other update/delete options on those rows. It might be helpful to look at the explain analyze output early on vs later in the process. It might be that you are getting very few hot updates and indexes are being updated constantly. >
-
Re: Batch update million records in prd DB
Yi Sun <yinan81@gmail.com> — 2021-03-01T08:09:59Z
Hi Michael This is the script and explain plan info, please check, seems Filter remove more records took more time DO $MAIN$ DECLARE affect_count integer := 1000; processed_row_count integer := 0; BEGIN LOOP exit WHEN affect_count = 0; UPDATE app gaa SET deleted_at = ( SELECT CAST(extract(epoch FROM now() at time zone 'utc') * 1000000000 AS bigint)) WHERE gaa.id IN ( SELECT gab.id FROM app gab LEFT JOIN pol gp ON gab.policy_id = gp.id WHERE gab.policy_type = 'policy.protection.total' AND gp.name LIKE 'Mobile backup%' AND gab.deleted_at IS NULL AND gp.deleted_at IS NOT NULL LIMIT 1000); GET DIAGNOSTICS affect_count = ROW_COUNT; COMMIT; processed_row_count = processed_row_count + affect_count; END LOOP; RAISE NOTICE 'total processed rows %', processed_row_count; END; $MAIN$; --early explain plan, 1000 records update take 156.488 ms -------------------------------------------------------------------- Update on app gaa (cost=3307.57..6085.41 rows=1000 width=3943) (actual time=156.347..156.347 rows=0 loops=1) InitPlan 1 (returns $0) -> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=1) -> Nested Loop (cost=3307.54..6085.39 rows=1000 width=3943) (actual time=18.599..33.987 rows=1000 loops=1) -> HashAggregate (cost=3306.99..3316.99 rows=1000 width=98) (actual time=18.554..19.085 rows=1000 loops=1) Group Key: ("ANY_subquery".id)::text -> Subquery Scan on "ANY_subquery" (cost=2.17..3304.49 rows=1000 width=98) (actual time=0.041..18.052 rows=1000 loops=1) -> Limit (cost=2.17..3294.49 rows=1000 width=37) (actual time=0.030..17.827 rows=1000 loops=1) -> Merge Join (cost=2.17..877396.03 rows=266497 width=37) (actual time=0.029..17.764 rows=1000 loops=1) Merge Cond: ((gab.policy_id)::text = (gp.id )::text) -> Index Scan using tmp_uq_policy_id_context2 on app gab (cost=0.56..487631.06 rows=3151167 width=74) (actual time=0.018..9.192 rows=3542 loops=1) Filter: ((policy_type)::text = 'policy.protection.total'::text) Rows Removed by Filter: 2064 -> Index Scan using pol_pkey on pol gp (cost=0.56..378322.78 rows=361105 width=37) (actual time=0.008..7.380 rows=1006 loops=1) Filter: ((deleted_at IS NOT NULL) AND (name ~~ 'Mobile backup%'::text)) Rows Removed by Filter: 3502 -> Index Scan using app2_pkey on app gaa (cost=0.56..2.77 rows=1 width=3874) (actual time=0.014..0.014 rows=1 loops=1000) Index Cond: ((id)::text = ("ANY_subquery".id)::text) Planning Time: 0.852 ms Execution Time: 156.488 ms --later explain plan, 1000 records update take 13301.600 ms -------------------------------------------------------------------------- Update on app gaa (cost=3789.35..6567.19 rows=1000 width=3980) (actual time=13301.466..13301.466 rows=0 loops=1) InitPlan 1 (returns $0) -> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1) -> Nested Loop (cost=3789.32..6567.17 rows=1000 width=3980) (actual time=12881.004..12896.440 rows=1000 loops=1) -> HashAggregate (cost=3788.77..3798.77 rows=1000 width=98) (actual time=12880.958..12881.378 rows=1000 loops=1) Group Key: ("ANY_subquery".id)::text -> Subquery Scan on "ANY_subquery" (cost=2.17..3786.27 rows=1000 width=98) (actual time=12850.663..12880.505 rows=1000 loops=1) -> Limit (cost=2.17..3776.27 rows=1000 width=37) (actual time=12850.656..12880.233 rows=1000 loops=1) -> Merge Join (cost=2.17..862421.74 rows=228510 width=37) (actual time=12850.655..12880.162 rows=1000 loops=1) Merge Cond: ((gab.policy_id)::text = (gp.id )::text) -> Index Scan using tmp_uq_policy_id_context2 on app gab (cost=0.56..474159.31 rows=2701994 width=74) (actual time=0.017..6054.269 rows=2302988 loops=1) Filter: ((policy_type)::text = 'policy.protection.total'::text) Rows Removed by Filter: 1822946 -> Index Scan using pol_pkey on pol gp (cost=0.56..378322.78 rows=361105 width=37) (actual time=0.007..5976.346 rows=936686 loops=1) Filter: ((deleted_at IS NOT NULL) AND (name ~~ 'Mobile backup%'::text)) Rows Removed by Filter: 3152553 -> Index Scan using app2_pkey on app gaa (cost=0.56..2.77 rows=1 width=3911) (actual time=0.014..0.014 rows=1 loops=1000) Index Cond: ((id)::text = ("ANY_subquery".id)::text) Planning Time: 0.785 ms Execution Time: 13301.600 ms --we also choose a temporary table solution to test, script as below DO $MAIN$ DECLARE affect_count integer; offset_count integer:=0; chunk_size CONSTANT integer :=1000; sleep_sec CONSTANT numeric :=0.1; BEGIN DROP TABLE IF EXISTS tmp_usage_tbl; CREATE TEMPORARY TABLE tmp_usage_tbl(id character varying(36)); INSERT INTO tmp_usage_tbl(id) SELECT gab.id FROM app gab LEFT JOIN pol gp ON gab.policy_id = gp.id WHERE gab.policy_type = 'policy.protection.total' AND gp.name LIKE 'Mobile backup%' AND gab.deleted_at IS NULL AND gp.deleted_at IS NOT NULL; loop exit when affect_count=0; UPDATE app gaa SET deleted_at = ( SELECT CAST(extract(epoch FROM now() at time zone 'utc') * 1000000000 AS bigint)) WHERE gaa.id IN (SELECT id FROM tmp_usage_tbl order by id LIMIT chunk_size offset offset_count); GET DIAGNOSTICS affect_count = ROW_COUNT; commit; offset_count:=offset_count+chunk_size; PERFORM pg_sleep(sleep_sec); end loop; END; $MAIN$; --1000 records update take around 2000 ms(each time same as use temporay table) which solution is better please? Michael Lewis <mlewis@entrata.com> 于2021年2月27日周六 上午1:46写道： > It might be a concern, but generally that should be a row level lock and > only block other update/delete options on those rows. It might be helpful > to look at the explain analyze output early on vs later in the process. It > might be that you are getting very few hot updates and indexes are being > updated constantly. > >> -
Re: Batch update million records in prd DB
Michael Lewis <mlewis@entrata.com> — 2021-03-01T16:08:19Z
1) Don't pretend it is a left join when your where clause will turn it into an INNER join. LEFT JOIN pol gp ON gab.policy_id = gp.id WHERE * AND gp.name <http://gp.name> LIKE 'Mobile backup%' AND gp.deleted_at IS NOT NULL;* 2) It is interesting to me that the row estimates are stable, but the number of rows filtered out and that are found by those two index scans changes so dramatically. Is your underlying data changing significantly during this run? Maybe I am not seeing something that should be obvious. 3) What is the execution plan for the update based on the temp table? It is hard to believe it takes 2 seconds to update 1000 rows. By the way, that temp table needs to be analyzed after it is created & populated with data, or the planner won't know how many rows it contains or any other stats about it. One advantage of the temp table should be that you have already found all the candidate rows and so the time that locks are held to update the 1000 target rows is smaller. Given you are doing a order by & limit in the use of the temp table, I might actually create an index on the id column to help the later runs. The temp table should likely remain in memory (temp_buffers) but still, btree is nice for ordered use. > -
Re: Batch update million records in prd DB
Kristjan Mustkivi <sonicmonkey@gmail.com> — 2021-03-02T09:26:24Z
Hi Yi, I found that in postgres the memory is slowly eaten away when doing updates within plsql loop. It only gets released once the whole block completes. While it is ok for small tables you will eventually run out of memory for really big ones. The working approach was to do the loop in e.g a python script that called the DML statements and also called commit. Several million rows is fortunately relatively small number to update but once you get to billions this approach would not likely work. Note that after each batch you also should call VACUUM before starting a new one to avoid significant table bloat. BR, Kristjan On Wed, Feb 24, 2021 at 3:01 PM Yi Sun <yinan81@gmail.com> wrote: > > Hello, > > Now need to update several million records in a table in prd DB, if can use batch update 1000 records and commit each time, if it will affect prd application like below sample script please? > > Sample script: > > DO $MAIN$ > DECLARE > affect_count integer; > chunk_size CONSTANT integer :=1000; > sleep_sec CONSTANT numeric :=0.1; > BEGIN > > loop > > exit when affect_count=0; > > UPDATE tbl a > SET name = '' > WHERE a.id IN (SELECT id > FROM tbl b > WHERE name IS NULL > LIMIT chunk_size); > > GET DIAGNOSTICS affect_count = ROW_COUNT; > > commit; > > PERFORM pg_sleep(sleep_sec); > > end loop; > END; > $MAIN$; > > Thanks and best regards -- Kristjan Mustkivi Email: kristjan.mustkivi@gmail.com
-
Re: Batch update million records in prd DB
Yi Sun <yinan81@gmail.com> — 2021-03-02T10:36:21Z
Hi Michael, Thank you, after create index to the temp table column, time cost become smaller Michael Lewis <mlewis@entrata.com> 于2021年3月2日周二 上午12:08写道： > 1) Don't pretend it is a left join when your where clause will turn it > into an INNER join. > LEFT JOIN pol gp ON gab.policy_id = gp.id > WHERE > > * AND gp.name <http://gp.name> LIKE 'Mobile backup%' > AND gp.deleted_at IS NOT NULL;* > > 2) It is interesting to me that the row estimates are stable, but the > number of rows filtered out and that are found by those two index > scans changes so dramatically. Is your underlying data changing > significantly during this run? Maybe I am not seeing something that should > be obvious. > > 3) What is the execution plan for the update based on the temp table? It > is hard to believe it takes 2 seconds to update 1000 rows. By the way, that > temp table needs to be analyzed after it is created & populated with data, > or the planner won't know how many rows it contains or any other stats > about it. One advantage of the temp table should be that you have already > found all the candidate rows and so the time that locks are held to update > the 1000 target rows is smaller. Given you are doing a order by & limit in > the use of the temp table, I might actually create an index on the id > column to help the later runs. The temp table should likely remain in > memory (temp_buffers) but still, btree is nice for ordered use. > >>
-
Re: Batch update million records in prd DB
Yi Sun <yinan81@gmail.com> — 2021-03-02T10:43:22Z
Hi Kristjan, Thank you for this information. "postgres the memory is slowly eaten away when doing updates within plsql loop" for this memory issue, I want to check if it exists in our current postgresql version. And let developer change to use python for loop also need to show them the proof, how to reproduce and check the memory slowly eaten away please? OS level cmd or psql cmd to verify? thanks Kristjan Mustkivi <sonicmonkey@gmail.com> 于2021年3月2日周二 下午5:26写道： > Hi Yi, > > I found that in postgres the memory is slowly eaten away when doing > updates within plsql loop. It only gets released once the whole block > completes. While it is ok for small tables you will eventually run out > of memory for really big ones. The working approach was to do the loop > in e.g a python script that called the DML statements and also called > commit. Several million rows is fortunately relatively small number to > update but once you get to billions this approach would not likely > work. Note that after each batch you also should call VACUUM before > starting a new one to avoid significant table bloat. > > BR, > > Kristjan > > On Wed, Feb 24, 2021 at 3:01 PM Yi Sun <yinan81@gmail.com> wrote: > > > > Hello, > > > > Now need to update several million records in a table in prd DB, if can > use batch update 1000 records and commit each time, if it will affect prd > application like below sample script please? > > > > Sample script: > > > > DO $MAIN$ > > DECLARE > > affect_count integer; > > chunk_size CONSTANT integer :=1000; > > sleep_sec CONSTANT numeric :=0.1; > > BEGIN > > > > loop > > > > exit when affect_count=0; > > > > UPDATE tbl a > > SET name = '' > > WHERE a.id IN (SELECT id > > FROM tbl b > > WHERE name IS NULL > > LIMIT chunk_size); > > > > GET DIAGNOSTICS affect_count = ROW_COUNT; > > > > commit; > > > > PERFORM pg_sleep(sleep_sec); > > > > end loop; > > END; > > $MAIN$; > > > > Thanks and best regards > > > > -- > Kristjan Mustkivi > > Email: kristjan.mustkivi@gmail.com >