1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Working with millions or billions of records

Discussion in 'MySQL' started by xhanch, Oct 17, 2009.

  1. #1
    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?
     
    xhanch, Oct 17, 2009 IP
  2. Natashalein

    Natashalein Peon

    Messages:
    83
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    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.
     
    Natashalein, Oct 17, 2009 IP
  3. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #3
    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.
     
    jestep, Oct 17, 2009 IP
  4. Natashalein

    Natashalein Peon

    Messages:
    83
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Natashalein, Oct 18, 2009 IP
  5. xhanch

    xhanch Member

    Messages:
    683
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    35
    #5
    What do you guys think about what database system that Google, FaceBook and Yahoo use? Is it MSSQL, MySQL, Postgre, or Oracle?
     
    Last edited: Oct 18, 2009
    xhanch, Oct 18, 2009 IP
  6. Natashalein

    Natashalein Peon

    Messages:
    83
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Don't know but i think google has its own^^.
     
    Natashalein, Oct 18, 2009 IP
  7. xhanch

    xhanch Member

    Messages:
    683
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    35
    #7
    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. :D

    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? :)
     
    Last edited: Oct 18, 2009
    xhanch, Oct 18, 2009 IP
  8. chisara

    chisara Peon

    Messages:
    141
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #8
    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.
     
    chisara, Oct 19, 2009 IP
  9. chandan123

    chandan123 Prominent Member

    Messages:
    11,586
    Likes Received:
    578
    Best Answers:
    0
    Trophy Points:
    360
    #9
    all those companies probably use their own version of modifies db servers
     
    chandan123, Oct 19, 2009 IP
  10. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #10
    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/
     
    jestep, Oct 19, 2009 IP
  11. xhanch

    xhanch Member

    Messages:
    683
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    35
    #11
    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.
     
    xhanch, Oct 19, 2009 IP
  12. mastermunj

    mastermunj Well-Known Member

    Messages:
    687
    Likes Received:
    13
    Best Answers:
    0
    Trophy Points:
    110
    #12
    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.
     
    mastermunj, Oct 20, 2009 IP
  13. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,333
    Likes Received:
    2,613
    Best Answers:
    462
    Trophy Points:
    710
    Digital Goods:
    29
    #13
    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.
     
    digitalpoint, Oct 20, 2009 IP
  14. xhanch

    xhanch Member

    Messages:
    683
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    35
    #14
    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.
     
    xhanch, Oct 21, 2009 IP
  15. krishmk

    krishmk Well-Known Member

    Messages:
    1,376
    Likes Received:
    40
    Best Answers:
    0
    Trophy Points:
    185
    #15
    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, Oct 30, 2009 IP
  16. mastermunj

    mastermunj Well-Known Member

    Messages:
    687
    Likes Received:
    13
    Best Answers:
    0
    Trophy Points:
    110
    #16
    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.
     
    mastermunj, Oct 30, 2009 IP
  17. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #17
    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.
     
    jestep, Oct 30, 2009 IP
  18. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,333
    Likes Received:
    2,613
    Best Answers:
    462
    Trophy Points:
    710
    Digital Goods:
    29
    #18
    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.
     
    digitalpoint, Oct 30, 2009 IP
  19. koba

    koba Peon

    Messages:
    88
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #19
    We have several mysql databases with many big tables (~50M records in single table). All is fine, just be careful with database indexes.
     
    koba, Oct 31, 2009 IP
  20. triper

    triper Member

    Messages:
    66
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    41
    #20
    I have 2 millions records in one table, and its work fine if you select something by id, but text is not good option.
     
    triper, Oct 31, 2009 IP