Rails - Jul 2010 - Sqlite3 busy and yet server has no load nor concurrency

Hi,

Strange behavior here. On my dev machine code works, but on the
production machine I get the dreaded:

SQLite3::BusyException: database is locked INSERT INTO "comments"
[...]

What kind of bug could yield such behavior? It''s impossible the
database
times out, there are no concurrent requests coming in, and the db is
only 150kB. Is there a possible race condition? Caching conflict?

In development mode, the DB time is less than 10ms and total time less
than 300ms with a timeout of 5000. The server is much faster than my dev
machine and loading a page on average takes less than 100ms.

Thanks for insights
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Check the permissions on the sqlite3 file and make sure your account
(www-data) can read/write.

On Jul 22, 4:57 pm, Fernando Perez
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
> Hi,
>
> Strange behavior here. On my dev machine code works, but on the
> production machine I get the dreaded:
>
> SQLite3::BusyException: database is locked INSERT INTO "comments"
[...]
>
> What kind of bug could yield such behavior? It''s impossible the
database
> times out, there are no concurrent requests coming in, and the db is
> only 150kB. Is there a possible race condition? Caching conflict?
>
> In development mode, the DB time is less than 10ms and total time less
> than 300ms with a timeout of 5000. The server is much faster than my dev
> machine and loading a page on average takes less than 100ms.
>
> Thanks for insights
> --
> Posted viahttp://www.ruby-forum.com/.
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Good idea, but permissions are set correctly.

The incredible thing is that this app uses the same code as another app. 
On the other one it works without any problem, and this one just decided 
to give me hell. I guess it''s a pretty stupid option hidden somewhere
in
my code that''s messing up everything, the thing is I don''t
know where to
look for :(
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

If anyone is interested, here the explanation:

I backed up the server without shutting it down, and it seems that the 
db file was in an inconsistent state. So by correctly backing it up and 
restoring the file, the problem got solved.

I was starting to get crazy!
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Fernando,

glad you solved the issue!!
However, I must say this: *don''t* use SQLite in production
environments!
It''s excellent for development, but flimsy and poor suited for
production.

Try MySQL or PostgreSQL for cost-free production ready databases.

Cheers!!

Fernando Perez wrote:
> If anyone is interested, here the explanation:
>
> I backed up the server without shutting it down, and it seems that the 
> db file was in an inconsistent state. So by correctly backing it up and 
> restoring the file, the problem got solved.
>
> I was starting to get crazy!
>   
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

> However, I must say this: *don''t* use SQLite in production
environments!
> It''s excellent for development, but flimsy and poor suited for 
> production.
Why is that? It''s so easy to setup, maintain, backup (and fuck up...), 
that I find it perfectly suited for production servers. Did you run into 
major issues?
> Try MySQL or PostgreSQL for cost-free production ready databases.
I used to run postgresql but I migrated to sqlite for its simplicity. 
I''m tired about super-performance-hypeness that''s painful to
maintain.
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Well, giving a second thought, it all depends. :)
If your app has very low concurrency and traffic, and if it is low in 
transaction intensity and you don''t have any security constraints, 
SQLite may be *it is* fit for production.

But... with SQLite you have poor support for concurrency, it''s no 
scalable, you can''t secure it more than set permissions in your 
directory, and since it is text-based, content is almost available to 
anyone (by default).


Fernando Perez wrote:
>> However, I must say this: *don''t* use SQLite in production
environments!
>> It''s excellent for development, but flimsy and poor suited for
>> production.
>>     
>
> Why is that? It''s so easy to setup, maintain, backup (and fuck
up...),
> that I find it perfectly suited for production servers. Did you run into 
> major issues?
>
>   
>> Try MySQL or PostgreSQL for cost-free production ready databases.
>>     
>
> I used to run postgresql but I migrated to sqlite for its simplicity. 
> I''m tired about super-performance-hypeness that''s painful
to maintain.
>   
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Unfortunately my apps have low traffic, so postgresql was really 
overkill.

I''ll report on this list if I run into any scaling problems, but I
guess
I be fine with sqlite for a good time.
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Fernando Perez wrote:
> Unfortunately my apps have low traffic, so postgresql was really 
> overkill.
Postgres isn''t overkill.  Juan Pablo is absolutely right: SQLite is 
inappropriate for production.  If you''ve been getting away with it till
now, consider yourself lucky -- and don''t push your luck.  Use a real 
multiuser DB for production.
> 
> I''ll report on this list if I run into any scaling problems, but I
guess
> I be fine with sqlite for a good time.
You most likely guess wrong.



-- 
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org

Sent from my iPhone
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Juan Pablo Genovese wrote:
> But... with SQLite you have poor support for concurrency, it''s no
> scalable, you can''t secure it more than set permissions in your
> directory, and since it is text-based, content is almost available to
> anyone (by default).
I just remembered that MySQL uses MyISAM tables by default, and they 
only support table locking. So it''s basically the same concurrency 
support as sqlite isn''t it? So why would MyISAM be cool and sqlite not?
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Fernando Perez wrote:
> Juan Pablo Genovese wrote:
>> But... with SQLite you have poor support for concurrency, it''s
no
>> scalable, you can''t secure it more than set permissions in
your
>> directory, and since it is text-based, content is almost available to
>> anyone (by default).
> 
> I just remembered that MySQL uses MyISAM tables by default, and they 
> only support table locking. So it''s basically the same concurrency
> support as sqlite isn''t it? So why would MyISAM be cool and sqlite
not?
Who the hell uses MyISAM, except for special cases?  Sure it''s the 
default, but even Rails'' migrations build InnoDB tables unless
otherwise
specified -- which, with MySQL, is the right thing to do.

But I''d really encourage you to use Postgres instead.  At the cost of 
*very slightly* more difficult setup, you get a *hugely* better DBMS.

Best,
-- 
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org

Sent from my iPhone
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Fernando Perez wrote:
> Juan Pablo Genovese wrote:
>> But... with SQLite you have poor support for concurrency, it''s
no
>> scalable, you can''t secure it more than set permissions in
your
>> directory, and since it is text-based, content is almost available to
>> anyone (by default).
> 
> I just remembered that MySQL uses MyISAM tables by default, and they 
> only support table locking. So it''s basically the same concurrency
> support as sqlite isn''t it? So why would MyISAM be cool and sqlite
not?
Hey, one other bit to that answer.  MyISAM locks the whole *table*.  I 
believe SQLite locks the whole *database*.  SQLite is really, really not 
meant for multiuser situations.

Best,
-- 
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org

Sent from my iPhone
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Well, for starters, that is why MySQL has two modes. MyISAM is only for 
very special cases and it is not the Rails default for MySQL.
Also, MyISAM locks a table, SQLite locks the whole file (hence, the 
whole database).
InnoDB is the way to go if you''ll use MySQL.
Postgre is another fantastic choice.
And if you''re feeling adventurous, try something like CouchDB (although
is a DB for a very different target than the traditional RDBMS)

Don''t get me wrong, SQLite is awesome for development. I use it
everyday
for my side projects and even I''ll start embedding an SQLite database 
for a Gnome2-ruby application I''ll be developing soon, but only for 
config data and stuff like that.
But, in case of Web Applications, I would rather go with something much 
more elaborated than SQLite.

Cheers!

Juan Pablo

Fernando Perez wrote:
> Juan Pablo Genovese wrote:
>   
>> But... with SQLite you have poor support for concurrency, it''s
no
>> scalable, you can''t secure it more than set permissions in
your
>> directory, and since it is text-based, content is almost available to
>> anyone (by default).
>>     
>
> I just remembered that MySQL uses MyISAM tables by default, and they 
> only support table locking. So it''s basically the same concurrency
> support as sqlite isn''t it? So why would MyISAM be cool and sqlite
not?
>   
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

> Hey, one other bit to that answer.  MyISAM locks the whole *table*.  I 
> believe SQLite locks the whole *database*.
Indeed, you''re right. I''ll stick to PostgreSQL then.
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.