Re: Millions storage...
Hi James,
Thanks a bunch for your helpful comments. I have some questions based on tidbits from your post:
> Don't create one table per day. Unlikely to be
> efficient or necessary.
I have a similar scenario. A table is truncated and updated with fresh rows every 10 minutes. It works fine and saves a whole lot of logic in the app if done this way, so we finally settled for it. The number of rows added and deleted thus is no more than 30 to 40, basically some code IDs.
SO, QUESTION 1:
Does frequent recreation of a table have any drawbacks in terms of performance or anything else like storage fragmentation, etc? Would it be better if I moved this to a separate database? Or, I could use a "temporary table" but I am not sure I can control it from within my application -- I mean how long it lasts, how to get its data across sessions, etc.
> But I'd still try InnoDB first, because it's more
> automatic and less likely to break if customers
> don't follow procedures properly. It would get
> slower over time but nothing would break. I think
> that with the primary key approach I described, it
> would be faster than MyISAM.
You've convinced me to try out InnoDB. Seems it is generally considered better for large tables, especially tables that have very frequent INSERTs (about a thousand a day) and even more SELECTs (about hundred thousand a day).
SO, QUESTION 2:
What're the caveats of InnoDB table types? Data space is not an issue with me, and I don't really need transactions (so I can set innodb_flush_log_at_trx_commit to 0 if needed), but other than that would it be worthwhile to try for my scenario?
> Many disks are probably most important for the
> hardware needs. Maybe four in RAID 10?
> CPU is less important. You're probably disk seek
> limited. More drives, good schema and covering
> indexes are the solutions for that.
I have an 80 GB ATA hard disk with 1 GB of RAM. My database has about a million records now, but is growing by a thousand every day. I would say that my application is very disk-seek intensive, as you mentioned, basically a lot of SELECTs from a table and 1% INSERTs.
QUESTION 3:
After a covering index, and innoDB type table, do you think I should still consider having multiple disk drives instead of one big one? (I have backup so redundancy is not an issue). What's the advantage of that scenario? Is SCSI dramatically better than ATA for MySQL type stuff?
Thanks for any thoughts!