powa 3 - optimisations avancées de postgresql

43
PoWA 3 Optimisations avancées de PostgreSQL Ronan Dunklau - Julien Rouhaud Dalibo - www.dalibo.org 24 septembre 2015 - PostgreSQL Session #7 [ 1 / 43 ]

Upload: trinhngoc

Post on 02-Jan-2017

228 views

Category:

Documents


0 download

TRANSCRIPT

Page 1: PoWA 3 - Optimisations avancées de PostgreSQL

PoWA 3Optimisations avancées de PostgreSQL

Ronan Dunklau - Julien Rouhaud

Dalibo - www.dalibo.org

24 septembre 2015 - PostgreSQL Session #7

[ 1 / 43 ]

Page 2: PoWA 3 - Optimisations avancées de PostgreSQL

License

I Create Commons BY-NC-SAI Vous êtes libre

I de partagerI de modifier

I Sous les conditions suivantesI AttributionI Non commercialI Partage dans les mêmes conditions

[ 2 / 43 ]

Page 3: PoWA 3 - Optimisations avancées de PostgreSQL

Auteurs

I Ronan DunklauI DBA @ DaliboI Open-Source : Multicorn...I Contributeur PostgreSQL (FDWs...)

I Julien RouhaudI DBA @ DaliboI Open-Source : HypoPG, OPM...I Contributeur PostgreSQL, PgSQL FR...

I Mais aussiI Marc CousinI Thomas Reiss

[ 3 / 43 ]

Page 4: PoWA 3 - Optimisations avancées de PostgreSQL

PoWA?Présentation

I Outil d’analyse de chargeI Et d’optimisationI En temps réel

[ 4 / 43 ]

Page 6: PoWA 3 - Optimisations avancées de PostgreSQL

pg_stat_statementsPrésentation

I Contrib officielle PostgreSQLI Normalisation de requêtesI Cumul de compteurs (buffers, tempsd’exécution...), par

I utilisateurI baseI requête

[ 6 / 43 ]

Page 7: PoWA 3 - Optimisations avancées de PostgreSQL

pg_stat_statementsConcrètement

I Nombre d’exécutions d’une requêteI Temps moyen d’exécution d’une requêteI Création de fichiers temporairesI Accès dans ou hors du cache de PostgreSQL

[ 7 / 43 ]

Page 8: PoWA 3 - Optimisations avancées de PostgreSQL

pg_stat_kcachePrésentation

I Capture de métriques systèmes par requêtenormalisée

I Accès physique aux disquesI Utilisation CPU

[ 8 / 43 ]

Page 9: PoWA 3 - Optimisations avancées de PostgreSQL

pg_stat_kcacheConcrètement

I "Vrai" hit-ratio (cache PostgreSQL / cache système)I Identification des requêtes coûteuses en tempsprocesseur

[ 9 / 43 ]

Page 10: PoWA 3 - Optimisations avancées de PostgreSQL

pg_qualstatsPrésentation

I Analyse des prédicatsI clause WHEREI clause JOIN

I Stockage de nombreuses statistiquesI SélectivitéI Échantillon de constantes (+/- filtrantes, fréquentes)I Nombre d’exécutionsI Type de parcours (séquentiel ou index)

[ 10 / 43 ]

Page 11: PoWA 3 - Optimisations avancées de PostgreSQL

pg_qualstatsConcrètement

I Trouver des index manquantsI Statistiques avancées sur les prédicatsI ...

[ 11 / 43 ]

Page 12: PoWA 3 - Optimisations avancées de PostgreSQL

powa-archivistPrésentation

I Historiser toutes ces sources de donnéesI Configurable (rétention, fréquence...)I Extensible à d’autres sources de données

[ 12 / 43 ]

Page 13: PoWA 3 - Optimisations avancées de PostgreSQL

powa-archivistConcrètement

I Quels sont les goulets d’étranglementI Pour quelle raisonI Y remédierI En temps réel

[ 13 / 43 ]

Page 14: PoWA 3 - Optimisations avancées de PostgreSQL

Compatibilité

I PostgreSQL 9.4 et plusI PoWA 1 compatible 9.3, mais beaucoup plus limité

[ 14 / 43 ]

Page 15: PoWA 3 - Optimisations avancées de PostgreSQL

powa-webPrésentation

I Interface graphique webI Contrôle une ou plusieurs instances PoWAI Drill-down analysis

[ 15 / 43 ]

Page 16: PoWA 3 - Optimisations avancées de PostgreSQL

powa-webExemple d’utilisation

I Mauvaises performances sur certaines partiesd’une application

I Choisir la période d’analyse (dernière heure pardéfaut)

I Trouver la base concernée

[ 16 / 43 ]

Page 17: PoWA 3 - Optimisations avancées de PostgreSQL

powa-webVue d’un cluster 1

[ 17 / 43 ]

Page 18: PoWA 3 - Optimisations avancées de PostgreSQL

powa-webVue d’un cluster 2

[ 18 / 43 ]

Page 19: PoWA 3 - Optimisations avancées de PostgreSQL

powa-webVue d’une base

I La base est identifiéeI Trouver les requêtes problématiques

[ 19 / 43 ]

Page 20: PoWA 3 - Optimisations avancées de PostgreSQL

powa-webVue d’une base 1

[ 20 / 43 ]

Page 21: PoWA 3 - Optimisations avancées de PostgreSQL

powa-webVue d’une base 2

[ 21 / 43 ]

Page 22: PoWA 3 - Optimisations avancées de PostgreSQL

powa-webVue à la requête

I 2 requêtes problématiques trouvéesI inspection de chacune d’elles

[ 22 / 43 ]

Page 23: PoWA 3 - Optimisations avancées de PostgreSQL

powa-webPremière requête - SQL

SELECT

com.id,

sum(c_l.pric) AS total_price

FROM

command com

JOIN command_line c_l ON com.id = c_l.id_command

JOIN client cli ON cli.id = com.id_client

WHERE

cli.id = ?

GROUP BY

com.id

[ 23 / 43 ]

Page 24: PoWA 3 - Optimisations avancées de PostgreSQL

powa-webPremière requête - cache

[ 24 / 43 ]

Page 25: PoWA 3 - Optimisations avancées de PostgreSQL

powa-webPremière requête - CPU

[ 25 / 43 ]

Page 26: PoWA 3 - Optimisations avancées de PostgreSQL

powa-webPremière requête - prédicats

[ 26 / 43 ]

Page 27: PoWA 3 - Optimisations avancées de PostgreSQL

powa-webPremière requête - index

[ 27 / 43 ]

Page 28: PoWA 3 - Optimisations avancées de PostgreSQL

powa-webDeuxième requête - SQL

SELECT id, dt

FROM command

WHERE state = ?

[ 28 / 43 ]

Page 29: PoWA 3 - Optimisations avancées de PostgreSQL

powa-webDeuxième requête - EXPLAIN

[ 29 / 43 ]

Page 30: PoWA 3 - Optimisations avancées de PostgreSQL

powa-webEn video

I

[ 30 / 43 ]

Page 31: PoWA 3 - Optimisations avancées de PostgreSQL

powa-webNouveautés version 3

I Support de l’extension HypoPGI Suggestion globale d’index

[ 31 / 43 ]

Page 32: PoWA 3 - Optimisations avancées de PostgreSQL

HypoPGPrésentation

I Gestion d’index hypothétiqueI Création instantanée, aucune ressourceconsommée

I Pris en compte lors d’un EXPLAIN

[ 32 / 43 ]

Page 33: PoWA 3 - Optimisations avancées de PostgreSQL

HypoPGConcrètement

I Mon index sera-t-il utilisé ?I Quelle taille fera-t-il ?I Quel gain apportera-t-il ?

[ 33 / 43 ]

Page 34: PoWA 3 - Optimisations avancées de PostgreSQL

HypoPGEn action

[ 34 / 43 ]

Page 35: PoWA 3 - Optimisations avancées de PostgreSQL

Optimisation globalePrésentation

I Trouver les index optimaux à créerI Satisfaire toutes les requêtesI Créer le moins d’index possibleI Gestion des index multi-colonnes

[ 35 / 43 ]

Page 36: PoWA 3 - Optimisations avancées de PostgreSQL

Optimisation globaleFonctionnement 1

I Récupération des prédicats à optimiserI Prédicats filtrant en moyenne plus de X lignesI Prédicats filtrant en moyenne plus de X %I Prédicats utilisés lors d’un parcours séquentiel

[ 36 / 43 ]

Page 37: PoWA 3 - Optimisations avancées de PostgreSQL

Optimisation globaleFonctionnement 2

I Validation du résultatI Si HypoPG est disponible sur la base

I Valider l’utilisation de chaque index avec HypoPG

[ 37 / 43 ]

Page 38: PoWA 3 - Optimisations avancées de PostgreSQL

Optimisation globaleFonctionnement 3

I Estimation du gainI Si HypoPG est disponible sur la base

I Comparaison du coût avec et sans indexhypothétique

I Rapprochement avec le nombre d’exécutions desrequêtes

I Gain en performance estimé par requête etglobalement

[ 38 / 43 ]

Page 39: PoWA 3 - Optimisations avancées de PostgreSQL

Optimisation globaleEn action

[ 39 / 43 ]

Page 40: PoWA 3 - Optimisations avancées de PostgreSQL

Optimisation globaleEn action

[ 40 / 43 ]

Page 41: PoWA 3 - Optimisations avancées de PostgreSQL

Optimisation globaleEn action

I vidéo

[ 41 / 43 ]