IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Administration MySQL Discussion :

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


Sujet :

Administration MySQL

  1. #1
    Candidat au Club
    Homme Profil pro
    Consultant en sécurité
    Inscrit en
    janvier 2023
    Messages
    7
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Consultant en sécurité
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : janvier 2023
    Messages : 7
    Points : 2
    Points
    2
    Par défaut Tailles différentes (x4 voire plus) pour tables quasi identiques
    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

  2. #2
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    5 786
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 5 786
    Points : 17 012
    Points
    17 012
    Par défaut
    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.
    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  3. #3
    Candidat au Club
    Homme Profil pro
    Consultant en sécurité
    Inscrit en
    janvier 2023
    Messages
    7
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Consultant en sécurité
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : janvier 2023
    Messages : 7
    Points : 2
    Points
    2
    Par défaut
    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 : 45
Taille : 49,3 Ko
    Et la structure des tables:
    Nom : tables_struct.GIF
Affichages : 48
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 : 48
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 : 45
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 : 45
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

  4. #4
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    5 786
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 5 786
    Points : 17 012
    Points
    17 012
    Par défaut
    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.
    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  5. #5
    Candidat au Club
    Homme Profil pro
    Consultant en sécurité
    Inscrit en
    janvier 2023
    Messages
    7
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Consultant en sécurité
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : janvier 2023
    Messages : 7
    Points : 2
    Points
    2
    Par défaut
    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!

  6. #6
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    9 187
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 9 187
    Points : 34 512
    Points
    34 512
    Billets dans le blog
    3
    Par défaut
    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

  7. #7
    Candidat au Club
    Homme Profil pro
    Consultant en sécurité
    Inscrit en
    janvier 2023
    Messages
    7
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Consultant en sécurité
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : janvier 2023
    Messages : 7
    Points : 2
    Points
    2
    Par défaut
    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 : 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 |             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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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)
    Images attachées Images attachées  

  8. #8
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    5 786
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 5 786
    Points : 17 012
    Points
    17 012
    Par défaut
    Salut à tous.

    Pour recréer une table à l'identique, on procède ainsi :
    Code mysql : Sélectionner tout - Visualiser dans une fenêtre à part
    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 mysql : Sélectionner tout - Visualiser dans une fenêtre à part
    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 mysql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    use <votre base de données>;
    select count(*) from <votre table>;

    Cordialement.
    Artemus24.
    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  9. #9
    Candidat au Club
    Homme Profil pro
    Consultant en sécurité
    Inscrit en
    janvier 2023
    Messages
    7
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Consultant en sécurité
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : janvier 2023
    Messages : 7
    Points : 2
    Points
    2
    Par défaut
    Hello Artemus24,

    voilà les résultats:
    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
    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.
    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.

  10. #10
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    5 786
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 5 786
    Points : 17 012
    Points
    17 012
    Par défaut
    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 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
    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 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
    --------------
    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.
    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  11. #11
    Candidat au Club
    Homme Profil pro
    Consultant en sécurité
    Inscrit en
    janvier 2023
    Messages
    7
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Consultant en sécurité
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : janvier 2023
    Messages : 7
    Points : 2
    Points
    2
    Par défaut
    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 : 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
    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
    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 : 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
    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.

  12. #12
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    5 786
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 5 786
    Points : 17 012
    Points
    17 012
    Par défaut
    Salut jlamazon.

    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
    --------------
    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 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
    --------------
    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 :
    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.
    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  13. #13
    Candidat au Club
    Homme Profil pro
    Consultant en sécurité
    Inscrit en
    janvier 2023
    Messages
    7
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Consultant en sécurité
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : janvier 2023
    Messages : 7
    Points : 2
    Points
    2
    Par défaut
    Merci pour toutes ces infos Artemus24, je vais faire quelque essais pour voir quelle est ma meilleure option avec les performances en face.
    Jean

  14. #14
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    9 187
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 9 187
    Points : 34 512
    Points
    34 512
    Billets dans le blog
    3
    Par défaut
    Bonjour,

    Citation Envoyé par Artemus24 Voir le message
    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

  15. #15
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    5 786
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 5 786
    Points : 17 012
    Points
    17 012
    Par défaut
    Salut Escartefigue.

    Ca ne fonctionne pas !
    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
    --------------
    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 :
    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.
    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  16. #16
    Membre actif
    Profil pro
    Inscrit en
    décembre 2008
    Messages
    722
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : décembre 2008
    Messages : 722
    Points : 256
    Points
    256
    Par défaut
    Bonjour à tous,

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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+

Discussions similaires

  1. Joindre 3 (voir plus) tables ensemble
    Par Unreal Time dans le forum Langage SQL
    Réponses: 8
    Dernier message: 14/01/2010, 12h30
  2. [SQL] Extraire des données de 2 tables quasi identiques
    Par kryss_63 dans le forum Requêtes et SQL.
    Réponses: 3
    Dernier message: 29/06/2007, 17h17
  3. Réponses: 2
    Dernier message: 04/05/2007, 18h16
  4. Utilitaire pour voir/modifier les tables
    Par fmarot dans le forum MS SQL Server
    Réponses: 6
    Dernier message: 24/04/2006, 09h41
  5. [VB.NET] Taille differente pour chaque colonne dans DATAGRID
    Par stephane93fr dans le forum Windows Forms
    Réponses: 14
    Dernier message: 12/01/2005, 17h50

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo