postgresql meetup nantes #2

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