Rails + Mongrel + Apache + MSSQL + Win2003 deployment issue

Rails Deployment
1

First off, a disclaimer: yes, I know, this is not an ideal production
environment. I do not have any control over it. Any replies of ‘lol use
linux’ or ‘mysql > mssql’ will be ignored; believe me, I would if I
could, but I have no sway over any of those choices.

Now, to the actual issue.

Background: our system is a legacy system, heavily dependent on stored
procedures. Take everything you know about systems that are heavily
dependent on stored procedures. Got it? Now double that. Literally a
good 3/4 of the app’s logic (that’s a guesstimate) is in MS SQL Server
stored procs.

In our development environment, we run Rails + a single Mongrel
instance. Our dev environment is not heavily trafficked, after all.
There are absolutely no problems in this environment. Everything works
beautifully. I use ActiveRecord magic where I can, call sprocs where I
have to, and life goes on.

In our production environment, things grind to a screeching halt. Here,
we’re using Apache 2.2 with mod_proxy_balancer to point to five Mongrel
instances. Basically, the problem arises because after calling three or
four stored procedures to do some data manipulation (adding, updating,
etc.) and then calling a select statement to get data for a simple view
page. When this happens, the select statement abruptly jumps from one
database connection to a different one - and is blocked at the database
level by the first one. I’ve watched this happen time and again, and
it’s very easy to reproduce.

The app times out forevermore, because all sessions seem to go onto the
same Mongrel instance, which is connected to SQL Server but being
blocked.

Any help you can muster would be fantastic, because while this app can
go live as a single Mongrel app (it’s not heavy-use by any means), we
need to figure this out or it’ll be a roadblock for future Rails use.

Thanks!
–Phil

2

On 7/9/07, Phil U. [email protected] wrote:

First off, a disclaimer: yes, I know, this is not an ideal production
environment. I do not have any control over it. Any replies of ‘lol use
linux’ or ‘mysql > mssql’ will be ignored; believe me, I would if I
could, but I have no sway over any of those choices.

I know you pain, so I’ll not make jokes :slight_smile:

Now, to the actual issue.

[…]

Could you explain “how” you’re calling 3 or 4 stored procedures? I
guess you mean 3 independant requests.

Example:

Hits to /myresource/1 works every time, but hits to /myresource/1/edit
hang…

I guess you need to wrap your queries into transactions, in that way,
will not be “jumped” by connection pooling.

Better info about this will help me understand your situation.

The app times out forevermore, because all sessions seem to go onto the
same Mongrel instance, which is connected to SQL Server but being
blocked.

That could be an apache problem, since is the one responsible of
balancing. Also, SQL Server should allow more than 1 connection from
the same process, and more than 1 connection from the same computer.

Any help you can muster would be fantastic, because while this app can
go live as a single Mongrel app (it’s not heavy-use by any means), we
need to figure this out or it’ll be a roadblock for future Rails use.

I guess you put your application in production mode (in your dev box)
and do some heavy load testing from different computers, right?

–
Luis L.
Multimedia systems

Leaders are made, they are not born. They are made by hard effort,
which is the price which all of us must pay to achieve any goal that
is worthwhile.
Vince Lombardi

3

On Jul 9, 9:12 am, “Phil U.” [email protected]
wrote:

good 3/4 of the app’s logic (that’s a guesstimate) is in MS SQL Server
instances. Basically, the problem arises because after calling three or

Any help you can muster would be fantastic, because while this app can
go live as a single Mongrel app (it’s not heavy-use by any means), we
need to figure this out or it’ll be a roadblock for future Rails use.

Thanks!
–Phil

–
Posted viahttp://www.ruby-forum.com/.

i assume you are ‘borrowing’ a connection from activerecord to execute
the procedures. as luis pointed out, try wrapping the commands in a
transaction block. unfortunately, i can’t remember the exact error
message, but in the past i had issues with mssql stored procedures and
rails. i was calling a single procedure, but it had read/write
operations within it. i resolved the issue by wrapping the command in
a transaction. however, the problem only occured when using ado
mode. it did not occur when using odbc. also, are you using ado or
odbc?

after using activerecord with ado mode for 18 months, a pesky ruby/ado
glitch with COM finally inspired me to move to odbc mode, which has
worked well for me other than a monkey patchable issue with the sign
dropping on negative decimals greater than -1.0 and less than 0.0.

4

Alex,

I have seen an error message of “Connection is busy with results for
another hstmt” when I run in ODBC mode, and it means
that a connection is still busy processing a query. SQL Server 2000
(fixed in 2005) only allows one active result set per connection.

It may be possible that the stored procedures return one or more result
sets and you need to use a find_by_sql call instead
of an execute call to actually retrieve the results of the stored
procedure. Because find_by_sql will actually pull the result of the SQL
statement, this
frees up the connection to do more queries.

However, this may or may not be your problem. What exactly do you mean
by “the select statement abruptly jumps from one database connection to
a different one”?

Are you running production on Windows or Linux/UNIX or Mac?

Wes

5

On Jul 11, 1:56 am, Wes G. [email protected] wrote:

procedure. Because find_by_sql will actually pull the result of the SQL
statement, this
frees up the connection to do more queries.

However, this may or may not be your problem. What exactly do you mean
by “the select statement abruptly jumps from one database connection to
a different one”?

Are you running production on Windows or Linux/UNIX or Mac?

Wes

windows (iis => fastcgi => sql2000)