Très haute disponibilité & Optimisation des performances

2. Très haute disponibilité & Optimisation des performances Christophe LaporteConsultantCONSEIL IT 8/2/2010 Frédéric Pichaut Senior EscalationEngineer « gourou »Support Microsoft Christian RobertSeniorDBA

3. Présentation • Frédéric Pichaut • Support EscalationEngineer • Support Microsoft

4. Présentation • MVP SQL Server – MCITP – MCT • +10 ans expérience SQL Server • +5 ans DBA Index Multimédia • Conseil IT • Site Web : http://www.conseil-it.fr • Conseil et formation • Architecture système et Bases de Données • Haute disponibilité • Montée en charge • Virtualisation • Indexation • Optimisation

5. Présentation • Christian Robert • Senior DBA • QXL Ricardo France • Secrétaire du Groupe des Utilisateurs Francophones de SQL Server (GUSS : www.guss.fr) MVP SQL Server 10 ans d’expérience sur SQL Server (depuis la version 7.0) Blog : http://blogs.codes-sources.com/christian

6. Agenda • Haute disponibilité ? • Définition, Facteurs, Solutions • Solutions de haute disponibilité • Envoi de journaux – Log Shipping • Equilibrage de charge réseau – Network LoadBalancing • Réplication • Cluster de basculement – Failover Cluster • Mise en miroir – DatabaseMirroring • Mix de solutions …  • ScalableSharedDatabase • Scale Out – Vues partitionnées distribuées • Optimisation des performances • CPU • Mémoire • Disque

7. Qu’est ce que la Disponibilité ? • La disponibilité du serveur est sa capacité à répondre aux requêtes en temps imparti • Exprimée généralement en pourcentage (H 24/24) • 99%  3 jours et demi par an • 99,9999%  30 secondes par an • Influencée par • La charge du serveur • Le temps laissé aux requêtes (Timeout) • La maintenance des serveurs

8. Pourquoi la haute disponibilité • Perte de CA si indisponibilité de la base de donnée • Applications critiques

9. Les facteurs de non disponibilité • Défaillance matérielle, coupure réseau • Disque dur HS, ventilateur HS, mémoire corrompue, etc. • Désastre naturel • Incendie, coupure électrique au niveau ville ou département, inondation, etc. • Opérations de maintenance • Charge du serveur, requêtes • Bugs

10. … et leurs solutions • Défaillance matérielle, Coupure réseau • Redondance matérielle, RAID, Onduleur, copie de secours disponible à chaud ou froid • Désastre naturel • Doubler le Data Center (si possible géographiquement éloigné), Sauvegardes • Opérations de maintenance • Utilisation d’une fenêtre de maintenance, copie disponible à chaud • Charge du serveur, requêtes • Augmenter les ressources matérielles ou les serveurs, optimisation • Bugs • Patcher avec parcimonie et Tester !!!

11. Envoi de journaux - Log Shipping • Automatisation • Sauvegarde des journaux • Envoi des journaux sur les serveurs de standby • Restauration des journaux • Fait partie de la stratégie de backup • Méthode personnalisable

12. Envoi de journaux Avantages • Possibilité de personnalisation • Modes de récupération • BulkLoogged • Complet • Plusieurs serveurs • Base de donnée standby • Disponible en lecture seule • Déconnexion utilisateurs ( restore) Inconvénients • Risque de perte de données • Basculement manuel • Obligation de reconnecter « manuellement » les utilisateur lors d’un basculement, ainsi que la reconfiguration des scripts de Log Shipping

13. Load balancing Load balancing Application server Application server Equilibrage de charge • Ferme de serveurs • Réplication de données • NLB Windows Read request Write request User traffic Replicated data

14. Equilibrage de charge Avantages • Nombre élevé de serveurs • Répartition de la charge • Disponibilité élevée • Insensible défaillances matérielles • Mise à jour OS / SQL Server Inconvénients • Maintenance un peu complexe • Réplication • NLB • Coûts • Trafic réseau (LAN / WAN) • Latence de mise à jour • Définition des rôles • Mise à jour de données

15. Réplication • Différentes topologies possibles • Plusieurs types de réplications • Capture instantanée • Transactionnelle • Fusion Abonné Editeur Distributeur Abonné

16. Démo Réplication

17. Réplication Avantages • Autonomie des serveurs • Données en lecture • Limitation du verrouillage • Répartition de la charge • Données en écriture • Peer To Peer • Transactionnelle avec maj des abonnés • Granularité : publication • Filtrage horizontal sur les articles • Nombre de serveurs • Répartition géographique • Gestion des conflits (fusion, peer to peer) Inconvénients • Mais attention aux conflits … • Stratégie de backup à prévoir • Maintenance un peu complexe • Coûts de stockage • Trafic réseau (LAN / WAN) • Latence de mise à jour des données • Coupure réseau • Log des bases éditeur • Taille des la base Distribution

18. Cluster de basculement • Couche de FailoverClustering Windows NœudA Groupederessources Serveurvirtuel Client Réseauprivé Quorum Disque 1 Pulsation Disque 1 Réseaupublic NœudB

19. Démo Cluster de basculement

20. Cluster de basculement Avantages • Défaillances matérielles • Défaillances logicielles • Stockage centralisé (SAN) • Simili cluster « actif/actif » • Nombre de nœuds / instances • Adresse IP et nom DNS virtuels • Granularité : Instance Inconvénients • Défaillance système disque • Pas de répartition de charge • Coupure se service • Coût • Durée du Recovery • Granularité : Instance

21. Mise en Miroir • Basculement • Automatique • Manuel • Pas de SPOF(Single Point Of Failure) • Envoi des journaux • Mode synchrone • Sécurité • Mode Asynchrone • Performance Serveurtémoin(facultatif) Sessioncliente Sessiondemiseenmiroirdebasesde données Serveurmiroir(basededonnéesdesecours) Serveurprincipal(basededonnéesenligne)

22. Démo Mise en miroir

23. Mise en Miroir Avantages • Défaillance matérielle • Défaillance OS • Défaillance stockage • Pas de coupure de service • Création de Snapshot • Solution « Fail Over » • Granularité : Base de donnée Inconvénients • Chaines de connexion (client) • Répartition de charge • Trafic réseau • Coupure réseau (Log …) • 1 seul réplica • 1 seul Witness (scrutateur, témoin) • Initialisation du miroir • Granularité : Base de donnée • Latence réseau : commit • Coût

24. La très haute performance… • Temps de coupure acceptable • La haute disponibilité à un coût … • Ingéniosité • Mix des solutions • Clustering – Mirroring (Démo à suivre) • Mirroring – Réplication • Mirroring – Log Shipping • Réplication – Loadbalancing (NLB) • Scale Out – Mirroring – FailoverClustering

25. La très haute performance • Solutions matérielles • Serveurs haute disponibilité (Stratus : 99,99999 % …) • Duplication de données sur le SAN (SAN mirroring) • Conception des bases • Vues partitionnées • Vues fédérées • Opérations en ligne • Madison

26. Démo Mise en Miroir et Cluster de Basculement

27. ScalableSharedDatabase Instance SQL Server Instance SQL Server Permet de lire une base de données placée sur une partition en lecture seule, via plusieurs instances SQL Server. Mais seule une instance peut y écrire quand la partition est en lecture / écriture SAN DB Instance SQL Server écriture lecture

28. ScalableSharedDatabase Avantages • Plusieurs instances accèdent à une base unique en lecture • Utilisation typique jusqu’à 8 instances • Répond aux problématiques de montée en charge horizontal (ajout de serveurs) Inconvénients • Besoin de configuration côté Windows (partition) • Seule une instance peut réaliser les écritures avec les autres instances déconnectés • Nécessite un SAN ou disque partagé iSCSI • Point défaillance : disque partagé

29. Scale Out Séparation des données sur des serveurs multiples, pour profiter des ressources de machines multiples. Utilisation des vues partitionnées distribuées par exemple, d’autres solutions « maison » sont possibles Vue / … Instance SQL ServerEurope Instance SQL Server Amérique Instance SQL Server Asie DB DB DB

30. Scale Out Avantages • Répond aux problématiques de montée en charge horizontal (ajout de serveurs) • Données accessibles en écriture • Mises à jour synchrones Inconvénients • Seule une instance peut réaliser les écritures • Besoin de d’une solution de redondance pour éviter la perte de données (Mirroring, Clustering, etc.) • Gestion des transactions distribuées (MS DTC) • Les requêtes doivent si possible cibler un serveur, la clef de partitionnement doit être bien choisie

31. Comparaison des méthodes * Edition Enterprise uniquement

32. Comparaison des méthodes

33. Opérations bridant l’accès aux données • Opération Hors Ligne • Création d’index, Reconstruction d’index • Suppression d’index Clustered • Ajout de colonne(s) dans une table • Opérations nuisant potentiellement à la disponibilité des données • Insertion / Suppression de grande quantité de données • Contention de méta données • Contention sur ressource matérielle

34. Partitionnement • Vues partionnées • Cas des vues partitionnées distribuées vu auparavant • Partitionnement natif • Switch entre les tables et les données • Import export de grande quantité de données dans une table existante • Verrou possible au niveau partition depuis SQL Server 2008 en lieu et place de la table • Plusieurs opérations de maintenance peuvent cibler une partition • Améliore la disponibilité durant les périodes de maintenance

35. Opérations En Ligne • Certaines opération d’index peuvent dorénavant être produite en ligne • A partir SQL Server 2005 / Edition Enterprise • Utilise le versionnement d’enregistrement dans tempdb • Le traitement est de fait un peu plus long • Reste Impossible sur les tables possédant des LOB (Large OBjects) • Certaines opérations restent à proscrire • Ajouts de colonnes, etc.

36. Démo Opérations en ligne / Partitionnement

37. Optimisation des performances • Les CPU • Affinity • SQL et SSAS • MAXDOP • La Mémoire • Les Disques • Configuration • Performances attendues • Resources

38. Les CPU • SQL 2008 R2 apporte une meilleur scalability • Support jusqu’à 256 CPU • Meilleur gestion des éléments internes (Latches, Xevent, Bufferpool, LogManager) • Gestion du temps interne en sous-millisecondes • Economie d’énergie en s’appuyant sur les amélioration de Windows Server 2008 R2

39. CPU ProcessAffinity • C’est l’association entre les threads et les processeurs logiques • Utilisation meilleur du cache proche des processeurs • Les types: • Auto (par defaut) • SQL Server décide où exécuter les threads pour utiliser tous les processeurs • Manuel • L’administrateur précise quels processeurs utiliser

40. Configuration de l’Affinity • Nouvelle commande T-SQL pour configurer la CPU Affinity ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = { AUTO | <range_spec> } | NUMANODE = <range_spec> [ ; ] <range_spec> ::= { <integer> | <integer> TO <integer> } [ ,...n ] • Marche pour toutes les configurations (<=64lp et >64lp) • Abandoner sp_configureaffinity_mask • Example d’Affinity: • Sur les nodes NUMA ALTER SERVER CONFIGURATION SET PROCESS AFFINITY NUMANODE=0 TO 3, 8 to 12; • Sur les CPUs ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU=0 TO 63, 128 TO 191;

41. Voirl’Affinity select m.memory_node_id, cast(m.cpu_affinity_maskas binary(8)) as cpu_mask, cast(m.online_scheduler_maskas binary(8)) as online_scheduler_mask, m.processor_group from sys.dm_os_memory_nodes m, sys.dm_os_nodes n where n.node_state_desc not like '%DAC%‘ and m.memory_node_id = n.memory_node_id order by m.processor_group, m.cpu_affinity_mask;

42. Configurer l’Affinity • Isolation des ressources • Multi-instances • SSAS et SQL sur la même machine • D’autres application sur la même machine • Fort taux d’interruption par les IO (disques et/ou réseau) • Comment: • Déterminer les besoins en CPU et mémoire pour chaque instance et application • Configurer les instances et applications par groupes de nodes • Pour SQL de pas configurer dans le Task Manager • Pour SQL toujours positionner le « max server memory » • Ne pas configurer avec plus de mémoire que disponible • Changer les configurations quand les serveurs sont inactifs • L’Affinity CPU est ignorée dans une VM.

43. SQL et SSAS • Isoler les processeurs pour chacun des deux process • Utiliser le Windows System Resource Manager (WSRM) pour SSAS • Mémoire: WSRM ou msmdsrv.ini <LowMemoryLimit> = <TotalMemoryLimit> - 10% <TotalMemoryLimit> <HardMemoryLimit> = <TotalMemoryLimit> +10% • Processeur: WSRM ou processor affinity dans le task manager • Ne pas utiliser WSRM avec le moteur SQL. • Mémoire : sp_configure “max server memory” • Processeur : ALTER SERVER CONFIGURATION SET PROCESS AFFINITY

44. Max Degree of Parallelism(MAXDOP) • Contrôle combien de thread peuvent être utilisés pour des requêtes impliquant le parallélisme • Le Query Processor décides quelles opérations peuvent être parallélisée • Le nombre de threads est déterminé au moment de l’exécution • Il peut être positionné au niveau du serveur • sp_configure “max degree of parallelism”,<value> • Ou comme indicateur d’une requête <query_hint> ::= MAXDOP value SELECT ProductID FROM SalesOrderDetail OPTION (MAXDOP 2);

45. MAXDOP et 2008 R2 • Defaut MAXDOP = min(cpus,64) • Recommandations: • Avec “full loggingrecovery model” , MAXDOP = 8. • Avec “bulk-logged” ou “simplyrecovery model”, MAXDOP jusqu’a 32. • Pour les serveurs NUMA, MAXDOP ne doit pas dépasser ne nombre de CPU physique affecté à chaque node NUMA. • Pour les serveurs utilisant l’hyper-threading, MAXDOP ne dois pas dépasser le nombre de processeurs physiques.

46. La Mémoire • Par défaut SQL Serveur ne vas pas acquérir toute la mémoire au démarrage • Le buffer pool va grossir au fur et a mesure des besoins • Si un autre process a acquis la mémoire avant SQL, il risque de ne pas obtenir ce qui a été configuré • Eviter la pagination du Buffer Pool avec “Locking Pages in Memory” • Automatique si le compte SQL Service a le privilège Lock Pages in Memory • Uniquement avec l’Enterprise Edition • Utiliser gpedit.msc http://msdn.microsoft.com/en-us/library/ms190730.aspx • Note: les autres éléments de SQL peuvent être paginées

47. La Mémoire • Min, Max Server Memory détermine si la taille du Buffer Pool est dynamique • Calcul du Buffer Pool désiré • Bpool = Msql - Threads - XPs - CLR GC heap – etc… • Sp_configure ‘Max Server Memory’ = Bpool • Etudier: Sp_configure ‘Min Server Memory’ = Bpool • Laisser assez de mémoire à L’OS et autres applications • La mémoire autre que le Buffer Pool ne peut pas être limitée • Réserver assez d’espace hors du Buffer Pool pour • SQL Threads • Les threads consomment chacune 4MB sur Itanium, 2MB sur X64 (= 1GB pour 256 Worker Threads sur Itanium!) • XPs, In-Proc OleDB drivers, CLR GC heap, log buffer etc…

48. La Mémoire • SQL réagit aux pressions mémoires externes • Si la mémoire est dynamique (Max <> Min) SQL essaiera de réduire son Working Set à la demande de l’OS • Parfois SQL ne répond pas assez vite et l’OS pagine des éléments du Working Set de SQL. • Si la mémoire est fixe (Max = Min), l’OS pagine des éléments du Working Set de SQL lors de pressions mémoire • Avec Windows 2003 quelques fixes sont disponibles pour limiter la pagination de SQL • La pagination de SQL est très mauvaise • Système bloqué, requêtes très longues • Des process non prévu ou un ayant une trop forte consommation mémoire peuvent provoquer la pagination de SQL • La pagination d’applications consomme CPU et disque • Peut aussi dégrader les performances de SQL

49. Les Disques • Les types d’I/O disque de SQL Server • En 10 ans, la capacité des disques à été multipliée par 100 • En 10 ans, le temps d’accès aux disques à seulement été divisé par 10 •  Déterminer le # de disques nécessaires n’est pas une question de volume mais de performances

50. Les Disques • Regardons les cas des lectures ‘random’ (8K pages): • Un disque standard 3.5’’ 15K RPM à de bonnes performances (I/O < 10ms) jusqu’à 150 lectures ‘random’/s • Les nouveaux 2.5’’ 15K RPM vont jusqu’a 200 /sec • Donc si j’ai besoin pour mon application de ~ 15K de lecture ‘random’ de pages (index seek) par seconde : • Mes disques me fournissant ~1.2MB/sec (150*8k) • Il va falloir prévoir 100 disques pour tenir la charge uniquement pour les fichiers de données • Regardons les cas des lectures séquentielles (64K= 1 extent): • Le cas le pire est lorsque 1 extent sur disque appartient à un objet SQL et le suivant appartient à un autre objet (cas fréquent) surtout en cas de fragmentation de table. • On comprend donc que le nombre de lecture reste sensiblement le même que dans le cas ‘random’ (~150 read/s) • En réorganisant les tables  un disque lit ~10MB/sec