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

Version imprimable

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:

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

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:

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:

SELECT COUNT(*) FROM TSENSOR;
+----------+
| COUNT(*) |
+----------+
| 3308886 |
+----------+
Code:

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:

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:

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

Salut jlamazon.

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

Code:

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:

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.
@+

5 pièce(s) jointe(s)

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:
Pièce jointe 632932
Et la structure des tables:
Pièce jointe 632933
Je lance alors la commande SQL suivante pour copier les données de BSENSOR sur SENSOR:
Code:

1 2 3 INSERT INTO SENSORS (DateTime,SensorId,Value) SELECT DateTime,1000+SensorId,Value FROM BSENSOR
Pièce jointe 632936
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:

1 2 3 INSERT INTO SENSORS (DateTime,SensorId,Value) SELECT DateTime,SensorId,Value FROM TSENSOR
Et là j'obtiens dans phpmyadmin:
Pièce jointe 632934
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:

CREATE INDEX SSensorID ON SENSORS(SensorID);

Code:

CREATE INDEX SDateTime ON SENSORS(DateTime);

J'obtiens:
Pièce jointe 632935

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

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

22/01/2023, 18h48
Artemus24
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:

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.
@+

Hello Artemus24,

Citation:

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:

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!

Citation:

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!

23/01/2023, 09h08
escartefigue
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:

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

1 pièce(s) jointe(s)

Bonjour escartefigue,

merci pour ces bonnes pratiques :-)
J'ai refait un essai en recréant la table SENSOR_BIS avec une structure à l'identique de TSENSOR et en ajoutant les 2 index (merci pour l'info comme quoi un est redondant avec la PK).
Puis inséré les données comme suggéré avec la clause ORDER BY:
INSERT INTO SENSOR_BIS (DateTime,SensorId,Value) SELECT DateTime,SensorId,Value FROM TSENSOR ORDER BY DateTime, SensorId;

Résultat:
Code:

1 2 3 4 5 6 7 8 +------------+-----------------+----------------+-----------------+ | Table | Total Size (MB) | Data Size (MB) | Index Size (MB) | +------------+-----------------+----------------+-----------------+ | SENSOR_BIS | 177 | 99 | 78 | | TSENSOR | 111 | 61 | 50 | | BSENSOR | 9 | 5 | 4 | | DEVENT | 0 | 0 | 0 | +------------+-----------------+----------------+-----------------+
Pas mieux :-( Même pire (177MB vs 163MB)

Edit: je ne sais pas pourquoi myphpadmin affiche un nombre d'enregistrements erroné mais TSENSOR et SENSOR_BIS ont bien à peu près le même nombre (un peu plus pour TSENSOR car cette table est mise à jour en permanence par les capteurs):
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 MariaDB [t_monitoring]> select COUNT(*) FROM SENSOR_BIS; +----------+ | COUNT(*) | +----------+ | 3313941 | +----------+ 1 row in set (4.763 sec) MariaDB [t_monitoring]> select COUNT(*) FROM TSENSOR; +----------+ | COUNT(*) | +----------+ | 3313951 | +----------+ 1 row in set (3.428 sec)

Salut à tous.

Pour recréer une table à l'identique, on procède ainsi :
Code:

1 2 3 4 5 drop table if exists new_table; create table new_table like old_table; insert into `new_table` (<vos colonnes>) select <vos colonnes> from `old_table` order by <les colonnes de la primary key>;
Vous avez encore plus simples :
Code:

1 2 3 drop table if exists new_table; create table new_table AS SELECT <vos colonnes> from `old_table` order by <les colonnes de la primary key>;
Pour visulaliser la taille d'occupation de la table, il y a deux méthodes.
a) en passant sous windows ou linux, vous pouvez consulter le répertoire où se trouve votre base de données.
Chez moi : F:\Wamp\bin\mysql\mysql8.0.29\data\base
Dans ce répertoire, j'ai mes tables qui se termine par le suffixe ".ibd".
La taille est exprimée en Ko.

b) en passant par MySql, comme vous le faite, mais ce n'est qu'une approximation de la taille réelle sur disque.

Etes vous certains de pointer vers les bonnes tables ?

faites ceci :
Code:

1 2 use <votre base de données>; select count(*) from <votre table>;
Cordialement.
Artemus24.
@+

Hello Artemus24,

voilà les résultats:

Code:

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
pi@moon:~ $ mysql -u t_amonitor -p t_monitoring
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6271
Server version: 10.3.36-MariaDB-0+deb10u2 Raspbian 10
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [t_monitoring]> DROP TABLE IF EXISTS SENSOR_BIS;
Query OK, 0 rows affected (0.088 sec)
 
MariaDB [t_monitoring]> CREATE TABLE SENSOR_BIS AS SELECT DateTime,SensorId,Value FROM TSENSOR ORDER BY DateTime,SensorId;
Query OK, 3314354 rows affected (1 min 34.193 sec)
Records: 3314354  Duplicates: 0  Warnings: 0
 
MariaDB [t_monitoring]> source mysql/dbsize.sql;
+------------+-----------------+----------------+-----------------+
| Table      | Total Size (MB) | Data Size (MB) | Index Size (MB) |
+------------+-----------------+----------------+-----------------+
| SENSOR_BIS |             122 |            122 |               0 |
| TSENSOR    |             111 |             61 |              50 |
| BSENSOR    |               9 |              5 |               4 |
| DEVENT     |               0 |              0 |               0 |
+------------+-----------------+----------------+-----------------+
4 rows in set (0.002 sec)
 
MariaDB [t_monitoring]> CREATE INDEX SensorID_bis_bis ON SENSOR_BIS(SensorId);
Query OK, 0 rows affected (30.549 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
MariaDB [t_monitoring]> CREATE INDEX DateTime_bis ON SENSOR_BIS(DateTime);
Query OK, 0 rows affected (30.393 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
MariaDB [t_monitoring]> source mysql/dbsize.sql;
+------------+-----------------+----------------+-----------------+
| Table      | Total Size (MB) | Data Size (MB) | Index Size (MB) |
+------------+-----------------+----------------+-----------------+
| SENSOR_BIS |             229 |            122 |             107 |
| TSENSOR    |             111 |             61 |              50 |
| BSENSOR    |               9 |              5 |               4 |
| DEVENT     |               0 |              0 |               0 |
+------------+-----------------+----------------+-----------------+
4 rows in set (0.002 sec)
 
pi@moon:/media/usb128GB/mysql $ sudo ls -hl t_monitoring/
total 525M
-rw-rw---- 1 mysql mysql  983 Jan 22 16:41 BSENSOR.frm
-rw-rw---- 1 mysql mysql  24M Jan 23 16:32 BSENSOR.ibd
-rw-rw---- 1 mysql mysql   67 Jan 30  2020 db.opt
-rw-rw---- 1 mysql mysql  985 Feb 16  2020 DEVENT.frm
-rw-rw---- 1 mysql mysql 240K Jan 22 14:54 DEVENT.ibd
-rw-rw---- 1 mysql mysql 1.5K Jan 23 16:32 SENSOR_BIS.frm
-rw-rw---- 1 mysql mysql 236M Jan 23 16:32 SENSOR_BIS.ibd
-rw-rw---- 1 mysql mysql 2.0K Jan 30  2020 TSENSOR.frm
-rw-rw---- 1 mysql mysql 264M Jan 23 16:34 TSENSOR.ibd

(J'ai rajouté les index pour pouvoir comparer avec TSENSOR qui a des index).
Au premier abord cela peut sembler surprenant:
TSENSOR.ibd --> 264Mo sur disque, 111Mo d'après mysql
SENSOR_BIS.ibd --> 236Mo sur disque, 229Mo d'après mysql

Mais si on se dit que mysql réserve physiquement plus d'espace disque pour stocker les prochaines données sans devoir redimensionner le fichier .ibd c'est cohérent et l'approximation mysql semble bonne. Peut-on vérifier ça de façon plus fine?
On dirait donc bien que la nouvelle table occupe plus de place que l'ancienne.

Citation:

Etes vous certains de pointer vers les bonnes tables ?

Oui, je fais tout cela depuis une unique connection mysql (mysql -u t_amonitor -p t_monitoring) sur la database t_monitoring.

Salut jlamazon.

Utilisez vous une Raspberry Pi ? Et quel modèle ?
Je pensais que vous étiez sous MySql et non MariaDB.

La taille du fichier sous linux ou sous windows ne correspond pas à l'occupation réelle des données.
Elle comprend, bien sûre, les données, mais aussi de l'espace libre pour les prochaines insertions.
Elle est gérée par un "tablespace", qui se fait automatiquement sous MySql.
Personne ne met jamais son nez dans ces "tablespace" sous MySql à l'inverse des autres SGBDR.

Vous m'avez indiqué avoir 3 313 951 lignes dans votre table. Je vais calculer l'occupation chez moi.
Je charge la table par paquet de 50 lignes et je fais ensuite un COMMIT. Ce qui donne 50 * 66279 = 3 313 950

J'ai compressé le table afin qu'elle occupe moins d'espace. J'obtiens sur disque du fichier "tabone.ibd" une volumétrie de 90 112 Ko.

Voici le résultat de ce test :

Code:

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
Heure Debut : 14:13:26,57
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP PROCEDURE IF EXISTS `remplir`
--------------
 
--------------
CREATE PROCEDURE `remplir`(_z    INTEGER)
BEGIN
  DECLARE _date DATETIME DEFAULT NULL;
  DECLARE _max  INTEGER  DEFAULT 50;
  DECLARE _i1d  INTEGER  DEFAULT NULL;
  DECLARE _i2d  INTEGER  DEFAULT NULL;
  DECLARE _fin  INTEGER  DEFAULT 1;
 
  SET _i1d = 0;
 
  WHILE (_i1d < _z)
  DO
    SET _i2d = 0;
 
    WHILE (_i2d < _max)
    DO
      INSERT INTO `tabone` (`SensorID`,`Datetime`,`Value`) values (_i1d+1, DATE_ADD(now(), INTERVAL _i2d DAY), 123.45);
      SET _i2d = _i2d + 1;
    END WHILE;
 
    COMMIT;
 
    SET _i2d = 0;
    SET _i1d = _i1d + 1;
  END WHILE;
END
--------------
 
--------------
COMMIT
--------------
 
--------------
DROP TABLE IF EXISTS `tabone`
--------------
 
--------------
CREATE TABLE `tabone`
( `SensorID`   INTEGER UNSIGNED NOT NULL,
  `Datetime`   DATETIME         NOT NULL,
  `Value`      FLOAT            NOT NULL,
  PRIMARY KEY (`SensorID`,`Datetime`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
call `remplir` (66279)
--------------
 
--------------
create index `idx1` on `tabone` (`Datetime`)
--------------
 
--------------
describe table `tabone`
--------------
 
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | tabone | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 3309009 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------+
--------------
optimize local table tabone
--------------
 
+-------------+----------+----------+----------+
| Table       | Op       | Msg_type | Msg_text |
+-------------+----------+----------+----------+
| base.tabone | optimize | status   | OK       |
+-------------+----------+----------+----------+
--------------
COMMIT
--------------
 
Heure Fin   : 15:03:47,94

Comme on peut le lire, démarrage à 14:13:26,57 et arrêt à 15:03:47,94.
Soit une durée du chargemment de 50 minutes environ.
La volumétrie sur mon disque est de 90 112 Ko.

Et voici le résultat de MySql :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
--------------
START TRANSACTION
--------------
 
--------------
select *
  from information_schema.tables
 where TABLE_SCHEMA = 'base'
--------------
 
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+-----------------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         | UPDATE_TIME         | CHECK_TIME | TABLE_COLLATION   | CHECKSUM | CREATE_OPTIONS        | TABLE_COMMENT |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+-----------------------+---------------+
| def           | base         | tabone     | BASE TABLE | InnoDB |      10 | Compressed |    3309009 |             16 |    54345728 |               0 |            0 |   3670016 |           NULL | 2023-01-24 15:03:39 | 2023-01-24 15:03:39 | NULL       | latin1_general_ci |     NULL | row_format=COMPRESSED |               |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+-----------------------+---------------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Je constate que le nombre de lignes affiché est faux.

Cordialement.
Artemus24.
@+

Salut Artemus24,

j'utilise un rPI4 (32bits), je ne sais plus trop si c'est mariadb ou mysql que j'ai installé, il me semble avoir les 2 maintenant:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
pi@moon:~ $ dpkg-query -l | grep maria
ii  libmariadb-dev                       1:10.3.36-0+deb10u2                          armhf        MariaDB database development files
ii  libmariadb-dev-compat:armhf          1:10.3.36-0+deb10u2                          armhf        MariaDB Connector/C, compatibility symlinks
ii  libmariadb3:armhf                    1:10.3.36-0+deb10u2                          armhf        MariaDB database client library
ii  mariadb-client-10.3                  1:10.3.36-0+deb10u2                          armhf        MariaDB database client binaries
ii  mariadb-client-core-10.3             1:10.3.36-0+deb10u2                          armhf        MariaDB database core client binaries
ii  mariadb-common                       1:10.3.36-0+deb10u2                          all          MariaDB common metapackage
ii  mariadb-server                       1:10.3.36-0+deb10u2                          all          MariaDB database server (metapackage depending on the latest ver
sion)
ii  mariadb-server-10.3                  1:10.3.36-0+deb10u2                          armhf        MariaDB database server binaries
ii  mariadb-server-core-10.3             1:10.3.36-0+deb10u2                          armhf        MariaDB database core server files
pi@moon:~ $ dpkg-query -l | grep mysql
ii  dbconfig-mysql                       2.0.11+deb10u1                               all          dbconfig-common MySQL/MariaDB support
ii  default-libmysqlclient-dev:armhf     1.0.5                                        armhf        MySQL database development files (metapackage)
ii  libdbd-mysql-perl:armhf              4.050-2                                      armhf        Perl5 database interface to the MariaDB/MySQL database
ii  mysql-common                         5.8+1.0.5                                    all          MySQL database common files, e.g. /etc/mysql/my.cnf
ii  php7.3-mysql                         7.3.31-1~deb10u2                             armhf        MySQL module for PHP

Citation:

Je constate que le nombre de lignes affiché est faux.

OK, mais je ne vois pas trop bien où vous voulez en venir :-)
Il me semble que le script dbsize.sql qui donne:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
source mysql/dbsize.sql;
+------------+-----------------+----------------+-----------------+
| Table      | Total Size (MB) | Data Size (MB) | Index Size (MB) |
+------------+-----------------+----------------+-----------------+
| SENSOR_BIS |             229 |            122 |             107 |
| TSENSOR    |             111 |             61 |              50 |
| BSENSOR    |               9 |              5 |               4 |
| DEVENT     |               0 |              0 |               0 |
+------------+-----------------+----------------+-----------------+
4 rows in set (0.002 sec)
 
select table_name, table_rows, data_length, data_free from information_schema.tables  where TABLE_SCHEMA = 't_monitoring';
+------------+------------+-------------+-----------+
| table_name | table_rows | data_length | data_free |
+------------+------------+-------------+-----------+
| TSENSOR    |    1411850 |    63553536 |   7340032 |
| DEVENT     |       3879 |      147456 |         0 |
| SENSOR_BIS |    3307115 |   127565824 |   2097152 |
| BSENSOR    |     120516 |     4734976 |  12582912 |
+------------+------------+-------------+-----------+
4 rows in set (0.003 sec)

donne bien la taille des données.
Mais pourquoi une même table avec les mêmes données donne des résultats si différents me rend perplexe.
Et surtout est-ce qu'il y a quelque chose que je peu faire pour arranger ça.

Salut jlamazon.
Code:

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 -------------- START TRANSACTION -------------- -------------- select count(*) from `tabone` -------------- +----------+ | count(*) | +----------+ | 100000 | +----------+ -------------- select table_name as `Table`, table_rows as `Rows`, round(DATA_LENGTH / 1024, 0) AS 'Data', round(INDEX_LENGTH / 1024, 0) AS 'Index', round(DATA_FREE / 1024, 0) AS 'Free', round((DATA_LENGTH + INDEX_LENGTH + DATA_FREE) / 1024, 0) AS 'Total Ko', round((DATA_LENGTH + INDEX_LENGTH + DATA_FREE) / 1024 / 1024, 2) AS 'Total Mo' from information_schema.tables where TABLE_SCHEMA = 'base' -------------- +--------+-------+------+-------+------+----------+----------+ | Table | Rows | Data | Index | Free | Total Ko | Total Mo | +--------+-------+------+-------+------+----------+----------+ | tabone | 85800 | 3848 | 776 | 1536 | 6160 | 6.02 | +--------+-------+------+-------+------+----------+----------+ -------------- COMMIT -------------- Appuyez sur une touche pour continuer...
Code:

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 -------------- START TRANSACTION -------------- -------------- SELECT t1.NAME, t1.ROW_FORMAT, t1.PAGE_SIZE, t1.ZIP_PAGE_SIZE, t1.FS_BLOCK_SIZE, t1.FILE_SIZE, t1.ALLOCATED_SIZE, t2.ENGINE, t2.FREE_EXTENTS, t2.EXTENT_SIZE, t2.INITIAL_SIZE, t2.AUTOEXTEND_SIZE, t2.DATA_FREE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES as t1 INNER JOIN INFORMATION_SCHEMA.FILES as t2 ON t2.FILE_ID = t1.SPACE WHERE t1.NAME like '%base%' -------------- +-------------+------------+-----------+---------------+---------------+-----------+----------------+--------+--------------+-------------+--------------+-----------------+-----------+ | NAME | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | ENGINE | FREE_EXTENTS | EXTENT_SIZE | INITIAL_SIZE | AUTOEXTEND_SIZE | DATA_FREE | +-------------+------------+-----------+---------------+---------------+-----------+----------------+--------+--------------+-------------+--------------+-----------------+-----------+ | base/tabone | Compressed | 16384 | 8192 | 65536 | 8388608 | 8388608 | InnoDB | 2 | 1048576 | 57344 | 1048576 | 1572864 | +-------------+------------+-----------+---------------+---------------+-----------+----------------+--------+--------------+-------------+--------------+-----------------+-----------+ -------------- COMMIT -------------- Appuyez sur une touche pour continuer...
Citation:

Envoyé par jlamazon

j'utilise un rPI4 (32bits), je ne sais plus trop si c'est mariadb ou mysql que j'ai installé, il me semble avoir les 2 maintenant:

Je crois que c'est MariaDB qui est installé dans les entrepos quand on demande MySql. Je ne sais plus trop où j'ai lu cela.

Citation:

Envoyé par jlamazon

OK, mais je ne vois pas trop bien où vous voulez en venir :-)

La question que vous vous êtes posée, était de connaitre à partir de MySql, la taille de l'allocation de la table MySql (enfin MariaDB).
J'ai recréé la table en ne mettant cette fois-ci que 100 000 lignes.
La colonne "ROWS" (nombre de lignes) est fausse. Il est indiqué 85 800 lignes alors que le count() donne 100 000 lignes.

Il est nécessaire de faire un count(*) pour connaitre le nombre de lignes, et non se fier à ce qu'indique la base "INFORMATION_SCHEMA".

La volumétrie de "tabone.ibd" sur le disque donne :

Citation:

Taille : 8.00 Mo (8 388 608 octets)
sur Disque : 8.00 Mo (8 388 608 octets)

La volumétrie de la table correspond à la colonne "ALLOCATED_SIZE".
Il y a eu deux extensions (FREE_EXTENTS) et si je ne me trompe pas, chacune de 4Mo puisque le total fait 8Mo.
La taille de la page est de 16Ko.

En regardant la documentation MySql, il y a un tableau qui indique pour la PAGE_SIZE de 16Ko, la taille minimale de l'extension qui est bien de 4Mo.

La colonne "FREE" donne 1536 Ko (1,5Mo) de libre. Cela correspond à la colonne "DATA_FREE" qui donne 1 572 864 octets, c'est-à-dire la même chose.
Je comprends que sur les 4Mo alloué, nous avons déjà consommé 2,5Mo et il nous reste encore 1,5Mo.

Je ne comprends pas la signification de "EXTENT_SIZE" et de AUTOEXTEND_SIZE" qui sont tous les deux à 1Go. Sur le disque, nous avons 8Mo d'alloué et non 1Go.

J'espère que ces quelques renseignement vous permettrons de maitriser la volumétrie sur votre Raspberry Pi 4B, avec bullseye, je suppose.

Citation:

Envoyé par jlamazon

Et surtout est-ce qu'il y a quelque chose que je peu faire pour arranger ça.

Compressez vos données. Voir la déclarative de la table "tabone".

Cordialement.
Artemus24.
@+

24/01/2023, 17h24
jlamazon

Merci pour toutes ces infos Artemus24, je vais faire quelque essais pour voir quelle est ma meilleure option avec les performances en face.
Jean
25/01/2023, 07h57
escartefigue

Bonjour,

Citation:

Envoyé par Artemus24

La colonne "ROWS" (nombre de lignes) est fausse. Il est indiqué 85 800 lignes alors que le count() donne 100 000 lignes.
Il est nécessaire de faire un count(*) pour connaitre le nombre de lignes, et non se fier à ce qu'indique la base "INFORMATION_SCHEMA".

Dans tous les SGBD relationnels, certaines tables du catalogue relationnel ne sont pas mises à jour en temps réel, mais seulement lors de l'activation des statistiques ou en temps différé quand le nombre de mises à jour atteint un certain seuil (par exemple 10% de l'effectif total).
C'est le cas notamment pour l'effectif des tables, d'où l'écart entre effectif réel obtenu par la fonction count() et effectif stocké dans la table INFORMATION_SCHEMA du catalogue.
Selon qu'on a besoin d'une estimation ou d'une information réelle, on s'appuiera sur le catalogue relationnel ou sur un comptage.

Pour forcer la mise à jour les statistiques, il faut effectuer un ANALYZE.

Comme l'optimiseur choisit ses chemins d'accès en fonction des index installés et des statistiques, il est important de veiller à leur mise à jour périodique ;)

Salut Escartefigue.

Ca ne fonctionne pas ! :?
Code:

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 -------------- START TRANSACTION -------------- -------------- select count(*) from `tabone` -------------- +----------+ | count(*) | +----------+ | 100000 | +----------+ -------------- analyze table `tabone` -------------- +-------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------+---------+----------+----------+ | base.tabone | analyze | status | OK | +-------------+---------+----------+----------+ -------------- COMMIT -------------- -------------- select table_name as `Table`, table_rows as `Rows`, round(DATA_LENGTH / 1024, 0) AS 'Data', round(INDEX_LENGTH / 1024, 0) AS 'Index', round(DATA_FREE / 1024, 0) AS 'Free', round((DATA_LENGTH + INDEX_LENGTH + DATA_FREE) / 1024, 0) AS 'Total Ko', round((DATA_LENGTH + INDEX_LENGTH + DATA_FREE) / 1024 / 1024, 2) AS 'Total Mo' from information_schema.tables where TABLE_SCHEMA = 'base' -------------- +--------+--------+------+-------+------+----------+----------+ | Table | Rows | Data | Index | Free | Total Ko | Total Mo | +--------+--------+------+-------+------+----------+----------+ | tabone | 107398 | 7696 | 1552 | 3072 | 12320 | 12.03 | +--------+--------+------+-------+------+----------+----------+ -------------- COMMIT -------------- Appuyez sur une touche pour continuer...
Voici ce que dit la documentation MySql :

Citation:

TABLE_ROWS

Le nombre de lignes. Certains moteurs de stockage, tels que MyISAM, stockent le nombre exact. Pour les autres moteurs de stockage, tels que InnoDB, cette valeur est une approximation et peut différer de la valeur réelle jusqu'à 40 % à 50 %. Dans de tels cas, utilisez SELECT COUNT(*)pour obtenir un décompte précis.

TABLE_ROWS est à NULL pour les tables de INFORMATION_SCHEMA.

Pour les tables InnoDB, le nombre de lignes n'est qu'une estimation approximative utilisée dans l'optimisation SQL. (Ceci est également vrai si la table InnoDB est partitionnée.)

Cordialement.
Artemus24.
@+

26/01/2023, 15h21
dari68

Bonjour à tous,

Code:

create table new_table AS SELECT <vos colonnes> from `old_table` order by <les colonnes de la primary key>;

Ne crée pas les contraintes (FK, unique, ...) pour la nouvelle table new_table ?

Merci.

A+