\documentclass[hyperref={colorlinks=true,urlcolor=blue}]{beamer}
\usepackage[utf8]{inputenc}
\usepackage{color}
\usepackage{minted}

\usepackage[frenchb]{babel}

\usepackage{verbments}
\usetheme{Dalibo}

%%\usetheme[opacity=0.40,titleopacity=0.30]{Dalibo}
%%\useoutertheme{Dalibo}

% utilisation des fontes non standard beamer
% necessite le package "texlive-fonts-extra"
\usepackage{DejaVuSans}

\usepackage[T1]{fontenc}

\usepackage{textpos}

\usemintedstyle{pastie}

% pour \sout{texte barre}
\usepackage{ulem}


\setbeamercovered{transparent}

\definecolor{coultitre}{RGB}{9,61,114}
\setbeamercolor{structure}{fg=coultitre}

\definecolor{coulbgcode}{RGB}{242,242,242}

% Background

% Disable navigation bar
\setbeamertemplate{navigation symbols}{}

\title{PoWA 3}
\subtitle {Optimizing your databases with PoWA 3}
% moi
\author{Julien Rouhaud}
% la boîte
\institute{Dalibo - \href{http://www.dalibo.com/en/}{www.dalibo.com/en/}}
% le logo
%\logo{\includegraphics[height=0.5cm]{images/logo_dalibo_large_blue}}
% numéroter les slides ?
%\logo{\insertframenumber/\inserttotalframenumber}
% la date
\date{June, 28 2016 - 5432... Meet us!}
\begin{document}

% Change line number style
\renewcommand{\theFancyVerbLine}{\sffamily\textcolor[rgb]{0.5,0.5,0.5}{\tiny\arabic{FancyVerbLine}}}

\newif\ifvideo
% uncomment me if needed
%\videotrue


\begin{frame}
\titlepage
\end{frame}

% Add Dalibo logo in header
\addtobeamertemplate{frametitle}{}{%
  \begin{textblock*}{100mm}(.81\textwidth,-1.2cm)
  \includegraphics[width=2.6cm]{../images/logo_dalibo_large_white.png}
  \end{textblock*}
}

\begin{frame}{Authors}
    \framesubtitle{~}
    \begin{itemize}
        \item Ronan Dunklau
        \begin{itemize}
          \item DBA @ Dalibo
          \item Open-Source: Multicorn...
          \item Some PostgreSQL contributions (IMPORT FOREIGN SCHEMA...)
        \end{itemize}
        \item Julien Rouhaud
        \begin{itemize}
          \item DBA @ Dalibo
          \item Open-Source: HypoPG, OPM...
          \item Some PostgreSQL contributions
        \end{itemize}
        \item But also...
        \begin{itemize}
            \item Marc Cousin
            \item Thomas Reiss
        \end{itemize}
    \end{itemize}
\end{frame}

\begin{frame}{PoWA ?}
    \framesubtitle{What is PoWA}
    \begin{columns}[t]
        \begin{column}{2cm}
           \begin{block}{~}
               \includegraphics[scale=.22]{./img/logo.png}
           \end{block}
        \end{column}

        \begin{column}{5cm}
            \begin{block}{~}
                \includegraphics[scale=.1]{./img/powa_main.png}
            \end{block}
        \end{column}
    \end{columns}

    \begin{itemize}
        \item Workload analysis tool
        \item Suggests opitmizations
        \item Live!
    \end{itemize}
\end{frame}

\begin{frame}{Application stack}
    \framesubtitle{Presentation}
    \begin{itemize}
        \item \href{http://docs.postgresql.fr/9.4/pgstatstatements.html}{pg\_stat\_statements}
        \item \href{https://github.com/dalibo/pg\_stat\_kcache}{github.com/dalibo/pg\_stat\_kcache}
        \item \href{https://github.com/dalibo/pg\_qualstats}{github.com/dalibo/pg\_qualstats}
        \item \href{https://github.com/dalibo/powa-archivist}{github.com/dalibo/powa-archivist}
        \item \href{https://github.com/dalibo/powa-web}{github.com/dalibo/powa-web}
    \end{itemize}
\end{frame}

\begin{frame}{pg\_stat\_statements}
    \framesubtitle{Presentation}
    \begin{itemize}
        \item Official PostgreSQL contrib
        \item Normalized queries
        \item Cumulative counters (buffers, execution time...), by
        \begin{itemize}
            \item user
            \item database
            \item query
        \end{itemize}
    \end{itemize}
\end{frame}

\begin{frame}{pg\_stat\_statements}
    \framesubtitle{Useful indicators}
    \begin{itemize}
        \item Number of execution per normalized query
        \item Average execution time
        \item Temporary file creation
        \item Blocks access from or outside \textbf{PostgreSQL}'s cache
    \end{itemize}
\end{frame}

\begin{frame}{pg\_stat\_statements}
    \framesubtitle{In action 1}
    \includegraphics[scale=.22]{./img/powa_pgs_cluster_1.png}
\end{frame}

\begin{frame}{pg\_stat\_statements}
    \framesubtitle{In action 2}
    \includegraphics[scale=.32]{./img/powa_pgs_cluster_2.png}
\end{frame}

\begin{frame}{pg\_stat\_kcache}
    \framesubtitle{Presentation}
    \begin{itemize}
        \item Collects system metrics, by normalized queries
        \begin{itemize}
            \item Physical disk access
            \item CPU usage
        \end{itemize}
    \end{itemize}
\end{frame}

\begin{frame}{pg\_stat\_kcache}
    \framesubtitle{Meaning...}
    \begin{itemize}
        \item "real" hit-ratio (PostgreSQL cache Vs system cache)
        \item Identify CPU bound queries
    \end{itemize}
\end{frame}

\begin{frame}{pg\_stat\_kcache}
    \framesubtitle{In action 1}
    \includegraphics[scale=.44]{./img/powa_pgs_pgkc_1.png}
\end{frame}

\begin{frame}{pg\_stat\_kcache}
    \framesubtitle{In action 2}
    \includegraphics[scale=.23]{./img/powa_pgs_pgkc_2.png}
\end{frame}

\begin{frame}{pg\_qualstats}
    \framesubtitle{Presentation}
    \begin{itemize}
        \item Predicate analysis
        \begin{itemize}
            \item WHERE clauses
            \item JOIN clauses
        \end{itemize}
        \item Collects various metrics
        \begin{itemize}
            \item Selectivity
            \item Constants sampling (most executed, most filtering...)
            \item Execution count
            \item Evalutation type (Index clause or post-scan filtering)
        \end{itemize}
    \end{itemize}
\end{frame}

\begin{frame}{pg\_qualstats}
    \framesubtitle{In action 1}
    \includegraphics[scale=.7]{./img/powa_pgs_pgqs_1.png}
\end{frame}

\begin{frame}{pg\_qualstats}
    \framesubtitle{In action 2}
    \includegraphics[scale=.32]{./img/powa_pgs_pgqs_2.png}
\end{frame}

\begin{frame}{pg\_qualstats}
    \framesubtitle{In action 3}
    \includegraphics[scale=.45]{./img/powa_pgs_pgqs_3.png}
\end{frame}

\begin{frame}{pg\_qualstats}
    \framesubtitle{In action 4}
    \includegraphics[scale=.34]{./img/powa_pgs_pgqs_4.png}
\end{frame}

\begin{frame}{powa-archivist}
    \framesubtitle{Presentation}
    \begin{itemize}
        \item Archive those data sources
        \item Configurable (retention, frequency...)
        \item Extensible to other datasources
    \end{itemize}
\end{frame}

\begin{frame}{powa-archivist}
    \framesubtitle{What to get}
    \begin{itemize}
        \item Where / when are the bottlenecks
        \item For what reason
        \item How to fix
        \item Live!
    \end{itemize}
\end{frame}

\begin{frame}{Compatibility}
    \framesubtitle{~}
    \begin{itemize}
        \item PostgreSQL \textbf{9.4} et later% (voir \href{http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=91484409bdd17f330d10671d388b72d4ef1451d7}{commit})
        \item PoWA 1 compatible with 9.3, but much more limited
    \end{itemize}
\end{frame}

\begin{frame}{powa-web}
    \framesubtitle{Presentation}
    \begin{itemize}
        \item Web interface for PoWA
        \item Manage one or more PoWA instance
        \item Drill-down analysis
    \end{itemize}
\end{frame}

\begin{frame}{powa-web}
    \framesubtitle{Usage example}
    \begin{itemize}
        \item problem: bad performance on parts of an application
        \item Select an analysis period
        \item Identify the database
    \end{itemize}
\end{frame}

\begin{frame}{powa-web}
    \framesubtitle{cluster view - 1}
    \includegraphics[scale=.22]{./img/powa_pgs_cluster_1.png}
\end{frame}

\begin{frame}{powa-web}
    \framesubtitle{cluster view - 2}
    \includegraphics[scale=.32]{./img/powa_pgs_cluster_2.png}
\end{frame}

\begin{frame}{powa-web}
    \framesubtitle{Database view}
    \begin{itemize}
        \item Problematic database has been identified...
        \item let's drill down to the query level!
    \end{itemize}
\end{frame}

\begin{frame}{powa-web}
    \framesubtitle{Database view - 1}
    \includegraphics[scale=.22]{./img/powa_pgs_db_1.png}
\end{frame}

\begin{frame}{powa-web}
    \framesubtitle{Database view - 2}
    \includegraphics[scale=.22]{./img/powa_pgs_db_2.png}
\end{frame}

\begin{frame}{powa-web}
    \framesubtitle{Database view - 3}
    \includegraphics[scale=.35]{./img/powa_pgs_db_2_zoom.png}
\end{frame}

\begin{frame}{powa-web}
    \framesubtitle{Query view}
    \begin{itemize}
        \item 2 problematic queries
        \item Drill down on each of them
    \end{itemize}
\end{frame}

\begin{frame}[fragile]
    \frametitle{powa-web}
    \framesubtitle{First query - SQL}
    \begin{minted}[mathescape,
               numbersep=5pt,
               gobble=2,
               frame=lines,
               framesep=2mm
        ]{sql}
        SELECT
          com.id,
          sum(c_l.pric) AS total_price
        FROM
          command com
          JOIN command_line c_l ON com.id = c_l.id_command
          JOIN client cli ON cli.id = com.id_client
        WHERE
          cli.id = ?
        GROUP BY
          com.id
    \end{minted}
\end{frame}

\begin{frame}{powa-web}
    \framesubtitle{First query - cache}
    \includegraphics[scale=.44]{./img/powa_pgs_pgkc_1.png}
\end{frame}

\begin{frame}{powa-web}
    \framesubtitle{First query - CPU}
    \includegraphics[scale=.23]{./img/powa_pgs_pgkc_2.png}
\end{frame}

\begin{frame}{powa-web}
    \framesubtitle{First query - predicates}
    \includegraphics[scale=.32]{./img/powa_pgs_pgqs_2a.png}
\end{frame}

\begin{frame}{powa-web}
    \framesubtitle{First query - index}
    \includegraphics[scale=.32]{./img/powa_pgs_pgqs_2b.png}
\end{frame}

\begin{frame}[fragile]{powa-web}
    \framesubtitle{Second query - SQL}
    \begin{minted}[mathescape,
               numbersep=5pt,
               gobble=2,
               frame=lines,
               framesep=2mm
        ]{sql}
      SELECT id, dt
      FROM command
      WHERE state = ?
    \end{minted}
\end{frame}

\begin{frame}{powa-web}
    \framesubtitle{Second query - EXPLAIN}
    \includegraphics[scale=.34]{./img/powa_pgs_pgqs_6.png}
\end{frame}

\begin{frame}{powa-web}
    \framesubtitle{Second query - distribution}
    \includegraphics[scale=.32]{./img/powa_pgs_pgqs_3.png}
\end{frame}

\ifvideo
    \begin{frame}{powa-web}
        \framesubtitle{Video}
        \begin{itemize}
            \item 
        \end{itemize}
    \end{frame}
\fi

\begin{frame}{powa-web}
    \framesubtitle{What's new in version 3}
    \begin{itemize}
        \item \href{https://github.com/dalibo/hypopg}{github.com/dalibo/HypoPG} extension support
        \item Global index suggestion
    \end{itemize}
\end{frame}

\begin{frame}{HypoPG}
    \framesubtitle{Presentation}
    \begin{itemize}
        \item Allow for hypothetical indexes creation
        \item Instant creation, no impact on resources and no lock
        \item Only used in EXPLAIN statements
    \end{itemize}
\end{frame}

\begin{frame}[fragile]{HypoPG}
    \framesubtitle{Example}
    \begin{minted}[mathescape,
               numbersep=5pt,
               gobble=2,
               frame=lines,
               framesep=2mm
        ]{sql}
  rjuju=# EXPLAIN SELECT * FROM t1 WHERE id = 3 ;
                       QUERY PLAN
  ---------------------------------------------------
  Seq Scan on t1  (cost=0.00..1693.00 rows=1 width=4)
    Filter:  (id = 3)
  (2 rows)
    \end{minted}
\end{frame}

\begin{frame}[fragile]{HypoPG}
    \framesubtitle{Example}
    \begin{minted}[mathescape,
               numbersep=5pt,
               gobble=2,
               frame=lines,
               framesep=2mm
        ]{sql}
  # SELECT hypopg_create_index('CREATE INDEX ON t1(id)') ;
      hypopg_create_index
  --------------------------
  (77523,<77523>btree_t1_id)
  (1 row)
 
  rjuju=# EXPLAIN SELECT * FROM t1 WHERE id = 3 ;
                                   QUERY PLAN
  --------------------------------------------------------------------------
  Index Only Scan using <77523>btree_t1_id on t1  (0.04..8.06 rows=1 width=4)
    Index Cond:  (id = 3)
  (2 rows)
    \end{minted}
\end{frame}

\begin{frame}{HypoPG}
    \framesubtitle{What is it useful for}
    \begin{itemize}
        \item Will PostgreSQL use such an index
        \item What size can I expect it to be
        \item How useful can it be
    \end{itemize}
\end{frame}

\begin{frame}{HypoPG}
    \framesubtitle{In action}
    \includegraphics[scale=.32]{./img/powa_explain_hypo.png}
\end{frame}

\begin{frame}{Global optimization}
    \framesubtitle{Presentation}
    \begin{itemize}
        \item Find the optimal set of index to add
        \begin{itemize}
            \item Helping every queries
            \item Minimum set of indexes
            \item Privileging multi-column indexes
        \end{itemize}
    \end{itemize}
\end{frame}

\begin{frame}{Global optimization}
    \framesubtitle{Algorithm - 1}
    \begin{itemize}
        \item Fetch the predicates that need optimization (pg\_qualstats)
        \begin{itemize}
            \item Predicates filtering more than X lines out
            \item Predicates filtering more than X\% of lines out
            \item Predicates used as part of a Seq Scan
        \end{itemize}
    \end{itemize}
\end{frame}

\begin{frame}{Global optimization}
    \framesubtitle{Algorithm - 2}
    \begin{itemize}
        \item Group predicates by supported access methods
        \begin{itemize}
            \item \textit{\textbf{Hint:} Think about btree\_gist and btree\_gin}
        \end{itemize}
        \item Build a list of predicates "contained" by each predicates
        \begin{itemize}
            \item WHERE id = ? AND label = ?
            \item WHERE id = ?
            \item WHERE label = ?
        \end{itemize}
        \item For each node, attribute a "score" to it (currently, number of columns)
    \end{itemize}
\end{frame}

\begin{frame}{Global optimization}
    \framesubtitle{Algorithm - 3}
    \begin{itemize}
        \item For each node, compute a path containing all included node
        \item Score it (sum of individual nodes scores)
        \item Starting with the highest scoring path, generate the index definition for it
        \item Delete any other path made obsolete by this one
        \item Loop until no path is left unoptimized
    \end{itemize}
\end{frame}

\begin{frame}{Global optimization}
    \framesubtitle{Validation}
    \begin{itemize}
        \item Goal: estimate if the indexes will be used, and how much improvement they bring
        \item If HypoPG is available on the target database:
        \begin{itemize}
            \item Create hypothetical index for each suggestion
            \item EXPLAIN every query with and without the hypothetical indexes
            \item Based on the difference cost, estimate the gain
            \item If HypoPG is available on the target database:
            \begin{itemize}
                \item By query
                \item Globally
            \end{itemize}
            \item Estimate the size of the indexes
        \end{itemize}
    \end{itemize}
\end{frame}

\begin{frame}{Global optimization}
    \framesubtitle{In action}
    \includegraphics[scale=.33]{./img/powa_optimize_1a.png}
\end{frame}

\begin{frame}{Global optimization}
    \framesubtitle{In action}
    \includegraphics[scale=.5]{./img/powa_optimize_1b.png}
\end{frame}

\begin{frame}{Global optimization}
    \framesubtitle{In action}
    \includegraphics[scale=.23]{./img/powa_optimize_2.png}
\end{frame}

\begin{frame}{Global optimization}
    \framesubtitle{In action}
    \includegraphics[scale=.33]{./img/powa_optimize_2a.png}
\end{frame}

\begin{frame}{Global optimization}
    \framesubtitle{In action}
    \includegraphics[scale=.33]{./img/powa_optimize_2b.png}
\end{frame}

\ifvideo
    \begin{frame}{Global optimization}
        \framesubtitle{In action}
        \begin{itemize}
            \item vidéo
        \end{itemize}
    \end{frame}
\fi

\begin{frame}{What's next}
    \framesubtitle{Future enhancements}
    \begin{itemize}
        \item Find correlations, and suggest them once correlated statistics are available
        \begin{itemize}
            \item WHERE cityname = ? AND zipcode = ? (10 rows avg)
            \item WHERE cityname = ? (10 rows avg)
            \item WHERE zipcode = ? (10 rows avg)
            \item It means that cityname and zipcode are probably correlated
        \end{itemize}
        \item Collect statistics on table to take DML workload into account
        \item Suggest partial indexes based on most-often used values
    \end{itemize}
\end{frame}

\begin{frame}{Useful links}
    \framesubtitle{~}
    \begin{itemize}
        \item powa-archivist
        \begin{itemize}
            \item \href{https://dalibo.github.io/powa}{dalibo.github.io/powa (website)}
            \item \href{https://github.com/dalibo/powa-archivist}{github.com/dalibo/powa-archivist (repository)}
        \end{itemize}
        \item powa-web
        \begin{itemize}
            \item \href{https://github.com/dalibo/powa-web}{github.com/dalibo/powa-web (repository)}
            \item \href{http://demo-powa.dalibo.com}{demo-powa.dalibo.com (demo)}
        \end{itemize}
        \item pg\_qualstats
        \begin{itemize}
            \item \href{https://github.com/dalibo/pg\_qualstats}{github.com/dalibo/pg\_qualstats (repository)}
            \item \href{http://rdunklau.github.io/postgresql/powa/pg\_qualstats/2015/02/02/pg\_qualstats\_part1/}{article on rdunklau.github.io}
        \end{itemize}
        \item pg\_stat\_kcache
        \begin{itemize}
            \item \href{https://github.com/dalibo/pg\_stat\_kcache}{github.com/dalibo/pg\_stat\_kcache (repository)}
            \item \href{https://rjuju.github.io/postgresql/2015/03/04/pg\_stat\_kcache-2-0.html}{article on rjuju.github.io}
        \end{itemize}
        \item HypoPG
        \begin{itemize}
            \item \href{https://dalibo.github.io/hypopg}{dalibo.github.io/hypopg (website)}
            \item \href{https://github.com/dalibo/hypopg}{github.com/dalibo/hypopg (repository)}
            \item \href{https://rjuju.github.io/postgresql/2015/07/02/how-about-hypothetical-indexes.html}{article on rjuju.github.io}
        \end{itemize}
    \end{itemize}
\end{frame}

\begin{frame}{Questions ?}
    \framesubtitle{~}
    \begin{itemize}
        \item \href{mailto:contact@dalibo.com}{contact@dalibo.com}
        \item \href{mailto:powa@dalibo.com}{powa@dalibo.com}
        \item \href{http://powa.readthedocs.org}{powa.readthedocs.org}
    \end{itemize}
\end{frame}

\end{document}
