# Migration too slow?

**URL:** <https://www.ruby-forum.com/t/migration-too-slow/158139>\
**Category:** Ruby\
**Created:** [January 19, 2009, 7:22pm UTC](https://www.ruby-forum.com/t/migration-too-slow/158139 "2009-01-19T19:22:21Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![yudora111](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/y/91b2a8/32.png) [@yudora111](https://www.ruby-forum.com/u/yudora111)\
**Post date:** [January 19, 2009, 7:22pm UTC](https://www.ruby-forum.com/t/migration-too-slow/158139/1 "2009-01-19T19:22:21Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![yudora111](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/y/91b2a8/32.png) [@yudora111](https://www.ruby-forum.com/u/yudora111)\
**Post date:** [January 19, 2009, 7:44pm UTC](https://www.ruby-forum.com/t/migration-too-slow/158139/2 "2009-01-19T19:44:37Z")

</div>

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?

dwh

---

<div class="post-metadata">

**Author:** ![yudora111](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/y/91b2a8/32.png) [@yudora111](https://www.ruby-forum.com/u/yudora111)\
**Post date:** [January 19, 2009, 8:16pm UTC](https://www.ruby-forum.com/t/migration-too-slow/158139/3 "2009-01-19T20:16:08Z")

</div>

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?
> 
> dwh

---

<div class="post-metadata">

**Author:** ![yudora111](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/y/91b2a8/32.png) [@yudora111](https://www.ruby-forum.com/u/yudora111)\
**Post date:** [January 19, 2009, 8:31pm UTC](https://www.ruby-forum.com/t/migration-too-slow/158139/4 "2009-01-19T20:31:43Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![yudora111](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/y/91b2a8/32.png) [@yudora111](https://www.ruby-forum.com/u/yudora111)\
**Post date:** [January 19, 2009, 9:21pm UTC](https://www.ruby-forum.com/t/migration-too-slow/158139/5 "2009-01-19T21:21:14Z")

</div>

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?
> > 
> > dwh

---

<div class="post-metadata">

**Author:** ![yudora111](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/y/91b2a8/32.png) [@yudora111](https://www.ruby-forum.com/u/yudora111)\
**Post date:** [January 19, 2009, 10:12pm UTC](https://www.ruby-forum.com/t/migration-too-slow/158139/6 "2009-01-19T22:12:14Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![yudora111](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/y/91b2a8/32.png) [@yudora111](https://www.ruby-forum.com/u/yudora111)\
**Post date:** [January 20, 2009, 12:03am UTC](https://www.ruby-forum.com/t/migration-too-slow/158139/7 "2009-01-20T00:03:00Z")

</div>

Nic Xie wrote:

> I found I could not […]

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