Hi, I have a potential project where the DB will be expected to store 1,000,000 new records records a week. Feeding this DB will be a multithreaded application which will run overnight. I was wondering if anyone could suggest a DB setup/product for such traffic. For example I am pretty sure MSSQL Express will not be up to scratch. Any help/advice would be much appreciated. Thanks
What size of record is being stored in there? What sort of usage do you anticipate as far as reads / writes go?
MySQL will do this just fine - just keep your indexes correct and optimize all your SQL queries. Don't expect to do any fulltext searches on this though. I run a DB in which one table has 20M entries. The other tables are in the millions too, just not as high. I have heard of people having 1B records in a mysql DB. Again, just keep your indexes light (such as an ID number only), and adjust mysql memory settings as necessary.
MySQL scales pretty far. You only need to look at the big expensive guys (e.g., Oracle) if you need higher concurrency. 50 million rows in MySQL is OK - 50 million rows with 10,000 people accessing it all at once is more challenging You could also consider Percona, which is a version of MySQL that is performance-enhanced.
A million records per week. I assume it's a pretty important database. Can you afford to lose this much information that's accumulating at a rate of over 4 million new records per month? Could you replace it easily if you lost it? MS SQL Express is not suitable for such an important task. Not because of any inherent problems with it, but because it doesn't provide what you really need (and free versions of MySQL doesn't either). If I was working with such volumes of important data I wouldn't be leaving it to a single DB, I'd be looking for a clustering solution with robust real-time replication between database servers, automatic failover, and a very reliable backup-system for emergencies - easy peasy with other versions of MS SQL 2008. If the data isn't important, then most any RDBMS solution that scales will work but I wouldn't be looking at exceptional performance without some very decent hardware - particularly in the disk subsystems.
Quoted some from RonBrown which I do accept. What you need is a Enterprise database like Ms SQL or Oracle. If it is going to be million records per week you ned to go for Oracle or MS Sql!!
I disagree with the above. MySQL can easily scale to millions of records per week. Apart from that we don't know what a record consists of. If it's 3 columns in a table then just about anything could handle it. If it's hundreds of columns then, there's probably going to be some major hardware requirements no matter what DB is being used. At a fraction of the cost of MSSQL or Oracle, MySQL can do just about anything a business would need. It supports clustering and several distributed systems. Using Google or Percona versions drastically increases performance. In the end, if this is actually a very large database with many transactions, the hardware will be the most important part to plan for because any of these databases can handle it.
I think SQL is more efficient option & for multithreaded application and process 1000000 record in a week.we can use Mainframe Technology.
Yeah, I think you'd be fine with mySQL, or if you really wanted, go Oracle. The choice of db engine won't matter too much, but the db design is what counts.
You didn't mention what kind of hosting environment you are using. I agree that the DB design is one of the most important items to consider. Having said that the second is the type of hosting plan you are on. If I had the option I would go with MSSQL where I could login with Server Management Studio and setup maintenance plans, backups, replication, integrations etc. It all comes down to budget. mySQL will probably do what you need but if I had the choice I would go for MSSQL