Migration too slow?


#1

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.


#2

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


#3

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.

Denis H. wrote:

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


#4

On 19.01.2009 20:14, Nic Xie wrote:

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


#5

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

Nic Xie wrote:

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.

Denis H. wrote:

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


#6

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


#7

Nic Xie wrote:

I found I could not […]

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