SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Mar 2010
    Location
    Cheshire, England
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Size and Optimisation Question

    Hi,

    Firstly, apologies for what's probably a newb question but I'm really unfamiliar with mysql DBA.

    I have two related questions and wondered if someone could help

    1) Is there any potential damage in running a daily php cron job to OPTIMIZE all the tables in a database? I'm trying to reduce the overhead to keep the DB size to a minimum

    2) I'm trying to get my head around storing a large amount of data - approximately 300,000 records that, from extrapolating the 1,000 records present, will take up about 750MB space for that table. Has anyone got first hand experience in doing something with this many records? Is the speed slow down likely to be highly noticable (i.e. greater than 3 second waits for queries)

    In relation to number 2), if anyone also has a suggestion (utilising another server / upgrading ram) that would help I would be grateful

    Thanks

    Justin
    Centurica
    Due diligence and purchase assistance for buyers of
    websites and internet business.
    Take a look at the 2014 Website Buyer's Report at Centurica.com

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,083
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by flipfilter View Post
    1) Is there any potential damage in running a daily php cron job to OPTIMIZE all the tables in a database? I'm trying to reduce the overhead to keep the DB size to a minimum
    Sounds a bit overkill to me, unless you have a lot INSERT and DELETE queries firing all the time. Once a week, or even once a month should suffice IMHO.

    Quote Originally Posted by flipfilter View Post
    2) I'm trying to get my head around storing a large amount of data - approximately 300,000 records that, from extrapolating the 1,000 records present, will take up about 750MB space for that table. Has anyone got first hand experience in doing something with this many records? Is the speed slow down likely to be highly noticable (i.e. greater than 3 second waits for queries)
    Nah, 300,000 is nothing, as long as you have you indexes in the right place and don't use queries that force a table scan (queries involving WHERE ... LIKE "%%" for example).
    I had a few tables once with several million rows and those ran just fine on a quite simple machine (dual core 2Ghz something with 2GB ram).
    Okay, they were a bit sluggish but didn't slow down to the point where it got annoying.

    Quote Originally Posted by flipfilter View Post
    In relation to number 2), if anyone also has a suggestion (utilising another server / upgrading ram) that would help I would be grateful
    What hardware do you currently have?
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    187
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree with ScallioXTX, and just another question - what storage engine are you planning to use?

  4. #4
    SitePoint Member
    Join Date
    Mar 2010
    Location
    Cheshire, England
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your reply

    Unfortunately I sometimes have to run LIKE %% queries as its for searching on domain names, unless you know of any better ways to do it. I'm not too sure what you mean by indices in the right place though?

    As for Hardware
    CPU
    1.13 GHZ
    RAM
    768 MB
    Disk Space
    30 GB
    Bandwidth
    500 GB

    Furicane - I think I'm running the default DB engine - MyISAM

    Thanks
    Centurica
    Due diligence and purchase assistance for buyers of
    websites and internet business.
    Take a look at the 2014 Website Buyer's Report at Centurica.com

  5. #5
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    187
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Running OPTIMIZE daily isn't effective, resource-wise. ScallioXTX explained it well, so you shouldn't worry about that.
    What's worrying is that you don't really have a lot of processing power, but the bottleneck here WILL be the hard disk.

    Sometimes, when working with tables that have several hundreds of thousands of rows and you need to perform expensive searches - it's better to fit the whole dataset in the RAM to avoid HDD as the bottleneck. That's why InnoDB is used, not only because it scales better with larger data size.

    However, without knowing how your table looks like and what indexes you're setting and why - it's hard to suggest anything that might help you out.
    I'd always suggest getting a dedicated server with 2 gigs of ram and sticking the table in question in memory if you're gonna work with it often (lots of users, lots of queries to retrieve the data).

  6. #6
    SitePoint Member
    Join Date
    Mar 2010
    Location
    Cheshire, England
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Furicane.

    In some ways I think I'm worrying about the wrong problem - it seems optimisation on the PHP side is probably more important.

    I think a server upgrade is certainly on the cards though.

    J
    Centurica
    Due diligence and purchase assistance for buyers of
    websites and internet business.
    Take a look at the 2014 Website Buyer's Report at Centurica.com

  7. #7
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    187
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you won't let PHP handle huge data sets or do expensive operations such as sorting those large datasets or similar, you shouldn't have a big problem with the optimization.
    You let the DB fetch / sort your data and PHP to display it. Anyway, I do suggest upgrading the server to a bit more processing power that is within your budget.


Tags for this Thread

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
  •