10

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.

1
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White
    Aug 29, 2017 at 10:17

1 Answer 1

8

5000 inserts per minute are about 83 inserts per second. With 5 indexes that's 400 physical rows inserted per second. If the workload was in-memory this would not pose a problem even to the smallest of servers. Even if this was a row-by-row insert using the most inefficient way I can think of. 83 trivial queries per second are just not interesting from a CPU standpoint.

Probably, you are disk-bound. You can verify this by looking at wait stats or STATISTICS IO.

Your queries probably touch a lot of different pages so that the buffer pool does not have space for all of them. This causes frequent page reads and probably random disk writes as well.

Imagine a table where you only physically insert at the end because of an ever-increasing key. The working set would be one page: the last one. This would generate sequential IO as well wen the lazy writer or checkpoint process writes the "end" of the table to disk.

Imagine a table with randomly-placed inserts (classic example: a guid key). Here, all pages are the working set because a random page will be touched for each insert. IOs are random. This is the worst case when it comes to working set.

You're in the middle. Your indexes are of the structure (SomeValue, SequentialDateTime). The first component partially randomizes the sequentiality provided by the second. I guess there are quite a few possible values for "SomeValue" so that you have many randomly-placed insert-points in your indexes.

You say that data is split into 10GB tables per week. That's a good starting point because the working set is now bounded by 10GB (disregarding any reads you might do). With 12GB of server memory it is unlikely, though, that all relevant pages can stay in memory.

If you could reduce the size of the weekly "partitions" or increase server memory by a bit you are probably fine.

I'd expect that inserts at the beginning of the week are faster then at the end. You can test this theory on a dev server by running a benchmark with a certain data size and gradually reducing server memory until you see performance tank.

Now even if all reads and writes fit into memory you might still have random dirty page flushing IO. The only way to get rid of that is to write into co-located positions in your indexes. If you can at all convert your indexes to use (more) sequential keys that would help a lot.

As a quick solution I'd add a buffering layer between the clients and the main table. Maybe accumulate 15min of writes into a staging table and periodically flush it. That takes away the load spikes and uses a more efficient plan to write to the big table.

8
  • 1
    @usr Thanks for the very comprehensive and well-explained answer! We have actually discussed increasing server memory, without knowing how much of a effect it would have - but now we really have a very compelling reason to do so :) You are right that the "SomeValue" partially randomizes the insert points - there are probably around 10000 device ids. Regarding the staging table, is your suggestion a table without any indices, and then a job to insert into the main table every X minutes?
    – sondergard
    May 7, 2014 at 11:14
  • @usr Reg. your suggestion for converting the clustered index to be sequential, we could add an auto-inc. identity column (integer), and change the clustered index to this column for the sole purpose of keeping it sequential? It would not be unique across tables, but as long as the primary key is, we should be fine.
    – sondergard
    May 7, 2014 at 11:25
  • 1
    If the staging table is small and your queries can live with it then you don't need to index at all. But you could.; One strategy would be to make the CI on an identity column (as you say). This can work wonders if the CI is big and the other indexes are small. Because the CI are writes are now sequential they contribute much less to your problem. This strategy is most successful if there is a meaningful size difference.; Another idea would be to have one table per day. Maybe merge monthly.
    – usr
    May 7, 2014 at 11:50
  • Ok so we looked into creating identity column for CI, but unfortunately it's not possible on a partioned view (no identity column allowed, no default values and all columns must be included in insert). Maybe the partioned view was a poorly chosen design, although it was recommended by a consultant
    – sondergard
    May 8, 2014 at 19:31
  • 2
    Seriously though, for anyone facing the same problem, if you've got lots of writes and only a few reads, you really want to append at the end and delay any indexing. On the other hand, if you want fast reads and don't care how long it takes to insert you need a clustered index.
    – tiktak
    Oct 13, 2015 at 17:00

Your Answer

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

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