search

bailoo / itrack

Location Tracking for Everyone
3 stars 2 forks source link

Move lastlog to MySQL #45

Closed bailoo closed 9 years ago

bailoo commented 9 years ago

Ashish, RIzwan: please comment if you have any concerns about this.

After providing api for removing given imei from lastog and truncating lastlog, I relized that lastlog is an anti-pattern for cassandra.

  1. lastlog is read and written at the arrival of every new data point. Although this is not really a performance issue, it creates tombstones in Cassandra which are cleared after a fixed given time period. This causes unnecessary compaction activity for a multi-node cluster and is not recommended.
  2. lastlog often ends up with junk data which required the need to truncate the whole table or delete rows manually. Not pretty.

lastlog is a small table, there is just one row per imei. It belongs in MySQL. There will be no performance issue.

Adhering to the philosophy of hiding data storage details from the developer, I will implement lastlog in MySQL and provide compatibility with the existing APIs in both Java and PHP.

bailoo commented 9 years ago

lastlog in MySQL will have a new schema. It will store the last seen data for every day so that when querying for last seen data from history we need not query the main log in Cassandra. This will make all last log related queries in MySQL alone.

bailoo commented 9 years ago

@ashish-iembsys says that listener is no longer reading from lastlog so this issue is closed and the idea to move lastlog to MySQL is dropped for the time being.