flipfilter — 2011-01-15T06:09:21-05:00 — #1
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
scallioxtx — 2011-01-15T07:15:01-05:00 — #2
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.
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.
What hardware do you currently have?
furicane — 2011-01-15T07:36:01-05:00 — #3
I agree with ScallioXTX, and just another question - what storage engine are you planning to use?
flipfilter — 2011-01-15T08:17:39-05:00 — #4
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
Furicane - I think I'm running the default DB engine - MyISAM
furicane — 2011-01-15T09:13:17-05:00 — #5
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).
flipfilter — 2011-01-16T14:48:23-05:00 — #6
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.
furicane — 2011-01-16T15:57:04-05:00 — #7
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.