Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 5 of 5
  1. #1
    Senior Member Steax's Avatar
    Join Date
    Dec 2006
    Location
    Bandung, Indonesia
    Posts
    1,207
    Member #
    14572
    Guys, as I'm relying more and more on MySQL (and PHP to fetch it), I'm wondering about how my systems should scale. For example, I've seen forums with hundreds of thousands of members (although many are largely inactive), and even a good site in beta can quickly gather a few hundred.

    Now, I've got this habit in MySQL - I often make things too expandible. Like, if my application says "delete data", it really only hides it in its display but the data is still there. I find these features really helpful because they cut down on "undo" stuff.

    I'm worried now, since nearly my entire website is based on MySQL for data, that my databases will overflow. I'm worried if they get to the scale of being gigabytes large. At that point, what if my server crashes? What if I need to move hosting locations? It would be a nightmare...

    Any opinions on this are very appreciated.
    Note on code: If I give code, please note that it is simply sample code to demonstrate an effect. It is not meant to be used as-is; that is the programmer's job. I am not responsible to give you support or be held liable for anything that happens when using my code.

  2.  

  3. #2
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    I'm building a web application right now that has about 60 million total records in the 3 primary tables, probably dozens of gigs so far. Upload, download, and backup is the lease of your concerns when you get "real" amounts of data

    PS - you shouldn't be concerned with your database at all if it's less than 100,000 total records of data... that much is easily managed in a text file, i.e. you can back up the whole thing in a couple minutes and save it as a SQL script.

  4. #3
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    PS - you're doing the right thing by not deleting data. It's a good habit to get in to for enterprise development.

  5. #4
    Senior Member Steax's Avatar
    Join Date
    Dec 2006
    Location
    Bandung, Indonesia
    Posts
    1,207
    Member #
    14572
    Upload, download, and backup is the lease of your concerns when you get "real" amounts of data
    So what are my main concerns? And how do you back it up? Kinda worried here, because I'm unsure about how large my DB can be... And on the not-delete thing, would it be better to just flag the rows (which means they're still scanned on each query) or should they be moved to another table?

    Thanks, transio!
    Note on code: If I give code, please note that it is simply sample code to demonstrate an effect. It is not meant to be used as-is; that is the programmer's job. I am not responsible to give you support or be held liable for anything that happens when using my code.

  6. #5
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Always flag for status. If a simple boolean isn't enough, create a relational table containing several stati. When you get into serious enterprise dev, you may choose instead to store state in a separate table in which you only do inserts. insert a new record reflecting the updated state and the date/time of the update. insert insert insert. The reason for this is that by never executing an update, you never have row locks at any time, failed updates, or data loss, and thus have complete enterprise stability. Also, you never have to worry about update triggers that way. That type of table is called a status pipeline, but there are very few occassions in which you should use one. A good example is financial transactions. You never see your bank, for example, make modifications to a past transaction. Any changes are always reflected in your account as a new transaction referencing the old one. That's a pipeline system.


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 11:53 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com