Details
Details
- Other Assignee
- brouberol
| Status | Subtype | Assigned | Task | ||
|---|---|---|---|---|---|
| Resolved | brouberol | T362788 Migrate Airflow to the dse-k8s cluster | |||
| Resolved | brouberol | T380590 Migrate the airflow databases to Kubernetes | |||
| Declined | brouberol | T380614 Migrate the airflow-analytics database to Kubernetes |
Event Timeline
Comment Actions
Before migrating this DB to a cloudnative PG cluster, should we maybe clean it when, where appropriate?
The airflow_analytics DB is ~25GB large:
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size |
---------------------------+---------------------------+----------+-------------+-------------+-------------------------------------------+---------+
airflow-test-k8s | airflow-test-k8s | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/"airflow-test-k8s" +| 140 MB |
| | | | | "airflow-test-k8s"=CTc/"airflow-test-k8s" | |
airflow_analytics | airflow_analytics | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 24 GB |
airflow_analytics_product | airflow_analytics_product | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 604 MB |
airflow_analytics_test | airflow_analytics_test | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 1971 MB |
airflow_platform_eng | airflow_platform_eng | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 518 MB |
airflow_research | airflow_research | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 1668 MB |
airflow_search | airflow_search | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 5428 MB |
airflow_test_k8s | airflow_test_k8s | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7925 kB |
airflow_wmde | airflow_wmde | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 265 MB |
growthbook | growthbook | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 11 MB |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7925 kB |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7917 kB |
| | | | | postgres=CTc/postgres | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7925 kB |
| | | | | postgres=CTc/postgres | |The 5 biggest tables are log, job, task_instance, task_reschedule and xcom.
airflow_analytics=# select table_name, pg_size_pretty(pg_total_relation_size(quote_ident(table_name))), pg_total_relation_size(quote_ident(table_name)) from information_schema.tables where table_schema = 'public' order by 3 desc limit 5; table_name | pg_size_pretty | pg_total_relation_size -----------------+----------------+------------------------ log | 11 GB | 11533426688 job | 7089 MB | 7433486336 task_instance | 3893 MB | 4082376704 task_reschedule | 1500 MB | 1573126144 xcom | 581 MB | 609280000
We have an cleanup_airflow_db DAG that we can leverage to regularly cleanup certain tables by applying a retention policy, in days.
I feel that the log and job tables could be good candidates there.
Do we want to keep all data here, or maybe just the last 3 months? Something else?
Comment Actions
Maybe we should just use the tool provided by Airflow itself? db clean --clean-before-timestamp for example.
Here's a useful link that explains what's stored in these tables: https://www.astronomer.io/docs/learn/airflow-database/#dag-and-task-runs-browse
(3 months sounds like a reasonable limit to keep these records IMHO)
Comment Actions
We talked with the data-engineering team yesterday, and we agreed to keep a full year of data.
cc @xcollazo
Comment Actions
Taking a full database dump:
postgres@an-db1001:~$ pg_dump airflow_analytics > "airflow_analytics_$(date -I).sql"
Comment Actions
This is 23 GB in size.
postgres@an-db1001:~$ ls -lh airflow_analytics_2025-01-21.sql -rw-r--r-- 1 postgres postgres 23G Jan 21 11:12 airflow_analytics_2025-01-21.sql postgres@an-db1001:~$
Proceeding to trim it down to a year's retention.
Comment Actions
Dry run looks good, proceeding with:
analytics@an-launcher1002:/home/btullis$ airflow db clean --clean-before-timestamp "2024-01-21 00:00:00;"
Comment Actions
The actual run returned successfully. We didn't include the --skip-archive so they were moved instead of being deleted.
analytics@an-launcher1002:/home/btullis$ airflow db clean --clean-before-timestamp "2024-01-21 00:00:00;"
/usr/lib/airflow/lib/python3.10/site-packages/airflow/configuration.py:859 FutureWarning: section/key [core/sql_alchemy_conn] has been deprecated, you should use[database/sql_alchemy_conn] instead. Please update your `conf.get*` call to use the new name
[2025-01-21T11:27:54.589+0000] {statsd_logger.py:171} INFO - Successfully loaded custom StatsD client
/usr/lib/airflow/lib/python3.10/site-packages/airflow/metrics/statsd_logger.py:184 RemovedInAirflow3Warning: The basic metric validator will be deprecated in the future in favor of pattern-matching. You can try this now by setting config option metrics_use_pattern_match to True.
You have requested that we purge all data prior to 2024-01-21 00:00:00+00:00 for tables ['callback_request', 'celery_taskmeta', 'celery_tasksetmeta', 'dag', 'dag_run', 'dataset_event', 'import_error', 'job', 'log', 'session', 'sla_miss', 'task_fail', 'task_instance', 'task_instance_history', 'task_reschedule', 'trigger', 'xcom'].
This is irreversible. Consider backing up the tables first and / or doing a dry run with option --dry-run.
Enter 'delete rows' (without quotes) to proceed.
delete rows
/usr/lib/airflow/lib/python3.10/site-packages/airflow/utils/db.py:1090 SAWarning: Skipped unsupported reflection of expression-based index idx_ab_user_username
/usr/lib/airflow/lib/python3.10/site-packages/airflow/utils/db.py:1090 SAWarning: Skipped unsupported reflection of expression-based index idx_ab_register_user_username
Checking table callback_request
Found 0 rows meeting deletion criteria.
[2025-01-21T11:28:21.397+0000] {db_cleanup.py:448} WARNING - Table celery_taskmeta not found. Skipping.
[2025-01-21T11:28:21.398+0000] {db_cleanup.py:448} WARNING - Table celery_tasksetmeta not found. Skipping.
Checking table dag
Found 73 rows meeting deletion criteria.
Performing Delete...
Moving data to table _airflow_deleted__dag__20250121112821
Finished Performing Delete
Checking table dag_run
Found 213854 rows meeting deletion criteria.
Performing Delete...
Moving data to table _airflow_deleted__dag_run__20250121112821
Finished Performing Delete
Checking table dataset_event
Found 0 rows meeting deletion criteria.
Checking table import_error
Found 0 rows meeting deletion criteria.
Checking table job
Found 3433059 rows meeting deletion criteria.
Performing Delete...
Moving data to table _airflow_deleted__job__20250121113115
Finished Performing Delete
Checking table log
Found 12163771 rows meeting deletion criteria.
Performing Delete...
Moving data to table _airflow_deleted__log__20250121113217
Finished Performing Delete
Checking table session
Found 756 rows meeting deletion criteria.
Performing Delete...
Moving data to table _airflow_deleted__session__20250121114525
Finished Performing Delete
Checking table sla_miss
Found 18222 rows meeting deletion criteria.
Performing Delete...
Moving data to table _airflow_deleted__sla_miss__20250121114525
Finished Performing Delete
Checking table task_fail
Found 40 rows meeting deletion criteria.
Performing Delete...
Moving data to table _airflow_deleted__task_fail__20250121114526
Finished Performing Delete
Checking table task_instance
Found 143 rows meeting deletion criteria.
Performing Delete...
Moving data to table _airflow_deleted__task_instance__20250121114526
Finished Performing Delete
Checking table task_instance_history
Found 0 rows meeting deletion criteria.
Checking table task_reschedule
Found 1 rows meeting deletion criteria.
Performing Delete...
Moving data to table _airflow_deleted__task_reschedule__20250121114528
Finished Performing Delete
Checking table trigger
Found 0 rows meeting deletion criteria.
Checking table xcom
Found 0 rows meeting deletion criteria.We will now delete these.
Schema | Name | Type | Owner --------+---------------------------------------------------+-------+------------------- public | _airflow_deleted__dag__20250121112821 | table | airflow_analytics public | _airflow_deleted__dag_run__20250121112821 | table | airflow_analytics public | _airflow_deleted__job__20250121113115 | table | airflow_analytics public | _airflow_deleted__log__20250121113217 | table | airflow_analytics public | _airflow_deleted__session__20250121114525 | table | airflow_analytics public | _airflow_deleted__sla_miss__20250121114525 | table | airflow_analytics public | _airflow_deleted__task_fail__20250121114526 | table | airflow_analytics public | _airflow_deleted__task_instance__20250121114526 | table | airflow_analytics public | _airflow_deleted__task_reschedule__20250121114528 | table | airflow_analytics
Comment Actions
Dropped these tables.
airflow_analytics=# DROP TABLE IF EXISTS "_airflow_deleted__dag__20250121112821"; DROP TABLE airflow_analytics=# DROP TABLE IF EXISTS "_airflow_deleted__dag_run__20250121112821"; DROP TABLE airflow_analytics=# DROP TABLE IF EXISTS "_airflow_deleted__job__20250121113115"; DROP TABLE airflow_analytics=# DROP TABLE IF EXISTS "_airflow_deleted__log__20250121113217"; DROP TABLE airflow_analytics=# DROP TABLE IF EXISTS "_airflow_deleted__session__20250121114525"; DROP TABLE airflow_analytics=# DROP TABLE IF EXISTS "_airflow_deleted__sla_miss__20250121114525"; DROP TABLE airflow_analytics=# DROP TABLE IF EXISTS "_airflow_deleted__task_fail__20250121114526"; DROP TABLE airflow_analytics=# DROP TABLE IF EXISTS "_airflow_deleted__task_instance__20250121114526"; DROP TABLE airflow_analytics=# DROP TABLE IF EXISTS "_airflow_deleted__task_reschedule__20250121114528"; DROP TABLE airflow_analytics=#
Comment Actions
I'm removing myself as the assignee of this ticket, as I'll be out on leave for a couple of weeks. Someone else may claim the ticket in the meantime.
Comment Actions
As we've decided to following a different migration strategy than for other airflow instances (see T386282), we have created a new instance from scratch and have elected not to migrate the database. As a result, we can close this one.