How to manage large amount of data inside MYSQL table

Question

I have a table where there is geospatial data receaved from hundred of mobile device every 10 second. The table is now around 20mln records and is growing very fast. I would like to increase performance and decrease the possibility of table crash.

Which is the best solution to adopt without rewriting all the queries that read/write on this table?

Best is to keep all data accessible but it is not mandatory, in worst case can keep last 30 days.

I have read about table partitioning but i don't know if it is the best solution in my case.

Thanks

Answer 1

This is a big topic.

PARTITION

You mentioned 30 days, but you may find that you run out of disk space at some point, thereby necessitating some purging, whether at 30 or some other cutoff. So, plain on PARTITION BY RANGE(TO_DAYS(..)) now. I emphasize "now" because it will take downtime to add the partitioning, and you have "only" 20M rows now.

Such partitioning will allow rapid DROP PARTITION of old data. More details: http://mysql.rjweb.org/doc.php/partitionmaint

SELECT and INSERT work without change after the partitioning.

Do not expect any performance difference due to performance -- except when it comes to periodic purging.

With partitioning, you can decide later whether "30" is the right number.

With that partitioning, any query that includes WHERE datetime BETWEEN... will do 'partition pruning'.

Reports

Think about what the output needs to be.

Do you need to plot a vehicle's path for a date last year? If not, then you are saving too much info for too long.

Do you need the 10-sec report from a vehicle that is parked with the motor running (eg, a trucker keeping the AC running while napping)?

Is one of the bits of info "the engine is on/off". That's just one bit, but it rarely changes.

So think about how to shrink or jettison selected bits of info.

If you provide more details on how the data is used, I can provide more tips.

Answer 2

you can create one history table and store older data on to history table and in this case you just need to write trigger or cron job which run on every day and move older data in to history table. for example: your table name id tbl_1 then create one more table with name table_1_history and move all data into table_1_history table which is older then 30 days.