activerecord going slow (not in rails...) (VERY slow)

[Nick Green]

Active record is taking 7+ seconds to save one item! I'm using
activerecord not in rails. I have a object that inherits
ActiveRecord::Base mapped to a table in mysql with 5 string columns and
3 text columns (and auto-generated id/time columns). One column is a
serialized hash, but I made it an empty hash in all of the tests I ran.

I have this piece of code:

start = Time.now.to_i
entity.save
puts "Saving one item took #{Time.now.to_i-start} seconds"

... it outputs

Saving one item took 7 seconds.

The text in one of the text columns is code (quite a bit of C/C++ code),
and so I thought maybe all that escaping of fancy characters was slowing
things down. I tried setting each string/text field to a single
character ("a","b","c"...) and the saves still took 6-7 seconds.

Doing the same database insertion with:

ActiveRecord::Base.connection.execute

tends to run in <1 second. Unfortunately this means I have to make sure
to escape/serialize all my own data, thus rewriting code and potentially
introducing new bugs. This is not an app that requires speed per-se,
but it does need to commit about 10,000 of these entries once a week,
and at 7-8 seconds per that would take 22 hours, which is not
acceptable.

Does anyone have any ideas why this would be going so insanely slow? I
am happy to provide further information (platform etc), but since the
insertions are fast when running ActiveRecord::Base.connection.execute,
I think this must be something I don't understand about activerecord
itself.

 

[Brian Candler]

I have this piece of code:

start = Time.now.to_i
entity.save
puts "Saving one item took #{Time.now.to_i-start} seconds"

... it outputs

Saving one item took 7 seconds.

Try saving two entities one after the other, and time each of them.
Maybe it's a startup overhead.

Startup problems could be:

- loading in the ActiveRecord and ActiveSupport libraries (although I
guess in your case they are already loaded)

- slowness establishing a connection to your database, such as lack of
reverse DNS (e.g. server accepts connection, tries to resolve your IP
into a name for logging purposes, times out, continues)

If it's the latter problem, on the server try adding your client's IP
address and name into /etc/hosts

I think this must be something I don't understand about activerecord
itself.

Detailled questions specific to ActiveRecord would be best asked on a
Rails mailing list, since AR is a component of Rails.

 

[Nick Green]

Try saving two entities one after the other, and time each of them.
Maybe it's a startup overhead.

Startup problems could be:

- loading in the ActiveRecord and ActiveSupport libraries (although I
guess in your case they are already loaded)

- slowness establishing a connection to your database, such as lack of
reverse DNS (e.g. server accepts connection, tries to resolve your IP
into a name for logging purposes, times out, continues)

If it's the latter problem, on the server try adding your client's IP
address and name into /etc/hosts


Detailled questions specific to ActiveRecord would be best asked on a
Rails mailing list, since AR is a component of Rails.

Thanks for the reply

Unfortunately, in response to your first question:
--The entries were saved sequentially, time varies slightly (usually 6
to 9 seconds), but is always extremely high.

And to the second
-- mysql server is localhost, though I don't know if that precludes it
from trying to log my IP. However, the fact that calling the
ActiveRecord::Base.connection.execute command correctly runs so fast
probably means its not connection issues (I think, I could definitely be
wrong about that, I have not perused the activerecord code to address
this specific issue).

As for posting in rails, will do, I think your right. Wasn't sure where
to post this one since its not rails, but it is issues with a rails
library in a plain ruby app.

 

[Wojciech Piekutowski]

I recommend to simply profile your code. First extract a very short
snippet of code that reproduces the problem. The lesser lines the
better. Then run it with ruby-prof. You will clearly see where
ActiveRecord::Base#save spends so much time.

Greetings,
Wojtek

 

[Nick Green]

I recommend to simply profile your code. First extract a very short
snippet of code that reproduces the problem. The lesser lines the
better. Then run it with ruby-prof. You will clearly see where
ActiveRecord::Base#save spends so much time.

Greetings,
Wojtek

ruby-prof is amazing. Thank you for making me aware of its existence,
how I had never managed to google "ruby profiling" I will never
understand, but my life is better now.

Unfortunately there was a number of functions slowing things down, the
solution I ended up using was switching to DataMapper. Its now going
very fast