Hi guys, just want to make sure MySQL ability to handle millions or billions of records. I realize that there must be a special treatment for this case in MySQL. Any tips or tricks regarding this issue?
If you have so much content i would suggest you to use mssql or something simillar to it..because even if you index your tables very good if you got billions of records only something like mssql could handle it compared to the others in speed and performance.
MySQL can scale to just about anything that MSSQL can. A lot depends on the hardware the database is on, the available resources, how MySQL is configured, how the database is designed, how the application uses the database, and how much usage the database is seeing.
Compare that...and there only mssql 2000 was tested...get details about mssql server 2005 and you will seee that mysql has no chance compared to mssql... http://swik.net/MySQL/MySQL+vs+MS+SQL+Server
What do you guys think about what database system that Google, FaceBook and Yahoo use? Is it MSSQL, MySQL, Postgre, or Oracle?
I ever heard the issue that Facebook use MySQL. If I am not mistaken, 1800+ MySQL servers are used to handle all the data. For Google and Yahoo, I have no information about it. Those large web applications can deals and handle with extremely large amount of data without problem. If the issue is true about Facebook uses MySQL, then is has proven the strength and capability of MySQL to handle this problem. Can anyone prove this issue?
To xhanch : The servers are for the query load due to lots of users. The architecture if the network + servers + software defines its ability to scale. These elements are defined by the data and load the facebook needs to handle, Since your data and usage are notfacebook you can not compare. To compare image you want the fastest car, so we tell you get a ferrari. But your usage will be to haul 10.000KG then the Ferrari will not be an option. P.S. Listen to Jestep and I will add it depends on your architecture as well. To Natashalein: If his usage case fits the limits of both MySQL or MS-SQL he is free to choose. His data and usage imposes the upper limits. Since his data and usage are not google or facebook you can not compare.
Here's a good answer to that question. - http://stackoverflow.com/questions/362956/what-database-does-google-use Google is so large and their usage so varied, that I don't think they are a good candidate to compare to. As that other article suggests, it's very difficult to compare MSSQL to MySQL on performance because so much of it will vary based on which MSSQL version is being used. The Enterprise version costs about $20,000 per physical processor for a web server or similar license. $20,000 can buy some pretty insane hardware, and you would have a fairly robust MySQL database on that. Also, if you're looking for better off the shelf versions of MySQL, Percona and Google's custom builds are significantly better than the standard MySQL community edition. Percona - http://www.percona.com/docs/wiki/release:start Google MySQL Tools - http://code.google.com/p/google-mysql-tools/
To clarify, I do not intend to compare. I just curious about this issue and I am looking for a good reference about good database system to support my project. One of my websites has been dealing with millions of records (using MySQL till now). So far the performance is still OK and satisfying. Luckily I have implemented well managed Indexing and good database structure. So, my next goal is to overwhelm tens of millions to billions records. You have great info there jestep. Thanx for sharing.
I have used website with over 4 million records in 1 table and still results come in few milliseconds. It all depends on how you structure the db, tables, indexes and data. More over do not forget hardware + os + networking setup to account for. It's all in brain otherwise all database engines are more over same.
To answer your question simply... yes. MySQL can easily handle millions or billions of records as long as the table is designed properly (applies to any RDBMS though). This forum has close to 13M large records in it's post table. Our keyword tracker has 250M+ records in a single table. No issues at all.
since you two shared your experience, I am so relieved now. I think I just need to focus/work with database structure and query optimization. When an application grow bigger, I have no worries at all with the application layer since we can simply have multiple application server. The only matter will be only the database layer since this layer will keep growing and getting larger.
Well, if you are taking about billion records, the best way is to go for Oracle. Although Mysql may handle such larger volumes, by using Oracle you would see a significant difference in performing queries, backup and usage of server resources. However it comes at its own costs and requires the technical expertise in maintaining a Oracle db.
krishmk, I strongly disagree with your point that "if one has to manage billion records Oracle is good". MySQL is equally capable of doing what Oracle can.
I don't think so... MySQL can't touch Oracle for large databases. MySQL just added partitioning in 5.1 and replication and clustering are still so far off from Oracle or MSSQL, it's not even comparable. Just a quick example, for MySQL to cluster, you have to fit the entire database in RAM, and then have enough RAM for normal operation. It doesn't support foreign keys for clustered setups. MYSQL's HA and enterprise functions are little more than hacks. Oracle has had these features and more at a production level for 10+ years. That doesn't mean MySQL is a poor choice, or is bad, it's more than enough for most of us, but it's not even close to being as capable as Oracle. The only exception to this is price and ease of administration.
Actually, databases used within MySQL Cluster environment don't need to be memory resident any longer... just the indexes can be stored in memory with the data on disk (if you want). Oracle has better fail safe measures than MySQL does for sure, but it comes at a cost. If you need something mission critical, Oracle is currently a better choice. If it's not mission critical and you just want fast, MySQL is a better choice (there is a lot of overhead Oracle needs to do for things like bidirectional replication, ACID compliance, etc.) I wish MySQL would get bidirectional replication going as an option at least, but it's not there yet, sadly.
We have several mysql databases with many big tables (~50M records in single table). All is fine, just be careful with database indexes.
I have 2 millions records in one table, and its work fine if you select something by id, but text is not good option.