1

I have a Table that stored GPS data from vehicle trackers.

There are 8 vehicles tracked via an API. Each time the information is returned from the web server each of the vehicles has its information stored in the table. The table is pinged every 10 seconds. So 8 new rows are added every 10 seconds.

I was doing a backup and noticed that the DB had grown from 100mb to approx ~3Gb.

I deleted all rows from the GPS table where the TimeStamp was older than 30 days which took the row count from 4 million down to 2.5 million.

The query to pull historical vehicle data was typically taking about 5 seconds before the purge; after it, it times out.

This is the query that displays dates for which vehicle data is available:

SELECT CAST(TIMESTAMP AS DATE) AS `Vehicle_Date` 
FROM `GPSLog` 
GROUP BY `Vehicle_Date` 
ORDER BY TIMESTAMP DESC

This is the query that is now timing out:

SELECT JSONString, TimeStamp, DATE(TimeStamp) as JDate, ID
FROM BusinessManager.GPSLog
Where IMEI = @ID
  AND DATE(TimeStamp) = @D1

I have tried OPTIMIZE TABLE GPSLog and increasing the Buffer_Pool_Size but neither of these things have resolved the issue.

Any anybody suggests any alternative actions I can take?

The table structure is:

ID (INT 10) PK/AI 
IMEI (Big INT 20) 0
JSONString (Json) NULL
TImeStamp (TimeStamp) CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Indexes:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
GPSLog  0   PRIMARY 1   ID  A   2055001             BTREE       
GPSLog  1   Vehicle 1   TimeStamp   A   367903          YES BTREE       
GPSLog  1   Vehicle 2   IMEI    A   2055252         YES BTREE   

Table, Create Table

GPSLog 

CREATE TABLE `GPSLog` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `IMEI` bigint(20) DEFAULT '0',
  `JSONString` json DEFAULT NULL,
  `TimeStamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `Vehicle` (`TimeStamp`,`IMEI`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5533794 DEFAULT CHARSET=utf8

EXPLAIN

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'GPSLog', NULL, 'ALL', NULL, NULL, NULL, NULL, '2055356', '100.00', NULL
5
  • Please post text RESULTS of SHOW INDEX FROM GPSLog; SHOW CREATE TABLE GPSLog; and EXPLAIN SELECT SQL_NO_CACHE JSONString, .....; for analysis. And the last 400 lines of your Slow Query Log, please. Apr 26, 2019 at 13:03
  • Have you defragged and updated the statistics on the table after the delete? Defrag will update the statistics on those indexes that it defrags, so do that first. Apr 26, 2019 at 14:40
  • WHERE DATE(TimeStamp) =@D1 is (one of) the problems. Possibly lack of appropriate indexes the other. Apr 26, 2019 at 18:55
  • Is there anything I can do to rectify that?
    – Dave B
    Apr 28, 2019 at 22:35
  • Please show us the code used to prep your@ID and your@D1 to verify DataTypes of the selectors are matched to the TABLE CREATE datatypes prior to running the SELECT query, each time requested. Better yet, a GENERAL LOG for appropriate time period would have the whole process documented, if you could post it to pastebin.com. May 4, 2019 at 16:43

1 Answer 1

1
  • ANALYZE TABLE .. may refresh the statistics such that queries run faster. (Defragging (OPTIMIZE TABLE) takes a long time and is probably overkill.)
  • For future purging, implement PARTITION BY RANGE; see this for discussion.
  • The first query would be faster if the ORDER BY said the same thing as the GROUP BY.
  • Instead of AND DATE(TimeStamp) = @D1, say AND Timestamp >= @D1 AND Timestamp < @D1 + INTERVAL 1 DAY, plus have composite INDEX(IMEI, Timestamp). (The index you have is not as efficient for this query.)
  • innodb_buffer_pool_size should be somewhere around 70% of RAM. Increasing it too much will lead to swapping, which is terrible for performance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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