[kwlug-disc] Newbie... Playing with Linux etc...

[Fernando Duran]

--- On Mon, 4/19/10, Khalid Baheyeldin <kb at 2bits.com> wrote:

From: Khalid Baheyeldin <kb at 2bits.com>
Subject: Re: [kwlug-disc] Newbie... Playing with Linux etc...
To: "KWLUG discussion" <kwlug-disc at kwlug.org>
Received: Monday, April 19, 2010, 10:59 PM

On Mon, Apr 19, 2010 at 10:27 PM, Fernando Duran <liberosec at yahoo.ca> wrote:


--- On Mon, 4/19/10, Khalid Baheyeldin <kb at 2bits.com> wrote:



>> SQLite is not really an option for many real life situations, where you need concurrency (e.g. a web site that uses a content management system).



> I disagree. Sqlite has a concurrency issue (locks) with _writes_ only, so most web sites (lots of reads, infrequent writes) can be good candidates for it. See for example http://www.sqlite.org/whentouse.html ("Websites" section) or http://www.appliedstacks.com/NewestFirst/SQLite .


That is interesting information.
 
I assume it has table (or even database) level locks, a problem I see with MySQL MyISAM, and use InnoDB which has row level locking to get around it.

-> (Sorry about the formatting, crappy Yahoo) Correct, I believe sqlite has table lock for writes which makes it a poor choice when there are many concurrent insert/updates. I see there's a Drupal-sqlite project btw.

> I have several web sites where I have migrated from Mysql to sqlite and I couldn't be happier: it's faster with less CPU and memory footprint. Plus I've seen too many mysql corruptions and data losses; I only use mysql if there is absolutely no other alternative and I don't know anybody with experience in both mysql and postgres that doesn't agree.


I rarely see corruptions and data loss on MySQL, unless the server was not shutdown properly and heavily written to tables are the ones affected (normally cache tables, or log tables, which can be discarded since it is transient information, at least in Drupal).


Any of those sites using the database heavily, like a CMS (Drupal for example?)

-> In the web hosting provider I work for I see performance issues in Mysql almost daily (a query stuck in the study phase, 100% of CPU usage etc) and table corruption not that often but is pretty common too. Sometimes is related to out-of-memory problems or other server failures but sometimes we can't just tell. Fortunately a check table/repair table fixes most of the issues but I've seen several cases where data was just lost. 

Mysql is probably (together with Apache) the most popular software used by our clients so it may be an unfair comparison but I can only remember one or two cases of having issues with postgres. This and other anecdotal stories are not hard evidence against mysql but I think they are valid indicators. 

What I find very powerful is what I mentioned before; in all cases so far, both from people I know or from the 'intertubes', seasoned db admins etc with experience in both mysql and postgres always choose the latter and have in general a low opinion of mysql. Conversely, mysql fans seem to either have a vested interest or they haven't worked with postgres. (I'd like to have a counter example).

Mysql took an early lead in the Linux web hosting world about 10 years ago when in was the standard RDBSM installed, probably because it was easier to use, easier to install and faster than postgresql. Since 5 years ago or so this is no longer true, and while mysql has been catching up in features and postgres in speed etc, mysql remains ridden with bugs and is not as solid as postgres. So the default choice for an open source RDBMS should be postgres, unless there are valid reasons: your expertise, legacy dbs, software than only works with mysql, good support for replication etc. 

TL;DR: Consider postgres or sqlite.


Fernando
http://fduran.com