Search Postgresql Archives

updating 6million rows, slowdown every 5minutes, manually doing checkpoints?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



hi,

i have a table where i need to update 7million rows.
i'm trying to do this without a downtime, but doesn't matter what i do,
i get massive slowdowns every 5 minutes.

details:
the table's schema contains 6integers, 2timestamps, 1 varchar, and 1text.
i added a new text-field (currently null), and now i need to update it
to the correct value(s).

basically i need to do 3 queries:

update mytable set textfield='text1' where field1=3 and field2=1 and
textfield is null;
update mytable set textfield='text2' where field1=4 and field2=1 and
textfield is null;
update mytable set textfield='' where textfield is null;

doing them this way would probably cause the database to slow down too
much (well, i haven't
tried doing it with the live database, but i don't like doing such
'unlimited' queries, because
i cannot track it's progress. so for example if it's already running
for 5 minutes, i do not know
how to see the 'completion percentage' :-( ) so i'm trying to do it
'step by step', like:

update mytable set textfield='text1' where idfield in (select idfield
from mytable where field1=3 and field2=1 and textfield is null limit
1000);

i tried different limits, waiting between queries, but whatever i do,
every 5 minutes the db slows down.

i checked the documentation and it seems it's doing the CHECKPOINT
every 5 minutes.
is there anything i could do to make this work better? the
documentation recommends to increase checkpoint_timeout,
but the current value is generally fine for us, except now, when i
need to do this mass-update.

could i just do CHECKPOINT commands "manually"? the idea is that if i
do it more often than every 5 minutes,
maybe the slowdown will not be so massive?

or is there perhaps some other way how to 'mass-update' a table in a
more efficient way?

thanks,
gabor

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux