[SqlServer] Utilité du découpage d'une grosse table ?

Utilité du découpage d'une grosse table ? [SqlServer] - SQL/NoSQL - Programmation

Marsh Posté le 17-01-2005 à 13:56:30    

Coucou tout le monde,
 
j'utilise une table qui commence à devenir lourde : 60.000.000 d'enregistrements ( 5.000.000 par mois ), et qui est consultée à longueur de journée par plusieurs utilisateurs.
 
Pour les recherches 'simples', çà ne pose pas encore de problème ; par contre, dès qu'il y a ajout ou suppression d'enregistrement, là çà commence à grincer, et toute requête un peu plus fine ( GROUP BY ) prend une éternité ...
 
Je me dis donc : pourquoi ne pas scinder cette table en plusieurs tables ? Je devrais y gagner en rapidité d'éxécution !
Mais la gestion de ces tables à la place d'une seule me parait un poil plus compliquée ...
 
Qq'un connait ce problème de volumétrie ?
Quels choix faire ?

Reply

Marsh Posté le 17-01-2005 à 13:56:30   

Reply

Marsh Posté le 17-01-2005 à 15:07:17    

Faire des index corrects.
 
A la limite, pour remplacer le découpage, tu peux faire une fonction du style :
 

Code :
  1. create function dbo.getDataMonth(month as datetime)
  2. returns table
  3. as
  4. begin
  5.    return (select * from magrossetable where ladate between month and dateadd(m, month, 1))
  6. end


 
Ce type de fonction (ou vue paramètrée) permet d'accélerer de façon conséquente la récupération d'un lot réduit de données. L'avantage, c'est que lorsque tu veux pas te limiter à 1 mois, tu peux toujours faire un select normal directement dans ta table.
De plus, si tu utilises SQL Server 2000, une telle fonction te permet de faire des mises à jour dedans (update, delete et insert sont authorisés tant que tu n'as pas de fonctions de regroupement ou de jointures)
Pour l'ajout/supression, tu peux aussi passer par des procédures stockées. Tu devrais y gagner énormément.
 
Mais pour moi, le plus important, c'est d'avoir des index.

Reply

Marsh Posté le 17-01-2005 à 15:12:47    

La table contient déjà plusieurs index ;)
Mais les index ne sont utiles que pour les select, non ?
Là mon pb du moment c'est l'insertion de données en masse ...

Reply

Marsh Posté le 17-01-2005 à 15:54:12    

Pour l'insertion, en effet, les indexne jouent que très peu (et même pire, plus il y a d'index, et plus ça peut ralentir : mise à jour de toutes les index, et vérification de l'unicité des index uniques)
 
Je n'ai pas d'idée pour optimiser l'insertion. Il n'y a pas de SQL Loader avec SQL Server, et en effet, l'insert est assez lent. A part augmenter la rapidité du serveur (ajout de disques en RAID, et augmentation de la mémoire) je ne vois pas trop comment tu peux améliorer les performances de façon conséquente à ce niveau.
 
Juste un truc : si c'est un insert de masse, je te conseille de faire :

Code :
  1. begin tran
  2. select numm from latable with lockxx
  3. insert ...
  4. insert ...
  5. insert ...
  6. commit


 
Le faite de faire un lock avec une portée sur toute la table durant ton insersion va éviter que des personnes fassent des select sur la table en même temps.
 
Il y a cependant deux points négatifs :
-> Le select avec lockxx va être mis en file d'attente jusqu'à ce que tous les locks (même d'une ligne) soient libérés sur la table.
-> Durant toute ta transaction, toutes les requêtes seront mises en file d'attente. Il convient donc de mettre à jour ta table à un moment de faible utilisation.
 
Pourquoi ne pas faire une table temporaire dans laquelle tu log les infos du jour, puis par job, tu les recopie durant la nuit dans la table en question ?
Si les données du jour ne représentent pas une information vitale, c'est ce qu'il y a de mieu à mon avis.

Reply

Marsh Posté le 17-01-2005 à 15:56:19    

Ah, et lors de tes insert de masse, tu peux désactiver le autocommit. Eviter à la base de faire une transaction par ligne, c'est une bonne optimisation déjà (bien que d'après des tests que j'ai pu faire, c'est pas super concluant)

Reply

Marsh Posté le 17-01-2005 à 16:20:10    

J'utilise un DTS pour faire mon insertion, je ne contrôle donc pas tout le processus ...
Pour ce qui est de la puissance de la bécane, bah on va difficilement pouvoir faire mieux :D
Bi Xeon 3.2G / 2G ram / Raid 5 SCSI / ...
 
Si je supprime temporairement tous les index avant l'insertion, et je les regénére après, çà peut aider ?


Message édité par WhyMe le 17-01-2005 à 16:21:28
Reply

Marsh Posté le 17-01-2005 à 17:08:36    

j'en doute grandement. et de toute façon, faut surtout pas faire ;)
 
PS: logiquement, un DTS est transactionnel, donc pour le "select null from latable with lockxx" tu dois pouvoir le mettre en première étape de ton lot. Je ne garantis pas que ça énormément améliorer la chose, mais ça devrait déjà être un minimum.

Reply

Marsh Posté le 17-01-2005 à 17:20:06    

Sous Oracle, t'as des options sur les tables pour améliorer un peu les perfs, genre pouvoir accéder à la table en parallèle (même en ne lançant qu'un requête).
 
Des infos ici :
 
http://www.oreilly.com/catalog/ora [...] /ch01.html
 
Il y a sûrement un équivalent SQL Server ..

Reply

Marsh Posté le 17-01-2005 à 19:34:23    

Sinon, je te renvoie sur cette page de la documentation de SQL Server :
 
Optimisation des performances de base de données


Message édité par Arjuna le 17-01-2005 à 19:34:57
Reply

Marsh Posté le 17-01-2005 à 19:36:02    

Mais y'a pas grand chose de plus que ce que j'ai dit :
- Indexes
- Procédures stockées : totalement interdit de faire la moindre requête "en live" :o

Reply

Marsh Posté le 17-01-2005 à 19:36:02   

Reply

Marsh Posté le 18-01-2005 à 09:24:47    

Et le découpage en plusieurs fichiers sert à qqchose ?
Là le fichier de données fait 10G ...
En sachant que j'utilise un fichier pour les données et un pour les index ;)


Message édité par WhyMe le 18-01-2005 à 09:26:08
Reply

Marsh Posté le 18-01-2005 à 10:19:11    

Si t'as plusieurs disques et qu'ils ne sont pas en RAID, oui, ça peut servir. Sinon, ça n'est pas spécialement utile. Les fichiers de données ne sont pas gérés dans SQL Server comme les TABLESPACE d'Oracle.
 
Par contre, tu peux dors et déjà mettre le fichier des index et celui des données sur deux disques distincts (pas sur la même chaîne RAID si tu es en RAID. Tu devrais déjà gagner considérablement.
 
Mais bon, le plus important, c'est surtout le journal des transactions.
M$ préconise (pendant l'installation de SQL Server) de découper comme suit :
 
C:\ (RAID mirroré) : Windows + Binaires de SQL Server
D:\ (RAID 5 ou 50) : Journal des transactions
E:\ (RAID 5 ou 50) : Fichier de données
 
Avec obligatoirement D et E sur deux canaux différents, voir deux contrôleurs si possible, car il y a des écritures/lectures intensives dans les deux fichiers en //, surtout lorsqu'on fait des mises à jour des données (c'est le cas de tes insert).
 
Chez moi j'ai pas de RAID, mais j'au tout de même fait attention à avoir chacun de ces 3 disques sur des disques physiques différents, et D: et E: sont sur deux canaux U3W séparés, ce qui me garanti des performances optimales pour mon matériel.

Reply

Marsh Posté le 18-01-2005 à 10:22:18    

Ah... Et juste un truc...
 
Ton fichier de données, il est à croissance automatique ? Tu insères quel volume quand tu passes un lot d'insertions ? Vérifie que le fichier des données/index/transactions n'a pas besoin d'un redimensionnement en plein milieu...
 
Dans l'absolu, fait comme la gestion par défaut des tablespace d'Oracle : pas de croissance automatique : prévoit à l'avance l'occupation disque. Redimensionner à la voler un fichier de 10 Go, c'est généralement pas très sympa pour le disque.
 
PS: 10 Go ça me paraît énorme. Ormis ta table de 60 000 000 de lignes, y'a quoi dans ta base ? T'es sûr que c'est pas le log des transactions qui bouffe toute la place (j'ai eu le cas, chez moi j'ai saturé un disque de 160 Go avec un journal des transactions plein... En 3 heures :lol: (pas bien de faire des jobs qui font des actions sur l'ensemble des lignes de la base toutes les 30 secondes :sol:)

Reply

Marsh Posté le 18-01-2005 à 11:58:33    

Config du serveur :
. C : raid 0
. D et E : raid 5 sur 3 disques
 
Fichier de données : 9.9G
Fichier des index : 2.3G
Fichier log : 33M
 
Les 3 fichiers sont sur le D, ds le même répertoire.
Les autres bases sont + ou - calquées sur ce même schéma.
 
Pour ce qui est des canaux et controleurs, j'en sais rien j'ai pas monté la machine ( j'aurais bien voulu :D )
 
Tous les fichiers sont en croissance automatique ...
Là je viens de voir que les fichiers grossissent par palier de 32M, pas terrible, je vais augmenter cette valeur tout de suite !
 
A part ma table de 60.000.000 de lignes y'a rien d'autre ds cette base :D
 
En basculant les logs sur le C, çà devrait déjà aller mieux puisque c'est un disque physiquement différent, nan ?


Message édité par WhyMe le 18-01-2005 à 11:59:21
Reply

Marsh Posté le 18-01-2005 à 16:17:33    

Si c'est possible, moi je te conseille grandement d'éclater ton RAID 5 en 3 disques :
D: LOG
E: INDEX
F: DATA
 
Et je te conseille de mettre E et F sur la même chaîne SCSI, et C et D sur l'autre.
 
Mine de rien, même si le RAID 5 est très performant, en changeant les fichiers de disques, tu vas énormément gagner. Notamment pour les raisons suivantes :
1) Ca va réduire les accès I/O sur le disque logique. Par conséquent, ça va réduire la file d'attente, et même si le RAID 5 est très rapide, tu devrais gagner à ce niveau. En effet, le serveur est actuellement incapable de sérialiser correctement plusieurs tâches dans les 3 fichiers. En temps normal, il faut permettre au serveur de lire dans un index pendant qu'il écrit dans les données et met à jour le ficher des transactions. Là, il est obligé de tout faire à la suite, impossible donc de sérialiser.
2) Ca va réduire la fragmentation de tes fichiers : en effet, imagine la config de base :
D : Fichier de données
I : Fichier d'index
L : Fichier des logs


Config de départ :
DDDDDDIIIILLLL
 
Après quelques temps d'utilisation (à chaque croissance, par manque de place contigüe, les fichers sont fragmentés et des petits bouts de 32 Mo sont rajoutés à la suite des premiers fichiers) :
DDDDDDIIIILLLLDDILDDI LIDDDI


 
Résultat, pour lire le contenu d'un index volumineux, dans ce cas, il va falloir faire 5 accès disque. Ensuite, une fois les données retrouvées, il va falloir faire 4 accès de données. Deplus, les fichiers n'étant pas contigüs, tu allonge les temps d'accès (plus de distance à parcourir pour retrouver les infos d'un bout de fichier à l'autre).
 
Seul problème, c'est que tu perds les capacités de récupération et d'agrandissement à chaud de l'espace disque du RAID 5. Il faudrait donc, dans l'absolu, racheter des disques pour au moins faire du RAID 0.
 
Dans un permier temps, tu peux tout de même améliorer les choses de la façon suivante :
 
LOG : sur C: => C'est un fichier qui ne fait que grossir (sauf lorsque tu le shrink à la fin d'un backup). Il n'y a jamais de lecture dessus. Les accès n'ont donc pas besoin d'être très rapide (ils sont juste nombreux). Il ne va donc pas spécialement alourdir les traîtements sur le disque système. Deplus, vu que ce fichier est extrêment sensible (combiné aux backups, il te permet de récupérer à la seconde près un état stable de la base de données, même si ton ficher de données est corrompu). Le RAID 0 est donc parfaitement approprié pour ce fichier.
Data sur le D:
Index sur le E:
Mettre les Index et les Data sur le même disque, c'est très moyen, à cause de la sérialisation. Cependant, les deux ont besoin d'un accès extrêment rapide, et font des lectures/écritures intensives, tout en ayant besoin d'un espace de stockage fiable. Le RAID 5 est donc le bienvenu (mais dans l'idéal il en faudrait deux). Le fait de les changer de disque logique (un sur D et l'autre sur E), c'est que lorsque les fichiers vont croître, ils font chacun le faire sur une partie du disque réservée à leur partition. Par conséquent, il n'y aura plus de phénomène de fragmentation (ce qui, d'après le paramètrage de 32 Mo, me semble est une source certaine des ralentissements que tu obtiens).
 
Tu peux déjà commencer par ce paramètrage, avant de tout péter. L'avantage de faire ça, c'est que tu ne perds pas la sécurisation du stockage du serveur actuel.
 
PS: Est-ce que tes données sont particulièrement sensibles ? Si ce n'est pas le cas, tu peux m'envoyer un backup de ta base ainsi que quelques requêtes types de consultation et d'alimentation en données, que je regarde sur mon serveur personnel (config que je t'ai indiqué). Il est bien moins puissant que le tiens (bi-piii 933 / 2 Go SDRAM / 4 HD U3W 10ktrm) mais c'est une bonne base pour faire des tests avec une architecture avec 4 disques différents, puisque je les ai déjà ;)

Reply

Marsh Posté le 18-01-2005 à 20:44:06    

Ouah !
En relisant tes explications, je vois que tu sais de quoi tu parles !
Merci pour toutes ces précisions :jap:
 
Je ne peux physiquement pas toucher aux machines, elles sont chez un hébergeur pro ; je ne peux donc pas faire de tests en modifiant / désactivant le raid ( j'aurais vraiment adoré pouvoir le faire, mais là çà va vraiment être difficile :D )
 
Vu le prix de ces machines, je ne pense pas qu'on va réinvestir ds des disques si mon argument c'est : "on va p'tête gagner en perf" :D
 
Donc pour l'instant, il faut utiliser la config matérielle actuelle :jap:
 
Ce qui est chiant c'est que tu ne peux pas modifier les fichiers d'origine ; j'ai voulu modifier le fichier de log pour le déplacer sur le C, mais imposible de supprimer celui d'origine sur le D ... Faut que je creuse un peu + ...
 
J'ai déjà fais une 1ère modif : désormais les fichiers s'agrandissent par palier de 512M, çà redimensionnera - souvent ;)
 
Le pb, c'est que cette machine est celle de prod, elle est donc utilisée toute la journée et une bonne partie de la nuit, difficile donc de faire des tests dessus ... Le seul moment où je peux faire des tests à peu près sans géner personne, c'est entre 12h30 et 13h30, un peu light pour faire des manip sur des fichiers monstrueux ...
 
Je peux utiliser la machine de pré prod à ma guise, mais elle est un peu - puissante ( 1 seul Xéon / 1G ram / C, D et E sur 3 disques en raid 5 )
 
Notre serveur de dév est encore - puissant ( 2800+ / 1.5G ram / 80G raid 0 ), c'est le seul que j'ai physiquement à portée de main, mais pas forcément intéressant vu qu'il y a que 2 disques dedans ...
 
Oui nos données sont sensibles : cette table contient les appels téléphoniques de nos clients ( on est opérateur téléphonique alternatif )
Mais bon, çà ne sont que des chiffres ;)
 
Je vais déjà essayer de mettre 1 fichier par partition, on verra bien ...
Mais demain çà va pas être possible, je suis pas dispo de la journée :(
 
La suite jeudi ...
 
Question bonus : défragmenter les disques peut vraiment aider sur ce type de machine, pour savoir si çà vaut le coup d'investir ds un VRAI soft de défragmentation ?


Message édité par WhyMe le 18-01-2005 à 20:47:49
Reply

Marsh Posté le 19-01-2005 à 09:39:55    

En cherchant un peu, je viens de découvrir 2 choses 'marrantes' :(
.Il y a 6 disques ds la bécane et pas 5 ( 2 en raid 1 pour le C et 4 en raid 5 pour le D, E )
.Il y a 18G d'espace non affecté sur le C et 36G d'espace non affecté sur le D, E ( on s'est déjà pris la tête plusieurs fois pour des pb d'espace disque ... )
 
Les gars qui ont monté cette bécane sont vraiment des boulets ...
 
Donc bonne nouvelle : avec 6 disques, je peux faire 3 raid 1  :love:  
 
Reste à voir çà avec mon responsable de projet pour pas péter le serveur :D

Reply

Marsh Posté le 20-01-2005 à 09:56:13    

Bon, j'ai pas trop le tps de jouer avec la base en ce moment pour trouver la config idéale ; quels réglages dois je adopter avec la config actuelle du serveur en considérant que j'ai 2 disques physiquement différents ?
Y'a qd même pas mal de possibilités :
index + log sur C / data sur D
index sur C / log + data sur D
...

Reply

Marsh Posté le 20-01-2005 à 09:59:09    

Même si c'est pas ce qu'il y a de mieu, je dirais log sur C et index + data sur D, pour diverses raisons liées à la config des disques.

Reply

Marsh Posté le 20-01-2005 à 10:26:04    

OK, je vais essayer de refaire une base avec cette architecture :jap:
 
Merci :hello:

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed