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.
A6c8bedf32359c2989105982d68a0f8e?d=identicon&s=25 Nic Xie (yudora111)
on 2009-01-19 19: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.
2a39aed820c125e6db9826fa8180043f?d=identicon&s=25 Denis Haskin (Guest)
on 2009-01-19 19: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
A6c8bedf32359c2989105982d68a0f8e?d=identicon&s=25 Nic Xie (yudora111)
on 2009-01-19 20: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 Haskin 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
E0d864d9677f3c1482a20152b7cac0e2?d=identicon&s=25 Robert Klemme (Guest)
on 2009-01-19 20: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
A6c8bedf32359c2989105982d68a0f8e?d=identicon&s=25 Nic Xie (yudora111)
on 2009-01-19 21: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 Haskin 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
2a39aed820c125e6db9826fa8180043f?d=identicon&s=25 Denis Haskin (Guest)
on 2009-01-19 22: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
699c00ad35f2755810b4aa5f423d73e2?d=identicon&s=25 Albert Schlef (alby)
on 2009-01-20 00: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.