migration too slow?

[Nic Xie]

I have a migration including a update_all statement to a big
table(having 100K+ rows).

It gets too slow. It has been running for 8 hours and not done yet. I
don't know if it is still running.
Any one can give me some food for thought?
I tried to use --trace to keep track of the migration. But it doesn't
give me enough info. I still don't know if that update_all statement is
running or dead.

My migration is like this:
def self.up
add_column :x_logs, :x_type, :string
rename_column :x_logs, :y_id, :z_id
XLog.update_all("x_type = 'blah'")

end


Thanks in advance.

 

[Denis Haskin]

Maybe sorta OT for this list, but:
- assuming the db is MySQL, I'd do a SHOW PROCESSLIST from a MySQL
client (or use mytop) to see if the query is still running, if it's
maybe blocked by something else, or what... 8 hours for 100K rows?
Something's wrong...
- Since you're assigning the same value to all rows for this
newly-created column, perhaps setting a default value for the column
would be appropriate instead?

add_column :x_logs, :x_type, :string, :default => 'blah'


dwh

 

[Nic Xie]

Thanks
I use postgresql. I tried select * from pg_stat_activity. And it showed
current query is updating the x_logs.

But why it is so slow?

I will try to use it as a default value. It sounds good.

 

[Robert Klemme]

I use postgresql. I tried select * from pg_stat_activity. And it showed
current query is updating the x_logs.

But why it is so slow?

This can depend on a lot of factors. Maybe too many SQL statements are
executed or it is just a single UPDATE statement and the transaction
needs too much storage, maybe the DB's setup is not appropriate for this
type of update etc.

Kind regards

robert

 

[Nic Xie]

I found I could not set it as a default. Because I have existing rows
and I want all of them have the type explicitly.

To Robert, I only have this query running.

Nic

 

[Denis Haskin]

[Note: parts of this message were removed to make it a legal post.]

Not sure if postgresql is any different, but in MySQL when you add a
column to a table and specify the default value for it, the column is
set to that default value for all existing rows in the table.

dwh

 

[Albert Schlef]

I found I could not [...]

Check out pgsql's ALTER TABLE documentation, perhaps you'll find hints
there.