Clickhouse : une performance étonnante

Clickhouse : une performance étonnante jpp

J'ai découvert, un peu par hasard, l'existence de cette base de données, c'est dans l'interface de Ntopng que j'ai vu que l'enregistrement des données dans Mysql était "deprecated" et ne serait pas poursuivi dans la prochaine version. 
C'est un peu dommage car MariaDB/Mysql est un standard incontournable, mais Clickhouse semble néanmoins présenter quelques avantages, notamment au niveau de la performance.

Logo clickhouse

J'ai donc décidé de voir un peu les caractéristiques de cette base Clickhouse. 
Ce groupe d'article présente, un peu, les caractéristiques de cette nouvelle base au moins au niveau des performances. 
Clickhouse propose deux "consoles" sur deux ports différents :

  1. Une à la syntaxe Clickhouse parfois un peu particulière.
  2. Une à la syntaxe MariaDB/Mysql qui permet un abord plus immédiat.

La plupart des tests réalisés dans ce groupe d'articles ont été réalisés avec la console "Mysql".

Clickhouse : installation

Clickhouse : installation drupadmin

Il existe un dépôt pour Debian 11 géré directement par Clickhouse, l'installation sera donc simple. 
Afin de ne pas risquer de "polluer" une machine existante j'ai décidé d'installer une machine virtuelle KVM munie d'un "gros" disque car j'avais dans l'idée d'y charger une grosse table, (justement mes archives de Ntopng) qui fait plus de 600 millions de rangs (6 ans d'archives). 
La machine comportait au démarrage : 

CPU       : 4 VCPU 
Disque   : #400Go 
RAM      = 6 Go 

Ajouter la clef GPG de clickhouse (en root) : 

apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 8919F6BD2B48D754 

Ajouter un fichier "clickhouse.list" dans /etc/apt/sources.list.d contenant : 
deb https://packages.clickhouse.com/deb stable main 

Il suffira alors d'un "apt update" et tout sera prêt pour lancer l'installation : 
apt install --force-overwrite clickhouse-client clickhouse clickhouse-common clickhouse-common-static clickhouse-server clickhouse-tools 

Note : J'ai été obligé d'utiliser --forcce-overwrite à cause du paquet clickhouse-tools. 
L'installation s'est bien passée et tout a fonctionné rapidement, le fichier "service" pour systemd est bien sûr disponible. Le serveur dispose de plusieurs ports dédiés à : 
Port natif
Port Mysql like
Port Postgresql like 

Il est ainsi possible d'utiliser une syntaxe connue telle de Mysql/MariaDB ou Postgresql. 
Je me suis concentré sur la syntaxe Mysql que je connais mieux que celle de Postgres. J'ai donc pu créer rapidement un utilisateur dédié, les bases de données et les tables dont j'avais besoin. 
On peut même créer une base "virtuelle" reliée directement à une base Mysql/MariaDB.
Mon seul problème a été la taille de RAM allouée à cette machine, les 6Go se sont vite avérés insuffisants, le chargement des données ne dépassait pas 100 millions de rangs ... 
Après quelques tâtonnements j'ai vu qu'il ne fallait pas être avare avec la RAM pour obtenir un fonctionnement sans plantage. Lors des chargements de données des "Segmentation fault" et lors de l'exécution d'ordres SQL des plantages pour "(MEMORY_LIMIT_EXCEEDED)".
j'ai donc, par paliers, augmenté la RAM jusqu'à un fonctionnement impeccable obtenu dès 24Go que j'ai un peu poussé à 28Go pour pouvoir charger sans problèmes ma "grosse" table.

28 Go cela fait déjà une jolie VM heureusement que la machine "support" dispose de 64Go !

Clickhouse : premier contact

Clickhouse : premier contact jpp

J'ai lu quelques doc, mais ce qui m'a intéressé c'est la compatibilité avec la syntaxe Mysql. 
Pour l'utiliser pleinement il suffit de connecter son client mysql favori (a installer si besoin) sur le port 9004. Je me suis alors retrouvé dans un environnement "connu" qui m'a permis de faire les premières manips. 
Créer une nouvelle base de données : "CREATE DATABASE toto;" 
Aller voir dans cette base : "use toto;" 
Créer un utilisateur avec les droits d'administration :

create user administrateur identified by 'le_mot_de_passe_qui_tue'; 
grant all on *.* to administrateur;

Par contre pour créer une table il faut se plier à la syntaxe "locale", les types ne sont pas tout à fait les mêmes et il faut un peu "ruser". 
Le "CREATE TABLE" n'offre aucun mystère. 
Pour le "ENGINE" à utiliser il y en a plusieurs j'ai utilisé le plus "simple" : "MergeTree" avec les majuscules pour lequel il faut préciser un "order by xxx" et éventuellement un partitionnement. 
Je n'ai pas testé le partitionnement, pas encore ... 
Pour les champs numériques : int16,int32,int64; UInt16, UInt32 ... 
Pour les champs date : Datetime fonctionne bien 
Pour les champs alphanumériques : String. 
Ne pas oublier de préciser "Nullable(type)" pour les champs qui peuvent prendre la valeur NULL.

En ce qui concerne les fonctions (par exemple celles sur les dates) la syntaxe est, malheureusement, différente de celle proposée par Mysql/MariaDB.

Une fonction particulièrement intéressante pour communiquer avec des bases Mysql/MariaDB est la possibilité de création de bases "distantes". Cette possibilité est analogue aux fonctionnalités accessibles à l'aide du module "federated" de MariaDB. 
Il devient ainsi possible de récupérer directement dans Clickhouse des données d'une base extérieure. C'est cette fonctionnalité que j'ai utilisée pour importer la grosse table destinée aux essais. 
La syntaxe est très simple (connexion sur le port "Mysql" : 
CREATE TABLE toto as select * from base_distante.table.

Clickhouse : charger table test

Clickhouse : charger table test jpp

Le chargement de la table va être possible en utilisant des ordres SQL (syntaxe Mysql) avec :

  • Création d'une base distante
  • Création d'une table locale
  • Insertion dans la table locale des rangs de la table distante.

La création d'une base distante possède une syntaxe proche de celle utilisée par les tables "federated" :

CREATE DATABASE base_distante 
  ENGINE = MySQL('IP:3306', 
  'nom_base_distante', 
  'user distant', 'mot_de_passe_distant') 
  SETTINGS read_write_timeout=10000, connect_timeout=10;

Il est alors possible de faire un "SELECT" sur la base distante. 
La recopie d'une table devient :

insert into base_locale.nom_table 
    select * from base_distante.nom_table 
    where idx > 0 ;

Pour une table volumineuse le temps peut être assez important. Dans mon cas le transfert entre la MV et la machine physique atteignait 130Mo/seconde et le temps total de chargement de plus de 500 millions de rangs a duré un peu moins d'une heure. 
Dans la base MariaDB cette table utilise environ 300GO, Dans la base Clickhouse le volume est d'un peu plus de 140GO. Visiblement la compression semble bien fonctionner. 
 

Clickhouse : Test 1

Clickhouse : Test 1 jpp

Pour ce premier test sur cette "grosse" table j'ai repris un ordre SQL dont je m'étais servi pour repérer les plus gros scanneurs. Je rappelle que cette table contient des données enregistrées par Ntopng sur ma machine "pont" vers Internet. Et les scans de port sont extrêmement courants, chaque machine connectée à Internet en subit plusieurs centaines par jour. 
Cet ordre examine toutes les données d'entrée qui n'ont donné lieu à aucune réponse (port fermé ou IP bloquée par le firewall) la sortie est limitée aux 20 plus "gros scanneurs". 
select IP_SRC_ADDR as IP_V4,count(*) as CTR, 
    count(distinct L4_DST_PORT) as NBPORTS, 
    min(FIRST_SWITCHED) as DEBUT,max(LAST_SWITCHED) as FIN 
from flowsv4 
where IP_SRC_ADDR not between '192.158.1.1' and '192.168.3.255' 
  and OUT_BYTES = 0 
group by 1 
order by 2 desc 
limit 20;

Et voici les résulats :

1) Pour Clickhouse :

┌─IP_V4───────────┬────CTR─┬─NBPORTS─┬───────────────DEBUT─┬─────────────────FIN─┐ 
│ 0.0.0.0         │ 133379 │       2 │ 2016-08-02 21:20:07 │ 2022-08-27 00:13:06 │ 
│ 45.143.220.101  │  80996 │   64408 │ 2020-03-22 22:19:49 │ 2020-04-17 02:14:24 │ 
│ 92.63.197.23    │  65295 │   65295 │ 2021-09-17 22:38:39 │ 2021-09-24 05:22:53 │ 
│ 45.143.200.34   │  46036 │   25699 │ 2021-04-09 23:14:37 │ 2022-02-10 21:25:53 │ 
│ 173.231.60.195  │  44355 │       2 │ 2021-12-09 06:09:06 │ 2022-03-09 13:07:02 │ 
│ 92.63.197.108   │  38179 │   32243 │ 2021-08-22 13:29:04 │ 2021-11-25 04:31:09 │ 
│ 146.88.240.4    │  37229 │      63 │ 2019-04-11 04:59:05 │ 2022-08-26 06:02:19 │ 
│ 92.63.197.112   │  36060 │   32081 │ 2021-08-22 13:27:51 │ 2021-11-25 10:31:34 │ 
│ 92.63.197.110   │  35671 │   29999 │ 2021-08-22 13:29:24 │ 2022-07-10 12:52:55 │ 
│ 212.70.149.69   │  35319 │       1 │ 2020-10-19 04:34:22 │ 2020-12-18 12:52:19 │ 
│ 10.128.175.2    │  33844 │       1 │ 2018-06-02 05:03:16 │ 2020-07-01 12:23:01 │ 
│ 176.113.115.246 │  33618 │   32906 │ 2020-01-29 09:24:25 │ 2020-05-09 20:52:01 │ 
│ 212.70.149.68   │  32400 │       1 │ 2020-08-21 04:34:36 │ 2020-10-16 20:38:35 │ 
│ 185.176.27.2    │  32322 │   22565 │ 2018-12-06 17:13:49 │ 2020-08-12 23:43:46 │ 
│ 92.154.95.236   │  31960 │    1092 │ 2017-12-07 23:05:08 │ 2021-12-13 13:30:54 │ 
│ 185.156.73.91   │  31756 │   20609 │ 2020-10-04 03:09:37 │ 2022-08-26 23:44:13 │ 
│ 185.156.73.107  │  31625 │   25338 │ 2021-06-19 09:04:14 │ 2022-02-13 12:30:53 │ 
│ 195.54.166.93   │  31183 │   29850 │ 2020-01-15 21:46:51 │ 2020-02-17 05:38:35 │ 
│ 51.68.126.248   │  30228 │       4 │ 2020-02-25 22:40:19 │ 2020-04-06 01:24:36 │ 
│ 92.63.197.105   │  29300 │   16042 │ 2021-05-16 17:24:56 │ 2021-12-07 23:21:30 │ 
└─────────────────┴────────┴─────────┴─────────────────────┴─────────────────────┘ 
20 rows in set. Elapsed: 6.138 sec. Processed 488.52 million rows, 18.98 GB (79.59 million rows/s., 3.09 GB/s.)

 2) Pour MariaDB : 

+-----------------+--------+---------+---------------------+---------------------+ 
| IP_V4           | CTR    | NBPORTS | DEBUT               | FIN                 | 
+-----------------+--------+---------+---------------------+---------------------+ 
| 0.0.0.0         | 133379 |       2 | 2016-08-02 21:20:07 | 2022-08-27 00:13:06 | 
| 45.143.220.101  |  80996 |   64408 | 2020-03-22 22:19:49 | 2020-04-17 02:14:24 | 
| 92.63.197.23    |  65295 |   65295 | 2021-09-17 22:38:39 | 2021-09-24 05:22:53 | 
| 45.143.200.34   |  46036 |   25699 | 2021-04-09 23:14:37 | 2022-02-10 21:25:53 | 
| 173.231.60.195  |  44355 |       2 | 2021-12-09 06:09:06 | 2022-03-09 13:07:02 | 
| 92.63.197.108   |  38179 |   32243 | 2021-08-22 13:29:04 | 2021-11-25 04:31:09 | 
| 146.88.240.4    |  37229 |      63 | 2019-04-11 04:59:05 | 2022-08-26 06:02:19 | 
| 92.63.197.112   |  36060 |   32081 | 2021-08-22 13:27:51 | 2021-11-25 10:31:34 | 
| 92.63.197.110   |  35671 |   29999 | 2021-08-22 13:29:24 | 2022-07-10 12:52:55 | 
| 212.70.149.69   |  35319 |       1 | 2020-10-19 04:34:22 | 2020-12-18 12:52:19 | 
| 10.128.175.2    |  33844 |       1 | 2018-06-02 05:03:16 | 2020-07-01 12:23:01 | 
| 176.113.115.246 |  33618 |   32906 | 2020-01-29 09:24:25 | 2020-05-09 20:52:01 | 
| 212.70.149.68   |  32400 |       1 | 2020-08-21 04:34:36 | 2020-10-16 20:38:35 | 
| 185.176.27.2    |  32322 |   22565 | 2018-12-06 17:13:49 | 2020-08-12 23:43:46 | 
| 92.154.95.236   |  31960 |    1092 | 2017-12-07 23:05:08 | 2021-12-13 13:30:54 | 
| 185.156.73.91   |  31756 |   20609 | 2020-10-04 03:09:37 | 2022-08-26 23:44:13 | 
| 185.156.73.107  |  31625 |   25338 | 2021-06-19 09:04:14 | 2022-02-13 12:30:53 | 
| 195.54.166.93   |  31183 |   29850 | 2020-01-15 21:46:51 | 2020-02-17 05:38:35 | 
| 51.68.126.248   |  30228 |       4 | 2020-02-25 22:40:19 | 2020-04-06 01:24:36 | 
| 92.63.197.105   |  29300 |   16042 | 2021-05-16 17:24:56 | 2021-12-07 23:21:30 | 
+-----------------+--------+---------+---------------------+---------------------+ 
20 rows in set (12 min 56,412 sec)

3) Comparaison.

Les résultats sont identiques ce qui est très bien ! 
La comparaison est impressionnante : 6 secondes au lieu de 776 soit quasiment 130 fois plus vite !

On voit aussi que l'adresse IP 45.143.220.101 a scanné quasiment l'intégralité des ports existants en un peu moins d'un mois (scan "lent") ou que l'IP 173.231.60.195 ne scanne que deux ports mais a effectué plus de 40000 connexions en un peu moins d'un an, cela fait quand même plus de 100 par jour.

Clickhouse : Test 2

Clickhouse : Test 2 jpp

Cet ordre SQL devait me permettre de voir s'il y avait des trous de numérotation dans les valeurs du champ "idx". 
Il m'a valu de nombreux ennuis avant que la mémoire ne soir "gonflée" avec de méchants messages : 
Received exception from server (version 22.8.2): 
Code: 241. DB::Exception: Received from localhost:9000. DB::Exception: Memory limit (total) exceeded: would use 3.73 GiB (attempt to allocate chunk of 537537181 bytes), maximum: 3.48 GiB. OvercommitTracker decision: Query was selected to stop by OvercommitTracker.: While executing AggregatingTransform. (MEMORY_LIMIT_EXCEEDED)

L'ordre est très simple : 
select min(idx),max(idx), count(distinct idx) from flowsv4;

Dès que la RAM a été "suffisante" (plus de 24Go de RAM) ces messages ont disparu et les résultats sont là et bien là.

1) Résultats Clickhouse.

┌─min(idx)─┬──max(idx)─┬─uniqExact(idx)─┐ 
│        1 │ 488553902 │      488521496 │ 
└──────────┴───────────┴────────────────┘ 
1 row in set. Elapsed: 27.822 sec. Processed 488.52 million rows, 3.91 GB (17.56 million rows/s., 140.47 MB/s.)

2) Résultats MariaDB.

+----------+-----------+---------------------+ 
| min(idx) | max(idx)  | count(distinct idx) | 
+----------+-----------+---------------------+ 
|        1 | 488553902 |           488521496 | 
+----------+-----------+---------------------+ 
1 row in set (5 min 42,522 sec)

3) Résultat final. 
27 secondes contre 342 soit environ 13 fois plus vite.

Clickhouse : Test 3

Clickhouse : Test 3 jpp

Le but de cet ordre SQL est de trouver les adresses IP qui ont contacté sans succès la machine, ceci est identifié par une valeur 0 dans le champ OUT_BYTES et qui ont "tenté" des connexions sur au moins 10 ports différents. Ceci peut arriver si la demande a été bloquée par le firewall (drop). 
L'ordre SQL est légèrement différent car les fonctions de gestion des dates ont des syntaxes légèrement différentes.

select IP_SRC_ADDR as SRC,IP_DST_ADDR as DST, 
    count(distinct L4_DST_PORT) as NPORT 
from flowsv4 
-- mariadb    where FIRST_SWITCHED > DATE_SUB(now(), interval 20 day) 
-- clickhouse where FIRST_SWITCHED > DATE_SUB(DAY,20,now()) 
and IP_SRC_ADDR not between '192.168.1.1' 
                and '192.168.3.255' 
and L4_DST_PORT < 15000 
group by  SRC,DST 
having NPORT > 10 
order by SRC;

La différence n'est pas énorme mais il faut y faire attention ... 
Et maintenant passons aux faits.

1) Clickhouse.

┌─SRC─────────────┬─DST─────────┬─NPORT─┐ 
│ 103.138.109.220 │ 192.168.1.2 │   105 │ 
│ 103.56.61.144   │ 192.168.1.2 │    12 │ 
│ 104.156.155.13  │ 192.168.1.2 │  1216 │ 
│ 104.168.53.114  │ 192.168.1.2 │    23 │ 
   ...... 
│ 94.232.45.241   │ 192.168.1.2 │    68 │ 
│ 95.161.131.235  │ 192.168.1.2 │    19 │ 
└─────────────────┴─────────────┴───────┘ 
377 rows in set. Elapsed: 0.120 sec. Processed 7.92 million rows, 395.57 MB (66.10 million rows/s., 3.30 GB/s.)

2) MariaDB.

+-----------------+-------------+-------+ 
| SRC             | DST         | NPORT | 
+-----------------+-------------+-------+ 
| 103.138.109.220 | 192.168.1.2 |   105 | 
| 103.56.61.144   | 192.168.1.2 |    12 | 
| 104.156.155.13  | 192.168.1.2 |  1216 | 
| 104.168.53.114  | 192.168.1.2 |    23 | 
    ...... 
| 94.232.45.241   | 192.168.1.2 |    68 | 
| 95.161.131.235  | 192.168.1.2 |    19 | 
+-----------------+-------------+-------+ 
377 rows in set (19,137 sec)

3) Comparaison. 
Le rapport entre 19 secondes et 0.12 est frappant, Clickhouse est 158 fois plus rapide.

Clickhouse : Test 4

Clickhouse : Test 4 jpp

Cet ordre SQL est très simple et le résultat est plus que spectaculaire, le passage "Clickhouse" a été réalisé avec l'interface compatible Mysql.

L'ordre est très simple :
select count(*),max(LAST_SWITCHED),max(FIRST_SWITCHED) from flowsv4;

1) Résultats Clickhouse.

+-----------+---------------------+---------------------+
| count()   | max(LAST_SWITCHED)  | max(FIRST_SWITCHED) |
+-----------+---------------------+---------------------+
| 521129534 | 2022-11-17 23:35:18 | 2022-11-17 23:35:18 |
+-----------+---------------------+---------------------+
1 row in set (1.863 sec)

2) Résultats MariaDB.

+-----------+--------------------+---------------------+
| count(*)  | max(LAST_SWITCHED) | max(FIRST_SWITCHED) |
+-----------+--------------------+---------------------+
| 521129338 |         1668724485 |          1668724485 |
+-----------+--------------------+---------------------+
1 row in set (13 min 40,620 sec)

3) Résultat final.  
1,85 secondes pour Clickhouse contre 820 pour MariaDB soit environ 440 fois plus vite !

Clickhouse : Test 5

Clickhouse : Test 5 jpp

J'ai voulu faire un dernier test qui représente un gros travail toujours à partir de la même table qui atteint maintenant 542 975 185 rangs ce qui représente un bon test.

J'ai voulu chercher les ports TCP les plus attaqués en sélectionnant les connections TCP qui ont fourni un retour de 0 octets, le SQL est donc :

select L4_DST_PORT,count(*) CTR
from local_ntopng.flowsv4
where OUT_BYTES = 0 -- aucune réponse
  and PROTOCOL = 6  -- (TCP)
group by 1
order by CTR DESC
limit 30;

Le résultat est, là aussi, stupéfiant :

Pour Clickhouse les 30 rangs sont retournés en moins de deux secondes (1;813 exactement et en 14 minutes et 18 secondes soit 858 secondes, le rapport de vitesse est proprement stupéfiant ... Clickhouse va 473 fois plus vite que MariaDB.

Rappels :

  • Les tests sont faits sur la même machine et les données sont sur les deux disques Samsung SSD 860 de 2To montés en miroir.
  • Les résultats présentent de très légères différences car la recopie des données depuis la source n'est pas "synchrone", il y a donc un (tout petit) décalage possible.

Voir les résultats ci-dessous.

CLICKHOUSE 1.813 sec MariaDB 14 min 18 se

443

1434014

443

1434014

80

1059068

80

1059067

23

749049

23

749049

445

253752

445

253752

22

229300

22

229300

465

184397

465

184397

6379

163679

6379

163679

8080

143206

8080

143205

1433

116601

1433

116600

81

85489

81

85489

3389

85076

3389

85075

5222

77222

5222

77222

25

74815

25

74815

2323

65065

2323

65065

995

62827

995

62827

 

     

Syntaxe Sql

Syntaxe Sql jpp

Il est possible, avec un client Mysql/MariaDB standard, de se connecter à Clickhouse (port 9004) et d'utiliser des ordres SQL avec la syntaxe propre à Mysql/MariaDB. 
Il existe aussi une possibilité d'utiliser un client Postgresl en se connectant à Clickhouse (port 9005), je n'ai pas encore testé cette entrée.

On peut par exemple créer un utilisateur avec : 
create user lechef identified by 'le_beau_password'; 
grant all on *.* to lechef; 
Ce que je préfère nettement au "bidouillage" de fichiers XML.

Par exemple pour charger ma "grosse" table depuis une base MariaDB dans Clickhouse (dans une machine virtuelle) j'ai créé dans Clickhouse un "serveur" connecté à la base située sur la machine "physique" à l'aide d'un ordre "simple" (du même style que pour l'option "federatedx" disponible dans MariaDB :

CREATE DATABASE test_maria 
  ENGINE = MySQL('192.168.2.7:3306', 
  'local_ntopng', 
  'test', 'test') 
  SETTINGS read_write_timeout=10000, connect_timeout=10;

Dans la base "physique" la table est au standard Mysql et on peut donc y accéder à travers cette base "test_maria" par cette sorte de "dblink". L'ordre de chargement des données est très simple :

insert into local_ntopng.flowsv4 
    select * from test_maria.flowsv4;

select min(idx),max(idx),count(distinct idx) from local_ntopng.flowsv4;

Le temps d'exécution est assez important car il faut passer par :

  • Mariadb sur la machine physique
  • Le réseau entre les deux machines
  • Et enfin Clickhouse à travers le fameux dblink.

Comme déjà remarqué la consommation d'IO disques de Clickhouse est très irrégulière et a oscillé entre 0 et 240Mo/seconde. Le lien réseau est toujours resté au dessus de 100MO/sec avec des pointes à près de 200MO/sec (c'est en local sur la même machine physique). 
Les résultats : 
Query OK, 505751818 rows affected (36 min 54.958 sec) 
+----------+-----------+----------------+ 
| min(idx) | max(idx)  | uniqExact(idx) | 
+----------+-----------+----------------+ 
|        1 | 505785066 |      505751818 | 
+----------+-----------+----------------+ 
1 row in set (31.284 sec)

Ce qui n'est pas mauvais et simple pour ceux qui connaissent un peu le SQL.

Syntaxe SQL : suite

Syntaxe SQL : suite jpp

J'ai eu besoin de modifier la notion "Nullable" d'une colonne, or il semble que ce soit impossible. 
La table que j'ai utilisée pour les premiers tests comporte toutes les colonnes en Nullable ce qui peut gêner certains types d' opérations. 
J'ai donc initialisé une nouvelle table avec les colonnes adéquates en "not null" et j'y ai inséré les lignes de l'ancienne table. 
Au passage une remarque sur la syntaxe : 
- Une colonne "nullable" se déclare sous la forme "Nullable(String)
- La même en "not null" se déclare sous la forme plus classique "String not null".

La recopie est une opération simple 

insert into table1 select * from table2;

J'ai donc lancé cet ordre en direct sur cette table de 540 Millions de rangs et le résultat est :

Elapsed: 817.372 sec. Processed 524.04 million rows, 200.88 GB 
(641.13 thousand rows/s., 245.76 MB/s.)

Soit quand même plus de 13 minutes  ...

CPU > 90 .. 130%
IO  60 .. 370 Mo/seconde la moitié du temps en lecture, l'autre moitié en écriture.

Pour se débarrasser de l'ancienne table :

- sudo touch /var/lib/clickhouse/flags/force_drop_table
- sudo chmod 666 /var/lib/clickhouse/flags/force_drop_table

- Puis, enfin dans le shell Clickhouse. 

"drop ancienne_table" 

 

Null ou pas Null

Null ou pas Null jpp

J'ai eu besoin de modifier la notion "Nullable" d'une colonne car il est impossible d'utiliser "GROUP BY" sur une colonne "Nullable", or il semble que ce soit impossible avec une commande "ALTER" standard. 
J'ai donc initialisé une nouvelle table avec les colonnes adéquates en "not null" et j'y ai inséré les lignes de l'ancienne table. 
Au passage une remarque sur la syntaxe (pas "standard" ?) :

  1. - Une colonne "nullable" se déclare sous la forme "Nullable(String)"
  2. - La même en "not null" se déclare sous la forme plus classique "String not null".

La notion de longueur de chaîne de caractères est ici inconnue.

La recopie est une opération simple : 

insert into table1 select * from table2;

J'ai donc lancé cet ordre en direct sur cette table de plus de 520 Millions de rangs et le résultat est :

Elapsed: 817.372 sec. Processed 524.04 million rows, 200.88 GB 
(641.13 thousand rows/s., 245.76 MB/s.)

Soit malgré tout plus de 13 minutes et demi ... mais pour autant de millions de lignes c'est une bonne performance.  
Rappel :  
la machine dispose de 28Go de RAM et de 4 VCPU ainsi que l'accès à un espace de stockage sur une paire de SSD en Raid 1. 
Avec des disques plus rapides, par exemple 4 NVME en Raid 10 on atteindrait des sommets. Avec un tel système disques j'ai vu des vitesses d'écriture à plus de 1,5Go/sec, hélas ce n'est pas à la portée de toutes les bourses et même impossible sur la majorité des cartes mères "amateurs". 
Au cours de cette copie j'ai pu constater :

CPU de 90 .. 130%
IO  60 .. 370 Mo/seconde la moitié du temps en lecture, l'autre moitié en écriture.

Pour se débarrasser de l'ancienne table :

  1.  sudo touch /var/lib/clickhouse/flags/force_drop_table
  2.  sudo chmod 666 /var/lib/clickhouse/flags/force_drop_table
  3.  Puis, enfin, le "drop ancienne_table" dans le shell Clickhouse.

Il n'est pas si facile que cela de se débarrasser d'une table encombrante... 
 

Quelques particularités

Quelques particularités jpp

Note spécifique : 
La mémoire « huge pages » peut être utilisée mais, au niveau du noyau, le paramètre « transparent_hugepages » ne doit pas être à « always », « madvise » est plus judicieux ici et de toutes façons tout le monde ne met pas en "huge pages" la quantité de mémoire nécessaire.

Clickhouse est un gros mangeur de mémoire et manifeste bruyamment son désaccord s’il ne dispose pas de la mémoire nécessaire à ses opérations. 
Au cours de mes essais, commencés avec une mémoire « faible » (6144Mo), je me suis heurté à quelques plantages pour des questions de mémoire. 
La plupart de ces problèmes ont eu lieu lors des tests de chargement à l’aide de l’utilitaire « clickhouse-mysql ». 
J’ai obtenu plusieurs fois (au fur et à mesure de l’augmentation de la taille mémoire de la machine virtuelle) le message suivant : 
Segmentation fault      clickhouse-mysql 
Dès que la mémoire a été « suffisante » cette erreur a disparu.

Autre type d’erreur rencontrée lors de l’exécution d’un ordre SQL simple (toujours avec une mémoire "insuffisante") permettant de repérer la quantité de « trous » dans la numérotation : 
select min(idx),max(idx), count(distinct idx) from flowsv4; 
Avant les augmentations de mémoire et sur une table partiellement chargée (de 100 à 260 millions de rangs) on obtient un stop brutal de l’ordre SQL avec quelques messages : 
Received exception from server (version 22.8.2): 
Code: 241. DB::Exception: Received from localhost:9000. DB::Exception: Memory limit (total) exceeded: would use 3.73 GiB (attempt to allocate chunk of 537537181 bytes), maximum: 3.48 GiB. OvercommitTracker decision: Query was selected to stop by OvercommitTracker.: While executing AggregatingTransform. (MEMORY_LIMIT_EXCEEDED)

 Dès que la mémoire est « suffisante » et même avec une table de plus de 488 millions de rangs l’ordre s’exécute sans ennuis et avec une rapidité tout à fait satisfaisante : 
┌─min(idx)─┬──max(idx)─┬─uniqExact(idx)─┐ 
│        1 │ 488553902 │      488521496 │ 
└──────────┴───────────┴────────────────┘ 
↙ Progress: 488.52 million rows, 3.91 GB (19.19 million rows/s., 153.56 MB/s.)   
1 row in set. Elapsed: 25.530 sec. Processed 488.52 million rows, 3.91 GB (19.13 million rows/s., 153.08 MB/s.) 
Alors que sur le même volume MariaDB attends à peine moins de 6 minutes avant de donner; heureusement, le même résultat.

Cas spécial du « drop table ». 
Une protection existe contre le drop accidentel d’une table, cette protection (par défaut à 50GB) est gérée par le paramètre « max_table_size_to_drop ». Si vous gardez cette protection le drop d’une « grosse table » peut makgré tout être effectué en créant un fichier spécifique qui sera détruit après le drop (Eh oui, il ne peut servir qu'une fois) : 
sudo touch  /var/lib/clickhouse/flags/force_drop_table 
sudo chown clickhouse: /var/lib/clickhouse/flags/force_drop_table 
Il est alors possible d’effectuer le « drop » de cette "grosse" table. 
Ce paramétrage peut sembler judicieux car le chargement de ces tables est assez consommateur de temps et on ne les charge pas uniquement pour les détruire.

Encore un test

Encore un test jpp

Ce test montre assez bien la vitesse de Clickhouse.

La table "origine" est stockée sur une machine distante, les accès se font donc à travers le réseau. 
Pour MariaDB utilisation de Federatedx pour accès à la base distante, pour Clickhouse la structure équivalente de base Mysql distante avec une syntaxe de création assez semblable à celle utilisée par MariaDB. 
Résultats :

MariaDB : 
Query OK, 1938339 rows affected (33,521 sec) 
Enregistrements: 1938339  Doublons: 0  Avertissements: 0

Clickhouse : 
Query OK, 1939250 rows affected (19.682 sec)

Le temps de transfert depuis la machine d'origine doit être à peu près le même car j'ai redémarré le service sur la machine d'origine pour éviter l'influence du cache.

Les ordres SQL sont identiques :

insert into local_ntopng.flowsv4 
    select * from portail_maria.flowsv4 
    where idx > ( select max(idx) from local_ntopng.flowsv4 );

Les deux tests utilisent un accès réseau vers la machine "origine" des données.