Re: MySQL vs. Postgres
> postgres is using record-level locking, what does mysql do, is it
> locking the whole table ? what are rollbacks? what other advantages are
AFAIK mysql locks whole table,
rollback is term used with transaction - thing is, you put some sql
statements inside transaction, and one of them fail, you rollback all your
changes so your database is in consistent state.
This is very important thing especially in networked apps (and that's what
you need sql server for, don't you?).
I'm probably a little bit biassed against mysql, since in their docs they
talk about transactions like their useless and that's why they haven't
implement them ( and not because they don't know how ;).
And according to my knowledge transactions are one of the fundaments
of database programming.
> there in using postgres instead of mysql ?
It's only my personal opinion postgres is more secure due to easier
administration and ability to define remote access permissions
easily and precisely.
> are planing to set up some complex web apps. So what do you propose?
> A solution would be to run postgres beside mysql on that server.
they are both sql server, with postgres being more advanced and mysql
being faster. There are only little differences in their sql
( there is book on postgresql being printed and accessible in pdf format
at www.postgresql.org, which highlights any additions to SQL/92 standard
so you can easily write SQL/92 conformant apps )
so it should be fairly easy to port all apps to postgres.
Of course it's easy to rewrite int(11) to SQL/92 sql type, but when you do
that on 100 customers apps it could be a major pain in ass.
Little note about speed - you gain much more speed by proper setup
(indices on other disks that actuall data, proper transactions,
smartly using subselects and things like that. ) that by using simplier
sql server. But you already know that first thing to optimize is an
algorithm not the compiler.
> and what do you propose for bigger sql apsp with permanent database
> usage. php , apache-module (perl or c), cgi ? what experience do you have
> with the performance ?
I've been doing some apps in php3 and asp with sql on oracle. also cgi in
perl and c.
CGI generaly is very slow thing, no metter if you use perl,c or asm.
but that's known fact.
If you want to be fast you use things like mod-perl or interpreters
like php or asp.
Based on my knowledge and experience the fastest solution are pure
mod-perl apps. but they're hard to impelement.
My favourite solution is Apache::Asp module, with which you get
transparent permanent database connections packaged with ease of
development and top-notch performance.
Second best solution would be php4 compiled with zend. You probably
already know that php4/zend is faster than asp. php3 is slower.
Probably, because differences ain't that big, maybe I've seen bad
benchmarks. For me it's easier to deploy perl-based solution then php-one,
but php-programmers are easy to buy and they're cheap. there ain't that
much perl hackers outhere, although perl is very easy to learn.
Although I have quite a lot of arguments to use perl and asp to build web
apps, argument about cheap programmers is very important and you can't
overlook it. Maybe situation is different where you live.
There is also another fast and advanced solution which is aolserver.
It's multithreaded (thing you get with apache 2.0 if you're brave, but
aolserver is stable, been multithreaded for like years..), the only issue
is programmers - If you can easily get people to programm in scheme or
lisp ( i think that's aolserver's scripting language.. maybe I've mistaken
it with sth more exotic ).
And here you've got the same issue as with perl - aolserver is fast,
scheme is quite easy to learn ( it took me two days to learn it enough to
pass some exams, and I must say that it wasn't luck, I actually learned it
) but you can't find scheme programmers out on the street. But if you can,
check out aolserver.
There are also things like Oracle Application Server, which is extremely
slow, probably due to overusage of Corba technology.
To sum things up
- php4+apache+postgres would be the cheapest and most promising solution
(php is evolving quickly, it is already quite nice tool, although
you get the feeling of using something young and not very mature)
- asp+apache+postgres - you get very fast development, ability to tune
your instalation as much as you want, all the nice stuff like
sessions, transparent persistent database connections ( you
write normal code, but Apache::ASP keeps cache of connections
and gives you already connected handle without you knowing
anything about it happening ). You get all numerous perl modules.
And it's real programming language.
But first you must find programmers who can write the code or are
willing to learn.
and perl is very RAM-hungry.
- php3+apache+mysql - with that setup you're set up.
it's most commonly used, most existing web apps are based on this
setup. php is evolving fast, mysql is very popular, there should be
many good programmers working on it, there's possibility that it'll
become SQL/92 compliant database someday... maybe.
Choose something you know or are willing to learn, something with a little
bit more features that you're going to use at the beggining.
Your customers and your mysql users are important. If they're not make
them move to postgres. I think postgres could get some bad carma due to
company which started promoted it - they made benchmarks showing that
postgres is fastest. It's untrue. mysql is faster.
And using files directly is a lot more faster anyway ;)
sorry for this quite long post, but I am in process of making the same
decisions, or maybe I went through that process not long ago.
regards, Eyck
Reply to: