Performances des requêtes - séries temporelles

Après avoir optimisé le stockage, l’étape suivante consiste à améliorer les performances des requêtes. Cette section présente deux techniques clés : l’optimisation des clés ORDER BY et l’utilisation de vues matérialisées. Nous verrons comment ces approches peuvent ramener le temps d’exécution des requêtes de quelques secondes à quelques millisecondes.

Optimiser les clés `ORDER BY`

Avant de tenter d’autres optimisations, vous devriez optimiser les clés de tri afin que ClickHouse produise les résultats les plus rapides possible. Le choix de la bonne clé dépend en grande partie des requêtes que vous allez exécuter. Supposons que la plupart de nos requêtes filtrent sur les colonnes project et subproject. Dans ce cas, il est judicieux de les ajouter à la clé de tri, ainsi que la colonne time, puisque nos requêtes filtrent aussi sur le temps. Créons une autre version de la table avec les mêmes types de colonnes que wikistat, mais triée selon (project, subproject, time).

CREATE TABLE wikistat_project_subproject
(
    `time` DateTime,
    `project` String,
    `subproject` String,
    `path` String,
    `hits` UInt64
)
ENGINE = MergeTree
ORDER BY (project, subproject, time);

Comparons maintenant plusieurs requêtes pour voir à quel point notre expression de clé de tri est essentielle aux performances. Notez que nous n’avons pas appliqué les optimisations précédentes sur les types de données et les codecs ; les écarts de performances entre les requêtes tiennent donc uniquement à l’ordre de tri.

Requête	`(time)`	`(project, subproject, time)`
`SELECT project, sum(hits) AS h FROM wikistat GROUP BY project ORDER BY h DESC LIMIT 10;`	2.381 sec	1.660 sec
`SELECT subproject, sum(hits) AS h FROM wikistat WHERE project = 'it' GROUP BY subproject ORDER BY h DESC LIMIT 10;`	2.148 sec	0.058 sec
`SELECT toStartOfMonth(time) AS m, sum(hits) AS h FROM wikistat WHERE (project = 'it') AND (subproject = 'zero') GROUP BY m ORDER BY m DESC LIMIT 10;`	2.192 sec	0.012 sec
`SELECT path, sum(hits) AS h FROM wikistat WHERE (project = 'it') AND (subproject = 'zero') GROUP BY path ORDER BY h DESC LIMIT 10;`	2.968 sec	0.010 sec

Vues matérialisées

Une autre possibilité consiste à utiliser des vues matérialisées pour agréger et stocker les résultats des requêtes les plus fréquentes. On peut interroger ces résultats plutôt que la table d’origine. Supposons que la requête suivante soit exécutée assez souvent dans notre cas :

SELECT path, SUM(hits) AS v
FROM wikistat
WHERE toStartOfMonth(time) = '2015-05-01'
GROUP BY path
ORDER BY v DESC
LIMIT 10

┌─path──────────────────┬────────v─┐
│ -                     │ 89650862 │
│ Angelsberg            │ 19165753 │
│ Ana_Sayfa             │  6368793 │
│ Academy_Awards        │  4901276 │
│ Accueil_(homonymie)   │  3805097 │
│ Adolf_Hitler          │  2549835 │
│ 2015_in_spaceflight   │  2077164 │
│ Albert_Einstein       │  1619320 │
│ 19_Kids_and_Counting  │  1430968 │
│ 2015_Nepal_earthquake │  1406422 │
└───────────────────────┴──────────┘

10 rows in set. Elapsed: 2.285 sec. Processed 231.41 million rows, 9.22 GB (101.26 million rows/s., 4.03 GB/s.)
Peak memory usage: 1.50 GiB.

Créer une vue matérialisée

Vous pouvez créer la vue matérialisée suivante :

CREATE TABLE wikistat_top
(
    `path` String,
    `month` Date,
    hits UInt64
)
ENGINE = SummingMergeTree
ORDER BY (month, hits);

CREATE MATERIALIZED VIEW wikistat_top_mv 
TO wikistat_top
AS
SELECT
    path,
    toStartOfMonth(time) AS month,
    sum(hits) AS hits
FROM wikistat
GROUP BY path, month;

Alimentation rétroactive de la table de destination

Cette table de destination ne sera alimentée que lorsque de nouveaux enregistrements seront insérés dans la table wikistat ; nous devons donc effectuer une alimentation rétroactive. Le moyen le plus simple de procéder consiste à utiliser une instruction INSERT INTO SELECT pour insérer directement dans la table cible de la vue matérialisée en utilisant la requête SELECT de la vue (transformation) :

INSERT INTO wikistat_top
SELECT
    path,
    toStartOfMonth(time) AS month,
    sum(hits) AS hits
FROM wikistat
GROUP BY path, month;

Selon la cardinalité du jeu de données brut (nous avons 1 milliard de lignes !), cette approche peut être gourmande en mémoire. Vous pouvez aussi utiliser une variante qui consomme très peu de mémoire :

Créer une table temporaire avec un Null table engine
Connecter une copie de la vue matérialisée habituellement utilisée à cette table temporaire
Utiliser une requête INSERT INTO SELECT pour copier toutes les données du jeu de données brut dans cette table temporaire
Supprimer la table temporaire et la vue matérialisée temporaire.

Avec cette approche, les lignes du jeu de données brut sont copiées bloc par bloc dans la table temporaire (qui ne stocke aucune de ces lignes) et, pour chaque bloc de lignes, un état partiel est calculé puis écrit dans la table cible, où ces états sont fusionnés de manière incrémentielle en arrière-plan.

CREATE TABLE wikistat_backfill
(
    `time` DateTime,
    `project` String,
    `subproject` String,
    `path` String,
    `hits` UInt64
)
ENGINE = Null;

Ensuite, nous allons créer une vue matérialisée pour lire à partir de wikistat_backfill et écrire dans wikistat_top

CREATE MATERIALIZED VIEW wikistat_backfill_top_mv 
TO wikistat_top
AS
SELECT
    path,
    toStartOfMonth(time) AS month,
    sum(hits) AS hits
FROM wikistat_backfill
GROUP BY path, month;

Et enfin, nous allons alimenter wikistat_backfill à partir de la table initiale wikistat :

INSERT INTO wikistat_backfill
SELECT * 
FROM wikistat;

Une fois la requête terminée, nous pouvons supprimer la table de chargement rétroactif et la vue matérialisée :

DROP VIEW wikistat_backfill_top_mv;
DROP TABLE wikistat_backfill;

Nous pouvons maintenant interroger la vue matérialisée plutôt que la table d’origine :

SELECT path, sum(hits) AS hits
FROM wikistat_top
WHERE month = '2015-05-01'
GROUP BY ALL
ORDER BY hits DESC
LIMIT 10;

┌─path──────────────────┬─────hits─┐
│ -                     │ 89543168 │
│ Angelsberg            │  7047863 │
│ Ana_Sayfa             │  5923985 │
│ Academy_Awards        │  4497264 │
│ Accueil_(homonymie)   │  2522074 │
│ 2015_in_spaceflight   │  2050098 │
│ Adolf_Hitler          │  1559520 │
│ 19_Kids_and_Counting  │   813275 │
│ Andrzej_Duda          │   796156 │
│ 2015_Nepal_earthquake │   726327 │
└───────────────────────┴──────────┘

10 rows in set. Elapsed: 0.004 sec.

Le gain de performances est spectaculaire. Auparavant, il fallait un peu plus de 2 secondes pour calculer la réponse à cette requête, et maintenant cela ne prend plus que 4 millisecondes.

​Optimiser les clés ORDER BY

​Vues matérialisées

​Créer une vue matérialisée

​Alimentation rétroactive de la table de destination

Optimiser les clés `ORDER BY`

Vues matérialisées

Créer une vue matérialisée

Alimentation rétroactive de la table de destination