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

[Khalid Baheyeldin]

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.
>>
>
> Correct, I believe sqlite has table lock for writes which makes it a poor
> choice when there are many concurrent insert/updates.


I would assume it is even worse, and locking the entire databases, simply
because a sqlite database is a single file.

This precludes use in rich CMS sites then.

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.


> I see there's a Drupal-sqlite project btw.
>

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).

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,


Never saw that. Perhaps a combination of certain versions of software
packages?

On Ubuntu 8.04 LTS did not see this problem at all.


> 100% of CPU usage etc)


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.


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

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.

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

For some applications, PostgreSQL is just too slow (e.g. high traffic Drupal
sites). Not a realistic option.

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.

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.

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.

Would be great to have this discussion at the KWLUG meeting on May 3rd as
part of the MySQL presentation.
-- 
Khalid M. Baheyeldin
2bits.com, Inc.
http://2bits.com
Drupal optimization, development, customization and consulting.
Simplicity is prerequisite for reliability. --  Edsger W.Dijkstra
Simplicity is the ultimate sophistication. --   Leonardo da Vinci
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://kwlug.org/pipermail/kwlug-disc_kwlug.org/attachments/20100420/a85ccab2/attachment.htm>