Requete sur 2.5 milliards de lignes trop lente

**pmithrandir** · 30/09/2015, 09h50

Bonjour,

Je travaille sur un système de sauvegarde de toutes les sessions utilisateurs de mon entreprise. (raisons légales)
Ce système est composé d'une API PHP qui requête une base de donnée Mysql.

Nous avons 3 tables :
Users : id (int), login (varchar)
Sessions_IPV4 : id (BigInt), start_date (int), end_date (int) user_login_id (int), ip (binary 4)
Sessions_IPV6 : id (BigInt), start_date (int), end_date (int) user_login_id (int), ip (binary 16)

Index présents :
Users :
primary key
unique key sur login.

Session IPV4 :
primary key
start_date
end_date
user_login_id
ip

Nos requêtes sont en général assez rapide, sauf une.

Code :

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

1
2
3
4
5
6
 
SELECT COUNT(*)
FROM session_IPV4 s
INNER JOIN users u ON s.user_login_id = u.id
WHERE u.login = 'unlogin'
AND end_date <= '144400000'

Sachant que celle ci se fait en quelques millisecondes :

Code :

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

1
2
3
4
5
 
SELECT COUNT(*)
FROM session_IPV4 s
INNER JOIN users u ON s.user_login_id = u.id
WHERE u.login = 'unlogin'

et qu'elle renvoie entre 50 et 2000 élements.

Quand on fait un explain, on voit que les index utilisés sont user.primary_key et session_IPV4.user_login_id

Les temps sont proportionnels au nombre de données reçues :
70 élements : 1.8 secondes
200 élements : 3-5 secondes
200 000 élements : time out à 20 minutes.

La base est dans un état figé aujourd'hui, elle sera en écriture presque constante en prod.
La table user fait 10 millions d'entrées.
La table sessions V4 fait 2.5 milliards d'entrée.

Avez-vous une idée de ce qui pourrait clocher ? Je suis habituellement pas mauvais pour trouver ce genre de truc, mais la ca me semble sacrément optimisé déjà. On s'y est mis a 3 hier pour aller plus loin et on a réussi en passant par une table temporaire intermédiaire à résoudre le problème, mais je ne trouve pas ca super élégant.

Merci,
Pierre

**escartefigue** · 30/09/2015, 10h10

Bonjour,

Vos identifiants de jointure ne sont pas de même format, (bigint d'un coté et int de l'autre), vérifiez votre chemin d'accès mais il est probable que les index ne soient pas utilisés pour cette raison.
De plus, pourquoi avoir déclaré des dates dans un format int

Dans la table user, il n'y a pas d'index sur l'ID ? (vous n'avez rien mentionné devant primary key je suppose que c'est un oubli et que l'id est primary key ?)

Dans la table session, votre index n'est pas éligible pour votre requete puisque il commence par start_date que vous n'utilisez pas
il faut donc créer un autre index avec uniquement user_login_id et end_date

**SQLpro** · 30/09/2015, 10h54

En sus des remarques d'escartefigue, je me permet de vous signaler qu'avec une telle quantité de lignes et surtout si cela est destiné à s'accroitre, MySQL n'est certainement pas le SGBDR le plus apte à obtenir rapidement les réponses aux requêtes demandées.
En effet, dans ce type de cas de figure, il est intéressant de disposer des vues indexées (SQL Server) ou de vues matérialisées (Oracle) qui pré-calcule les données agrégées de manière automatique.
Dans ce cas, les temps de réponse deviennent au pire de quelques millisecondes, et ne sont souvent pas mesurables !

Si cela vous intéresse, amusez vous à faire le test avec SQL Server version Enterprise (vous pouvez le télécharger et l'essayer gratuitement pendant 180 jours) en reproduisant exactement vos tables et vos données :

Code :

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

1
2
3
4
CREATE TABLE dbo.Users (id int PRIMARY KEY, login varchar(32) NOT NULL UNIQUE);
CREATE TABLE dbo.Sessions_IPV4 (id bigInt PRIMARY KEY, start_date int, end_date int, user_login_id int REFERENCES Users (id), ip binary(4));
CREATE TABLE dbo.Sessions_IPV6 (id bigInt PRIMARY KEY, start_date int, end_date int, user_login_id int REFERENCES Users (id), ip binary(16));
GO

Et en y rajoutant les vues et index suivants :

Code :

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

1
2
3
4
5
6
7
CREATE VIEW dbo.V_USER_SESSION_COUNT_DATE_IPV4
WITH SCHEMABINDING
AS
SELECT user_login_id, end_date, COUNT_BIG(*) AS NOMBRE
FROM   dbo.Sessions_IPV4
GROUP  BY user_login_id, end_date;
GO

Code :

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

1
2
CREATE UNIQUE CLUSTERED INDEX XV4 ON dbo.V_USER_SESSION_COUNT_DATE_IPV4 (user_login_id, end_date);
GO

Testez alors votre requête sans tenir compte de la vue :

Code :

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

1
2
3
4
5
6
SELECT COUNT(*)
FROM   dbo.Sessions_IPV4 s
       INNER JOIN dbo.Users u 
             ON s.user_login_id = u.id
WHERE  u.login = 'unlogin'
  AND  end_date <= '144400000';

Je pense que vous serez surpris !

A +

**aieeeuuuuu** · 30/09/2015, 11h53

et puisque end_date est de type INT (je me pose la même question qu'escartefigue), enlevez les guillemets dans votre filtre :

Code :

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

1
2
 
AND end_date <= 144400000

**pmithrandir** · 30/09/2015, 13h04

Bonjour,

session_IPV4.user_login_id et user.id sont tous 2 de type int... et cet index est utilisé d'après le explain.

Index présents :
Users :
primary key
unique key sur login.

Session IPV4 :
primary key
start_date
end_date
user_login_id
ip

Chaque ligne représente un index différent. Nous avons donc 4 index + une clef primaire(bien évidement sur id) sur chaque table).

Nous n'avons pas accès au format de données timestamp sur notre ORM, seul DateTime. En définissant nos colonnes en entier, nous gagnons 4 byte par valeur, 8 par ligne. Environ 40 gigas + les index. (sur une base qui frise les 450 GB a terme quand IPV6 sera rempli).

Ce qui me parait bizarre, c'est que d'après ma compréhension des index, le but est de réduire le nombre de donnée à traiter au maximum avec un index, puis de traiter les données restantes de manière classique.
Ici, nous réduisons en quelques millisecondes de 2.5 milliards de lignes à 70 éléments. Puis pour appliquer le filtre sur ces 70 éléments, il lui faut 1.75 secondes. n'importe quel script débile fait mieux.

Si nous décomposons l'opération en plusieurs étapes, pas de problème :
1 : création d'une table temporaire depuis cette requête :

Code :

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

1
2
3
4
SELECT s.end_date
FROM session_IPV4 s
INNER JOIN users u ON s.user_login_id = u.id
WHERE u.login = 'unlogin'

Puis

Code :

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

1
2
3
4
 
SELECT COUNT(*)
FROM ma_table_temp
WHERE end_date<144400000

Les deux opérations se font en quelques millisecondes.

**escartefigue** · 30/09/2015, 14h39

A vérifier par un explain, mais il est très probable que votre requete ...

Code :

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

1
2
3
SELECT COUNT(*)
FROM ma_table_temp
WHERE end_date<144400000

...fasse un table scan vu que vous n'avez aucun index commençant par la colonne end_date
Faire un table scan de quelques milliards de lignes, peut prendre quelques secondes ou minutes, mais pas 20 minutes

Le problème et que, quand vous faites une jointure, vous faite le produit cartésien entre 2 tables à très forte volumétrie, d'où la différence de temps

**pmithrandir** · 30/09/2015, 14h48

Hum, c'est le contraire.

La table temporaire + la requête = 0.5 seconde en tout.

La jointure est rapide(vu qu'elle se fait en quelques millisecondes)
c'est jointure + end_date qui déconne...

**escartefigue** · 30/09/2015, 14h55

Envoyé par pmithrandir

Hum, c'est le contraire.

La table temporaire + la requête = 0.5 seconde en tout.

La jointure est rapide(vu qu'elle se fait en quelques millisecondes)
c'est jointure + end_date qui déconne...

C'est bien de la jointure avec end date dont je parle puisque c'est CA votre problème !
Encore une fois, créez vous un index comme je l'ai indiqué, ca devrait déjà beaucoup changer la donne (sauf si votre critère date n'est pas filtrant bien sur)

**pmithrandir** · 30/09/2015, 15h07

Hum, c'est assez délicat.
Ca voudrait dire créer 2 index :
- user_login_id, start_date
- user_login_id, end_date.

Ca va me prendre encore quelques dizaines de giga ca... et ralentir mes insertions.

Pourquoi l'index sur user_login_id ne suffit pas ? (d un point de vue théorique). Je ne comprends pas comment mysql n'arrive pas à évaluer les 70 éléments en qq millisecondes.

Je vais encore réfléchir et attendre un peu, j'en ai pour 4-5 heures par index de création. Je vais pas les tester tout de suite.(surtout en ne comprenant pas le problème)

Pierre

**escartefigue** · 30/09/2015, 15h17

Envoyé par pmithrandir

Hum, c'est assez délicat.
Ca voudrait dire créer 2 index :
- user_login_id, start_date
- user_login_id, end_date.

Ca va me prendre encore quelques dizaines de giga ca... et ralentir mes insertions.

En effet, sauf si l'index existant (start_date, end_date, user_login_id, ip) peut être modifié pour mettre Start_date après end_date et user_login_id, auquel cas cet index suffira
A vous de faire l'étude d'impact pour le vérifier

Envoyé par pmithrandir

Pourquoi l'index sur user_login_id ne suffit pas ? (d un point de vue théorique).

Parceque votre filtrage WHERE porte sur le login ET la end_date
Pour que votre requete soit "sargable" (indexable) il faut qu'un index contienne ces 2 colonnes et que ce soient les 2 premières colonnes de cet index.
L'index correspondant à votre primary key de Session_IPV4 n'est pas eligible car il nécessite de connaitre aussi la start_date

**pmithrandir** · 30/09/2015, 16h09

Bonjour,

Pourtant, mon explain me donne ca :

Code :

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

1
2
3
 
1	SIMPLE	u	const	PRIMARY,UNIQ_48CA3048AA08CB10	UNIQ_48CA3048AA08CB10	194	const	1	Using index
1	SIMPLE	s	ref	idx_end_date,IDX_233D08B5BC3F045D	IDX_233D08B5BC3F045D	8	const	4	Using where

Il me semble que l'index est bien utilisé. (IDX_233 est le nom barbare de mon index sur la colonne user_login_id).

**escartefigue** · 30/09/2015, 16h34

Envoyé par pmithrandir

Chaque ligne représente un index différent. Nous avons donc 4 index + une clef primaire(bien évidement sur id) sur chaque table).

Désolé j'avais loupé cette phrase dans votre réponse plus haut

Vous avez donc un index contenant seulement end_date et un autre contenant seulement user_login_id
Vous pouvez altérer l'index sur user_login_id en lui ajoutant end_date (ASC) ce qui permettra de balayer cet index en se positionnant sur user_login_id et jusqu'à concurrence de la end_date
(puisque votre where utilise l'opérateur <=).

**pmithrandir** · 30/09/2015, 17h25

Bonjour,

Si je pense que ca fonctionnera très bien, je vais donc devoir dupliquer mon index.

un pour user_login_id, start_date,
et un autre pour user_login_id, end_date.

Il faut que je vois quel est l'impact en terme de rapidité et de stockage.(cet index met en général 8-12 heures a se créer, il doit pas être petit).

Merci,
Pierre

**escartefigue** · 30/09/2015, 19h49

Du coup, vu que tous vos index sont mono-colonnes, je m'interroge sur le besoin fonctionnel pour lequel tous ces index ont été créés :

start_date
end_date
user_login_id
ip

Y a -t- il vraiment un intérêt à rechercher sur la start_date seule, ou la end_date seule par exemple ?
Il y a peut être des gains potentiels de ce coté là

**Artemus24** · 01/10/2015, 10h23

salut à tous.

Quel est la version MySql que vous utilisez ?
Quel est le moteur que vous utilisez (MyIsam ou InnoDB) ?

Envoyé par escartefigue

Vos identifiants de jointure ne sont pas de même format, (bigint d'un coté et int de l'autre),

N'aurais-tu pas besoin d'une paire de lunette, Escartefigue ?

Voici les identifiants de la jointure :

Code :

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

ON s.user_login_id = u.id

Et voici le descriptif des deux tables. Je mets en rouge les deux colonnes en questions :

Code :

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

1
2
3
Users : id (int), login (varchar)
Sessions_IPV4 : id (BigInt), start_date (int), end_date (int) user_login_id (int), ip (binary 4)
Sessions_IPV6 : id (BigInt), start_date (int), end_date (int) user_login_id (int), ip (binary 16)

Elles sont bien dans le même type !

Envoyé par escartefigue

De plus, pourquoi avoir déclaré des dates dans un format int

Envoyé par pmithrandir

Nous n'avons pas accès au format de données timestamp sur notre ORM, seul DateTime.

Si vous soulevez la question du TIMESTAMP, c'est que vous avez trouvé un intérêt à cela ? Peut-on savoir lequel ?
Je vous conseille de ne pas utiliser 'datetime', mais séparer 'date' et 'time' en deux colonnes bien distinctes.

Si vous venez à modifier le type 'date' en type 'int', certaines vérifications ne seront pas actives.
En conséquence, la variable 'sql_mode' n'aura pas d'impact (ou de vérification) sur votre saisie :
--> ALLOW_INVALID_DATES : pas de dates invalides, comme par exemple '2004-04-31'.
--> NO_ZERO_IN_DATE : pas de zéro dans une date : '0000-00-00' sera signalée.
Donc, ce n'est pas une bonne idée de procéder ainsi !

Envoyé par pmithrandir

En définissant nos colonnes en entier, nous gagnons 4 byte par valeur, 8 par ligne. Environ 40 gigas + les index. (sur une base qui frise les 450 GB a terme quand IPV6 sera rempli).

Es-ce que cette astuce est faite pour gagner de la place ?
Si la réponse est OUI, et bien compressez vos données ! Il suffit de mettre dans la table :

Code :

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

ROW_FORMAT=COMPRESSED;

Et vous gagnerez bien plus que quelques octets.

Je reprends votre requête qui pose problème :

Code :

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

1
2
3
4
5
SELECT COUNT(*)
FROM session_IPV4 s
INNER JOIN users u ON s.user_login_id = u.id
WHERE u.login = 'unlogin'
AND end_date <= '144400000'

Il y a plusieurs points que ne vont pas.

1) pourquoi avoir scinder en deux, vos tables session ?
Ne serait-il pas plus judicieux de créer une seule table en ajoutant une colonne contenant le type de 'IP' ?
Peut-être à cause de la volumétrie, non ?

2) Pourquoi n'avez-vous pas créé une clef étrangère entre la table 'users' et la table 'session_idv4' ?

3) j'ai pris l'habitude de ne pas faire un '=' sur une chaîne de caractères, mais un 'like', même s'il n'y a pas de '%'. Bon, c'est juste un détail qui n'a pas beaucoup d'importance.

4) pour faire un encadrement de date, il faut bien utiliser :

Code :

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

end_date between '2014-03-31' and '2015-03-31'

Cela permet de réduire le nombre de lignes à balayer.
Je vous déconseille de faire un "end_date <= '2015-10-01'" car il va balayer la table depuis le début de celle-ci.

5) il est tout à fait normal d'avoir une proportion entre le nombre de lignes lus et le temps d'exécution de la requête.
Pour faire un décompte ('count(*)'), MySql est obligé de lire et de compter les lignes qui ont été sélectionnées.

6) Pour améliorer les performances, vous devez utiliser les bons index.
Or d'après ce que vous dites :

Envoyé par pmithrandir

Quand on fait un explain, on voit que les index utilisés sont user.primary_key et session_IPV4.user_login_id

on voit bien que MySql n'utilise pas les bons index.

Dans l'exemple après, dont le test a été fait avec 'MySql 5.7.8RC', j'ai fait en sorte de faire apparaître 'using index' dans la colonne 'extra'.
En espérant que cela va grandement améliorer les performances.

J'ai même créé une seconde requête en décomposant la première.

7) attention de ne pas inverser les colonnes qui sont référencées dans un index.
Vous risquez de ne pas avoir le même résultat dans l'explain.
L'astuce que j'utilise ici, est de mettre toutes les colonnes qui sont utilises soit dans la partie 'select' soit dans la partie 'where'.

Code :

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
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `users`
--------------
 
--------------
CREATE TABLE `users`
(
  `id`      int          NOT NULL AUTO_INCREMENT,
  `login`   varchar(255) NOT NULL,
  PRIMARY KEY              (`id`),
  UNIQUE INDEX `idx_login` (`login`,`id`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `users` (`login`) values
('user01'),('user02')
--------------
 
--------------
select * from users
--------------
 
+----+--------+
| id | login  |
+----+--------+
|  1 | user01 |
|  2 | user02 |
+----+--------+
--------------
DROP TABLE IF EXISTS `session_ipv4`
--------------
 
--------------
CREATE TABLE `session_ipv4`
(
  `id`            bigint        NOT NULL AUTO_INCREMENT,
  `start_date`    date          NOT NULL,
  `end_date`      date          NOT NULL,
  `user_login_id` int           NOT NULL,
  `ip`            varchar(255)  NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `FOREIGN` FOREIGN KEY (`user_login_id`) REFERENCES `users` (`id`) ON DELETE cascade ON UPDATE cascade,
  INDEX `idx_end_date` (`user_login_id`,`end_date`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `session_ipv4` (`start_date`,`end_date`,`user_login_id`,`ip`) values
('2014-04-01', '2014-07-14', 1, '192.168.1.33'),
('2015-03-01', '2015-12-31', 1, '192.168.1.33'),
('2015-01-01', '2015-02-07', 2, '192.168.1.25'),
('2015-03-01', '2015-03-31', 2, '192.168.1.25')
--------------
 
--------------
select * from session_ipv4
--------------
 
+----+------------+------------+---------------+--------------+
| id | start_date | end_date   | user_login_id | ip           |
+----+------------+------------+---------------+--------------+
|  1 | 2014-04-01 | 2014-07-14 |             1 | 192.168.1.33 |
|  2 | 2015-03-01 | 2015-12-31 |             1 | 192.168.1.33 |
|  3 | 2015-01-01 | 2015-02-07 |             2 | 192.168.1.25 |
|  4 | 2015-03-01 | 2015-03-31 |             2 | 192.168.1.25 |
+----+------------+------------+---------------+--------------+
--------------
explain
SELECT COUNT(*)
FROM       session_IPV4 s
INNER JOIN users        u
ON  s.user_login_id = u.id
WHERE (u.login like 'user02')
and   (end_date between '2015-01-01' and '2015-04-01')
--------------
 
+----+-------------+-------+------------+-------+-------------------+--------------+---------+-----------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys     | key          | key_len | ref       | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+-------------------+--------------+---------+-----------+------+----------+--------------------------+
|  1 | SIMPLE      | u     | NULL       | range | PRIMARY,idx_login | idx_login    | 257     | NULL      |    1 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | s     | NULL       | ref   | idx_end_date      | idx_end_date | 4       | base.u.id |    1 |    25.00 | Using where; Using index |
+----+-------------+-------+------------+-------+-------------------+--------------+---------+-----------+------+----------+--------------------------+
--------------
set @id=0
--------------
 
--------------
explain
select id into @id
from users
where login like 'user02'
--------------
 
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | users | NULL       | range | idx_login     | idx_login | 257     | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
--------------
explain
SELECT COUNT(*)
FROM  session_IPV4
WHERE (user_login_id = @id)
and   (end_date between '2015-01-01' and '2015-04-01')
--------------
 
+----+-------------+--------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table        | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | session_IPV4 | NULL       | range | idx_end_date  | idx_end_date | 7       | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+--------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

@+

Requete sur 2.5 milliards de lignes trop lente

Requêtes MySQL

Discussions similaires

Partager

Partager