Tailles différentes (x4 voire plus) pour tables quasi identiques

**jlamazon** · 21/01/2023, 17h35

Bonjour,

J'ai un problème curieux avec mySQL; j'ai des tailles utiles très différentes pour 2 tables ayant quasi la même structure/index et nombre de lignes, les tables sont définies ainsi:

Code :

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

1
2
3
4
5
CREATE TABLE TSENSOR (
DateTime DATETIME NOT NULL,
SensorID TINYINT UNSIGNED,
Value FLOAT,
PRIMARY KEY(DateTime,SensorID));

Code :

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

1
2
3
4
5
CREATE TABLE SENSORS (
DateTime DATETIME NOT NULL,
SensorID SMALLINT UNSIGNED,
Value FLOAT,
PRIMARY KEY(DateTime,SensorID));

La seule difference est SensorID comme TINYINT dans TSENSOR et comme SMALLINT dans SENSORS, seulement 1 octet de plus pour SENSORS normalement.
Les index ont été créés de la même façon:

Code :

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

1
2
3
4
5
CREATE INDEX SensorID ON TSENSOR(SensorID);
CREATE INDEX DateTime ON TSENSOR(DateTime);
 
CREATE INDEX SensorID ON SENSORS(SensorID);
CREATE INDEX DateTime ON SENSORS(DateTime);

Cependant, pour un nombre similaire de lignes, la taille des tables est très différente:

Code :

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

SELECT COUNT(*) FROM TSENSOR;

+----------+
| COUNT(*) |
+----------+
| 3308886 |
+----------+

Code :

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

SELECT COUNT(*) FROM SENSORS;

+----------+
| COUNT(*) |
+----------+
| 3581182 |
+----------+

source /home/pi/mysql/dbsize.sql;
+---------+-----------------+----------------+-----------------+
| Table | Total Size (MB) | Data Size (MB) | Index Size (MB) |
+---------+-----------------+----------------+-----------------+
| SENSORS | 511 | 390 | 121 |
| TSENSOR | 111 | 61 | 50 |

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
dbsize.sql is as follows:
SELECT
TABLE_NAME AS `Table`,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Total Size (MB)`,
ROUND((DATA_LENGTH) / 1024 / 1024) AS `Data Size (MB)`,
ROUND((INDEX_LENGTH) / 1024 / 1024) AS `Index Size (MB)`
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = "t_monitoring"
ORDER BY
(DATA_LENGTH + INDEX_LENGTH)
DESC;

En gros chaque enregistrement dans TSENSOR occupe environ 34 octets alors qu'il en occupe environ 154 dans SENSORS.
Les données ont été dupliquées de TSENSOR dans SENSORS de la façon suivante (je ne pense pas que cela ait de l'importance mais sait-on jamais):

Code :

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

1
2
3
4
5
6
7
INSERT INTO SENSORS (DateTime,SensorID,Value)
SELECT DateTime,1000+SensorID,Value
FROM BSENSOR;
 
INSERT INTO SENSORS (DateTime,SensorID,Value)
SELECT DateTime,SensorID,Value
FROM TSENSOR;

Quelqu'un a une explication?
Jean

**Artemus24** · 22/01/2023, 14h53

Salut jlamazon.

Il y a un problème dans la déclarative de vos index.

Code :

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

1
2
3
4
5
CREATE INDEX SensorID ON TSENSOR(SensorID);
CREATE INDEX DateTime ON TSENSOR(DateTime);
 
CREATE INDEX SensorID ON SENSORS(SensorID);
CREATE INDEX DateTime ON SENSORS(DateTime);

Vous nommez du même nom vos index pour des tables différentes.
Les index de la seconde table ne sont pas créés.
Le nom de chaque objet doit être unique dans votre base de données.

Comme vous utilisez la primary key, à savoir dans cet ordre (DateTime,SensorID), il n'est pas nécessaire de créer l'index (DateTime).
L'index sur (DateTime) est déjà présent dans la Primary Key.

J'ai refait le test que voici :

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
145
146
147
148
149
150
151
152
153
154
155
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `t1sensor`
--------------
 
--------------
CREATE TABLE `t1sensor`
( `SensorID`   TINYINT UNSIGNED NOT NULL,
  `DateTime`   DATETIME         NOT NULL,
  `Value`      FLOAT            NOT NULL,
  PRIMARY KEY(`SensorID`,`DateTime`),
  index `idx1` (`DateTime`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `t1sensor` (`DateTime`,`SensorID`,`Value`) values
  ('2023-01-01 00:00:01', 1, 17.2),('2023-01-01 00:00:02', 2, 15.5),('2023-01-01 00:00:03', 2, 34.7),('2023-01-01 00:00:05', 2, 38.2)
--------------
 
--------------
select * from `t1sensor`
--------------
 
+----------+---------------------+-------+
| SensorID | DateTime            | Value |
+----------+---------------------+-------+
|        1 | 2023-01-01 00:00:01 |  17.2 |
|        2 | 2023-01-01 00:00:02 |  15.5 |
|        2 | 2023-01-01 00:00:03 |  34.7 |
|        2 | 2023-01-01 00:00:05 |  38.2 |
+----------+---------------------+-------+
--------------
DROP TABLE IF EXISTS `t2sensor`
--------------
 
--------------
CREATE TABLE `t2sensor`
( `SensorID`   SMALLINT UNSIGNED NOT NULL,
  `DateTime`   DATETIME          NOT NULL,
  `Value`      FLOAT             NOT NULL,
  PRIMARY KEY(`SensorID`,`DateTime`),
  index `idx2` (`DateTime`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `t2sensor` (`DateTime`,`SensorID`,`Value`) values
  ('2023-01-01 00:00:01', 1, 17.2),('2023-01-01 00:00:02', 2, 15.5),('2023-01-01 00:00:03', 2, 34.7),('2023-01-01 00:00:05', 2, 38.2)
--------------
 
--------------
select * from `t2sensor`
--------------
 
+----------+---------------------+-------+
| SensorID | DateTime            | Value |
+----------+---------------------+-------+
|        1 | 2023-01-01 00:00:01 |  17.2 |
|        2 | 2023-01-01 00:00:02 |  15.5 |
|        2 | 2023-01-01 00:00:03 |  34.7 |
|        2 | 2023-01-01 00:00:05 |  38.2 |
+----------+---------------------+-------+
--------------
select   table_name                                      AS `table`,
         ROUND(((DATA_LENGTH               ) / 1024), 2) AS `Data Size (KB)`,
         ROUND(((INDEX_LENGTH              ) / 1024), 2) AS `Index Size (KB)`,
         ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024), 2) AS `Total Size (KB)`
    from information_schema.tables
   where TABLE_SCHEMA = 'base'
order by (DATA_LENGTH + INDEX_LENGTH) desc
--------------
 
+----------+----------------+-----------------+-----------------+
| table    | Data Size (KB) | Index Size (KB) | Total Size (KB) |
+----------+----------------+-----------------+-----------------+
| t1sensor |           8.00 |            8.00 |           16.00 |
| t2sensor |           8.00 |            8.00 |           16.00 |
+----------+----------------+-----------------+-----------------+
--------------
explain select * from t1sensor where `SensorID` = 2
--------------
 
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1sensor | NULL       | ref  | PRIMARY       | PRIMARY | 1       | const |    3 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
--------------
explain select * from t2sensor where `SensorID` = 2
--------------
 
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t2sensor | NULL       | ref  | PRIMARY       | PRIMARY | 2       | const |    3 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
--------------
explain select * from t1sensor where `DateTime` between'2023-01-01 00:00:00' and '2023-01-01 23:59:59'
--------------
 
+----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1sensor | NULL       | range | idx1          | idx1 | 5       | NULL |    4 |   100.00 | Using index condition |
+----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
--------------
explain select * from t2sensor where `DateTime` between'2023-01-01 00:00:00' and '2023-01-01 23:59:59'
--------------
 
+----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t2sensor | NULL       | range | idx2          | idx2 | 5       | NULL |    4 |   100.00 | Using index condition |
+----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
--------------
explain select * from t1sensor where `DateTime` = '2023-01-01 00:00:02'
--------------
 
+----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1sensor | NULL       | ref  | idx1          | idx1 | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+
--------------
explain select * from t2sensor where `DateTime` = '2023-01-01 00:00:02'
--------------
 
+----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t2sensor | NULL       | ref  | idx2          | idx2 | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Et comme vous pouvez le constater, la volumétrie est pareille.
J'ai aussi testé le passage par les index et c'est conforme à vos souhaits.

Cordialement.
Artemus24.
@+

**jlamazon** · 22/01/2023, 17h12

Merci Artemus24,

je ne savais pas que l'on devait donner un nom unique aux index vu que l'on précisait la table, merci pour l'info :-)
Cela dit, cela n'explique pas tout il me semble, j'ai supprimé tous les index sauf SensorId, DateTime sur la table TSENSOR, voici la DB telle que vue par phpmyadmin:
Nom : tables.GIF
Affichages : 172
Taille : 49,3 Ko

Nom : tables.GIF
Affichages : 172
Taille : 49,3 Ko

Et la structure des tables:
Nom : tables_struct.GIF
Affichages : 185
Taille : 69,5 Ko

Je lance alors la commande SQL suivante pour copier les données de BSENSOR sur SENSOR:

Code :

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

1
2
3
INSERT INTO SENSORS (DateTime,SensorId,Value)
SELECT DateTime,1000+SensorId,Value
FROM BSENSOR

Nom : tables_after_insert1.GIF
Affichages : 175
Taille : 49,2 Ko

Déjà on peut voir que SENSOR est un peu plus gros que BSENSOR (9.5Mo contre 8.5Mo) mais bon pourquoi pas après tout on a théoriquement un octet de plus par enregistrement.
Ensuite je lance alors la commande SQL suivante pour copier les données de TSENSOR sur SENSOR:

Code :

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

1
2
3
INSERT INTO SENSORS (DateTime,SensorId,Value)
SELECT DateTime,SensorId,Value
FROM TSENSOR

Et là j'obtiens dans phpmyadmin:
Nom : tables_after_insert2.GIF
Affichages : 169
Taille : 49,2 Ko

Près de 4 fois plus en taille que prévu et SANS les index SensorId,DateTime !
En rajoutant ces index (et avec un nom unique cette fois):

Code :

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

CREATE INDEX SSensorID ON SENSORS(SensorID);

Code :

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

CREATE INDEX SDateTime ON SENSORS(DateTime);

J'obtiens:
Nom : tables_after_index.GIF
Affichages : 169
Taille : 49,3 Ko

Bref, pas trop différent du résultat avec les index au même nom:

Code :

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

1
2
3
4
5
6
7
8
9
source mysql/dbsize.sql;
+---------+-----------------+----------------+-----------------+
| Table   | Total Size (MB) | Data Size (MB) | Index Size (MB) |
+---------+-----------------+----------------+-----------------+
| SENSORS |             479 |            380 |              99 |
| TSENSOR |             111 |             61 |              50 |
| BSENSOR |               9 |              5 |               4 |
| DEVENT  |               0 |              0 |               0 |
+---------+-----------------+----------------+-----------------+

Au sujet des index, je les ai créées dans le but d'accélérer les recherches sur SensorId et DateTime (indépendamment) mais c'est peut être inutile vu qu'il existe une clé primaire sur DateTime/SensorId ?

Visiblement sur vos tests, avec peu de données, cela est OK mais avec mes 1.5M d'enregistrements, cela semble différent.
J'avoue être un peu perdu dans ces tailles/index :-)
Merci,
Jean

**Artemus24** · 22/01/2023, 18h48

Salut jlamazon.

Je n'ai fait que simuler votre problème.
Dans mon exemple, j'ai exactement le même nombre de lignes dans chaque table.
J'ai fait en sorte de retrouver la même occupation volumétrique.

Vous avez raison, avec peu de lignes, il se peut que l'occupation sur le disque change.

La taille de la ligne dépend de ce que vous y mettez.
Il y a aussi le problème des alignements à la frontière des mots.
Du coup, on perd de l'espace qui sera inocupé si l'on met par exemple, un char(1) entre deux integer.
Un integer occupe quatre octets, soit un mot et un char(1), 1 octet, soit au total 9 octets utiles.
Mais avec les alignements, l'occupation devient 12 octets d'occupation à cause de l'integer qui doit commencer à l'adresse d'un multiple de quatre octets.

Dans la ligne, il y a d'autres données qui sont destinées à la gestion du SGBDR.

Code :

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

1
2
3
INSERT INTO SENSORS (DateTime,SensorId,Value)
SELECT DateTime,1000+SensorId,Value
FROM BSENSOR

Je ne connais pas l'existent de la table "sensors".
Vous désirez insérer de nouvelles lignes sachant que vous modifiez l'identifiant.
Il est fort probable que la page où sera stockée la nouvelle ligne soit splitée.
Ce qui va augmenté la taille de votre table, sans qu'elle soit, au niveau des pages, à 100% remplies.

Reprenez votre table "sensors".
Créez une nouvelle table de nom "sensors_bis", vide et ayant exactement la même structure que "sensors".
Insérer les lignes par recopies en faisant en sorte qu'elles soient exactement triées dans l'ordre de la clef primaire.
Vous n'obtiendrez pas la même taille d'occupation. Elle sera plus petite.

En gros, plus une table est utilisée, plus elle va se désorganiser, et occuper de l'espace sur le disque.

Cordialement
Artemus24.
@+

**jlamazon** · 22/01/2023, 21h49

Hello Artemus24,

Reprenez votre table "sensors".
Créez une nouvelle table de nom "sensors_bis", vide et ayant exactement la même structure que "sensors".
Insérer les lignes par recopies en faisant en sorte qu'elles soient exactement triées dans l'ordre de la clef primaire.
Vous n'obtiendrez pas la même taille d'occupation. Elle sera plus petite.

En gros, plus une table est utilisée, plus elle va se désorganiser, et occuper de l'espace sur le disque.

Désolé il y a un truc louche tout de même:
J'ai créé SENSOR_BIS qui a exactement la même structure que TSENSOR:
CREATE TABLE SENSOR_BIS (
DateTime DATETIME NOT NULL,
SensorID TINYINT UNSIGNED,
Value FLOAT,
PRIMARY KEY(DateTime,SensorID));

Et les index comme pour TSENSOR:
CREATE INDEX SensorID_bis_bis ON SENSOR_BIS(SensorId);
CREATE INDEX DateTime_bis ON SENSOR_BIS(DateTime);

Et j'ai rempli cette table ainsi:
INSERT INTO SENSOR_BIS (DateTime,SensorId,Value) SELECT DateTime,SensorId,Value FROM TSENSOR;

Résultat:

Code :

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

1
2
3
4
5
6
7
8
+------------+-----------------+----------------+-----------------+
| Table      | Total Size (MB) | Data Size (MB) | Index Size (MB) |
+------------+-----------------+----------------+-----------------+
| SENSOR_BIS |             163 |             91 |              72 |
| TSENSOR    |             111 |             61 |              50 |
| BSENSOR    |               9 |              5 |               4 |
| DEVENT     |               0 |              0 |               0 |
+------------+-----------------+----------------+-----------------+

Ben non c'est le contraire: la nouvelle table est plus grosse que celle d'origine pour exactement la même structure de données et données!

Insérer les lignes par recopies en faisant en sorte qu'elles soient exactement triées dans l'ordre de la clef primaire.

Je ne saurais dire si elles sont triées dans l'ordre de la clé primaire mais est-ce la taille d'une table peut dépendre de l'ordre dans lequel les données ont été insérées?
Je n'y comprends rien!

**escartefigue** · 23/01/2023, 09h08

Bonjour,

Quelques remarques préalables

il est préférable de ne pas utiliser des noms réservés SQL comme noms d'objets. Par exemple "value" est un nom reservé, "datetime" aussi. Ces noms contraignent à utiliser des séparateurs dans les requêtes (quote inversée pour MySQL), ce qui est bien peu pratique.
Je suis d'ailleurs supris que vos requêtes passent sans encadrer ces colonnes avec des quotes inversées...
il y a des index redondants : on a ici à la fois une PK sur l'horodatage + l'identifiant du sensor et un index sur chacune des deux colonnes, l'index sur la colonne datetime seule est donc inutile puisque déjà inclus dans l'index implicite de la PK.

Ensuite, l'insertion dans la table SENSOR se fait par lecture de la table BSENSOR sans préciser d'ordre de lecture.
En l'absence de clause ORDER BY, il n'y a aucune garantie de l'ordre dans lequel les données seront restituées, ce faisant, il n'y a aucune garantie d'insérer dans SENSOR selon l'ordre de la PK (c'est à dire datetime + sensorid).
Du coup, il est fort possible que l'insertion provoque une fragmentation dans la table cible : les lignes ne sont pas rangées selon l'ordre cluster.

Ajoutez un clause ORDER BY dans l'insertion comme suit :

Code SQL :

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

1
2
3
4
INSERT INTO SENSORS (DateTime,SensorId,Value)
SELECT DateTime,1000+SensorId,Value
FROM BSENSOR
ORDER BY DateTime, SensorId

Tailles différentes (x4 voire plus) pour tables quasi identiques

Administration MySQL

Vue hybride

Discussions similaires

Partager

Partager