We receive real-time GPS data at a rate of around 5000 pr. minute (from 4 TCP servers). Each server uses a single connection to insert the data, and buffers data in between inserts. Every 15 minutes or so, a service fetches this data, and processes it into trips. Once the trips have been generated, the actual GPS data is usually not so important, only if the user wants to see the route on a map.
The problem is that it seems the database is struggling to keep up with the rate of data being inserted. Sometimes when the load increases, the insert time suddenly increases drastically (> 30 seconds), which in turn allows more data to be buffered, which in turn results in larger inserts and longer insert duration.
I hope to get some comments on the current design, and some of the ideas we have to improve performance, and answers to some of our questions - and any other tips people might have!
Current design
The data is currently separated into tables representing one week, and data older than a year is archived into a secondary database. The whole thing is joined together in an editable view, which is used for both inserts and reads.
Table design
- Id (PK, uniqueidentifier)
- DeviceId (FK, int)
- PersonId (FK, int)
- VehicleId(FK, int)
- TokenId (FK, int)
- UtcTime (PK, datetime2(3))
- Latitude (float)
- Longitude(float)
- Speed (smallint)
- Heading (smallint)
- Satellites (tinyint)
- IOData (varbinary(100))
- IgnitionState (tinyint)
- UserInput (tinyint)
- CreateTimeUtc (datetime2(3))
Indices
- DeviceId_CreateTimeUtc_Desc
- DeviceId_UtcTime_Desc (Clustered)
- PersonId_UtcTime_Desc
- TokenId_UtcTime_Desc
- VehicleId_UtcTime_Desc
Every week currently takes up around 10 GB including indices, and currently there is around 300 GB data in the main database.
The data tables in the main database have their own filegroup with 1 file, but it is on the same disk as all other tables in the main database. The secondary database is on a different disk, but on the same machine.
I think we are also running an index rebuild job weekly, when a new table partition (week) is taken into use. No shrink is performed.
The machine is an 8-core HP with 12 GB memory, and the disk holding the main database is running RAID 10.
Ideas
- Limit the amount of data stored in primary database to e.g. max 1 month. At the very least it would make the database more managable for backup/restoration, but could we expect to see a performance improvement by doing this?
- Create 2 files in filegroup for current data, and distribute them onto 2 different physical partitions
- Create master-slave databases holding current data, so inserts and reads are performed on different databases
- Put files for current data on SSD disks (would mirroring make any performance difference with SSD disks?)
Please let me know if more info is needed. There are horribly many factors influencing performance, and probably equally many ways to tweak it.