DBA au service du développeur

Rodolphe Quiédeville

Meetup PostgreSQL Nantes

26 avril 2016

#mylife

I Découvert 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 d’oiseaux dans un espace finitoutes les 5 minutes

Table ‘‘public.observation’’Column | 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 données

Exploitation des données

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 réponse dépend du volume de données

I 1 an de données 91 msecI 4 ans de données 523 msecI 12 ans de données 1495 msec

Non scalabilité

Le temps de réponse dépend du volume de données

I 1 an de données 91 msec

I 4 ans de données 523 msecI 12 ans de données 1495 msec

Non scalabilité

Le temps de réponse dépend du volume de données

I 1 an de données 91 msecI 4 ans de données 523 msec

I 12 ans de données 1495 msec

Non scalabilité

Le temps de réponse dépend du volume de données

I 1 an de données 91 msecI 4 ans de données 523 msecI 12 ans de données 1495 msec

Mélange de données

Pour un graphique temps réel avec un historique conséquent

I donnée froide (1,7M de tuples)I donnée chaude (toutes les 5 min)

Mélange de données

Pour un graphique temps réel avec un historique conséquent

I donnée froide (1,7M de tuples)

I donnée chaude (toutes les 5 min)

Mélange de données

Pour un graphique temps réel avec un historique conséquent

I donnée froide (1,7M de tuples)I donnée chaude (toutes les 5 min)

Exploitation des données

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 données

Gérer les données 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 données 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 données chaudes

I un peu plus complexeI appel à une solution extérieure

PipelineDB

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

Le stream

Créer 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

Créer une vue pour consulter les données

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 développeur

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 développeur

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 développeur

La table locale pour récupérer les données

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 l’alimentation

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 d’historique

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

Dernière 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 résumé

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 résumé

Temps de requête passe de 2723 ms à 1.962 ms

Questions ?

Rodolphe Quiédeville

rodolphe.quiedeville@people-doc.com

Document publié sous Licence Creative Commons BY-SA 2.0