[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.ruby

migration too slow?

Nic Xie

1/19/2009 6:21:00 PM

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.
--
Posted via http://www.ruby-....
6 Answers

Denis Haskin

1/19/2009 6:42:00 PM

0

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 wrote:
> 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.
> ...

Nic Xie

1/19/2009 7:15:00 PM

0

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

--
Posted via http://www.ruby-....

Robert Klemme

1/19/2009 7:29:00 PM

0

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

--
remember.guy do |as, often| as.you_can - without end

Nic Xie

1/19/2009 8:20:00 PM

0

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

--
Posted via http://www.ruby-....

Denis Haskin

1/19/2009 9:07:00 PM

0

[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


Nic Xie wrote:
> 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
>
>
>

Albert Schlef

1/19/2009 11:02:00 PM

0

Nic Xie wrote:
> I found I could not [...]

Check out pgsql's ALTER TABLE documentation, perhaps you'll find hints
there.
--
Posted via http://www.ruby-....