Forum: Ruby migration too slow?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Nic X. (Guest)
on 2009-01-19 20:22
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 H. (Guest)
on 2009-01-19 20:44
(Received via mailing list)
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 X. (Guest)
on 2009-01-19 21:16
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
Robert K. (Guest)
on 2009-01-19 21:31
(Received via mailing list)
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
Nic X. (Guest)
on 2009-01-19 22:21
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
Denis H. (Guest)
on 2009-01-19 23:12
(Received via mailing list)
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 S. (Guest)
on 2009-01-20 01:03
Nic Xie wrote:
> I found I could not [...]

Check out pgsql's ALTER TABLE documentation, perhaps you'll find hints
there.
This topic is locked and can not be replied to.