Airflow MySQL Meta store performance tuning

airflow-mysql.md

Login MySQL with this shell script

 #!/bin/bash
SECRET_VALUE=$(tink-kubectl -n airflow get secrets airflow-credentials -o json)
PASSWORD=$(echo $SECRET_VALUE | jq ."data" | jq ."rootPassword")
CORRECT_PASSWORD=$(echo "$PASSWORD" | sed -e 's/^"//' -e 's/"$//' | base64 -d)
tink-kubectl -n airflow exec -it airflow-mysql-0 bash
mysql -h 127.0.0.1 -P 3306 -u root -p $CORRECT_PASSWORD
use airflow;

Enable slow query logging

Turn on slow query log

SET GLOBAL slow_query_log = 'ON';

Set log location to /tmp since that's a writtable volume

SET GLOBAL slow_query_log_file = '/tmp/slow_query.log';

Set a threshold value for slow query, in seconds

SET GLOBAL long_query_time = 2;

Now any query takes more than 2 seconds will be logged in the file /tmp/slow_query.log

Check and create table indice

To check existing indice for a table

show index from task_instance;

To create a index for a table

create index <index name> on dag_run(col1, col2, col3 ...);

Example:

create index dag_run_dag_id_execution on dag_run(dag_id, execution_date);