SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Nov 2008
    Location
    Pune, India.
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Large (10 million+ rows) Databases and MySQL

    Can anyone comment on MySQL performance on large tables with 10 million+ rows, from one's own-experience? How about 50 mn or 1 billion rows?

    I have a phpBB forum with MySQL. Is anyone aware of large forums (with multi-million posts) running on MySQL? Or, will I have to switch to Oracle (later on)?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by amu1983 View Post
    Is anyone aware of large forums (with multi-million posts) running on MySQL?
    check this list -- http://rankings.big-boards.com/?p=all

    it doesn't say which databases are used, but i think it's a safe bet that many phpbb and vbulletin forums run on mysql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    We have databases with 25mill records to 1.2bill records, and they work pretty well.

    A few things to remember:
    - always select on keys
    - keep the keys small
    - try not to update the keys...
    - never delete records...
    - don't select limit x,z...
    - write to a master db without indexes, replicate the data to slaves, and read from there.
    - do an "explain" on every query you add to your script to make sure it's using the correct keys / doesn't go through the entire db.
    - get a server with allot of ram (ram is usually the choke point, not the cpu, if you did your queries well)

  4. #4
    SitePoint Member
    Join Date
    Nov 2008
    Location
    Pune, India.
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    We have databases with 25mill records to 1.2bill records, and they work pretty well.
    MySQL databases?

  5. #5
    SitePoint Addict
    Join Date
    Oct 2006
    Posts
    210
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The product my company provides will add about 1.5 million records per day to its main table. Today, the table contains about 1 billion records. A long transaction for us is 0.5 sec. We have a dedicated database server running MySQL with 4GB of RAM. We use the MyISAM engine.

    Vali gave very good advice. Use Explain to see how efficient your database requests will be and always select records based upon an appropriate index.

    mikem


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •