Results 1 to 5 of 5

Thread: DB architecture

  1. #1
    Join Date
    Feb 2011
    Posts
    2

    Unanswered: DB architecture

    Hi,
    I am making application for vehicle tracking system using GPS. There I am supposed to receive data every minute from a device.
    Technically - I will be getting 5 records every minute per device.
    i.e 5 * 60 * 24 records/day = 7200 records/day for single device.

    I am suppose to store that data for 2 months
    i.e 7200 * 30 * 2 = 4,32,000 records for a single device.

    If number of devices are 100 then storing all the records (43200000 records) in single table wont be a feasible option, as i need to fetch records from the table for report generation.

    Can any one help to come over the above mentioned problem?

    Thanks,
    Rahul

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    There is no problem storing 43200000 rows in a single table.

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    When designing the data model look closely at what you will be doing with the data rather than the number of records in the table. For instance, what types of reports will you be running? Will it really need access to all this information? On which keys will it be accessing the information? Can information be summarized and called upon at a later point? What about data management of data, will you need to delete a months worth of data at a time?

    On the reporting requirements you will need to get this information to determine the indexes and the types of indexes to be created on each of the tables. For managing large amounts of data within the table have a look at partitions.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Feb 2011
    Posts
    2

    DB Architecture

    Thanks Ronan for the inputs. Table majorly will have the column as deviceid (primary), latitude, longitude, time stamp, date. Report will be generated
    1) as where was the vehicle was in the past at a specific time (query as per device id, time stamp, date).
    2) What was the route followed by vehicle on a specific day etc (number of queries as per device id, time stamp, date).

    Indexing will surely be done (on device id). But when user demands for the report, still there will be ample data in the table when the query will be fired, which might result in performance issue.
    Our initial thought to this problem is to create table for every device. So when there are 100 device there will be 100 table to cater the need. Or we can make partition where 1 table will have 20-25 device info. In such a case our initial thought are that our problem can be solved. Need your inputs over the same Or any better solution for the same.

    Also for the data - it will be archived every 2 months. Anyways I will be storing 2 month data, which is also good in number.

    Awaiting reply...

    Thanks,
    Rahul

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    It sounds like partitioning your table will help you with performance. Have a read of the following link which highlights the performance improvements while using partitions for large volumes of data: MySQL :: Improving Database Performance with Partitioning
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •