From: jzakiya <jzakiya@...>
Date: 2006-12-05T13:25:08+09:00
Subject: Re: MySQL and SQLite question

David Vallner wrote:
> nephish wrote:
> > I know that the SQL does not support all of the same abilities
> > that MySQL does...
>
> From my own experience, some 80% of MySQL users are stuck at versions
> 3.x in their heads and don't use fancy features like transactions or
> referential integrity enforcement anyway.
>
> > but, is there much difference with regard to how it
> > is hanled by ActiveRecord ?
>
> ActiveRecord has very basic requirements on the underlying database
> engine. Either should be sufficient.
>
> > I was just curious as to how they may
> > compare from a rails point of view, or if there are some gotchas out
> > there i may need to be aware of.
> >
>
> ActiveRecord has a very strongly programmer-centric approach to
> persistence, so it might not be all too gentle to the database. Using
> SQLite will remove the network communication overhead, but I -think-
> SQLite only uses rather coarse transaction isolation, table-level, or
> even file-level locking. This is very bad for scalability with
> write-access users and could literally blow up performance.
>
> Either way, Rails lets you switch underlying databases with reckless
> abandon. Do the obvious sensible thing, do the benchmark against
> anything you feel comfortable with (I'd go SQLite for development
> because it's simpler to get running, YMMV), and then do some load
> testing with both the database engines, and in the case of MySQL, with
> memcached or another caching mechanism to mitigate the network
> connection overhead. (That shouldn't be too noticeable with a local
> connection.)
>
> Also, if you have a lot of write-access, you should benchmark both
> MyISAM tables and InnoDB tables. The former should be overall faster on
> grounds of supporting less SQL features (which you don't need with AR
> anyway unless you drop out to SQL often. In which case, ActiveRecord
> probably isn't the cleanest choice anyway and you might want to consider
> RBatis or something similar). However, the latter supports row-level
> locking, which should scale better with higher amounts of concurrent
> write-access transactions. Either way, a benchmark is what can tell you
> what will really happen.
>
> David Vallner
>
>
> --------------enig3BD27CA271309EC1BD8FFB7B
> Content-Type: application/pgp-signature
> Content-Disposition: inline;
> 	filename="signature.asc"
> Content-Description: OpenPGP digital signature
> X-Google-AttachSize: 188


This may answer many of your questions. I was just browsing
through it at a Borders bookstore today.

"The Definitive Guide to SQLite" by Mike Owens, Apress 2006

http://www.apress.com/book/bookDisplay.html?bID=10130
http://www.apress.com/book/supplementDownload.html?bID=10130&sID=3520