postgresql meetup nantes #2
Embed Size (px)
TRANSCRIPT

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
Document publié sous Licence Creative Commons BY-SA 2.0