tisdag, april 03, 2007

On ActiveRecord-JDBC performance

I have been a bit concerned about the performance of our component that connects ActiveRecord with JDBC. Since ActiveRecord demands that every result of a select should be turned into a big array of hashes of strings to strings, I suspected we would be quite inefficient at this, and I wasn't sure I could put all my faith in JDBC either.

So, as a good developer, I decided to test this, with a very small microbenchmark, to see how bad the situation actually was.

Since I really wanted to check the raw database and unmarshalling performance, I decided to not use ActiveRecord classes, but do executions directly. The inner part of my benchmark execution looks like this:
conn.create_table :test_perf, :force => true do |t|
t.column :one, :string
t.column :two, :string

100.times do
conn.insert("INSERT INTO test_perf(one, two) VALUES('one','two')")

1000.times do
conn.select_all("SELECT * FROM test_perf")

conn.drop_table :test_perf

It is executed with a recent MySQL Community Edition 5 server, locally, with matching JDBC drivers. The MRI tests is run with 1.8.6, and both use ActiveRecord 1.15.3. ActiveRecord-JDBC is a prerelease of 0.2.4, available from trunk. My machine is an IBM Thinkpad T43p, running Debian. It's 32bit and Java 6.

The results were highly interesting. First, let's see the baseline: the Ruby results:
      user     system      total        real
7.730000 0.020000 7.750000 ( 8.531013)

Frankly, I wasn't that impressed with these numbers. I thought Ruby database performance was better. Oh well. The interesting part is the JRuby AR-JDBC results:
      user     system      total        real
6.948000 0.000000 6.948000 ( 6.948000)
WOW! We're actually faster in this quite interesting test. Not what I had expected at all, but very welcome news indeed. Note that there is still much block overhead in the interpreter, so the results are a little bit skewed in MRI's favour by this, too.

3 kommentarer:

Unknown sa...

Were you using the native mysql for ruby (ie gem install mysql)?

Ola Bini sa...

Marcus, oh yes. Otherwise it wouldn't have been fair, now would it? =)

Sean Geoghegan sa...

I hate the "big array of hashes" method in Active Record. It is terribly inefficient for large result sets. It forces you to manually paginate things even when pagination is not required by the application, such as when performing some processing function on every row in a large table you either need to be able to fit the entire table in memory or you need to paginate and to make matters worse, MySQL gets progressively slower as the offset value grows. The worse thing is that it ignores all the work done by DBMS in this area, such as pre-fetching and cursors.

Maybe one day I'll submit a patch or create a plugin.