postgresql meetup nantes #2
Post on 21-Jan-2017
348 views
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