Rails core - Dec 2005 - On Rails-0.14.4 as 1.0, good, except for Oracle speed

A vote of confidence for 0.14.4 -> 1.0:  We''ve successfully moved
our
app to 0.14.4.  We have the following code stats:

+----------------------+-------+-------+---------+---------+-----+-------+
| Name                 | Lines |   LOC | Classes | Methods | M/C | LOC/M |
+----------------------+-------+-------+---------+---------+-----+-------+
| Helpers              |   412 |   214 |       1 |      19 |  19 |     9 |
| Controllers          |   976 |   698 |       9 |      61 |   6 |     9 |
| APIs                 |     0 |     0 |       0 |       0 |   0 |     0 |
| Components           |     0 |     0 |       0 |       0 |   0 |     0 |
|   Functional tests   |  2213 |  1687 |      26 |     165 |   6 |     8 |
| Models               |  1278 |   684 |      26 |      59 |   2 |     9 |
|   Unit tests         |  2563 |  1912 |      19 |     219 |  11 |     6 |
| Libraries            |   562 |   267 |       4 |      27 |   6 |     7 |
+----------------------+-------+-------+---------+---------+-----+-------+
| Total                |  8004 |  5462 |      85 |     550 |   6 |     7 |
+----------------------+-------+-------+---------+---------+-----+-------+
  Code LOC: 1863     Test LOC: 3599     Code to Test Ratio: 1:1.9

We''re hitting both postgres and oracle successfully now that
we''ve
patched for #3133.

Downside:  we''ve seen a pretty significant slowdown on Oracle when
running our test suite (this may not be present as a production-time
slowdown -- we haven''t measured it).  Where prior to the move to 0.14.4
w/ #3133 we were seeing postgres running about 50% faster than Oracle,
now Postgres is roughly the same speed as before, but Oracle is
significantly slower.

Postgres:
unit: Finished in 24.523295 seconds. / 201 tests, 733 assertions, 0 failures, 0
errors
func: Finished in 14.954842 seconds. / 135 tests, 597 assertions, 0 failures, 0
errors

Oracle:
unit: Finished in 83.750658 seconds. / 201 tests, 733 assertions, 0 failures, 0
errors
func: Finished in 78.09833 seconds.  / 135 tests, 597 assertions, 0 failures, 0
errors

Previously we would''ve expected Oracle to run in ~36-38 seconds, and
~22-24 seconds, so the difference is quite visible.

Neither database is optimized (for better or for worse), and while
we''re
happy to see the open source alternative leading the closed source
behemoth, it''s a bit unfair if the difference is to be found in
oci_adapter.rb.

Rick
-- 
 http://www.rickbradley.com    MUPRN: 3
                       |  In my grand scheme
   random email haiku  |  of things, I plan to have
                       |  some random content.

> Neither database is optimized (for better or for worse), and while
we''re
> happy to see the open source alternative leading the closed source
> behemoth, it''s a bit unfair if the difference is to be found in
> oci_adapter.rb.
Best guess is that the change is driven by the final approach for 
handling reconnection on failure -- which proactively checks the 
connection before every db access. Since the core OCI library doesn''t 
provide a way of checking status, this is being done by a select against 
DUAL (in the OCI8AutoRecover#ping method).

Can you try modifying oci_adapter.rb, and change OCIAdapter#active? to 
use @connection.active? instead of @connection.ping. The 
OCI8AutoRecover#active? method doesn''t hit the db at all, it just 
returns the last known state. This will be faster, though it means 
you''ll actually get a db failure in your app before it reconnects.

In trunk this is line 222 of oci_adapter.rb.

If this corrects the performance issue, we can make this a configurable 
options -- better performance w/ a hit on db failure, or worse 
performance w/ better failure handling.

* Michael Schoen (schoenm@earthlink.net) [051212 14:15]:
> Can you try modifying oci_adapter.rb, and change OCIAdapter#active? to 
> use @connection.active? instead of @connection.ping. The 
> OCI8AutoRecover#active? method doesn''t hit the db at all, it just 
> returns the last known state. This will be faster, though it means 
> you''ll actually get a db failure in your app before it reconnects.
> 
> In trunk this is line 222 of oci_adapter.rb.
Sorry for the delay -- the dba''s were making some changes to the layout
on that system late yesterday and early today.  Since the baseline may
have moved in the process, I reran the tests more today.

I found that changing to .active? didn''t have any effect on
performance.
I started doing some more exploration.

It turns out that the performance hit is coming from the other change in
oci_adapter.rb -- the columns() method.

We had the following "patch" loaded out of our /lib/ directory to
protect us against the data_default whitespace bug from #2788.  Since
0.14.4 rolled we took the patch out:

if $".grep(%r,oci8.rb,).length > 0
  module ActiveRecord
    # Fix bug in data_default regex
    module ConnectionAdapters
      class OCIAdapter
        def columns(table_name, name = nil) #:nodoc:
          select_all(%Q{
              select column_name, data_type, data_default, nullable,
                     case when data_type = ''NUMBER'' then
data_precision
                          when data_type = ''VARCHAR2'' then
data_length
                          else null end as length,
                     case when data_type = ''NUMBER'' then
data_scale
                          else null end as scale
              from user_catalog cat, user_synonyms syn, all_tab_columns col
             where cat.table_name =
''#{table_name.to_s.upcase}''
               and syn.synonym_name (+)= cat.table_name
               and col.owner = nvl(syn.table_owner, user)
               and col.table_name = nvl(syn.table_name, cat.table_name)}
          ).map do |row|
            
row[''data_default''].gsub!(/^''(.*)''\s+$/,
''\1'') if row[''data_default'']
             OCIColumn.new(
               oci_downcase(row[''column_name'']),
               row[''data_default''],
               row[''data_type''],
               row[''length''],
               row[''scale''],
               row[''nullable''] == ''Y''
             )
          end
        end
      end
    end
  end

I can toggle a 3x+ performance hit (~22 seconds for a unit test or
functional test run to 77-80 seconds for the same) by toggling that
patch in and out on the 0.14.4 Rails install.

I''ve filed a trac on this (#3210).

Rick
-- 
 http://www.rickbradley.com    MUPRN: 40
                       |  motivation for
   random email haiku  |  emissions controls is to
                       |  reduce oil exports.