Five second delay with Sqlite and ActiveRecord

Another project I am working on uses Sinatra as a framework, so doesn’t have all the fancy Rails stuff built in. Originally I was using my own database code, but that started to get a bit complicated, so I am using ActiveRecord (as is used in Rails) instead. This worked fine, apart from one thing… Occasionally when accessing pages I would get a random 5 second delay.

At first I thought it was the Sqlite built in timeout when the database is locked, as this defaults to 5 seconds. I tried setting the :timeout configuration option, but this had no effect. I gave up a few months ago and put it down to a bug.

I have been working on this project again recently. I have upgraded everything to the latest version, yet I was still getting this bug. I was wondering how I could be the only person. I had tried it on different machines and different architectures, yet I still had the problem. Today I decided to debug it, and ruby-prof (a great profiler, although a bit complicated at first) came to the rescue.

It turned up the it that a Kernel#sleep was being run for 5 seconds, as well as MonitorMixin::ConditionVariable#create_timer. I did a search thought the ActiveRecord source for sleep, but none came anywhere near close to 5 seconds. I did another search for Monitor (which I hadn’t heared of before), which had a result in one class ActiveRecord::ConnectionAdapters::ConnectionPool.

Apparently this enables a set number (default 5) of database connections at any time, to limit the number of connections that an app can make. This is somewhat useless though, as in proper deployment environments more than one instance of Rails is running, and these limits are not shared between instances, and under Mongrel (which most deployment techniques use), only one thread is active at a time. So in most cases (unless you fiddle about and create your own threads), there will only be one database connection active. It is also useless in my case, as there should only be one thread active at a time (I guess Sinatra does some stuff to use more than one), and Sqlite can only handle one connection so has its own blocking stuff.

Back to the 5 second delay. If all connections in the pool are in use (I am not sure how in my case, but they are…) a sleep command will be issued. This defaults to 5 seconds (…..) which I think in most cases is far too long. It can be overridden by setting the :wait_timeout option in the ActiveRecord configuration though, which unlike the :timeout setting this is set in seconds, however real numbers can be used. Here are my connection settings now:

ActiveRecord::Base.establish_connection(
        :wait_timeout => 0.25,
        :timeout => 250,
        :adapter => 'sqlite3',
        :database => 'database.db'
)

Hopefully this will help someone else who comes across this problem, and hopefully this will be changed to a more sensible value!