search

tableau / TabMon

A Tableau Server performance monitoring service
https://tableau.github.io/TabMon/
MIT License
108 stars 50 forks source link

Dropping rows from countersamples failing #252

Closed Batwoman007 closed 4 years ago

Batwoman007 commented 4 years ago

Hi Is anyone able to assist with this please? We are trying to get rows over 90 days removed automatically, but its getting timed out.

We are running an incremental refresh on Tabmon and want to have a rolling 90 days. We have changed the config file to reflect however it doesnt seem to be able to do this. Below is the message we are getting in the logs. Can anyone help with this please?

2020-08-13 10:50:19,631 [10] DEBUG DataTableWriter.Adapters.DbAdapter - Dropping rows from table 'countersamples' older than 90 days.. 2020-08-13 10:50:39,634 [10] ERROR DataTableWriter.Adapters.DbAdapter - Unable to drop rows from table 'countersamples': ERROR: 57014: canceling statement due to statement timeout 2020-08-13 10:50:39,636 [10] ERROR DataTableWriter.Writers.DataTableDbWriter - Unable to delete rows: ERROR: 57014: canceling statement due to statement timeout

danjrahm commented 4 years ago

Hello,

The issue here is that we are hitting a query timeout due to the amount of data that we're attempting to purge on the first attempt. To resolve this, let's try manually running the delete query in postgres. Here is the query we use to delete the data:

DELETE FROM {0} WHERE timestamp < NOW() - INTERVAL '90 days'; Please replace {0} with the table name when running the query.

Once this is done, please let me know the results.

Thanks, Dan

Batwoman007 commented 4 years ago

Thanks Dan, will try that now.

Batwoman007 commented 4 years ago

Quick question, can you please confirm for me where i run this from? Sorry am a newbie admin with Tableau and Tabmon

Batwoman007 commented 4 years ago

Query returned successfully: 77556912 rows affected, 890477 ms execution time. is the response when running. Will check the dates

Batwoman007 commented 4 years ago

The extract was being created but at 19 million rows i had to cancel it. I think I will rerun your script to 30 days, we can always build it up again.

danjrahm commented 4 years ago

Hello,

If the extract is too large, you may want to look at stepping the poll interval down. That will decrease the amount of data being collected.

-Dan

Batwoman007 commented 4 years ago

Thanks, that has worked. Much appreciated your help.

Batwoman007 commented 4 years ago

Great support, thank you