VEH-56: Split JourneyTrackPoints Table

Issue Information

Issue Type: Task
 
Priority: Major
Status: Closed

Reported By:
Ben Tasker
Assigned To:
Ben Tasker
Project: VehMan (VEH)
Resolution: Won't Fix (2019-09-09 16:22:05)
Target version: 1.0.2,

Created: 2013-12-01 22:05:37
Time Spent Working


Description
At the current rate, with 1 live user, the JourneyTrackPoints table will contain 50,000 records by (very) early 2014.

With multiple users live, the table is likely to grow in size quite substantially (a six hour drive is likely to create ~4320 records)

Would be wise to split the table before proceeding with any further modifications to the Journeys submodule.

Current thinking is to base the split on the trackID;

|trackid|trackpoints table|
|1|#__JourneyTrackPoints_Tracks_50|
|40|#__JourneyTrackPoints_Tracks_50|
|50|#__JourneyTrackPoints_Tracks_50|
|70|#__JourneyTrackPoints_Tracks_100|
|71|#__JourneyTrackPoints_Tracks_100|

The method of calculation could be as simple as


(ceil($trackpointID/50))*50;


50 is an arbitrary number picked as an example, but a set figure should be chosen as it won't be possible to change it at a later date.

Would be wise to add the calculation to the helper.

As the current Track ID has reached 2704 (due to a resolved bug discovered in VEH-46) will need to perform a manual split of the existing data in the live database.

The cron run should check whether the limit is being approached and create a new table if it's near.

Would also be wise to store the current table number somewhere so that various routines (especially cron) can quickly look it up


Subtasks

VEH-57: Create States table
VEH-58: Perform Manual Split of JourneyTrackPoints table on live API
VEH-59: Create TrackPoints Table creation routine
VEH-61: Old tables should be optimized
Toggle State Changes

Activity


2013-12-01 22:11:32
Following the change, the following files will need to be amended


ben@milleniumfalcon:~/Documents/src.old/Vehicle_Man/VehMan$ grep -R "JourneyTrackPoints" ./* -l | grep -v "\~"
./API/lib/Stats/module_scripts/journeystats.php
./API/lib/Journey/helper.php
./API/lib/Journey/api_init.php
./API/lib/cron.php
./db/VehMan.sql
./live-api_update_scripts/V1.0.15_db_update.sql

2013-12-01 22:30:18
There are currently 67 tracks in the database, with 212163 trackpoints.

Obviously the number of trackpoints per journey will vary, but also don't want to create more tables than is necessary, but based on the current size


mysql> SHOW TABLE STATUS FROM vfuet_VehMan LIKE 'JourneyTrackPoints';

| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
| JourneyTrackPoints | InnoDB | 10 | Compact | 215389 | 95 | 20496384 | 0 | 14204928 | 33554432 | 381001 | 2013-12-01 17:46:34 | NULL | NULL | latin1_swedish_ci | NULL | | |

1 row in set (0.01 sec)


Table size on disk
20496384/1024^2 = 19.54MB

Index size on disk
14204928 / 1024^2 = 13.546875MB

Might also be worth looking at whether it's possible to reduce the size of the index somewhat

2013-12-01 22:31:50
btasker changed status from 'Open' to 'In Progress'

2013-12-01 22:46:41
Thinking about creating an intermediate release for this, as the longer
it's left the harder the manual split will be.

To try and reduce the index size, it's worth taking a dump of the live
table and adjusting the indexes offline. This needs to happen before the
split can be implemented.

2013-12-27 00:04:25
VEH-58 is complete and the current maximum TrackID is 116.

2019-09-09 16:22:05
Bulk Close.

Realistically, this project's not going to see any further development (other than possibly some bug fixes for anything new that's found).

Closing any outstanding issues as Won't Fix

2019-09-09 16:22:05
btasker changed status from 'In Progress' to 'Closed'

2019-09-09 16:22:05
btasker added 'Won't Fix' to resolution