########################################################################################## VEH-56: Split JourneyTrackPoints Table ########################################################################################## Issue Type: Task ----------------------------------------------------------------------------------------- Issue Information ==================== Priority: Major Status: Closed Resolution: Won't Fix (2019-09-09 16:22:05) Project: VehMan (VEH) Reported By: btasker Assigned To: btasker Targeted for fix in version: - 1.0.2 Time Estimate: 0 minutes Time Logged: 0 minutes ----------------------------------------------------------------------------------------- Issue 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 -- BEGIN QUOTE -- (ceil($trackpointID/50))*50; -- END QUOTE -- 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 ----------------------------------------------------------------------------------------- Activity ========== ----------------------------------------------------------------------------------------- 2013-12-01 22:11:32 btasker ----------------------------------------------------------------------------------------- Following the change, the following files will need to be amended -- BEGIN QUOTE -- 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 -- END QUOTE -- ----------------------------------------------------------------------------------------- 2013-12-01 22:30:18 btasker ----------------------------------------------------------------------------------------- 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 -- BEGIN QUOTE -- 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) -- END QUOTE -- *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 mail handler ----------------------------------------------------------------------------------------- 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 btasker ----------------------------------------------------------------------------------------- VEH-58 is complete and the current maximum TrackID is 116. ----------------------------------------------------------------------------------------- 2019-09-09 16:22:05 btasker ----------------------------------------------------------------------------------------- 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