MySQL Forums
Forum List  »  Performance

Re: Millions storage...
Posted by: Craig Matthews
Date: March 30, 2005 01:16AM

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!

Options: ReplyQuote


Subject
Views
Written By
Posted
1761
March 28, 2005 07:53PM
1751
March 30, 2005 12:50AM
Re: Millions storage...
1790
March 30, 2005 01:16AM
1979
April 01, 2005 12:51AM
1755
March 31, 2005 11:34PM
1812
April 01, 2005 09:07AM
1856
April 01, 2005 09:09PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.