Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2004

    Best way to manage LARGE MySQL databases

    I am about to put a new site online that will contain over 9 billion MySQL records however, I do not know the best way to manage a database of this size. As an example lets use phone numbers. When someone searches for a phone number, it will search the database and pull the information out of it. If it is not there, the user can have the option of entering info for the phone number and it will be saved.

    The site will use Php (possibly Ajax/jQuery) to pull the data.

    Some ideas I have had to handle the database:
    1. Create a separate table for each starting letter, number, character (A-Z,0-9,~!@#$$....) and have each record that starts with the particular number or character in that table.

    2. One giant table.

    3. Compress the individual tables somehow and retrieve the data in those tables.

    Does anyone have experience with managing databases of this size and what the best option is? Should I just do a straight mysql database or what? I have no problem paying for a consult from an experienced programmer/database manager.

  2. #2
    Join Date
    Apr 2003
    Los Angeles, CA
    One thing you don't mention is the size of the database (data + index). With 9 billion rows, the index will be large (50+ GB, at least).

    I can't comment on 9 billion rows, but we have one database with 2 TB of data in a single table with ~500 million rows. We used table partitioning (see manual) into 128 partitions by hash (1 partition = 1 data + 1 index file). Then you end up working with ~16 GB files rather than one huge file. The setup has been pretty stable for several years.
    Pings <1 ms, Unlimited Transfer, Lowest Price: http://localhost/

  3. #3
    Join Date
    Jul 2008
    Minneapolis, MN
    For something that big, you might also want to look at a more scalable database solution like HyperTable or Hbase (part of Hadoop). It sounds like your project is a custom written one, so you should easily be able to adapt to those new technologies.

    Keep in mind future growth, also. That would allow you to scale to billions and trillions of rows easily and quickly, and horizontally.
    01 Networks / Hosting and Consulting Services
    Pay as you Go hosting -- the cheapest prices in town.
    Zimbra (Network Edition and Open Source) Hosting
    100% full uptime guarantee / 24x7x365 support

  4. #4
    Join Date
    Jul 2004
    It sounds to me like I am going to have to research this a little further and perhaps hire someone for a consultation and assist getting things set up. I was planning on running this off of a cloud or VPS, but I am not sure if that is going to be good enough.

    Right now I have 1.1 million entries in my database on the live site and it is taking up 100mb of space. So 1 billion entries will give me roughly 1GB database size, so I am estimating for 5 billing entries the space will be 10-20GB of space.

    Could it be possible to run this off of compressed flat files instead of a database or is a MySQL database the better way to go?
    Last edited by jdk; 05-22-2011 at 05:15 PM.

  5. #5
    Join Date
    May 2007
    I think your math is off a bit - if roughly 1 million entries takes up 100 MB of space, 1 billion entries will take up 100 GB of space, and 5 billion will take up 500 GB or half a TB.

    As for flat files vs. MySQL, it really depends on what you'll be doing with the data. If you're going to be making lots of random queries into the dataset and need quick turnaround time, something like MySQL with indexes on key fields will be much faster. If you're doing more batch operations where you need to scan through the full dataset to perform computations, flat files could be faster, although something like Hadoop and HDFS will be much faster still.

  6. #6
    Join Date
    Jul 2004
    Thanks my math was a bit off there. I may have to trim down my initial upgrade to the site as I want to keep things under 50GB initially.

    There are no computations. The site simply queries the database for a unique key and if founds will display the database. If not it will allow the user to add details for the searched string into the database. So it sounds like mysql is the way to keep it. Would having different tables slow the searching down any or would it be a good idea vs one large table with the unique keys.

  7. #7
    Join Date
    Mar 2011
    If the database is really phone numbers, you could partition by area code or something like that.

    Another option would be to use MySQL Cluster for all the writes and replicate the cluster out to multiple slaves and load balance against them for the reads. This would give you very fast scalable writes and then the benefits of fast myisam reads with the load spread out.

    Cluster is mostly used as in memory data, so you would need to figure out the size of your data and indexes and get as many data nodes as you would need to hold the data and grow. When you need more space, you would add more data nodes. The value of NoOfReplicas is also something to keep in mind when doing this calculation.

  8. #8
    Join Date
    Jan 2006
    Athens, Greece
    I can see only 2 options with this.
    1. If there will be no fancy operation on the data and no further features upgrade to the
    application, I would build a custom system.
    2. Use table partitioning with MySQL plus custom caching and "data search" techniques.

Similar Threads

  1. How to view large mysql databases
    By Relo74 in forum Programming Discussion
    Replies: 6
    Last Post: 10-25-2005, 06:31 AM
  2. Restoring large mysql databases
    By Aughavey in forum Hosting Security and Technology
    Replies: 3
    Last Post: 10-04-2005, 12:29 PM
  3. [MYSQL] Handling Large Databases
    By Barti1987 in forum Programming Discussion
    Replies: 16
    Last Post: 05-02-2005, 11:47 PM
  4. Backing up large MySQL databases
    By Sizzly in forum Programming Discussion
    Replies: 2
    Last Post: 01-21-2005, 10:58 AM
  5. PowWeb and large mysql databases?
    By adambrooks in forum Web Hosting
    Replies: 12
    Last Post: 05-23-2004, 05:11 PM

Posting Permissions

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