Discussion :

[Artemus24]

Salut à tous.

Comment obtenir le nombre de lignes dans une table du SGBDR MySql le plus rapidement possible ?

Cordialement.
Artémus24.
@+

[Séb.]

InnoDB n'est pas bon pour cela dès que la table prend du volume.

Le mieux que j'ai pu obtenir, à tester pour une valeur exacte :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT ALL COUNT(*)
FROM your_table
WHERE id > 0; -- Pour forcer l'utilisation de l'index

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT ALL COUNT(*)
FROM your_table USE INDEX (PRIMARY);

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT ALL COUNT(*)
FROM your_table USE INDEX (); -- Désactivation des index

Si une valeur approximative te suffit, et pour un résultat instantané :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT ALL TABLE_ROWS
FROM information_schema.TABLES
WHERE (TABLE_SCHEMA, TABLE_NAME) = ('your_db', 'your_table');

[Artemus24]

Salut Seb.

Dans une table, j'ai 1 million de lignes. Voici un exemple de ce que j'ai testé.

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
--------------
START TRANSACTION
--------------
 
--------------
flush tables
--------------
 
--------------
set profiling=1
--------------
 
--------------
select sql_no_cache count(*)   as Nbre from `charniere` use index()
--------------
 
+---------+
| Nbre    |
+---------+
| 1000000 |
+---------+
--------------
select sql_no_cache count(*)   as Nbre from `charniere` use index(primary)
--------------
 
+---------+
| Nbre    |
+---------+
| 1000000 |
+---------+
--------------
select sql_no_cache count(*)   as Nbre from `charniere` use index(fk_01)
--------------
 
+---------+
| Nbre    |
+---------+
| 1000000 |
+---------+
--------------
select sql_no_cache count(*)   as Nbre from `charniere` use index(fk_02)
--------------
 
+---------+
| Nbre    |
+---------+
| 1000000 |
+---------+
--------------
select max(id)    as Nbre from `charniere`
--------------
 
+---------+
| Nbre    |
+---------+
| 1000000 |
+---------+
--------------
select table_rows as Nbre from information_schema.tables WHERE table_schema='base' and table_name='charniere'
--------------
 
+--------+
| Nbre   |
+--------+
| 997162 |
+--------+
--------------
select *                  from `charniere` where id=127
--------------
 
+-----+---------+---------+
| id  | medecin | patient |
+-----+---------+---------+
| 127 |     122 |     127 |
+-----+---------+---------+
--------------
set profiling=0
--------------
 
--------------
SHOW PROFILES
--------------
 
+----------+------------+---------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                         |
+----------+------------+---------------------------------------------------------------------------------------------------------------+
|        1 | 0.02963225 | select sql_no_cache count(*)   as Nbre from `charniere` use index()                                           |
|        2 | 0.02776975 | select sql_no_cache count(*)   as Nbre from `charniere` use index(primary)                                    |
|        3 | 0.03404550 | select sql_no_cache count(*)   as Nbre from `charniere` use index(fk_01)                                      |
|        4 | 0.02986225 | select sql_no_cache count(*)   as Nbre from `charniere` use index(fk_02)                                      |
|        5 | 0.00022950 | select max(id)    as Nbre from `charniere`                                                                    |
|        6 | 0.00304200 | select table_rows as Nbre from information_schema.tables WHERE table_schema='base' and table_name='charniere' |
|        7 | 0.00021450 | select *                  from `charniere` where id=127                                                       |
+----------+------------+---------------------------------------------------------------------------------------------------------------+
--------------
COMMIT
--------------
 
Début :  8:18:06,58
Fin ..:  8:18:07,79
Durée : 00:00:01,21

Mon script a durée environ 1,21 secondes de temps élaps.
Pour chacune des requêtes testées, elles sont toutes inférieures à 1/20^ième de seconde, soit 0.05 seconde.
Je trouve que c'est relativement performant. Je m'attendais à pire.

J'ai juste un petit problème avec les statistiques de la table
J'ai fait "analyze table charniere", et je constate que les statistique de la table "information_schema.tables" restent fausses.

Tout le monde dit que MySql est pourri en terme de performance, je pense plutôt qu'ils ne savent pas correctement le paramétrer.
J'ai refais l'exercice que j'ai trouvé sous developpez.
Le chargement des trois tables, médecin, patient et charnière à pris au maximum 1 minute 30,63 secondes en temps élaps.
Comparativement, dans l'exercice, il est indiqué plus de 50 minutes que pour le chargement.

Cordialement.
Artemus.

[Séb.]

J'ai refais l'exercice que j'ai trouvé sous developpez.
Le chargement des trois tables, médecin, patient et charnière à pris au maximum 1 minute 30,63 secondes en temps élaps.
Comparativement, dans l'exercice, il est indiqué plus de 50 minutes que pour le chargement.

Sont bizarres ses chiffres, 7 sec. pour insérer 4000 lignes
Tu sais où je peux récupérer la data pour essayer moi aussi ?

[Séb.]

Source primaire : https://www.developpez.net/forums/bl...esql-vs-mysql/
Je viens de faire attention, il s'agit d'un billet de 2015, au mieux obsolète

[Artemus24]

Salut Seb.

Ce test me sert à améliorer d'une part le chargement des tables et l'écriture des requêtes en terme de performance.
Mais aussi de voir ce qui peut être changé dans mon fichier "my.cnf".

Tu sais où je peux récupérer la data pour essayer moi aussi ?

Dans mon autre sujet, dont voici le lien .
Quand j'ai fait la première fois l'exercice, j'avais tout mis dans le même script MySql. Cette fois-ci, j'ai préféré le découper en cinq scripts

Je viens de faire attention, il s'agit d'un billet de 2015, au mieux obsolète

Peut-être, mais je pense que cet utilisateur ne sait pas configurer correctement MySql.
J'avais déjà ouvert un sujet, il y a quelques années, sur cet exercice, et j'avais une meilleure performance que ce M. Gugelhupf.
Si tu prends le temps de lire les commentaires, tu remarques que je ne suis pas le seul à trouver ce benchmark aberrant.

Sont bizarres ses chiffres, 7 sec. pour insérer 4000 lignes

Parles-tu du A1 dont MySql met 6860.102ms soit 6,860102 secondes ou 7 secondes en arrondissant ?

J'ai créé des procédures stockées pour simuler le chargement de ces trois tables.
Voir le lien ci-dessus où je t'ai donné mes scripts MySql que tu peux utiliser et améliorer selon ta convenance.

Il dit qu'il utilise le "set profiling" pour effectuer les temps machines ou temps cpu.
Il y a déjà un problème car normalement un insert va produire un query dans le "profiling".
S'il y a 4000 lignes, il y a 4000 query et doit faire un cumul pour trouver le bon résultat.

En ce qui me concerne, j'ai fait différemment, en calculant le temps élaps.
Comme je suis sous Windows 10 Pro, je me suis fait un batch windows qui va calculer cette différence de temps.
Voici mon batch windows :

Code batch :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
@echo off
chcp 1252 > nul
 
SET PATH=.;%WAMPSERVER%\bin\mysql\%MYSQL%\bin\;%PATH%
SET FIC=%1
SET FIC=%FIC:sql=txt%
 
cd %CD%
 
set DEB=%TIME%
mysql  --execute "source %CD%/%1" 1>%FIC% 2>&1  --local_infile=1  --verbose  --force  --unbuffered
set FIN=%TIME%
 
IF "%FIN:~0,1%" == "0" (set /a HH=%FIN:~1,1%)  ELSE (set /a HH=%FIN:~0,2%)
IF "%FIN:~3,1%" == "0" (set /a MM=%FIN:~4,1%)  ELSE (set /a MM=%FIN:~3,2%)
IF "%FIN:~6,1%" == "0" (set /a SS=%FIN:~7,1%)  ELSE (set /a SS=%FIN:~6,2%)
IF "%FIN:~9,1%" == "0" (set /a CC=%FIN:~10,1%) ELSE (set /a CC=%FIN:~9,2%)
 
set /a DIF=(%HH%*36000)+(%MM%*6000)+(%SS%*100)+(%CC%*1)
 
IF "%DEB:~0,1%" == "0" (set /a HH=%DEB:~1,1%)  ELSE (set /a HH=%DEB:~0,2%)
IF "%DEB:~3,1%" == "0" (set /a MM=%DEB:~4,1%)  ELSE (set /a MM=%DEB:~3,2%)
IF "%DEB:~6,1%" == "0" (set /a SS=%DEB:~7,1%)  ELSE (set /a SS=%DEB:~6,2%)
IF "%DEB:~9,1%" == "0" (set /a CC=%DEB:~10,1%) ELSE (set /a CC=%DEB:~9,2%)
 
set /a DIF=%DIF%-(%HH%*36000)-(%MM%*6000)-(%SS%*100)-(%CC%*1)
 
set /a Q1=%DIF%/360000
set /a R1=%DIF%-%Q1*360000
 
set /a Q2=%R1%/6000
set /a R2=%R1%-%Q2%*6000
 
set /a Q3=%R2%/100
set /a R3=%R2%-%Q3*100
 
set HH=00%Q1%
set MM=00%Q2%
set SS=00%Q3%
set CC=00%R2%
 
echo Début : %DEB%>>%FIC%
echo Fin ..: %FIN%>>%FIC%
echo Durée : %HH:~-2%:%MM:~-2%:%SS:~-2%,%CC:~-2%>>%FIC%
pause
exit

Pour A1, je trouve en temps élaps 1,28 secondes alors que son test donne 6,8 secondes.
Je pense qu'il n'a pas désactivé, lors du chargement de la table, les index et la journalisation.
Le jeu d'essai doit être conforme à l'intégrité des données que l'on doit obtenir.
On peut se permettre de désactiver les vérifications d'usages dans le SGBDR MySql.

Pour A2, 25 minutes alors que je trouve en temps élaps 38,37 secondes !
Pour A3, 24 minutes alors que je trouve en temps élaps 34,86 secondes.
Puis ensuite, je crée les clefs étrangères dans la table charnière, soit en temps élaps 5,36 secondes.

Je peux calculer le temps cpu à partir de la table information_schema.profiling.
Comme j'utilise une procédure stockée, je risque de comptabiliser autre chose que le temps nécessaire au chargement du jeu d'essai dans la table.

Dois-je te donner aussi mon fichier "my.ini" ?
Peux-tu faire les tests avec mes scripts et me dire ce que tu obtiens comme temps élaps ?

Cordialement.
Artemus24.
@+

[Artemus24]

Salut Seb.

Je suis sous windows 10 Pro et j'utilise la version MySql 8.0.32.
Je pense avoir un problème de paramétrage avec mon SGBDR MySql.

a) Je redémarre MySql. Je lance le script qui contient les tests sur les count(*) et je constate que j'ai perdu mes connexions.
--> Lost connection to MySQL server during query
Quelle peut-être la cause de ce problème ?

Pensant que le problème venait de my.ini, je l'ai désactivé. Mais au redémarrage, mes connexions sont encore perdues.
Il se peut que cela soit normal. C'est quand même bizarre d'avoir ce genre de problème après un redémarrage de MySql.

b) Je relance une deuxième fois le même script et j'ai des résultats.
Sauf que le premier test me donne un temps d'exécution trop important.

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
+----------+------------+----------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                                      |
+----------+------------+----------------------------------------------------------------------------------------------------------------------------+
|        1 | 0.13315200 | select sql_no_cache count(*)   as Nbre from `charniere` use index()                                                        |
|        2 | 0.03271450 | select sql_no_cache count(*)   as Nbre from `charniere` use index(primary)                                                 |
|        3 | 0.03527950 | select sql_no_cache count(*)   as Nbre from `charniere` use index(fk_01)                                                   |
|        4 | 0.03778800 | select sql_no_cache count(*)   as Nbre from `charniere` use index(fk_02)                                                   |
|        5 | 0.00164950 | select sql_no_cache max(id)    as Nbre from `charniere`                                                                    |
|        6 | 0.01566075 | select sql_no_cache table_rows as Nbre from information_schema.tables WHERE table_schema='base' and table_name='charniere' |
|        7 | 0.00069250 | select sql_no_cache *                  from `charniere` where id=127                                                       |
+----------+------------+----------------------------------------------------------------------------------------------------------------------------+

Le premier test (query_id = 1) est plus important que les autres. Il est possible que celui-ci a un impact sur les autres tests.
J'ai mis celui-ci en commentaire et j'ai recommencé la même manipulation.
J'obtiens un temps d'exécution plus important pour le nouveau premier test (query_id = 2 dans la liste ci-dessus).
Les tests suivants sont similaires en temps d'exécution.

c) Je relance une troisième fois le même script.
Cette fois-ci le premier test (query_id = 1) me donne le même résultat que les autres tests.

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
+----------+------------+----------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                                      |
+----------+------------+----------------------------------------------------------------------------------------------------------------------------+
|        1 | 0.03610075 | select sql_no_cache count(*)   as Nbre from `charniere` use index()                                                        |
|        2 | 0.02860750 | select sql_no_cache count(*)   as Nbre from `charniere` use index(primary)                                                 |
|        3 | 0.02862875 | select sql_no_cache count(*)   as Nbre from `charniere` use index(fk_01)                                                   |
|        4 | 0.03519250 | select sql_no_cache count(*)   as Nbre from `charniere` use index(fk_02)                                                   |
|        5 | 0.00025900 | select sql_no_cache max(id)    as Nbre from `charniere`                                                                    |
|        6 | 0.00451875 | select sql_no_cache table_rows as Nbre from information_schema.tables WHERE table_schema='base' and table_name='charniere' |
|        7 | 0.00023400 | select sql_no_cache *                  from `charniere` where id=127                                                       |
+----------+------------+----------------------------------------------------------------------------------------------------------------------------+

J'ai l'impression que le premier test a bien un impact sur les autres tests.

d) J'ai mis "sql_no_cache" dans le select.
Mais cette astuce ne change rien au problème d'avoir le même temps à l'exécution de chaque lancement du script.

Question : Comment puis-je faire pour obtenir à chaque exécution approximativement le même temps à l'exécution ?
Je constate dans mes tests qu'il existe une influence de la requête précédente sur la suivante.

e) Je pense que les temps de la duration correspondent à la récupération d'un résultat stocké en cache.
Sauf que dans la version 8.0, il n'y a plus de cache. Je ne comprends pas. Cache ou pas cache ?

f) Quand je consulte internet, les astuces fonctionnent pour les versions antérieures de MySql 8.0.
Par exemple "set session query_cache_size=0" et "set session query_cache_type=0".
--> https://dev.mysql.com/doc/refman/5.7...iguration.html
Ils sont devenus obsolètes à partir de la version 5.7.20.

g) A priori, le cache est désactivé dans MySql 8.0 :

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
> show variables like 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | NO    |
+------------------+-------+
1 row in set (0.01 sec)
 
>

Mais le comportement de mes requêtes semblent me dire que j'ai quelque part un cache.

h) Après cela, je suis complètement perdu au sujet de ce cache qui est maintenant déactivé :
--> https://mysqlquicksand.wordpress.com...-switched-off/
Je ne peux pas me fier aux astuces et à ce que dit MySql car cela dépend fortement de la version utilisée.

Je trouve aberrant ces temps de la colonne duration. Je m'attendais à quelque chose de plus grand.

i) j'ai gagné environ 10 secondes en modifiant l'insertion des lignes, sur la table `patient` et `charniere`.
Je suis passé de 36 secondes à 26 secondes sur mes deux procédures, soit 33% de gain.
Au lieu de le faire ligne par ligne, je l'ai fait par paquet de 100 lignes.

Cordialement.
Artemus24.
@+

[Séb.]

a) Je redémarre MySql. Je lance le script qui contient les tests sur les count(*) et je constate que j'ai perdu mes connexions.
--> Lost connection to MySQL server during query
Quelle peut-être la cause de ce problème ?

Le serveur plante ou pas ? Cela peut simplement être un timeout entre le client et le serveur.
Ex. sur MySQL Workbench > Preferences > SQL Editor > MySQL Session

Mais le comportement de mes requêtes semblent me dire que j'ai quelque part un cache.

Il reste un cache sur les index.

Je teste tout ça sur ma config dès que possible.

[escartefigue]

@Artemus24 : attention, le query n° 5 n'est absolument pas utilisable pour connaitre le nombre de lignes.
En effet, la valeur maximale de l'auto_increment ne tient évidemment pas compte des valeurs supprimées par DELETE, des valeurs non commitées (et donc jamais insérées), d'un pas d'incrément différent de 1...

[Artemus24]

Salut à tous.

Le serveur plante ou pas ?

Aucun plantage, aucun message d'anomalie dans le fichier "mysql_error.log".

Cela peut simplement être un timeout entre le client et le serveur. Ex. sur MySQL Workbench > Preferences > SQL Editor > MySQL Session

Je n'utilise pas MySql Workbench, mais MySql Community Server 8.0.33, que je viens d'installer aujourd'hui même. Pour être plus précis, j'utilise mon propre WampServer que j'ai adapté à mes besoins.

Il reste un cache sur les index.

C'est à dire ?

@ Escartefigue : Ce qui m'intéresse ici est d'améliorer les performances de différentes requêtes sous MySql.
J'ai mis le query N°5, juste pour voir le temps qu'il met à s'exécuter.
En aucun cas, c'est une solution pour récupérer le nombre de lignes dans une table.

A titre indicatif, la requête qui prend beaucoup de temps est la suivante :

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
+----------+------------+-----------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                           |
+----------+------------+-----------------------------------------------------------------------------------------------------------------+
|        1 | 3.44589075 | select sql_no_cache medecin, count(patient) as nbre from charniere group by medecin order by nbre desc limit 10 |
+----------+------------+-----------------------------------------------------------------------------------------------------------------+

A la seconde exécution de ce test, j'obtiens le résultat suivant :

Code mysq :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
+----------+------------+-----------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                           |
+----------+------------+-----------------------------------------------------------------------------------------------------------------+
|        1 | 1.98959750 | select sql_no_cache medecin, count(patient) as nbre from charniere group by medecin order by nbre desc limit 10 |
+----------+------------+-----------------------------------------------------------------------------------------------------------------+

Pourquoi je n'obtiens pas la même duration ? Il me semble bien qu'il y a un cache dans MySql.

Cordialement.
Artemus24.
@+

[escartefigue]

L'option SQL_NO_CACHE s'applique aux données, mais pas au choix du chemin d'accès.
Certains SGBD comme SQL Server mettent en cache le chemin d'accès pour ne pas avoir à le recalculer lors de la prochaine exécution de la même requête.
C'est peut-être ce qu'il se passe ici.

Possiblement, l'ajout d'une constante dans la requête permettrait de forcer une réestimation, par exemple

select sql_no_cache medecin, count(patient), 'TOTO' [...]

À tester

[Artemus24]

Salut à tous.

Je n'ai pas à me plaindre car le chargement de mes trois tables est plutôt rapide sur mon ordinateur.
J'ai découpé le chargement en six étapes donnant le temps elaps suivant :
--> 00,15s + 00,25s + 26,39s + 22,87s + 04,46s + 00,11s
--> 54,23s
Soit moins d'un 1 minute. Comparativement, le benchmark indique environ 50 minutes ???
Le temps élaps dépend aussi du type d'ordinateur que vous avez :
--> Processeur AMD Ryzen 5 3550H with Radeon Vega Mobile Gfx, 2100*MHz, 4 cœur(s), 8 processeur(s) logique(s)
--> Mémoire physique (RAM) installée 8,00*Go

Ce premier point me fait dire qu'il faut savoir configurer MySql afin d'obtenir des temps d'exécution correcte.
Et ne pas faire n'importe quoi puis ensuite dire que MySql est de la merde !

Si nous désirons connaitre le temps CPU d'une requête, il faut procéder aux instructions suivantes :
--> flush tables
--> set profiling=1
--> la ou les requête(s)
--> set profiling=0
--> show profiles

Que j'ajoute ou pas dans la requête "sql_no_cache", celui-ci n'a aucun effet sur la duration.
Ce qui me fait dire que MySql a bien supprimé la gestion du cache à partir de la version 8.0.

Je refais le test que j'ai fait précédemment, mais cette fois-ci dans la version MySql 8.0.33.
Je redémarre Wampserver. Cette fois-ci, je ne perds pas la connexion durant l'exécution du test.
A ce sujet, je n'ai pas du tout compris pourquoi dans la version MySql 8.0.32, j'avais ce problème.
Voici le premier résultat :

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
+----------+-------------+----------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration    | Query                                                                                                                      |
+----------+-------------+----------------------------------------------------------------------------------------------------------------------------+
|        1 | 14.66187500 | select sql_no_cache count(*)   as Nbre from `charniere` use index()                                                        |
|        2 |  0.03121900 | select sql_no_cache count(*)   as Nbre from `charniere` use index(primary)                                                 |
|        3 |  0.03579025 | select sql_no_cache count(*)   as Nbre from `charniere` use index(fk_01)                                                   |
|        4 |  0.03111875 | select sql_no_cache count(*)   as Nbre from `charniere` use index(fk_02)                                                   |
|        5 |  0.00349600 | select sql_no_cache max(id)    as Nbre from `charniere`                                                                    |
|        6 |  0.02014775 | select sql_no_cache table_rows as Nbre from information_schema.tables WHERE table_schema='base' and table_name='charniere' |
|        7 |  0.00031775 | select sql_no_cache *                  from `charniere` where id=127                                                       |
+----------+-------------+----------------------------------------------------------------------------------------------------------------------------+

Comme vous le constatez, 14 secondes pour la première requête qui s'exécute juste après le redémarrage de WampServer.
J'ai refait le premier test et j'obtiens à chaque fois la même duration.

Je lance à nouveau le même script :

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
+----------+------------+----------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                                      |
+----------+------------+----------------------------------------------------------------------------------------------------------------------------+
|        1 | 0.03408875 | select sql_no_cache count(*)   as Nbre from `charniere` use index()                                                        |
|        2 | 0.03144600 | select sql_no_cache count(*)   as Nbre from `charniere` use index(primary)                                                 |
|        3 | 0.03368050 | select sql_no_cache count(*)   as Nbre from `charniere` use index(fk_01)                                                   |
|        4 | 0.03013275 | select sql_no_cache count(*)   as Nbre from `charniere` use index(fk_02)                                                   |
|        5 | 0.00028400 | select sql_no_cache max(id)    as Nbre from `charniere`                                                                    |
|        6 | 0.00327775 | select sql_no_cache table_rows as Nbre from information_schema.tables WHERE table_schema='base' and table_name='charniere' |
|        7 | 0.00022975 | select sql_no_cache *                  from `charniere` where id=127                                                       |
+----------+------------+----------------------------------------------------------------------------------------------------------------------------+

La duration est similaire aux autres tests que j'ai fait dans la version MySql 8.0.32.

Si je relance encore le même script, j'obtiens des durations similaires.
Pourquoi le premier query de la première exécution donne un résultat de 14 secondes ?
Tout se passe comme si MySql devait initialiser quelque chose lors de la première exécution d'une requête juste après son redémarrage.

L'option SQL_NO_CACHE s'applique aux données, mais pas au choix du chemin d'accès.

L'option "sql_no_cache" sert à dire à MySql de ne pas prendre dans le cache le résultat qui est stocké, si celui-ci existe.
MySql doit exécuter la requête comme si c'était la première fois.

Le résultat de la requête est stockée dans le cache à partir du calcul du code HASH basé sur la requête.
Si la requête vient à être modifié, le code HASH sera différent.
L'astuce est de modifier la requête afin d'avoir un nouveau code HASH et ainsi MySql ne va pas récupérer le résultat dans le cache puisque celui-ci n'existe pas.
Sauf qu'à la deuxième exécution, le résultat a déjà été stocké dans le cache et MySql va le récupérer.

Oui, mais MySql ne gère plus le cache puisque celui-ci à disparu avec la version MySql 8.0.

J'interverti le premier query avec le deuxième et je relance Wampserver. Voici le résultat :

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
+----------+-------------+----------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration    | Query                                                                                                                      |
+----------+-------------+----------------------------------------------------------------------------------------------------------------------------+
|        1 | 14.55918675 | select sql_no_cache count(*)   as Nbre from `charniere` use index(primary)                                                 |
|        2 |  0.03092325 | select sql_no_cache count(*)   as Nbre from `charniere` use index()                                                        |
|        3 |  0.03141325 | select sql_no_cache count(*)   as Nbre from `charniere` use index(fk_01)                                                   |
|        4 |  0.03140825 | select sql_no_cache count(*)   as Nbre from `charniere` use index(fk_02)                                                   |
|        5 |  0.00359775 | select sql_no_cache max(id)    as Nbre from `charniere`                                                                    |
|        6 |  0.01991375 | select sql_no_cache table_rows as Nbre from information_schema.tables WHERE table_schema='base' and table_name='charniere' |
|        7 |  0.00028625 | select sql_no_cache *                  from `charniere` where id=127                                                       |
+----------+-------------+----------------------------------------------------------------------------------------------------------------------------+

Le premier query dure aussi 14 secondes et je ne me l'explique. Ensuite, la duration est correcte.

Pour les autres tests, j'ai modifié la requête en modifiant juste le nom de la colonne "Nbre". La duration est similaire à ce que j'obtiens.
Il n'y a que le premier query juste après le redémarrage de WampServer qui provoque une duration aberrante.

Un temps d'exécution de moins de 1/20^ième de seconde me semble tout à fait correcte.

Pour ce qui est du chemin, un plan d'exécution doit être envisagé.

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
--------------
explain select sql_no_cache count(*) as Nbre from `charniere`
--------------
 
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+--------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key   | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | charniere | NULL       | index | NULL          | FK_01 | 4       | NULL | 951212 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+--------+----------+-------------+
--------------
select sql_no_cache count(*) as Nbre from `charniere`
--------------
 
+---------+
| Nbre    |
+---------+
| 1000000 |
+---------+
Début : 14:21:43,77
Fin ..: 14:21:43,86
Durée : 00:00:00,09

Pour calculer le nombre de lignes, il va utiliser l'index "FK_01".
Il est à remarquer que le nombre de lignes de la colonne "rows" est faux et correspond à ce qui est stocké dans la colonne "table_rows" de la table "information_schema.tables".

Pour ce premier test basique sur le count(*), je n'ai rien d'autre à dire.

Cordialement.
Artemus24.
@+

[Artemus24]

Salut à tous.

J'ai essayé d'améliorer les performances en adaptant mon fichier "my.ini".
Je ne comprends pourquoi quand je lance le chargement de mes tables, je n'ai pas le même temps.
J'ai des différences qui pourrait s'expliquer si j'avais en tâche de fond des traitements en cours d'exécution.
La différence est minime, de l'ordre des 15 secondes.

Je teste la requête la plus longue de ce benchmark.

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
--------------
START TRANSACTION
--------------
 
--------------
flush tables
--------------
 
--------------
set profiling=1
--------------
 
--------------
select medecin, count(patient) as nbre from charniere group by medecin order by nbre desc limit 10
--------------
 
+---------+------+
| medecin | nbre |
+---------+------+
|    1789 |  322 |
|    3684 |  308 |
|     743 |  308 |
|    3909 |  302 |
|    1202 |  301 |
|     182 |  301 |
|    3214 |  301 |
|    1565 |  301 |
|    3998 |  300 |
|    1221 |  300 |
+---------+------+
--------------
set profiling=0
--------------
 
--------------
SHOW PROFILES
--------------
 
+----------+------------+----------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                              |
+----------+------------+----------------------------------------------------------------------------------------------------+
|        1 | 1.64489650 | select medecin, count(patient) as nbre from charniere group by medecin order by nbre desc limit 10 |
+----------+------------+----------------------------------------------------------------------------------------------------+
--------------
COMMIT
--------------
 
Début : 16:11:00,84
Fin ..: 16:11:02,54
Durée : 00:00:01,70

Je me rends compte avec cette duration, que MySql ne va pas chercher en cache le résultat du dernier lancement que j'ai fait.
Cela me rassure car j'ai bien le temps disons moyen de l'exécution de cette requête.
Dans le cas contraire, la duration sera de l'ordre du millième de seconde, juste pour lire ce qui est en cache.

Cette requête est basique puisqu'elle se fait sur une seule table.
Je pense qu'il impossible d'améliorer la performance de cette requête.

Si vous avez des suggestions à me faire concernant l'amélioration des performances, je suis preneur.

Cordialement.
Artemus24.
@+

[escartefigue]

Un index sur la colonne medecin serait couvrant et permettrait donc de s'épargner la lecture des pages data pour ce comptage.
Pour autant, créer cet index pour ce seul besoin serait probablement une erreur, car même s'il améliorait à la marge les perfs du comptage, il dégraderait les accès en mise à jour (delete, insert et certains update) à cause de la maintenance supplémentaire de son chemin d'accès.

[Artemus24]

Salut Escartefigue.

Dans le benchmark, il y a déjà des index sur les colonnes medecin et patient :

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
--------------
show create table `charniere`
--------------
 
+-----------+-------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                    |
+-----------+-------------------------------------------------------------------------------------------------+
| charniere | CREATE TABLE `charniere` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `medecin` int unsigned NOT NULL,
  `patient` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_01` (`medecin`),
  KEY `FK_02` (`patient`),
  CONSTRAINT `FK_01` FOREIGN KEY (`medecin`) REFERENCES `medecin` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_02` FOREIGN KEY (`patient`) REFERENCES `patient` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=COMPRESSED |
+-----------+-------------------------------------------------------------------------------------------------+
--------------
describe `charniere`
--------------
 
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int unsigned | NO   | PRI | NULL    | auto_increment |
| medecin | int unsigned | NO   | MUL | NULL    |                |
| patient | int unsigned | NO   | MUL | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
--------------
explain select medecin, count(patient) as nbre from charniere group by medecin order by nbre desc limit 10
--------------
 
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+--------+----------+---------------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key   | key_len | ref  | rows   | filtered | Extra                           |
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+--------+----------+---------------------------------+
|  1 | SIMPLE      | charniere | NULL       | index | FK_01         | FK_01 | 4       | NULL | 927499 |   100.00 | Using temporary; Using filesort |
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+--------+----------+---------------------------------+
Début : 15:55:13,85
Fin ..: 15:55:13,90
Durée : 00:00:00,05

Cordialement.
Artemus24.
@+

[Artemus24]

Salut à tous.

Je reprends le fil de cette discussion après avoir résolu l'erreur "lost connection to mysql server during query" dont j'étais à l'origine.
Je gagne environ 50% du temps d'exécution du chargement d'une table InnoDB en désactivant "redo log".
Je mets 55 secondes et se je le désactive, je ne mets plus que 22 secondes.

Je n'ai pas pu améliorer la requête ci-dessus sur le count qui mets environ 2 secondes.

D'où l'importance de bien configurer son fichier "my.ini", et de savoir gérer les performances en testant les variantes des requêtes.
Je constate aussi que la gestion des caches sous mysql a été désactivée.

Il serait intéressant de comparer mes résultats sous PostgreSQL ou sous SQL Server.

Cordialement.
Artemus24.
@+

[escartefigue]

Bonjour Artemus24

Je suis un peu perplexe, le titre de la discussion est "performance sur un count(*) et la première question est la suivante :

Salut à tous.

Comment obtenir le nombre de lignes dans une table du SGBDR MySql le plus rapidement possible ?

C'est pourquoi j'avais répondu :

@Artemus24 : attention, le query n° 5 n'est absolument pas utilisable pour connaitre le nombre de lignes.
En effet, la valeur maximale de l'auto_increment ne tient évidemment pas compte des valeurs supprimées par DELETE, des valeurs non commitées (et donc jamais insérées), d'un pas d'incrément différent de 1...

Si tu le souhaites, je peux modifier le titre de la discussion s'il n'est pas adéquat.


Ensuite, par rapport à ceci :

Je gagne environ 50% du temps d'exécution du chargement d'une table InnoDB en désactivant "redo log".

Y a -t-il d'autres transaction sur la DB qui s'exécutent en parallèle du comptage ?
En effet, la documentation de la "redo log" n'est pas très claire, elle explique bien qu'elle est utilisée pour un redémarrage en cas de crash, mais sans préciser si les actions en lecture seule sont également enregistrées dans ce journal.
Je suppose que seules les transactions de MàJ y sont tracées (phrase During normal operations, the redo log encodes requests to change table data that result from SQL statements or low-level API calls. de la doc.
Auquel cas, la désactivation de cette log ne devrait rien changer sur une requête en lecture seule.
À creuser.

[Artemus24]

Salut Escartefigue.

Si tu le souhaites, je peux modifier le titre de la discussion s'il n'est pas adéquat.

Oui, tu peux le changer si cela ne te convient pas.

Séb. a parfaitement répondu à la question car je me demandais s'il existait d'autres approches.
J'ai en effet repris le benchmark pour créer cette discussion et améliorer les temps d'exécution.
Avec le SGBDR MySql bien configuré, je suis arrivé à obtenir des temps d'exécution acceptables.

Je n'ai pas pu résoudre la valeur erronée des statistiques données par la table "information_schema.tables".
Par le count(*), j'obtiens bien mon million de lignes tandis que la valeur de la statistique reste approximative.

Le but de cet exercice est de tenter d'améliorer le paramétrage du fichier "My.ini" afin d'avoir des temps d'exécution acceptables.
Je dis acceptable car je suis sur un simple ordinateur et non sur un serveur dédié partagé entre plusieurs utilisateurs.

Y a -t-il d'autres transaction sur la DB qui s'exécutent en parallèle du comptage ?

Non, juste mon script et rien d'autre. C'est d'ailleurs le cas du benchmark fait par Gugelhupf.
J'obtiens des résultats comparable à ceux de PostgreSQL fait à l'époque.

Auquel cas, la désactivation de cette log ne devrait rien changer sur une requête en lecture seule.

Cela dépend aussi du mode de transaction qui est utilisé. Dans mon cas, je suis en :

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
transaction-isolation = SERIALIZABLE
transaction-read-only = OFF

Je ne suis pas dans le cas de la récupération d'un incident mais bien dans celui du chargement d'une ou plusieurs table.

L'astuce que j'ai utilisée, consiste à désactiver les écritures dans le journal afin d'améliorer les temps d'exécution du chargement des tables.
Voici le commentaire dans le lien que tu as donné :

On Unix and Unix-like operating systems, the performance impact is typically minor, assuming there is not a sustained high rate of updates. On Windows, the performance impact is typically a bit higher, assuming the same.

Je ne sais pas pour linux, mais pour Windows, ce n'est pas du tout négligeable car c'est plus du double du temps élaps.
Si la journalisation est désactivée, le chargement de la table "charniere" mets environ 22 secondes tandis que si elle est activée, cela prend 50 secondes.

This functionality is intended for loading data into a new MySQL instance. Disabling redo logging speeds up data loading by avoiding redo log writes and doublewrite buffering.

Il est dit de le faire au démarrage d'une nouvelle instance comme dans le cas de la restauration (ou migration) d'une base de données.
Quand le chargement sera terminé, et qu'il n'y aura point eu de problèmes d'intégrités alors j'aurai un point de sauvegarde valide.
Ce qui se passe durant le chargement n'a aucun intérêt pour la journalisation, sauf à perdre du temps pour rien.

En quelque sorte, c'est une réponse à la question du temps de chargement où MySql mettait des heures.
Pour mon test, j'ai utilisé une procédure stockée, mais cela peut se faire aussi avec le dump de la commande "mysqldump".

Pour désactiver le "redo log", mettre en début de script : "ALTER INSTANCE DISABLE INNODB REDO_LOG;".
Puis en fin de script, pour le réactiver, faire : "ALTER INSTANCE ENABLE INNODB REDO_LOG;".

Pour ce qui est de la performances, les requêtes sont en lectures seules et l'impact est négligeable.
J'ai un autre outils, mysqlslap qui permet d'émuler une requête partagée entre plusieurs utilisateurs. Les temps ne sont plus du tout les mêmes.

Cordialement.
Artemus24.
@+

[escartefigue]

Bonjour,

Je n'ai pas pu résoudre la valeur erronée des statistiques données par la table "information_schema.tables".
Par le count(*), j'obtiens bien mon million de lignes tandis que la valeur de la statistique reste approximative.

Le décalage entre les statistiques et les valeurs réelles n'est pas une particularité de MySQL.
Les statistiques ont pour vocation de donner des valeurs suffisamment proches de la réalité pour que les stratégies d'accès choisies par l'optimiseur soient efficientes. On n'a pas besoin des valeurs exactes dans ce contexte. Que la table contienne 10 millions ou 11 millions de lignes ne change le plus souvent pas la stratégie d'accès. Et ça ne concerne évidemment pas que les cardinalités, mais aussi les autres valeurs statistiques (valeurs extrêmes des colonnes, cluster ratio, facteur de filtrage...).
Toutefois, dans certains cas, les stats réelles influent l'optimiseur (ex : cas des plus fortes valeurs de clefs quand les insertions sont nombreuses et que ces valeurs sont incrémentales ou décrémentales), c'est pourquoi, certains SGBD choisissent de mettre à jour les stats au fil de l'eau (pas en temps réel, ce serait trop coûteux, mais toutes les n minutes par exemple).

[Artemus24]

Salut Escartefigue.

Le décalage entre les statistiques et les valeurs réelles n'est pas une particularité de MySQL.

Tu retrouves le même problème sous Microsoft SQL Server. C'est bon à savoir.

Je n'ai plus de warning au démarrage de mon MySql et j'ai des temps tout à fait acceptable. Les échanges entre le client et le serveur se font en "ssl".

Je suis en mesure d'améliorer les requêtes soit dans leur écriture, soit en ajoutant des index, soit en modifiant la stratégie après avoir consulté le plan d'exécution, soit en modifiant la modélisation de la base de données.

A l'inverse de ce que dit SQLPRO, je ne trouve pas Mysql aussi merdique qu'il le prétend.
Je ne sais pas si les autres SGBDR ont le même problème mais je trouve que le paramétrage de MySql est plutôt complexe.
Quand à ses performances, ils sont comparables à d'autres SGBDR.

Quand au test sur le chargement, MySql met environ 45 secondes alors que le benchmark met 50 minutes.

Cordialement.
Artemus24.
@+