Partitioning of Database Tables helps improving performance of the dynaTrace Performance Warehouse for large installations. It also improves manageability since old data can be removed very quickly without the need for long-running delete statements. In the current implementation, the tables MEASUREMENT_HIGH and PERCENTILES_HIGH are subject for partitioning.
Using Partitioning will add additional complexity in Database Administration. The scripts shown here should act as an example on how to partition the MEASUREMENT_HIGH and PERCENTILES_HIGH tables. Other tables are not supported. Only use it for large installations, double-check with the database administrator (e.g. licensing in Oracle) before applying the script.
With the usage of the provided partitioning scripts, the storage duration for percentiles changes from the Low Resolution duration to the High Resolution duration.
In Oracle, Partitioning must be licensed separately as an Option on top of the Oracle Enterprise Edition.
Our partitioning implementation for Oracle executes a split partition operation on a so-called overflow partition. The partitioning script creates new empty daily partitions beforehand, where the data will automatically be stored by Oracle (depending on the timestamp range of the daily partition). Daily partitions, being older than the retention time will be dropped.
Check the temp space settings: for the initial run of the script, temp space must be at least the size of the biggest table to be partitioned (measurement_high or percentiles_high). After the initial run, the standard setting can be used again.
Search for a local variable called highRetentionTimeInDays in the SQL script and set the value (in days) according to the High Resolution Duration configuration for the Performance Warehouse in the dynaTrace client. They MUST match!
Run the script, together with your DBA.
Before executing: Adjust <owner> and optionally job_name in the WHERE clause properly!
Especially the ADDITIONAL_INFO field in DBA_SCHEDULER_JOB_RUN_DETAILS can return some useful information in case something went wrong during job execution.
Hint: If you have deleted a high amount of data out of the measurement_high table or have problems with the size of this table, you might have to shrink the table, using the following script. Please review the script with your DBAs and let them decide, if it is needed for your DB instance.
In Microsoft SQL Server, the Partitioning feature is included in the Enterprise/Developer Edition only. Compared to Oracle, it is NOT an additional feature/option on-top of the Enterprise/Developer Edition, which must be licensed separately. If the customer is running SQL Server Enterprise/Developer Edition, then Partitioning is available out-of-the box.
Our partitioning implementation for Microsoft SQL Server adds new empty daily partitions before-hand and purges data older than the configured retention time by switching partitioned data into an auxiliary table, which then gets truncated. The implementation is designed to minimize SQL Server lock contention and zero data movement while adding the new empty daily partitions.
Operates on the [PRIMARY] file group only!
Search for a local variable called @highRetentionTimeInDays and set the value (in days) according to the High Resolution Duration configuration for the Performance Warehouse in the dynaTrace client. They MUST match!
Set the retention time while calling the stored procedure. Default is 14 days. This MUST match with the configured high retention time as well!
Check, if the data type of the countvalue field in the additional/new table MEASUREMENT _HIGH _PARTITION_SWITCH_OUT is exactly the same as in MEASUREMENT _HIGH . If they are different, change the new MEASUREMENT _HIGH _PARTITION_SWITCH_OUT and leave MEASUREMENT _HIGH as it is.
It can be created with SQL Server Management Studio in a visual way. The job simply needs to include a single T-SQL step for executing the stored procedure. Make sure the stored procedure parameter matches the high retention time in days.
Search for a local variable called 'declare retentionDays' and set the value (in days) according to the High Resolution Duration configuration for the Performance Warehouse in the dynaTrace client. They MUST match!
The last step to finish up the partitioning setup is to enable the option 'Manually manage deletion of high resolution data (for partitioning)' in PWH storage management(Dynatrace Client).
Partitioning for postgreSQL works by creating inheriting tables from a base table. The base table contains rules to distribute the inserts into the correct child table. This enables us to DROP the respective child table during the nightly aging task, so that we don't need to execute many DELETE statements.
Please use the script PostgreSQL_partitioning.sql to set up partitioning. Note and review the instructions at the top of the script.
Search for the function called 'high_retention_time()' and set the values (in days) according to the desired retention time for normal measures (the measurement_high case) and percentiles (the percentiles_high case). The value for measuremnt_high MUST match the value that's configured in the AppMon client for high resolution retention! Default is 14 days for both values. Measurement records that are older than this value will be dropped during the daily partitioning job.
Run the script! It should be run with the customer DBA.
Setup a scheduler to run the daily partitioning job. PostgreSQL does not include one by default, but there are several options available, e.g. crontab, pgAgent, Windows Scheduler, dt scheduler, etc. This job MUST execute select dynatrace_partitioning_daily_job() . See the instructions at the top of the script for some example.
These two commands have been commented out, as the execution of them may take some while (depending on the amount of data several hours or even days). Therefore, we recommend to run this migration outside of the business hours or on the weekend. You may want to reduce the chunk size (currently it's set to one day, which can be a lot of data in some cases) by adapting the function 'migrate_data'.
sometime at night (preferably before the dynatrace aging task that start by default at 02:00 AM).
Any dates for the DB2 support for partitioning.
No date. Sorry. Do you have a concrete DB2 customer case where partitioning might be useful?
We have issues with DB2 and then after a support case and no resolution decided to rebuild. We do have heavy traffic both UEM and Java agents.
It would be helpful to have some guidance on when Partitioning is recommended. Number of agents? Volume of transactions/day? Total size of the database? Number of rows in Measurement_high table?
These recommendations are hard to give, because this strongly depends on the customer DB environment and it's capabilities. We have seen customers with ~ 70 million records in MEASUREMENT_HIGH per day. This is a volume where we have introduced partitioning in their environment, because the aging task can't keep up with removing old data in that sizing. Beside number of records, another indicator is a long running aging task (> 10hrs), also affecting daily business resulting in skipped PWH in the server log etc. We also don't want to (semi-)automatically recommend partitioning, because it adds additional complexity to the environment and shifting responsibility for parts of the aging task to the control of the DB and/or DBA, so the route through the support team is recommended.
And what we have seen a lot in the past is that customers, although high load, wouldn't need partitioning at all, if some sort of measure reduction in the customer system profiles would take affect in a first step, because the customer might not be interested in a lot of mesures/measurements at all. E.g. API measures, split by agents etc.
Keep us updated. Partitioning in DB2 is a bit "different" compared to Oracle and SQL Server, thus we appreciate all feedback we can get. Thanks!
Update to DB2 partitioning: With the great help from Jibi, partitioning for DB2 is also available now.
Thanks Alois for your help too. I was wondering how to find 'retentionDays' that is right now configured in the DB? Just in case there is a mismatch in DT client and DB.
Since PostgreSQL also supports partitioning, are there any plans to support this for DTAM in the future?
just talked to Alois and he says: Not immediately.
What are your experiences with PostgreSQL in general or partitioning in particular? Do you / Does someone know here how long this feature is around and how stable it is?
We've now implemented a partitioning script for PostgreSQL. Currently we use it on one of our internal PostgreSQL performance warehouses. Let me know if you want to try it at a customer's.
It's unclear when we say that we support partitioning with PostgreSQL, whether that applies to any version of AppMon, or does this only work with version X.Y and onwards. For example, does 6.1 work with PostgreSQL Partitioning? I thought this page might better belong in the regular versioned doc pages, but given partitioning is really a database issue, i'm not sure the version of AppMon matters. A statement in the doc pages would be great to clarify this issue.
you're right, that's not clearly stated. Currently, all the scripts support all the supported dynatrace versions (so yes, dt 6.1 will work with postgresql partitioning), because the two tables that are being partitioned haven't changed in structure for a long time. As soon as either measurement_high or percentiles_high changes, we would need to reconsider this page here.
The cleanest way would probably be to host the page once per version, I'll talk to the doc team about this.
If I don't set the 'Manually manage deletion...' everything should be fine, right ?
if everything runs fine, you should be ok without partitioning. It isn't enforced in the product, only highly recommended.
Even though the clean up task works fine, for larger installations it will run into the business hours where it could negatively affect pwh performance for charting / storing.
What we will lose in case of moving PWH from Large to xLarge?
I meant for the xLarge environment we are going to do a special partitioning.
Is there any best practices to not achieve without loosing any historic data?
you have to do nothing from the performance warehouse side.
move the appmon(dynatrace) server to xLarge and watch the performance warehouse for problems during measure writing (check selfmonitoring performance warehouse write dashboard).
much more as the server sizing, the number of measurements per minute is the key metric for partitioning. If there are to many measurements to delete during the cleanup task it produce to much load on the dbserver and decrease the insertion of measurements. in such case, the solution is partitioning.
In your case, if your performance warehouse run smotly with an large appmon (dt) server it will also work with an xlarge server( because only a move from large to xlarge does not produce more measurements).
What does real schema name mean?