> I am writing an application that will poll up to 500 devices every 5
> minutes. For this application I need to keep at least 6 months
> of data. If
> I were to use a single record for every update, I would have ~25 million
> records. Obviously this is not an ideal solution.
Why not? Given the proper HW and a carefully designed table layout,
MySQL would have no trouble handling this amount of data. MySQL may
have trouble handling 100 inserts/minute if you need to extract a
lot of data at the same time, but that's a different beast entirely.
Remember that MySQL was built to effectively handle e.g. SELECTs on
huge amounts of data, while concurrency on e.g. INSERTS/UPDATE/SELECTs
has always had a lower priority.
> I am interested in getting ideas on how to tackle this. I was
> thinking that
> I would have a table that stored all the values for the current day and a
> table for historical data. After 24 hours the data would be
> averaged into a
> daily snapshot and stored in another table. The data from that day would
> then be cleared. This would only require ~90k recoo s.
>
> I would appreciate any ideas. I have written a ton of web applications
> using mySQL, but have never had to scale above 100k entries :)
I would consider writing the log data into a flat file, then import
it into MySQL, with reasonable intervals, say every half hour or so.
Once a day (or once a week?), I would execute a query to delete old
records.
- though I wonder: If you're only interested in the daily average, then
why do you need to poll every 5 minutes???
/ Carsten
--
Carsten H. Pedersen
keeper and maintainer of the bitbybit.dk MySQL FAQ
http://www.bitbybit.dk/mysqlfaq
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
