I have a serious dilemma. I am about to embark on building a large interactive website but I am very confused.
I have read recent reports on my SQL failing due to too many users using a database at a given time.
The website I am building must be able to tolerate hundreds of thousands of users. Each will have a login name and password. Logins will timeout after say a 2 hour period of inactivity - therefore there will be constant read/write activity to a database to monitor the timeout function alone never mind anything else.
Everyone says that I should steer away from a flatfile approach but nobody gives me a good reason. My plan is to have one mySQL database/table with all the login and password data and then to have one flatfile for each user to manage the timeout function and all other personal data of that user. Can someone please advise me on both the a)flatfile issue and b)the mySQL issue?
Hmmm...well, if we're talking about 100s of thousands of users, MySQL can handle it. MySQL can handle a ton of raw data...the problem lies with concurrent users, from what I understand. Do you know how many people will be browsing your site during peak traffic hours? If it's more than several hundred, then I think you'd be better off with something more robust than MySQL.
Plus how much database-stored information are these hundreds of thousands of users going to generate? There are practical limits on how much data MySQL can store as well as issues with over about 700 concurrent users.
Flat Files with that many concurrent users will be anightmare - you'll get horrific performance (Thanks to all the read/writes to disk) and they'll probably end up going corrupt somewhere along the line as well.
I'd consider PostGreSQL - iut's open source and much more resiliant under high loads than MySQL. SyBase is meant to be good as well (there's a good post on here somehwere by MattR about SyBase).