Comment résoudre les problèmes de performances avec VACUUM dans Amazon Redshift ?

Brève description

VACUUM est une opération intense en ressources, qui peut être ralentie pour les raisons suivantes :

• Pourcentage élevé de données non triées
• Grand table avec trop de colonnes
• Utilisation de clés de tri entrelacées
• Utilisation irrégulière ou peu fréquente de VACUUM
• Tables simultanées, requêtes de cluster, instructions DDL, ou tâches ETL

Utilisez la requête svv_vacuum_progress pour vérifier l'état et les détails de votre opération VACUUM. Suivez ensuite les bonnes pratiques VACUUM pour résoudre et éviter tout problème futur.

Solution

Remarque : Ce qui suit s'applique aux clusters Amazon Redshift provisionnés. Les tables système et requêtes suivantes ne fonctionneront pas sur Amazon Redshift sans serveur.

Pour vérifier si l'opération VACUUM est en cours, exécutez la requête svv_vacuum_progress :

dev=# SELECT * FROM svv_vacuum_progress;
table_name |          status                 | time_remaining_estimate
-----------+---------------------------------+-------------------------
 data8     |  Vacuum: initialize merge data8 | 4m 55s
(1 row)

La requête svv_vacuum_progress vérifie également le nom de la table sujette à une opération VACUUM, l'état de cette opération ainsi que le temps restant estimé avant son achèvement. Si aucune opération VACUUM n’est en cours d'exécution, la requête svv_vacuum_progress affiche l'état de la dernière exécutée.

Remarque : la requête svv_vacuum_progress ne renvoie qu'une seule ligne de résultats.

Vérifiez les détails de la table vidée. Spécifiez les noms de table et de schéma dans la clause WHERE :

SELECT schema, table_id, "table", diststyle, sortkey1, sortkey_num, unsorted, tbl_rows, estimated_visible_rows, stats_off 
FROM svv_table_info 
WHERE "table" IN ('data8');

Voici un exemple de sortie :

Schema     | table_id | table | diststyle | sortkey1 | sortkey_num | unsorted | tbl_rows  | est_visible_rows | stats_off 
------------+----------+-------+-----------+----------+-------------+----------+-----------+------------------+-----------
 testschema | 977719   | data8 | EVEN      | order_id |  2          |    25.00 | 755171520 | 566378624        | 100.00

À partir de cette sortie, la colonne sortkey1 affiche la clé de tri principale.

Si la table a une clé de tri entrelacée, cette colonne affiche l'état INTERLEAVED.

• La colonne sortkey_num indique le nombre de colonnes de la clé de tri.
• La colonne unsorted affiche le pourcentage de lignes non triées à trier.
• La colonne tbl_rows affiche le nombre total de lignes, y compris celles supprimées et mises à jour.
• La valeur estimated_visible_rows est le nombre de lignes sans les lignes supprimées.
• Après un vidage complet (supprimer et trier), les valeurs des colonnes tbl_rows et estimated_visible_rows doivent être proches, et la colonne unsorted doit indiquer 0. Remarque : Les données de la table sont mises à jour en temps réel. Pour vérifier la progression de VACUUM, continuez à exécuter la requête. Notez que les lignes non triées diminuent progressivement à mesure que VACUUM avance. Pour vérifier si le pourcentage de vos données non triées est élevé, consultez les informations VACUUM d’une table spécifique.

Exécutez la requête suivante pour vérifier les informations VACUUM d'une table, en spécifiant son ID dans la requête précédente :

SELECT table_id, status, rows, sortedrows, blocks, eventtime
FROM stl_vacuum
WHERE table_id=977719
ORDER BY eventtime DESC LIMIT 20;

Voici un exemple de sortie :

table_id |             status             |    rows    | sortedrows | blocks |         eventtime         
----------+--------------------------------+------------+------------+--------+----------------------------
   977719 | [VacuumBG] Finished            |  566378640 |          0 |  23618 | 2020-05-27 06:55:33.232536
   977719 | [VacuumBG] Started Delete Only | 1132757280 |  566378640 |  47164 | 2020-05-27 06:55:18.906008
   977719 | Finished                       |  566378640 |  566378640 |  23654 | 2020-05-27 06:46:04.086842
   977719 | Started                        | 1132757280 |  566378640 |  45642 | 2020-05-27 06:28:17.128345
(4 rows)

La sortie répertorie en premier les événements les plus récents, puis les événements plus anciens, dans l'ordre selon lequel ils ont été triés.

• Le dernier vidage effectué était un VACUUM DELETE automatique, qui a commencé le 27/05/2020 à 06:55:18.906008 UTC et s'est terminé après quelques secondes.
• Ce vidage a libéré l'espace occupé par les lignes supprimées, ce qui est confirmé par le nombre de lignes et de blocs affichés au démarrage et à la fin de l'opération.

Notez les changements qui se produisent dans le nombre de blocs occupés par la table entre le début et la fin de l'opération VACUUM.

Remarque : Amazon Redshift effectue automatiquement des opérations VACUUM SORT et VACUUM DELETE sur les tables en arrière-plan. Ces opérations VACUUM en arrière-plan s'exécutent pendant les périodes de charge réduite et sont interrompus pendant les périodes de charge élevée. Ces opérations VACUUM automatiques évitent d'avoir à exécuter la commande VACUUM.

La colonne sortedrows indique le nombre de lignes triées dans la table. Lors de la dernière opération de vidage, aucun tri n'a été effectué, car il s'agissait d'une opération automatique de VACUUM DELETE. La ligne marquée pour suppression affiche le même nombre de lignes triées qu'au démarrage de VACUUM, car les lignes actives n'ont pas été triées. Une fois l'opération VACUUM DELETE terminée, elle indique 0 lignes triées.

L'opération de vidage initiale, qui a commencé le 27/05/2020 à 06:28:17.128345 UTC, indique un vidage complet. Elle a libéré l'espace des lignes supprimées et des lignes triées en environ 18 minutes. Une fois l'opération de vidage terminée, la sortie affiche les mêmes valeurs pour les lignes et les lignes triées, car l'opération a réussi à trier les lignes.

Dans le cas d'un vidage déjà en cours, continuez à surveiller ses performances et intégrez les bonnes pratiques VACUUM.

Bonnes pratiques VACUUM

Il est possible d’améliorer les performances de VACUUM en appliquant les bonnes pratiques suivantes :

• VACUUM étant une opération intensive en ressources, exécutez-la pendant les heures creuses.
• Pendant les heures creuses, utilisez wlm_query_slot_count pour remplacer temporairement le niveau de simultanéité dans une file d'attente pour une opération VACUUM.
• Exécutez l'opération VACUUM avec un paramètre de seuil allant jusqu'à 99 % pour les grandes tables.
• Déterminez le seuil approprié et la fréquence d'exécution de VACUUM. Par exemple, vous pouvez exécuter VACUUM à un seuil de 100 % ou toujours faire trier vos données. Utilisez l'approche optimisant les performances des requêtes de votre cluster Amazon Redshift.
• Exécutez un VACUUM FULL ou un VACUUM SORT ONLY assez régulièrement afin d'éviter l'accumulation de régions non triées dans les tables de grande taille.
• S'il existe une grande quantité de données non triées sur une grande table, effectuez une copie approfondie. Une copie profonde peut vous aider à charger les données dans une nouvelle table plutôt que d'exécuter VACUUM SORT sur la table existante.
• Exécutez la commande VACUUM avec l'option BOOST. L'option BOOST alloue des ressources supplémentaires (telles que mémoire et espace disque disponibles) à VACUUM. Avec l'option BOOST, VACUUM fonctionne dans une seule fenêtre et bloque les suppressions et mises à jour simultanées pendant toute la durée de l'opération VACUUM.
  Remarque : exécuter VACUUM avec l'option BOOST peut affecter les performances des requêtes. Il est recommandé d'exécuter l'opération VACUUM BOOST uniquement pendant les opérations de maintenance ou en heures creuses.
• Divisez les tables de grande taille en tables de séries chronologiques afin d'améliorer les performances de VACUUM. Dans certains cas, l'utilisation d'une table de séries chronologiques permet d'éviter l'exécution de VACUUM.
• Choisissez un type de compression de colonne pour les grandes tables. Les lignes compressées consomment moins d'espace disque lors du tri de données.
• Utilisez la commande ANALYZE après l'opération VACUUM pour mettre à jour les statistiques, qui sont utilisées par le planificateur de requêtes pour choisir les plans optimaux.

---