Using ActiveRecord::Schema.define causes extreme slowdown
- Swtaarrs
- Posts: 10
- Starts: 9
- Wiki Edits: 1
- Location: Pittsburgh, PA
My application needs to create and maintain its own tables, so for a while I had been using SQL statements manually executed using ActiveRecord::Base.connection.execute. I decided to try using ActiveRecord::Schema.define to make the table, since it's a lot cleaner and it would work if I switched away from MySQL. However, when I used it to make the tables, any operations I did afterwards involving the database became incredibly slow. I have a method that imports the data from a CSV file into the database and sets up all the relationships between the objects that usually takes about 60 seconds. When I ran it after creating the tables with Schema.define, that same operation took about 6 minutes. My hard drive was thrashing the whole time and it was barely using any cpu. Does anyone know what could cause this?
- William
- Posts: 1061
- Starts: 32
- Wiki Edits: 56
So we're talking about your local development machine here only, right? Have you tried looking into BackgrounDRB to do this kind of work? Also, does it help if you restart your server before executing the ActiveRecord::Schema.define process?
2007-03-31 03:21 AM
- Swtaarrs
-
- Posts: 10
- Starts: 9
- Wiki Edits: 1
- Location: Pittsburgh, PA
Yeah, I've only tried it on my local machine. When I saw how slow it was, I decided I probably shouldn't deploy it. BackgrounDRB looks like it could help with a few other problems I've been having, but I never initiate this particular action via the web, I always use script/runner. I haven't tried restarting the server, I will later today.
I noticed that the tables that Shema.define makes are InnoDB, while the tables are MyIsam when I use the raw Sql statements. I did some reading about the two types and it looks like myisam is generally faster, but is it normal for there to be that much of a difference? I guess I'll look for a way to have Schema.define make myisam tables and see if that fixes it.
I noticed that the tables that Shema.define makes are InnoDB, while the tables are MyIsam when I use the raw Sql statements. I did some reading about the two types and it looks like myisam is generally faster, but is it normal for there to be that much of a difference? I guess I'll look for a way to have Schema.define make myisam tables and see if that fixes it.
2007-03-31 12:21 PM
- William
-
- Posts: 1061
- Starts: 32
- Wiki Edits: 56
I don't think its normal for it to be that much of a difference - let me know how it goes.
2007-03-31 12:56 PM
- Swtaarrs
-
- Posts: 10
- Starts: 9
- Wiki Edits: 1
- Location: Pittsburgh, PA
I was able to get it to make MyISAM tables and it's back to the speed it was at before. I think the reason it was so slow when I tried it before was that I was working on my laptop, which has a much slower drive than my desktop. Even on my desktop with the faster drive InnoDB tables are about 30% slower for this process.