From: David Vallner <david@...>
Date: 2006-12-05T06:23:12+09:00
Subject: Re: MySQL and SQLite question

--------------enig3BD27CA271309EC1BD8FFB7B
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

nephish wrote:
> I know that the SQL does not support all of the same abilities
> that MySQL does...=20

=46rom 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 ?=20

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.
>=20

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; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (MingW32)

iD8DBQFFdJG6y6MhrS8astoRAiX8AJ9Qvestd3/g288MczvFCHKToDFe4ACeITSp
nwqrnlEZGpaK1GSHhqcmAWk=
=zny9
-----END PGP SIGNATURE-----

--------------enig3BD27CA271309EC1BD8FFB7B--