Discussion :

[geounknown]

Bonjour à tous,
Je n'ai pas une très grande expérience SQl et j'aurai besoin d'avis éclairés.

Je travail sur une infra et la taille des tables me préoccupe.
En listant le poids des tables grâce à cette requete

Code :

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

1
2
3
4
5
SELECT table_name AS "Tables", round(((data_length + index_length) / 1024 / 1024), 2) AS size 
FROM information_schema.tables 
WHERE table_schema =  'mabase'
ORDER by size DESC
LIMIT 1000

j'obtient ça :
table1 198106.00
table2 21204.36
table3 17753.72
...
C'est en MB, donc environ 200GO pour ma table1...
C'est beaucoup non?

Sachant que je pourrai facilement décharger cette table en ne gardant que les données des 2 derniers mois, ce qui serait largement suffisant pour garder intègre les fonctionnalités qui utilisent cette table.

Par contre la taille de la table me pose problème... comment sauvegarder, faire des opérations.... J'ai l'impression que chaque opération dessus sera très longue

Mes questions :
Est-ce qu'une requete DELETE sur la table basé sur la date pourrait se dérouler rapidement (genre - de 5 minutes?) pendant une maintenance?
Avez vous une expérience similaire ou des conseil à me donner sur des tables si importantes?
Est-ce que la taille a une grosse influence sur les performances générales? je veux dire est-ce que si je réduit la table, je verrai une différence sur les temps d'accès?


Merci beaucoup d'avance !

Geo

[Artemus24]

Salut geounknown.

C'est en MB, donc environ 200GO pour ma table1... C'est beaucoup non ?

C'est difficile de répondre ainsi. Sur un petit ordinateur, oui, cela peut paraitre énorme.
Mais sur de gros serveurs bien taillés, genre multi-coeur, multi-tâche, un max de RAM, cela peut sembler moyen.

Sachant que je pourrai facilement décharger cette table en ne gardant que les données des 2 derniers mois, ce qui serait largement suffisant pour garder intègre les fonctionnalités qui utilisent cette table.

Qu'elle est votre question ? S'agit d'une question de temps d'exécution ou de faisabilité ?
Ou bien, vous ne savez pas juger des conséquences de ce que vous allez faire.
Si vous n'avez plus besoin des lignes au delà des deux derniers mois, faites le grand nettoyage.

Par contre la taille de la table me pose problème... comment sauvegarder, faire des opérations...

Pour accélérer vos sauvegardes ou vos requêtes, vous pouvez partitionner vos tables.
Est-ce que vos plus vieilles lignes servent encore à quelques choses dans vos traitements ?
Faites-vous des traitements par mois, par trimestre, par semestre, annuel ?

J'ai l'impression que chaque opération dessus sera très longue

En général, plus les tables sont grosses et plus ça prend du temps.
Mais avec une optimisation de vos requêtes et avec l'ajout de quelques index, on peut rendre vos temps d'exécutions acceptables.

Est-ce qu'une requete DELETE sur la table basé sur la date pourrait se dérouler rapidement (genre - de 5 minutes?) pendant une maintenance?

Faire un "delete from 'test' where date < '2015-06-07';" peut prendre pas mal de temps, surtout s'il n'y a pas d'index sur la colonne date.
Si vous avez disons plus de 80% de lignes à supprimer, je vous conseille plutôt de créer une nouvelle en faisant un "insert into 'test2' ('col1', 'col2', ...) select col1, col2, ... from test where date >= '2015-06-01';".
Votre moteur, est-ce 'MyIsam' ou 'InnoDB' ?

Est-ce que la taille a une grosse influence sur les performances générales ?

On ne peut pas répondre ainsi. Cela dépend de vos traitements.
Si vous faites un balayage de votre table, plus elle est grosse et plus ça prend du temps.
Inversement, si vous sélectionner disons 10 lignes à partir d'une colonne sachant que cette colonne est indexée, le temps d'exécution sera constant, quelque soit la volumétrie de votre table.

je veux dire est-ce que si je réduis la table, je verrai une différence sur les temps d'accès ?

J'avais bien compris votre question. Hormis les temps d'accès, une petite table est plus facile à gérer au niveau de la maintenance.

@+

[geounknown]

Merci énormément de votre réponse Artemus. Cela m’aiguille beaucoup.

C'est difficile de répondre ainsi. Sur un petit ordinateur, oui, cela peut paraitre énorme.
Mais sur de gros serveurs bien taillés, genre multi-coeur, multi-tâche, un max de RAM, cela peut sembler moyen.

Je suis sur une grosse infra chez amazon avec de gros serveur. Mais cela a un coût... et je n'aime pas trop gaspiller des resources ou de l'argent.

Sachant que je pourrai facilement décharger cette table en ne gardant que les données des 2 derniers mois, ce qui serait largement suffisant pour garder intègre les fonctionnalités qui utilisent cette table.

Qu'elle est votre question ? S'agit d'une question de temps d'exécution ou de faisabilité ?
Ou bien, vous ne savez pas juger des conséquences de ce que vous allez faire.
Si vous n'avez plus besoin des lignes au delà des deux derniers mois, faites le grand nettoyage.
Pour accélérer vos sauvegardes ou vos requêtes, vous pouvez partitionner vos tables.
Est-ce que vos plus vieilles lignes servent encore à quelques choses dans vos traitements ?
Faites-vous des traitements par mois, par trimestre, par semestre, annuel ?
En général, plus les tables sont grosses et plus ça prend du temps.
Mais avec une optimisation de vos requêtes et avec l'ajout de quelques index, on peut rendre vos temps d'exécutions acceptables.

OK on est d'accord, il faut décharger cette table... Je ne sais ni le temps, ni les conséquences des opérations sur des tables de ce volume.
Il n'y a aucun traitement régulier sur cette table. Que des INSERT et UPDATE lors du fonctionnement du site
La colonne de date n'est pas indexé.

Faire un "delete from 'test' where date < '2015-06-07';" peut prendre pas mal de temps, surtout s'il n'y a pas d'index sur la colonne date.
Si vous avez disons plus de 80% de lignes à supprimer, je vous conseille plutôt de créer une nouvelle en faisant un "insert into 'test2' ('col1', 'col2', ...) select col1, col2, ... from test where date >= '2015-06-01';".
Votre moteur, est-ce 'MyIsam' ou 'InnoDB' ?

Très bonne idée çà!
Cela me permettrai de tester pendant une des maintenances le temps de copie sur 1 jour par exemple pour estimer le temps total de l'opération...
En plus cela conserve les données...

J'ai 1 an de données dans cette table.
Est- ce qu'il est envisageable de copier 1 mois de datas dans une table 'table1_temp', renommer 'table1' en 'table1_old' et enfin 'table1_temp' en 'table1' pour rétablir le service sans changer la partie php
Les tables sont en InnoDB

On ne peut pas répondre ainsi. Cela dépend de vos traitements.
Si vous faites un balayage de votre table, plus elle est grosse et plus ça prend du temps.
Inversement, si vous sélectionner disons 10 lignes à partir d'une colonne sachant que cette colonne est indexée, le temps d'exécution sera constant, quelque soit la volumétrie de votre table.

J'avais bien compris votre question. Hormis les temps d'accès, une petite table est plus facile à gérer au niveau de la maintenance.

Le fait que table soit maintenable me semble suffisant pour réfléchir sérieusement à la possibilité de lancer la procédure...
J'ai conscience grâce à vos explications que le temps gagné au runtime ne sera pas énorme mais j'imagine que le gain se retrouve ailleurs... par exemple sur le temps mis par le backup quotidien...
ou le temps pour remettre en route l'infra en cas de crash...


Encore une fois merci beaucoup de vos réponse...

[Artemus24]

Salut geounknown.

Merci énormément de votre réponse Artemus. Cela m’aiguille beaucoup.

Pas de quoi. A votre service. Et dans la mesure de mes capacités.

Mais cela a un coût... et je n'aime pas trop gaspiller des resources ou de l'argent.

Vous avez entièrement raison ! L'optimisation de votre application passe aussi par l'organisation.
En gros système, le temps d'exécusion et la volumétrie sont facturés chaque mois, ce qui ne doit pas exister en micro.

Qu'est-ce qui vous empêche de déclencher durant la nuit via la crontab si vous êtes sous linux, un traitement de duplication de votre table ?
Au préalable, vous devez être certain d'avoir à votre disposition la volumétrie nécessaire. Et rien ne vous empêche non plus, de compresser vos données.

Je suppose que cette duplication est juste pour préparer le terrain avant le jour fatidique, c'est-à-dire le jour du basculement.

La colonne de date n'est pas indexé.

Si vous décidez de faire un "delete ... where date =", alors il serait bien d'indexer la colonne date. Le temps d'exécusion sera plus rapide.

Est-ce qu'il est envisageable de copier 1 mois de datas dans une table 'table1_temp', renommer 'table1' en 'table1_old' et enfin 'table1_temp' en 'table1' pour rétablir le service sans changer la partie php

Vous voulez faire une substitution (un swap) entre la table copie et la table en ligne. C'est envisageable mais je n'ai jamais fais ça.
Le problème est que je fais réguièrement des imports et des exports mais pas ce genre de manoeuvre.

Il y a juste un truc que je ne sais pas répondre. Quel est l'impact sur le fichier ibdata1, si vous faites la bascule, puisque votre mpteur est innodb ?

Un autre problème qu'il faudra envisager de faire. Admettons que la recopie se fasse durant la nuit du lundi au mardi.
Durant les jours suivants la table aura subit quelques modifications (des delete, des insert et des update).
Le jeudi matin, vous faites le basculement en mettant en ligne la copie modifiée.
C'est comme si le jeudi matin, vous vous retrouvez avec les données du mardi matin.
Est-ce que cela vous pose un problème d'avoir perdu deux jours de travail sur votre table ?
Et si OUI, avez-vous pensez comment réintroduire les modifications de ces deux jours ?
Avez-vous une procédure pour faire la comparaison entre la version en ligne et la copie ?

Le faire durant la semaine est une erreur. Je pense que vous devez le faire durant un week-end lorsque vous êtes tout seul.
Vous devez préparer vos procédures, ainsi que les outils pour les déclencher.
Tout ce que vous aurez à faire, c'est les déclencher et les surveiller.
Ah, oui, vous devrez durant cette manoeuvre bloquer le traitement pour ne pas vous retrouver avec un problème d'intégrité avec vos données.
Si vous avez bien travaillé, vous n'aurez aucune intervention à faire.

@+