From: nephish <nephish@...>
Date: 2006-12-05T15:00:12+09:00
Subject: Re: MySQL and SQLite question


jzakiya wrote:
> 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

Hey, thanks for the links, looks cool.
sk