Programming (web/data/user management)

[Khamsin]

What language (perl, php, asp, etc.), SQL server (mysql, MSSQL, etc.), and web server (apache, iis, etc.) works best for a web-based application that supports a bunch of users all doing stuff at once, and can efficiently lock and queue database transactions, etc. to prevent corruption, and handle all other issues of many simultaneous users?

[The Stig]

PHP + MySQL is the "way" to go. Both are "free" more or less. Stuff like Microsoft SQL require very specific requirements/servers and cost more money. I'm not too sure about it locking data and such, but a lot of sites (i.e. this forum) is made with PHP/MySQL too so ...

[Snowknight26]

PHP+MySQL+Apache.

[Khamsin]

That's what I was kind of figuring. So, that being the case, are there any special issues I have to account for myself, or will MySQL automatically lock and queue transactions, etc.?

[Bardicrune]

How many simultaneous transactions are you talking about? What type of transactions (updates? queries? inserts? etc.)? It really depends on what you are trying to accomplish and what your budget is for the project.

As to your second question, yes, mysql can be setup for either record, or table locking.

[Khamsin]

How many simultaneous transactions are you talking about? What type of transactions (updates? queries? inserts? etc.)? It really depends on what you are trying to accomplish and what your budget is for the project.

As to your second question, yes, mysql can be setup for either record, or table locking.

Well, multiple queries aren't an issue, and multiple inserts to less a degree, but multiple updates are the sticking point. If, say, you have 100 people all updating some of the same info at the same time (say, a "last accessed" date for a common resource or something), then the database should be able to handle it? For collaboration of multiple people working on a single document, that's something I'd have to program myself.

Multiple inserts could be an issue if I had a sign-up sheet of sorts, and limited it to 10 sign-ups but had 100 people trying to sign up at once. It'd have to be able to know when 10 was reached, even if it happens during the middle of the process on someone else's end.

Personally, I've only ever done stuff for small groups never for large, so I'm just trying to find out what sort of concerns there are when dealing with a larger number of simultaneous users.

[Fallso]

What people have mentioned is pretty solid info. I wouldn't deviate away from apache really, IIS is right load of bollocks.

[Bardicrune]

Apache+PHP is the way to go for your web server unless you really need to program the site in .NET/ASP. However, even with .NET you can still run a MySQL database for your back end on a windows server. With your example of 100 simultaneous transactions, almost any of the SQL database types will work. It's only when you get to 300+ that you really need to be discerning about which database server you choose (and which filesystem you use).

[ismarc]

Depending on your performance requirements/number of simultaneous inserts/updates/etc., mySQL generally has some problems once you reach 500+ simultaneous transactions. Depending on what you're doing with it, if you assume 1 db handle + 1 server thread for each request, at an average of 5 requests a second, mySQL will be fine. Now, if you're spawning multiple handles for each request to try and speed up execution/page load time, at the same rate, you're looking at (at an average load time of 5 seconds per page) a rough maximum of 250 simultaneous db handles (assume 2 db handles for each connection). So, what this means is...if you are expecting 20 requests a second, or multiple handles per request and 10 requests a second, you should look at a higher performance DB solution like Oracle or postgres.

The main slowdown is going to be the db access, or poorly written code/queries processing the data you got back from it. If you're doing volumes of processing large amounts of text, I would recommend perl for the implementation language. If you're not (most likely) and instead processing small strings of text, PHP is going to be a better solution. PHP has fairly robust OO capabilities that are nice if you want to use it for custom datastructures or objects while still keeping the procedural approach that fits well for dynamic pages. Perl is similar, but may require a bit more learning to figure out all the intricacies of creating your own modules.

[Khamsin]

Thing is, I already know perl, but have been under the impression that it is slower and less efficient for something that is being used by multiple people and where speed is desired.

[The Stig]

Thing is, I already know perl, but have been under the impression that it is slower and less efficient for something that is being used by multiple people and where speed is desired.

One of the research papers I've read at university, to quote, mentioned that Perl "doesn’t scale well to busy environments". So you're quite correct.