postgresql meetup nantes #2

Download PostgreSQL Meetup Nantes #2

Post on 21-Jan-2017

348 views

Category:

Technology

1 download

Embed Size (px)

TRANSCRIPT

  • DBA au service du dveloppeur

    Rodolphe Quideville

    Meetup PostgreSQL Nantes

    26 avril 2016

  • #mylife

    I Dcouvert Internet 28.kbits avec Netscape NavigatorI Utilise et produit du logiciel libre exclusivementI PostgreSQL depuis ... la 6.X ?I Consultant en performance des SI(G)I Senior Performance Engineer @PeopleDocI Formateur Upstream University

  • SQL

  • ORM

  • Cas concret

    Une observation du nombre doiseaux dans un espace finitoutes les 5 minutes

    Table public.observationColumn | Type | Modifiers

    ----------+-----------------------------+---------------obs_date | timestamp without time zone | default now()duration | integer |birds | integer |

  • Cas concret

    Table des observations

    pocpipe# select * from obs2 limit 5 ;obs_date | duration | birds

    ---------------------+----------+-------2000-01-01 00:00:00 | 4 | 672000-01-01 00:05:00 | 6 | 1942000-01-01 00:10:00 | 17 | 2732000-01-01 00:15:00 | 10 | 1492000-01-01 00:20:00 | 4 | 77

  • Exploitation des donnes

  • Exploitation des donnes

    SELECTdate_par t ( year , obs_date ) ,sum( b i r ds ) : : f l o a t / sum( du ra t i on ) : : f l o a t

    FROM observa t ionGROUP BY 1ORDER BY 1 ASC ;

  • Non scalabilit

    Le temps de rponse dpend du volume de donnes

    I 1 an de donnes 91 msecI 4 ans de donnes 523 msecI 12 ans de donnes 1495 msec

  • Non scalabilit

    Le temps de rponse dpend du volume de donnes

    I 1 an de donnes 91 msec

    I 4 ans de donnes 523 msecI 12 ans de donnes 1495 msec

  • Non scalabilit

    Le temps de rponse dpend du volume de donnes

    I 1 an de donnes 91 msecI 4 ans de donnes 523 msec

    I 12 ans de donnes 1495 msec

  • Non scalabilit

    Le temps de rponse dpend du volume de donnes

    I 1 an de donnes 91 msecI 4 ans de donnes 523 msecI 12 ans de donnes 1495 msec

  • Mlange de donnes

    Pour un graphique temps rel avec un historique consquent

    I donne froide (1,7M de tuples)I donne chaude (toutes les 5 min)

  • Mlange de donnes

    Pour un graphique temps rel avec un historique consquent

    I donne froide (1,7M de tuples)

    I donne chaude (toutes les 5 min)

  • Mlange de donnes

    Pour un graphique temps rel avec un historique consquent

    I donne froide (1,7M de tuples)I donne chaude (toutes les 5 min)

  • Exploitation des donnes

    SELECTdate_par t ( year , obs_date ) ,sum( b i r ds ) : : f l o a t / sum( du ra t i on ) : : f l o a t

    FROM observa t ionGROUP BY 1ORDER BY 1 ASC ;

  • Exploitation des donnes

    Grer les donnes froides

    CREATE MATERIALIZED VIEW observat ion_past ASSELECT

    date_par t ( year , obs_date ) AS obs_year ,SUM( b i r ds ) : : f l o a t / SUM( dura t i on ) : : f l o a t AS

    bi rds_per_hourFROM

    observa t ionWHERE

    date_par t ( year , obs_date ) : : i n t < 2016GROUP BY 1;

    Time : 1275.701 ms

  • Exploitation des donnes froides

    pocpipe#SELECT * FROM observation_pastWHERE obs_year > 2010 ORDER BY obs_year;

    obs_year | birds_per_hour----------+------------------

    2011 | 16.64109140092052012 | 16.63713002270292013 | 16.69850433846722014 | 16.63092083051992015 | 16.6618262419789

    (5 rows)

    Time: 0.609 ms

  • Exploitation des donnes chaudes

    I un peu plus complexeI appel une solution extrieure

  • PipelineDB

    I www.pipelinedb.comI PostgreSQL CompatibleI Continuous AggregationsI Sliding Window QueriesI ...

  • Le stream

    Crer un stream pour alimenter la base

    CREATE STREAM observat ions (obs_date timestamp ,du ra t i on i n t ,b i r ds i n t ) ;

  • La vue

    Crer une vue pour consulter les donnes

    CREATE CONTINUOUS VIEW observation_now ASSELECT date_par t ( year , obs_date ) AS obs_year ,

    SUM( dura t i on ) : : f l o a t AS sum_duration ,SUM( b i r ds ) : : f l o a t AS sum_birds

    FROMobservat ions

    WHEREdate_par t ( year , obs_date ) >= 2016

    GROUP BYdate_par t ( year , obs_date ) ;

  • Le DBA au service du dveloppeur

    On va jouer domicile :

    CREATE EXTENSION postgres_fdw ;

    CREATE SERVER fo re ign_se rve rFOREIGN DATA WRAPPER postgres_fdwOPTIONS ( host 127 .0 .0 .1 , po r t 9203 ,

    dbname p i p e l i n e ) ;

    CREATE USER MAPPING FOR rodoSERVER fo re ign_se rve r

    OPTIONS ( user p i p e l i n e ,password p i p e l i n e ) ;

  • Le DBA au service du dveloppeur

    La table locale pour alimenter le stream

    CREATE FOREIGN TABLE observat ion_stream (obs_date timestamp ,du ra t i on i n t ,b i r ds i n t )

    SERVER fo re ign_se rve rOPTIONS (

    schema_name p u b l i c , table_name observat ions , updatable t r ue ) ;

  • Le DBA au service du dveloppeur

    La table locale pour rcuprer les donnes

    CREATE FOREIGN TABLE observation_now (obs_year i n t ,sum_duration i n t ,sum_birds i n t )

    SERVER fo re ign_se rve rOPTIONS (

    schema_name p u b l i c , table_name observation_now , updatable f a l s e ) ;

  • Toujours vide

    pocpipe# select * from observation_now ;obs_year | sum_duration | sum_birds

    ----------+--------------+-----------(0 rows)

  • Alimentons pipelinedb

    On va automatiser lalimentation

    CREATE OR REPLACE FUNCTION observat ion_feed_stream ( )RETURNS TRIGGER AS $BODY$BEGININSERT INTO observat ion_stream

    VALUES (NEW. obs_date ,NEW. dura t ion ,NEW. b i r ds ) ;

    RETURN NEW;END;$BODY$ LANGUAGE p lpgsq l ;

    CREATE TRIGGER o b s e r v a t i o n _ t r i g g e rAFTER INSERT ON observa t ion FOR EACH ROWEXECUTE PROCEDURE observat ion_feed_stream ( ) ;

  • Alimentons pipelinedb

    Reprise dhistorique

    INSERT INTO observat ion_stream(SELECT FROM observa t ion wheredate_par t ( year , obs_date ) = 2016 ) ;INSERT 0 33409

  • Toujours vide ?

    pocpipe# select * from observation_now ;obs_year | sum_duration | sum_birds

    ----------+--------------+-----------2016 | 299864 | 5010160

    (1 row)

    Time: 4.179 ms

  • Toujours vide ?

    pocpipe# select * from observation_now ;obs_year | sum_duration | sum_birds

    ----------+--------------+-----------2016 | 299864 | 5010160

    (1 row)

    Time: 4.179 ms

    pocpipe# INSERT INTO observation VALUES (now(), 30, 200);INSERT 0 1

    Time: 9.904 ms

  • Toujours vide ?

    pocpipe# select * from observation_now ;obs_year | sum_duration | sum_birds

    ----------+--------------+-----------2016 | 299864 | 5010160

    (1 row)

    Time: 4.179 ms

    pocpipe# INSERT INTO observation VALUES (now(), 30, 200);INSERT 0 1

    Time: 9.904 ms

    pocpipe# select * from observation_now ;obs_year | sum_duration | sum_birds

    ----------+--------------+-----------2016 | 299894 | 5010360

    (1 row)

    Time: 1.194 ms

  • One Shot

    Dernire vue

    CREATE OR REPLACE VIEW o b s e r v a t i o n _ a l l ASWITH cte AS (

    SELECT obs_year , b i rds_per_hour FROM observat ion_pastUNION ALL

    SELECTobs_year , sum_birds : : f l o a t / sum_duration : : f l o a t AS

    bi rds_per_hourFROM observation_now )

    SELECT obs_year , b i rds_per_hour FROM cte ;

  • Badoum Ba !

    pocpipe# select * from observation_all order by 1;obs_year | birds_per_hour

    ----------+------------------2000 | 16.62038137754562001 | 16.66958993946532002 | 16.64714235978472003 | 16.57568147472122004 | 16.65753996854072005 | 16.63972995090022006 | 16.63713392702422007 | 16.65604801623632008 | 16.69656775345872009 | 16.68128522312782010 | 16.64042740473212011 | 16.64109140092052012 | 16.63713002270292013 | 16.69850433846722014 | 16.63092083051992015 | 16.66182624197892016 | 16.7081076754795

    (17 rows)

    Time: 4.211 ms

  • En rsum

    On remplace une table par une vueet

    SELECTdate_par t ( year , obs_date ) AS obs_year ,SUM( b i r ds ) : : f l o a t / SUM( dura t i on ) : : f l o a t AS

    bi rds_per_hourFROM

    observa t ionGROUP BY 1;

    par

    SELECT obs_year , b i rds_per_hour FROM o b s e r v a t i o n _ a l l ;

  • En rsum

    Temps de requte passe de 2723 ms 1.962 ms

  • Questions ?

    Rodolphe Quideville

    rodolphe.quiedeville@people-doc.com

    Document publi sous Licence Creative Commons BY-SA 2.0

    Introduction