<br><br><div class="gmail_quote"><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class="im"><blockquote style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;" class="gmail_quote">
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.<br></blockquote>
<br>
</div>Correct, I believe sqlite has table lock for writes which makes it a poor choice when there are many concurrent insert/updates. </blockquote><div><br>
I would assume it is even worse, and locking the entire databases, simply because a sqlite database is a single file.<br><br>This precludes use in rich CMS sites then.<br><br>In Drupal for example, if the site admin configures the site to log node views (e.g. statistics module), or writing to the session table when crawlers hit the site, or logging certain info to the watchdog, ...etc.<br>
<br></div><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">I see there's a Drupal-sqlite project btw.<br></blockquote><div><br>Support for sqlite is now in core for Drupal 7 (the next version). It is meant only for testing and development in single user setups (one page load at a time). <br>
</div><div> <br></div><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div class="im"><blockquote style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;" class="gmail_quote">Any of those sites using the database heavily, like a CMS (Drupal for example?)<br>
</blockquote>
<br>
</div>In the web hosting provider I work for I see performance issues in Mysql almost daily (a query stuck in the study phase, </blockquote><div><br>Never saw that. Perhaps a combination of certain versions of software packages?<br>
<br>On Ubuntu 8.04 LTS did not see this problem at all.<br> <br></div><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">100% of CPU usage etc) </blockquote>
<div><br>Can't remember seeing this either, unless there are bad queries runnings (joining 12 tables for example). Using processlist shows which queries there are at the time.<br> <br></div><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
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.<br>
</blockquote><div><br>I see those occasionally, but it is on servers that were not shutdown properly, and tables are often transient (logs, cache, sessions, ...etc.). Yes, REPAIR works, and I even do not bother if the data is transient and just do a TRUNCATE.<br>
<br></div><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
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. <br>
</blockquote><blockquote style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;" class="gmail_quote"><div> </div></blockquote><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
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).<br>
<br>
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. <br>
</blockquote><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<br>
TL;DR: Consider postgres or sqlite.<br></blockquote></div><br>For some applications, PostgreSQL is just too slow (e.g. high traffic Drupal sites). Not a realistic option.<br>
<br>We have this discussion every year within Drupal. We do support both MySQL and PostgreSQL (and sqlite lately) in core. Contributed modules have sparse support for PostgreSQL though.<br><br>We then have people come and preach virtues of PostgreSQL (free, capable, ...etc.) and all that, but in the end no one is willing to step in and do the work needed. We have about one maintainer that comes in does some work and then disappear and PostgreSQL is left in the lurch.<br>
<br>We hear this speech of "PostgreSQL is better, MySQL is crap" too often, but when it comes to people putting their code where their mouth is, the reality is different.<br clear="all">
<br>
Would be great to have this discussion at the KWLUG meeting on May 3rd as part of the MySQL presentation.<br>-- <br>Khalid M. Baheyeldin<br><a href="http://2bits.com">2bits.com</a>, Inc.<br><a href="http://2bits.com">http://2bits.com</a><br>
Drupal optimization, development, customization and consulting.<br>Simplicity is prerequisite for reliability. -- Edsger W.Dijkstra<br>Simplicity is the ultimate sophistication. -- Leonardo da Vinci<br>