The Old Joel on Software Forum: Part 3 (of 5)

MySQL or Postgress

Hey all,

I'm working on a project, to bring something like Friendster to my language - portuguese. I lost a good opportunity to do this with blogs in 1999 (opendiary at the time), because I lacked the resources to keep a server up serving a million people updating it daily.

Friendster isn't as resource-hog as a weblog, but it has viral marketing features that makes it grow very fast. I'm worried because my current tools (ASP & Access/SQL Server) do not scale well. I'm developing the website in ASP, but will convert it to PHP before launching.

Now, my server supports MySQL and Postgress databases. Which one should I use? I want speedy, and will not have transactions. But I may have to change my host provider in the future, so I will also need to be able to backup/restore databases with easy.

Am I wrong to think that a PHP/(MySQL or Postgress) combo is way better than ASP/(Access or SQL Server)? I've heard of php websites with moret han 200 users per minute and working well with only a dedicated server. Is that really feasible?

Thanks for any suggestions!

Mauricio Macedo
Friday, December 26, 2003

"I'm worried because my current tools (ASP & Access/SQL Server) do not scale well."

Yes, I could not agree more. You need full blown J2EE for your site. There are no large successful sites written with Microsoft technologies. None.

Mike
Friday, December 26, 2003

Oops, make the options PostgreSQL or MySQL.
Sorry!

Mauricio Macedo
Friday, December 26, 2003

I use PostgreSQL on Linux, it is more solid and more SQL-99 compliant than MySQL. Even though MySQL might be slighly faster in certain situations, I'm more concerned about reliability and compatibility.

If you want to run on Windows, then your only choice is MySQL.

jim
Friday, December 26, 2003

Mauricio - Sounds like your description lends itself to MySQL. It is fast, you don't need transactions and most web hosting companies use the PHP/MySQL combination (or so it seems from where I'm sitting).

Mike - "There are no large successful sites written with Microsoft technologies. None. " Does dell.com not count? Seems like they are kind of successful.

m
Friday, December 26, 2003

I was being sarcastic to the op's view that sqlserver and asp is unscalable. My point was there are many sites that show that "I'm worried because my current tools (ASP & Access/SQL Server) do not scale well." is pure ignorance.

Mike
Friday, December 26, 2003

Mike - I don't a spare 1 Gb of RAM for J2EE. My website is very, very simple. I'm very surprised of the simplicity of Friendster, and how it was able to reach 1 million registered users. The /concept/ is strong.

m - you're right, seems like MySQL is the best option, as they even created an acronym for the solution - LAMP! *laugh* I will reasearch a bit more about the backup/restore functionality, though.

thanks for the input!

Mauricio Macedo
Friday, December 26, 2003

Mike wrote:
I was being sarcastic to the op's view that sqlserver and asp is unscalable. My point was there are many sites that show that "I'm worried because my current tools (ASP & Access/SQL Server) do not scale well." is pure ignorance.

Maybe I wasn't clear enough. They do not scale well according to my budget. I want to serve thousands of users daily, with a simple server. I will start with a shared server, and can afford a dedicated one in the future. But I can not afford a server farm.

Now, if you tell me of a windows box with ASP/SQL Server capable of serving 100 different users per minute without coughing up or crashing and costing $50-$200 per month, then I'll be quite impressed and very thankful!

Then again, I have heard of LAMP boxes doing just that. If this is really true, it means that I will be able to support my project for months without outside investors! Mind you, if I am getting into all the trouble of using another platform than the one I know, it is because I know the limitations of the ASP platform for high traffic websites and am trying to find a better solution.

Mauricio Macedo
Friday, December 26, 2003

Mauricio, don't take our word for it - you can get SQL Server on a 120-day demo for the cost of postage. Then get any of the ASP or ASP.Net demo sites available online, or write your own test pages. Then get the Web Application Stress Tool (WAST), the Microsoft Application Center Test (with Visual Studio.Net), or any other stress test tool.

And bang on it yourself.

I know in a previous contract I had an app with hundreds of users accessing hundreds of thousands of documents via ASP.Net and SQL Server (one box each) - the CPU load was generally 0%.

Best of luck with your venture!!!

Philo

Philo
Friday, December 26, 2003

By the sound of it, the scalabillity of the languge it self is not something you need to concern yourself with. I.E. It sounds like not a lot of program logic, but tons of database access. In such applications the bottleneck is always the database.

My 2 cents are:

Avoid Jet. MySQL will probably serve you best since it has (afaik) the best read performance. MS SQL and Postgre will definatly work too though.

Use connection pooling. Setting up and dropping database connections is a performance killer. Can you do this in ASP? I pretty sure you can do it in ASP.NET and PHP.

Sometimes, when one platform is considred to be the fastest, everyone starts talking like all the other platforms are slow as f*ck. If thinks were like that we wouldnt have to have performance tests to begin with. What I mean to say is, the diffrences are not as big in reality as they are in our heads, and there are other factors that may be more important.

Eric DeBois
Friday, December 26, 2003

I've written some simple stress tools and even on the cruddy K6-450mhz 384mb test machine, MS-SQL handled nearly a thousand simple inserts (no transactions) per second even with 3-4million rows in the database.

That's a best-case scenario (no concurrent reads to induce thrashing, no transations or locking, etc) but it was still an impressive showing, I thought.

John Rose
Friday, December 26, 2003

Mauricio,

With thousands of users without using a web farm you will have to worry about forking with Apache, at least the 1.3x line, not sure how the architecture has changed in 2.0x. If you only have one server you may want to look at Zues, AOL Server, IIS as they have a persistant pool of connections and don't fork. Also you will not want traditional cgi as this is more forking. This is why AOL Server with the embedded TCL was so scalable.

I was joking about the J2EE. If you don't like the ram hunger of java because you only have one box, you aren't going to like forking either.

Read up on Greenspun's stuff. It is old in web time, but the principles still make sense.
http://philip.greenspun.com/panda/index.html and specifically this one
http://philip.greenspun.com/panda/databases-interfacing

Mike
Friday, December 26, 2003

I don't think anybody doubts that MS-SQL Server is a pretty tough database that can handle big loads. It's just that for our inquirer, the budget doesn't support it that well. He will have much better luck with the MySQL/PHP combination on a low budget.

What I have found though is that C/MySQL will scale better. If the processing to be done is simple, PHP can hold it's own with a C program. If there is more complex processing, such as processing XSL stylesheets or some variety of template engine, the C program will run faster and put less stress on the server.

This comparison was done without connection pooling. I stopped using connection pooling some time back because it was collapsing under high loads, but that was a major version number ago for PHP. If the current version can sustain connections under load, that might make for some real gains.

Clay Dowling
Friday, December 26, 2003

Mauricio, from your comments it looks like you intend to use a shared server rather than a dedicated one. In one post you said that you don't want to pay $50-$200 per month, but if you pay less than $50 then you'll probably have to use a shared server. In that case, the choice of DBMS will be far less important than the load placed on your server by the other users on the same machine. See if you can get a guarantee from the hosting company about the amount of CPU and RAM that will be reserved for you. Then you can start worrying about the DBMS...

Oren
Friday, December 26, 2003

Clay - I understand where you're coming from, but his budget concerns seemed to be about "web farms" and "database farms" as opposed to the cost of single machine licensing - I was simply saying that it's entirely possible a single machine solution will suit him fine.

And if even single-CPU SQL is too high, there's always MSDE, which is nicely suited for small web applications, since connection concurrency is a lot lower. Then growing into SQL if necessary is very easy.

Philo

Philo
Friday, December 26, 2003

"I don't think anybody doubts that MS-SQL Server is a pretty tough database that can handle big loads. It's just that for our inquirer, the budget doesn't support it that well. He will have much better luck with the MySQL/PHP combination on a low budget."
------

Oh yeah, good point! I forgot some people have to pay for their software. :P

John Rose
Sunday, December 28, 2003

Thanks all!

I will go with LAMP (Linux, Apache, MySQL and PHP). My website is expected to have a heavy load, and each page is customized for each user. I can't figure out much space for optimization, and we *tested* a similar website with ASP/SQL Server using a stress test tool. IIS/SQL Server would not support more than 100 page requests per minute. We were using two servers (Pentium III 512 RAM and SCSI HDs), and at a certaing point SQL Server starts to take more and more time to answer requests (HD spinning madly), making IIS timeout. It gets worse because if the user does not see anything after a few seconds she is prone to hit reload!

So I am not an expert, but I never saw a high volume site behaving well under ASP without some serious investment.
As I wil lstart on a shared server and can only afford paying $500 (top) per month to keep the service up until it attracts (hopefully) a buyer, I think that LAMP is the best solution in my case.

Mauricio Macedo
Monday, December 29, 2003

With all due respect, you did something wrong.

I'll run WAST against a complex asp.net web app I have at home tonight and post the results; I can pretty much guarantee I can get better than two hits per second...

If you want to go with LAMP, go for it. I'm just trying to make sure it's an informed decision. :-)

Philo

Philo
Monday, December 29, 2003

You can get a dedicated server for much less than $500 a month here: http://www.server4you.com. In my opinion you should prefer a dedicated server at least because of the performance guarantee and greater dedicated memory.

Seun Osewa
Sunday, January 18, 2004