SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2002
    Location
    New York
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MSSQL VS Mysql VS POSgres

    We own a site which got close to 300K users and about 10 GB of data in MSSQL. This application was written in ASP 2 years back and we are strugling to update lots of features because lack of good ASP programmer and simply ASP cant do that. Now we know we can move toward .net but we dont have expert developer. However we have solid PHP coder already avilable who has done many projects. Now before I talk to them do you guys think I should ask them to develop the application in PHP but keep MSSQL. Application is not complicated but old programmer saved pictures as binary on DB. I dont know how can we convert to mysql or anything else? How does PHP work on MSSQL? Or should we
    move this to mysql or POStgessql ? we have a huge traffic on the site so cant experiement that much, just looking for opinion from web application expert.
    We are currently hosting the site in 2 dedicated server and soon getting another server.

  2. #2
    SitePoint Enthusiast
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Postgres has shown to hold up better under extreme high volume and database size. Also, Postgres supports the "transaction" construct - so if your application requires a rollback upon transaction failure, it's the way to go.

    Be advised that this subject attracts a lot of "personal opinion" on both sides.

    We developed a large web based application with PHP and Postgres and have not found any major shortcomings. The app is mission critical to our clients and must be available 24/7. On the other hand, a similar app that is asp.net has been nothing but problems - from performance to security...

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PS - if you have a lot of images, don't store them in the DB! Storing the in the filesystem and using the DB to manage the files is faster and more efficient.

  4. #4
    SitePoint Enthusiast Mary_Itohan's Avatar
    Join Date
    Dec 2004
    Location
    e-Planet
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Be advised that this subject attracts a lot of "personal opinion" on both sides.
    I think you got the first personal opinion.

    Apart from the fact that i do agree with you on the subject about transactions, being a plus for postgres, coupled with the fact that MySQL dont support, views, stored proc, etc

    We used oracle and MySQL for a project and the MySQL db kept hanging, it dont mean its a bad db. I know a company that uses it on an enterprise scale since 1995 or so, and never had a complaint.

    However i strongly disagree on the issue of MS being a problem in every area.

    I have come to see that the perfomance of a db is based on the implementation. ie bad implementation and design gives you bad results.

    As far as APS vs. PHP and the like, these arguments are mostly biased. For anything but large enterprise solutions, you are really not going to notice much of a difference. You can get more performance and reliability gains by having a good programmer in either language.

    There is no 'right' OS, there is only the OS that is right for you.

    People will scream about anything.... give them 2 food choices, they take the "better" one and say the other one sucks.

    However if you dont feel comforatable with MS SQL i would advise you migrate, so you can have sleepless nights :-)

    Dont fight the flow :-(
    Mary

  5. #5
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by holdech
    Postgres has shown to hold up better under extreme high volume and database size. Also, Postgres supports the "transaction" construct - so if your application requires a rollback upon transaction failure, it's the way to go.

    Be advised that this subject attracts a lot of "personal opinion" on both sides.

    We developed a large web based application with PHP and Postgres and have not found any major shortcomings. The app is mission critical to our clients and must be available 24/7. On the other hand, a similar app that is asp.net has been nothing but problems - from performance to security...
    There are signifcant speed differences between MySQL and Postgres. A lot of the features that slow Postgres down (transactions, true foreign keys, etc.) were left out of MySQL for speed reasons. However, the transactional features are built into the InnoDB table type if you need them.

  6. #6
    SitePoint Enthusiast Mary_Itohan's Avatar
    Join Date
    Dec 2004
    Location
    e-Planet
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    we are strugling to update lots of features because lack of good ASP programmer
    lastly, i would advice you get good programmers, and a "CAPTAIN" who would sit with your long term vision to the end. That way, you have less headaches

    Application is not complicated but old programmer saved pictures as binary on DB.
    I also think saving images (BLOB) is a bad design implementation, esp when you db begins to scale.

    lastly, never, never, never...
    just looking for opinion from web application expert.
    look for opinion, opinion is cheap.
    Its FORD vs GMC ?

    I would advice you get a group of strategists, consultants and have a CLEAR 20/20 Vision, on what you intend to implement and strategize the way to go.

    HAVE A LONG TERM PLAN.

    and have long term programmers, not just simply bcos you have a good php programmer inhouse 2day.

    After all, you dont knwo who i am, i might just be giving you cheap opinion, then again, i might not be.

    It would amaze you, who fortune companies use ??? It would be burdensome switching every time, you loose a programmer.

    Mary

  7. #7
    SitePoint Evangelist comfixit's Avatar
    Join Date
    Dec 2004
    Location
    Pasadena
    Posts
    537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP can work with MSSQL fine. Unless you are worried about paying extra for use of MSSQL as is the case when you purchase partialy managed dedicated servers I would just stick with MSSQL as the database.

  8. #8
    Apache Expert i_like_php's Avatar
    Join Date
    Nov 2001
    Location
    Dallas, Texas
    Posts
    1,342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by holdech
    asp.net has been nothing but problems - from performance to security...
    that's funny considering i've had my site powered by .net and mssql and yet not a single problem.

    hmm i wonder why that is...
    i love php

  9. #9
    Non-Member DaveMichaels's Avatar
    Join Date
    Nov 2004
    Location
    US
    Posts
    535
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Hartmann
    There are signifcant speed differences between MySQL and Postgres. A lot of the features that slow Postgres down (transactions, true foreign keys, etc.) were left out of MySQL for speed reasons. However, the transactional features are built into the InnoDB table type if you need them.
    Not true (as of a few years ago). Postgres keeps pace with, and exceeds mysql in speed.

  10. #10
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There are signifcant speed differences between MySQL and Postgres. A lot of the features that slow Postgres down (transactions, true foreign keys, etc.) were left out of MySQL for speed reasons
    whats speed without brakes, transactions in my opinion are an important part of enterprise db.

    I worked as a consultant once, one of our clients was using a DB that didnt support transactions (ACID) and once had a problem with an update that was half completed. And that fiscal year was a headache to them.

    Anyway, not deviating from the point, i recommend, as Mary said

    There is no 'right' OS, there is only the OS that is right for you.
    However if you dont feel comforatable with MS SQL i would advise you migrate, so you can have sleepless nights :-)
    I would advice you get a group of strategists, consultants and have a CLEAR 20/20 Vision, on what you intend to implement and strategize the way to go.

    HAVE A LONG TERM PLAN.
    Lets not deviate from what you are seeking.
    ... Advice

    ...And i go with Mary_itohan's Advice

  11. #11
    SitePoint Zealot
    Join Date
    Jul 2003
    Location
    Los Angeles
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would never recommend changing so much at one time. If your heart is set on replacing the ASP code with PHP then do that while keeping your MSSQL database intact. You'll have bugs to work out in the new PHP code so why complicate matters with a new database also? Once the new PHP code has been worked out to your satsifaction then take the next step and investigate other database alternatives.

    As long as the new PHP code is not hard-coded for mssql calls then it shouldn't be too painful of a process to switch to a new database. Make sure you use a database abstraction layer like creole, adodb, etc. It won't make your PHP code 100% portable but it will ease the pain of switching to a different database like mysql, postgres.

    If your current database layout requires transactions, referential integrity(RI) and has lots of views and SPs then migrating to Postgres will be a less painful process. OTOH, things like fulltext search and replication are not easy with Postgres.

    If you decide to switch to MySQL just make sure you use INNODB tables if you require transactions and RI. Any industrial strength database relying on MyISAM tables is asking for a world of hurt.

    As for speed , Postgres and MySQL are the same if you are using INNODB tables in MySQL. RI always slows a database down and any sane person wanting ACID compliance will want transactions and RI.

    Good luck

  12. #12
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by afrika
    whats speed without brakes, transactions in my opinion are an important part of enterprise db.

    I worked as a consultant once, one of our clients was using a DB that didnt support transactions (ACID) and once had a problem with an update that was half completed. And that fiscal year was a headache to them.

    Anyway, not deviating from the point, i recommend, as Mary said




    Lets not deviate from what you are seeking.
    ... Advice

    ...And i go with Mary_itohan's Advice
    Speed is everything when you require it. High traffic sites would rather have something fast than something that has transactions but can't keep up with the load. Postgres has always struggled with high loads of traffic and still does. Yes, some of the problems have been fixed but it still cannot keep pace with MySQL (even when it's using InnoDB).

    The one thing that is a plus with Postgres is its licensing structure. It is licensed under the Berkley License which gives more leeway in its use.

  13. #13
    SitePoint Guru MikeBigg's Avatar
    Join Date
    Jun 2004
    Location
    Reading, UK
    Posts
    970
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think a lot of sense has been typed in this thread.

    My take on it (for what it is worth):

    1) Transaction are important for large multi-user database systems. I wouldn't want to work without them on large systems.

    2) Stored procedures and triggers are invaluable. Although PHP is very close to MySQL, you can't beat running code actually in the database. How about a delete trigger that moves the data into a deleted table. Then even if a mistake is made using direct sql, the data is not lost. If similar code was implemented outside the database (ie with PHP), the code can be bypassed.

    I wouldn't store images in any of the databases. I always put them in a folder and store a relative link to the folder in the database. This way the images can be on any drive available to the server ... some on a CD or DVD, whilst others can be on the hard drive before being moved to the CD. Or when you start running out of disk space a NAS drive can be added. Also makes the database backup files smaller and more manageable.

    Languages are like religions and everyone has their own opinions. I think the principle that the best language to use is the one the developer works best in holds some merit.

    Mike

  14. #14
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  15. #15
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MikeBigg
    I think a lot of sense has been typed in this thread.

    My take on it (for what it is worth):

    1) Transaction are important for large multi-user database systems. I wouldn't want to work without them on large systems.

    2) Stored procedures and triggers are invaluable. Although PHP is very close to MySQL, you can't beat running code actually in the database. How about a delete trigger that moves the data into a deleted table. Then even if a mistake is made using direct sql, the data is not lost. If similar code was implemented outside the database (ie with PHP), the code can be bypassed.

    I wouldn't store images in any of the databases. I always put them in a folder and store a relative link to the folder in the database. This way the images can be on any drive available to the server ... some on a CD or DVD, whilst others can be on the hard drive before being moved to the CD. Or when you start running out of disk space a NAS drive can be added. Also makes the database backup files smaller and more manageable.

    Languages are like religions and everyone has their own opinions. I think the principle that the best language to use is the one the developer works best in holds some merit.

    Mike
    Everyone has their opinions yes, but there are things that do work better in certain situations.

  16. #16
    Non-Member DaveMichaels's Avatar
    Join Date
    Nov 2004
    Location
    US
    Posts
    535
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Hartmann
    Speed is everything when you require it. High traffic sites would rather have something fast than something that has transactions but can't keep up with the load. Postgres has always struggled with high loads of traffic and still does. Yes, some of the problems have been fixed but it still cannot keep pace with MySQL (even when it's using InnoDB).
    That's just flatly not true. In my experience, and that of many others I know, Postgres has absolutely no problem competing with mysql in speed. Postgres even performs better. If anything, mysql is slowing down these days as they add all the features to become ACID compliant.

  17. #17
    SitePoint Enthusiast
    Join Date
    Jul 2002
    Location
    New York
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks everyone/ This is really helping me to make my decession
    SJ Innovation
    Indianherbcare.com : Buy natural herbs, nutritional
    products and vitamin supplements, beauty care.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •