0

We are logging data on hardware with only small memory (short on disk storage) only 4GB.

We only require the data to be stored for 1 month and then be over written in a way that it overrides older data first. The memory on hardware is very small so cannot continue to record indefinitely.

We are using a MySQL data base, the hardware it is running on is not always powered on as it is in a vehicle. The data will be viewed in a graph to show historical data over time.

A few options I have thought of but not sure how to execute it:

Let’s assume I will record 1 million rows of data in a month

When the table (table1) gets to 1 million rows, move this table to another and start new table (table2). When table2 reaches 1 million rows. Delete table1, move table2 to new table and create table3 etc...

This way there will be minimum 1 month of entries.

Second option (not sure if possible):

When the table gets to 1 million rows it starts to override from row 1 again.

  • you should look into events, where you can run every day a delete query which deletes everything older the today minus INTERVAL 1 Month, You can also delete every week depending your needs – nbk Jan 29 at 21:33
  • 1
    why do you think you need multiple tables? How is this stored data used? What exactly is "small memory". Are you short on disk storage? Edit the question to clarify. Welcome to SO. – danblack Jan 29 at 21:34
  • maybe consider sqlite based on footprint and rotate the database file. – danblack Jan 29 at 21:36
  • @nbk Thanks will try this – Phil Jan 30 at 3:38
  • @danblack as stated above - yes small disk storage and it will be viewed in a graph (data over time) I was reading the best way to do this was truncating tables? – Phil Jan 30 at 3:39
0

A short rotation script/event could look like:

drop table if exists table2;
rename table table1 to table2;
create table table1 like table2;
| improve this answer | |
0

Use PARTITION BY RANGE(TO_DAYS(..)). Have a cron job that does DROP PARTITION and REORGANIZE PARTITION nightly. This way you can automatically have one "table" that contains no more than one extra day.

Details: http://mysql.rjweb.org/doc.php/partitionmaint

| improve this answer | |
0

This Code Worked - CREATE EVENT cleaning ON SCHEDULE EVERY 2 HOUR ENABLE DO DELETE FROM 'table' WHERE Datetime < CURRENT_TIMESTAMP - INTERVAL 1 MONTH;

Im unsure if this will continue to fill up disk storage or just overwrite

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.