Activerecord save takes too long

Ruby JRuby
1

Hi list,

I am using JRuby 1.5.1, JVM 1.6u11, and mysql database in my rails 2.3.8
application.

I recently switched to mysql after discovering that sqlite3 was too slow
for my purposes (activerecord save was taking around 5-6 seconds).

In my config/environment.rb, I have config.threadsafe!

I am using the following in database.yml:

development:
adapter: jdbc
encoding: utf8
driver: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/api_development
username:
password:

Mysql classes are those in mysql-connector-java-5.0.8-bin.jar
(I have unpacked the jar and put the classes in my classpath).

Also, in my app, I run an external daemon that has access to the rails
environment (it does so by requiring config/environment.rb) and uses the
activerecord models, and it synchronises changes from an external source
to the database.

Now, I observed that the activerecord save in my daemon was taking way
too long (it went up to 2 seconds sometimes).

I am a Rails newbie, so I am not too familiar with profiling rails apps,
so I just downloaded jruby-prof gem, and ran my app with --profile flag.

The most expensive methods listed in profile.txt:

±-------------------------------------+
| Most expensive methods summarized |
±-------------------------------------+

           Net
      ------------

Count Time Pct Location
===== ==== === ========
1807 14547.6 134.8
ruby.jit.require_120F9897B5F59B6B5FA66BC56E0277183EA7EAA5:rescue_1$RUBY$__rescue___0
54 7788.7 91.0 arjdbc.jdbc.RubyJdbcConnection$4:call
14 3079.7 28.5 script.server:file
183238 1453.3 13.5
ruby.jit.local_constant_names_09C5B43324BD06CCA023EF35AE3D4FE68110198C:block_0$RUBY$block
5745 1390.1 12.9
ruby.jit.local_constants_C5E86470B8F309E82DBA5F102775C5E8A439BFA1:block_0$RUBY$block
183238 1324.8 12.3
ruby.jit.local_constant_names_09C5B43324BD06CCA023EF35AE3D4FE68110198CBlockCallback$block_0$RUBY$__block__xx1:call
183238 1278.3 11.8
ruby.jit.local_constants_C5E86470B8F309E82DBA5F102775C5E8A439BFA1BlockCallback$block_2$RUBY$__block__xx1:call
183238 1183.9 11.0
ruby.jit.local_constants_C5E86470B8F309E82DBA5F102775C5E8A439BFA1:block_2$RUBY$block
23088 952.5 8.8
ruby.jit.transition_D093E1CADDE01E603BA2157FEE42159D3E5DCE1D:file
1914 914.4 8.5
ruby.jit.local_constant_names_09C5B43324BD06CCA023EF35AE3D4FE68110198C:file
27188 735.2 6.8
ruby.jit.transition_D093E1CADDE01E603BA2157FEE42159D3E5DCE1D:block_0$RUBY$block
23088 506.4 4.7
ruby.jit.initialize_559CBB41251F0E86B0A61E97B11AF6CF99AC2314:file
3 490.7 4.5 arjdbc.jdbc.RubyJdbcConnection:commit
184195 488.7 4.5
ruby.jit.local_constant_names_09C5B43324BD06CCA023EF35AE3D4FE68110198C:setPosition
198610 461.2 4.3
ruby.jit.local_constants_C5E86470B8F309E82DBA5F102775C5E8A439BFA1:setPosition
1615 403.5 3.8
ruby.jit.require_F5B9B69B62A1A8EB304B060744C40F783057DECA:rescue_1$RUBY$__rescue___0
169816 382.2 3.5
ruby.jit.transition_D093E1CADDE01E603BA2157FEE42159D3E5DCE1D:setPosition
22854 380.0 3.5
ruby.jit.transition_index_FFD2071A066A3794CC987F951C8B7A041F214554:file
295 349.0 3.2
ruby.jit._lt_equal_gt__4A5A1F59C8232D2538B64D6FDC20F5E2E3E016A1BlockCallback$block_0$RUBY$__block__xx1:call
1 244.0 2.3 JopensslService:basicLoad

My questions:

  • Is --profile a good way to profile my app?
  • Is JDBC taking too long to talk to the database, based upon the
    profiler results shown above?
  • Have I neglected to configure some aspect of mysql on my Centos 5.3
    box?
  • Would adding indexes to the tables make a big impact on the save time?
    ( I have to mention that two of the models that I save have a
    has_and_belongs_to_many association).

Best,
Radhesh

2

I would set debug mode on within active record and execute your queries
within a MYSQL editor and look at the query plan. My guess is it’s the
queries that are slow and you’re in need of some indexes.

3

Karl B. wrote in post #965770:

I would set debug mode on within active record and execute your queries
within a MYSQL editor and look at the query plan. My guess is it’s the
queries that are slow and you’re in need of some indexes.

Thanks a lot for the suggestion Karl.
Adding indexes helped a lot, even with very light loads!

Write time is down to less than 1 second.

I was also thinking about the pool size – do you have any
recommendations for setting the pool size? I would like to get the
database lookups to even lower values…

Also, any way to speed up has_and_belongs_to_many apart from creating
indexes on the join table? Does specifing an explicit ‘:through’
(instead of letting rails guess by joining the names of the tables)
help?

Best,
Radhesh

4

On 12/02/2010 02:31 PM, Radhesh K. wrote:

I was also thinking about the pool size – do you have any
recommendations for setting the pool size? I would like to get the
database lookups to even lower values…
Pool size is pretty subjective. I have yet to see a definitive guide on
selecting a pool size for any database. What is basically comes down to
is how many users do you want accessing the database at the time time?
The more resources you have on the server, and the faster your disks,
the more you will be able to accommodate. For a single user, a larger
pool size will have no effect on query performance.
Also, any way to speed up has_and_belongs_to_many apart from creating
indexes on the join table? Does specifing an explicit ‘:through’
(instead of letting rails guess by joining the names of the tables)
help?
Specifying the :through options should have no effect on query speed.
The main optimization I usually see missed when dealing with
associations is eager loading them when you know they are going to be
needed. For a simple (contrived) example in Rails 3, see
db.sql · GitHub. As you can see, we save a query by
telling it to load the posts association in advance. If we had more
authors, we’d save more queries. For more information on this see
Active Record Query Interface — Ruby on Rails Guides under “Eager
Loading Associations”.
Note if you are using Rails 2.3.x you’ll instead use the :include option
of the find method.

5

Since you are running in threadsafe mode, i don’t think having a bigger
pool will help.

As far as joins go, just make sure your indexes are on the correct
columns. In this case it will be the foreign key columns.

For really speeding up your application, i recommend NewRelic and no i
don’t work there ;-).