Mysql vs. Postgresql

[Kbee]

I use Postgresql[largest table ~8M records] daily in my company and find that performance wise it is very good (just remember ,Vacuum 3x/week).While Mysql[largest table ~3M records] , which is being used for a few systems (one being a complicated Chat system) is slow and has issues with stability.
So , my question is why then do people continue to promote and use mysql(3.23.x) with half hacked transaction support over a far more stable and robust Postgresql (7.2.x).
What do you look for in a RDBMS , Speed , power, Features or price.
-Kurt ;D

[CKnight]

This is why:

http://www.mysql.com/information/benchmarks.html

[Kbee]

So then Craig your only consideration is speed. Tell me this, have you ever tried to explain why (Mysql)server downtime is so high to your boss ? or why mysql during a crash, at times of heavy inserts you get a "lovely" corruption of its tables , extending your downtime as you run a myisamchk or reload the DB from backup ? But if speed is your only consideration well good luck.
What of unsupported features of mysql eg. stored procedures, triggers or Foreign keys, (heres a nice one) "ON DELETE CASCADE" is not supported nor no B-tree indexing or what is that crap HEAP table implementation such that if you are not very carefull with your system resources can result in DB errors (and data loss).
I will give them this much , Mysql 4.0 does support In memory Query Caching which is very cool(tm)
-Kurt

[CKnight]

Who's Craig?

I'd respond to the points you've raised but I've never experienced any of the aforementioned faults. Maybe my inserts aren't big enough, who knows, but it's been my experience that MySQL is as stable as Linux itself. Don't get me wrong, this is just my personal experience (though I've never some across anyone who's faced the issues you've raised). And how much down time have you actually experienced with MySQL or any DBMS?

It's a known fact in the world of MySQL that it's slow on inserts, I'll give you that, but it shines in other areas to make up for the faults, selects for instance. Yes, again I'm basing my argument on speed but the bottom line is, it's the only issue that has ever come up in my time working with MySQL.

MySQL 4 will have better support for standard features like stored procedures so maybe you should wait on this highly anticipated version before you make your judgement.

Keep in mind, MySQL is the youngest player in the DB market, it needs time to mature.

[Chris]

From a lay-mans perspective...why then is MySQL the most popular DB promoted/offered by Web Hosts? (from what I've seen)

[CKnight]

Because of performance. For a hosting company speed is probably the biggest bottom line. They'll sacrifice a lot once they have that feature.

MySQL is sucky on inserts and updates, but great on selects.
The vast majority of website hits are views, not edits. People look at information more than they change it. A DBMS that sacrifices insert speed for selects makes the perfect application for this purpose. There are other benefits, but from a lay point of view, that may be the easiest to explain.

[Kbee]

Sorry I thought that your name was Craig Knight however I may have been assuming incorrectly.
The problems i have raised are not unique to my company. However this does not mean that things have not improved with later releases (we have recently upgraded production servers to 3.23.50) .
Actully Mysql is very fast on INSERT since traditionally it did not support Transactions -does now with InnoDB- however now with innodb we have a insert buffer to deal with large INserts and preven D state processes (if you use innodb and have slow inserts set autocommit to 0).
We experience very litte (less than 5%)downtime with Postgresql and Oracle (I don't touch this) and With Mysql between products such as OAS,Tacoda,Flatfox and Chatxtra we get about 80% uptime excluding Hardware related downtime.
I have been using Mysql for long enough to know what it lacks and why it will never hold a candle to a REAL (ANSI compliant ) RDBMS. As for 4.0 we have been using this in test since Feb of this year and I am impressed with it's speed and finally the ability to use more compilent SQL with Innodb.
While it is young i just wish people would stop hyping it so much , after all a DB stores important data and not just used of random file access. If all you want is speed in you DB then i guess you are correct , use Mysql.
-Kurt
P.s. I have Mysql 4.0.2 and it still does not support Stored procedures, i guess it may come later.
To get similar insert speed with Postgresql try using COPY (eg. COPY MY_TABLE FROM '/path/to/kurts/data_file';) however this method while fast does not activate/utilize triggers.

[Kbee]

From a lay-mans perspective...why then is MySQL the most popular DB promoted/offered by Web Hosts? (from what I've seen)

This is a due to the fact that many open source (small) apps are written to make use of it hance the name LAMP[Linux Apache Mysql Perl/PHP] and the fact that it is fast on inserts and somewhat fast on selects.
Larger companies for -example mine- tend to use Postgresql or Oracle since stability is more important than speed.
That Mysql benchmark is very suspect , since i cannot find if they did indexing on heavly inserted tables (which is bad) just how the postgresql DB was tuned or why vacuuming was not done.
However small lightweight apps usually require less power and stability.
-kurt

[RobyG]

MySQL's speed is probably due to the fact that it does not offer certain features such as triggers, stored procedures, etc

More mature RDBMS offer these features so it is not a surprise that they would be slower. From my experience, large companies are more concerned with data integrity than say speed. It is believed by many that true RDBMS must have all the bells and wistles and I am beginning to agree with them. I would go with Postgresql anyday because I am able to store most of the business logic at the database level so that changing or adding front ends using Delphi, Java, VB , etc is easy.

Since we are on the topic, an excellent Database Server is Advantage Database Server (ADS) by Extended Systems (of which I am a partner). There is of course a linux version and Win 98/NT/2000/XP version. I have been using it as back-end for projects for a few years now. Go to www.advantagedatabase.com and see for yourself.

- RobyG

[Kbee]

Hey Rob.
      I hope this is not a reflection on the quality of ADS ;D :P
Active Server Pages

error 'ASP 0126'

Include file not found


/NR/exeres/00000695mhxglvoabhyzzamn/Channel+Default+Redirect.asp, line 1

I guess the site is down.
-Kurt
The include file '/NR/System/Access/Resolution.inc' was not found.

[CKnight]

I heard about this a little while back. Didn't get around to trying it. From your experience, why should I choose this over MSSQL for example?

[Kbee]

I heard about this a little while back. Didn't get around to trying it. From your experience, why should I choose this over MSSQL for example?


Are you refering to Postgresql or ADS, sorry but that is the dissadvantage of a non threaded Mailing list.
;D

[CKnight]

ADS

[Chris]

Are you refering to Postgresql or ADS, sorry but that is the dissadvantage of a non threaded Mailing list.
;D

Yes I will agree that this is one of the disadvantages of a non-threaded discussion forum, :-\ however this can be easily overcome by using the quoting feature :D. I've found that threaded discussion forums can get very hard to navigate due to the threads that spin off in different directions.

[RobyG]

why should I choose this over MSSQL for example?

Go to www.advantagedatabase.com and go to benchmarks. This will indicate why we choose ADS over other databases. One of the major things is the cost. MSSQL is far more expensive than ADS. ADS can handle hundreds of concurrent users and scales very well. Extended Systems also boasts that you do not need an DB administrator for ADS. There will be no database corruption.

[Kbee]

Extended Systems also boasts that you do not need an DB administrator for ADS. There will be no database corruption.

The only two ways i know that someone can say that there is no DB corruption is that :-

1) They address a "raw" device, therefore not having to deal with the various filesystem locking issues that arise. Since it would only have access to a raw device, the DB would not have to worry about calling fsync() to write it's data or waiting for the OS , thereby circumventing
the VFS altogether. This what Oracle and Sybase do on Linux,IRIX,AIX and Solaris. However recent benchmarks suggest that the theoretical speed advantage is not there, just the write assurance.

2) The other method a DB will assure proper writing of data is to call fsync() after every INSERT. This is a big performance penality that we had to deal with till we upgraded from postgresql 7.0 to 7.1.x (and 7.2.x).This does not circumvent the filesystem, and depending on amount of system IO, and kernel version, the write (instead of flushing to disk) will enter the device_queue(like another buffer).

However this latter method is less reliable and while it will tell the application that the write is done (and database transaction is "committed"), because it is scheduled, the system could possibly crash leaving the table write incomplete (corruption).However since most DB applications run at at high priority, this is better than nothing. IIRC windows2k/NT did not support assured writes via raw devices. Windows of cource may have ways of doing dirty_buffer flush scheduling that i don't know of.
-Kurt
;D

[Kbee]

Hey Rob,
      You may wany to check this site,
http://pgadmin.postgresql.org/pgadmin2.php?ContentID=16
and since a picture is worth a thousand words :-
http://pgadmin.postgresql.org/pgadmin2.php?ContentID=20

[Kbee]

For The adventurous who wish to try running Postgresql (a very full-featured RDMS) instead of Mysql , here are some scripts and links to convert your backend DB , you may want to read the first link to get a feel as to just why I prefer postgresql :-) ::-
http://www.designharbor.org/Coding/opentut.php3?mn=&pn=t&id=67&page=1&
http://www.xach.com/aolserver/mysql-to-postgresql.html
http://ziet.zhitomir.ua./~fonin/code/my2pg.pl
http://65.108.58.129/programs/mysqlphp2postgres-0.92.tar.gz
-Kurt