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

MySQL Discussion :

Très grosse BDD: Table limitée à 20 712 471 lignes ? [MySQL-8.0]


Sujet :

MySQL

  1. #1
    Membre actif Avatar de franckcl
    Homme Profil pro
    Developpeur Delphi
    Inscrit en
    septembre 2004
    Messages
    332
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Developpeur Delphi
    Secteur : High Tech - Électronique et micro-électronique

    Informations forums :
    Inscription : septembre 2004
    Messages : 332
    Points : 262
    Points
    262
    Par défaut Très grosse BDD: Table limitée à 20 712 471 lignes ?
    Bonjour à tous,

    J'utilise un WAMP sur WIN10 en NTFS.

    J'ai créé une table qui comportent les champs suivants
    Nom : table1.png
Affichages : 88
Taille : 88,0 Ko

    Le nombre de lignes affiché est de 20 712 471 et plus aucune ligne n'est ajouté;

    Nom : table2.png
Affichages : 74
Taille : 62,1 Ko
    Depuis PhpMyAdmin, si j'insère une ligne manuellement, je n'ai aucune erreur, il m'indique que l'opération s'est bien passée mais finalement je constate qu'aucune ligne n'est ajoutée.

    Depuis PhpMyAdmin, je peux visualiser le début du contenu de la base mais pas la fin, le message "Chargement en cours" reste afficher en permanence et rien ne se passe.

    Comme vous pouvez le constater j'utilise un index sur 4 colonnes avec l'option UNIQUE.
    Je n'ai pas de colonne AUTO_INC, est ce obligatoire ?

    Cette table est destinée à réaliser un data logger. Il y a environ 20 à 100 lignes qui sont créées toutes les secondes et j'aimerai tenir ce rythme pendant au moins 6 mois.
    Je suis conscient que cela va faire une très grosse base de données mais pourquoi cette limite à 20 712 471 lignes ?
    J'ai vu sur que MySQL était utilisé sur de très grosses bases comme Youtube, donc c'est pour cette raison que je suis parti avec cette base.

    Merci pour vos avis.
    Franck

  2. #2
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    19 477
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 19 477
    Points : 46 228
    Points
    46 228
    Par défaut
    Citation Envoyé par franckcl Voir le message
    ….
    J'ai vu sur que MySQL était utilisé sur de très grosses bases comme Youtube, donc c'est pour cette raison que je suis parti avec cette base.
    En lisant des crétineries on se fait des opinions idiotes !

    MySQL n'est PAS DU TOUT fait pour gérer de fortes volumétries.

    YouTube et cie utilisent deux concepts pour gérer de fortes volumétrie avec MySQL
    1) une version customisée de MySQL pour laquelle ils ont fait plusieurs années hommes en R&D
    2) une répartition des données sur des milliers de serveurs.

    Si vous voulez gérer de la volumétrie dans une seule et même machine, il faut un SGBDR ayant des techniques de stockages interne comme Oracle, MS SQL server ou DB2.

    J'ai plusieurs clients qui ont dépassé le milliard de ligne dans une seule et même table dans SQL Server….

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  3. #3
    Membre actif Avatar de franckcl
    Homme Profil pro
    Developpeur Delphi
    Inscrit en
    septembre 2004
    Messages
    332
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Developpeur Delphi
    Secteur : High Tech - Électronique et micro-électronique

    Informations forums :
    Inscription : septembre 2004
    Messages : 332
    Points : 262
    Points
    262
    Par défaut
    J'ai trouvé d'où venait mon problème, donc je donne la réponse pour partager.
    J'en suis maintenant à 192 millions de lignes sur une même table et les temps de réponse sont absolument excellents pour le type de machine qui me sert de banc de test.
    Il s'agit d'un vieux PC portable qui a 10 ans (un I7, sous Windows 10, avec 4G de RAM), la taille de la table occupe sur le disque 20Go.
    La requete suivante s'effectue en seulement 10ms:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    SELECT * FROM `tag_values` WHERE hash_config=15135684329552187526 and id_system=1 and id_tag<5 and update_number=249505
    Donc pour l'application que je veux en faire, c'est largement suffisant et ce sera encore meilleurs sur la machine définitive qui sera une bête de course.

    J'ai tout simplement supprimé l'index unique qui était sur 4 colonnes et je l'ai remplacé par 4 index simple (1 par colonne). Je gère les restrictions au niveau du soft qui rempli la table.
    J'ai aussi ajouté une colonne d'index primaire auto incrémental.

    Franck

  4. #4
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    19 477
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 19 477
    Points : 46 228
    Points
    46 228
    Par défaut
    Citation Envoyé par franckcl Voir le message
    ...
    J'ai tout simplement supprimé l'index unique qui était sur 4 colonnes et je l'ai remplacé par 4 index simple (1 par colonne). Je gère les restrictions au niveau du soft qui rempli la table.
    J'ai aussi ajouté une colonne d'index primaire auto incrémental.

    Franck
    Ce n'est pas du tout la même chose d'un point de vu sémantique ! Imaginez ce qui va se passer si vous avez des doublons… !!!!

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  5. #5
    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
    4 304
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 80
    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 : 4 304
    Points : 12 525
    Points
    12 525
    Par défaut
    Salut à tous.

    Citation Envoyé par franckcl
    Le nombre de lignes affiché est de 20 712 471 et plus aucune ligne n'est ajouté;
    Je constate que votre index unique est constitué des colonnes suivantes :
    --> hash_config
    --> id_system
    --> id_tag
    --> update_number.

    Quel est l'intérêt de procéder ainsi ? Je vois que c'est pour optimiser une requête en terme de performance.

    En l'état des informations fournis, nous ne pouvons répondre sur pourquoi votre clef primaire constitué de quatre colonnes est l'origine de votre problème.

    Je confirme les propos de SQLPRO :
    1) MySql n'est pas fait pour une grosse volumétrie. Dans ce cas, passez à Microsoft SQL Server qui est mieux adapter à ce genre de problème.
    2) si vous supprimez votre index unique vous risquez d'avoir des problèmes de doublons.

    Votre table est mal construite.
    a) Vous devez créer une clef primaire basée sur un auto incrément.

    b) je ne comprends pas l'intérêt de la colonne hash_config.
    Soit elle est informationnelle ou si vous préférez à titre indicatif, soit elle joue un rôle particulier dans le pointage de vos données.
    Elle peut servir de clef secondaire, dans le sens d'une clef candidate en tant que clef primaire.

    c) Les colonnes id_system et id_tag doivent être des clefs étrangères, pointant sur les tables respectives.

    d) la colonne update_number est un numéro séquentiel qui sert à dissocier les doublons sur les trois premières colonnes de votre index unique.
    Cela peut se gérer automatiquement dans un déclencheur.
    Sinon, il faudrait définir le rôle joué par cette colonne.

    Citation Envoyé par franckcl
    J'ai tout simplement supprimé l'index unique qui était sur 4 colonnes et je l'ai remplacé par 4 index simple (1 par colonne).
    Ce que vous faites ne sert à rien, sinon à alourdir inutilement les performances de votre base de données.
    MySql ne sait gérer qu'un seul index à la fois.
    Utilisez EXPLAIN pour connaitre l'index utilisé dans votre requête.

    Si vous créez un seul index sur hash_config, c'est largement suffisant en terme de performance pour accéder à un tout petit groupe de lignes.
    C'est sur des tests de performances que l'on se rend compte de la pertinence de ce groupe de lignes.
    De combien est constitué au maximum ce groupe de lignes ?

    Citation Envoyé par franckcl
    J'ai aussi ajouté une colonne d'index primaire auto incrémental.
    C'est une bonne idée à la condition que cela vous serve à quelque chose dans vos traitements.

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

  6. #6
    Expert éminent sénior

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    5 416
    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 : 5 416
    Points : 16 334
    Points
    16 334
    Billets dans le blog
    1
    Par défaut
    En plus, je vois que vous êtes en MyISAM qui ne gère pas l'intégrité référentielle

  7. #7
    Modérateur

    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    16 261
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 56
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : août 2006
    Messages : 16 261
    Points : 32 523
    Points
    32 523
    Billets dans le blog
    12
    Par défaut
    Il y a plus de 10 ans, j'ai créé à l'INRA une BDD MySQL sur un portable de l'époque (ensuite exportée sur un petit serveur de l'époque) à partir de 4 années de la base de données nationale d'identification des bovins. Une de mes tables avait 63 millions de lignes et plusieurs autres en avaient plusieurs millions. Toutes mes requêtes, parfois complexes, à fins de statistiques pour un thésard, s'exécutaient au maximum en moins de 2 secondes.

    Donc votre limite de 20 millions de lignes n'en est pas une. Peut-être est-ce une limite donnée par un paramètre de config dans MySQL ou dans phpMyAdmin ?
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Performances sur très grosse table
    Par CinePhil dans le forum Optimisations
    Réponses: 2
    Dernier message: 17/09/2008, 18h52
  2. [SSIS][2k5] jointure entre très grosse table
    Par RicardMan dans le forum SSIS
    Réponses: 1
    Dernier message: 18/04/2008, 17h54
  3. Trier de trés grosses tables
    Par funckfot dans le forum Algorithmes et structures de données
    Réponses: 12
    Dernier message: 07/06/2007, 18h30
  4. Gestion de très grosse table
    Par Arni23 dans le forum Requêtes
    Réponses: 11
    Dernier message: 04/06/2007, 21h41
  5. Sauvegarde très grosse bdd
    Par creezeer dans le forum Administration
    Réponses: 7
    Dernier message: 27/07/2006, 17h24

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