2

I was importing some old data to the Timestream table successfully for a while but then it started to give the error:

Timestream error: com.amazonaws.services.timestreamwrite.model.ThrottlingException: Your magnetic store writes to Timestream are throttled for this database. Refer to Timestream documentation for 'ActiveMagneticStorePartitions' metric to prevent recurrence of this issue or contact AWS support.

The metrics it refers to raise to the limit of 250 but it drops to 0 after a while even after that when I start the import it immediately hits the limit and the error is raised again so nothing is imported at all.

I am not running import in parallel but only one at a time but nevertheless it still raises the error.

As a workaround, I've decided to increase the memory retention period for this table but still get the same error for some reason even when importing data within the new memory retention period.

ArchRanger
  • 69
  • 1
  • 8
mico
  • 709
  • 5
  • 18

2 Answers2

1

If you're ingesting old data, you should try to sort your data by timestamp. This will help to create fewer active partitions. Then, before inserting the old data into Timestream, you should check the active partitions.

I met with the AWS support team several times to understand the best way to ingest data into the magnetic store (the memory store doesn't have this constraint). They suggested ingesting data sorted by timestamp. So if you have multiple devices, you should ingest the data by timestamp instead of by device.

The criteria behind an active partition is not clear and they always talk about likelihood...

I've run load tests to ingest the same data into the magnetic store and ended up with different numbers of active partitions.

Here are the results of my load tests:

I ingest 2142288 records belonging to January 2022, which it will be written in the magnetic store with my current timestream configuration. Between each execution, I increased the record version to override the previous record.

January (total active partitions: 0)

  • Ingest 2142288 records -> new 16 active partitions (new: 16)
  • Ingest 2142288 records -> new 16 active partitions (new: 16, total: 32)
  • Ingest 2142288 records -> new 16 active partitions (new: 16, total: 48)
  • Ingest 2142288 records -> new 0 active partitions (new: 0, total: 48)
  • Ingest 2142288 records -> new 0 active partitions (new: 0, total: 48)

Without waiting for the active partitions to drop to zero, I ingested 1922784 records belonging to February 2022.

February(total active partitions: 48)

  • Ingest 1922784 records -> new 0 active partitions (new: 0, total:48 )

I waited until active partitions decreased to zero, increased the record version and ran the same tests

February(total active partitions: 0)

  • Ingest 1922784 records -> new 82 active partitions (new: 0, total:82)

As you can see, there is no clear pattern regarding the creation of active partitions but if you sorted your data by timestamp you'll get a better likelihood of success while ingesting data into the magnetic store.

Juanchi Rios
  • 119
  • 6
  • Thanks for your answer Juanchi! I will look further into details but just need to mention that my data is actually sorted by the timestamp (descending) and the error is raised right in the beginning of the ingestion (sometimes the very first message) – mico Sep 21 '22 at 18:03
  • @mico di you ever figure this out - having the same issue where timestamp ordered data is immediately jumping to 250 active magnetic partitions and fails. – spaceman Jan 10 '23 at 08:01
  • @spaceman, nope, I had a very long discussion with the AWS but no success. They were saying something about the structure of my data but that was unrelated and cannot be changed anyway. The only option I saw was creating a new DB and try to shard the data. – mico Jan 18 '23 at 08:47
  • @mico we have a table with millions of rows. its being live streamed to while we are ingesting historical records at the same time. it was working fine for ages but then stopped and after that any historical import would almost immediately hit the 250 partition limit. our solution which we came up with yesterday is to create a new empty table, import historical rows to that, then use a scheduled task to import them to the actual table. for some reason this works fine. and its what we're using. whenever the 250 limit is problematic, just another empty table and another scheduled task. – spaceman Jan 19 '23 at 09:25
  • @spaceman in your temp table and scheduled copy solution, does the historical data ingest to magnetic stores as well? Is the setting the same as your original table? – Liu Feb 23 '23 at 09:12
  • Yes. The settings were the same. – spaceman Feb 24 '23 at 10:17
1

have the exact same issue.

have a table that we were ingesting historical records to that was working fine until we got past some threshold. (not sure if its worth mentioning but this table is also being written to in realtime with current data as it arrives). we got ~500m rows into the table without ever hitting the 250 active partitions limit, the data is ordered, etc.

then a few weeks ago something changed and ever since then whenever writing historical rows to the table, it almost immediately jumps from 0 to 250 active magnetic partitions and historical ingestion is halted. we've been battling this for weeks.

our solution was create another empty table, import historical records to that, and then every 50m rows or so use a scheduled query to copy all the data from this "temp" table to the actual table we want to use. this temp tables settings are basically minimal memory store and maximal magnetic store since we're only writing historical data thats 6 months old at a minumum.

for some reason this works fine, all rows are accounted for and it never hits the 250 active partition limit. it costs a bit more, but not much more in our case and its the only thing we've found that works.

if we write the same data to our original table, it immediately hits 250 active mag partitions. pause the process, change the target table, run it again and the new target table barely gets beyond the 8-12 active magnetic partion range for the same data.

running the scheduled query to copy the data from the temp table to the target table seems to have zero impact on the target tables active partition counter that i can see, im assuming its just happening behind the scenes somewhere.

at present this seems to be the only path to finishing our historical data import.

streaming realtime or "present day" data to the mem-store always works fine. this is specifically only happening when writing historical data to the magnetic storage.

spaceman
  • 1,628
  • 1
  • 16
  • 19