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.
2
Answers
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)
Without waiting for the active partitions to drop to zero, I ingested 1922784 records belonging to February 2022.
February(total active partitions: 48)
I waited until active partitions decreased to zero, increased the record version and ran the same tests
February(total active partitions: 0)
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.
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.