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