Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,535
    Thanks
    45
    Thanked 259 Times in 256 Posts

    When to watch table sizes

    So while backing up some databases today, I noticed that two of my tables had reached fairly substantial sizes: half a million records. Fortunately, I also know only about 50k of those records are in use at any given time, ie, the rest are basically archived. I guess, never having had a very large DB, is this problematic? When should I start considering a DB large, or too large? I know indexing can be an issue with large DBs, but if file space is not a concern, are there other major issues I should be aware of?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,155
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Largest tables in MYSQL that I have *personally* dealt with were 3GB each. The records were quite small, and even with indexes each only ate up about 150 bytes. So 3GB divided by 150 is 20,000,000 records. And it performed just fine. (It was used in *REAL TIME* for both storage and retrieval.)

    SO... I don't think MySQL is going to choke on half a million records. But the size of the records *does* matter some. Could you consider splitting that table into active and archival records? (Notice I didn't ask "should you".) If it's a lot of trouble, don't bother. If it isn't, you could try it to see if it helped performance. But don't be too surprised if the performance boost is only nominal.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • The Following 2 Users Say Thank You to Old Pedant For This Useful Post:

    bazz (04-12-2011), resellcode (04-12-2011)

  • #3
    New Coder
    Join Date
    Jan 2011
    Location
    Reston, VA
    Posts
    37
    Thanks
    0
    Thanked 3 Times in 3 Posts
    The only issue i've had with large tables was when doing a query that didn't properly use an index. In such as case your query has to check each row. The performance will suffer with each new row added.

    If your indices work as expected you won't have this issue.
    Sr. Software Engineer | Paisley Systems Inc. | www.paisleysystems.com/php

  • #4
    Regular Coder
    Join Date
    Apr 2011
    Posts
    286
    Thanks
    2
    Thanked 39 Times in 39 Posts
    The other issue is if the index is complicated or too big to fit in memory, then you might have some performance gains from tweaking your database settings, redefining your indexes, or reducing the size of your table.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,155
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    MySQL doesn't do *too* badly when an index doesn't fit in memory, *IF* you are requesting records that are relatively close together by index order. But, yes, you get much better performance if the index fits in memory *AND* if the machine's memory is not in use by many other processes. It doesn't do much good to fit an index in memory only to have that memory swapped out because of pressure from other processes. Having a dedicated (or near-dedicated) server for the DB may be the best solution is you have really large indexes.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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