Re: help with site+database
On Wed, Jul 18, 2001 at 12:37:05AM +1000, Jeremy Lunn wrote:
> On Tue, Jul 17, 2001 at 11:52:14PM +1000, Craig Sanders wrote:
> > > Expecially if you have a dinamic PHP site, for me, MySQL and PHP,
> > > is the better and faster solution.
> >
> > actually, it's not. mysql is faster (these days, only marginally
> > faster) than postgres for extremely simple queries, but not for
> > complex queries. postgres blows mysql out of the water on anything
> > more complex than a single select of a bunch of fields from a single
> > table by a single user.
>
> I had to set up a server for a company who insisted on using MySQL and
> PHP and it hasn't caused any problems for them. I believe the load
> was high at times but I'm not sure how many queries they got per a
> second. Chances are that they were using mostly the one database user
> and they could have been using simple queries.
one database user-id is normal. but in a web environment you can get
any number of actual users. with mysql, if one of them happens to be
writing something to the table (e.g. to a sessions table, such as for
Apache::Session) then the whole table is locked and no-one else can get
access. this effectively serialises access.
this is absurd for a web site database. it won't scale beyond about 5
simultaneous users without a lot of very ugly client-side tricks (such
as using multiple tables to do a job that should be done by one table).
it's stupid things like this which convince me that mysql's much-hyped
blazing speed is more illusion than reality. when you look behind the
smoke and mirrors, you see a very different story.
btw, with postgres, only the row that is being updated is locked which
allows simultaneous use.
from the review i referenced: "With 30 clients pounding the test
machine, Postgres chugged along at 3.76 pages per second. That sounded
really bad at first, until we ran the same test on MySQL. MySQL did so
poorly that we eventually cancelled the test and reduced the concurrency
to just 5 users, and it still only fared .77 pages per second."
(if you haven't done so already then read the entire review)
look at the graphs in the review, it's clear that mysql maxes out
between 5 and 15 simultaneous users...whereas postgresql scales smoothly
to well over 100 simultaneous users.
> > PHP is OK, but i wouldn't use mysql for anything. i really don't
> > understand how anyone could trust their data to a toy.
>
> I've never used MySQL for a serious use by choice, but when I have used
> it with no choice it hasn't caused any problems. I think you are
> forgetting something though... some people would trust their data to a
> cow if they could get it in there somehow!
no, i'm not forgetting it. i see it all the time. for some reason, mysql
gets all the press even though it's a grossly inferior product.
> Well at least they trust it to that un-nameable redmond based software
> company known as the evil empire!
MSSQL/Sybase, for all it's flaws, is a real database. i'd rather use MS
SQL than Mysql.
> > there's an excellent article comparing the relative performance of
> > postgresql and mysql (with the same php based application) at
> >
> > http://www.phpbuilder.net/columns/tim20001112.php3
>
> I've found varying reviews to be mixed. Just by searching
> for "postgres mysql comparison" like you said I found this:
> http://phd.pp.ru/Software/SQL/PostgreSQL-vs-MySQL.html
he calls his comparison "an unbiased comparison". i'd call it "an
ignorant comparison". he's comparing apples and oranges and doesn't even
seem to realise that they're different things.
for instance, he says "MySQL has some basic provisions for transactions
and rollbacks." - i see that as proof that he doesn't know what
he's talking about. a transaction means that an entire set of
inserts/deletes/updates is done as a single *atomic* operation. it is
NOT possible to implement transactions on the client side - it MUST be
done in the server. you can emulate them on the client side but all that
will give you is the *illusion* of having transactions.
further, i see that as proof that the mysql team don't know what they're
talking about, because THEY recommend emulating transactions in the
client.
like most M vs P comparisons, that is using an old version of Postgres
against mysql. 6.5 is ancient.
and he says that Postgres is less stable than Mysql. that's ridiculous.
i've been using postgres for years on several servers, running a variety
of databases (some running one or two huge databases, some running
dozens of small to medium sized databases) and have *never* had a single
problem with it - it has been as solid as a rock.
the people who have had trouble with postgres all seem to be running on
redhat. that tells me that our postgres maintainer (Oliver Elphick) is
doing an excellent job, and that the redhat packager is doing a lousy
job.
i've seen quite a few simple benchmarks that seem designed to take
advantage of mysqls's simplicity....they show that mysql is MUCH faster
than Postgres 6.5. 6.5 is ancient.
the best comparison i've seen is the one i referenced above. not because
it shows that postgres wins, but because the guy actually knows what
he's talking about - and performed REAL-WORLD comparisons on an existing
large database (the sourceforge.net database)
prior to that article, he did an earlier comparison (against pg 6.5 or
7.0) which came out strongly in favour of mysql...there should be a link
to it from the URL i provided. a few months later he did another review
and changed his conclusion based on the evidence. both articles are well
worth reading for anyone thinking about using a database for the web.
> I do have a serious use for a database soon so I'll be sure to test
> both database packages out myself. Replication will be important
> though and last time I checked mysql didn't have any which is pretty
> useless (but I think they might have been implementing it).
the main problem i have with mysql is that it is a toy which has been
hacked on and extended so that it can pretend to be a real database.
postgresql (and oracle and sybase and ...) were designed to be real
databases from the start.
initially, mysql was a LOT faster than postgres - because mysql cut a
lot of corners while postgres did things Right<tm>. Postgresql achieved
most of their design goals about 12-18 months ago, and they have been
concentrating on fine-tuning (incl. performance) since then...and the
results of that really show. 7.0 is much faster than 6.5, and 7.1 is
much faster again.
these speed improvements basically wipe out the single advantage that
mysql ever had over postgres.
so, postgres started with a good design and is now being fine-tuned
and tweaked for performance. it's fast now at version 7.1, with more
improvements in store for future versions.
by contrast, mysql started with a bad design that was extremely fast and
has had (and is still having) new features grafted on to it to enable it
to compete with real databases. it should be obvious where that is going
to lead. those new featurs are going to come at the price of stability
and speed.
craig
--
craig sanders <cas@taz.net.au>
Fabricati Diem, PVNC.
-- motto of the Ankh-Morpork City Watch
Reply to: