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.
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
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
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
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
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 […]
Check out pgsql’s ALTER TABLE documentation, perhaps you’ll find hints
there.